Kako brže izraditi SQL definiciju neke tabele uz pomoć Emacs Lispa

Kako brže izraditi SQL definiciju koristeći Emacs Lisp

Recimo da stalno pravite nove SQL tabele, ne želite trošiti vrijeme na pripremu definicija i možda i ne želite kopirati prethodne definicije i onda ih prepravljati.

I stalno pišete sa GNU Emacs uređivačem teksta.

Elisp funkcija zove sql/table-template i pokreće se komandom:

M-x sql/table-template

Onda će Vas GNU Emacs pitati za naziv SQL tabele i nakon toga će tabela već biti upisana u tekst koji obrađujete.


-- ------------------------------------------
-- ------------ 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'; 
*/

Podrazumijeva se da ćete skript tako prilagoditi svojim potrebama da se tu pojavljuje odgovarajuća SQL definicija.

(defun template-sql-table ()
  "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)))

Za sva pitanja, stojimo na raspolaganju, kontaktirajte nas putem formulara.

Leave Your Comment or Contact GNU.Support

Contact GNU.Support now. There is a simple rule at GNU.Support: if we can help you, we do, whenever and wherever necessary, and it's the way we've been doing business since 2002, and the only way we know


Full name:


E-mail:


Message: