SQLite-ListBox Read/Write strategy

In a previous project that use a SQLite data base, I used the following desing:

Past design:
a. Load the data from the SQLite data base in the ListBox,

b. Work on the ListBox contents (for the display and print operations)

c. To make changes to the ListBox, I open a “Show one Record” Window and read the selected Row from the ListBox into the new window (into TextFields). Then, if I modify the data in that window, I report the changes in the ListBox and save them into the data base too. (I do not save the data into the SQLite data base and then reload them from the data base and re-fill the ListBox).
I was feeling a bit insecure for unknow reasons. After some years of use, I never get any report (bad or good) from the application users. No data loss, no bug, no crash and not even a feature request !

d. Even the export feature use the ListBox contents (export the data from the ListBox…).

Future design:
Now that I will use a SQLite data base in another project, I am questionning myself about this design.

Will I follow the “old design” (read Past design above) or will I make some changes:

Always write the change(s) in the SQLite file (then read the data back from the SQLite file),
Always read the data from the SQLite file (then read the data back from the SQLite file).

How do you do these things in your projects ?
What is your advice ?

Will your application be multi-user ?

No Joost, it will be a multimedia (creator and) displayer. It have to deal with text and images (now, but tomorrow ?)

To allow for potential future multi-user use I would always write the data back to the database then reload the listbox. SQLite is a very fast database and the reload takes minimal time, in my opinion.

I use SQLite extensively and some of my apps have more than a million entries in the tables. Utilising indexes and carefully crafted select statements/views I have a maximum of a 1.5 second wait for the listbox to refresh.

Also, I would enable for edit certain columns in the listbox that will allow the user to edit directly the item (like a spreadsheet). Then update the database directly following the edit. Then you do not need to reload the listbox.

So, no Record View Editing ?

If we speak about performance. Why not just check for errors after writing into the database and if everything went fine, just go on? No need to reload the Data from the Database. :slight_smile:

Damn! While i am writing, the following post appeared: [quote=196700:@Simon Berridge]Also, I would enable for edit certain columns in the listbox that will allow the user to edit directly the item (like a spreadsheet). Then update the database directly following the edit. Then you do not need to reload the listbox.[/quote]

I was too slow… :smiley:

My collegues at work would bash me if they would have to wait 1+ seconds while working in any Table :wink:

Because of this i sometimes do the work in Threads, so that the user can continue it’s work. But if you do SQL in a Thread, try to avoid Prepared Statements. I have the feeling that these cause “lags” in Threads. But maybe, i am just doing something wrong there. :wink:

(“try to avoid Prepared Statements” means; just do those querries which syntax can be directly influenced by users input, as Prepared Statements in Threads)

For every table that I have in my database I have a corresponding Class that read/writes to the db. I have written an app that automatically generates the class code (download: https://dl.dropboxusercontent.com/u/10747116/InternetUpdates/SourceCodeGenerator/SourceCodeGenerator.pkg)

The problem I have with writing to the db and then just moving on is that the listbox does not reflect the current data (unless you have used my suggestion of directly editing in the listbox). In Emile’s original design he opens a window where the editing is performed - I don’t use that method as I don’t like windows popping up all over the place (I stress, my personal preference). If I have to have a window then I use a sheet window and the ShowModalWithin method.

I agree with you, Sascha, on the 1.5 seconds. However, sometimes it is necessary to reload a listbox and, if that listbox has a few thousand entries then this sort of delay cannot be helped (I know that it can by using by populating on demand but my users aren’t that fussy). I am lucky inasmuch as my users are used to mid-range to mainframe screen to screen and 1.5 seconds is fast to them!

Since SQLite supports triggers you could use this mechanism to decide updating screen-controls or not.

[quote=196724:@Simon Berridge]For every table that I have in my database I have a corresponding Class that read/writes to the db. I have written an app that automatically generates the class code (download: https://dl.dropboxusercontent.com/u/10747116/InternetUpdates/SourceCodeGenerator/SourceCodeGenerator.pkg)

The problem I have with writing to the db and then just moving on is that the listbox does not reflect the current data (unless you have used my suggestion of directly editing in the listbox). In Emile’s original design he opens a window where the editing is performed - I don’t use that method as I don’t like windows popping up all over the place (I stress, my personal preference). If I have to have a window then I use a sheet window and the ShowModalWithin method.

I agree with you, Sascha, on the 1.5 seconds. However, sometimes it is necessary to reload a listbox and, if that listbox has a few thousand entries then this sort of delay cannot be helped (I know that it can by using by populating on demand but my users aren’t that fussy). I am lucky inasmuch as my users are used to mid-range to mainframe screen to screen and 1.5 seconds is fast to them![/quote]

But you could update the ListBox after a successfull db write operation, without querrying the db again.

If you keep the ListBox content in “shaddow” Lists like arrays and querrie the db only when needed or regulary in multi-user environments (just asking for updates, not for db rows or db tables) AND do those querries in Threads (each with it’s own connection to the Database(s)), you could update the UI by using Timers, Focus Handling and others and keep it responsive at the same time.

[quote=196629:@Emile Schwarz]How do you do these things in your projects ?
What is your advice ?[/quote]

I think this question is as so often, not so easy to answer. Because it depends highly on what Data you handle (Blobs, Strings, …), if the Database is stored locally or in a network folder or on a Server, if it’s a single or multi-user environment and so on.

Talking about SQlite in a single User environment and a locally stored db, you can do what you can do best. The main challenge will be the performance of the ListBox itself, not the db tasks.

Just try to keep updates of your UI as rare as possible. Only update UI elements which really changed and only when they have changed. If you can be sure that everything reagarding your db is working just fine, do not querrie it just to “make sure” it has the Data you already have on the screen or in memory.

Oh! And as always: Just load Data from a db which is really needed. In case of a ListBox; just load the rows which are visible to the user + a few more to be prepared for scrolling + reloading additional rows.
Let the db work for you. Do not make calculations based on the Data in your ListBox, let the db calculate and then jsut deliver the results into your ListBox. :wink:

Aaaand, Profiling your code will help to find the “real” causes of lags :slight_smile:

Thank you all for the advices.

Now, the hard part: the implementation.

I will make some search on my own, but do you have some url that explain how to implement them to give… ?

As an example, how can I read and display data to pupulate 100 lines (to fill the screen and to have some Row above and below) ?
(this is what was noted as “ListBox display on demand”)

[quote=197003:@Emile Schwarz]As an example, how can I read and display data to pupulate 100 lines (to fill the screen and to have some Row above and below) ?
(this is what was noted as “ListBox display on demand”)[/quote]

In mySQL f.e. you use the LIMIT parameter of the SELECT command.

[LIMIT {[offset,] row_count | row_count OFFSET offset}]

You can set an Offset (the Starting Row) and a Row_Count (the number of Rows). And with this it’s easy to “jump” X lines forward and backward. :slight_smile:

Dont know if its of any help but I have always re-queried after saving the data. In this way you are using fresh upto date information, and you will be able to see if anything has saved but not in the way you expected it. In multi user you would need to do this anyway.

I create a method that populates the listbox with the required data. That is called from the listbox open event. In the code that saves the record, I put a little condition at the end that resets the edit/input fields but only if the save has been successful, and then call the method above irrespective of whether the save has been successful or not.

This way, the listbox data is always fresh, and if the save has not worked, the data remains in the edit/input fields so I can correct it and try again. Depending on the error code, you can always popup a small window or messagbox with the reason, and you could also popup a messagebox confirming the save has been successful if that helps the end user.

This can cause lags while the Window/Container is drawn on opening. I would recommend to move this code to another location. If there is no other trigger location, just put it in a Timer with Mode Single and maybe 500ms? So that the Tiemr would populate the Listbox 500ms after the Window/Container has been opened? Even better would be a Thread which would load the DB Data and a Timer which populates the Listbox with the Data the Thread collected. :wink:

Here i would like to recommend to just update the ListBox Cell/Tags/… instead of making another DB call. Only exception is: If the changed ListBox row can affect other rows Data, it may be needed to just re-querry the Data from the DB.

[quote=197031:@Sascha S]This can cause lags while the Window/Container is drawn on opening. I would recommend to move this code to another location. If there is no other trigger location, just put it in a Timer with Mode Single and maybe 500ms? So that the Tiemr would populate the Listbox 500ms after the Window/Container has been opened? Even better would be a Thread which would load the DB Data and a Timer which populates the Listbox with the Data the Thread collected. :wink:

Here i would like to recommend to just update the ListBox Cell/Tags/… instead of making another DB call. Only exception is: If the changed ListBox row can affect other rows Data, it may be needed to just re-querry the Data from the DB.[/quote]

I kinda get the idea you are suggesting here, my tables are relatively small though, even the biggest is only a couple of hundred rows maximum at a time; the majority are much smaller, so im not sure if there would be any real impact. Im looking at using threads for a different part of the sytem but honestly havent looked into the intricacies of setting them up yet.

Most of the tables are linked in some way, so I kinda think its the best way for my stuff. Whichever table is actually being updated, I load its primary key into the rowtag which gives an easy handle to the record ID when doing the save itself.

Given the above, Ive never seen any real world slowdowns on the data or the drawing. I suppose a timer/thread may be a good idea to measure the time difference between the methods for a purist approach.