--
-- atom.sql
-- Version 3.1 of the Postgres database schema for the AtomSpace.
--
-- Changes since version 3.0:
-- * Add SEQUENCE's for multi-user uuid and vuid alloc's.
--
-- Changes since version 2.0:
-- * Added support for generic values.
-- * TruthValues now treated as generic Values.
--
-- Changes since version 1.0:
-- * Added support for multiple atomspaces.
--
-- -----------------------------------------------------------
-- Table showing inheritance relationship between atomspaces.
-- Atomspaces have UUID's identifying them. Top-level atomspaces have
-- a UUID of zero. Otherwise, all atomspaces have some (other)
-- atomspace as a parent.
CREATE TABLE Spaces (
space BIGINT PRIMARY KEY,
parent BIGINT
);
-- Space 0 is used primarily (only) in testing of raw atoms.
INSERT INTO Spaces VALUES (0,0); -- no space defined; raw atoms.
INSERT INTO Spaces VALUES (1,1); -- default root
-- -----------------------------------------------------------
-- An SQL table representation for OpensCog Atoms
--
-- This contains both the Node and Link types; having a single table
-- to contain both seemed easier than having two tables, one for Nodes
-- and one for Links. In particular, forcing the UUID to be unique is
-- much easier when there is only one table.
CREATE TABLE Atoms (
-- The uuid maps to the atom handle.
-- Must be unique, and must be non-null. Ergo, primary key.
-- Use 64-bit int to match the C++ range.
uuid BIGINT PRIMARY KEY,
-- The atomspace that this atom belongs to.
space BIGINT REFERENCES spaces(space),
-- Atom type, e.g. Link, Node, etc.
type SMALLINT,
-- distance from this link to farthest Node in the outgoing
-- set of this atom.
-- height of Nodes is by definition zero.
-- height of Links containing only nodes is one, etc.
height SMALLINT,
-- The node name, non-empty only for nodes
name TEXT,
-- An array of the outgoing edges; non-empty only for links
-- The ELEMENT REFERENCES has not been implemented yet.
outgoing BIGINT[], -- ELEMENT REFERENCES uuid,
-- Force the uniqueness of atoms!!
-- This has the side-effect of creating indexes for fast lookup by
-- name or by outgoing set.
UNIQUE (type, name),
UNIQUE (type, outgoing)
);
-- Incoming set index. Very important when asking for the incoming set
-- of some atom. Heavily used to accomplish atom deletion.
CREATE INDEX incoming_idx on Atoms USING GIN(outgoing);
-- -----------------------------------------------------------
-- An SQL table representation for opencog Valuations
--
CREATE TABLE Valuations (
-- The key for this value
key BIGINT REFERENCES Atoms(uuid),
-- The Atom to which this value applies
atom BIGINT REFERENCES Atoms(uuid),
-- Value type, e.g. FloatValue, StringValue, etc.
type SMALLINT,
-- An array of values associated with the (key,atom) pair
-- Only one of the three of float, or string or link should be
-- non-empty; the other two should be empty. We could have three
-- different tables, one each for each of these, but then the
-- UNIQUE constraint is harder to force.
-- The linkvalue should be an array of vuid's from the Values
-- table. The "ELEMENT REFERENCES" would do the trick, but it's
-- not yet implemented in postgres.
floatvalue DOUBLE PRECISION[],
stringvalue TEXT[],
linkvalue BIGINT[], -- ELEMENT REFERENCES Values(vuid),
UNIQUE (key, atom)
);
-- Index for the fast lookup of all valuations for a given atom.
CREATE INDEX ON Valuations (atom);
-- Index for the fast lookup of all valuations with a given key.
-- This does not seem to be needed, right now...
-- CREATE INDEX ON Valuations (key);
-- A recursive overflow table, if recursive values did not directly
-- fit into the Valuation table.
CREATE TABLE Values (
-- The unique ID for this value
vuid BIGINT PRIMARY KEY,
-- Value type, e.g. FloatValue, StringValue, etc.
type SMALLINT,
-- An array of values associated with the vuid.
-- Only one of the three of float, or string or link should be
-- non-empty; the other two should be empty.
-- The linkvalue should be an array of vuid's for other rows
-- in this table. The "ELEMENT REFERENCES" would do the trick,
-- but it's not yet implemented in postgres.
floatvalue DOUBLE PRECISION[],
stringvalue TEXT[],
linkvalue BIGINT[] -- ELEMENT REFERENCES Values(vuid)
);
-- -----------------------------------------------------------
-- Table associating type names to stored integer values. The list of
-- type names and numbers may differ from one version of the opencog
-- server to another; thus, we need to convert from type names, to
-- numbers.
CREATE TABLE TypeCodes (
type SMALLINT UNIQUE,
typename TEXT UNIQUE
);
-- -----------------------------------------------------------
-- Number sequence generators, so that multiple users can share the
-- Atoms table and the Values table, and issue uuid's, vuid's uniquely,
-- without colliding with one-another.
--
-- IMPORTANT: the number '400' is hard-coded in the C++ code in several
-- places, and it MUST NOT BE CHANGED, without also changing ALL of the
-- right places in the C++ code!
CREATE SEQUENCE uuid_pool START WITH 1 INCREMENT BY 400;
CREATE SEQUENCE vuid_pool START WITH 1 INCREMENT BY 400;
-- -----------------------------------------------------------