-- Please note that we used smallint for types bool. Not all the databases use type
-- boolean. So we wanted to be compatible with others.
--
-- If you don't want to get any errors comment out the DROP table commands
-- for the first time you run the script.
DROP SEQUENCE accountid_seq, actionid_seq, groupid_seq, activityid_seq;
DROP TABLE accounts, groups, actions, groupaccounts, groupactions, log, activity, configuration;
CREATE SEQUENCE "accountid_seq" start 2 increment 1 maxvalue 2147483647 minvalue 2 cache 1 ;
CREATE SEQUENCE "actionid_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;
CREATE SEQUENCE "groupid_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;
CREATE SEQUENCE "activityid_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;
CREATE TABLE "accounts" (
"accountid" integer DEFAULT nextval('accountid_seq'::text) NOT NULL,
"firstname" text,
"lastname" text,
"initials" text,
"username" text NOT NULL UNIQUE,
"password" text,
"email" text NOT NULL UNIQUE,
"hintquestion" text,
"hintanswer" text,
"expired" smallint,
"expireddate" integer,
"tries" integer,
"lasttrieddate" integer
);
CREATE TABLE "groups" (
"groupid" integer DEFAULT nextval('groupid_seq'::text) NOT NULL,
"groupname" text NOT NULL UNIQUE,
"hierarchy" integer NOT NULL
);
CREATE TABLE "actions" (
"actionid" integer DEFAULT nextval('actionid_seq'::text) NOT NULL,
"actionname" text NOT NULL UNIQUE
);
CREATE TABLE "groupaccounts" (
"groupid" integer NOT NULL,
"accountid" integer NOT NULL
);
CREATE TABLE "groupactions" (
"groupid" integer NOT NULL,
"actionid" integer NOT NULL
);
CREATE TABLE "log" (
"timestamp" integer,
"ip" varchar(16) NOT NULL DEFAULT '----------------',
"accountid" integer,
"username" text,
"activityid" int
);
CREATE TABLE "activity" (
"activityid" integer DEFAULT nextval('activityid_seq'::text) NOT NULL,
"description" text NOT NULL UNIQUE
);
CREATE TABLE "configuration" (
"md5" smallint DEFAULT 1 NOT NULL,
"bad_attempts_max" int DEFAULT 5 NOT NULL,
"bad_attempts_wait" int DEFAULT 300 NOT NULL,
"log_activities" smallint DEFAULT 1 NOT NULL,
"timeout" int DEFAULT 900 NOT NULL,
"error_reporting" smallint DEFAULT 1 NOT NULL,
"stylesheet" text NOT NULL
);
insert into configuration (md5,bad_attempts_max,bad_attempts_wait,log_activities,timeout,error_reporting,stylesheet) values(1,5,300,1,900,1,'Corona');
insert into accounts (accountid,firstname,lastname,initials,username,password,email,hintquestion,hintanswer,expired,expireddate,tries,lasttrieddate) values(1,'administrator','admin','aa','admin','5ebe208eab7294ecd0e0f690d2a6ee69','admin@mydomain.com','how are you?','very well',0,null,0,null);
insert into accounts (accountid,firstname,lastname,initials,username,password,email,hintquestion,hintanswer,expired,expireddate,tries,lasttrieddate) values(nextval('accountid_seq'),'test user','brown','tb','test','5ebe0e0f08eab2294ecd7690d2a6ee69','test@mydomain.com','what is your pets name','black',0,null,0,null);
insert into accounts (accountid,firstname,lastname,initials,username,password,email,hintquestion,hintanswer,expired,expireddate,tries,lasttrieddate) values(nextval('accountid_seq'),'Joe','Blow','jb','joe','5144abbeb84239f038276f8e36b2251d','joe@mydomain.com','what is my last name','blow',0,null,0,null);
insert into groups (groupid,groupname,hierarchy) VALUES(nextval('groupid_seq'),'Admins',1);
insert into groups (groupid,groupname,hierarchy) VALUES(nextval('groupid_seq'),'Power Users',2);
insert into groups (groupid,groupname,hierarchy) VALUES(nextval('groupid_seq'),'Users',3);
insert into groups (groupid,groupname,hierarchy) VALUES(nextval('groupid_seq'),'Guests',4);
insert into groupaccounts (groupid,accountid) values(1,1);
insert into groupaccounts (groupid,accountid) values(2,2);
insert into actions (actionid,actionname) values(nextval('actionid_seq'),'Show Admin Menu');
insert into actions (actionid,actionname) values(nextval('actionid_seq'),'Add Action');
insert into actions (actionid,actionname) values(nextval('actionid_seq'),'Delete Action');
insert into actions (actionid,actionname) values(nextval('actionid_seq'),'Modify Action');
insert into actions (actionid,actionname) values(nextval('actionid_seq'),'View Action');
insert into actions (actionid,actionname) values(nextval('actionid_seq'),'Add Group');
insert into actions (actionid,actionname) values(nextval('actionid_seq'),'Delete Group');
insert into actions (actionid,actionname) values(nextval('actionid_seq'),'Modify Group');
insert into actions (actionid,actionname) values(nextval('actionid_seq'),'View Group');
insert into actions (actionid,actionname) values(nextval('actionid_seq'),'Add Account');
insert into actions (actionid,actionname) values(nextval('actionid_seq'),'Delete Account');
insert into actions (actionid,actionname) values(nextval('actionid_seq'),'Modify Account');
insert into actions (actionid,actionname) values(nextval('actionid_seq'),'View Account');
insert into actions (actionid,actionname) values(nextval('actionid_seq'),'Add Activity');
insert into actions (actionid,actionname) values(nextval('actionid_seq'),'Delete Activity');
insert into actions (actionid,actionname) values(nextval('actionid_seq'),'Modify Activity');
insert into actions (actionid,actionname) values(nextval('actionid_seq'),'View Activity');
insert into actions (actionid,actionname) values(nextval('actionid_seq'),'View Log');
insert into actions (actionid,actionname) values(nextval('actionid_seq'),'Modify Config');
insert into actions (actionid,actionname) values(nextval('actionid_seq'),'Insert Group Accounts');
insert into actions (actionid,actionname) values(nextval('actionid_seq'),'Delete Group Accounts');
insert into actions (actionid,actionname) values(nextval('actionid_seq'),'View MD5');
insert into activity (activityid,description) values(nextval('activityid_seq'),'Failed Login');
insert into activity (activityid,description) values(nextval('activityid_seq'),'Failed Admin Login');
insert into activity (activityid,description) values(nextval('activityid_seq'),'Successful Login');
insert into groupactions (groupid,actionid) values(1,1);
insert into groupactions (groupid,actionid) values(1,2);
insert into groupactions (groupid,actionid) values(1,3);
insert into groupactions (groupid,actionid) values(1,4);
insert into groupactions (groupid,actionid) values(1,5);
insert into groupactions (groupid,actionid) values(1,6);
insert into groupactions (groupid,actionid) values(1,7);
insert into groupactions (groupid,actionid) values(1,8);
insert into groupactions (groupid,actionid) values(1,9);
insert into groupactions (groupid,actionid) values(1,10);
insert into groupactions (groupid,actionid) values(1,11);
insert into groupactions (groupid,actionid) values(1,12);
insert into groupactions (groupid,actionid) values(1,13);
insert into groupactions (groupid,actionid) values(1,14);
insert into groupactions (groupid,actionid) values(1,15);
insert into groupactions (groupid,actionid) values(1,16);
insert into groupactions (groupid,actionid) values(1,17);
insert into groupactions (groupid,actionid) values(1,18);
insert into groupactions (groupid,actionid) values(1,19);
insert into groupactions (groupid,actionid) values(1,20);
insert into groupactions (groupid,actionid) values(1,21);
insert into groupactions (groupid,actionid) values(1,22);
insert into groupactions (groupid,actionid) values(2,1);
insert into groupactions (groupid,actionid) values(2,2);
insert into groupactions (groupid,actionid) values(2,3);
insert into groupactions (groupid,actionid) values(2,4);
insert into groupactions (groupid,actionid) values(2,5);
insert into groupactions (groupid,actionid) values(2,18);
insert into log (timestamp,ip,accountid,username,activityid) values(1022640120,'64.27.112.77',2,'',1);
insert into log (timestamp,ip,accountid,username,activityid) values(1022630120,'10.8.1.111',2,'test',2);
|
Stumble It!
Bookmark in del.icio.us
Download
