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.
sqlite3, database views and db.gb.form controls. Does it work?
- Quincunxian
- Posts: 173
- Joined: Sunday 25th June 2017 12:14am
- Location: Western Australia
Re: sqlite3, database views and db.gb.form controls. Does it work?
- Attachments
-
- Lenses.tar.gz
- Project
- (523.18 KiB) Downloaded 330 times
Cheers - Quin.
I code therefore I am
I code therefore I am
Re: sqlite3, database views and db.gb.form controls. Does it work?
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.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.
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?
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?
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
- Quincunxian
- Posts: 173
- Joined: Sunday 25th June 2017 12:14am
- Location: Western Australia
Re: sqlite3, database views and db.gb.form controls. Does it work?
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.
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.
Cheers - Quin.
I code therefore I am
I code therefore I am
Re: sqlite3, database views and db.gb.form controls. Does it work?
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 sayingQuincunxian wrote: ↑Monday 1st February 2021 1:56pm Double clicking on a row in the grid view opens that row for maintenance.
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?
- Attachments
-
- Screenshot_20210201_175910.png (451.76 KiB) Viewed 7052 times
- Quincunxian
- Posts: 173
- Joined: Sunday 25th June 2017 12:14am
- Location: Western Australia
Re: sqlite3, database views and db.gb.form controls. Does it work?
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.
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)
Cheers - Quin.
I code therefore I am
I code therefore I am
Re: sqlite3, database views and db.gb.form controls. Does it work?
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
Here you go, you'll get an icy snowman from the northern hemisphere attached
- Attachments
-
- L1000675.jpg (219.26 KiB) Viewed 6774 times
- Quincunxian
- Posts: 173
- Joined: Sunday 25th June 2017 12:14am
- Location: Western Australia
Re: sqlite3, database views and db.gb.form controls. Does it work?
It was 38 degrees here today in Perth Australia.
Your snowman is truly appreciated. !
Your snowman is truly appreciated. !
Cheers - Quin.
I code therefore I am
I code therefore I am
Re: sqlite3, database views and db.gb.form controls. Does it work?
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 "frm_Lenses". There are now 43 fields in table "Lenses". I think I found an option to replace the code in by a more generic function. The following (beta version) code loads the data into the form regardless of the appropriate control exists or not.
clsFields is a separate class defining some variables:
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?
Code: Select all
Cls_Lenses.GetRecordData(InId As Integer)
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
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