Kako brže izraditi SQL definiciju neke tabele uz pomoć Perl skripte

Kako brže izraditi SQL definiciju koristeći Perl skript

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.

U tom slučaju možete koristiti ovaj Perl skript koji naravno možete promijeniti onako kako to želite.

Unutar nekog programerskog editora, trebate znati kako pokrenuti vanjsku komandu i onda podatke i izlaz iz te vanjske komande automatski upisati u datoteku SQL definicije koju obrađujete.

Kod vim programerskog editora, jednostavno ćete pritisnuti !! i onda napisati naziv vanjske komande pa će njen izlaz automatski biti upisan u editor.

Kod GNU Emacs editora teksta ćete pritisnuti C-u ! i napisati vanjsku komandu čiji će izlaz biti onda unesen u editor.

Za korištenje ove skripte trebate instalirati Perl modul Text::Template.

Skripta se zove table i pokreće se komandom:

table autori

i u tom slučaju ćete dobit tabelu koja se zove autori i koja će izgledati ovako slično:


-- ------------------------------------------
-- ------------ 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.

Cijeli skript možete skinuti sa Interneta i možete ovde pogledati kako izgleda:

#!/usr/bin/perl
exit unless($ARGV[0]);

use Text::Template;

my $table_name = $ARGV[0];
my $ucfirst_table_name = ucfirst($table_name);

my $template = Text::Template->new(TYPE => 'STRING', SOURCE => 
qq{
-- ------------------------------------------
-- ------------ Table {$table_name}
-- ------------------------------------------
DROP SEQUENCE {$table_name}_{$table_name}_id_seq;
CREATE TABLE {$table_name} (
{$table_name}_id SERIAL NOT NULL PRIMARY KEY,
{$table_name}_datecreated TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
{$table_name}_datemodified TIMESTAMP,
{$table_name}_usercreated TEXT NOT NULL DEFAULT current_user,
{$table_name}_usermodified TEXT NOT NULL DEFAULT current_user,
{$table_name}_name TEXT,
{$table_name}_title TEXT,
{$table_name}_description TEXT,
{$table_name}_ TEXT
);
GRANT ALL ON {$table_name} TO PUBLIC;
DROP VIEW {$table_name}_combo;
CREATE OR REPLACE VIEW {$table_name}_combo AS
SELECT {$table_name}_id AS id,
{$table_name}_name AS TEXT
FROM {$table_name};
GRANT SELECT ON {$table_name}_combo TO PUBLIC;
COMMENT ON TABLE {$table_name} IS '{$ucfirst_table_name}';
COMMENT ON COLUMN {$table_name}.{$table_name}_id IS 'ID';
COMMENT ON COLUMN {$table_name}.{$table_name}_datecreated IS 'Date created';
COMMENT ON COLUMN {$table_name}.{$table_name}_datemodified IS 'Date modified';
COMMENT ON COLUMN {$table_name}.{$table_name}_usercreated IS 'User created';
COMMENT ON COLUMN {$table_name}.{$table_name}_usermodified IS 'User modified';
COMMENT ON COLUMN {$table_name}.{$table_name}_hid IS 'HID';
COMMENT ON COLUMN {$table_name}.{$table_name}_name IS 'Name';
COMMENT ON COLUMN {$table_name}.{$table_name}_title IS 'Title';
COMMENT ON COLUMN {$table_name}.{$table_name}_description IS 'Description';
COMMENT ON COLUMN {$table_name}.{$table_name}_IS '';
CREATE UNIQUE INDEX {$table_name}_index ON {$table_name} ( {$table_name}_weekend );
INSERT INTO meta_fields VALUES ('{$table_name}','{$table_name}_description','widget','area(rows=10,cols=60)');
INSERT INTO meta_fields VALUES ('{$table_name}','{$table_name}_datecreated','widget','readonly');
INSERT INTO meta_fields VALUES ('{$table_name}','{$table_name}_datemodified','widget','readonly');
INSERT INTO meta_fields VALUES ('{$table_name}','{$table_name}_usercreated','widget','readonly');
INSERT INTO meta_fields VALUES ('{$table_name}','{$table_name}_usermodified','widget','readonly');
INSERT INTO meta_fields VALUES ('{$table_name}','{$table_name}_','hide_list','1');
-- INSERT INTO {$table_name} ({$table_name}_name) VALUES ('');
-- INSERT INTO meta_tables VALUES ('{$table_name}', 'hide', '1');

-- Triggers
-- For Date Modified
CREATE TRIGGER {$table_name}_moddatetime
BEFORE UPDATE ON {$table_name}
FOR EACH ROW
EXECUTE PROCEDURE moddatetime({$table_name}_datemodified);

-- For User Modified
CREATE TRIGGER insert_username_{$table_name}
BEFORE INSERT OR UPDATE ON {$table_name}
FOR EACH ROW
EXECUTE PROCEDURE insert_username({$table_name}_usermodified);


-- List view
/*
DROP VIEW {$table_name}_list;
CREATE OR REPLACE VIEW {$table_name}_list AS
SELECT {$table_name}_id, {$table_name}_name
FROM {$table_name} ORDER BY {$table_name}_id DESC;
COMMENT ON VIEW {$table_name}_list IS '{$ucfirst_table_name}';
COMMENT ON COLUMN {$table_name}_list.{$table_name}_id IS 'ID';
COMMENT ON COLUMN {$table_name}_list.{$table_name}_name IS 'Name'; 
*/
} 
);

my $text = $template->fill_in(); 
print $text;

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:


"/>