-- Database schema which goes with the DBeg1 backend. -- By Richard W.M. Jones. -- Create an example SQL database. This database supports hierarchical -- file storage and multiple users. It currently lacks quotas, permissions, -- timestamps and other high-end features (all of these features and more -- have, however, been implemented in the schoolmaster.net database, so -- they are possible with a little work). -- To run: -- -- createdb ftp -- psql ftp < eg1.sql -- -- This will DELETE any existing data in your FTP database file store!! -- This version should work with Postgres 6.3. -- Remove any old tables, indexes, sequences, etc. drop sequence files_id_seq; drop index files_id_key; drop index files_name_idx; drop sequence directories_id_seq; drop index directories_id_key; drop index directories_name_idx; drop sequence users_id_seq; drop index users_id_key; drop index users_username_idx; drop table files; drop table directories; drop table users; -- Create new tables. create sequence files_id_seq; create table files ( -- id serial, id int4 default nextval ('files_id_seq'), dir_id int4 not null, -- references directories ( id ), name text not null, content oid ); create unique index files_id_key on files ( id ); create unique index files_name_idx on files ( dir_id, name ); create sequence directories_id_seq; create table directories ( -- id serial, id int4 default nextval ('directories_id_seq'), parent_id int4, -- references directories ( id ), name text not null ); create unique index directories_id_key on directories ( id ); create unique index directories_name_idx on directories ( parent_id, name ); create sequence users_id_seq; create table users ( -- id serial, id int4 default nextval ('users_id_seq'), username text not null, password text -- crypted password ); create unique index users_id_key on users ( id ); create unique index users_username_idx on users ( username ); -- Insert some test data. -- My password is '123456'. insert into users ( username, password ) values ( 'rich', 'MpU8yRWrKoWKc' ); insert into users ( username, password ) values ( 'dan', 'MpU8yRWrKoWKc' ); -- Root directory. insert into directories ( name ) values ( '' ); -- Top level directories. insert into directories ( name, parent_id ) values ( 'Home', 1 ); insert into directories ( name, parent_id ) values ( 'Website', 1 ); insert into directories ( name, parent_id ) values ( 'Private', 1 ); insert into directories ( name, parent_id ) values ( 'Test', 1 );