SQLite Forum

Timeline
Login

50 most recent forum posts by user cuz

2021-10-13
22:58 Reply: inconsistent output: insert on conflict with returning (artifact: a490a6b058 user: cuz)

I suppose he got it from the documentation.

See the INSERT...ON CONFLICT (aka UPSERT) documentation here: sqlite.org/lang_upsert.html#examples

As to the original question...

The OP is doing an UPSERT with RETURNING clause that works when the INSERT happens, but if conflicted and in stead an UPDATE happens, no row is returned (since no insert happened), which is probably as designed and as documented since the full RETURNING field-set might not be available to the UPDATE mechanism, the same as when the INSERT mechanism runs and the "excluded.xxx" construct is not available when INSERTing since no exclusion happened - so not sure the RETURNING clause can deal with both.

I suspect it simply works for the INSERT part only as designed, but that is pure speculation on my part and as far as I can tell, no such limitation is documented, so it may simply be a bug.

12:25 Reply: binary .dump format (artifact: 5ffa89363d user: cuz)

Yes I agree, my contemplation imagined something that will be handled by a text processor and even editor, which can still use the mentioned companion files, or if text-editing isn't required, then length sections can work where context dependence is a bigger issue than corrupt bytes I think, but true, in such a case it's certainly not "theoretically impossible".

The TLV suggestion can work also, but then the entire data should be TLV sections, (even Schema, normal data) like it is in the file-types Andreas mentioned, avoiding context dependence and simplifying readers/writers. Much like the usual way of serializing data.

Either way, the moment you can no longer load an sqlite dump into a text editor (or importable via the standard cli sql reader), I think it stops being what it aimed to be at birth. In that regard, Base64 is drop-in replacement/alternate for hex and can still work 100%.

I like the suggestion using SQLite DIFF files on ZFS (or similar), maybe writing a full backup weekly, say, and diffs in between - the "lightness" of which will wholly depend on how often existing blobs change.

I have not actually tried using diffs for backup in sqlite - I wonder if you (or someone else maybe) have done this and can say something about how well it works and mention any caveats?

09:51 Reply: binary .dump format (artifact: bd7d3d1c1b user: cuz)

A binary dump format is not only not available, it's not even theoretically possible since any byte sequence can be present in any blob which makes format bytes impossible, or at least very difficult - something like MIME boundaries works but is heavy on checking for boundary matches in content, which doesn't matter much for typical e-mail, but will for DBs. It also means even small blobs will add large boundary identifiers, which will take away from the saving.

I suppose a best-case enhancement for large blob data would be 3-to-4 encoding (aka Base64) rather than the current hex representation in .dump files, which gives you a ~33% increase on blob size in stead of ~100%[1] increase and is de-duplication safe.

This change, or alternate ability will be needed on both the .dump side and the .import side, perhaps indicated with starting a Base64 binary with '0z' in stead of '0x' used for Hex, or some such. Should not be much effort to implement either, but it's certainly not available at this point.

Andreas' compression suggestion is better for full file deduplication mechanisms, but if you have intra-file data section (byte-run) dedupliaction it won't work, plus if the data is similar through blobs it will reduce size well, but if the data blobs are random or noise-like, compression would do more harm than good (same reason why PNGs of photos are much much larger than JPEGs, yet a PNG of a solid colour background weighs almost nothing while its JPEG is still huge). Text compresses quite well (because of similarity of a language's lexemes and morphemes), so if the ratio of text-to-blobs in the DB is high, then compression may be quite good regardless of the above. I suppose testing is needed for your data. Either way, I think that suggestion is the most viable avenue for you to explore if size-saving is your goal.

[1] I use approximations ~nn% because Hex strings include the '0x' added to a blob, if the blob was 3 bytes long, the resulting text would be 2 + 3 x 2 = 8 bytes long, which is nearly a 200% increase in size, and as blob length increase, that percentage approaches 100%. Similarly Base64 will have such a marker, but it also requires 3-byte alignment so can end with one or two "=" characters, producing a saw-tooth size-gain graph that smooths out (--> 33%) for larger blobs.
i.e - Lots of small blobs are the worst, whatever encoding you use.

2021-10-09
13:52 Reply: x86_64/amd64 sqlite3 binaries (artifact: 90fb69202d user: cuz)

You're correct in that nearly all hardware now is 64bit, but the problem here isn't the hardware, it's operating systems, and more specifically, software.

While most of the install-base for OSes are also 64bit these days, quite many are still 32bit, and even the 64bit OSes have to be able to run software in the wild, of which very many are still fully 32bit, and by that token, if they use libraries, it has to also be 32bit libraries.

Why are there still 32bit software around?, I hear you ask. Well there are different reasons for different 32bit software ranging anywhere from "couldn't be bothered" to "An enormous codebase that will take years to refactor" or just "using old outdated software that is no longer supported but it still does the job, so why not?". On Linux we are used to compiling stuff ourselves, but for most of the World, software comes pre-compiled, certainly 90+% of Windows or Apple user installed software and even these days on Linux it's more common for basic users to install from pre-compiled binaries.

If you doubt me, just see the ruckus caused when Ubuntu tried to drop 32-bit support. I am betting that any major OS that wants to remain a major OS will keep supporting 32bit software for many years to come, meaning people will keep using 32bit software and need 32bit libraries (such as sqlite3).

That's why it's still available.

2021-10-02
15:07 Reply: How to read Images from sqlite3 and show via opencv, PIL or Matplot lib ? (artifact: 67bd9291c4 user: cuz)

I'm afraid SQLite's domain ends at "storing the bytes and giving it back to you".

How you use those bytes, to display images, write them to file or supply them as codes to nuclear missiles aimed at the moon, is completely up to you.

It's irrelevant to SQLite, quite off-topic here, and most importantly, we have very little thoughts on that and may not be much help.

Someone here may even have some valuable input, but your chances of getting good help will be much higher on some programming forum.

2021-09-30
21:18 Reply: sqlite3_exec: Any way to validate the first argument? (artifact: 9b9468ede5 user: cuz)

To emphasize what Larry said (lest someone thinks we represent different schools of thought), Larry's contention is absolutely correct and the test I have is used in pre-production code and the return values are used in ASSERT() statements for sanity checks. I sometimes get involved with the question to the point one forgets to also convey sane programming practices.

There is a very big difference between testing code for a pointer you obtained by valid means yourself and is sure where it comes from but unsure whether it is still valid during the test phase of your software - and - "trying" operations on memory pointers which you do not own or know where you got it from, even during testing. Such a programmer should be taken outside and shot.

Also, the entire notion of you ending up at a point where you do not know if a pointer you are using is still good, is a sign of serious incompleteness of your program - which is ok in development and testing, but atrocious in production.

20:54 Reply: How to filter find duplicate and show row with same field using sqlite code? (artifact: 2b969bffcc user: cuz)

To add to the other excellent replies, a favourite of mine if I want to see how many and which records are duplicate, is to do:

SELECT name, COUNT(*) AS cnt, GROUP_CONCAT(person_no) AS nos
  FROM table
 GROUP BY name
 HAVING COUNT(*) > 1

20:45 Reply: sqlite3_exec: Any way to validate the first argument? (artifact: 5e446ddfdb user: cuz)

I don't think sqlite3_execute() has a helpful "check if my DB pointer is good" return mode, but almost every other function that takes the DB pointer will fail hard if wrong (fail how is hard to say, depends if the pointer points to valid but uninitialized memory, or invalid memory, etc.)

A test I usually do is simply prepare a statement with query: SELECT 2 * 3;

then check it returns SQLITE_OK, then step it, see the return value is good and check the single returned field is 6. Anything that isn't in perfect working order, from the SQLite DB engine to the DB being open or my own pointers, will cause that chain to fail, and it costs very few cycles.

20:15 Reply: Feature request: Stored Procedures (artifact: 1321b318dc user: cuz)

The same goes for Triggers - easy to implement in application code. And who needs things like Foreign Keys - it is not as if that could not be implemented in an application. (That was sarcasm - sorry).

Sarcasm? really? we couldn't tell. The problem here is your sarcasm proves the opposite point than which you are trying to make. Triggers in program code is impossible (rephrasing - exquisitely difficult). So is foreign key maintenance. By contrast, scripts inside Triggers and UDF's are both very easy to add and any other processing in your code is as easy as how good a programmer you are.

There is a lot of functionality in SQLite that not everyone needs or uses. A lot of people will never use FTS (I do), others will never need Geopoly (I don't).

The truth is that very many people use FTS and so it is included in the standard builds (but you can exclude it), as opposed to Geopoly that has to be included if you wish to use it, precisely because not many implementations use it. Both of these however are based on the virtual table mechanism (always included) and infinitely less complex than would be a scripting language for stored procedures.

Furthermore, such an inclusion as stored procedures wouldn't be able to be an add-on or loadable library, it would have to form intricate part of the main engine code and cause a lot of bloat and added processing, something punishing all of us for a thing which very few of us have real use for in SQLite.

I'll say this: There are a couple of requests like this almost every year, so you are not alone, but even tens of requests per year likely won't move the needle.

I'm not saying it won't be "nice" to have, just saying the gain-vs-input efficiency ratio is way too low currently. Don't lose hope though, we've seen lots of other claimed-to-be-bloaty additions making it into SQLite over time, such as Backup tooling, CTE's, Window functions, and the very recent RETURNING clause. That said, they all either had been requested much more (possibly monetary requests too), or took much less complexity/effort to add.

07:15 Edit reply: A little help with temp tables? (artifact: e76ca95844 user: cuz)

You are missing some correct syntax only, the idea is fine.

Try:

CREATE TEMP TABLE hello AS
  SELECT ProductId, UnitPrice, SUM(Quantity) AS Qty
    FROM S
   GROUP BY ProductId
   ORDER BY 3
   LIMIT 5
;

EDIT: Not sure "ORDER BY Qty" would work since it's an alias, it works in some engines. To be safe I changed it to "ORDER BY 3" (to order by the third column) or you could use "ORDER BY SUM(Quantity)" too.

This way of creating tables is quite useful, but note that performance is a slave to the elements.

If you need it to be fast for querying, perhaps using some unique key, then pre-creating the table with relevant keys and filling it is best, and it can be achieved in almost the same way with a bit more verbose SQL.

Example:

CREATE TEMP TABLE hello(
  ProductId TEXT NOT NULL COLLATE NOCASE PRIMARY KEY,
  UnitPrice REAL,
  Qty REAL
);

INSERT INTO hello
SELECT ProductId, UnitPrice, SUM(Quantity)
  FROM S
 GROUP BY ProductId
 LIMIT 5
;

EDIT: Removed the Order-By in the second example as it has no effect since the table will be ordered according to the primary key. Adding a second Index on Qty will make ordering the output by Qty a lot faster - which is a good example reason for doing it this way.

07:04 Reply: A little help with temp tables? (artifact: c099d849b1 user: cuz)

You are missing some correct syntax only, the idea is fine.

Try:

CREATE TEMP TABLE hello AS
  SELECT ProductId, UnitPrice, SUM(Quantity) AS Qty
    FROM S
   GROUP BY ProductId
   ORDER BY Qty
   LIMIT 5
;

This way of creating tables is quite useful, but note that performance is a slave to the elements.

If you need it to be fast for querying, perhaps using some unique key, then pre-creating the table with relevant keys and filling it is best, and it can be achieved in almost the same way with a bit more verbose SQL.

Example:

CREATE TEMP TABLE hello(
  ProductId TEXT NOT NULL COLLATE NOCASE PRIMARY KEY,
  UnitPrice REAL,
  Qty REAL
);

INSERT INTO hello
SELECT ProductId, UnitPrice, SUM(Quantity)
  FROM S
 GROUP BY ProductId
 ORDER BY Qty
 LIMIT 5
;
2021-09-28
23:09 Reply: Reset database (artifact: 9eb4a71222 user: cuz)

Is there an SQL i.e. a programmatic way to reset a database to the same state as it was when it was created?

So many contradictions in one sentence, but since we're nice people, let's try answer them all after dissection:

  • Is there a Programmatic way? - Yes
  • Is there a SQL way? - No
  • when it was created? (read: before) - Yes (Unlink the file, reopen in SQLite)
  • when it was created? (read: after the FILE was created but nothing in the DB yet) - Yes, same as previous point.
  • when it was cretaed? (read: after, with content) - No, you have to truncate each table, or drop and recreate it, then redo whatever data entry was part of the create process, and even then you will have a different schema version etc. The only way to do this programmatically is painstakingly manual, in any Database engine).

I am pre-empting failure when I have just read access to the database

How can you change the "state" of anything in the DB, never mind the DB itself, to anything else (historic or otherwise) when you only have read-only access to it?
Further to that, what good is resetting an empty DB if you only have read-only access to it? It contains no information that would be useful to you and you certainly cannot add any.

I'm going to just assume you were intoxicated when posting and that you do have some write access to the file/directory.

If it's some standard setup DB, you could just keep a copy of the file around by another name, then once all connections to it are closed, replace it with the pre-made file and reopen. Be sure those connections are closed though, else everything will explode and everyone will die.

15:19 Reply: User-defined functions in the CLI? (artifact: 8c4ca9f788 user: cuz)

I believe that actually loads programmable extensions, not SQL.

It does however differ from what the OP wanted, actually I'm not very sure what the OP wants. Loading extensions are very possible (not just in the CLI, but any SQLite engine instance where allowed), alternatively, compiling added code into the CLI is possible too.

The original request reads as if the OP wished to access the sqlite_create_function API somehow from an already-compiled CLI but not via a loaded library, which is very much not possible currently, as Larry explained.

I recall someone here posting an extension which lets you add sqlite UDF functions, taking a Name and an SQL statement, and any time after that you use that Name inside a query, the SQL code will execute.

Something like:

  SELECT MAKEFUNC('Twice', 'SELECT ?1 * 2');

  SELECT Twice(3);
--> 6

I thought it quite clever at the time, but had no cause to use it yet and unfortunately do not recall the name, but someone else here may.

15:04 Reply: Possible bug in group_concat() ? (artifact: 69c95b0314 user: cuz)

Larry, running some tests on my side (but not with debugger, and not being overly familiar with the SQLite internals), it's real hard to find other ways to cause this, but I did come to believe the problem originates in the Window function return rather than the group_concat() code itself.

This deduced by manner of poking the black box with a stick and seeing how it rolls, so I could be very wrong, but posted on the off chance it saves some bug-hunting time.

2021-09-22
00:04 Reply: Queries across databases (artifact: eb314c4733 user: cuz)

The SQL for it is simply:

ATTACH DATABASE 'c:/sqlite/db/contacts.db' AS contacts;

and in program terms:
sqlite3_exec(pdb, "ATTACH DATABASE 'c:/sqlite/db/contacts.db' AS contacts;" ... );

Which should all already work according to your given example, but note that:

  • attaching has to be allowed by your version of SQLite (or C# wrapper for it),
  • there are limits to how many DBs can be attached,
  • you need read/write privileges to the folder of the file you are attaching,
  • and to the DB file itself. (Depending on the Journal mode and intended use.)

To get rid of it again you can do:

DETACH DATABASE contacts;
2021-09-15
12:17 Reply: about "strict" mode (artifact: e960514ad5 user: cuz)

May I enquire as to how exactly the session extension is having difficulty with ROWIDs?

I'm not looking to argue, I plan to use the session extension soon in a project, and am now concerned of usage caveats. Being as specific as possible will be much appreciated.

Thanks!

2021-09-14
21:15 Reply: DELETE FROM … RETURNING (artifact: 961a86551c user: cuz)

You are correct about UPDATEs needing to be passable as they are updated - i.e. there is no way to defer constraint checking till the end of a transaction, so every record update needs to succeed as if it was the only update in the query, yet also not rely on the full completed state of the transaction to check its immediate constraints.
That also means that your update order within a single statement, if it was possible, will still not solve this problem.

I think the people who advocate for this are maybe more concerned with triggers and FK actions happening in predetermined order on UPDATE and DELETE, but for that it's best to rely only on yourself and post those transactions/queries in the exact order you want them executed. No engine quirk or different DB engine's habit can ever mess with it then.

18:47 Reply: DELETE FROM … RETURNING (artifact: 9166c79448 user: cuz)

No, neither should UPDATE ordering. And (to your previous question): No, calling it a bug is not a smart conclusion - hope that wasn't you or a loved one! :)

ORDER BY is an output modifier not a relational method. Once you dictate in which order the engine should update or delete entries, you remove from it any optimization opportunity, or possibility thereof. You attempt to interfere with the engine process and no longer simply the veracity of the data or statements. You neuter the QP - undermining the very utility of an RDBMS engine.

Further, I would always want the feedback to be in order of actual deletes, but have space for people needing a different order, however that can easily be done.

What about stipulating the order of UPDATEs or DELETEs? Well, IF that is really really needed, you can easily achieve that by simply stating exactly which item to UPDATE or DELETE, and then the next, rinse, repeat, in any order you want it to happen, whether writing an SQL script or doing it in your own code.

I can't see a situation in which you would want to UPDATE or DELETE whole blobs of records, but then also needing to fine-grain control exactly which of them go first, I mean, not unless you are using a severely broken relational DB schema.

As to the other reply specifying it MUST be so because of Postgres - Well, its outcome might agree with mine, but it's an argument from authority fallacy to start with, even though a really good authority in this case. I feel looking into WHY Postgres did it, and holding up that point would be a better motivated argument.

12:58 Reply: Slow query (artifact: 85c08744da user: cuz)

That does not look like a function that can be resolved by the Query planner easily.

I don't have your data so cannot easily test it, but this should make a difference, try testing it, especially if you have already added the index on timestamp and assuming timestamp is integer:

SELECT s.Timestamp, s.fst_field , ...
  FROM table s 
  JOIN table p ON p.Timestamp BETWEEN s.Timestamp - 9 AND s.Timestamp - 1

Note that this query on a billion-row table will produce output of easily 5-Billion+ rows (if your example data can be assumed an average indication). That in itself will take significant time to output, let alone query. Probably adding a WHERE clause to limit the Query domain to some specific time-frame or such would be better.

What is it you want to learn form repeating the close data together? There's almost certainly a better way to do the thing you want to do, but for that we need to know what the real information is you wish to glean from the query.

Let us know if that sped things up, and good luck.

2021-09-09
15:17 Reply: Write-ahead logging on SAN (artifact: d9c58a7d27 user: cuz)

To add to Warren's already good reply - SQLite's ability to guarantee Database ACID requirements has very little to do with whether the file is physically on a Network storage or not (or a SAN for that matter), but rather depends on the process being fully in control of the locking mechanism for the DB file. With WAL this is achieved as Warren describes, and for other DB journal types it depends on the locking mechanism of the OS.

So the most correct assessment would be: As long as All read/write control of DB connector processes originate from the same machine+OS, or rather, the same OS process space in memory (i.e. not different VM's that are merely on the same hardware), then you should not have any problem.

2021-09-08
17:06 Reply: Segmentation fault in function isLikeOrGlob() (artifact: f7f6afa30e user: cuz)

, and I didn't feel bad.

Yeah sorry, I think there is a large cultural gap between our phrasing.

I did not actually think you were feeling bad. Perhaps my reply was a wordier version of simply saying:
"That's nothing, great job mate!"

or if I was Australian:
"No worries mate, great job!"

or maybe as an English gangster:
"Relax Gov, 's all good."

or maybe an American one:
"Chill mother(bleep)er, we cool."

or perhaps just as myself:
"All good, Keep up the good work!"

:)

16:48 Edit reply: NEXT VALUE FOR in SQLite (artifact: ac3c8a1056 user: cuz)

So do you want to create an incrementing counter, or do you want to update the value and get a returned value back? Those two are not the same.

For creating a counter, Richard's example will work a treat, and it can never return a value because it happens invisibly to the statements (i.e. In the background).

For the second option, should the counter increase by being updated? or upon inserting a new row?

If updating a value and getting the new value back, that can be achieved with this type of query - imaging there is a table "t" with an integer field "i" in record with "id" 5 that will be our update target:

UPDATE t SET i = IFNULL((SELECT MAX(i) FROM t),0) + 1 
 WHERE id = 5
RETURNING i

If it has to be an Insert query then a very similar (assuming id is also a Autoinc PK):

INSERT INTO t(i, x, y....) VALUES
(IFNULL((SELECT MAX(i) FROM t),0) + 1, x-value, y-value, ... )
RETURNING id, i

BIG NOTE: You need a very recent release of SQLite to be able to use the "RETURNING" clause.

ALSO NOTE: I've not tested these, the principle is sound but there may be syntax errors. The docs are your friend.

16:45 Reply: NEXT VALUE FOR in SQLite (artifact: abdd917fef user: cuz)

So do you want to create an incrementing counter, or do you want to update the value and get a returned value back? Those two are not the same.

For creating a counter, Richard's example will work a treat, and it can never return a value because it happens invisibly to the statements (i.e. In the background).

For the second option, should the counter increase by being updated? or upon inserting a new row?

If updating a value and getting the new value back, that can be achieved with this type of query - imaging there is a table "t" with an integer field "i" in record with "id" 5 that will be our update target:

UPDATE t SET i = IFNULL((SELECT MAX(i) FROM t),0)
 WHERE id = 5
RETURNING i

If it has to be an Insert query then a very similar (assuming id is also a Autoinc PK):

INSERT INTO t(i, x, y....) VALUES
(IFNULL((SELECT MAX(i) FROM t),0), x-value, y-value, ... )
RETURNING id, i

BIG NOTE: You need a very recent release of SQLite to be able to use the "RETURNING" clause.

ALSO NOTE: I've not tested these, the principle is sound but there may be syntax errors. The docs are your friend.

16:11 Reply: Segmentation fault in function isLikeOrGlob() (artifact: b7bb7b9ff7 user: cuz)

Richard does have extensive knowledge of the SQLite innards. I'm not saying you shouldn't try simplifying, or that it is right for him to do the work, but I'm sure he is orders of magnitude faster at it because of said knowledge.

i.e: Do not feel bad for it, finding that bug is already a job well done.

00:10 Reply: Automatic indexing (idle question) (artifact: 95055dabef user: cuz)

Well put Larry, and I would like to add this somewhat in his defense (lest he thinks we are only negative):

For all I said/pleaded with Mr. Aranda, I honestly do believe he is not a troll. That is - I've not seen a post intended to solicit argument or tried to infuriate/bait others or derail the thread topic, merely some that are off-topic or not adding substance.

2021-09-07
21:36 Reply: Automatic indexing (idle question) (artifact: d0c96834dc user: cuz)

I think maybe you are mistaking this forum somewhat.

Your statements seem to try and prompt the sort of musings that entertain old men with puffs of smoke exiting their pipes while wielding wrinkled brows, deep stares and slow beard-rubs in that far-away mystical land, known only as "reddit".

More to the point - they don't add anything of value to any of the conversations, yet they appear with startling regularity. "Sounding clever" is not an admired trait here... one could say it achieves the opposite.

I won't go so far as another poster to wish you moderated away, you are welcome to stay and be ignored - Or stay, and join us proper by making the next statement one that is both relevant and helpful. Not only may it help the others, but I for one might come to enjoy reading them. And I'm not suggesting appeasing me - I am nobody, but I can't be the only one feeling this way, so I hope that if I am open about my thoughts it might be useful to you.

PS: Apologies to everyone else - The irony is not lost on me of now having posted a response that is neither helpful, nor relevant, to the original question.

2021-09-06
13:18 Reply: Automatic indexing (idle question) (artifact: d88484807c user: cuz)

Nice Idea. Full-self-driving for SQL Engines. I suspect however it will be much more delayed than the Tesla version.

The problem I think is that the Query planner and expert mode suggestions require a posed query to assess.

The only realistic way a DB can be full-self-driving is if every column in every table was also an index, and, every combination of columns (all permutations) up to and including the full set of covering indexes.

Obviously this would grow DB data exponentially, but perhaps if we could prune down the needed indexes....

To prune that list you have to consider removing (or not-adding) every index. To remove any index IA on column A (or any Index IABC on columns A, B and C), you have to answer the question:
"Can I guarantee that there will never be a circumstance where querying by this specific column (or combination of columns) will be useful/appropriate?"

Of course that is not an answer that can be known algorithmically at design-time. Perhaps a sort-of learning algorithm can observe the DB in-use for some time and prune the never-used indexes. But for this to be acceptable you have to ensure that any query that will ever be needed of the DB is run within that learning time-frame, and as Gunter already pointed out with his World-ending analogy, that is not possible or even feasible.

Perhaps then a kind of mode where the requirement states: "Run the learning algorithm while using the DB for every possible query that may be needed of it" to produce a robust set of indexes that is guaranteed to service at least those queries. I feel like this can already be achieved by a script taking as input a DB, a set of Queries, and outputs the distinct list of indexes obtained from running said queries using the CLI .expert mode.

Actually, that may be a worthwhile script to design. Perhaps with the added function option of simply adding those indexes. Not sure I would blindly trust it myself, but may be a good start.

12:15 Reply: Issues with sqlite3IsNaN() and HAVE_ISNAN (artifact: c7e8a45861 user: cuz)

One may have seen a lot of mathematics, but one has seen a very limited amount of SQLite documentation apparently. One can assert that because the documentation shows that SQLite will store 4.0 as 4 (Integer)[1] which does not help the demonstration of possible floating point byte-order problem in the stored data.

[1] From SQLite Data Types: "As an internal optimization, small floating point values with no fractional component and stored in columns with REAL affinity are written to disk as integers in order to take up less space and are automatically converted back into floating point as the value is read out. This optimization is completely invisible at the SQL level and can only be detected by examining the raw bits of the database file".

2021-09-05
22:29 Reply: STRICT tables and UNIQUE indices (artifact: ebf7f4ab5a user: cuz)

...we can have a deeper discussion about metaphysical terms like 'truth', 'logic' etc.

No we can't.

22:21 Reply: STRICT tables and UNIQUE indices (artifact: 9b6a3e5409 user: cuz)

NULL values from different rows ARE considered equal in SELECT DISTINCT.

This is true, simply because with DISTINCT Queries you face the same logic problem as UNIQUE indexes, but this time inverted - think of it like this: for a UNIQUE constraint, you have to prove two values ARE EQUAL to constrain them, which cannot be done with NULLS as described elsewhere, and for a DISTINCT requirement on a query you have to assert that two values are definitely NOT EQUAL (distinct) to add them both, and that is again something you cannot do with NULL/undefined/unset/unknown values. Ok, so both equality AND inequality is not provable for NULLs, what to do then?

Well, what DISTINCT queries (and sorters) use is the most sensible alternate: to lump all the unknowns together. Perfectly sensible and fine, you will find nobody crying about this and I think near all engines agree on it. In order to facilitate this "lumping together" of the unknowns, the engine and its documentation "considers them equal" - but that is merely a consideration, a means to an end and not an admission of equality. To think otherwise would be a logic mistake, and doesn't transfer/apply to the rest of the discussion regarding UNIQUE constraints.

As to your proposed language extension - sure, if you can make a case for needing distinct queries to separate all the null values into different rows in stead of lumping them together, perhaps it might be considered.

13:06 Reply: STRICT tables and UNIQUE indices (artifact: 11364a882f user: cuz)

I'm sorry to say, but this entire thesis seems biased towards imperfect expectation born from limited experience. Not being nasty - I have had this happen to me before (about other concepts) so I recognize this reasoning from my own imperfect past.

Let me deal with the points raised one by one:

... and as far as I know, there is no good work around with sqlite.

Several workarounds (good ones) have been demonstrated in this thread, even some by the very poster who originally posed the question.

algorithmic languages like c, c#, Java all allow the use of null to represent the unset concept in a way that is very useful.

So does SQL. (More below)

You can check if something is null, not needing to worry about the type in any way.

Yeah, same as in SQL. You even go right ahead from that statement to note exactly the method of checking null in SQL (not bound to any typed check at all):

In sql, u can check for nulls with “is null” logic in sql selects,

Exactly.

but for nulls in the unique index there is no way to do what I need

It's not clear what is it you need, but I imagine you need unique nulls (since that's the main gist of the post), though you never explain why.

One solution I’ve seen people suggest is to use partial indexes, leaving out the nullable The problem with this is it won’t work if I have many nullable columns. I would have to have an index for every combination of nulled column, N choose M combinations

That sentence is either unparsable/misunderstood by me, or false. Could you demonstrate the problem with SQL that doesn't work, and for which there is no suitable way to make it work? We'd be better-able to respond to that.

Another solution is to use triggers or conflict clauses, which as far as I know will stop the automatic “insert or replace” statements from working on unique matches. (I’ll be happy to be wrong on this one)

Again not understood - You seem to claim that the thing you want is possible (for triggers to stop inserts for unique matches), and then say you will be happy to be wrong.
Did you mean to say that there is no way for a trigger to stop the insert where a column of your choice may contain more than one null value? If so then you are wrong, it's a rather easy thing to do.

Sql is an incredibly old language and null represents a bunch of different concepts all muddled up together. I think it would not be done the same way today

SQL is incredibly new next to C, for instance. I bet it will be done exactly the same way again, and this time the Standard would probably be more clear on the silly notion that NULLs can ever be "duplicate". I'd say MSSQL would probably do it different if they had the opportunity to start over.

A claim of "duplicate" means that you can assert that one value (call it A) is exactly the same as another value (B). Only if A == B can be demonstrated, then it can be called "Duplicate" or "Not Unique". When both A and B are unknown, unset, unspecified, indeterminate (pick your favourite word for it) then by definition A cannot possibly be claimed to be exactly equal to B and therefore cannot be duplicate, non-unique, etc.

You can say that you want to insert items that are unknown because of an expectation, say "new" entries the user makes that were not accepted yet. Then the question becomes, Ok, but why do you only want to allow 1 of that and never more? Perhaps the user want to add 3 things so long that are not yet accepted? I'm willing to bet it's purely because MSSQL did not allow any more with their broken system, and so you designed your system that way and now your mindset is hooked on that notion. (That kind of thing certainly happened to me before)

The algo languages have made null to mean more of unset rather than sql s “unknown” meaning.
and This means bridging the algo and db worlds difficult because the sql world doesn’t do it cleanly and in particular sqlite can’t represent unique unset values in the index.

That can't be true, right? Please demonstrate the claim with code examples in an algo language (C, say) and SQL where these differences in meanings of NULL (i.e. "Unset" vs. "Unknown") would produce different mathematically sound outcomes.

Not allowing the unique unset means interoperability, and data loss issues if pulling data from an sqlserver db or other null unique capable software

Ah, here we get to the real crux of the matter. You are bemoaning the fact that SQLite (and Postgres and MySQL and many more) do not also allow the wrong and mathematically incompetent way that another RDBMS is doing it and so compatibility is somewhat hampered. This is like calling Tesla and complaining they they do not also support fossil fuel in their cars because the gas station nearest your house doesn't have an EV charger.

To answer the question though, I do very many project cooperations between specifically MSSQL and SQLite, it's never a problem, it's very easy to do, unless the aim is (which I suspect in your case) to make the SQLite table behave exactly like the MSSQL table - in which case, even though I think it's folly, near any of the workarounds already discussed earlier in this thread can accomplish it.

There is absolutely a valid use for both cases...

Not really, and I posit (from my own mentioned past misconceptions) that the only reason you think that is because you came from MSSQL and found that it works like that and then built previous projects around that expectation, adjusting your thinking to comply, and then being thoroughly surprised it wasn't so when you encountered another DB engine, like SQLite. You are simply used to doing it the wrong way, when you really open up your mind to what is technically correct (which I'm hoping to assist with here), you will see that the correct way is better and adjusting your projects to work with the correct way will in the long run help you.

...and I think sqlite would benefit from having an extra keyword somewhere that allows null to be unique in indexes perhaps for each column or for the entire unique index.

I do not disagree here, perhaps I would add that the benefit is small but I would welcome it if it placed no additional resource-requirement/strain on the SQLite Query engine. If it's a choice of small added benefit vs. faster Queries, I'm going to always choose the latter, and so should you.

Whether or not this comes to pass, you would do yourself an enormous favour to start designing DB interactions/projects from the expectation that more than one record can be unset/unknown (NULL) in a UNIQUE field in the your tables. Designing things so that it CAN work like that, means that the project/code will be completely compatible between MSSQL and SQLite (and any other DB you like).

Oh and PS: If you will petition MSSQL to improve their system and add a Keyword to their TSQL that would allow multiple NULLS in UNIQUE constraints - I can find you many people here, including myself, who will 100% join and sign that. :)

2021-09-01
12:57 Reply: STRICT tables and UNIQUE indices (artifact: db4044fa3c user: cuz)

I'm not sure there is a misunderstanding, but in the interest of honest discourse, let me state it like this: The problem has two facets -

##1 - Is it sensible to have NULLs be NON-DISTINCT values in UNIQUE INDEXES?
Certainly a couple fringe DB Engines feel this way, but most of them luckily do not. I'm not being flippant when I say "Luckily". There are Two considerations:
- If you declare a column to be UNIQUE, you either

1 -- absolutely never want to add two of the same things in there, or
2 -- sometimes want to add records which do not yet have their Unique Key assigned, so being able to insert a NULL helps.

Now both 1 and 2 are reasonable and sensible ways of doing things, and works in the wider used interpretation of the standard.
However, there is one little addition in that some people, while wanting to absolutely be able to add records without Keys to UNIQUE columns, now also want the added restriction of absolutely never being allowed to do that more than once. Why? This is less sensible, and I am not going to argue the case against it, you may have very valid reasons for doing so, but my point is that it should be clear this is not the general need, this is a very fringe benefit (and can already be achieved via the workarounds we agreed on).

##2 - Should it change?
SQLite uses the interpretation of the SQL Standard that is most sensible and used by most other DB Engines (except a fringe few). It is not wrong, not buggy, not silly, not a design flaw, not a problem that needs fixing, not a stupid choice from long ago that makes developing difficult and should be eradicated... none of those. What you are asking is to now, above and beyond its already best interpretation of the standard and accordance to other major engines, also expand its tool-set to allow a mode that is used by the lesser DB engines and lesser used application - in fact to widen its regime of interpretation.

You can surely see the irony of how this is the very opposite of what STRICT mode tries to do.

To be clear - I'm not against the idea of allowing perhaps a Column parameter that will make NULLS non-distinct, perhaps a collation can achieve this? I just do not think that should be an effect of STRICT mode, ever.

10:02 Reply: STRICT tables and UNIQUE indices (artifact: c1387060bc user: cuz)

I think you are mistaken - NULL values are distinct in UNIQUE INDEXes in most RDBMSes, even in PostGres. The only kind of UNIQUE INDEX that should not allow NULLS is a Primary Key (for obvious reasons) unless the field is also declared as NOT NULL, but then no nulls can exist, ditto for SQLite.

If you run into bugs because of that notion then I'm afraid to say the bugs are your own doing.

One immediate remedy for your situation I can think of is to declare another calculated column with the expressions "COALESCE(my_key,-1)" which will always have the same value as the example "my_key" field, unless that field is NULL in which case it would have the value -1. Declaring a Unique Index on the calculated field should solve your problem (I think, but I haven't yet tried it).

2021-08-28
13:02 Reply: Best tool to handle SQLite code? (artifact: b3869d4aae user: cuz)

That's essentially software-versioning, since you seem to use "SQL" as software code, SV fits exactly with that need.

There's of course the GIT world - best for large numbers of contributors, or my personal choice SVN - best for speed and ease of use for smaller groups (using SQLite as the data store btw), or if you like compactness and great for large projects with fewer contributors, then Fossil (which hosts the SQLite project itself).

These are all pretty simple both to set up and use with insanely large amounts of community users out there, so help is never far away. Probably some of the best maintained pieces of software on the planet by nature of having programmers as their main clientèle. You can setup any of these on a local server, or somewhere on a cloud machine etc.

Quite the no-brainer. Or was there a reason I'm missing why SV won't satisfy your needs?

2021-08-24
12:46 Reply: When is "IS" used in SQLite? (artifact: fc9e27adbf user: cuz)

Nice writeup and nitpicking is very much welcomed - it serves only to improve everyone's understanding, but you make false claims right off the bat during your nitpick, which achieves the opposite.

To expand on the above: is differs from == only when the considered expression contains a null.

Not True which is clear from my examples, but to reiterate:

SELECT (5 = TRUE) AS ' == ',  (5 IS TRUE) AS ' IS '

  --    ==  |     IS     
  -- -------|------------
  --     0  |      1     
    

Nowhere in that statement did NULL appear and yet "IS" produced different results than "==" did.

Not disagreeing with the theory btw - big fan of Codd myself, but reality rarely conforms to ideals.

2021-08-23
13:08 Edit reply: When is "IS" used in SQLite? (artifact: 072af59c63 user: cuz)

When you need to test semantic equality between things that do not necessarily have mathematical equality.

Examples 
5 IS 5       --> TRUE
5 IS 6       --> FALSE
5 IS NULL    --> FALSE
NULL == NULL --> NULL  (not mathematically equal, but )
NULL IS NULL --> TRUE  (they are semantically equivalent)
1 == TRUE    --> TRUE  (mathematically equal)
5 == TRUE    --> FALSE (not mathematically equal)
5 IS TRUE    --> TRUE  (semantically equal)

EDIT:
There is also testing semantic unequivalence (if that is a real word) using IS NOT:
5 IS NOT NULL    --> TRUE
NULL IS NOT NULL --> FALSE
NULL <> NULL     --> NULL

There is also "ISNULL" which is equivalent to "IS NULL" and sometimes a more easy statement to use, so that:
... WHERE a IS NULL;
is exactly the same as saying:
... WHERE a ISNULL;

13:06 Edit reply: When is "IS" used in SQLite? (artifact: b256c9cebf user: cuz)

When you need to test semantic equality between things that do not necessarily have mathematical equality.

Examples 
5 IS 5       --> TRUE
5 IS 6       --> FALSE
5 IS NULL    --> FALSE
NULL == NULL --> NULL  (not mathematically equal, but )
NULL IS NULL --> TRUE  (they are semantically equivalent)
1 -- TRUE    --> TRUE  (mathematically equal)
5 == TRUE    --> FALSE (not mathematically equal)
5 IS TRUE    --> TRUE  (semantically equal)

EDIT:
There is also testing semantic unequivalence (if that is a real word) using IS NOT:
5 IS NOT NULL    --> TRUE
NULL IS NOT NULL --> FALSE
NULL <> NULL     --> NULL

There is also "ISNULL" which is equivalent to "IS NULL" and sometimes a more easy statement to use, so that:
... WHERE a IS NULL;
is exactly the same as saying:
... WHERE a ISNULL;

12:54 Reply: When is "IS" used in SQLite? (artifact: 4c63575b85 user: cuz)

When you need to test semantic equality between things that do not necessarily have mathematical equality.

Examples 
5 IS 5       --> TRUE
5 IS 6       --> FALSE
5 IS NULL    --> FALSE
NULL == NULL --> FALSE (not mathematically equal, but )
NULL IS NULL --> TRUE  (they are semantically equivalent)
5 == TRUE    --> FALSE
5 IS TRUE    --> TRUE

12:48 Reply: About STRICT tables (artifact: 16545b5444 user: cuz)

To add to Larry's comment and in case it wasn't clear to you before: Length decorations were never used or useful[1] in SQLite although they were allowed for compatibility sake (much like fake TYPE names), hence they won't be allowed in STRICT tables.

[1] Length indicators after Text-affinity type-names (Varchar etc.) were "consulted" for some query optimization in the Query Engine, when given. But they never had any data impact or restrictive qualities.

2021-08-21
21:30 Reply: about "strict" mode (artifact: 11d1ecccbc user: cuz)

My further 2c here:

  1. Consider that strict mode won't ever be backward compatible, in the sense that the entire domain of existing SQLite databases were not subjected to strict mode upon creation, so opening an old DB in a strict=ON connection will very likely fail (unless the previous schema makers were very strict themselves).

  2. The moment a programmer chooses to invoke "strict mode", I cannot see why they would ever want to turn back to the old ways, so being able to turn strict mode on and off at a whim seems completely unneeded for future use.

  3. Any table/schema made with "strict = ON" would be perfectly able to open and be used happily by any previous version of SQLite, so there really is not ever any reason to embed strict-ness in the DB file itself, other than perhaps a flag that indicates the last schema write was done with a strict-mode connection (or not).

  4. One might still have old DBs and have to maintain them, while using the newest build of SQLite, so that provides a clear case where being able to turn strict-mode OFF for a connection prior to opening the DB could be useful, but it must be PRIOR to opening the DB file, and if it is a pragma, then that switch-over must become a no-op if done after a DB is already opened, or like some other pragmas, perhaps require a rebuild (vacuum) to get enabled on the DB and then heartily FAIL hard if the schema fails the strictness check.

Disabling/Enabling strict-mode at build-level though might have a lot of advantages as I imagine one could get rid of a whole lot of nonsense-checking code that won't be needed in strict mode anymore.

We've been lobbying hard here for strict-mode and love that this is becoming a reality, but we should understand that it is a hard paradigm shift and should not be compromised. I would probably start by using two builds myself, perhaps using the old in my older projects (until I can adapt them) and the new strict build for anything recent and going forward.

PS: Nice to have tools would be: Some simple check to see if a DB already complies to "strict" or not, and perhaps something to tell you what things in the current DB file needs to change for it to comply to "strict" mode. Probably nice additions to add to the CLI (else we might put out such a tool, since we need to make one for our purposes anyway.)

Very excited to start using strict-mode! :)

21:24 Reply: about "strict" mode (artifact: a11c4cbacd user: cuz)

Perhaps the description of what strict-mode does above isn't complete, but I am missing these checks:

  • forcing/requiring the use of correct quoting: strings can only be in single-quotes,
  • identifiers only bare or in double-quotes)
  • column default values having to be of the specific type of the column (checked at schema creation time)

Any chance those would be included?

2021-08-20
19:39 Reply: Updated Website Idea (artifact: e5c1ebdb3d user: cuz)

Appreciate the effort, but straight-up hate that design intensely.

I felt subtle angst, as though a Skynet attack was imminent...

That's not to say a different one won't be great. Keep in mind though that the SQLite site (to my best knowledge) is largely programmatically produced and so some of the fanciness might not be as suitable.

01:45 Reply: How do I sort my Safari History.db History by DATE? (artifact: d3947744b6 user: cuz)

I feel like this thread already has too many "lay-mans" terms in it, so I'm going to step it up a few notches and go straight for clever-people terms and say:

YES, there is a way to sort or search by date.

And... for bonus points, If you tell us what those little field names are in the table you are trying to sort and/or search, we might even be nice enough to offer a "how-to" for doing it.

2021-08-17
17:04 Reply: sqlite DB + data file - write out all entries (artifact: 02084d8119 user: cuz)

Yeah, I'm with Random on that matter.

What I've figured out is that all you need should be in the DB file (Engelsk.gdb). The .dat file contains what seems like byte streams all put together with the DB having entries that refer to the byte offset and length/size of the streams. The first streams may be pictures or so, but I'm not sure. Later there are streams that are the sound data.

Either way, the tables seem to link English and Dansk together in a way that is not immediately obvious to me. One way I've been able to get some stuff out that seems to make some sense is with this query:

-- Dansk --> English
SELECT COALESCE(DN0.word_,DN1.word_) AS Dansk_Word, group_concat(ENG.word_,', ') AS English
  FROM      entries1            AS D2E
  LEFT JOIN lookup1             AS DN0 ON DN0.entry_id_ = D2E.id_
  LEFT JOIN collocation_lookup1 AS DN1 ON DN1.entry_id_ = D2E.id_
  LEFT JOIN reverse1            AS ENG ON ENG.entry_id_ = D2E.id_
 WHERE 1
 GROUP BY D2E.id_
 ORDER BY COALESCE(DN0.word_,DN1.word_)
 LIMIT 20;

  -- Exerpt from Results:
  -- Dansk_Word         |English                                                                                                                         
  -- -------------------|-----------------------------------------------------
  -- accept             |acceptance, confirmation                                                                                                        
  -- accept             |accept, accept, accept, accept, accept, accept, accept, accept                                                                  
  -- accept             |non-acceptance, non-acceptance, non-acceptance, non-acceptance, non-acceptance, non-acceptance, non-acceptance, non-acceptance  
  -- acceptabel         |acceptable                                                                                                                      
  -- acceptabel         |acceptable to                                                                                                                   
  -- acceptabilitet     |acceptability                                                                                                                   
  -- acceptabiliteten   |NULL                                                                                                                            
  -- acceptabilitetens  |NULL                                                                                                                            
  -- acceptabilitets    |NULL                                                                                                                            
  -- acceptant          |acceptor                                                                                                                        
  -- acceptanten        |NULL                                                                                                                            
  -- acceptantens       |NULL                                                                                                                            
  -- acceptanter        |NULL                                                                                                                            


and this one for the opposite:

-- English --> Dansk
SELECT COALESCE(EN0.word_,EN1.word_) AS English_Word, group_concat(DAN.word_,', ') AS Dansk
  FROM      entries2            AS E2D
  LEFT JOIN lookup2             AS EN0 ON EN0.entry_id_ = E2D.id_
  LEFT JOIN collocation_lookup2 AS EN1 ON EN1.entry_id_ = E2D.id_
  LEFT JOIN reverse2            AS DAN ON DAN.entry_id_ = E2D.id_
 WHERE 1
 GROUP BY E2D.id_
 ORDER BY COALESCE(EN0.word_,EN1.word_)
 LIMIT 20;


  -- Exerpt from Results:
  -- English_Wo-|                                                                                              
  -- rd         |Dansk                                                                                         
  -- -----------|--------------------------------------------------------------
  -- ad-lib     |improvisation                                                                                 
  -- ad-lib     |improviseret                                                                                  
  -- ad-lib     |improvisere                                                                                   
  -- adage      |mundheld, ordsprog, talemåde                                                                  
  -- adam       |adam                                                                                          
  -- adam       |jeg aner ikke hvem han er, jeg kender ham slet ikke                                           
  -- adam       |adamsæble                                                                                     
  -- adamant    |ikke lade sig rokke, være ubøjelig                                                            
  -- adamant    |ikke lade sig rokke hvad angår, ikke lade sig rokke med hensyn til                            
  -- adamant    |ikke lade sig rokke hvad angår, ikke lade sig rokke med hensyn til                            
  -- adamant    |holde stejlt på at, være fast besluttet på at                                                 
  -- adamantly  |være absolut imod, være en absolut urokkelig modstander af                                    
  -- adams      |nul og nix, slet ingenting                                                                    
  -- adapt      |afpasse, bearbejde, indrette, indrette sig, omarbejde, tilpasse, tilpasse sig, tilrettelægge  
  -- adapt      |tilpasse til                                                                                  
  -- adapt      |indrette efter                                                                                
  -- adapt      |indrette til                                                                                  

etc.

This may be a sqlite DB they use, but that format is not a well-known or open format (at least not one I am aware of)

Good luck on that.

14:35 Reply: sqlite DB + data file - write out all entries (artifact: f403dfd4db user: cuz)

Probably - Depending on what "accompanying binary datafile" actually means.

Could you be more specific? Perhaps upload the DB file somewhere so we can see the format tell you how to write it out?

If you do not wish to upload it - What OS do you use? If Linux then "DB Browser for SQLite" would probably make it easy, if Windows you can try SQLiteSpeed.com or just google "sqlite db admin" or such. Another option is dumping from the CLI and sending us the schema.

Whichever you choose, we need a lot more knowledge about the DB schema and how you want to see it to form useful queries to "write it out".

2021-08-12
13:33 Reply: SQLite irrespective query (artifact: 21660da4e5 user: cuz)
SELECT shopper_first_name, shopper_surname, shopper_email_address,
       IFNULL(gender, 'Not known' ) AS 'Gender',
       STRFTIME('%d-%m-%Y', date_joined) AS 'date_joined',
       STRFTIME('%Y',date('now'))- STRFTIME('%Y',date(date_of_birth)) AS 'Age'
  FROM shoppers
 WHERE (gender = 'F') OR (gender = 'M' AND date_joined >= '2020-01-01')
ORDER BY gender,Age DESC
; 

Should do it.

2021-08-10
13:18 Reply: Right and full outer joins (artifact: ef85e82b09 user: cuz)

No.

2021-08-09
23:22 Reply: Getting a date range using "like" (artifact: 95fb0a1295 user: cuz)

What Keith said, plus the fact that NUMERIC is perfect for a column with date and time values and should work perfectly for your case.

Also, in SQLite there is a pragma "case_sensitive_like" if you fancy changing that behaviour.

2021-08-08
20:51 Reply: Virtual RDBMS over SQLite (artifact: a7fcb39f5e user: cuz)

I searched the net and discovered that a database with only 1 table was very popular

It's not very popular at all, and you certainly did not find that on this forum.

I mean I'm sure you can find examples of people doing it, but that's like saying the act of strapping a Deer to the hood of your car on the drive home after the hunting trip is very popular because you can find many pictures of idiots who did that on the internet. While it is true that you can find many examples/pics of said idiots, it must never be mistaken for popularity with people in general.

I can't speak for all DBs, but for the ones I do use (SQLite, MySQL/MariaDB, Postgres, MSSQL) I can promise you there is no advantage to any DB having one table per schema precisely, as opposed to two or more. In fact, I can point out many disadvantages.

I feel less strongly about the other possible meaning you could have intended, namely that using a set of single-table files is one way of solving a multi-format virtual/object model. Perhaps "also-used-sometimes" is better phrasing than "popular" - plus I still fail to see a significant advantage. [PS: If there is a tangible advantage that can be shown, I'm very willing to be corrected and very interested to hear about it.]

20:32 Reply: Best practice: Save partial dates (artifact: 0a3ed73ccd user: cuz)

While there might be some sense to this suggestion in terms of data handling-ability (and perhaps prettiness?), it is cumbersome, requires intermediate encoding/decoding and will be orders of magnitude slower. JSON (and other object orientated storage types) are typically more sensible precisely where form and format varies, and in this case the format is very much set in stone by contrast.

More ↓