Eric Thiebaut - 2006-03-23

You can use this script to use postgreSql. Tested on Windows.

Eric

/* First, create a database.  You might name it "oqdb".*/
/* Then, execute all SQL below to create and populate tables.*/

/* Make sure to *CHANGE THE PASSWORD* of the built-in user 'root'!*/
/*               ====== === ========*/

/**/
/* Table structure for table 'Messages'*/
/**/
CREATE TABLE messages (
  TopicID integer DEFAULT 0 NOT NULL,
  MessageID integer DEFAULT 0 NOT NULL,
  ReceiveDate timestamp without time zone,
  MessageSize integer,
  MessageData text,
  MessageSender integer,
  Subject character varying(100),
  Headers text
) WITHOUT OIDS;

/**/
/* Table structure for table 'Subscriptions'*/
/**/
CREATE TABLE subscriptions (
  SubscriptionID integer DEFAULT 0 NOT NULL,
  UserID integer DEFAULT 0 NOT NULL,
  TopicID integer DEFAULT 0 NOT NULL,
  LastSeen integer DEFAULT 0 NOT NULL,
  Flags smallint DEFAULT 0::smallint NOT NULL,
  MaxDefault integer DEFAULT -1 NOT NULL
) WITHOUT OIDS;

/**/
/* Table structure for table 'TopicPaths'*/
/**/
CREATE TABLE topicpaths (
  PathID integer DEFAULT 0 NOT NULL,
  TopicID integer DEFAULT 0 NOT NULL,
  Path character varying(255) DEFAULT ''::character varying NOT NULL
) WITHOUT OIDS;

/**/
/* Table structure for table 'Topics'*/
/**/
/* Only topic owner and administrators can currently write.*/
/* ReaFlags: bitwise combination of */
/* 1=owner, 2=admin, 4=ACL (to be done), 8=everyone*/
/**/

CREATE TABLE topics (
  Title character varying(200),
  "Owner" integer,
  StorageWindow integer,
  SubscribeWindow integer,
  LookbackWindow integer,
  TopicID integer DEFAULT 0 NOT NULL,
  Persistent smallint DEFAULT 0::smallint NOT NULL,
  LastMessageID integer DEFAULT 0 NOT NULL,
  readFlags smallint DEFAULT 3::smallint
) WITHOUT OIDS;

/**/
/* Table structure for table 'Users'*/
/**/
/* utype is the User Type: 0=general, 1=administrator*/
/**/
CREATE TABLE users (
  Username character varying(60),
  upassword character varying(40),
  UserID integer DEFAULT 0 NOT NULL,
  utype smallint DEFAULT 0::smallint
) WITHOUT OIDS;

/**/
/* Dumping data for table 'Subscriptions'*/
/**/

INSERT INTO Subscriptions VALUES (1,1,101,0,0,-1);

/**/
/* Dumping data for table 'TopicPaths'*/
/**/

INSERT INTO TopicPaths VALUES (1,101,'/system/stats/connections/total');
INSERT INTO TopicPaths VALUES (2,102,'/system/stats/connections/current');
INSERT INTO TopicPaths VALUES (3,755,'/test/stuff');

/**/
/* Dumping data for table 'Topics'*/
/**/

INSERT INTO Topics VALUES ('Total User Connections',1,NULL,NULL,NULL,101,0,0,11);
INSERT INTO Topics VALUES ('Current User Connections',1,NULL,NULL,NULL,102,0,0,11);
INSERT INTO Topics VALUES ('Test Stuff',1,NULL,NULL,NULL,755,1,0,11);

/**/
/* Dumping data for table 'Users'*/
/**/

INSERT INTO Users VALUES ('root','password',1,1);

/* Make sure to *CHANGE THE PASSWORD* of the built-in user 'root'!*/
/*               ====== === ========*/

/**/
/* Create Indexes and Primary Keys*/
/**/

ALTER TABLE ONLY subscriptions
  ADD CONSTRAINT "SubscriptionIDIndex" PRIMARY KEY (SubscriptionID);
ALTER TABLE ONLY topics
  ADD CONSTRAINT "TopicIDIndex" PRIMARY KEY (TopicID);
ALTER TABLE ONLY users
  ADD CONSTRAINT "UserIDIndex" PRIMARY KEY (UserID);
ALTER TABLE ONLY topicpaths
  ADD CONSTRAINT "pathIDindex" PRIMARY KEY (PathID);

CREATE INDEX "UsernameIndex" ON users USING btree (Username);
CREATE INDEX "pathIndex" ON topicpaths USING btree (Path);
CREATE INDEX "topicMsgIndex" ON messages USING btree (TopicID, MessageID);