UnQLite Users Forum

Is unqlite good for unstructured arbitrary user data and searching ?

append delete DD2

Hello forum,
Apologies if this is something of a 'noob' question - databases where never my strong point.

I have the need to for a lightweight database where the data is ideally mapped to a file. My DB needs to store some predefined values and a set of arbitrary user defined values: I see this as a table with some user defined columns i.e.

key, name, size, type, user1, user2, etc
001, f1.txt, 235, xml, x, y, z, ...
002, f3.txt, 675, xml, a, b, c, ...

I need to be able to search for any entry on any column value.
Would the Jx9 interface allow this?

Reply RSS


append delete #1. chm


Yes Jx9 should be a great fit for this typical use case. Just take a look at the following samples on how to structure your data with Jx9 and come back here if you have any trouble.

Jx9 Datastore intro: https://unqlite.org/jx9_samples.html

append delete #2. DD2

This post was deleted by its owner

:: @DD2 added on 12 Jan ’18 · 09:54

OK, thanks for the help.

I am however struggling with the Jx9 API. There are 2 main issues that I'm unsure of:

1. The records in my collection are uniquely identified by a 'key' field. When I store one or more records into the collection using the Jx9 db_store(..) method how do I ensure the record actually contains that key. I've tried playing with the db_set_schema(..) and db_get_schema(..) calls but they didn't seem to affect db_store() in any way.

2. The records are identified by a key so when I write an record with an existing key I'd like the DB to update the existing record. What seems to happen is the new record is simply appended into the collection so I end up with 2 records with same key. I must be doing something wrong?


append delete #3. chm

Jx9 assign a unique 64-bit integer to each inserted record in your collection. No need to maintain any separate unique key by yourself.
You can extract the unique ID of a stored record via the special __id field which is appended automatically to each object after successful return from db_store() as shown in the doc https://unqlite.org/jx9_func/db.html#db_store (example included).

:: @chm added on 13 Jan ’18 · 03:47


append delete #4. DD2

Thanks for the reply @chm.

That doesn't really solve the problem though (unless I'm missing something obvious). Assume I have a collection called 'users' as per the site examples. I populate it with

Paul, 37, 1 Sandy Lane.
Rob, 22, 14 Marsh Road.
Mary, 30, 2 Why Close.

I now want to update the record for Mary. Using the __id scheme you mention I first have to search for the record Mary to get its __id. I don't want to have to store Mary == __id(100) elsewhere as that's what my DB was for in the 1st place. So it seems that in order to update a record I first have to search for it which seems inefficient.

Worse still when I insert new record I have to ensure none of them is actually an overwrite of an existing record. Am I missing something or is Jx9?

append delete #5. chm

In that case, you have to maintain the unique id of all your inserted record separately if you want to update them at a specific time. You can use the key/value store layer which is a good fit: https://unqlite.org/c_api/unqlite_kv_fetch.html

One last thing, could you provide a working snippet of your code. Perhaps you are missing something with your retrieval approach which I must take a look at. You can email me directly via chm at symisc dot net.

append delete #6. DD2

Will do when I'm back in the office Monday.

:: @DD2 added on 13 Jan ’18 · 16:50

I have emailed you. Thanks for the help.


(Leave this as-is, it’s a trap!)

There is no need to “register”, just enter the same name + password of your choice every time.

Pro tip: Use markup to add links, quotes and more.

Your friendly neighbourhood moderators: chm_at_symisc, devel_at_symisc