sqlite3, database views and db.gb.form controls. Does it work?

Ask about the individual Gambas components here.
User avatar
Quincunxian
Posts: 171
Joined: Sunday 25th June 2017 12:14am
Location: Western Australia

Re: sqlite3, database views and db.gb.form controls. Does it work?

Post 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.
Attachments
Lenses.tar.gz
Project
(523.18 KiB) Downloaded 294 times
Cheers - Quin.
I code therefore I am
01McAc
Posts: 75
Joined: Sunday 24th January 2021 8:25pm

Re: sqlite3, database views and db.gb.form controls. Does it work?

Post 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.
01McAc
Posts: 75
Joined: Sunday 24th January 2021 8:25pm

Re: sqlite3, database views and db.gb.form controls. Does it work?

Post 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.
01McAc
Posts: 75
Joined: Sunday 24th January 2021 8:25pm

Re: sqlite3, database views and db.gb.form controls. Does it work?

Post 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
User avatar
Quincunxian
Posts: 171
Joined: Sunday 25th June 2017 12:14am
Location: Western Australia

Re: sqlite3, database views and db.gb.form controls. Does it work?

Post 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.
Cheers - Quin.
I code therefore I am
01McAc
Posts: 75
Joined: Sunday 24th January 2021 8:25pm

Re: sqlite3, database views and db.gb.form controls. Does it work?

Post 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?
Attachments
Screenshot_20210201_175910.png
Screenshot_20210201_175910.png (451.76 KiB) Viewed 6653 times
User avatar
Quincunxian
Posts: 171
Joined: Sunday 25th June 2017 12:14am
Location: Western Australia

Re: sqlite3, database views and db.gb.form controls. Does it work?

Post 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)
Cheers - Quin.
I code therefore I am
01McAc
Posts: 75
Joined: Sunday 24th January 2021 8:25pm

Re: sqlite3, database views and db.gb.form controls. Does it work?

Post 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 :o . 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 :D
Attachments
L1000675.jpg
L1000675.jpg (219.26 KiB) Viewed 6375 times
User avatar
Quincunxian
Posts: 171
Joined: Sunday 25th June 2017 12:14am
Location: Western Australia

Re: sqlite3, database views and db.gb.form controls. Does it work?

Post by Quincunxian »

It was 38 degrees here today in Perth Australia.
Your snowman is truly appreciated. !
Cheers - Quin.
I code therefore I am
01McAc
Posts: 75
Joined: Sunday 24th January 2021 8:25pm

Re: sqlite3, database views and db.gb.form controls. Does it work?

Post 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 "frm_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?
Post Reply