As I don't use SQLite (or any other SQL) all the time and am no database expert, it's always a bit of a challenge to get the SELECT syntax right, specifically when relationships between tables (JOINs) come into play. There's that, and the fact that it was fun to develop (and I wanted to have the functionality in another project I've been working on): this class helps to build SELECT statements. I don't think it will be helpful for real end users who have never touched a database, but for developers it could be useful.
Credits
A big thank you to @William Lancee whose brilliant Arrows and WiLNetChart classes I used for the chart. I customised the WiLNetChart class and saved it as wmWiLNetChart, which is used in this project.
Dependencies
- Core libraries to add: BCTextEngine, DesignerUtils, jControlsFX9, jSQL, XUI Views
- Non-core libraries: sqlite-jdbc-3.39.2.0 (or newer)
- Modules from the project's root folder: Arrows.bas, wmSQLiteSelectBuilder.bas, wmWiLNetChart.bas
- Files from the project's Files folder: BBCV.bjl, wmSQLiteSelectBuilder.bjl
Example project
Is attached to this post. The screenshots were created working with the Chinook database (https://www.sqlitetutorial.net/sqlite-sample-database), and the attached file 'saved_example.sqlite' (which can be loaded using the 'Open' button).
Usage
- Pass an already initialised SQL object to the class, or the directory and filename of the database with which to work
- Localise strings by updating map 'localisationMap' which is a public variable
- Select the tables to include in the query
- Define table name aliases if necessary
- Define column name aliases if necessary
- For each table, select the columns to include in the result, filters to apply, sorting specifications, and relationships with 'child' tables (to involve a table in multiple relationships, use aliases)
- Right-click a node in the chart to see all of the node's relationships (JOIN types, table names, column names)
- Build the SELECT statement
- Test the SELECT statement and inspect its result (it uses the SELECT statement that is shown at the bottom; it can be edited if desired)
- When clicking OK, the SELECT statement is returned to the caller
- Save the specifications (to an SQLite file) for later reuse
- Open a saved specifications file
Updates
- 2023-08-19: bug fix in handling of filter columns and sort columns (see "This next line added 2023-08-19" in the class for the changes)
Enjoy!
Credits
A big thank you to @William Lancee whose brilliant Arrows and WiLNetChart classes I used for the chart. I customised the WiLNetChart class and saved it as wmWiLNetChart, which is used in this project.
Dependencies
- Core libraries to add: BCTextEngine, DesignerUtils, jControlsFX9, jSQL, XUI Views
- Non-core libraries: sqlite-jdbc-3.39.2.0 (or newer)
- Modules from the project's root folder: Arrows.bas, wmSQLiteSelectBuilder.bas, wmWiLNetChart.bas
- Files from the project's Files folder: BBCV.bjl, wmSQLiteSelectBuilder.bjl
Example project
Is attached to this post. The screenshots were created working with the Chinook database (https://www.sqlitetutorial.net/sqlite-sample-database), and the attached file 'saved_example.sqlite' (which can be loaded using the 'Open' button).
Usage
- Pass an already initialised SQL object to the class, or the directory and filename of the database with which to work
- Localise strings by updating map 'localisationMap' which is a public variable
- Select the tables to include in the query
- Define table name aliases if necessary
- Define column name aliases if necessary
- For each table, select the columns to include in the result, filters to apply, sorting specifications, and relationships with 'child' tables (to involve a table in multiple relationships, use aliases)
- Right-click a node in the chart to see all of the node's relationships (JOIN types, table names, column names)
- Build the SELECT statement
- Test the SELECT statement and inspect its result (it uses the SELECT statement that is shown at the bottom; it can be edited if desired)
- When clicking OK, the SELECT statement is returned to the caller
- Save the specifications (to an SQLite file) for later reuse
- Open a saved specifications file
Updates
- 2023-08-19: bug fix in handling of filter columns and sort columns (see "This next line added 2023-08-19" in the class for the changes)
Enjoy!
Last edited:
