From 3ded753cd826732a37f08de308d26da236901108 Mon Sep 17 00:00:00 2001 From: linuxpoet Date: Thu, 7 Jun 2007 01:30:49 +0000 Subject: first run of new notes system, includes tsearch2 full text indexing git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@1258 4979c152-3d1c-0410-bac9-87ea11338e46 --- sql/Pg-database.sql | 19 ++++++++++++------- 1 file changed, 12 insertions(+), 7 deletions(-) (limited to 'sql') diff --git a/sql/Pg-database.sql b/sql/Pg-database.sql index 43e6b657..28bff22d 100644 --- a/sql/Pg-database.sql +++ b/sql/Pg-database.sql @@ -191,20 +191,25 @@ CREATE TABLE company_to_contact ( COMMENT ON TABLE company_to_contact IS $$ To keep track of the relationship between multiple contact methods and a single company $$; -- Begin rocking notes interface -CREATE TABLE note_class(id serial primary key, class text not null check (class ~ '[[:alnum:]_]'), vectors tsvector not null, created date not null default current_date); +CREATE TABLE note_class(id serial primary key, class text not null check (class ~ '[[:alnum:]_]')); INSERT INTO note_class(id,class) VALUES (1,'Entity'); INSERT INTO note_class(id,class) VALUES (2,'Invoice'); -CREATE UNIQUE INDEX note_class_idx ON notes_class(lower(class)); +CREATE UNIQUE INDEX note_class_idx ON note_class(lower(class)); -CREATE TABLE note (id serial primary key, note_class integer not null references note_class(id), note text not null); -CREATE TABLE entity_note() INHERITS notes_class(); +CREATE TABLE note (id serial primary key, note_class integer not null references note_class(id), + note text not null, vector tsvector not null, + created timestamp not null default current_date); + +CREATE TABLE entity_note() INHERITS (note); ALTER TABLE entity_note ADD CHECK (id = 1); CREATE INDEX entity_note_id_idx ON entity_note(id); -CREATE TABLE invoice_note() INHERITS notes_class(); +CREATE UNIQUE INDEX entity_note_class_idx ON note_class(lower(class)); +CREATE INDEX entity_note_vectors_idx ON entity_note USING gist(vector); +CREATE TABLE invoice_note() INHERITS (note); CREATE INDEX invoice_note_id_idx ON invoice_note(id); +CREATE UNIQUE INDEX invoice_note_class_idx ON note_class(lower(class)); +CREATE INDEX invoice_note_vectors_idx ON invoice_note USING gist(vector); ALTER TABLE invoice_note ADD CHECK (id = 2); - - -- END entity -- -- cgit v1.2.3