This shows you the differences between two versions of the page.
Next revision | Previous revisionNext revisionBoth sides next revision | ||
sqlschema [2007/01/24 09:39] – amatubu | sqlschema [2008/02/08 18:49] – external edit 127.0.0.1 | ||
---|---|---|---|
Line 88: | Line 88: | ||
create table users ( id integer primary key, -- unique ID for this user | create table users ( id integer primary key, -- unique ID for this user | ||
- | < | + | |
password varchar(255), | password varchar(255), | ||
unique (name) | unique (name) | ||
- | );</ | + | ); |
-- --------------------------------------------------------------------------------------------- | -- --------------------------------------------------------------------------------------------- | ||
Line 104: | Line 104: | ||
create table buckets( id integer primary key, -- unique ID for this bucket | create table buckets( id integer primary key, -- unique ID for this bucket | ||
- | < | + | userid integer, |
-- the users table | -- the users table | ||
name varchar(255), | name varchar(255), | ||
Line 111: | Line 111: | ||
| | ||
-- with the same name | -- with the same name | ||
- | );</ | + | ); |
-- --------------------------------------------------------------------------------------------- | -- --------------------------------------------------------------------------------------------- | ||
Line 123: | Line 123: | ||
create table words( | create table words( | ||
- | < | + | word varchar(255), |
| | ||
- | );</ | + | ); |
-- --------------------------------------------------------------------------------------------- | -- --------------------------------------------------------------------------------------------- | ||
Line 135: | Line 135: | ||
create table matrix( id integer primary key, -- unique ID for this entry | create table matrix( id integer primary key, -- unique ID for this entry | ||
- | < | + | |
bucketid integer, | bucketid integer, | ||
times integer, | times integer, | ||
Line 142: | Line 142: | ||
-- or written | -- or written | ||
unique (wordid, bucketid) -- each word appears once in a bucket | unique (wordid, bucketid) -- each word appears once in a bucket | ||
- | );</ | + | ); |
-- --------------------------------------------------------------------------------------------- | -- --------------------------------------------------------------------------------------------- | ||
Line 156: | Line 156: | ||
create table user_template( id integer primary key, -- unique ID for this entry | create table user_template( id integer primary key, -- unique ID for this entry | ||
- | < | + | name varchar(255), |
-- parameter | -- parameter | ||
def varchar(255), | def varchar(255), | ||
-- the parameter | -- the parameter | ||
| | ||
- | );</ | + | ); |
-- --------------------------------------------------------------------------------------------- | -- --------------------------------------------------------------------------------------------- | ||
Line 171: | Line 171: | ||
create table user_params( id integer primary key, -- unique ID for this | create table user_params( id integer primary key, -- unique ID for this | ||
- | < | + | |
| | ||
utid integer, | utid integer, | ||
-- user_template | -- user_template | ||
- | val varchar(255), | + | val varchar(255), |
-- parameter | -- parameter | ||
- | < | + | unique (userid, utid) -- each user has just one |
-- instance of each parameter | -- instance of each parameter | ||
- | < | + | ); |
-- --------------------------------------------------------------------------------------------- | -- --------------------------------------------------------------------------------------------- | ||
Line 190: | Line 190: | ||
create table bucket_template( id integer primary key, -- unique ID for this entry | create table bucket_template( id integer primary key, -- unique ID for this entry | ||
- | < | + | name varchar(255), |
-- parameter | -- parameter | ||
def varchar(255), | def varchar(255), | ||
-- the parameter | -- the parameter | ||
| | ||
- | );</ | + | ); |
-- --------------------------------------------------------------------------------------------- | -- --------------------------------------------------------------------------------------------- | ||
Line 205: | Line 205: | ||
create table bucket_params( id integer primary key, -- unique ID for this | create table bucket_params( id integer primary key, -- unique ID for this | ||
- | < | + | |
| | ||
btid integer, | btid integer, | ||
-- bucket_template | -- bucket_template | ||
- | val varchar(255), | + | val varchar(255), |
-- parameter | -- parameter | ||
- | < | + | unique (bucketid, btid) -- each bucket has just one |
-- instance of each parameter | -- instance of each parameter | ||
- | < | + | ); |
-- --------------------------------------------------------------------------------------------- | -- --------------------------------------------------------------------------------------------- | ||
Line 222: | Line 222: | ||
create table magnet_types( id integer primary key, -- unique ID for this entry | create table magnet_types( id integer primary key, -- unique ID for this entry | ||
- | < | + | |
-- (e.g. from) | -- (e.g. from) | ||
header varchar(255), | header varchar(255), | ||
unique (mtype) | unique (mtype) | ||
- | );</ | + | ); |
-- --------------------------------------------------------------------------------------------- | -- --------------------------------------------------------------------------------------------- | ||
Line 236: | Line 236: | ||
create table magnets( id integer primary key, -- unique ID for this entry | create table magnets( id integer primary key, -- unique ID for this entry | ||
- | < | + | bucketid integer, |
mtid integer, | mtid integer, | ||
val varchar(255), | val varchar(255), | ||
| | ||
seq int -- used to set the order of magnets | seq int -- used to set the order of magnets | ||
- | );</ | + | ); |
-- MySQL SPECIFIC | -- MySQL SPECIFIC | ||
Line 277: | Line 277: | ||
create trigger delete_bucket delete on buckets | create trigger delete_bucket delete on buckets | ||
- | < | + | |
delete from matrix where bucketid = old.id; | delete from matrix where bucketid = old.id; | ||
delete from magnets where bucketid = old.id; | delete from magnets where bucketid = old.id; | ||
delete from bucket_params where bucketid = old.id; | delete from bucket_params where bucketid = old.id; | ||
- | end;</ | + | end; |
-- --------------------------------------------------------------------------------------------- | -- --------------------------------------------------------------------------------------------- | ||
Line 292: | Line 292: | ||
create trigger delete_user delete on users | create trigger delete_user delete on users | ||
- | < | + | |
delete from buckets where userid = old.id; | delete from buckets where userid = old.id; | ||
delete from user_params where userid = old.id; | delete from user_params where userid = old.id; | ||
- | end;</ | + | end; |
-- --------------------------------------------------------------------------------------------- | -- --------------------------------------------------------------------------------------------- | ||
Line 305: | Line 305: | ||
create trigger delete_magnet_type delete on magnet_types | create trigger delete_magnet_type delete on magnet_types | ||
- | < | + | |
delete from magnet where mtid = old.id; | delete from magnet where mtid = old.id; | ||
- | end;</ | + | end; |
-- --------------------------------------------------------------------------------------------- | -- --------------------------------------------------------------------------------------------- | ||
Line 316: | Line 316: | ||
create trigger delete_user_template delete on user_template | create trigger delete_user_template delete on user_template | ||
- | < | + | |
delete from user_params where utid = old.id; | delete from user_params where utid = old.id; | ||
- | end;</ | + | end; |
-- --------------------------------------------------------------------------------------------- | -- --------------------------------------------------------------------------------------------- | ||
Line 327: | Line 327: | ||
create trigger delete_bucket_template delete on bucket_template | create trigger delete_bucket_template delete on bucket_template | ||
- | < | + | |
delete from bucket_params where btid = old.id; | delete from bucket_params where btid = old.id; | ||
- | end;</ | + | end; |
-- Default data | -- Default data |
Should you find anything in the documentation that is incomplete, unclear, outdated or just plain wrong, please let us know and leave a note in the Documentation Forum.