-- ------------------------------------------
-- ------------ Table autori
-- ------------------------------------------
DROP SEQUENCE autori_autori_id_seq;
CREATE TABLE autori (
autori_id SERIAL NOT NULL PRIMARY KEY,
autori_datecreated TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
autori_datemodified TIMESTAMP,
autori_usercreated TEXT NOT NULL DEFAULT current_user,
autori_usermodified TEXT NOT NULL DEFAULT current_user,
autori_name TEXT,
autori_title TEXT,
autori_description TEXT,
autori_ TEXT
);
GRANT ALL ON autori TO PUBLIC;
DROP VIEW autori_combo;
CREATE OR REPLACE VIEW autori_combo AS
SELECT autori_id AS id,
autori_name AS TEXT
FROM autori;
GRANT SELECT ON autori_combo TO PUBLIC;
COMMENT ON TABLE autori IS 'Autori';
COMMENT ON COLUMN autori.autori_id IS 'ID';
COMMENT ON COLUMN autori.autori_datecreated IS 'Date created';
COMMENT ON COLUMN autori.autori_datemodified IS 'Date modified';
COMMENT ON COLUMN autori.autori_usercreated IS 'User created';
COMMENT ON COLUMN autori.autori_usermodified IS 'User modified';
COMMENT ON COLUMN autori.autori_hid IS 'HID';
COMMENT ON COLUMN autori.autori_name IS 'Name';
COMMENT ON COLUMN autori.autori_title IS 'Title';
COMMENT ON COLUMN autori.autori_description IS 'Description';
COMMENT ON COLUMN autori.autori_IS '';
CREATE UNIQUE INDEX autori_index ON autori ( autori_weekend );
INSERT INTO meta_fields VALUES ('autori','autori_description','widget','area(rows=10,cols=60)');
INSERT INTO meta_fields VALUES ('autori','autori_datecreated','widget','readonly');
INSERT INTO meta_fields VALUES ('autori','autori_datemodified','widget','readonly');
INSERT INTO meta_fields VALUES ('autori','autori_usercreated','widget','readonly');
INSERT INTO meta_fields VALUES ('autori','autori_usermodified','widget','readonly');
INSERT INTO meta_fields VALUES ('autori','autori_','hide_list','1');
-- INSERT INTO autori (autori_name) VALUES ('');
-- INSERT INTO meta_tables VALUES ('autori', 'hide', '1');

-- Triggers
-- For Date Modified
CREATE TRIGGER autori_moddatetime
BEFORE UPDATE ON autori
FOR EACH ROW
EXECUTE PROCEDURE moddatetime(autori_datemodified);

-- For User Modified
CREATE TRIGGER insert_username_autori
BEFORE INSERT OR UPDATE ON autori
FOR EACH ROW
EXECUTE PROCEDURE insert_username(autori_usermodified);


-- List view
/*
DROP VIEW autori_list;
CREATE OR REPLACE VIEW autori_list AS
SELECT autori_id, autori_name
FROM autori ORDER BY autori_id DESC;
COMMENT ON VIEW autori_list IS 'Autori';
COMMENT ON COLUMN autori_list.autori_id IS 'ID';
COMMENT ON COLUMN autori_list.autori_name IS 'Name'; 
*/

(defun sql/table-template ()
  "Inserts SQL table"
  (interactive)
  (let* ((table (read-from-minibuffer "Table name: "))
     (template "
-- ------------------------------------------
-- ------------ Table @@TABLE@@
-- ------------------------------------------
DROP SEQUENCE @@TABLE@@_@@TABLE@@_id_seq;
CREATE TABLE @@TABLE@@ (
@@TABLE@@_id SERIAL NOT NULL PRIMARY KEY,
@@TABLE@@_datecreated TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
@@TABLE@@_datemodified TIMESTAMP,
@@TABLE@@_usercreated TEXT NOT NULL DEFAULT current_user,
@@TABLE@@_usermodified TEXT NOT NULL DEFAULT current_user,
@@TABLE@@_name TEXT,
@@TABLE@@_title TEXT,
@@TABLE@@_description TEXT,
@@TABLE@@_ TEXT
);
GRANT ALL ON @@TABLE@@ TO PUBLIC;
DROP VIEW @@TABLE@@_combo;
CREATE OR REPLACE VIEW @@TABLE@@_combo AS
SELECT @@TABLE@@_id AS id,
@@TABLE@@_name AS TEXT
FROM @@TABLE@@;
GRANT SELECT ON @@TABLE@@_combo TO PUBLIC;
COMMENT ON TABLE @@TABLE@@ IS '{$ucfirst_table_name}';
COMMENT ON COLUMN @@TABLE@@.@@TABLE@@_id IS 'ID';
COMMENT ON COLUMN @@TABLE@@.@@TABLE@@_datecreated IS 'Date created';
COMMENT ON COLUMN @@TABLE@@.@@TABLE@@_datemodified IS 'Date modified';
COMMENT ON COLUMN @@TABLE@@.@@TABLE@@_usercreated IS 'User created';
COMMENT ON COLUMN @@TABLE@@.@@TABLE@@_usermodified IS 'User modified';
COMMENT ON COLUMN @@TABLE@@.@@TABLE@@_hid IS 'HID';
COMMENT ON COLUMN @@TABLE@@.@@TABLE@@_name IS 'Name';
COMMENT ON COLUMN @@TABLE@@.@@TABLE@@_title IS 'Title';
COMMENT ON COLUMN @@TABLE@@.@@TABLE@@_description IS 'Description';
COMMENT ON COLUMN @@TABLE@@.@@TABLE@@_IS '';
CREATE UNIQUE INDEX @@TABLE@@_index ON @@TABLE@@ ( @@TABLE@@_weekend );
INSERT INTO meta_fields VALUES ('@@TABLE@@','@@TABLE@@_description','widget','area(rows=10,cols=60)');
INSERT INTO meta_fields VALUES ('@@TABLE@@','@@TABLE@@_datecreated','widget','readonly');
INSERT INTO meta_fields VALUES ('@@TABLE@@','@@TABLE@@_datemodified','widget','readonly');
INSERT INTO meta_fields VALUES ('@@TABLE@@','@@TABLE@@_usercreated','widget','readonly');
INSERT INTO meta_fields VALUES ('@@TABLE@@','@@TABLE@@_usermodified','widget','readonly');
INSERT INTO meta_fields VALUES ('@@TABLE@@','@@TABLE@@_','hide_list','1');
-- INSERT INTO @@TABLE@@ (@@TABLE@@_name) VALUES ('');
-- INSERT INTO meta_tables VALUES ('@@TABLE@@', 'hide', '1');

-- Triggers
-- For Date Modified
CREATE TRIGGER @@TABLE@@_moddatetime
BEFORE UPDATE ON @@TABLE@@
FOR EACH ROW
EXECUTE PROCEDURE moddatetime(@@TABLE@@_datemodified);

-- For User Modified
CREATE TRIGGER insert_username_@@TABLE@@
BEFORE INSERT OR UPDATE ON @@TABLE@@
FOR EACH ROW
EXECUTE PROCEDURE insert_username(@@TABLE@@_usermodified);


-- List view
/*
DROP VIEW @@TABLE@@_list;
CREATE OR REPLACE VIEW @@TABLE@@_list AS
SELECT @@TABLE@@_id, @@TABLE@@_name
FROM @@TABLE@@ ORDER BY @@TABLE@@_id DESC;
COMMENT ON VIEW @@TABLE@@_list IS '{$ucfirst_table_name}';
COMMENT ON COLUMN @@TABLE@@_list.@@TABLE@@_id IS 'ID';
COMMENT ON COLUMN @@TABLE@@_list.@@TABLE@@_name IS 'Name'; 
*/
} 
);")
    (replaced (replace-regexp-in-string  "@@TABLE@@" table template t)))
    (insert replaced)))