CREATE TABLE plm_config (
conf_key                 VARCHAR(100) NOT NULL,
conf_val                 VARCHAR(255) NOT NULL
, CONSTRAINT plm_config_pk PRIMARY KEY (conf_key)
);
CREATE TABLE plm_message_queue (
messageid                INTEGER NOT NULL,
subscriberid             INTEGER NOT NULL,
sendcomplete             SMALLINT NOT NULL DEFAULT 0,
senddatetime             DATETIME
, CONSTRAINT plm_message_queue_pk PRIMARY KEY (messageid, subscriberid), 
CONSTRAINT plm_message_fk FOREIGN KEY (messageid) REFERENCES plm_messages (messageid) ON DELETE CASCADE,
CONSTRAINT plm_subsciber_fk FOREIGN KEY (subscriberid) REFERENCES plm_subscribers (subscriberid) ON DELETE CASCADE
);
CREATE TABLE plm_messages (
messageid                INTEGER NOT NULL ,
subject                  VARCHAR(255) NOT NULL,
content                  LONGTEXT,
contentplain             LONGTEXT,
createdatetime           DATETIME,
onhold                   SMALLINT DEFAULT 0
, constraint plm_messages_pk PRIMARY KEY (messageid)
);
CREATE TABLE plm_sub_queue (
subqueueid               INTEGER NOT NULL ,
keyhash                  VARCHAR(32),
data                     LONGTEXT,
action                   VARCHAR(1),
queuedatetime            DATETIME
, CONSTRAINT plm_sub_queue_pk PRIMARY KEY (subqueueid)
);
CREATE TABLE plm_subscribers (
subscriberid             INTEGER NOT NULL ,
emailaddr                VARCHAR(250) NOT NULL,
firstname                VARCHAR(100) NOT NULL,
lastname                 VARCHAR(100),
userfield1               VARCHAR(200),
userfield2               VARCHAR(200),
createdatetime           DATETIME,
pending                  SMALLINT DEFAULT 1
, CONSTRAINT plm_subscribers_pk PRIMARY KEY (subscriberid)
);
CREATE TABLE plm_users (
userid                   INTEGER NOT NULL ,
username                 VARCHAR(100) NOT NULL,
password                 VARCHAR(32)
, CONSTRAINT plm_users_pk PRIMARY KEY (userid)
);
CREATE UNIQUE INDEX plm_sub_queue_keyhash ON plm_sub_queue (keyhash);
CREATE UNIQUE INDEX plm_subscribers_emailaddr ON plm_subscribers (emailaddr);
CREATE UNIQUE INDEX plm_users_username ON plm_users (username);
CREATE INDEX plm_users_userpass ON plm_users (username, password);