Page 2 of 3
Re: sqlite3, database views and db.gb.form controls. Does it work?
Posted: Sunday 31st January 2021 2:48am
by Quincunxian
I had a look at the database schema and there were a few things that were not needed.
A database view is normally a complex(?) series of select statements using multiple tables.
Having a view that is a single select statement is a bit pointless.
I took your original data and built a quick project to show using sqlite without any data bound controls.
The Makers and Mounts maintenance forms are working, but I only did a partial on the lenses form as that
was a LOT of work but it has the basics to show you.
I also added a 'Country' table for Makers and the ability to store the maker Logo.
The txt file with the country names and codes is located in Lenses/Data
The upload to database is in Module AG but commented out.
Run the Administration menu option as soon as you start the application and then set the 'Logo Path'
which should be [Project Area]/Lenses/Images
Set the Save path to [Project Area]/Lenses or your Documents folder.
The save path is where any Extracted Documents will be saved.
You can extract data from any table in HTML, CSV, JSon and XML
(The extraction process is quite generic and should work with any sqlite database. )
In the maintenance forms you will see a 'stamp' button on the top tool bar.
This is to allow cloning of any valid record and should clear any specific data on the form.
You will also see an 'Add another' button on the input form.
This is to allow you to add another record without having to click ok and open a new record
if you are entering multiple records in a session.
NOTE # I did not test all functionality in the application so there may be a few bugs here and there.
There is an Error management System that allows you to provide better feedback on error location & data.
All located in Module AE
Any questions, let me know.
Re: sqlite3, database views and db.gb.form controls. Does it work?
Posted: Sunday 31st January 2021 1:16pm
by 01McAc
Quincunxian wrote: ↑Sunday 31st January 2021 2:48am
I had a look at the database schema and there were a few things that were not needed.
A database view is normally a complex(?) series of select statements using multiple tables.
Having a view that is a single select statement is a bit pointless.
I took your original data and built a quick project to show using sqlite without any data bound controls.
The Makers and Mounts maintenance forms are working, but I only did a partial on the lenses form as that
was a LOT of work but it has the basics to show you.
I also added a 'Country' table for Makers and the ability to store the maker Logo.
The txt file with the country names and codes is located in Lenses/Data
The upload to database is in Module AG but commented out.
Run the
Administration menu option as soon as you start the application and then set the 'Logo Path'
which should be [Project Area]/Lenses/Images
Set the Save path to [Project Area]/Lenses or your Documents folder.
The save path is where any
Extracted Documents will be saved.
You can extract data from any table in HTML, CSV, JSon and XML
(The extraction process is quite generic and should work with any sqlite database. )
In the maintenance forms you will see a 'stamp' button on the top tool bar.
This is to allow cloning of any valid record and should clear any specific data on the form.
You will also see an 'Add another' button on the input form.
This is to allow you to add another record without having to click ok and open a new record
if you are entering multiple records in a session.
NOTE # I did not test all functionality in the application so there may be a few bugs here and there.
There is an Error management System that allows you to provide better feedback on error location & data.
All located in Module AE
Any questions, let me know.
Stunning! Thank you Quincunxian for all your time and effort. It is actually a new application and it shows what is Gambas capable of. The idea how to filter lenses is much better and a completely different approach. A lot easier for users and more intuitive. Well done.
The application runs in the "Projects" directory in my $Home. I am going to have a closer look into the code in the next days and try to understand how things can be done. A steep learning curve for me. I am afraid I'll screw up the whole application when I try to add another feature.
Re: sqlite3, database views and db.gb.form controls. Does it work?
Posted: Sunday 31st January 2021 1:50pm
by 01McAc
Just one word re the database view. The view in the my DB was just an example to find out if gb.db supports views in general. The answer is afaik no, unfortunately. The original DB scheme is very basic and there is probably no reason to use a database rather than a CSV file.
Re: sqlite3, database views and db.gb.form controls. Does it work?
Posted: Monday 1st February 2021 1:10pm
by 01McAc
Quincunxian wrote: ↑Sunday 31st January 2021 2:48am
Any questions, let me know.
I still have a closer look into code, was cranking up the debugger and set a lot of breaking points. At some point I was wondering what happens when I double click a row in the Frm_Lenses. The panel "Pan_Tools" disables, the form seems to hang but still responses to mouse clicks. In debugging mode the code points to some other actions but I cannot find out what the purpose would be.
Another question I have is regarding the value boxes (Vbx_MaxAperture, Vbx_MinAperture, ..) in the same form. Do they have any functionality or is this unfinished yet? I am happy to take over your great job but it's not very clear to me why these valueboxes are behind the panel "Pan_Maint".
Cheers
01McAc
Re: sqlite3, database views and db.gb.form controls. Does it work?
Posted: Monday 1st February 2021 1:56pm
by Quincunxian
Double clicking on a row in the grid view opens that row for maintenance.
The tool panel is disabled at that point so you cannot inadvertently:
Add a new record while halfway through changing and existing record.
The way in which the form works is that the Pan_Maint with the IO controls is hidden.
Either the Gridview or the Treeview selection screens allow you to either change, clone or delete an existing record.
Pan_Maint is made visible; covering up the selection screen so that you can't inadventantly select another record
halfway though a valid maintenance process.
Adding a new record can be accessed from the Tool panel and additional records can be added
by the "Add_Another" button. This is disabled when changing a record as you are not 'adding another' and you must
Select 'Ok' to update the changed record.
The value box controls are used as they only allow numerical input. Storing numbers as text fields limits your capabilities
in doing specialised criteria SELECT statements. Ie:
"SELECT * FROM Lenses WHERE( Max_Aperture BETWEEN 1 AND 2.9)"
"SELECT * FROM Lenses WHERE( Max_Aperture <= 3.9 OR Max_Aperture >= 2.0)"
You could use Textboxes for this input then convert it to a number before updating the record but this
saves a validation step to ensure that someone entered a number and not some text.
Re: sqlite3, database views and db.gb.form controls. Does it work?
Posted: Monday 1st February 2021 5:06pm
by 01McAc
Quincunxian wrote: ↑Monday 1st February 2021 1:56pm
Double clicking on a row in the grid view opens that row for maintenance.
Oh yes, thanks. My fault. I was curious and moved the panel into the background to see what's behind. So, I reverted it back and the behaviour of the form is exactly as described it. But ... I get an error when I double click a row in the lens maintenance form, edit a record and when I save it (button "OK"). The red error message (see screen capture attached) actually saying
Code: Select all
"Unknown Symbol 'MountName' in Class 'CLS_Lenses' in Frm_Lenses' 168"
.
I suspect the KeyClass didn't populate the data into KeyClass-attributes (?). How can I make sure, that the data is populated before the data is validated/updated?
Re: sqlite3, database views and db.gb.form controls. Does it work?
Posted: Monday 1st February 2021 10:43pm
by Quincunxian
My bad.
When I built the lenses maintenance form, I used the existing Maker Form as a template.
I forgot to change the remaining functions over to Lenses.
Because I build my forms in the same way, you can use ( more or less) any form as a template
to build a new form. To build the Mounts Form, which was very simple took around 15 minutes, as an example.
Here is the missing code that should make the form workable so that you can follow the logic in the SQL methods.
I would recommend using the Mounts form as it is far simpler and will give you the same information as the SQL
processes are exactly the same.
Please Note # These are NOT '
the best way' to manage these functions , they are just MY way
There are a lot of sub routines & functions in some of the classes that you will never need but I have other applications that do,
and I just copy them over. I keep a library of 'master' classes so that I don't have to maintain (too many) multiple versions.
If Cmb_Mount.Index = 0 Then
ErrorCount += 1
Mess &= "You must set a " & Lab_MountName.Text & Gb.Crlf
If IsNull(FocusCtl) Then FocusCtl = Cmb_Mount
Endif
If Vbx_MinAperture <= 0 Then
ErrorCount += 1
Mess &= "You must set a " & Lab_MinAperture.Text & Gb.Crlf
If IsNull(FocusCtl) Then FocusCtl = Vbx_MinAperture
Endif
If Vbx_MaxAperture <= 0 Then
ErrorCount += 1
Mess &= "You must set a " & Lab_MaxAperture.Text & Gb.Crlf
If IsNull(FocusCtl) Then FocusCtl = Vbx_MaxAperture
Endif
If Vbx_FocalLength <= 0 Then
ErrorCount += 1
Mess &= "You must set a " & Lab_FocalLength.Text & Gb.Crlf
If IsNull(FocusCtl) Then FocusCtl = Vbx_FocalLength
Endif
If Txa_Information.Text = "" Then
Txa_Information.Text = "Updated by " & User.name & " on " & Now
Endif
If ErrorCount = 0 Then 'The record is valid
KeyClass = New Cls_Lenses
KeyClass.LensType = Txt_LensType.Text
KeyClass.MakerId = SelectedMakerId
KeyClass.MountId = SelectedMountId
KeyClass.FocalLength = Vbx_FocalLength.Value
KeyClass.MinAperture = Vbx_MinAperture.Value
KeyClass.MaxAperture = Vbx_MaxAperture.Value
KeyClass.Information = Txa_Information.Text
'Add the rest of the fields as desired...
KeyClass.AddORChangeRecord(SelectedId)
Re: sqlite3, database views and db.gb.form controls. Does it work?
Posted: Tuesday 2nd February 2021 7:37pm
by 01McAc
Thanks, fixed. I noticed that I have to extend the database scheme at first. There are a bunch of fields needed for lenses
. Secondly, I'll create some valid/real test data before I can have deep dive into your forms and code. The use of classes and inheritance is quite new for me - so it's not straight forward.
Here you go, you'll get an icy snowman from the northern hemisphere attached
Re: sqlite3, database views and db.gb.form controls. Does it work?
Posted: Tuesday 2nd February 2021 10:25pm
by Quincunxian
It was 38 degrees here today in Perth Australia.
Your snowman is truly appreciated. !
Re: sqlite3, database views and db.gb.form controls. Does it work?
Posted: Friday 5th February 2021 9:35am
by 01McAc
In the meantime I added a few more attributes to the table "
Lenses" which lead me to the question how to get and load the data into the Form "f
rm_Lenses". There are now 43 fields in table "
Lenses". I think I found an option to replace the code in
Code: Select all
Cls_Lenses.GetRecordData(InId As Integer)
by a more generic function. The following (beta version) code loads the data into the form regardless of the appropriate control exists or not.
Code: Select all
Private Function fill_Fields(tbl As String, sSQL As String) As Integer 'As Collection
Dim DBS As New Cls_SQL_DataBase
Dim hCollection As Collection
Dim hTable As Table
Dim fld As Field
Dim sKeyPraefix As String
Dim sSep As String = ";"
Dim colFields As New Collection
Dim clsFld As ClsFields
Dim sPKey As String
Dim $resSQL As Result
Dim sMyTextbox_name As String
Dim tbox As TextBox
hCollection = ["-2": "Blob", "-1": "Serial", "1": "Boolean", "4": "Integer", "5": "Long",
"7": "Float", "8": "Date", "9": "String"]
$resSQL = DBS.$Con.Exec(sSQL)
If Not ($resSQL.Available) Then
Message("No result: Select Stmt.")
Return
Endif
If Not (DBS.$Con.Tables.Exist(tbl)) Then
Message("Table does not exist")
Return
Endif
hTable = DBS.$Con.Tables[tbl]
sKeyPraefix = hTable.Name & sSep & "FIELDS" & sSep
Print "DBTableName: "; hTable.Name
Print "---------------------------------------"
Print
For Each fld In hTable.Fields
sPKey = Upper(sKeyPraefix & fld.Name)
clsFld = New ClsFields
clsFld.Key = sPKey
clsFld.fldDefault = fld.Default
clsFld.fldLength = fld.Length
clsFld.fldName = fld.Name
clsFld.fldTable = fld.Table
clsFld.fldType = fld.Type
colFields.Add(clsFld, sPKey)
Print "DBField-Name: "; fld.Name
Print "--------------------------------------"
Print "DBField-Collation: "; IIf(fld.Collation = Null, " Not defined", fld.Collation)
Print "DBField-Default: "; IIf(fld.Default = Null, " Not defined", fld.Default)
Print "DBField-Type: "; hCollection[fld.Type]
sMyTextbox_name = "Txt_" & fld.Name
tbox = Frm_Lenses.Controls[sMyTextbox_name]
Try tbox.Text = $resSQL[fld.Name]
If fld.Type = db.String Then
If fld.Length = 0 Then
Print "DBField-Length: No limit."
Else
Print "DBField-Length: "; fld.Length
Endif
Endif
Print
Next
End
clsFields is a separate class defining some variables:
Code: Select all
' Gambas class file
Public Key As String
Public fldDefault As Variant
Public fldDefaultSymbol As Picture = Picture["icon:/16/add"]
Public fldLength As Integer
Public fldLengthSymbol As Picture = Picture["icon:/16/add"]
Public fldName As String
Public fldTable As Table
Public fldType As Integer
Public fldTypeSymbol As Picture = Picture["icon:/16/add"]
Public fldSymbol As Picture = Picture["icon:/16/add"]
Public fldStatus As Boolean = True
Public fldStatusDiff As Boolean = False
I borrowed parts of the code from the
DB-diff example (Software-Farm). Actually, my intention was to code a function which returns a collection with field name and field type. But I'm not that experienced and couldn't find any specific code examples. I think the code above should do the trick. What do you think?