Implementation of Semantic Triplets in PostgreSQL database

This is attempt to implement semantic triplets in PostgreSQL database. As suggested by Karl Voit Nayuki did not mention semantic triplets in the Tag-Based File System.

I was thinking how to implement it in PostgreSQL so that I may have a table that contains relation definitions without being internally related to other tables.

Predicates table in PostgreSQL

The first thing I had to make was the table predicates as it would be referenced from semantictriplets table, it is very simple, predicate is described in the column predicates_name and may have its internal description.

-- ------------------------------------------
-- ------------ Table predicates
-- ------------------------------------------
DROP SEQUENCE predicates_id_seq;

CREATE TABLE predicates (
predicates_id SERIAL NOT NULL PRIMARY KEY,
predicates_name TEXT,
predicates_description TEXT
);
GRANT ALL ON predicates TO PUBLIC;
DROP VIEW predicates_combo;
CREATE OR REPLACE VIEW predicates_combo AS
SELECT predicates_id AS id,
predicates_name AS TEXT
FROM predicates;
GRANT SELECT ON predicates_combo TO PUBLIC;

DROP VIEW predicates_rcd;
CREATE OR REPLACE VIEW predicates_rcd AS
SELECT concat(predicates_id, ' ',predicates_name) AS id
FROM predicates;
GRANT SELECT ON predicates_rcd TO PUBLIC;

COMMENT ON TABLE predicates IS 'Predicates';
COMMENT ON COLUMN predicates.predicates_id IS 'ID';
COMMENT ON COLUMN predicates.predicates_name IS 'Name';
COMMENT ON COLUMN predicates.predicates_description IS 'Description';

CREATE UNIQUE INDEX predicates_index ON predicates ( predicates_name );

INSERT INTO meta_fields VALUES ('predicates','predicates_description','widget','area(rows=10,cols=60)');

Semantic Triplets table in PostgreSQL

The Semantic Troplets table is implemented here in PostgreSQL. It does not relate directly to any other table but to predicates and itself.

Is the date of creation of relation important? I think so.
Is it important to know when the relation have been modified?
Which user created the relation also helps in future queries.
Which user modified the relation may be also helpful.

And now comments on more important parts of this table:

subjecttriplet references other possible semantic triplets. It could be possible to expand this that it may reference groups of semantic triplets.
subjectable references any PostgreSQL table, could be people or pages or sms, thus just any table may be fundamentally related to other table. For example table countries with ID 5 being "American Samoa" may be used to say "John lives in American Samoa". Later we could relate that object to say "John who lives in American Samoa exports coffee." as there exists also he objecttable and objecttriplets.
all tables have been designed to have its unique ID number, that is how the information may be stored. But problem is that this is not a table that is directly relational to other table, it is standalone. If any ID number ever changes, it would not be able to automatically update relations. However, a trigget could be used in case that any ID number changes, so to alert user to update relations eventually.
every table should have its OID, thus OID may be used to locate the table in case its name ever changes. As names of tables are recorded in the semantictriplets table, what would happen if the name of table is ever renamed? Relation could be lost. But OID could remain the same, so we keep OID in the table to make it more future redundant.

Example would be to relate following tables and its entries with specific ID:

Table people with the ID number 11, designating John, further having more information about John. This information would be recorded in the semantictriplets table as object.
Table predicates could be used to designate a predicate such as lives or visited or has sister, even better has sibbling or introduced or some other similar predicates.
The table country could be related to John with to say how John traveled to Hungary.

Various predicates could be translated to various languages.

This type of fundamental knowledge could help define relations that would foster future knowledge use.

-- ------------------------------------------
-- ------------ Table semantictriplets
-- ------------------------------------------
DROP SEQUENCE semantictriplets_id_seq;

CREATE TABLE semantictriplets (
semantictriplets_id SERIAL NOT NULL PRIMARY KEY,
semantictriplets_datecreated TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
semantictriplets_datemodified TIMESTAMP,
semantictriplets_usercreated TEXT NOT NULL DEFAULT current_user,
semantictriplets_usermodified TEXT NOT NULL DEFAULT current_user,
semantictriplets_subjecttriplet INT4 REFERENCES semantictriplets,
semantictriplets_subjecttable TEXT,
semantictriplets_subjectoid INT4,
semantictriplets_subjectcolumn TEXT,
semantictriplets_subjectid INT4,
semantictriplets_predicates INT4 REFERENCES predicates NOT NULL,
semantictriplets_objecttriplet INT4 REFERENCES semantictriplets,
semantictriplets_objecttable TEXT NOT NULL,
semantictriplets_objectoid INT4,
semantictriplets_objectcolumn TEXT,
semantictriplets_objectid INT4 NOT NULL,
semantictriplets_description TEXT
);
GRANT ALL ON semantictriplets TO PUBLIC;
DROP VIEW semantictriplets_combo;
CREATE OR REPLACE VIEW semantictriplets_combo AS
SELECT semantictriplets_id AS id,
semantictriplets_subjecttable || ' ' ||
predicates_name emantictriplets_name || ' ' ||
semantictriplets_objecttable AS TEXT
FROM semantictriplets, predicates
WHERE predicates_id = semantictriples_predicates;
GRANT SELECT ON semantictriplets_combo TO PUBLIC;

DROP VIEW semantictriplets_rcd;
CREATE OR REPLACE VIEW semantictriplets_rcd AS
SELECT concat(semantictriplets_id, ' ',semantictriplets_name) AS id
FROM semantictriplets;
GRANT SELECT ON semantictriplets_rcd TO PUBLIC;

COMMENT ON TABLE semantictriplets IS 'Semantic Triplets';
COMMENT ON COLUMN semantictriplets.semantictriplets_id IS 'ID';
COMMENT ON COLUMN semantictriplets.semantictriplets_datecreated IS 'Date created';
COMMENT ON COLUMN semantictriplets.semantictriplets_datemodified IS 'Date modified';
COMMENT ON COLUMN semantictriplets.semantictriplets_usercreated IS 'User created';
COMMENT ON COLUMN semantictriplets.semantictriplets_usermodified IS 'User modified';
COMMENT ON COLUMN semantictriplets.semantictriplets_objecttable IS 'Object table';
COMMENT ON COLUMN semantictriplets.semantictriplets_objectoid IS 'Object OID';
COMMENT ON COLUMN semantictriplets.semantictriplets_objectcolumn IS 'Object column';
COMMENT ON COLUMN semantictriplets.semantictriplets_objectid IS 'Object ID';
COMMENT ON COLUMN semantictriplets.semantictriplets_objecttriplet IS 'Object triplet';
COMMENT ON COLUMN semantictriplets.semantictriplets_subjecttriplet IS 'Object triplet';
COMMENT ON COLUMN semantictriplets.semantictriplets_predicates IS 'Predicates';
COMMENT ON COLUMN semantictriplets.semantictriplets_subjecttable IS 'Subject table';
COMMENT ON COLUMN semantictriplets.semantictriplets_subjectoid IS 'Subject OID';
COMMENT ON COLUMN semantictriplets.semantictriplets_subjectid IS 'Subject ID';
COMMENT ON COLUMN semantictriplets.semantictriplets_subjectcolumn IS 'Subject column';
COMMENT ON COLUMN semantictriplets.semantictriplets_description IS 'Description';

CREATE UNIQUE INDEX semantictriplets_index ON semantictriplets ( semantictriplets_subjecttable, semantictriplets_predicates, semantictriplets_objecttable);

INSERT INTO meta_fields VALUES ('semantictriplets','semantictriplets_description','widget','area(rows=10,cols=60)');
INSERT INTO meta_fields VALUES ('semantictriplets','semantictriplets_datecreated','widget','readonly');
INSERT INTO meta_fields VALUES ('semantictriplets','semantictriplets_datemodified','widget','readonly');
INSERT INTO meta_fields VALUES ('semantictriplets','semantictriplets_usercreated','widget','readonly');
INSERT INTO meta_fields VALUES ('semantictriplets','semantictriplets_usermodified','widget','readonly');
INSERT INTO meta_fields VALUES ('semantictriplets','semantictriplets_','hide_list','1');

-- Triggers
-- For Date Modified
CREATE TRIGGER semantictriplets_moddatetime
BEFORE UPDATE ON semantictriplets
FOR EACH ROW
EXECUTE PROCEDURE moddatetime(semantictriplets_datemodified);

-- For User Modified
CREATE TRIGGER insert_username_semantictriplets
BEFORE INSERT OR UPDATE ON semantictriplets
FOR EACH ROW
EXECUTE PROCEDURE insert_username(semantictriplets_usermodified);

Functions for Semantic Triplets

(defun cf-predicates-add (&optional predicate description)
  "Insert PREDICATE with DESCRIPTION into `predicates' database table."
  (interactive)
  (let ((predicate (or predicate (rcd-repeat-until-not-empty-string 'rcd-ask "Predicate name: ")))
	(description (or description (rcd-repeat-until-not-empty-string 'rcd-ask "Predicate description: "))))
    (rcd-sql "INSERT INTO predicates (predicates_name, predicates_description) VALUES ($1, $2)" cf-db predicate description)))

Related hyperdocuments

Related hyperdocument tags

Emacs Postgresql emacs-lisp semantic-triplets asciidoc

GNU Free Documentation License

Copyright © 2021-05-04 01:10:04.29659+03 by Jean Marc Louis. Permission is granted to copy, distribute and/or modify this document under the terms of the GNU Free Documentation License, Version 1.3 or any later version published by the Free Software Foundation; with no Invariant Sections, no Front-Cover Texts, and no Back-Cover Texts. A copy of the license is included in the section entitled "GNU Free Documentation License"