31 lines
929 B
PL/PgSQL
31 lines
929 B
PL/PgSQL
BEGIN;
|
|
|
|
INSERT INTO version_history (component, from_version, to_version, author)
|
|
VALUES ('QUOTES', 0, 1, 'Initial Creation');
|
|
|
|
CREATE TABLE quotes(
|
|
quoteid INTEGER NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
|
|
communityid INTEGER NOT NULL REFERENCES communities(communityid) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
content TEXT NOT NULL,
|
|
created_by INTEGER REFERENCES user_profiles(profileid) ON UPDATE CASCADE ON DELETE NO ACTION,
|
|
deleted_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
_timestamp TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
CREATE TRIGGER quotes_timestamp BEFORE UPDATE ON quotes
|
|
FOR EACH ROW EXECUTE FUNCTION update_timestamp_column();
|
|
|
|
CREATE VIEW
|
|
quotes_info
|
|
AS
|
|
SELECT
|
|
*,
|
|
(deleted_at is not NULL) AS is_deleted,
|
|
(row_number() OVER (PARTITION BY communityid ORDER BY created_at ASC)) as quotelabel
|
|
FROM
|
|
quotes
|
|
ORDER BY (communityid, created_at);
|
|
|
|
|
|
COMMIT;
|