;;; rcd-db-init.el --- RCD Database Initialization and basic SQL query functions ;; Copyright (C) 2016-2020 by Jean Louis ;; Author: Jean Louis ;; Version: 1.7 ;; Package-Requires: ;; Keywords: applications ;; URL: https://gnu.support/gnu-emacs/packages/rcd-db-init-el.html ;; This file is not part of GNU Emacs. ;; This program is free software: you can redistribute it and/or ;; modify it under the terms of the GNU General Public License as ;; published by the Free Software Foundation, either version 3 of the ;; License, or (at your option) any later version. ;; ;; This program is distributed in the hope that it will be useful, but ;; WITHOUT ANY WARRANTY; without even the implied warranty of ;; MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU ;; General Public License for more details. ;; ;; You should have received a copy of the GNU General Public License ;; along with this program. If not, see . ;;; Commentary: ;; RCD Database Initialization and basic SQL query functions uses ;; PostgreSQL database backend. This may change in future to use any ;; other type of a database. ;; ;; RCD is acronym for Reach, Connect, Deliver, my personal ;; principle and formula for Wealth. ;;; Change Log: ;;; Code: ;; TODO remove the following line when emacs-libpq becomes part of GNU ELPA (add-to-list 'load-path "~/Programming/git/emacs-libpq/") (require 'pq) (defun rcd-db-connect (database &optional port host username password) "Connects to PostgreSQL database with specified parameters, by using the GNU Emacs module emasc-libpq" (let* ((port (if port port 5432)) (connection (format "dbname=%s port=%s" database port)) (connection (if host (concat connection (format " host=%s" host)) connection)) (connection (if username (concat connection (format " user=%s" username)) connection)) (connection (if password (concat connection (format " password=%s" password)) connection)) (connection (or (getenv "PG_CONNINFO") connection))) (pq:connectdb connection))) (defun rcd-sql (sql pg) "Sends SQL queries to PostgreSQL database and returns results" (condition-case err (pq:query pg sql) (error (if (string-match "^ERROR: syntax error" (cdr err)) (progn (if (fboundp 'speak) (speak (cdr err))) (message (cdr err))) ;; re-throw (signal (car err) (cdr err)))))) (defun rcd-sql-list (sql pg) "Returns list of lists instead of vectors" (let ((list '())) (dolist (i (apply 'rcd-sql (list sql pg)) (reverse list)) (cond ((eq (type-of i) 'vector) (push (append i '()) list)) (t (push i list)))))) (defun rcd-sql-first (sql pg) "Returns first entry from SQL query" (car (apply 'rcd-sql (list sql pg)))) (defun rcd-sql-list-first (sql pg) "Returns first item of the list of lists instead of vectors" ;; TODO this is not verified that it works well as intended (let ((list (rcd-sql-first sql pg))) (append list '()))) (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; DROP VIEW " table "_rcd; CREATE OR REPLACE VIEW " table "_rcd AS SELECT concat(" table "_id, ' '," table "_name) AS id FROM " table "; GRANT SELECT ON " table "_rcd 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'; */ } );") (provide 'rcd-db-init) ;;; rcd-db-init.el ends here