tables.sql

CREATE TABLE USERS (id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,name VARCHAR(255) NOT NULL, password VARCHAR(255) NOT NULL,ip VARCHAR(255) NOT NULL,last_login timestamp(12) NOT NULL, PRIMARY KEY (id));
CREATE UNIQUE INDEX XUSERS ON USERS (name ASC, password ASC);


CREATE TABLE MESSAGES ( id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY, message CLOB NOT NULL, from_handle VARCHAR(255) NOT NULL, post_date TIMESTAMP(12) NOT NULL, PRIMARY KEY (id));


CREATE TABLE UNDELIVERED ( user_id INTEGER NOT NULL, message_id INTEGER NOT NULL, FOREIGN KEY (USER_ID) REFERENCES USERS (ID) ON DELETE RESTRICT, FOREIGN KEY (MESSAGE_ID) REFERENCES MESSAGES (ID) ON DELETE RESTRICT);