SQLite In-Memory Table Demo using the SQLite Driver

Hello -

I discovered that you can easily use SQLite memory tables by way of the SQLite driver.
Went ahead and made a simple app to demo it. I wrote it in C11, but it might work as far back as C9. Not sure.

This is an alpha version. If you have any bugs to report or suggestions, please holler.

I hope it can give you some ideas.

2 Likes

Hey Jeff,
Did you try any tests for performance against the regular in-memory driver?

Hi Rick - No performance tests yet. I can see lots of uses for SQLite tables where you want to create stuff on the fly and be able to use SQL on it.

Itā€™s really neat that you can do ATTACH DATABASE for loading additional SQlite disk files. So your OwnerID on the TurboSQL table might be just a single SQlite file, but you can load multiple SQLite files via ATTACH DATABASE.

If you have any SQLite files, you can test this right in my demo app.
Try this:
ATTACH DATABASE ā€œ.\YourSQLiteFile.SQLiteā€ AS File1
Then Execute that by pressing F8 (or Ctrl+Enter). There wonā€™t be a response unless thereā€™s an error.

Now you can list the tables in that database file:
SELECT name FROM File1.sqlite_master WHERE type = ā€œtableā€

Hopefully, you see some table names there, which you can use to construct another query to list that table.

You can ATTACH multiple databases that way and query multiple tables at once.
And itā€™s all managed within PROP:SQL.

I havenā€™t tried join yet, but thatā€™s pretty powerful:

John Taylor did a lot of performance testing in his February 2007 article for ClarionMag

https://clarionmag.jira.com/wiki/spaces/archive/pages/399450/ClarionMag+monthly+PDFs+and+source+ZIPs+2007

Itā€™s the 2007-02 pdf and zip (source files) that contain his SQLite classes

Hi Jeff,
Very nice example. I have a question, why do you call the windows API ā€œInvalidateRectā€ on EVENT:SIZED?

This is the first time I have seen this done.
Thanks!
Edgard

Thanks Graham. I was trying to find that article but totally forgot the authorā€™s name.

Thatā€™s where I got the ā€œATTACHā€ idea, but had not considered trying it in PROP:SQL before this week.

IMDD would certainly be more suitable for cases where youā€™d want to use appgen to create forms/browses. I donā€™t know if a TurboSQL table could be used in the same way (such as for inserting a record on a browse in the standard way). Seems like it would be more trouble than itā€™s worth in that regard.

I see tons of uses for the In-Memory SQLite, but not as a ā€œwhich is better, IMDD or SQlite?ā€ kind of thing.

But you can also use SQLite for non database related stuff too.

For example, in my demo app, try this:

!To create 4 random 10 character strings.
SELECT hex(randomblob(10)) as R1,hex(randomblob(10)) as R2,hex(randomblob(10)) as R3,hex(randomblob(10)) as R4

!Or select an INT as hex (This requires a newer version of sqlite3.dll because the one that ships with Clarion doesnā€™t suport printf():):
SELECT printf(ā€™%08Xā€™, 99119191)

You can get the SQLIte version of your sqlite3.dll in the demo too. The version I just downloaded is 3.32.2, but the shipping one is 3.7.11. Support for printf() came in 3.8.x
SELECT sqlite_version() AS Ver

These functions are all for free and itā€™s really simple to implement them.

https://sqlite.org/lang_corefunc.html

And date/time stuff (with caveats) https://www.sqlite.org/lang_datefunc.html

Thereā€™s a lotta neat stuff in there.

Hi Edgard - Thanks. InvalidateRect forces a re-draw of the rect() in question. I was getting occasional artifacts on the screen when moving the controls around during resize. DISPLAY() would not clear them up so I used a bigger hammer..
I used InvalidateRect() a lot on my C6 apps for certain situations. Maybe a different technique would have worked, but I was unsuccessful at figuring it out with plain Clarion code.

I have updated the repository with some small improvements. instead of parsing sqlite_masterā€™s.sql to get the column names, pragma table_info() seems to work fine.
That allowed me to get rid some SystemString parsing that I didnā€™t like too much.

Also made it optional to display the data type.

Yet again, the repo has been updated.

Federico Navarro added a window for testing threads and demonstrates that the in-memory data is usable across threads until the turbosql table closes (which disposes of the in-memory data).

He also taught me that if you use ā€˜:memory:ā€™ as the OWNER string (along with the ATTACH line) then you donā€™t need a disk file at all.

can some one explain this ? DummyDBOwner = ā€˜:memory:ā€™ ! Per Federico Navarro, and it works!

what is this all about, looks interesting?

Stev - SQLite has a built in in-memory database called.":memory:". By setting the OWNER of the Clarion SQLite FILE to ā€˜:memory:ā€™, a SQLite disk file is not needed.

However, in order to make use of the memory database, you still have to ā€œATTACHā€ to it. Then you can dynamically create and fill tables 100% in memory.

Some people have tried using ā€˜:memory:ā€™ as an OWNER in the past, but since it ā€œdidnā€™t workā€, it was assumed that in-memory SQLIte tables donā€™t work in the Clarion driver. But if they would have also used ā€˜ATTACHā€™ to attach to the memory database, it would have worked.

thank you very much! where was this documented? and does it apply to any other drivers?

becuase i would like to try it with the russian DLIB runtime databases driver source code we bought. i have found the russians in moscow but they cannot be contacted and we have taken over the source code since we paid a descent some for it.

Stev - :memory: is not a Clarion specific thing itā€™s a built-in database inside SQLite. You can attach to and access it in Clarion via PROP:SQL as demonstrated in the demo. https://sqlite.org/inmemorydb.html

i see thank you for the information.

I modified the demo a little bit to demonstrate how fast it can be to load a table into memory.

Instead of pre-loading the data via an SQL script, the demo now uses a pre-built SQLite 198K record table. It adds 36MB to the repository size though.

On my my VM it takes less than .25 seconds to load those rows into memory. Desktop is about .13 seconds.

Queries are a bit slower than when there are only 500 rows, but itā€™s still pretty dang quick.
I havenā€™t tested how indexes would impact the loading and query times, but queries are really fast without them.

2 Likes