Топ-100
 
CerebroSQL

SQL query editor for SQLite databases

Не нашли нужных функций? Пишите в комментариях на форуме и мы добавим нужный функционал

Для работы с СУБД, установка дополнительного ПО не требуется. Все необходимые библиотеки идут в комплекте

 

Создание нового листа для работы с SQLite

 

  1. Press the button "New list" SQLite ""

  2. File - New - SQLite

  3. Keyboard shortcut: Shift + F2

Create new page for database SQLite.jpg
 

Подключение к базам SQLite

Connecting to databases is done in two ways:

  • Using the "attach" command

  • By creating static connections in the connection manager for quick connection to databases

 

Sheet structure

 
Connection manager

In the meantime, there is no need to worry about it. ”

SQLite Database Connection Wizard for Fast Connection

In the meantime, there is no need to worry about it. ”

SQLite connection manager.jpg

Database list

List of previously saved connections to SQLite databases

Creating a new connection

  • Attach as - alias under which the database will be connected

  • Database file - path to the SQLite database file.

If the database is encrypted,

  • Mode - encryption protocol

  • Database password - password from the database

The program implements the ability to backup SQLite databases from the program interface after connecting it to the sheet, to use the functionality, fill in the fields:

  • Specify the path to the backup file in the "Backup file" field

If you need to encrypt the backup,

  • Mode (bkp) - encryption mode

  • Database password (bkp) - password

  • Save the connection by clicking the "Save" button

Attach database
 

Manager for connecting saved databases to the current sheet.

By default, there are 4 program bases in the list

  • ASH - storage of information on the activity of sessions in the database, which is monitored by the main core of the program

  • CONNECT - storage for tables with connection data with different DBMS, which the programs support

  • CORE - the main base of the program

  • EXIST - a database that stores information on monitoring the operation of bases through the mechanism for monitoring the operation of "Clouds"

You can connect to any number of bases at the same time

  • Check the boxes next to the required bases

  • Press the "Attach" button

SQLite attach database file.jpg
 

Object tree

 

  • ATTACH - list of database aliases connected to the editor sheet

  • TABLE - a list of tables in the connected databases. The name consists of a database alias and a table name

  • INDEX - a list of indexes in the connected databases. The name consists of a database alias and an index name

  • VIEW - a list of views in the connected databases. The name consists of a database alias and an index name

  • TRIGGER - a list of triggers in the connected databases. The name consists of a database alias and an index name

  • PARAMETER - parameters of the selected base. The base is selected from the "Database attached list" drop-down list

  • HELP - a description of the commands supported by the SQLite base. Loaded separately.

 
Node "ATTACH"

Contains a list of connected bases.

CerebroSQL SQLite tree object - attach.j

Menu:

  • Sweep - Perform an optimization on the selected SQLite database.

  • Backup - back up the SQLite database. The operation is performed if the backup parameters were specified while saving the connection.

  • Show all SQL - generate the code of all objects in the database

  • Detach - detach SQLite database from the current sheet

 
TABLE Node

List of tables in connected databases

CerebroSQL SQLite tree object - table.jp

-> Request to retrieve data <-

PRAGMA database_list;

SELECT Upper (name) "name" FROM $$ATTACHDB.sqlite_master where Upper (type) = 'TABLE' order by Upper (name);

-----

Menu:

  • Reindex - rebuild all indexes based on table columns

-> Command <-

reindex <Table_name>

-----

  • Get DDL - generate table creation code

-> Request to retrieve data <-

select sql from $$ATTACHDB.sqlite_master where Upper (name) = Upper ($$TABLE_NAME)

-----

  • SQL [Select] - generate a query code for the table and add to the editor

  • SQL [Insert] - generate a command code for inserting data into a table and add it to the editor

  • SQL [Delete all rows] - generate a command code to delete all rows in the table and add to the editor

  • SQL [Rename table] - generate a command code to change the table name and add to the editor

  • SQL [Drop] - generate a table drop code and add to the editor

  • Data - show table data

Column Node

List of table columns

-> Request to retrieve data <-

PRAGMA $$ATTACHDB.table_info ($$TABLE_NAME)

-----

Index Node

List of indexes by table columns

-> Request to retrieve data <-

select name from $$ATTACHDB.sqlite_master where Upper (tbl_name) = Upper ($$TABLE_NAME) and Upper (type) = 'INDEX' order by 1

-----

Child node "Details"

Information about the columns by which the index is built

> Query to retrieve data <-

pragma $$ATTACHDB.index_info ($$INDEX_NAME)

-----

 

Trigger Node

List of triggers on a table

> Query to retrieve data <-

select name from $$ATTACHDB.sqlite_master where Upper (tbl_name) = Upper ($$TABLE_NAME) and Upper (type) = 'TRIGGER' order by 1

-----

 
INDEX node

List of indexes in all connected databases

[CerebroSQL] SQLite list index.jpg

-> Request to retrieve data <-

PRAGMA database_list;

select name from $$ATTACHDB.sqlite_master where U Upper (type) = 'INDEX' order by 1

-----

 
VIEW node

List of views in all connected databases 

[CerebroSQL] SQLite list view.jpg

-> Request to retrieve data <-

PRAGMA database_list;

select name from $$ATTACHDB.sqlite_master where U Upper (type) = 'VIEW' order by 1

-----

Menu:

  • Source - generate the code for creating a view and add it to the editor

-> Request to retrieve data <-

select sql from $$ATTACHDB.sqlite_master where Upper (name) = Upper ($$VIEW_NAME)

-----

  • Data - show view data

 
PARAMETER node

Database parameters 

[CerebroSQL] SQLite database parameter.jpg

List of executable pragma :

PRAGMA automatic_index;

PRAGMA busy_timeout;
PRAGMA cache_size;
PRAGMA cache_spill;
PRAGMA checkpoint_fullfsync;
PRAGMA collation_list;
PRAGMA compile_options;
PRAGMA defer_foreign_keys;
PRAGMA encoding;
PRAGMA foreign_keys;
PRAGMA freelist_count;
PRAGMA fullfsync;
PRAGMA integrity_check;
PRAGMA journal_mode;
PRAGMA journal_size_limit;
PRAGMA locking_mode;
PRAGMA page_size;
PRAGMA max_page_count;
PRAGMA page_count;
PRAGMA query_only;
PRAGMA quick_check;
PRAGMA read_uncommitted;
PRAGMA recursive_triggers;
PRAGMA reverse_unordered_selects;
PRAGMA schema_version;
PRAGMA user_version;
PRAGMA secure_delete;
PRAGMA soft_heap_limit;
PRAGMA synchronous;

 

Control buttons

[CerebroSQL] SQLite list control button.

 

  • Execute current SQL - Execute the currently selected query or all queries in the sheet

Execute - similar to clicking a button

To run the script from a file - execute the script from disk. The script is not loaded into the editor.

Check the script file for validate - check the script on disk for correctness without executing it

Show a list of command for execute - parse commands in the editor into execution blocks. The internal code and text of a single command is displayed

In the meantime, there is no need to worry about it. ”

CerebroSQL for Oracle - execute menu
  • Commit - commit changes

  • Rollback - roll back the changes made to the data by the last requests

  • Stop execute - stop execution of the current request

  • Close open cursor - close the current dataset

  • Show plan - show the execution plan of the selected query

In the meantime, there is no need to worry about it. ”

[CerebroSQL] SQLite view query plan.jpg

 

-> Request to retrieve data <-

EXPLAIN QUERY PLAN <Query text>

-----

  • Export data in file - exporting grid data (visible part of the data) to a file on disk in:

CerebroSQL for Oracle - export data

Export data in XLS - export data to a file in XLS format

Export data in TXT - export data to a file in TXT format

Export data in CSV - export data to a file in CSV format

Export data in HTML - export data to a file in HTML format

Export data in RTF - export data to a file in RTF format

 

  • Save current list in file - save the contents of the current sheet to a file on disk

  • Open file - open file manager

CerebroSQL for Oracle - open file

 

  • List saving code - open the manager for creating code blocks. Code blocks are code constructs saved in the program, which are accessed from the drop-down menu of the button

CerebroSQL - code block
 

 

Creating a block of code

  1. Press the "New" button

  2. From the drop-down list "Parent name" select the name of the root node. The root node is also the parent of the menu. To add a new entry to the list, click the "Add new parent" button and enter new values

  3. From the drop-down list "Page type" select the type of DBMS for which the code block should be displayed in the list

  4. In the "Code" field, enter the text of the code block, this text is added to the editor

  5. Click the "Save" button

CerebroSQL - code block saved
[CerebroSQL] Query block add list.jpg

 

  • Project manager - open the project manager

Workspace

 

This is a field on the sheet for entering queries / commands.

Splitting the code into its constituent parts

The engine of the query editor, before executing commands, parses the code, identifying the constituent parts.

To separate the code, special characters are used (; - semicolon, / - slash, go - keyword on a separate line).

The program supports two code syntaxes:

  • Classic Oracle syntax: a symbol is used to separate commands from each other; (semicolon) except for the create, begin, declate commands - these commands are separated from others by the / (slash) character on a separate line

  • MSSQL Server syntax: use the go keyword on a separate line to separate commands from each other

Any of the two syntaxes are allowed, including both at the same time

 
 
[CerebroSQL] PostgreSQL query execute.jp
 

 

Logging of operations performed

After execution, the text of any command/query in the SQL language is saved to the local storage (the Core database is the cer_history_query table). The following is recorded in the database: the text of the request/command of the program actually executed by the kernel, the execution status, the execution time, the number of changed lines, the DB messages (output)

CerebroSQL for Oracle - save query execute

 

In addition to storing data in the database, the progress of each command execution is recorded on the "Messages" tab of the sheet: the status of command execution, execution time, line numbers in the editor on which the executed command is located is displayed. These tabs are cleared before running command execution

CerebroSQL for Oracle - query executing messages
 

 

The icons on the left side of the window show the progress of execution (executed commands, current command, pending execution, query execution error, line with an error in the query)

Code hinter

The code hint helps to speed up the writing of a query by suggesting the names of objects and columns.

For schemes - all objects of the scheme are displayed

[CerebroSQL] code compliter 1.jpg

 

For aliases of tables, views - a list of columns

[CerebroSQL] code compliter 2.jpg

 

As part of the object name - objects in the database are displayed whose name begins with the entered text. The tooltip is displayed by the key combination Crtl + Space

CerebroSQL for Oracle - code compliter l
 
 
Query aliases

In the program for queries, you can assign an alias and subsequently execute a query on it, for details, see the section "Query Alias ​​Manager"

Workspace menu
  • Copy - copy the selected text to the clipboard

  • Past - paste the contents of the clipboard into the editor window

  • Cut - cut the selected text

  • SelectAll - select all text on the stage

  • View table - viewing detailed information on the selected table

  • Get DDL - show the code for creating the selected table

  • Show command help - show help for the selected command

 
CerebroSQL for Oracle - Show selected command
[CerebroSQL] PostgreSQL help command.jpg

 

  • Show object data - show data of the selected table, view, synonym

  • Project manager -> Add object - create a page for the highlighted code in the current project by default

CerebroSQL for Oracle - add code in project

 

  • Project manager -> Search object card - find the card of the selected object in the current project by default

  • Comment -> Add current line - add comment characters to the beginning of the current line

  • Comment -> Remove current line - remove comment characters at the beginning of the current line

  • Comment -> Add selected text - add comment characters to the beginning of each of the selected lines

  • Comment -> Remove selected text - remove comment characters at the beginning of each of the selected lines