top of page
CerebroSQL

CerebroSQL databases

Local bases

When you first start the program, it creates 4 encrypted SQLite databases to store all information:

  • CoreDB - Core 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 for both the program core and 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.

 

Changing the master password is done 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 

Structure of the ConDB database 

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

Структура  базы CoreDB

​Структура  базы CoreDB

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

Таблицы "Project manager"
Master-data
eid
value
11
RICHEDIT
10
COMBOBOX
9
PANEL
8
BUTTON
7
RADIOGROUP
6
PAGE
5
PAGECONTROL
4
EDIT
3
LABEL
2
GROUPBOX
1
SPLITTER
User manuel
User manual tables

User document storage

U$MANUAL table - document tree

CREATE TABLE U$MANUAL (
    [ID] INTEGER PRIMARY KEY AUTOINCREMENT,
    [OID] Integer,
    [Parent] integer,
    [PFlg] integer default 0,
    [EFlg] integer default 0,
    [Caption] TEXT,
    [Body] BLOB,
    [ListKey] Text,
    [Info] Text,
    [Locked] integer default 0,
    [Version] integer default 0,
    [Updated] Timestamp default current_timestamp,
    [Creator] Text,
    [Key_find] Text,
    [Type] Text default 'USER',
    constraint pk_usr_name UNIQUE ([OID], [Parent], [Caption], [Version]),
    FOREIGN KEY ([OID]) REFERENCES C$CATALOG (id))

  • ID - ordinal number of the record

  • OID - ID of the entry in the system catalog C$CATALOG

  • Parent - the ID of the parent record

  • PFlg is a pointer to the record type. 0 - node, 1 - document

  • EFlg - document export permission flag

  • Caption - the title of the document

  • Body - the body of the document

  • ListKey - a list of name = value pairs

  • Info - information about the document

  • Locked - flag of prohibition of document editing. 0 - editing is allowed, 1 - editing is prohibited

  • Version - the version of the document

  • Updated - the date and time the document was modified

  • Creator - author of the document

  • Key_find - key for internal links to documents

  • Type - the type of the document. user or system

Table U$MANUAL_TEMPLATE - Document Templates

CREATE TABLE U$MANUAL_TEMPLATE (
    [Owner] Text,
    [Caption] Text,
    [Body] BLOB,
    unique ([Owner], [Caption])
)

  • Owner - parent record

  • Caption - the title of the document

  • Body - the body of the document template

Central Database (PostgreSQL)

Схемы центральной базы данных

audit - таблицы аудита действий пользователей

core - таблицы конфигурации

manual - схема для хранения таблиц модуля "Документация"

proj - схема для хранения таблиц модуля "Project manager"

transfer - схема для хранения данных модуля "Transfer data"

mdm - схема для хранения таблиц модуля "Master-data"

bottom of page