SQLite database importance

How important SQLite is to you? We are doing final review and this library is absolutely huge, like 400KB!! Any SQLite experts here?

1 Like

yes it very important for me because i want to allow offline rfid access on lift - how and what is to do is to store each 6 hours new updated list of rfid serials .
how system work offline - select count(*) from rfidlist where rfidserial=serialrfid

where serialrfid is read during authentication

if result of count(*)>0 that mean have acess otherwise no access.

that is what i want for 500-2000 rfid serials to store on sqlite

Not to detract from @Valon’s requirements, but IMHO the “disconnected” scenarios are M.U.C.H. less important these days. Again, not to say that it’s not important for some requirements like the ability to still RFID out a door when there’s an incident.

Have you used sqlite intensively? There are many features that we can remove to reduce size but not sure which of those are common and which can go. For example, last version we used was 300kb on size, today it is over 400kb. Both work the same to me!!!

i used just default features only:

-create db
-create table
-select,insert,update,delete (from/into table and where clausoles)
1 file(db),1 table (3 field max but latest time mostly 1 field because of changed algorithme)
-drop table
-drop db

rfid is used only inside lift butons (numbers only) of an building with 10-14 flour and each flour have 5 - 8 apartman per flour(one apartman have 3-5 rfid keyfob) if you have no rfid you can not select flour to go the only job what do is that db downloaded once in 6 hour (4 time on 1 day)

My point is, why does it only connect four times in a day? That is an old way of thinking - when wifi or networking was hard, when power management with networking was hard, when latency was hard… that kind of cached approach made sense. Now, it’s often more sensible to build NONE of that into a product and just jump to the central source of data in the first place.

(and no, I’m not saying that in your specific case you should use this methodology, resilience is clearly important when you’re allowing or disallowing someone access to their premises, you can’t have something not reliable and a local cache makes sense… but do you need SQLite for 600 IDs and floor numbers?)

because no wifi,and only poor unstable 2G communication,so only way is to use stored db with rfid only(no flour id) because we need just return value for relay to be true (counr( * )>0) or false (count( * )=0)

I use sql for educational purposes. Basic sql functions are enough for me, like @valon.

I had two points

  • what can’t you do with a (now 1-dimensional) list of RFID tag IDs that are permitted access, that you can’t do outside SQLite? It seems in your particular case it’s no longer a complex SQL problem, just a memory / size management question as there’s no complex joins needed (which may still be enough to push you to need SQLite - if you had 10x more cards for example, that would clearly no longer be a problem you’d want to deal with outside a database, even your relatively simple one)

  • if you had the floor they lived, they could only get to the floor they live like many hotels do these days (plus “community” ones where the restaurant and gym etc are). Just thinking of future version enhancements for you :wink:

excatly but that’s was enought
(if i want to expand for more i add more field) with true/false

example for table

rfid , gym ,rest
11111 , 0 ,0
2222 , 1 , 0
3333 , 0 , 1

if (11111 x 0) = no access
if (11111 x 1) = have access at gym …

as sayed above max 3 field if have gym,rest till now maybe will be more in future but still no requested for more than 3 field

idea is to make faster and simpler db management inside board as much is possible less data

1 Like

We also use SQLite in file and memory mode data sets.

I must disagree on this one, we are in most cases isolated from all internet access for (functional) safety reasons, connection is only allowed when devices are in a pre-defined safe state. Edge is still very real for many applications.

something still needs to talk to those leaf nodes/sensors, and that means inter-connection. I mean the whole IoT is a T because of this, so you have to agree that where we all came from ( NETMF 3x and 4x) the interconnection aspect is no longer as peculiar as it was, it’s virtually assumed. (also I didn’t mean to say that your device had to be internet connected, but I did imply network connected, so it can talk to something “internal”). And one of the key drivers in the TinyCLR OS journey is because of networking.

I am totally onside with seeing where SQLite has a clear and compelling case, but it clearly has a cost - if I had infinite memory and unlimited GHI development resources then I’d opt to have as much of SQLite as Gus would fund/integrate. But if I had to trade it off, I’m one who would now say I’d send my query over to my intelligent edge device within my network and just have it tell me the answer, as long as you made the networking bit robust as all heck for me.

In my case the G120 is the intelligent edge device :slightly_smiling_face:

fully agree. i develop troubleshooting application for medical devices. connected to the cloud, able to download logs from instruments or to work with off line logs. relying solely on a connection has never been an option for me. not for the embedded devices but remain valid for all situation.
and i will never use VS on line…

Gus - We do not use sqlLite but am trying to understand the issue better.

I would think(?) those that do or may use sqlLite would wish to keep all the features.
Is the size (400KB) mainly a concern for smaller memory devices, i.e. smaller than G120 say?
I assume, if code does not reference any sqlLite assemblies (GHI.SQLite) none of the 400KB gets compiled into the application, correct?

Perfect example! You do nto need it but you will always lose 400K of flash for nothing! SQLite core library gets built into the firmware. So it eats up flash even if you never ever use it. 400K gone for a feature you do not need vs 400K that you can use to deploy resources and code.

We are now looking into making SQLite load dynamically at runtime.

All SITCore hardware chipsets/modules have the same memory size so that is not the concern. For example, SQLite is now available on the 100pin SC20100 chipset! But we are making sure we have made a good decision there. If we take SQLite out, you get 400K more flash for your own use.

Thanks…now I understand!

I think the dynamic loading would be the way to go with SQLite, or any other ‘specialized’ assembly, i.e.
GHI.Glide, GHI.GHIElectronics.Accelerometer, etc

We do not use many of these ‘specialized’ assemblies also. Are they also gobbling up flash, for no reason?

Could a dual firmware be a starter option ? One that has the SQLite code compiled in and one without. The one without can throw a “Not Implemented” exception for the relevant functions.

With appropriate build scripts/pre-processer it can be part of your workflow to output multiple binaries.

1 Like