CerebroSQL databases

At the first start, the program creates 4 SQLite databases to store all the information:

• CoreDB - The main storage, contains information necessary for the operation of program modules

• ASHDB - Storage for the history of active sessions in the database. Data is collected every second.

• ConDB - Storage of tables with information for connecting to databases both for the core of the program and for the query editor

• ExistDB - Storage for information collected by the built-in monitoring system (obsolete, not used in the current version)

​

All databases are encrypted. The algorithm and master password are specified by the user at the first start of the program.

 

The master password is changed in the program settings.

To connect to any of the databases without entering a password, you can use the "Query Editor for SQLite Databases"

 

ConDB database structure

The database stores information necessary for connecting to databases by program modules.

 

POSTGRES_LIST table - the table stores information required to connect to PostgreSQL databases .

​

CREATE TABLE POSTGRES_LIST (
    [id] INTEGER PRIMARY KEY AUTOINCREMENT,
    [alias] text,
    [groupname] text,
    [servername] text,
    [serverport] text,
    [dbname] text,
    [username] text,
    [userpasswd] text,
    [charset] text,
    unique ([alias])
)

​

• ID - record number in the table

• alias is a synonym for connection. Displayed in connection selection forms. Unique value.

• groupname is the name of the group for sorting and grouping data. Used in the connection wizard.

• servername - DNS name of the server or its IP address.

• serverport is the port on which the PostgresSQL DBMS instance is running. The default is 5432.

• dbname - the name of the database to which you want to connect.

• username - username in the PostgreSQL DBMS under which the connection is made.

• userpasswd - user password. Stored in ASCII code due to the peculiarities of storing data in some encodings in SQLite databases.

• charset - encoding

​

​

MSSQL_LIST table - the table stores information required to connect to MS SQL Server databases .

​

CREATE TABLE MSSQL_LIST (
    [id] INTEGER PRIMARY KEY AUTOINCREMENT,
    [alias] TEXT,
    [groupname] TEXT,
    [servername] text,

    [serverport] text,

    [dbname] Text,
    [auth] INTEGER default 0,
    [username] Text,
    [userpasswd] Text,
    [color_page] Text,
    UNIQUE ([alias])

)

• id - record number in the table

• alias is a synonym for connection. Displayed in connection selection forms. Unique value.

• groupname is the name of the system to which the MS SQL Server belongs. The value in the field is used to group data in lists.

• servername - DNS name of the server or its IP address.

• serverport is the port on which the MS SQL Server DBMS instance is running.

• auth - authentication method, 0 - domain, 1 - login / password.

• username is the name of the user in the MS SQL Server DBMS under which the connection is made.

• userpasswd - user password. Stored in ASCII code due to the peculiarities of storing data in some encodings in SQLite databases.

• color_page is deprecated.

​

​

SQLITEDB_LIST table - the table stores information needed to connect to SQLite databases.

​

CREATE TABLE SQLITEDB_LIST (
    [id] INTEGER PRIMARY KEY AUTOINCREMENT,
    [attach] TEXT,
    [file] TEXT,
    [mode] TEXT,
    [passwd] TEXT,
    [bkp_file] TEXT,
    [bkp_mode] TEXT,
    [bkp_passwd] TEXT,
    [comment] TEXT,
    UNIQUE ([attach])

)

• id - record number in the table

• attach - alias under which the database is connected in the editor. Unique value.

• file - full path to the SQLite database file.

• mode - database encryption protocol

• passwd - password to the database. Stored in ASCII code due to the peculiarities of storing data in some encodings in SQLite databases.

• bkp_file - full path to the database backup file. The backup is performed from the Query Editor interface.

• bkp_mode - backup database encryption protocol

• bkp_passwd - database password. Stored in ASCII code due to the peculiarities of storing data in some encodings in SQLite databases.

• comment - comment

​

​

CoreDB database structure

The main repository of the program.

​

Table C$CATALOG - storage of information on tree nodes, menus, lists for different program modules.

​

CREATE TABLE C$CATALOG (
    [id] INTEGER PRIMARY KEY AUTOINCREMENT,
    [name] Text,
    [value] Text,
    [type] Text,
    [updated] Timestamp default current_timestamp,
    [modify] Text,
    [comment] Text,
    UNIQUE (name, value)

)

​

• id - record number in the table

• name - the name of the entry

• value - value

• type - module code

• updated - date and time when the entry was created / updated

• modify - who created / updated the entry

• comment - comment

​

Project manager tables

​

Table PJ$LIST - list of projects

​

CREATE TABLE PJ$LIST (
    [id] INTEGER PRIMARY KEY AUTOINCREMENT,
    [create_dttm] timestamp default current_timestamp,
    [update_dttm] timestamp,
    [name] Text,
    [owner] Text,
    [caption] Text,
    [status] integer default 1,
    [creator] Text,
    [defrdbms] integer,
    unique ([name])
)

​

• id - record number in the table

• create_dttm - date and time when the entry was created

• update_dttm - date and time when the record in the table was updated

• name - the name of the project

• owner - the owner of the project, by default the Program owner from the program settings

• caption - title, short description

• status - project status, 1 - active, 2 - completed

• creator - who created the project, always the Program owner from the program settings

• defrdbms - flag indicating that the project is the default project for sheets in the query editor. The flag is used to pre-fill the fields of the save code

  • 1 - Oracle

  • 2 - PostgreSQL

  • 3 - MySQL

  • 4 - SQLite

  • 5 - MS SQL Server

​

Master-data (MDM) tables

A structured storage of any information that can be presented in the form of cards. Cards are created separately in the designer

​

NS $ REFERENCEBOOK table - list of registered user directories

CREATE TABLE NS$REFERENCEBOOK (
    [id] INTEGER PRIMARY KEY AUTOINCREMENT,
    [tablename] Text,
    [comment] Text,
    [created_dttm] timestamp default current_timestamp
)

• id - record number in the table

• tablename - the name of the reference table

• comment - custom comment for the table

• created_dttm - date and time when the table was created

​

​

NS$PAGEELEMENTS table - page elements

CREATE TABLE NS$PAGEELEMENTS (
    [id] INTEGER PRIMARY KEY AUTOINCREMENT,
    [oid] integer,
    [name] Text,
    [ename] Text,
    [eid] integer,
    [position] integer,
    [pid] integer
)

• id - record number in the table

• oid - id of the record from C$CATALOG (page name)

• name - the name of the element

• ename - the name of the element, generated randomly when the element is created

• eid - internal element number

• position - the position of the element at the current level, used as a sorting key when building the page

• pid - the id of the parent record from the current table

​

NS$PAGEELEMENTPARAM table - item parameters

CREATE TABLE NS$PAGEELEMENTPARAM (
    [ename] text,
    [name] Text,
    [value] Text
)

• ename - element name ( NS$PAGEELEMENTS.ename)

• name - parameter name

• value - value

​

NS$PAGEELEMENTDEF table - default values ​​for elements

CREATE TABLE NS$PAGEELEMENTDEF (
    [ename] Text,
    [value] Text,
    [is_sql] integer default 0,
    [sql] Text
)

• ename - element name ( NS$PAGEELEMENTS.ename)

• value - value

• is_sql - flag indicating to get a list of default values ​​using an SQL query (0 - value, 1 - SQL query)

• sql - SQL query text to get a list of default values ​​for an element

​

NS$TREE table - directory object tree

CREATE TABLE NS$TREE (
    [id] INTEGER PRIMARY KEY AUTOINCREMENT,
    [oid] integer,
    [pid] integer,
    [page_name] Text,
    [pageid] integer,
    [flg] integer
)

id - ordinal number of the record

oid - id of the record from C$CATALOG (hostname), -1 if page

pid - id of parent node from NS$TREE

page_name - page name

pageid - page ID from NS$PAGEELEMENTS

flg - flag indicating the post type (1 - node, 2 - page)

​

NS$LISTDATA table - values ​​of elements of saved pages

CREATE TABLE NS$LISTDATA (
    [oid] integer,
    [pageid] integer,
    [ename] Text,
    [eid] integer,
    [evalue] Text
)

oid - id of the record from NS$TREE

pageid - page ID from NS$PAGEELEMENTS

ename - element name (NS$PAGEELEMENTS.ename)

eid - element code

evalue - field value

​

EID - values