Data Management Made Easy Using Nennius: Advanced Data Handling

Introduction

This article is part three of an introduction to application development for the Nennius engine. If you have not yet read part one or two, you should do so before continuing. We will be enhancing the News Manager application (http://nennius.sourceforge.net/nennius/webapps/news/) already discussed in the previous tutorial, so you will need a copy of the source code (http://nennius.sourceforge.net/nennius/webapps/news.zip) for that application if you want to work along.

Before we can start, we will once again need to run a few SQL queries to populate the application's database with the appropriate information. These queries simply build on top of the database created in part two:

-- create table to hold news release file attachments (images in this case)

CREATE TABLE `news_attachments` (

        `id` INT( 4 ) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT ,

        `news_id` INT( 3 ) UNSIGNED ZEROFILL NOT NULL ,

        `filename` VARCHAR( 50 ) NOT NULL ,

        PRIMARY KEY ( `id` ) ,

        INDEX ( `news_id` )

);

-- create table to hold record history (& associate to users & news tables via foreign keys)

CREATE TABLE `news_history` (

        `id` INT( 4 ) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT ,

        `news_id` INT( 3 ) UNSIGNED ZEROFILL NOT NULL ,

        `user_id` INT( 2 ) UNSIGNED ZEROFILL NOT NULL ,

        `datetime` DATETIME NOT NULL ,

        `description` TEXT NOT NULL ,

        PRIMARY KEY ( `id` ) ,

        INDEX ( `news_id` , `user_id` )

);

-- create comments table to hold user comments

CREATE TABLE `comments` (

        `id` INT( 10 ) UNSIGNED ZEROFILL NOT NULL ,

        `news_id` INT( 3 ) UNSIGNED ZEROFILL NOT NULL ,

        `datetime` DATETIME NOT NULL ,

        `name` VARCHAR( 50 ) NOT NULL ,

        `subject` VARCHAR( 100 ) NOT NULL ,

        `comment` TEXT NOT NULL ,

        PRIMARY KEY ( `id` ) ,

        INDEX ( `news_id` )

);

-- create notes table for moderators store private messages regarding user comments in

CREATE TABLE `comment_notes` (

        `id` int(4) unsigned zerofill NOT NULL auto_increment,

        `news_id` int(3) unsigned zerofill NOT NULL default '000',

        `user_id` int(2) unsigned zerofill NOT NULL default '00',

        `datetime` datetime NOT NULL default '0000-00-0000:00:00',

        `description` text NOT NULL,

        PRIMARY KEY  (`id`),

        KEY `news_id` (`news_id`,`user_id`)

);

-- insert addition user record with threshold of USER for later use

INSERT INTO `users` ( `id` , `username` , `password` , `name` , `email` , `threshold_id` )

        VALUES ( '', 'user', 'user', '', 'user@corporatewebsite.com', '03' );

As you can see, we've created several additional SQL tables (which will be explained later in the article) as well as an additional user record; nothing too complex. Now we're ready to start modifying our application.