|Applications are Evil||DB Brother Help|
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|
Administrative rights are required to perform installation and removal.
Use the provided installer to install and remove the product.
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.
Select 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 from the context menu of the database info file. Similarly, the command creates and selects the files for the system database objects, such as sqlite_master table.from the context menu of a database file. A database info file named
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 clickfrom the context menu.
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).
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.
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, selectfrom the context menu of a database info file. An empty query file will be created.
To create a query for a table/view, select SELECT statement for the table/view.from the context menu of a table/view file. A query file will be created containing a
When multiple table/view files are selected thencreates one query file per table/view file. Also additional options become available: the command creates a single query file contaning semicolon-separated queries for all selected tables/views; and the command creates a single query file contaning a single query joining all selected tables/views.
Selectfrom 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, selectfrom 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.
Selectfrom 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 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.
Selectfrom 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.
Select .html file.from the context menu of a table, a view, or a query. The command will extract the data and write it to an
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.
This command can be useful when the database stores links to the external files. It is invoked by theitem 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.
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.
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.
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|
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.
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.
|version 1.2 released 11-Feb-2022|
|Multi-valued clipboard parameter.|
|Upgraded to SQLite version 3.37.2.|
|FTS5 is enabled.|
|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|
|SQL statements can read data from system clipboard.|
|Upgraded to SQLite version 3.35.5.|
|SQLite's JSON functions enabled.|
|version 1.0.4 released 13-Apr-2021|
|Upgraded to SQLite version 3.35.4.|
|The database file's context menu can be hidden.|
|The command "Create database info" renamed to "Show user objects".|
|version 1.0.3 released 25-Mar-2021|
|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|
|Fixed menu text color for dark Windows theme.|
|Added templates for the "rename table", "rename column" commands.|
|version 1.0.1 released 07-Mar-2021|
|The software could interfere with the functioning of Windows shell.|
|version 1.0 released 19-Dec-2020|
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.