-- 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 works with Postgres 6.4. Not tested on other platforms. -- 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 table files ( id serial, dir_id int4 not null references directories ( id ), name text not null, content oid ); create unique index files_name_idx on files ( dir_id, name ); create table directories ( id serial, parent_id int4 references directories ( id ), name text not null ); create unique index directories_name_idx on directories ( parent_id, name ); create table users ( id serial, username text not null, password text -- crypted password ); 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 );