B4J Library [Class] [B4J] [SQLite] wmSQLiteSelectBuilder - a more or less graphical builder for SQLite SELECT statements

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!

01-start.JPG
02-select tables.JPG


03-setup table aliases.JPG
04-setup column aliases.JPG


05-select columns.JPG
06-define filters.JPG


07-define sort order.JPG
08-define relationships.JPG


09-click Tables to show the chart.JPG
10-click Build to build the SELECT statement and then possibly edit it.JPG


11-click Test to test the SELECT statement.JPG
12-click OK to return the SELECT statement to the caller.JPG
 

Attachments

  • saved_example.zip
    4.2 KB · Views: 161
  • wmSQLiteSelectBuilderDemo.zip
    36.8 KB · Views: 126
Last edited:
Top