Page 1 of 1

SQLite help

Posted: Monday 28th January 2019 4:06am
by Godzilla
I've been aware that Gambas has SQLite functionality. But its something I've always shied away from because I don't know SQL and I don't understand it. But lately I've set my mind to learning it at least enough to use it, because I really need it for my coding. I've been hardcore researching, and trying to figure it out the best I can.

Gambas-related SQLite examples are sparse. But the best example I've been able to find is:

https://kalaharix.wordpress.com/gambas/ ... om-gambas/

Using that code as a tutorial, I've built on it and expanded on it as best as I could figure out. I've come up with a proof-of-concept test project which I'll attach to this message.

I've primarily focused on only the basic SQLite functionality that I need for my code, most of which it appears I've been able to make work. But at the same time, I built this project through a lot of guesswork and not really knowing what I'm doing.

I'm requesting two points of help:

1)

One function I need in SQLite which I can't figure out how to implement with Gambas is: for my main project, new data (dozens of rows) will periodically come in and will automatically be added to the database. Easy enough. However, with some of those rows, the primary key will already exist in the database. We can ignore those rows. BUT, some of those incoming rows will contain updated information in one or more fields, which in such instances those rows will need to be updated in the database.

I need to determine, amongst the already-existing primary keys in the database, if any data in the incoming rows are different. And if so, update only those rows needing updated.

My understanding that if I were to blindly update all existing rows where no data has changed (when dealing with a dB with tens of thousands, maybe hundreds of thousands of rows) would cause big I/O operation bottlenecks and adversely affect performance.

2)

I build this test project using guesswork and not really knowing what I'm doing. Could someone who's experienced with SQLite look over the code and tell me what needs to be changed? Or if I'm doing things incorrectly or redundantly or something, could you tell me the proper method of doing it?

The attachment was coded using Gambas 3.12.2, in Linux Mint Mate 18.3, 64 bit.

To anyone else also trying to learn SQLite, please use this guesswork code with caution until someone can verify how correctly its been written. It has potential to eventually be a great tutorial for the Gambas community.

Re: SQLite help

Posted: Monday 28th January 2019 7:04pm
by cogier
I have looked at Databases before and I found them to be a bit complicated. I use CSV files to store data I want to use. Have a look at the attached program and see if this is simpler for you to use.
NoDB_DB.tar.gz
(16.49 KiB) Downloaded 20 times

Re: SQLite help

Posted: Monday 28th January 2019 11:37pm
by Quincunxian
Hi Godzilla.
Your code is fine and you seem to have grasped the basics well.
I do a lot of SQL programming in both MySQL & SQLite in Gambas so please feel free to ask any questions you have and I'll try to assist - this is what the forum is for.

Few things...
If your program is going to be just a personal one and not shared then SQLite is perfect for the task.
If you want to share data across a home or office network then I would suggest MySql as it has the right security capabilities to allow the admin to select who can add, change or delete records.
Please read this from the https://sqlite.org/whentouse.html SQLite web site.

Having all your code in one class ( in your case, FMain) is ok when you are testing something but you will need to break this down into additional forms or user created classes as the code will simply become pretty hard to debug and pretty unmanageable.

I'll break down your code into a few additional classes and forms and you can see what I mean.
Give me a day or so and I'll post something back for you to have a look at.

Re: SQLite help

Posted: Wednesday 30th January 2019 6:59am
by Godzilla
cogier wrote:
Monday 28th January 2019 7:04pm
I have looked at Databases before and I found them to be a bit complicated. I use CSV files to store data I want to use. Have a look at the attached program and see if this is simpler for you to use.

NoDB_DB.tar.gz
Hi cogier thank you for your reply.

What I've been using up to this point is almost identical to your CSV system. Nowhere near as brilliantly coded as your example, though! Just your simple example taught me useful coding methods I wasn't aware of, and I thank you for it.

CSV or text files will be perfectly sufficient for the average coder. But for me and my tendency to push things to the very limit, the sheer volume of data I now find myself managing forces my hand to take things to the next level. I'm not ready to switch over to SQLite just yet, but I'm getting close. My hope is to have a fully-functional SQLite example that will cover the basics to the point that anyone who needs SQLite functionality will be able to simply copy the code into their projects and be good to go.

Re: SQLite help

Posted: Wednesday 30th January 2019 8:31am
by Godzilla
Quincunxian wrote:
Monday 28th January 2019 11:37pm
Hi Godzilla.
Your code is fine and you seem to have grasped the basics well.
I do a lot of SQL programming in both MySQL & SQLite in Gambas so please feel free to ask any questions you have and I'll try to assist - this is what the forum is for.

Few things...
If your program is going to be just a personal one and not shared then SQLite is perfect for the task.
If you want to share data across a home or office network then I would suggest MySql as it has the right security capabilities to allow the admin to select who can add, change or delete records.
Please read this from the https://sqlite.org/whentouse.html SQLite web site.

Having all your code in one class ( in your case, FMain) is ok when you are testing something but you will need to break this down into additional forms or user created classes as the code will simply become pretty hard to debug and pretty unmanageable.

I'll break down your code into a few additional classes and forms and you can see what I mean.
Give me a day or so and I'll post something back for you to have a look at.
Hi Quincunxian, thank you for your reply. I appreciate your comments that I'm on the right track, and your offer to help.

Yes, the dB I'll be using is just personal, being handled on one computer, and not shared. From my research, SQLite seems the way to go for my purposes. And your very useful link further convinces me that SQLite is a bullseye.

I am very interested in seeing what you mean by breaking down the code into additional forms or classes. I'm looking forward to studying your example code very closely.

The one thing that's holding me back from switching over from my current system of reading / writing to text files, is how to handle incoming data in the way I need for it to be done. Everything will be automated. So my system needs to handle things properly under different circumstances. These circumstances will be:

1) adding incoming data to the dB, consisting of rows whose primary keys don't already exist in the dB
2) ignoring incoming data, whose primary keys already exist in the dB, and whose field contain identical info to what's already in the dB
3) incoming data whose primary keys already exist in the dB, but where one or all fields contain new information, indicating these rows need to be overwritten or updated

I'm thinking the best way to handle this would be inside a loop. I have an idea of the logic needed to handle all these 3 situations.

Code: Select all

For Counter = 0 to IncomingData.Max
      rTest!s_name = IncomingData[Counter].Name   'this is the primary key
      rTest!s_telephone =  IncomingData[Counter].Telephone
      rTest!s_sales =  IncomingData[Counter].Sales
      rTest!s_updated = Now
      Try rTest.Update      'we use Try to prevent a crash and to capture the error message
      If Error = "T" Then   'if true, we know the primary key already exists in the dB, so we handle it thusly
         If Comp(dB.Telephone, IncomingData[Counter].Telephone, gb.Binary) = 0 OR 
            Comp(dB.Sales, IncomingData[Counter].Sales, gb.Binary) = 0 Then
             'if either of these conditions indicate changed info, as compared to what's in the dB, we need to update
               (here, we execute whatever command that forces the dB row in question to be updated with the rTest data)
         Endif
      Endif
Next
What i don't know is:
1) dB.Telephone, db.Sales... these are pseudocode, because I don't know how to read info from the fields specific to the one row in question that produces the "T" error
2) what command forces the update of that one specific row containing the already-existing primary key without throwing an error

Once I get these hurdles out of the way, I should be ready to switch over from my current system of reading/writing text files. I'm looking forward to it!

Thank you Quincunxian again for any help you can provide.

Re: SQLite help

Posted: Wednesday 30th January 2019 9:20am
by Quincunxian
Almost finished and have examples of just about all you want to do.
Some tidy up and testing to be done but should be able to post shortly.

How is the data coming in ?
Text file, Serial port, Ftp, Other ?
If it's a bunch of text files in a folder then on application start you can just grab them all and process into the DB provided the format is consistent and then move to a backup folder....
You can then set this task to a timer and any new files that are added while the application is running will be processed.
One thing that you will need is a Key provided from your data files to identify an existing record in the DB.
You can use Customer Fname & Sname but could run into issues if you have multiple "John Smith" 's

We can work on that as you go.

Re: SQLite help

Posted: Friday 01st February 2019 8:50am
by Quincunxian
Finally done and had a lot of fun putting this together and learnt a few new things along the way.

This is a simple sales transaction application using SQLite and provides an example of how it can be used.
Note # I use my personal coding style and this example is meant as a simple reference
Not a 'best practice' Guide !

It has a basic Logging function with the ability to review logs files.

It has the ability to manually Add/Change/Delete/Clone records for Customers, Products & Sales.
When in the 'Add' screen you can 'Add Another' without having to return to the main display.
It can display records in a GridView or TreeView as required.
Double clicking any record in the GridView or TreeView will automatically select that record for 'Change'

It will create the database based on the Schema for the respective tables from a Text file (located in the 'Data Folder' in the Project Zip.
You can also create a test data set from lists of Male/Female/Surnames from text files , also located in the 'Data Folder'.
Test data files are uploaded by a timer function and new Customer & Sales records are added with the appropriate logging.
if a customer already exists then it will update their Mobile & Telephone data fields.

It has a Bug management system that allows you to determine ( in most cases) the exact line number and backtrace of the error. (There is an overhead to this but it's worth the effort with a complex debug session)

There is a database review form that will allow you to list all the tables and fields in the current database.
It will allow you to write an sql creation script and automatically sets up the variables you can cut and paste from a text box into your code if required.
I use this as a debugging tool and normally remove the form for the final application Make.

There are some ToolBox classes which provide some functionality to assist with DB functions.
Such as....
Loading a combo or list box with a text field from a table.
Getting a record Id given a (unique) text field
Getting a record Text Field given an Id
Getting record counts from any table
Getting totals from Currency fields ( ie total sales)

There is a 'ToolBox class to set up maintenance forms.
It sets up the gridview size, adds the column headers and sets the column size as required.
It will remove any spaces from text input fields prior to a validation check.
After validation has been run it will change all the label forecolours to Black.
(I change my label text to red to identify that there is a validation error with a field )
It will set ComboBox Indexes to 0

You will find a DataTable Class.
This allows the dotted reference to all table names so in the event that you have to change a name at any time, you can do so in one place.

You will find a SQL_database class.
This allows you to set the connection and create a database and specific tables as per the creation scripts.
Note # The database review form can create the SQL scripts with the 'Drop Table if Exist' option. If you run this script and you have data in the table ... it's toast / gone / unrecoverable.

Any questions about the code, send me a post or pm.
Caveat: I've tested most things; but as Mr Murphy is alive and well there could still be bugs....

Enjoy (Hopefully)

Re: SQLite help

Posted: Monday 04th February 2019 8:28am
by Godzilla
Quincunxian thank you again for your replies and your amazing code example. I feel like Spock exploring Vger every time I study it! I'm grasping the basic fundamentals of what you're doing, but its going to take more studying to fully understand it. I'm happy to have been given this opportunity to do so. Thank you again, and thank you for taking the time to write something so complex and thorough. You went above and beyond!

For me, the data is coming in by XML files over a network. I was lucky enough to find a Gambas XML parser example a while back. And with a lot of modifications, the parser works perfectly for my purposes.

I assign the parsed XML data to a class array (essentially the same as a user-defined type from the VB6 days). From there, my idea is to loop the data in the class array into an SQLite dB. The XML primary keys are always unique within itself. However, the XML is generated without regard to what I may or may not have saved in my dB and I have no control over it. This means there will always be a percentage of duplicate data to deal with.

As I sit here thinking about it, I suppose one way to deal with duplicates is I could query the dB once after each parse loop. In the event of a query result, compare the parsed data to the result of the query (if there is a result). If the parsed data is updated in any way (compared to the dB query result) then put it in the class array for later row updating in the dB. Otherwise, skip it.

And as I think about it further, if I were to simply add/update the dB as needed in the midst of each parse loop, it would eliminate the need of a class array altogether. With a lot of planning, a lot of coding, and using your code example as a guide, I think this could certainly work.

I'm not sure what the impact on speed might be. Each full parse consists of thousands loops, thus would require thousands of queries per each full parse. All this in a dB with probably 100k rows or more, each row containing a dozen or more fields. But on the other hand, SQLite is designed for this kind of thing and optimized for speed. I think a ram drive will be essential for something like this, which is not a problem except the risks inherent with ram drives.

This will certainly be an interesting project. I thank you once again Quincunxian for your invaluable input, code example, as well as the time you put into every last detail. Even to the point of including a Godzilla icon! Mindblown. :D

Re: SQLite help

Posted: Monday 04th February 2019 10:58pm
by Quincunxian
No problems Godzilla,
I'm semi-retired so it was a fun project to do and a good way to pass on some knowledge to you and anyone else who wants to see a working example.
XML files will work really well with a similar core structure so you are well set up to experiment with it.

The bit's I learnt was to be able to get a date by subtracting a value.
The DateDiff function will give you the difference provided you know both dates but I needed to calculate the date back in time for the SQL statement to determine transactions processed over a period of time.
Apparently there is no standard function to find a date, say 30 days prior to the current date.
I saw from one of the examples on date functions that you can convert a date to a float value with the days on the left hand side of the decimal point and the milliseconds on the right.
Subtracted 31 days from this to give a reasonable rounding of a 30 day period and converted back into a date string for the query.

I'd also never done any transactional work with timers before and that was quite a bit of fun.
You need to consider quite a few things when you interrupt the timing sequence to do maintenance work.

Anyways - have fun and all the best with your project. If you need me to explain anything, drop me a post and I'll see what I can do.

Re: SQLite help

Posted: Monday 04th February 2019 11:02pm
by Quincunxian
Forgot to mention - don't worry about IO time with your transactions.
Gambas if very fast and the SQLite engine is not too bad either so i would just 'see how you go' before you start to worry about that. Unless you need the data transacted in near real time then you should be fine.