?

Log in

Online Creativness [entries|archive|friends|userinfo]
Online Creativness

[ userinfo | livejournal userinfo ]
[ archive | journal archive ]

Changed SQL [Aug. 17th, 2002|10:22 pm]
Online Creativness

tmtl
use online_create;

DROP TABLE IF EXISTS stories;
DROP TABLE IF EXISTS poems;
DROP TABLE IF EXISTS lyrics;
DROP TABLE IF EXISTS types;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS styles;

CREATE TABLE stories (
storyid smallint(5) unsigned NOT NULL auto_increment,
uid smallint(5) unsigned NOT NULL default '0',
security_type enum('0','1','2') NOT NULL default '0',
story_title varchar(40) default NULL,
story_text text,
story_written_by varchar(40) default NULL,
story_deleted enum('0','1') NOT NULL default '0',
story_a_comments enum('0','1') NOT NULL default '0',
story_num_comments tinyint(4) default '0',
PRIMARY KEY (storyid)
) TYPE=MyISAM;

CREATE TABLE poems (
poemid smallint(5) unsigned NOT NULL auto_increment,
uid smallint(5) unsigned NOT NULL default '0',
security_type enum('0','1','2') NOT NULL default '0',
poem_title varchar(40) default NULL,
poem_text text,
poem_written_by varchar(40) default NULL,
poem_deleted enum('0','1') NOT NULL default '0',
poem_a_comments enum('0','1') NOT NULL default '0',
poem_num_comments tinyint(4) default '0',
PRIMARY KEY (poemid)
) TYPE=MyISAM;

CREATE TABLE lyrics (
lyricid smallint(5) unsigned NOT NULL auto_increment,
uid smallint(5) unsigned NOT NULL default '0',
security_type enum('0','1','2') NOT NULL default '0',
lyric_title varchar(40) default NULL,
lyric_text text,
lyric_written_by varchar(40) default NULL,
lyric_from_album varchar(40) default NULL,
lyric_deleted enum('0','1') NOT NULL default '0',
lyric_a_comments enum('0','1') NOT NULL default '0',
lyric_num_comments tinyint(4) default '0',
PRIMARY KEY (lyricid)
) TYPE=MyISAM;

CREATE TABLE types (
typeid smallint(5) unsigned NOT NULL auto_increment,
typename varchar(20) NOT NULL default 'Empty data type',
PRIMARY KEY (typeid)
) TYPE=MyISAM;

# I'm not sure why I created this "types" table ...

CREATE TABLE users (
userid smallint(5) unsigned NOT NULL auto_increment,
username varchar(20) default NULL,
firstname varchar(20) default NULL,
lastname varchar(20) default NULL,
password varchar(15) default NULL,
account_type tinyint(3) unsigned NOT NULL default '1',
email varchar(40) default NULL,
dob date default '2002-08-01',
confirmation varchar(10) default NULL,
confirmed enum('0','1') default '0',
deleted enum('0','1') default '0',
default_style smallint(5) default '1',
valid varchar(1) default '1',
website varchar(100) default '',
bio text,
hasbio enum('y','n') default 'n',
showem enum('y','n') default 'y',
lastmod datetime default '2002-08-17 17:20:00',
PRIMARY KEY (userid),
UNIQUE KEY username (username)
) TYPE=MyISAM;

# There was a reason for "valid" and "hasbio" but I forget what

CREATE TABLE styles (
styleid smallint(5) unsigned NOT NULL auto_increment default '0',
styleowner smallint(5) unsigned NOT NULL default '0',
styledef text,
titles text,
texts text,
written text,
uploaded text,
PRIMARY KEY (styleid)
);

This table isn't fully used yet
linkpost comment

(no subject) [Aug. 1st, 2002|09:29 pm]
Online Creativness

tmtl
[mood |boredbored]

The begenings of a database

Obv, there aren't enough tables of typename yet

use online_create;

DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS types;
DROP TABLE IF EXISTS stories;

CREATE TABLE users (
user_ID SMALLINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT DEFAULT '0',
username VARCHAR(20) UNIQUE,
firstname VARCHAR(20),
lastname VARCHAR(20),
password VARCHAR(15),
account_type TINYINT UNSIGNED NOT NULL DEFAULT '1',
email VARCHAR (20),
dob DATE DEFAULT '2002-08-01',
confirmation VARCHAR (10),
confirmed ENUM ('y','n') DEFAULT 'n',
deleted ENUM('y','n') DEFAULT 'n'
);

CREATE TABLE types (
type_ID SMALLINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT DEFAULT '0',
typename VARCHAR(20) NOT NULL DEFAULT 'Empty data type'
);

CREATE TABLE stories (
story_ID SMALLINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT DEFAULT '0',
user_ID SMALLINT UNSIGNED NOT NULL DEFAULT '0',
security_type ENUM ('Public','Logged-In','Private') NOT NULL DEFAULT 'Public',
story_title VARCHAR(40),
story_text text,
story_written_by VARCHAR(40),
story_deleted ENUM('y','n') NOT NULL DEFAULT 'n',
story_a_comments ENUM('y','n') NOT NULL DEFAULT 'n',
story_num_comments TINYINT DEFAULT '0'
);

INSERT INTO users VALUES ('','BenQuick','Ben','Quick','password123','','ben@domain.com','1983-01-05','cOnFiRmEd','y','n');
INSERT INTO users VALUES ('','TMTL','Ben','Quick','p123','','ben@domain.co.uk','1983-01-05','passed','y','n');
INSERT INTO types VALUES ('','Stories');
INSERT INTO types VALUES ('','Lyrics');
INSERT INTO types VALUES ('','Poems');
INSERT INTO stories VALUES ('','1','','TITLE','My story...','Ben Quick','','','');
linkpost comment

Welcome! [Aug. 1st, 2002|12:14 am]
Online Creativness

tmtl
[mood |thoughtfulthoughtful]

Read the following (It's also in userinfo) then discuss

The chosen database - MySQL
The chosen language - Perl

My idea, a creativness server. Sounds weird, I know. Here's the URL ideas

http://www.domain.com/
- Welcome screen, New user sign up, login, manage account etc
http://public.domain.com/
- Welcome screen, New user sign up, login, manage account etc
http://my.domain.com/
- Welcome screen, New user sign up, login, manage account etc

http://my.domain.com/$type/
- Only availabe once logged on (Otherwise prompt for logon)
- Allows $user to edit $entries of $type (Type being, type of submission - Poem, Story etc)

http://public.domain.com/$type/
- Allows searching of $entries that match $type, as long as $entry is public/available

http://public.domain.com/$type/$ID
- $user's entry ($ID) of $type (Viewable only if security allows, otherwise prompts for logon)

http://my.domain.com/$type/$ID
- Allow editing of $user's entry ($ID) if logged on and the person who made the entry, else just show entry(If security allows)

http://public.domain.com/$user
- View all of $user's entries (Publically viewable ones, or security setting permitting)

http://public.domain.com/$user/$type
- View all of $user's entries of $type (Publically viewable ones, or security setting permitting)


$type is defined by, essentially, me (It will be gathered from the 'types' table, once populated). Hence my 'How are you creative' poll (http://www.livejournal.com/poll/?id=50428)
The only three $type's I can currently think of are 'Poems' 'Stories' and 'Lyrics'. You may think that images are creative - And they are. But, for that, go here http://www.livejournal.com/users/fotobilder

So, I sign up to this website, and decide to submit a few short stories. But, that's all I submit
I log on and goto http://my.domain.com/stories/ and I can edit my submissions. If however, I goto http://my.domain.com/poems/, I'll be slapped and told I haven't submitted any poems yet

Depending on how complex http://public.domain.com/$user is to generate, it may be down to the user type (E.G. you've got more priv.'s if you've submitted a lot of work, or whatever)
Generally people will link to stuff by http://public.domain.com/$type/$ID though, I'd imagine

The complex bit for me, if the design of the database. I've never designed a database before, so it could be tricky
;-)

The general idea for the database goes thusly

- Users table
*UserID - Auto incrementing number, thus unique. Something that's not really relevent, but can be used simply to find out how many people have joined up
*Username - A unique name, keyfield (Simlar to a username on LJ, TMTL for instance)
*Realname - The users real name (No way to confirm, obv). This'd be for e-mailing them, I'd guess
*Password - The users password, maybe hashed. I'm not sure. Hashing it would create issues with lost passwords, as the only hashes I know are one way ones
*Account_type - Just for scalability. Not currently used, though (Account type of x can\cannot perform action y)
*E-Mail - So the user can be contacted
*DOB - Just because it seems right
*Random_confirmation_junk - Just a few randomly picked chars. so the user can enter them on a form (Once only) to confirm the valid e-mail address. Account isn't usable until e-mail address is confirmed
*Confirmed - Has the 'Random_confirmation_junk' been confirmed? If yes, flag account as confirmed (And therefore active)
*Deleted - Has this account been deleted? I wouldn't actually remove stuff, just disable it

The only stuff from this table that'll be public is the username (so people can goto http://public.domain.com/$user or search on stuff, and provide a user as a result etc etc)

- Types table
*TypeID - Auto incrementing number, thus unique
*Typename - For instance 'stories' and 'lyrics' are two valid entries for instance

Typename would be public, so people knew what they could submit

- TypeEntriesX table(s)
Multipul tables with the name TypeEntriesTypeID. For instance TypeEntries1 would be all the entries for 'stroies' (Assuming that TypeID 1 was for 'stories')
The contents would all be the same, though

*EntryID - Auto incrementing number, thus unique. This would be referenced via http://public.domain.com/$type/$ID (Assuming public entry)
*UserID - The user id from the Users table (Taken the ID, as they can change their name, if they want to), so you know who's submitted what
*Security - This is simply (For now) 0, 1 or 2. 0 = Private entry (But would it be needed?). 1 = Only viewable to logged in users and 2 = public access (The default one)
*Title - Not required, but entered if the entry has a title (Useful only for lyrics, I thought)
*Entry - The actual body of the entry
*Written_by - May sound daft, but not every submitted would be written by the person who submitted it
*Deleted - Has this entry ($ID) of $type been deleted? If so, flag it as so. Just disable it though, don't actually delete anything
*Allow_comments - A simple 0 or 1. Not sure if this is to be implemented, as I'm not sure how to tackle comments, yet. If comments are something that'd be implemented, then...
*Num_comments - Number of comments received for this $type$ID

This is where you come in
- The visuals. I can never make things visually impressive. I hope you can!
-The technicals. As I say, I've never designed a database before, so I could be waaaay off with my ideas for it. I have therefore never connected to a database via Perl either
link3 comments|post comment

navigation
[ viewing | most recent entries ]