Central Files: Preparing the GNU Emacs skeleton for PostgreSQL tables

To easier create PostgreSQL database tables for personal information management system by the name Central Files we will use the built-in GNU Emacs feature named Skeleton.

You may find everything about it on EmacsWiki and in the Info page about Autotype.

In general it will help you create new database tables by hand, then when you need it. You may be real estate manager, you may want to create database table for real estate properties. You may be boat seller, you may wish to create database table for boats. Anything is possible

Here is an example:

(define-skeleton hello-world-skeleton
  "Write a greeting"
  "Type name of user: "
  "hello, " str "!")

That creates the interactive function named hello-world-skeleton and by doing M-x hello-world-skeleto it will ask you to type the name of a user. It will then insert the template into the buffer with the output being: hello, John!.

As we will be creating tables for PostgreSQL we do not wish to get into repetitive actions, we have GNU Emacs editor, the skeleton functions for small templates, and we can make the process of table creation rapid.

GNU Emacs skeleton for creation of PostgreSQL tables

Here is the skeleton template function for cf-sql-table. It will ask you for the table name and it will insert the SQL into buffer for your further editing. You will spare a lot of time if you are designing your own tables.

(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'; 
*/
}
);")

Example SQL output of GNU Emacs skeleton `cf-sql-table`

When you run M-x cf-sql-table you will be asked for Table name:, in this case you could call it continents. We will create few continents in the table.

-- ------------------------------------------
-- ------------ Table continents
-- ------------------------------------------
DROP SEQUENCE continents_id_seq;

CREATE TABLE continents (
continents_id SERIAL NOT NULL PRIMARY KEY,
continents_datecreated TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
continents_datemodified TIMESTAMP,
continents_usercreated TEXT NOT NULL DEFAULT current_user,
continents_usermodified TEXT NOT NULL DEFAULT current_user,
continents_name TEXT,
continents_title TEXT,
continents_description TEXT,
continents_ TEXT
);
GRANT ALL ON continents TO PUBLIC;
DROP VIEW continents_combo;
CREATE OR REPLACE VIEW continents_combo AS
SELECT continents_id AS id,
continents_name AS TEXT
FROM continents;
GRANT SELECT ON continents_combo TO PUBLIC;
COMMENT ON TABLE continents IS 'Continents';
COMMENT ON COLUMN continents.continents_id IS 'ID';
COMMENT ON COLUMN continents.continents_datecreated IS 'Date created';
COMMENT ON COLUMN continents.continents_datemodified IS 'Date modified';
COMMENT ON COLUMN continents.continents_usercreated IS 'User created';
COMMENT ON COLUMN continents.continents_usermodified IS 'User modified';
COMMENT ON COLUMN continents.continents_hid IS 'HID';
COMMENT ON COLUMN continents.continents_name IS 'Name';
COMMENT ON COLUMN continents.continents_title IS 'Title';
COMMENT ON COLUMN continents.continents_description IS 'Description';
COMMENT ON COLUMN continents.continents_IS '';

CREATE UNIQUE INDEX continents_index ON continents ( continents_weekend );

INSERT INTO meta_fields VALUES ('continents','continents_description','widget','area(rows=10,cols=60)');
INSERT INTO meta_fields VALUES ('continents','continents_datecreated','widget','readonly');
INSERT INTO meta_fields VALUES ('continents','continents_datemodified','widget','readonly');
INSERT INTO meta_fields VALUES ('continents','continents_usercreated','widget','readonly');
INSERT INTO meta_fields VALUES ('continents','continents_usermodified','widget','readonly');
INSERT INTO meta_fields VALUES ('continents','continents_','hide_list','1');
-- INSERT INTO continents (continents_name) VALUES ('');
-- INSERT INTO meta_tables VALUES ('continents', 'hide', '1');

-- Triggers
-- For Date Modified
CREATE TRIGGER continents_moddatetime
BEFORE UPDATE ON continents
FOR EACH ROW
EXECUTE PROCEDURE moddatetime(continents_datemodified);

-- For User Modified
CREATE TRIGGER insert_username_continents
BEFORE INSERT OR UPDATE ON continents
FOR EACH ROW
EXECUTE PROCEDURE insert_username(continents_usermodified);


-- List view
/*
DROP VIEW continents_list;
CREATE OR REPLACE VIEW continents_list AS
SELECT continents_id, continents_name
FROM continents ORDER BY continents_id DESC;
COMMENT ON VIEW continents_list IS 'Continents';
COMMENT ON COLUMN continents_list.continents_id IS 'ID';
COMMENT ON COLUMN continents_list.continents_name IS 'Name'; 
*/
}
);

In the next step you have to edit the table for real usage

A lot of text have been cut from the above skeleton template to get this simple table definition here.

CREATE TABLE public.continents (
    continents_id SERIAL NOT NULL PRIMARY KEY,
    continents_name TEXT NOT NULL,
    continents_areasqkm NUMERIC,
    continents_description TEXT
);

COMMENT ON TABLE public.continents IS 'Continents';

COMMENT ON COLUMN public.continents.continents_id IS 'ID';
COMMENT ON COLUMN public.continents.continents_name IS 'Name';
COMMENT ON COLUMN public.continents.continents_areasqkm IS 'Area km^2';
COMMENT ON COLUMN public.continents.continents_description IS 'Description';

Worst is over

While there are some technicalities to learn here, they are really not much. And it will be worth for anybody who wish to easier manage personal information by using the database. It will empower you to expand your own database as you wish, when you wish.

In the next step we will populate the table continents with some usable values. We do not need all of the continents, if you wish to enter North Pole or South Pole, you are welcome, it can be you know people who are scientists or otherwise dwell there for whatever reasons.

Following may be enough for my personal needs. Remember, it is about personal information management, you should be able to adapt your information as you wish.

INSERT INTO public.continents (continents_id, continents_name, continents_areasqkm, continents_description) VALUES (1, 'Europe', 10180000, NULL);
INSERT INTO public.continents (continents_id, continents_name, continents_areasqkm, continents_description) VALUES (2, 'Africa', 30370000, NULL);
INSERT INTO public.continents (continents_id, continents_name, continents_areasqkm, continents_description) VALUES (3, 'North America', 24709000, NULL);
INSERT INTO public.continents (continents_id, continents_name, continents_areasqkm, continents_description) VALUES (4, 'South America', 17840000, NULL);
INSERT INTO public.continents (continents_id, continents_name, continents_areasqkm, continents_description) VALUES (5, 'Asia', 44579000, NULL);
INSERT INTO public.continents (continents_id, continents_name, continents_areasqkm, continents_description) VALUES (6, 'Australia', 8600000, NULL);

You may ask yourself why “continents”?

We are creating a relational database, so entries are related to each other. Person is related often to a city, city is related to country, countries are related to continents.

Apparently you may think that you will never need to use continents, but it can become usable, you can as well skip the creation of such table or you could skip using it even if it is created in the database.

In the next step we will create some countries.

How to insert new line in Emacs editor? - Several few ways are explained here on how to insert a new line before the current line while editing text with Emacs editor.

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