Abstract
Another one in my 'I don't like doing repetitive stuff' repertoire. The rationale for this one is to avoid having to code the 'boring stuff', i.e. CRUD for data tables (usually slightly or completely different than others from other programs) in a new program, again and again.
The idea was to basically just describe the columns in the caller, and let the class do the work. In its simplest form (without data validations or other customisations), the code would be like this for a table 'People' containing columns 'AutoIncID' (integer autoincrement), 'FirstName' (text), 'LastName' (text):
Potential limitation
The class has only been tested with an SQLite database. I have made a couple of educated guesses to cater for other SQL databases too, but these are untested.
I believe the only (Private) methods that would be impacted are:
- SQLcolumnExists
- SQLgetTableColumnNames
- SQLtableExists
I'll happily correct/enhance those if someone spots a problem and provides the code.
Prerequisites/dependencies
- The database table's unique key column must have type 'integer autoincrement'
- All files from the demo project's Files directory whose names start with 'wmcrud' must be added to the project
- Library dependencies (all B4J-internal libraries): ByteConverter, CSSUtils, JavaObject, jCore, jFX, jReflection, jSQL, jXUI, XUI Views
- Additional jar requirement: sqlite-jdbc-3.39.2.0 (or another version of the sqlite-jdbc jar, see https://www.b4x.com/android/forum/threads/sqlite-jdbc-library-version-updates.133792)
Features
- A meta SQLite database that is created/used to save/restore user choices
- Show (or don't) a filter Pane at the left with filter fields for all the columns in the TableView; values are saved in the meta database
- Column order and widths can be changed by the user (saved in the meta database)
- Column headers are clickable for sorting; the sort order is saved in the meta database
- TableView rows have a ContextMenu to (if allowed via the Initialize2 call) create, update, delete, and duplicate rows
- Double-click a TableView row to edit its data (if allowed via the Initialize2 call)
Public properties
- DataTypes: used in MakeColumnDefinition calls, for data validation, and to determine which view types to use for filter fields (ComboBox, DatePicker TextArea, TextField)
- DbChanged: returns a Boolean that reports whether the data in the database have been changed (by create/update/delete operations)
- LocalisedStrings: a Map containing strings that are used for error messages, views, tooltips, ...
- SelectedTable: returns a string containing the table name that was passed to Initialize2
- ShowFilters: instructs the class to show or hide the filters Pane
Public methods
- Initialize (first call): this method only initialises the Class minimally so that MakeColumnDefinition can be used
- MakeColumnDefinition (second series of calls): generates wmCRUDcolumnDefinition entries for a List that will be passed to Initialize2
- Initialize2 (third call): the 'real' initialisations
- Show: shows the data
- AddFontAwesome: adds a FontAwesome character and its colour for a specific value in a database table; this is then used in the TableView in columns with a DataTypes.xxx.WITH_FONTAWESOME data type
- AddMaterialIcon: adds a MaterialIcons character and its colour for a specific value in a database table; this is then used in the TableView in columns with a DataTypes.xxx_WITH_MATERIALICONS data type
- ClearValidationError: can be called e.g. from the caller's data validation method when creating/updating rows, and resets the red text for a view's label in the update/add dialogue and removes any error message
- Close: saves the TableView layout (column order and widths) in the meta database and hides the class's views
- ResetLayout: clears all saved layout data (column order and widths, sort order, and filter values)
- SetColors: sets the foreground and background colours that are used throughout the class
- SetValidationError: can be called e.g. from the caller's data validation method, and sets red text for a view's label in the update/add dialogue and adds an error message
- UpdateComboBoxValues: updates comboBoxItemsValues and comboBoxItemsDisplay for the given column; this can be useful if the caller decides to change the allowed values for a column that is represented by a ComboBox
Demo project attached. Enjoy!
Changes
2024-05-14:
- replaced "[...]" with "`...`" for all SQL-related strings (MySQL doesn't allow square brackets; SQLite does allow backticks)
2024-05-04:
- added 'zero/empty string' provisions for filters (Note: this is for "" values, not for NULLs)
- subs 'SQLtableExists' and 'SQLgetTableColumnNames' have been corrected (and tested) so that they work with MySQL databases too
Another one in my 'I don't like doing repetitive stuff' repertoire. The rationale for this one is to avoid having to code the 'boring stuff', i.e. CRUD for data tables (usually slightly or completely different than others from other programs) in a new program, again and again.
The idea was to basically just describe the columns in the caller, and let the class do the work. In its simplest form (without data validations or other customisations), the code would be like this for a table 'People' containing columns 'AutoIncID' (integer autoincrement), 'FirstName' (text), 'LastName' (text):
B4X:
Dim sqlDb as SQL
sqlDb.InitializeSQLite(...)
Dim wmCRUD1 As wmCRUD
wmCRUD1.Initialize
Dim columnDefinitions As List
columnDefinitions.Initialize
columnDefinitions.Add(wmCRUD1.MakeColumnDefinition("HR number", "AutoIncID", "RIGHT", False, False, False, wmCRUD1.DataTypes.INTEGER, Null, Null))
columnDefinitions.Add(wmCRUD1.MakeColumnDefinition("First name", "FirstName", "RIGHT", False, True, True, wmCRUD1.Datatypes.TEXT, Null, Null))
columnDefinitions.Add(wmCRUD1.MakeColumnDefinition("Last name", "LastName", "RIGHT", False, True, True, wmCRUD1.Datatypes.TEXT, Null, Null))
If wmCRUD1.Initialize2(Me, MainForm, PaneForWmCRUD, File.DirApp, sqlDb, "People", "People data", "AutoIncID", _
Null, Null, columnDefinitions, True, True, True, "", "", "") = False Then
xui.MsgboxAsync("wmCRUD1.Initialize2 failed - check the log/stdout", "Error")
Return
End If
wmCRUD1.ShowPotential limitation
The class has only been tested with an SQLite database. I have made a couple of educated guesses to cater for other SQL databases too, but these are untested.
I believe the only (Private) methods that would be impacted are:
- SQLcolumnExists
- SQLgetTableColumnNames
- SQLtableExists
I'll happily correct/enhance those if someone spots a problem and provides the code.
Prerequisites/dependencies
- The database table's unique key column must have type 'integer autoincrement'
- All files from the demo project's Files directory whose names start with 'wmcrud' must be added to the project
- Library dependencies (all B4J-internal libraries): ByteConverter, CSSUtils, JavaObject, jCore, jFX, jReflection, jSQL, jXUI, XUI Views
- Additional jar requirement: sqlite-jdbc-3.39.2.0 (or another version of the sqlite-jdbc jar, see https://www.b4x.com/android/forum/threads/sqlite-jdbc-library-version-updates.133792)
Features
- A meta SQLite database that is created/used to save/restore user choices
- Show (or don't) a filter Pane at the left with filter fields for all the columns in the TableView; values are saved in the meta database
- Column order and widths can be changed by the user (saved in the meta database)
- Column headers are clickable for sorting; the sort order is saved in the meta database
- TableView rows have a ContextMenu to (if allowed via the Initialize2 call) create, update, delete, and duplicate rows
- Double-click a TableView row to edit its data (if allowed via the Initialize2 call)
Public properties
- DataTypes: used in MakeColumnDefinition calls, for data validation, and to determine which view types to use for filter fields (ComboBox, DatePicker TextArea, TextField)
- DbChanged: returns a Boolean that reports whether the data in the database have been changed (by create/update/delete operations)
- LocalisedStrings: a Map containing strings that are used for error messages, views, tooltips, ...
- SelectedTable: returns a string containing the table name that was passed to Initialize2
- ShowFilters: instructs the class to show or hide the filters Pane
Public methods
- Initialize (first call): this method only initialises the Class minimally so that MakeColumnDefinition can be used
- MakeColumnDefinition (second series of calls): generates wmCRUDcolumnDefinition entries for a List that will be passed to Initialize2
- Initialize2 (third call): the 'real' initialisations
- Show: shows the data
- AddFontAwesome: adds a FontAwesome character and its colour for a specific value in a database table; this is then used in the TableView in columns with a DataTypes.xxx.WITH_FONTAWESOME data type
- AddMaterialIcon: adds a MaterialIcons character and its colour for a specific value in a database table; this is then used in the TableView in columns with a DataTypes.xxx_WITH_MATERIALICONS data type
- ClearValidationError: can be called e.g. from the caller's data validation method when creating/updating rows, and resets the red text for a view's label in the update/add dialogue and removes any error message
- Close: saves the TableView layout (column order and widths) in the meta database and hides the class's views
- ResetLayout: clears all saved layout data (column order and widths, sort order, and filter values)
- SetColors: sets the foreground and background colours that are used throughout the class
- SetValidationError: can be called e.g. from the caller's data validation method, and sets red text for a view's label in the update/add dialogue and adds an error message
- UpdateComboBoxValues: updates comboBoxItemsValues and comboBoxItemsDisplay for the given column; this can be useful if the caller decides to change the allowed values for a column that is represented by a ComboBox
Demo project attached. Enjoy!
Changes
2024-05-14:
- replaced "[...]" with "`...`" for all SQL-related strings (MySQL doesn't allow square brackets; SQLite does allow backticks)
2024-05-04:
- added 'zero/empty string' provisions for filters (Note: this is for "" values, not for NULLs)
- subs 'SQLtableExists' and 'SQLgetTableColumnNames' have been corrected (and tested) so that they work with MySQL databases too
Last edited:
