Топ-100
 
CerebroSQL

CerebroSQL databases

Local databases

On first launch, the program creates 4 encrypted SQLite databases to store all information:

  • CoreDB - 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.

 

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

The table contains 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 is the user's password. Stored in ASCII code due to the peculiarities of storing data in some encodings in SQLite databases.

  • charset - encoding

MSSQL_LIST

 

The table contains 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 -  synonym for connection. Displayed in connection selection forms. Unique value.

  • groupname  - 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 -  the port on which the MS SQL Server DBMS instance is running.

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

  • username -  username in 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

The table stores information required 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 to  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

MYSQL_LIST

The table stores information required to connect to MySQL databases .

CREATE TABLE MYSQL_LIST (
     [id]       INTEGER PRIMARY KEY AUTOINCREMENT,
     [alias]     TEXT,
     [groupname]  TEXT,
     [servername] TEXT,
     [serverport] TEXT,
     [dbname]     TEXT,
     [username]   TEXT,
     [userpasswd] TEXT,
     [charset]   TEXT,
     [compress]   INTEGER DEFAULT 0,
     [ssl]      INTEGER DEFAULT 0,
     [timedout]   TEXT,
     [readtime]   TEXT,
     [writetime]  TEXT,
     [resmode]   TEXT,
     [tiny]      TEXT,
     [meta]      TEXT,
     [ssl_use]   TEXT,
     [ssl_ca]     TEXT,
     [ssl_cert]   TEXT,
     [ssl_key]   TEXT,
     UNIQUE (alias, groupname)
)

  • id - record number in the table

  • alias - connection alias

  • groupname - group name (used to group entries in the connection manager)

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

  • serverport - port of the MySQL listener on the server

  • dbname - the name of the database to connect to

  • username - username

  • userpasswd - user password (stored in ASCII code with delimiter |)

  • charset - connection encoding

  • compress - compress traffic

  • ssl_use - connect using SSL certificate

  • ssl_ca is the path to the certification authority file.

  • ssl_cert is the path to the certificate file.

  • ssl_key - path to the key file.

Fields  ssl, timedout, readtime, writetime, resmode, tiny are not used

ORACLE_LIST

The table stores information required to connect to Oracle databases.

CREATE TABLE ORACLE_LIST (
  [id]      INTEGER PRIMARY KEY AUTOINCREMENT,
  [alias]     TEXT,
  [groupname] TEXT DEFAULT 'DEFAULT',
  [db_alias]  TEXT,
  [username]  TEXT,
  [passwd]   TEXT,
  [tns_block] TEXT,
  [ocipath]   TEXT,
  [tns_admin] TEXT,
  [nls_lang]  TEXT,
  [comment]   TEXT,
  [osauth]   INTEGER DEFAULT 0,
  [mode]     TEXT,
  [odbcuse]   INTEGER,
  [odbcname]  TEXT,
  [current]   INTEGER DEFAULT 0,
  UNIQUE (alias, groupname)
)

  • id - ordinal number of the record

  • alias - connection alias

  • groupname group name (used to group entries in the connection manager)

  • db_alias - connection alias from the tnsnames file or connection string in the format server: port / service_name

  • username - username

  • passwd - user password ( stored in ASCII code with delimiter | )

  • tns_block - block for connecting to the database (informational)

  • ocipath - path to the oci.dll file 

  • tns_admin - path to the directory where the tnsnames.ora file is located

  • nls_lang - connection encoding

  • comment - comment

  • osauth - OS account authentication

  • mode - connection mode (Normal, sysdba, ...)

  • odbcuse - a flag indicating the type of driver used, 0 - Oracle, 1 - ODBC

  • odbcname - ODBC connection name

  • current - a flag indicating the default connection for connecting to the database by the main monitoring core at the start of the program

 

​Структура  базы 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

 
 
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 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"