diff options
| -rw-r--r-- | sql/Pg-database.sql | 30 |
1 files changed, 29 insertions, 1 deletions
diff --git a/sql/Pg-database.sql b/sql/Pg-database.sql index deaf3f5a..b0d63331 100644 --- a/sql/Pg-database.sql +++ b/sql/Pg-database.sql @@ -72,6 +72,7 @@ CREATE TABLE session( session_id serial PRIMARY KEY, token VARCHAR(32) CHECK(length(token) = 32), last_used TIMESTAMP default now(), +ttl int default 3600 not null, users_id INTEGER NOT NULL references users(id), transaction_id INTEGER NOT NULL ); @@ -79,9 +80,36 @@ transaction_id INTEGER NOT NULL -- CREATE TABLE transactions ( id int PRIMARY KEY, - table_name text + table_name text, + locked_by int references "session" (session_id) ON DELETE SET NULL ); +COMMENT on TABLE transactions IS +$$ This table tracks basic transactions across AR, AP, and GL related tables. +It provies a referential integrity enforcement mechanism for the financial data +and also some common features such as discretionary (and pessimistic) locking +for long batch workflows. $$; + +CREATE FUNCTION lock_record (int, int) returns bool as +$$ +declare + locked int; +begin; + SELECT locked_by into locked from transactions where id = $1; + IF NOT FOUND THEN + RETURN FALSE; + ELSEIF locked_by is not null AND locked_by <> $2 THEN + RETURN FALSE; + END IF; + UPDATE transactions set locked_by = $2 where id = $1; + RETURN TRUE; +end; +$$ language plpgsql; + +COMMENT ON column transactions.locked_by IS +$$ This should only be used in pessimistic locking measures as required by large +batch work flows. $$; + -- BEGIN new entity management CREATE TABLE entity ( id serial PRIMARY KEY, |