* Eric Abrahamsen <e...@ericabrahamsen.net> [2019-08-03 23:33]:
> time I'm actually starting to feel comfortable with sql.
I am using skeleton to quickly create SQL definitions.
Now imagine `contacts', `accounts', `countries', etc. It works fast.
(define-skeleton cf-sql-table
"Prepare the SQL table for Central Files database design"
nil
"
-- ------------------------------------------
-- ------------ Table " (setq table (skeleton-read "Table name: ")) "
-- ------------------------------------------
DROP SEQUENCE " 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 '" (capitalize table) "';
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 '" (capitalize table) "';
COMMENT ON COLUMN " table "_list." table "_id IS 'ID';
COMMENT ON COLUMN " table "_list." table "_name IS 'Name';
*/
}
);")
