Installing the Database and Configuring SQL
In these steps, replace 8230 with a random number between 8001 and 8999. Also be sure to replace the pathname:
with the pathname of your home directory. You can get the home path by using the 'pwd' command, by executing:
cd ~ pwd
Now to set up sql environment, make sure you have directory called mysql in your top home directory. If you already do, you need to clear out some of the old stuff, or risk errors with the install script. I have tested with my old 505 junk and had issues so I wiped it clean and had no issues. Create the directory if it doesn't exist:
Change directories, and be sure to stay in this directory for a while:
Run the script:
Open the EMACS editor, or your favorite editor such as Nano, or Vi. The important part hew is you make a file called: ".my.cnf"
emacs -nw ~/.my.cnf
Enter the following into your text editor, be sure to use your own port and home directory as described above.
[mysql] port=8230 socket=/ubc/icics/mss/cics515/2011/mysql/mysql.sock [mysqld] port=8230 socket=/ubc/icics/mss/cics515/2011/mysql/mysql.sock datadir=/ubc/icics/mss/cics515/2011/mysql [client] port=8230 socket=/ubc/icics/mss/cics515/2011/mysql/mysql.sock
Close your editor, and run the mysql demon:
Run mysqladmin to create a user:
mysqladmin -u root password 'password'
Loginto mysql as that user:
mysql -uroot -ppassword
At this point the mysql prompt will appear and you can input the database described below.
To test this works, insert an author
INSERT INTO `discussion`.`author`(`authorName` ,`email` ,`password` ,`rating`) VALUES('Ben', 'firstname.lastname@example.org', 'password', '0');
Show the authors, notice the auto increment if the authorID works too:
select * from author;
Now, create a test PHP script to show the data via web interface:
cd ~ mkdir public_html chmod 755 public_html cd public html touch discussionTest.php chmod 755 discussionTest.php nano discussionTest.php
Database Latest Deliverable
Discussion Forum Database Schema
create database Discussion;
use database Discussion;
CREATE TABLE `Discussion`.`Author` ( `authorID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY , `authorName` VARCHAR( 25 ) NOT NULL , `email` VARCHAR( 25 ) NOT NULL , `password` VARCHAR( 50 ) NOT NULL , `rating` INT NOT NULL DEFAULT '0', UNIQUE ( `email` ));
CREATE TABLE `Discussion`.`topic` ( `topicID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY , `title` VARCHAR( 50 ) NOT NULL , `content` TEXT NOT NULL , `rating` INT NOT NULL DEFAULT '0', `authorID` INT NOT NULL , `date_created` DATE NOT NULL , `date_edited` DATE NOT NULL, FOREIGN KEY (authorID) REFERENCES author(authorID) );
CREATE TABLE `Discussion`.`post`( `PostID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY , `postReplyID` INT, `content` VARCHAR( 1000 ) NOT NULL , `rating` INT NOT NULL , `authorID` INT NOT NULL , `topicID` INT NOT NULL , `date_posted` DATE NOT NULL, FOREIGN KEY (authorID) REFERENCES author(authorID), FOREIGN KEY (topicID) REFERENCES topic(topicID) );
CREATE TABLE `Discussion`.`keywords`( `keyword` VARCHAR( 25 ) NOT NULL , `topicID` INT NOT NULL , PRIMARY KEY ( `keyword` , `topicID` ), FOREIGN KEY (topicID) REFERENCES topic(topicID) );
KEYWORD table, code level business logic implementation suggestions by the team;
follow the below given links to learn