use twig
go

CREATE TABLE twig_accounts (
   	id 		NUMERIC IDENTITY NOT NULL PRIMARY KEY,
   	username 	varchar(128) NOT NULL,
   	password 	varchar(128),
	SID		numeric,
   	onnow		numeric,
   	expire		numeric,
   	laston		numeric
)
go


CREATE INDEX twig_accounts_id_index ON twig_accounts (id, username)
go


CREATE TABLE twig_acls (
   	SID 		int DEFAULT '0' NOT NULL PRIMARY KEY,
   	ident	 	varchar(128) NOT NULL,
   	alevel 		int DEFAULT '0' NOT NULL
)
go

CREATE INDEX twig_acls_sid_index ON twig_acls (SID)
go
CREATE INDEX twig_acls_ident_index ON twig_acls (ident)
go

CREATE TABLE twig_acl_groups (
   	SID 		numeric DEFAULT '0' NOT NULL,
   	groupname 	varchar(64) NOT NULL PRIMARY KEY,
   	username 	varchar(128) NOT NULL,
)
go

CREATE INDEX twig_acl_groups_u_idx ON twig_acl_groups (username)
go

CREATE TABLE twig_announce (
   	id 		NUMERIC IDENTITY NOT NULL PRIMARY KEY,
   	thedate 	DATETIME,
   	username 	varchar(128) NOT NULL,
	email		varchar(128),
   	subject 	varchar(128) NOT NULL,
   	announce 	TEXT NOT NULL,
   	PRIMARY KEY 	(id)
);

CREATE INDEX twig_acl_announce_g_idx ON twig_announce (id)
go

CREATE TABLE twig_bookmarks (
 	id		NUMERIC IDENTITY NOT NULL PRIMARY KEY,
 	username	VARCHAR(128),
 	thedate		DATETIME,
 	groupid		INT NOT NULL,
 	item		varchar(100),
 	description	varchar(100),
 	url		varchar(100)
)
go

CREATE INDEX twig_acl_bookmarks_u_idx ON twig_bookmarks (username)
go
CREATE INDEX twig_acl_bookmarks_g_idx ON twig_bookmarks (id)
go

CREATE TABLE twig_bookmarks_prefs (
   	id 		NUMERIC IDENTITY NOT NULL PRIMARY KEY,
   	username 	varchar(128),
   	sortby 		varchar(10) NOT NULL,
   	sortorder 	int DEFAULT '0' NOT NULL,
   	dgroup 		int DEFAULT '0' NOT NULL,
	pagesize	int DEFAULT '0' NOT NULL,
	viewgrp		varchar(10) DEFAULT '0' NOT NULL
)
go

CREATE INDEX twig_bookmarks_prefs_u_idx ON twig_bookmarks_prefs (username)
go

CREATE TABLE twig_contacts (
 	id		NUMERIC IDENTITY NOT NULL PRIMARY KEY,
 	username	VARCHAR(128),
 	thedate		DATETIME,
 	groupid		INT NOT NULL,
 	item		VARCHAR(85),
 	description	TEXT,
 	url		TEXT,
 	firstname	VARCHAR(80),
 	lastname	VARCHAR(80),
 	email		VARCHAR(70),
 	company		VARCHAR(80),
 	home_phone	VARCHAR(25),
 	work_phone	VARCHAR(25),
 	mobile		VARCHAR(25),
 	fax		VARCHAR(25),
 	address1	VARCHAR(155),
 	address2	VARCHAR(155),
 	city		VARCHAR(65),
 	state		VARCHAR(15),
 	zipcode		VARCHAR(15),
 	country		VARCHAR(65),
 	email2 		VARCHAR(70),
 	email3 		VARCHAR(70),
 	title 		VARCHAR(60),
 	pager 		VARCHAR(30),
 	ofax 		VARCHAR(30),
 	department 	VARCHAR(85),
 	office 		VARCHAR(85),
 	spouse 		VARCHAR(85),
 	bday 		VARCHAR(40),
 	oaddress1 	VARCHAR(80),
 	oaddress2 	VARCHAR(80),
 	ocity 		VARCHAR(65),
 	ozip 		VARCHAR(15),
 	ostate 		VARCHAR(15),
 	ocountry 	VARCHAR(65),
	type		INT,
	userpers	INT
)
go

CREATE INDEX twig_contacts_u_idx ON twig_contacts (username)
go
CREATE INDEX twig_contacts_g_idx ON twig_contacts (groupid)
go

CREATE TABLE twig_contacts_lists_entries (
	listid		INT DEFAULT '0' NOT NULL,
	type		INT DEFAULT '0' NOT NULL,
	item		INT DEFAULT '0' NOT NULL,
	subitem		INT
)
go

CREATE INDEX twig_contacts_lists_entries_i_idx ON twig_contacts_lists_entries (listid)
go
CREATE INDEX twig_contacts_lists_entries_t_idx ON twig_contacts_lists_entries (item)
go

CREATE TABLE twig_contacts_prefs (
   	id 		NUMERIC IDENTITY NOT NULL PRIMARY KEY,
   	username 	varchar(128),
   	sortby 		varchar(10) NOT NULL,
   	sortorder 	tinyint DEFAULT '0' NOT NULL,
   	dgroup 		int DEFAULT '0' NOT NULL,
	pagesize	int NOT NULL,
	viewgrp		varchar(10) NOT NULL
)
go

CREATE INDEX twig_contacts_prefs_u_idx ON twig_contacts_prefs (username)
go

CREATE TABLE twig_context (
   	id 		varchar(40) NOT NULL,
   	username 	varchar(128) NOT NULL,
   	context 	text NOT NULL,
   	sid 		numeric DEFAULT '0' NOT NULL,
)
go

CREATE INDEX twig_context_id_index ON twig_context (id)
go
CREATE INDEX twig_context_u_idx ON twig_context (id)
go
CREATE INDEX twig_context_sid_index ON twig_context (id)
go

CREATE TABLE twig_folders (
   	id 		NUMERIC IDENTITY NOT NULL PRIMARY KEY,
   	owner 		varchar(128) NOT NULL,
   	server 		varchar(255) NOT NULL,
   	port 		int DEFAULT '0' NOT NULL,
   	groupname 	varchar(255) NOT NULL,
   	username 	varchar(128),
   	password 	varchar(128),
   	servertype 	varchar(10),
)
go

CREATE INDEX twig_folders_owner_index ON twig_folders (owner)
go
CREATE INDEX twig_folders_groupname_index ON twig_folders (groupname)
go

CREATE TABLE twig_global_prefs (
   	id 		NUMERIC IDENTITY NOT NULL PRIMARY KEY,
   	username 	varchar(128),
   	mstyle1 	tinyint DEFAULT '0' NOT NULL,
   	mstyle2 	tinyint DEFAULT '3' NOT NULL,
   	tstyle1 	tinyint DEFAULT '5' NOT NULL,
   	language 	varchar(),
   	mailfoot	tinyint,
   	prevnext	varchar(20),
	timeformat	NUMERIC,
	stylesheet	varchar()
)
go

CREATE INDEX twig_global_prefs_u_idx ON twig_global_prefs (username)
go

CREATE TABLE twig_groups (
	id		NUMERIC IDENTITY NOT NULL PRIMARY KEY,
	owner		varchar(128),
	groupname	varchar(100),
	type		varchar(20)
)
go

CREATE INDEX twig_groups_owner_index ON twig_groups (owner)
go

CREATE TABLE twig_lhsqltable (
   	LID 		varchar(128) DEFAULT '0' NOT NULL PRIMARY KEY,
   	username 	varchar(128) NOT NULL,
   	password 	varchar(128) NOT NULL,
   	expire 		NUMERIC DEFAULT '0' NOT NULL,
)
go

CREATE TABLE twig_mail_prefs (
	id		NUMERIC IDENTITY NOT NULL PRIMARY KEY,
	username	varchar(128),
	realname	TEXT,
	signature	TEXT,
 	linewrap	TEXT,
	replyto		TEXT,
	pagesize	INT,
	sortby		varchar(10),
	sortorder	INT,
	dande		INT,
   	savesent 	INT,
   	sentfold 	TEXT,
   	draftfold 	TEXT,
	cmdline2	INT,
	newwindow	INT,
	newcontact	INT,
	numup		INT,
	mailtree 	INT,
	mtexpand 	INT,
	mtview 		varchar(255),
	mdn		INT,
	mdn_mode	INT,
	areply		INT,
	aforward	INT,
	clines		INT,
	mande		INT,
	dreturn		INT,
	fromaddress	varchar(128),
	cgroup		varchar(10)
)
go

CREATE INDEX twig_mail_prefs_u_idx ON twig_mail_prefs (username)
go

CREATE TABLE twig_main_prefs (
   	id 		NUMERIC IDENTITY NOT NULL PRIMARY KEY,
   	username 	varchar(128),
   	features 	varchar(128),
	maxmail		INT,
	maxmeetings	INT,
	maxschedule	INT,
	maxtodo		INT
)
go

CREATE INDEX twig_main_prefs_u_idx ON twig_main_prefs (username)
go

CREATE TABLE twig_meetings (
  	id 		NUMERIC IDENTITY NOT NULL PRIMARY KEY,
  	groupid 	int(11) DEFAULT '0' NOT NULL,
  	username 	varchar(128),
  	secretary	varchar(128),
  	responsible	varchar(128),
  	thedate 	timestamp(14),
  	itemstart 	timestamp(14),
  	itemstop 	timestamp(14),
  	itemdone 	timestamp(14),
  	item 		text,
  	description 	text,
  	minutes		text,
  	conclusion 	text,
  	actionitems 	text,
  	location	int DEFAULT '0' NOT NULL,
  	userlimit 	int DEFAULT '0' NOT NULL,
  	closed 		int DEFAULT '0' NOT NULL,
  	deleted 	int DEFAULT '0' NOT NULL,
  	private 	int DEFAULT '0' NOT NULL
)
go

CREATE TABLE twig_meetings_prefs (
  	id 		NUMERIC IDENTITY NOT NULL PRIMARY KEY,
  	username 	varchar(128),
  	dgroup 		int DEFAULT '0' NOT NULL,
  	mainview	int DEFAULT '0' NOT NULL,
  	pagesize	int DEFAULT '0' NOT NULL,
  	viewgrp		varchar(10) DEFAULT '0' NOT NULL
)
go

CREATE TABLE twig_meetings_registration (
  	id 		NUMERIC IDENTITY NOT NULL PRIMARY KEY,
  	username 	varchar(128),
  	eventid 	int DEFAULT '0' NOT NULL,
  	thedate		TIMESTAMP,
  	rsvp 		int DEFAULT '0' NOT NULL
)
go

CREATE TABLE twig_meetings_invitations (
  	id 		NUMERIC IDENTITY NOT NULL PRIMARY KEY,
  	username 	varchar(128),
  	eventid 	int DEFAULT '0' NOT NULL,
  	thedate 	TIMESTAMP,
  	priority	int DEFAULT '0' NOT NULL
)
go

CREATE TABLE twig_members (
	id		NUMERIC IDENTITY NOT NULL PRIMARY KEY,
	groupid		INT NOT NULL,
	username	varchar(128),
	writeperms	INT
)
go

CREATE TABLE twig_news_prefs (
   	id 		NUMERIC IDENTITY NOT NULL PRIMARY KEY,
   	username	varchar(128) NOT NULL,
   	replyto 	text NOT NULL,
   	pagesize 	int DEFAULT '0' NOT NULL,
   	newwindow 	int DEFAULT '1' NOT NULL,
	linewrap	text NOT NULL,
	realname	text NOT NULL,
	signature	text NOT NULL,
   	inline 		int
)
go

CREATE INDEX twig_news_prefs_u_idx ON twig_news_prefs (username)
go

CREATE TABLE twig_notes (
	id		NUMERIC IDENTITY NOT NULL PRIMARY KEY,
        username        VARCHAR(128) NOT NULL,
        thedate         TIMESTAMP,
        groupid         INT NOT NULL,
        item            TEXT,
        description     TEXT,
        attachname      TEXT,
	attachmime	TEXT,
        attachment      BINARY
)
go

CREATE INDEX twig_notes_username_index ON twig_notes (username)
go

CREATE TABLE twig_notes_prefs (
	id		NUMERIC DEFAULT '0' NOT NULL IDENTITY PRIMARY KEY,
	username	VARCHAR(128) NOT NULL,
	sortby		VARCHAR(10) NOT NULL,
	sortorder	INT(1) DEFAULT '0' NOT NULL,
	dgroup		INT(11) DEFAULT '0' NOT NULL,
	width		INT(11),
	height		INT(11),
	pagesize	INT(11),
	viewgrp		varchar(10)
)
go

CREATE INDEX twig_notes_prefs_username_index ON twig_notes_prefs (username)
go

CREATE TABLE twig_schedule (
 	id		NUMERIC IDENTITY NOT NULL PRIMARY KEY,
 	username	VARCHAR(128),
 	thedate		DATETIME,
 	groupid		INT NOT NULL,
 	itemstart	DATETIME,
 	itemstop	DATETIME,
 	item		TEXT,
 	description	TEXT,
	notime		INT,
	recurtype	INT,
	recurid		INT
	remind_time	DATETIME,
	remind_qty	INT,
	remind_units	VARCHAR(10),
	remind_address	VARCHAR(100),
)
go

CREATE INDEX twig_schedule_u_idx ON twig_schedule (username)
go
CREATE INDEX twig_schedule_thedate_index ON twig_schedule (thedate)
go
CREATE INDEX twig_schedule_groupid_index ON twig_schedule (groupid)
go
CREATE INDEX twig_schedule_start_index ON twig_schedule (itemstart)
go
CREATE INDEX twig_schedule_finish_index ON twig_schedule (itemstop)
go

CREATE TABLE twig_schedule_recurring (
	id		NUMERIC IDENTITY NOT NULL PRIMARY KEY,
	schid		INT,
	recurtype	INT DEFAULT '0' not null,
	schedule	INT,
	cycle		INT,
	position	INT,
	days		VARCHAR(13),
	endtype		INT DEFAULT '0' not null,
	end		DATETIME
	)
go

CREATE INDEX twig_schedule_recurring_i_idx ON twig_schedule_recurring (id)
go
CREATE INDEX twig_schedule_recurring_c_idx ON twig_schedule_recurring (schid)
go
CREATE INDEX twig_schedule_recurring_t_idx ON twig_schedule_recurring (recurtype)
go
CREATE INDEX twig_schedule_recurring_e_idx ON twig_schedule_recurring (endtype)
go
CREATE INDEX twig_schedule_recurring_d_idx ON twig_schedule_recurring (end)
go

CREATE TABLE twig_schedule_prefs (
   	id 		NUMERIC IDENTITY NOT NULL PRIMARY KEY,
   	username 	varchar(128),
   	dgroup 		int DEFAULT '0' NOT NULL,
   	viewgrp		varchar(10) DEFAULT '0' NOT NULL
)
go

CREATE INDEX twig_schedule_prefs_u_idx ON twig_schedule_prefs (username)
go

CREATE TABLE twig_sclhsqltable (
   	LID 		varchar(128) DEFAULT '0' NOT NULL PRIMARY KEY,
   	pwdata 		varchar(40) DEFAULT '0' NOT NULL,
   	username 	varchar(128) NOT NULL,
   	expire 		NUMERIC DEFAULT '0' NOT NULL,
)
go

CREATE TABLE twig_session (
   	id 		varchar(40) NOT NULL,
   	username 	varchar(128) NOT NULL,
   	sdata 		text NOT NULL,
   	sid 		numeric DEFAULT '0' NOT NULL,
)
go

CREATE INDEX twig_session_id_index ON twig_session (id)
go
CREATE INDEX twig_session_u_idx ON twig_session (username)
go
CREATE INDEX twig_session_sid_index ON twig_session (sid)
go

CREATE TABLE twig_todo (
 	id		NUMERIC IDENTITY NOT NULL PRIMARY KEY,
 	username	varchar(128),
 	thedate		DATETIME,
 	groupid		INT NOT NULL,
 	item		TEXT,
 	description	TEXT,
 	priority	INT,
 	completed	INT,
	duedate		DATETIME.
	duetype		INT
)
go

CREATE INDEX twig_todo_u_idx ON twig_todo (username)
go

CREATE TABLE twig_todo_lists_entries (
	listid		INT DEFAULT '0' NOT NULL,
	type		INT DEFAULT '0' NOT NULL,
	item		INT DEFAULT '0' NOT NULL,
	subitem		INT
)
go

CREATE INDEX twig_todo_lists_entries_i_idx ON twig_todo_lists_entries (listid)
go
CREATE INDEX twig_todo_lists_entries_t_idx ON twig_todo_lists_entries (item)
go

CREATE TABLE twig_todo_prefs (
   	id 		NUMERIC IDENTITY NOT NULL PRIMARY KEY,
   	username 	varchar(128),
   	sortby 		varchar(10) NOT NULL,
   	sortorder 	int DEFAULT '0' NOT NULL,
   	dgroup 		int DEFAULT '0' NOT NULL,
   	priority 	int DEFAULT '0' NOT NULL,
   	pagesize 	int DEFAULT '0' NOT NULL,
   	viewgrp 	varchar(10) DEFAULT '0' NOT NULL,
	duetype		int DEFAULT '0' NOT NULL
)
go

CREATE INDEX twig_todo_prefs_u_idx ON twig_todo_prefs (username)
go