Applications are Evil DB Brother Help

Table of contents


1. Introduction

DB Brother is a tool for exploring and modifying SQLite databases. It integrates with Windows shell and uses ordinary files to represent database objects and queries.

The following table summarizes the file types implemented by DB Brother:

File extension File contents What is displayed in the File Explorer preview pane File usage
.dbb-info Database file path; view and export options; File Explorer properties query The contents of the file Show database objects, create queries and non-queries, export database schema
.dbb-table A name of a table; File Explorer properties query The table's data Copy table's data to clipboard, create queries and non-queries, export table definition, clear and delete the table
.dbb-view A name of a view; File Explorer properties query The view's data Copy view's data to clipboard, create queries, export view definition, delete the view
.dbb-query SQL select statements The data returned by the query Copy data returned by the query to clipboard, create a view defined by the query, create an INSERT, UPDATE, or DELETE statement constrained by the query, select files in the current folder
.dbb-non-query SQL data manipulation statements and schema definition statements The contents of the file (SQL statements) Execute the statements
.dbb-index A name of an index Index definition (CREATE INDEX statement) Export index definition, delete the index
.dbb-trigger A name of a trigger Trigger definition (CREATE TRIGGER statement) Export trigger definition, delete the trigger

In this manual the terms "table", "view", "index", and "trigger", where unambigious, are used to refer to both the database objects and the dbb-* files which represent the objects.

This manual uses the Northwind sample database to give examples.

Northwind database as presented by DB Brother

Back to top


2. Installation and removal

Administrative rights are required to perform installation and removal.

Automatic installation

Use the provided installer to install and remove the product.

Manual installation

Copy the product's file to desired location and register it with the regsvr32 utility:

regsvr32 dbbrother.dll

To uninstall the product, run regsvr32 with the -u option:

regsvr32 -u dbbrother.dll

then delete the file. You may need to logoff prior to deleting as the shell extension modules are usually locked by shell.

Back to top


3. Browsing database objects

Select DB Brother > Show user objects from the context menu of a database file. A database info file named DBBrother.dbb-info will be created and selected in the database file's folder. Also the files representing the database objects will be created and selected in the same folder. The object files can also be created and selected by choosing the Show user objects from the context menu of the database info file. Similarly, the Show system objects command creates and selects the files for the system database objects, such as sqlite_master table.

The database info file connects together the database and the files representing database objects. The database is explicitly specified in the database info file. The object files implicitly relate to the database info file which is located in the folder where an object file is located, or in one of its parent folders. This arrangement allows organizing object files into subfolders.

Once database object files are created, you can select tables and views to view the data they provide, or select indexes and triggers to view their definitions. A context menu of an object file offers various commands to act on the database, as summarized in Introduction.

As the names of the database objects are stored inside the object files, the files can be renamed, for example, for the purpose of giving the objects descriptive names.

Another way to create database info file is to right-click a database file and drag it to a folder where you want to create the info file, then click Show user objects here (DB Brother) from the context menu.

Back to top


4. Viewing table data

Displaying table data in the preview pane

Select a table, a view, or a query file from the File Explorer folder. The data contained in the table/returned by view/query will be shown in the preview pane.

The data for the preview of the tables and views is extracted by the query written in the [Preview] section of the table/view file. This query can be modified as needed.

In addition to the data a summary about the data is displayed, in particular, the query running time and the number of the displayed rows. When the source of the data contains more rows than displayed, a plus sign is added to the number.

The number of displayed rows is limited by the MaxRows value in the [Preview] section of the database info file. This value can be set in range from 0 to 10,000. The default value is 500.

A table/view/query file can specify multiple queries separated by semicolon. The preview pane shows the data returned by each query. The row count limit is applied per query.

The length of displayed text data is limited by 5,000 characters. Text values whose length is above limit are truncated and appended with a triple hyphen ("---").

The presentation of the data displayed in the preview pane is controlled by the values in the [PreviewStyling] section of the database info file. The database info file created by DB Brother contains a predefined style which can be modified as needed. Because an HTML renderer is utilized to display the table data in the preview pane, you will have to use CSS specifiers for these values.

The predefined style matches Windows theme (light or dark).

Displaying table data as file properties/columns

DB Brother extends the File Explorer's property set with five generic properties named from A to E (the names are prefixed with [DBB]). For a value to be displayed as one of these properties that value should be returned in the first row of a query and should have a special name: to be displayed as property A the value should have name prefixed with a. (A-dot), and so on.

For example, to display the name and the hire date of the latest hired employee as the properties A, B, and C of a query file, put the following query in the query file:

select LastName [a. Last name], FirstName [b. First name], HireDate [c. Hire date] from Employees order by HireDate desc

Displaying table data in the File Explorer columns

To display data as the properties of the files representing tables and views write the property query in the [FileProperties] section of the table/view file.

The table/view files created by DB Brother contain a predefined property query which returns a row count of the table/view. The row count is displayed as property C.

Back to top


5. Embedding HTML markup into the query output

Because HTML is used to display query results, DB Brother escapes the HTML special characters if they are present in the displayed data - in order to avoid breaking the output layout. Still, it can be useful to mark the displayed data up; e. g. to format text, to embed hyperlinks, and so on.

To insert HTML markup into the query results, one should disable HTML escaping. This can be done on per-column basis. The query columns which will contain the markup should have their names enclosed in angle brackets.

In the columns where HTML escaping is disabled the HTML markup will be applied to the output instead of being displayed as is. However, it still necessary to escape the markup which can possibly present in the data coming from the database. For that use SQL function escape_html (implemented in the DB Brother DLL).

To avoid manually building HTML elements, use another SQL function supplied by DB Brother - html. This function creates a text value which represents an HTML element.

The following example shows how to insert a hyperlink and an image reference into the query results:

select
html('a', 'href','http://evilapp.com/help/dbbrother/index.html', 'DB Brother Help') [<hyperlink>],
html('img', 'src','http://evilapp.com/images/dbbrother.jpg', '') [<image>]

Embedding HTML markup into a query output

The appearance of the two particular HTML elements - a and img - is controlled by Anchor and Image values in the [PreviewStyling] section of the database info file.

Note that the linked images will not be shown in the preview pane of Windows File Explorer. The query results should be exported to HTML to make the images visible.

Tip: use HTTP (rather than HTTPS) protocol for the href attribute of the a element; otherwise the links will not open from the preview pane of Windows File Explorer.

Another example shows how to use HTML to output a histogram:

select ProductName, UnitPrice,
html('span', 'style', 'width: ' || round(UnitPrice / max.price * 100.0) || '%; background-color: teal', '') [<PriceGraph>]
from Products
cross join (select max(UnitPrice) price from Products) max

Displaying a histogram

Back to top


6. Creating and running queries

A query is run immediately after a query file is selected in File Explorer folder, provided that the preview pane is shown. The result of the query is displayed in the preview pane.

To create an empty query, select Create query from the context menu of a database info file. An empty query file will be created.

To create a query for a table/view, select Create query from the context menu of a table/view file. A query file will be created containing a SELECT statement for the table/view.

When multiple table/view files are selected then Create query creates one query file per table/view file. Also additional options become available: the Create queries (single file) command creates a single query file contaning semicolon-separated queries for all selected tables/views; and the Create multi-table query command creates a single query file contaning a single query joining all selected tables/views.

Back to top


7. Viewing schema definition

Select Export schema definition from the context menu of a file representing a database object. A non-query file containing the definition (a CREATE statement) will be created and selected.

To export schema defintion of the entire database, select Export schema definition from the context menu of a database info file.

The definitions of the indexes and the triggers can also be viewed in the preview pane by selecting the files representing these types of objects.

Back to top


8. Modifying table data and database schema

Select Create non-query from the context menu of a table, then select one of the commands to create a data manipulation statement or schema definition statement. A non-query file containing a statement template will be created. Modify the template as needed and then select Execute SQL from the context menu of the non-query file to execute the statements.

If the non-query creates or deletes database objects, then the files representing the database objects will be created (if not existing already) and selected for the created objects and will be deleted for the deleted objects. The files will be deleted to Recycle Bin. Hold the Ctrl key down to prevent deletion of the files.

You can also create an empty non-query file from the context menu of a database info file.

Clearing tables

Select Clear table from the context menu of the table to delete all rows from the table. This command executes unconstrained DELETE FROM statement on the table.

Deleting database objects

Select Drop object from the context menu of a .dbb-* file representing a database object to delete both the object and the file. The file will be deleted to Recycle Bin. Hold the Ctrl key down to prevent deletion of the file.

Back to top


9. Exporting data

Copying table data to clipboard

Select Copy data from the context menu of a table, a view, or a query. The command will extract the data and put it to the clipboard in separated values format with the rows separated by new line character and the fields separated by TAB character. The separator characters contained within the field values will be replaced with spaces. For the BLOB values the size in bytes of the value will be copied. The copied data will be suitable for pasting to a spreadsheet.

When copying tables/views, the data for the copy is extracted by the query written in the [ExportClipboard] section of the table/view file. This query can be modified as needed.

The number of copied rows is limited by a MaxRows parameter in the [ExportClipboard] section of a database info file. This number can be set in range from 0 to 1,000,000. The default value is 10,000.

Multiple tables/views/queries can be selected for this command and their data be copied at once. The limit is applied per table/view/query.

Saving table data to HTML file

Select Export HTML from the context menu of a table, a view, or a query. The command will extract the data and write it to an .html file.

When exporting tables/views, the data is extracted by the query written in the [ExportHTML] section of the table/view file. This query can be modified as needed.

The number of saved rows is limited by a MaxRows parameter in the [ExportHTML] section of a database info file. This number can be set in range from 0 to 1,000,000. The default value is 10,000.

Multiple tables/views/queries can be selected for this command and their data be exported at once. The limit is applied per table/view/query.

Back to top


10. Selecting files by a query

This command can be useful when the database stores links to the external files. It is invoked by the Select files by query item from the context menu of a query file.

The command highlights the files in the File Explorer folder where the query file is located. A query must return paths of the files in a column named path. The name of the column is specified in the database info file in the [SelectFiles] section and can be changed as needed.

The paths can be absolute or relative to the query file's folder.

Back to top


11. Database opening sequence

An opening sequence is the set of SQL statements which DB Brother executes immediately after it opened the database. The opening sequence is the place to create temporary tables, attach databases, load extensions, and so on.

The statements are written in the [OpeningSequence] section of a database info file. The names of the values in this section are arbitrary and irrelevant. The statements are executed in the order they are written in. The section can have arbitrary number of values and each value can have multiple statements separated by semicolon.

Back to top


12. SQLite extensions

To allow SQLite extension loading, set 1 for the EnableExtensions value in [DatabaseInfo] section of the database info file. This value should be checked in SQL via the @dbb_extensions_enabled parameter:

select load_extension (...) where @dbb_extensions_enabled = 1

Extensions can be loaded in a .dbb-(non-)query file or in the opening sequence section of the database info file.

Back to top


13. CSV virtual table extension

The CSV virtual table extension is built into the DB Brother DLL. It can be loaded as follows:

select load_extension (@dbb_module_path, 'sqlite3_csv_init' ) where @dbb_extensions_enabled = 1

The @dbb_module_path parameter is provided by DB Brother and its value is the full path to the DB Brother DLL file.

After the extension is loaded, a CSV file can be opened as usual:

create virtual table temp.csvtable using 'csv' (filename='csvfile', header)

Back to top


14. Using clipboard data in SQL statements

With DB Brother SQL statements can use an SQL parameter named @clipboard to read the text contained in the system clipboard at the moment the SQL statement is executed.

For example, assuming that the clipboard contains "berry", the following query:

select * from Products where ProductName like '%' || @clipboard || '%'

will return the following results:

Selecting rows by clipboard data

The clipboard parameter can also be denoted as $clipboard or :clipboard.

Multi-valued clipboard parameter

The text that is put to clipboard can be split into parts by commas and the parts can be referenced as @clipboard1, @clipboard2, and so on.

If a part is empty then the corresponding @clipboardn parameter is left unbound (has null value) rather than assigned an empty text value. If the ordinal of a clipboard parameter is greater than the number of parts then the parameter is also left unbound.

When text in clipboard is not split then @clipboard and @clipboard1 are interchangeable.

The following query shows the use of the @clipboardn parameters (assuming that clipboard contains text "one,two,,four"):

select @clipboard, @clipboard1, @clipboard2, @clipboard3, @clipboard4, @clipboard5

Using multi-valued clipboard parameter

Back to top


15. Case-insensitive text operations

Because SQLite supports case-insensitivity only for Latin characters, DB Brother provides its own implementations of lower and upper functions which support case conversion of non-Latin characters.

The implementations use the user's default locale to convert the character case. To use specific locale, pass its name as an optional second argument. Standard locale names should be used, such as en-US, for example:

select * from Products where lower(ProductName, 'en-US') like lower('%Biscuit%', 'en-US')

When it is not desirable to override standard lower and upper functions then the DB Brother's implementations may be given the alternate names by setting Lower and Upper values in the [TextMapping] section of the database info file. The implementations can be disabled altogether by setting the Enabled value to zero.

Back to top


16. SQL functions

DB Brother implements a set of scalar functions which can be called from an SQL code:

Function name Description Specification Usage example Output example
env Returns the value of the specified environment variable env(<environment_variable_name>) select env('SystemRoot') C:\Windows
escape_html Escapes HTML special characters encountered in the given text escape_html(<html_text>) select escape_html('<span>text</span>') &lt;span&gt;text&lt;/span&gt;
html Marks given text up as an HTML element with a given name and attributes. Attributes are specified as pairs of the function arguments: name1, value1, name2, value2, ... The element content and the values of the atributes are escaped. html (<element_name>, <attributes>, <element_content>) select html('a', 'href','http://evilapp.com/help/dbbrother/index.html', 'DB Brother Help') <a href="http://evilapp.com/help/dbbrother/index.html">DB Brother Help</a>

Should any of these functions cause name collision, it can be given the alternate name in the [FunctionName] section of the database info file.

Back to top


17. Tips

Back to top


18. Getting further help

You can send questions and feedback by e-mail: support (at) evilapp (dot) com.

You can also subscribe to RSS feed to receive notifications about new product releases.

Back to top


19. Release history

version 1.3 released 02-Jun-2024
New
HTML can be used to format an output of a query.
Enabled SQLite extensions loading.
The CSV virtual table extension is built into the DB Brother's binary file.
Implemented a set of functions which can be called from an SQL code.
Improvements
Upgraded to SQLite version 3.46.0.
version 1.2 released 11-Feb-2022
New
Multi-valued clipboard parameter.
Improvements
Upgraded to SQLite version 3.37.2.
FTS5 is enabled.
Changes
Foreign keys are enabled in newly created database info files.
The length limit of displayed text data has been increased from 500 to 5,000 characters.
version 1.1 released 20-May-2021
New
SQL statements can read data from system clipboard.
Improvements
Upgraded to SQLite version 3.35.5.
SQLite's JSON functions enabled.
version 1.0.4 released 13-Apr-2021
Improvements
Upgraded to SQLite version 3.35.4.
The database file's context menu can be hidden.
Changes
The command "Create database info" renamed to "Show user objects".
version 1.0.3 released 25-Mar-2021
Improvements
Upgraded to SQLite version 3.35.2 (with math functions enabled).
Added the "drop column" command template.
Added dark preview theme.
version 1.0.2 released 09-Mar-2021
Fixes
Fixed menu text color for dark Windows theme.
Improvements
Added templates for the "rename table", "rename column" commands.
version 1.0.1 released 07-Mar-2021
Fixes
The software could interfere with the functioning of Windows shell.
version 1.0 released 19-Dec-2020
First release

Back to top


20. Disclaimer

The software is provided "AS IS" without any warranty, either expressed or implied, including, but not limited to, the implied warranties of merchantability and fitness for a particular purpose. The author will not be liable for any special, incidental, consequential or indirect damages due to loss of data or any other reason.

Back to top