/* SQL-92 Table Creation Script with DBISAM Extensions */

DROP TABLE IF EXISTS "au_x";

CREATE TABLE IF NOT EXISTS "au_x"
(
   "Author" VARCHAR(30),
   "Ref_ID" INTEGER,
PRIMARY KEY ("Author","Ref_ID") COMPRESS FULL
LANGUAGE "ANSI Standard" SORT "Default Order"
USER MAJOR VERSION 7
);


DROP TABLE IF EXISTS "bib";

CREATE TABLE IF NOT EXISTS "bib"
(
   "Ref_ID" AUTOINC,
   "Authors" VARCHAR(250),
   "Title" VARCHAR(250),
   "Keywords" MEMO,
   "Ref_mark" VARCHAR(1),
   "Created_by" VARCHAR(20),
   "Year_pub" SMALLINT,
   "Ref_type" VARCHAR(30),
   "Subject" VARCHAR(100),
   "Sec_authors" VARCHAR(250),
   "Sec_title" VARCHAR(250),
   "Notes" MEMO,
   "Place_pub" VARCHAR(100),
   "Publisher" VARCHAR(150),
   "Volume" VARCHAR(20),
   "Number" VARCHAR(20),
   "Page_start" VARCHAR(20),
   "Page_end" VARCHAR(20),
   "Tert_authors" VARCHAR(250),
   "Tert_title" VARCHAR(250),
   "Edition" VARCHAR(20),
   "Date_pub" DATE,
   "Type_work" VARCHAR(100),
   "Quat_authors" VARCHAR(250),
   "Quat_title" VARCHAR(250),
   "Isbn_issn" VARCHAR(20),
   "Label" VARCHAR(60),
   "Abstract" MEMO,
   "Date_created" DATE,
   "Availability" VARCHAR(12),
   "Location" VARCHAR(150),
   "Address" VARCHAR(250),
   "Language" VARCHAR(30),
   "Country" VARCHAR(30),
   "Url" VARCHAR(250),
   "Custom_1" VARCHAR(250),
   "Custom_2" VARCHAR(250),
   "Custom_3" VARCHAR(100),
   "Custom_4" VARCHAR(100),
   "Ref_doc" MEMO,
   "Date_modified" DATE,
   "Ref_read" VARCHAR(1),
   "Priority" VARCHAR(10),
   "Ref_type_ID" INTEGER,
   "Internal" VARCHAR(20),
   "Modified_by" VARCHAR(20),
   "Custom_5" VARCHAR(50),
   "Custom_6" VARCHAR(50),
   "Attachment" VARCHAR(250),
   "File_as" VARCHAR(40),
   "Call_number" VARCHAR(30),
   "Description" VARCHAR(150),
   "Reprint" VARCHAR(150),
   "Date_freeform" VARCHAR(20),
   "Ref_misc" MEMO,
   "Categories" MEMO,
   "Web_post_hide" VARCHAR(1),
   "Title_short" VARCHAR(100),
   "Work_reviewed" VARCHAR(100),
   "Extent_work" VARCHAR(20),
   "Section" VARCHAR(20),
   "Accession_num" VARCHAR(20),
   "Last_post" VARCHAR(22),
   "BPPalmId" INTEGER,
   "BPPrivate" BOOLEAN,
   "BPModified" BOOLEAN,
   "BPDeleted" BOOLEAN,
   "BPArchived" BOOLEAN,
   "BPCategory" INTEGER,
   "BPSyncAction" INTEGER,
   "Folder_ID" INTEGER,
   "Folder" VARCHAR(50),
   "Deleted" VARCHAR(1),
   "DOI" VARCHAR(50),
   "Original_pub" VARCHAR(100),
   "DB_name" VARCHAR(20),
   "DB_provider" VARCHAR(20),
   "Custom_7" VARCHAR(20),
   "Custom_8" VARCHAR(20),
   "Custom_date" DATE,
   "Custom_number" FLOAT,
   "Ref_order" INTEGER,
   "Parent" INTEGER,
   "Parent_order" INTEGER,
   "gRelatedID" VARCHAR(12),
   "gCitedID" VARCHAR(20),
   "gCitedCnt" INTEGER,
PRIMARY KEY ("Ref_ID") COMPRESS FULL
TEXT INDEX ("Authors","Title","Keywords","Sec_authors","Sec_title","Notes","Publisher","Tert_authors","Tert_title","Abstract","Language","File_as","Ref_misc","Categories","Ref_doc")

LANGUAGE "ANSI Standard" SORT "Default Order"
USER MAJOR VERSION 8
);

CREATE NOCASE INDEX IF NOT EXISTS "secx_File_as" ON "bib" ("File_as") COMPRESS FULL;
CREATE NOCASE INDEX IF NOT EXISTS "secx_Ref_mark" ON "bib" ("Ref_mark") COMPRESS FULL;
CREATE NOCASE INDEX IF NOT EXISTS "secx_Ref_type" ON "bib" ("Ref_type") COMPRESS FULL;
CREATE NOCASE INDEX IF NOT EXISTS "secx_Year_pub" ON "bib" ("Year_pub") COMPRESS FULL;
CREATE NOCASE INDEX IF NOT EXISTS "secx_Authors" ON "bib" ("Authors") COMPRESS FULL;
CREATE INDEX IF NOT EXISTS "BPPalmIdIndex" ON "bib" ("BPPalmId");
CREATE INDEX IF NOT EXISTS "sec_Deleted" ON "bib" ("Deleted") COMPRESS FULL;
CREATE INDEX IF NOT EXISTS "sec_Folder_ID" ON "bib" ("Folder_ID") COMPRESS FULL;
CREATE DESC INDEX IF NOT EXISTS "sec_Ref_ID" ON "bib" ("Ref_ID") COMPRESS FULL;
CREATE INDEX IF NOT EXISTS "sec_Date_created" ON "bib" ("Date_created") COMPRESS FULL;
CREATE INDEX IF NOT EXISTS "sec_Date_modified" ON "bib" ("Date_modified") COMPRESS FULL;
CREATE INDEX IF NOT EXISTS "sec_Parent_order" ON "bib" ("Parent_order") COMPRESS FULL;

DROP TABLE IF EXISTS "borrower";

CREATE TABLE IF NOT EXISTS "borrower"
(
   "Borrower" VARCHAR(30),
   "Department" VARCHAR(30),
   "Phone" VARCHAR(20),
   "Fax" VARCHAR(20),
   "Email" VARCHAR(100),
   "Address" VARCHAR(100),
   "City" VARCHAR(30),
   "State" VARCHAR(20),
   "Zip" VARCHAR(20),
   "Bor_privilege" VARCHAR(10),
   "Notes" MEMO,
   "Date_created" DATE,
   "Last_post" VARCHAR(22),
PRIMARY KEY ("Borrower") COMPRESS FULL
LANGUAGE "ANSI Standard" SORT "Default Order"
USER MAJOR VERSION 7
);


DROP TABLE IF EXISTS "cat_x";

CREATE TABLE IF NOT EXISTS "cat_x"
(
   "Cat_name" VARCHAR(40),
   "Category_ID" INTEGER,
   "Name_scope" VARCHAR(40),
PRIMARY KEY ("Cat_name","Category_ID") COMPRESS FULL
LANGUAGE "ANSI Standard" SORT "Default Order"
USER MAJOR VERSION 7
);


DROP TABLE IF EXISTS "catalog";

CREATE TABLE IF NOT EXISTS "catalog"
(
   "Catalog_ID" AUTOINC,
   "Call_number" VARCHAR(30),
   "Cat_type" VARCHAR(30),
   "Cat_type_ID" INTEGER,
   "Class" VARCHAR(20),
   "Label" VARCHAR(20),
   "Copy" VARCHAR(30),
   "Title" VARCHAR(250),
   "Subtitle" VARCHAR(250),
   "Authors" VARCHAR(250),
   "Author_corp" VARCHAR(100),
   "Responsibility" VARCHAR(20),
   "Edition" VARCHAR(20),
   "Source" VARCHAR(250),
   "Publisher" VARCHAR(150),
   "Place_pub" VARCHAR(100),
   "Date_pub" DATE,
   "Description" VARCHAR(150),
   "Series" MEMO,
   "Abstract" MEMO,
   "Notes" MEMO,
   "Subject" MEMO,
   "Isbn_issn" VARCHAR(20),
   "Date_cat" DATE,
   "Order_num" VARCHAR(20),
   "Acct_num" VARCHAR(20),
   "Purchase_order" VARCHAR(20),
   "Copies_order" SMALLINT,
   "Requester" VARCHAR(30),
   "Date_request" DATE,
   "Date_expected" DATE,
   "Date_order" DATE,
   "Date_received" DATE,
   "Date_sent" DATE,
   "Order_type" VARCHAR(20),
   "Supplier" VARCHAR(50),
   "Invoice_num" VARCHAR(20),
   "Price_list" FLOAT,
   "Cost_actual" FLOAT,
   "Order_notes" VARCHAR(250),
   "Date_start" DATE,
   "Date_expire" DATE,
   "Date_renew" DATE,
   "Holdings" VARCHAR(50),
   "Frequency" VARCHAR(20),
   "Special_issue" VARCHAR(100),
   "Date_created" DATE,
   "Code" VARCHAR(20),
   "Policy" VARCHAR(250),
   "Policy_serial" VARCHAR(50),
   "Location" VARCHAR(50),
   "Date_freeform" VARCHAR(20),
   "Hold" VARCHAR(30),
   "Reserve" VARCHAR(30),
   "Serial_type" VARCHAR(30),
   "Status" VARCHAR(12),
   "Last_post" VARCHAR(22),
   "Year_pub" INTEGER,
PRIMARY KEY ("Catalog_ID") COMPRESS FULL
LANGUAGE "ANSI Standard" SORT "Default Order"
USER MAJOR VERSION 7
);

CREATE NOCASE INDEX IF NOT EXISTS "secx_Title" ON "catalog" ("Title") COMPRESS FULL;
CREATE NOCASE INDEX IF NOT EXISTS "secx_Call_number" ON "catalog" ("Call_number") COMPRESS FULL;

DROP TABLE IF EXISTS "category";

CREATE TABLE IF NOT EXISTS "category"
(
   "Category_ID" AUTOINC,
   "Parent" INTEGER,
   "Category" VARCHAR(40),
   "Definition" MEMO,
   "Other_names" VARCHAR(120),
   "Created_by" VARCHAR(20),
   "Date_created" DATE,
   "Modified_by" VARCHAR(20),
   "Date_modified" DATE,
   "Cat_mark" VARCHAR(1),
   "Folder_ID" INTEGER,
   "Folder" VARCHAR(50),
   "Cat_order" INTEGER,
   "Cat_type" VARCHAR(40),
   "Last_post" VARCHAR(22),
   "Web_post_hide" VARCHAR(1),
   "Priority" VARCHAR(10),
   "Cat_uri" VARCHAR(200),
   "Parent_order" INTEGER,
   "Deleted" VARCHAR(1),
   "Cat_type_ID" SMALLINT,
   "Rec_count" INTEGER,
   "Refs_count" INTEGER,
   "Notes_count" INTEGER,
   "Tasks_count" INTEGER,
   "Charts_count" INTEGER,
PRIMARY KEY ("Category_ID") COMPRESS FULL
TEXT INDEX ("Category","Definition","Other_names","Created_by","Modified_by","Folder","Cat_type")

LANGUAGE "ANSI Standard" SORT "Default Order"
USER MAJOR VERSION 8
);

CREATE INDEX IF NOT EXISTS "sec_Parent_order" ON "category" ("Parent_order") COMPRESS FULL;
CREATE INDEX IF NOT EXISTS "sec_Deleted" ON "category" ("Deleted") COMPRESS FULL;
CREATE INDEX IF NOT EXISTS "sec_Folder_ID" ON "category" ("Folder_ID") COMPRESS FULL;
CREATE INDEX IF NOT EXISTS "sec_Folder" ON "category" ("Folder") COMPRESS FULL;
CREATE INDEX IF NOT EXISTS "sec_Date_created" ON "category" ("Date_created") COMPRESS FULL;
CREATE INDEX IF NOT EXISTS "sec_Date_modified" ON "category" ("Date_modified") COMPRESS FULL;

DROP TABLE IF EXISTS "chart";

CREATE TABLE IF NOT EXISTS "chart"
(
   "Chart_ID" AUTOINC,
   "Parent" INTEGER,
   "Chart_name" VARCHAR(50),
   "Bk_color" INTEGER,
   "Image" SMALLINT,
   "Chart" BLOB,
   "Last_post" VARCHAR(22),
   "Chart_order" INTEGER,
   "Folder_ID" INTEGER,
   "Folder" VARCHAR(50),
   "Chart_mark" VARCHAR(1),
   "Categories" MEMO,
   "Web_post_hide" VARCHAR(1),
   "Chart_type" VARCHAR(30),
   "Created_by" VARCHAR(20),
   "Date_created" DATE,
   "Modified_by" VARCHAR(20),
   "Date_modified" DATE,
   "Chart_text" MEMO,
   "Parent_order" INTEGER,
   "Deleted" VARCHAR(1),
   "Priority" VARCHAR(10),
   "Chart_type_ID" SMALLINT,
PRIMARY KEY ("Chart_ID") COMPRESS FULL
TEXT INDEX ("Chart_name","Folder","Categories","Chart_text")

LANGUAGE "ANSI Standard" SORT "Default Order"
USER MAJOR VERSION 7
);

CREATE INDEX IF NOT EXISTS "Parent" ON "chart" ("Parent") COMPRESS FULL;
CREATE INDEX IF NOT EXISTS "sec_Parent_order" ON "chart" ("Parent_order") COMPRESS FULL;
CREATE INDEX IF NOT EXISTS "sec_Deleted" ON "chart" ("Deleted") COMPRESS FULL;
CREATE INDEX IF NOT EXISTS "sec_Folder_ID" ON "chart" ("Folder_ID") COMPRESS FULL;
CREATE INDEX IF NOT EXISTS "sec_Date_created" ON "chart" ("Date_created") COMPRESS FULL;
CREATE INDEX IF NOT EXISTS "sec_Date_modified" ON "chart" ("Date_modified") COMPRESS FULL;

DROP TABLE IF EXISTS "circul";

CREATE TABLE IF NOT EXISTS "circul"
(
   "Circul_ID" AUTOINC,
   "Catalog_ID" INTEGER,
   "Borrower" VARCHAR(30),
   "Date_loan" DATE,
   "Date_due" DATE,
   "Date_returned" DATE,
   "Permanent_loan" VARCHAR(3),
   "Class_history" VARCHAR(20),
   "Waiting_list" VARCHAR(50),
   "Fine" FLOAT,
   "Notes" VARCHAR(100),
   "Last_post" VARCHAR(22),
PRIMARY KEY ("Circul_ID") COMPRESS FULL
LANGUAGE "ANSI Standard" SORT "Default Order"
USER MAJOR VERSION 7
);

CREATE INDEX IF NOT EXISTS "Borrower" ON "circul" ("Borrower") COMPRESS FULL;
CREATE INDEX IF NOT EXISTS "Catalog_ID" ON "circul" ("Catalog_ID") COMPRESS FULL;

DROP TABLE IF EXISTS "deleted";

CREATE TABLE IF NOT EXISTS "deleted"
(
   "Deleted_ID" AUTOINC,
   "Rec_ID" INTEGER,
   "Rec_type" VARCHAR(10),
   "Rec_subject" VARCHAR(150),
   "Rec_data" MEMO,
   "Rec_folder" VARCHAR(50),
   "Rec_folder_ID" INTEGER,
   "Created_by" VARCHAR(20),
   "Deleted_by" VARCHAR(20),
   "Date_created" DATE,
   "Date_deleted" DATE,
   "Time_deleted" TIMESTAMP,
PRIMARY KEY ("Deleted_ID") COMPRESS FULL
TEXT INDEX ("Rec_type","Rec_subject","Rec_data","Rec_folder","Created_by","Deleted_by")

LANGUAGE "ANSI Standard" SORT "Default Order"
USER MAJOR VERSION 7
);


DROP TABLE IF EXISTS "folder";

CREATE TABLE IF NOT EXISTS "folder"
(
   "Folder_ID" AUTOINC,
   "Parent" INTEGER,
   "Folder" VARCHAR(50),
   "Created_by" VARCHAR(20),
   "Fld_type_ID" SMALLINT,
   "Last_post" VARCHAR(22),
   "Fld_order" INTEGER,
   "Fld_type" VARCHAR(12),
   "Fld_search" VARCHAR(1),
   "Web_post_hide" VARCHAR(1),
   "Modified_by" VARCHAR(20),
   "Date_created" DATE,
   "Description" VARCHAR(250),
   "Fld_sql" MEMO,
   "Fld_mark" VARCHAR(1),
   "Date_modified" DATE,
   "Parent_order" INTEGER,
   "Deleted" VARCHAR(1),
   "Rec_count" INTEGER,
PRIMARY KEY ("Folder_ID") COMPRESS FULL
LANGUAGE "ANSI Standard" SORT "Default Order"
USER MAJOR VERSION 8
);

CREATE NOCASE INDEX IF NOT EXISTS "sec_parent" ON "folder" ("Parent") COMPRESS FULL;
CREATE INDEX IF NOT EXISTS "sec_Parent_order" ON "folder" ("Parent_order") COMPRESS FULL;
CREATE INDEX IF NOT EXISTS "sec_Deleted" ON "folder" ("Deleted") COMPRESS FULL;

INSERT INTO "folder" (Folder, Parent, Fld_type_ID, Fld_type, Fld_order, Parent_order) VALUES ("Places", -1, 5, "Container", 1, 11);
INSERT INTO "folder" (Folder, Parent, Fld_type_ID, Fld_type, Fld_order, Parent_order) VALUES ("Folders", -1, 5, "Container", 2, 12);
INSERT INTO "folder" (Folder, Parent, Fld_type_ID, Fld_type, Fld_order, Parent_order) VALUES ("Collections", -1, 5, "Container", 3, 13);
INSERT INTO "folder" (Folder, Parent, Fld_type_ID, Fld_type, Fld_order, Parent_order) VALUES ("Smart Collections", -1, 5, "Container", 4, 14);

INSERT INTO "folder" (Folder, Parent, Fld_type_ID, Fld_type, Fld_order, Parent_order) VALUES ("Deleted", 1, 11, "Deleted", 1, 11);
INSERT INTO "folder" (Folder, Parent, Fld_type_ID, Fld_type, Fld_order, Parent_order) VALUES ("Links", 1, 9, "Links", 2, 12);
INSERT INTO "folder" (Folder, Parent, Fld_type_ID, Fld_type, Fld_order, Parent_order) VALUES ("Library", 1, 6, "Library", 3, 13);
INSERT INTO "folder" (Folder, Parent, Fld_type_ID, Fld_type, Fld_order, Parent_order) VALUES ("Internet", 1, 4, "Internet", 4, 14);

INSERT INTO "folder" (Folder, Parent, Fld_type_ID, Fld_type, Fld_order, Parent_order) VALUES ("Online Search", 2, 0, "References", 1, 21);
INSERT INTO "folder" (Folder, Parent, Fld_type_ID, Fld_type, Fld_order, Parent_order) VALUES ("New Categories", 2, 7, "Categories", 2, 22);
INSERT INTO "folder" (Folder, Parent, Fld_type_ID, Fld_type, Fld_order, Parent_order) VALUES ("References", 2, 0, "References", 3, 23);
INSERT INTO "folder" (Folder, Parent, Fld_type_ID, Fld_type, Fld_order, Parent_order) VALUES ("Notes", 2, 1, "Notes", 4, 24);
INSERT INTO "folder" (Folder, Parent, Fld_type_ID, Fld_type, Fld_order, Parent_order) VALUES ("Tasks", 2, 2, "Tasks", 5, 25);
INSERT INTO "folder" (Folder, Parent, Fld_type_ID, Fld_type, Fld_order, Parent_order) VALUES ("Charts", 2, 3, "Charts", 6, 26);
INSERT INTO "folder" (Folder, Parent, Fld_type_ID, Fld_type, Fld_order, Parent_order) VALUES ("Categories", 2, 7, "Categories", 7, 27);

INSERT INTO "folder" (Folder, Parent, Fld_type_ID, Fld_type, Fld_order, Parent_order, Fld_search) VALUES ("Composition", 3, 12, "Composition", 1, 31, "L");

DROP TABLE IF EXISTS "interlib";

CREATE TABLE IF NOT EXISTS "interlib"
(
   "Request_ID" AUTOINC,
   "Borrower" VARCHAR(30),
   "Lender" VARCHAR(100),
   "Title_article" VARCHAR(250),
   "Authors" VARCHAR(250),
   "Title" VARCHAR(250),
   "Imprint" VARCHAR(250),
   "Edition" VARCHAR(20),
   "Call_number" VARCHAR(30),
   "Date_pub" VARCHAR(20),
   "Volume" VARCHAR(20),
   "Number" VARCHAR(20),
   "Copyright" VARCHAR(50),
   "Cost" VARCHAR(10),
   "Date_request" DATE,
   "Date_due" DATE,
   "Date_need" DATE,
   "Date_renew" DATE,
   "Date_due_renew" DATE,
   "Status" VARCHAR(20),
   "Bill_to" VARCHAR(50),
   "Ship_via" VARCHAR(20),
   "Notes" MEMO,
   "Charges" VARCHAR(50),
   "Date_shipped" DATE,
   "Restrictions" VARCHAR(100),
   "Notes_lender" VARCHAR(200),
   "Returned_via" VARCHAR(20),
   "Date_returned" DATE,
   "Date_created" DATE,
   "Date_received" DATE,
   "Last_post" VARCHAR(22),
PRIMARY KEY ("Request_ID") COMPRESS FULL
LANGUAGE "ANSI Standard" SORT "Default Order"
USER MAJOR VERSION 7
);


DROP TABLE IF EXISTS "jn_x";

CREATE TABLE IF NOT EXISTS "jn_x"
(
   "Journal" VARCHAR(100),
   "Ref_ID" INTEGER,
PRIMARY KEY ("Journal","Ref_ID") COMPRESS FULL
LANGUAGE "ANSI Standard" SORT "Default Order"
USER MAJOR VERSION 7
);


DROP TABLE IF EXISTS "kw_n";

CREATE TABLE IF NOT EXISTS "kw_n"
(
   "Keyword" VARCHAR(30),
   "Note_ID" INTEGER,
PRIMARY KEY ("Keyword","Note_ID") COMPRESS FULL
LANGUAGE "ANSI Standard" SORT "Default Order"
USER MAJOR VERSION 7
);


DROP TABLE IF EXISTS "kw_x";

CREATE TABLE IF NOT EXISTS "kw_x"
(
   "Keyword" VARCHAR(30),
   "Ref_ID" INTEGER,
PRIMARY KEY ("Keyword","Ref_ID") COMPRESS FULL
LANGUAGE "ANSI Standard" SORT "Default Order"
USER MAJOR VERSION 7
);


DROP TABLE IF EXISTS "lender";

CREATE TABLE IF NOT EXISTS "lender"
(
   "Lender" VARCHAR(100),
   "Lender_code" VARCHAR(20),
   "Contact" VARCHAR(30),
   "Phone" VARCHAR(20),
   "Fax" VARCHAR(20),
   "Email" VARCHAR(100),
   "Address" VARCHAR(100),
   "City" VARCHAR(30),
   "State" VARCHAR(20),
   "Zip" VARCHAR(20),
   "Notes" MEMO,
   "Date_created" DATE,
   "Last_post" VARCHAR(22),
PRIMARY KEY ("Lender") COMPRESS FULL
LANGUAGE "ANSI Standard" SORT "Default Order"
USER MAJOR VERSION 7
);


DROP TABLE IF EXISTS "link_rec";

CREATE TABLE IF NOT EXISTS "link_rec"
(
   "Link_from" VARCHAR(10),
   "Link_from_ID" INTEGER,
   "Link_to" VARCHAR(10),
   "Link_to_ID" INTEGER,
   "Link_relationship" VARCHAR(40),
   "Link_to_rec" VARCHAR(250),
   "Description" VARCHAR(150),
   "Last_post" VARCHAR(22),
   "Link_ID" AUTOINC,
   "Link_parent" INTEGER,
   "Link_order" INTEGER,
   "Created_by" VARCHAR(20),
   "Modified_by" VARCHAR(20),
   "Date_created" DATE,
   "Date_modified" DATE,
   "Link_from_rec" VARCHAR(150),
   "Link_from_role" VARCHAR(40),
   "Link_to_role" VARCHAR(40),
   "Link_to_text" MEMO,
   "Link_scope" VARCHAR(40),
   "Deleted" VARCHAR(1),
   "Link_from_tag" INTEGER,
   "Link_to_tag" INTEGER,
   "Link_mark" VARCHAR(1),
   "Link_parent_order" INTEGER,
   "Exclude_exp" VARCHAR(1),
   "Page_break" VARCHAR(1),
   "Preserve_fmt" VARCHAR(1),
   "Link_to_number" FLOAT,
PRIMARY KEY ("Link_from","Link_from_ID","Link_to","Link_to_ID","Link_from_tag","Link_to_tag") COMPRESS FULL
TEXT INDEX ("Link_relationship","Link_from_rec","Link_to_rec","Description","Created_by","Modified_by","Link_from_role","Link_to_role","Link_to_text")

LANGUAGE "ANSI Standard" SORT "Default Order"
USER MAJOR VERSION 8
);

CREATE INDEX IF NOT EXISTS "Link_from_ID" ON "link_rec" ("Link_from_ID") COMPRESS FULL;
CREATE INDEX IF NOT EXISTS "Link_from" ON "link_rec" ("Link_from") COMPRESS FULL;
CREATE INDEX IF NOT EXISTS "sec_Link_order" ON "link_rec" ("Link_order") COMPRESS FULL;
CREATE INDEX IF NOT EXISTS "sec_Deleted" ON "link_rec" ("Deleted") COMPRESS FULL;
CREATE INDEX IF NOT EXISTS "sec_Link_parent_order" ON "link_rec" ("Link_parent_order") COMPRESS FULL;
CREATE INDEX IF NOT EXISTS "Link_to" ON "link_rec" ("Link_to") COMPRESS FULL;
CREATE INDEX IF NOT EXISTS "Link_to_ID" ON "link_rec" ("Link_to_ID") COMPRESS FULL;
CREATE INDEX IF NOT EXISTS "Link_relationship" ON "link_rec" ("Link_relationship") COMPRESS FULL;

DROP TABLE IF EXISTS "note";

CREATE TABLE IF NOT EXISTS "note"
(
   "Note_ID" AUTOINC,
   "Parent" INTEGER,
   "Title" VARCHAR(250),
   "Note" MEMO,
   "Note_type_ID" SMALLINT,
   "Keywords" VARCHAR(250),
   "Date_created" DATE,
   "Date_modified" DATE,
   "Created_by" VARCHAR(20),
   "Modified_by" VARCHAR(20),
   "Internal" VARCHAR(20),
   "Attachment" VARCHAR(200),
   "Last_post" VARCHAR(22),
   "Web_post_hide" VARCHAR(1),
   "Format_type" VARCHAR(1),
   "Deleted" VARCHAR(1),
   "Folder_ID" INTEGER,
   "Folder" VARCHAR(50),
   "Note_type" VARCHAR(30),
   "Note_order" INTEGER,
   "Categories" MEMO,
   "Label" VARCHAR(20),
   "Shape" INTEGER,
   "Color" INTEGER,
   "Note_mark" VARCHAR(1),
   "Priority" VARCHAR(10),
   "Date_target" DATE,
   "Web_url" VARCHAR(200),
   "Parent_order" INTEGER,
   "Note_misc" MEMO,
   "Height" INTEGER,
   "Width" INTEGER,
   "Abstract" VARCHAR(250),
   "Date_content" DATE,
   "Status" VARCHAR(20),
   "Wordcount" INTEGER,
   "Target_wc" INTEGER,
   "Progress_wc" INTEGER,
   "Snapshot" VARCHAR(1),
   "Snapshot_ID" INTEGER,
   "Snapshot_num" INTEGER,
PRIMARY KEY ("Note_ID") COMPRESS FULL
TEXT INDEX ("Title","Note","Keywords","Created_by","Modified_by","Categories","Label","Folder","Priority","Note_misc","Abstract")

LANGUAGE "ANSI Standard" SORT "Default Order"
USER MAJOR VERSION 8
);

CREATE INDEX IF NOT EXISTS "Parent" ON "note" ("Parent") COMPRESS FULL;
CREATE INDEX IF NOT EXISTS "sec_Parent_order" ON "note" ("Parent_order") COMPRESS FULL;
CREATE INDEX IF NOT EXISTS "sec_Deleted" ON "note" ("Deleted") COMPRESS FULL;
CREATE INDEX IF NOT EXISTS "sec_Folder_ID" ON "note" ("Folder_ID") COMPRESS FULL;
CREATE INDEX IF NOT EXISTS "sec_Date_created" ON "note" ("Date_created") COMPRESS FULL;
CREATE INDEX IF NOT EXISTS "sec_Date_modified" ON "note" ("Date_modified") COMPRESS FULL;
CREATE INDEX IF NOT EXISTS "sec_Date_target" ON "note" ("Date_target") COMPRESS FULL;
CREATE INDEX IF NOT EXISTS "sec_Date_content" ON "note" ("Date_content") COMPRESS FULL;
CREATE INDEX IF NOT EXISTS "sec_Snapshot" ON "note" ("Snapshot") COMPRESS FULL;

DROP TABLE IF EXISTS "serial";

CREATE TABLE IF NOT EXISTS "serial"
(
   "Serial_ID" AUTOINC,
   "Catalog_ID" INTEGER,
   "Copy_management" VARCHAR(20),
   "Volume" VARCHAR(10),
   "Issue" VARCHAR(10),
   "Status" VARCHAR(10),
   "Date_published" DATE,
   "Date_expected" DATE,
   "Date_received" DATE,
   "Notes" VARCHAR(250),
   "Table_of_contents" MEMO,
   "Routing_list" VARCHAR(250),
   "Date_rounting" DATE,
   "Date_created" DATE,
   "Last_post" VARCHAR(22),
PRIMARY KEY ("Serial_ID") COMPRESS FULL
LANGUAGE "ANSI Standard" SORT "Default Order"
USER MAJOR VERSION 7
);


DROP TABLE IF EXISTS "supplier";

CREATE TABLE IF NOT EXISTS "supplier"
(
   "Supplier" VARCHAR(100),
   "Contact" VARCHAR(30),
   "Phone" VARCHAR(20),
   "Fax" VARCHAR(20),
   "Email" VARCHAR(100),
   "Address" VARCHAR(100),
   "City" VARCHAR(30),
   "State" VARCHAR(20),
   "Zip" VARCHAR(20),
   "Notes" MEMO,
   "Date_created" DATE,
   "Last_post" VARCHAR(22),
PRIMARY KEY ("Supplier") COMPRESS FULL
LANGUAGE "ANSI Standard" SORT "Default Order"
USER MAJOR VERSION 7
);


DROP TABLE IF EXISTS "task";

CREATE TABLE IF NOT EXISTS "task"
(
   "Task_ID" AUTOINC,
   "Task" VARCHAR(250),
   "Priority" VARCHAR(10),
   "Status" VARCHAR(30),
   "Percent_complete" VARCHAR(4),
   "Task_misc" VARCHAR(100),
   "Date_start" DATE,
   "Date_due" DATE,
   "Created_by" VARCHAR(20),
   "Modified_by" VARCHAR(20),
   "Date_created" DATE,
   "Date_modified" DATE,
   "Complete" VARCHAR(1),
   "Date_completed" DATE,
   "Description" MEMO,
   "Task_type_ID" SMALLINT,
   "Reminder" VARCHAR(1),
   "Rem_date" DATE,
   "Rem_time" VARCHAR(14),
   "Categories" MEMO,
   "Internal" VARCHAR(20),
   "Last_post" VARCHAR(22),
   "Web_post_hide" VARCHAR(1),
   "Task_order" INTEGER,
   "Parent" INTEGER,
   "Folder_ID" INTEGER,
   "Folder" VARCHAR(50),
   "Task_type" VARCHAR(30),
   "Task_mark" VARCHAR(1),
   "Parent_order" INTEGER,
   "Deleted" VARCHAR(1),
PRIMARY KEY ("Task_ID") COMPRESS FULL
TEXT INDEX ("Task","Priority","Status","Task_misc","Created_by","Modified_by","Description","Categories","Folder")

LANGUAGE "ANSI Standard" SORT "Default Order"
USER MAJOR VERSION 7
);

CREATE INDEX IF NOT EXISTS "Complete" ON "task" ("Complete") COMPRESS FULL;
CREATE INDEX IF NOT EXISTS "sec_Parent_order" ON "task" ("Parent_order") COMPRESS FULL;
CREATE INDEX IF NOT EXISTS "sec_Folder_ID" ON "task" ("Folder_ID") COMPRESS FULL;
CREATE INDEX IF NOT EXISTS "sec_Deleted" ON "task" ("Deleted") COMPRESS FULL;
CREATE INDEX IF NOT EXISTS "sec_Date_created" ON "task" ("Date_created") COMPRESS FULL;
CREATE INDEX IF NOT EXISTS "sec_Date_modified" ON "task" ("Date_modified") COMPRESS FULL;
CREATE INDEX IF NOT EXISTS "sec_Date_start" ON "task" ("Date_start") COMPRESS FULL;
CREATE INDEX IF NOT EXISTS "sec_Date_due" ON "task" ("Date_due") COMPRESS FULL;
