Subject: [wasm] sqlite as rfc-spec'd web-interchange-format?

From: kai zhu <[email-masked]>
Date: Thu, 26 Nov 2020 18:08:57 -0600

i'm noob to mailing-list, and if you think there's more appropriate place
for subject, please let me know.

motivation:
as product-developer, i waste huge amount of time reinventing error-prone
serializers/revivers to exchange complex-datasets across web.
standardizing sqlite's file-format would reduce integration-headaches
between me / teammates / 3rd-parties, since we could more easily agree to
use wasm-sqlite's standard serializers/revivers (instead of everyone
reinventing their own for the web).

why sqlite as web-interchange-format?
1.
wasm-sqlite's serializers/revivers are opensource and accessible to
web-clients
2.
The [U.S.] Library of Congress Recommended Formats Statement (RFS) includes
SQLite as a preferred format for datasets
<https://www.loc.gov/preservation/resources/rfs/data.html>. [1]
3.
sqlite's last file-format (v4) remains stable and unchanged since 2006
4.
all sqlite file-formats since 2004 (v1, v2, v3, v4) are intended to be
forward-compatible with future sqlite releases
5.
sqlite is the "Most Widely Deployed and Used Database Engine" (and
perhaps second most widely deployed software library, after libz). [2]

the spec for sqlite's file-format is here [3].  i'm no expert at reading
it, but am wondering how feasible to translate it to an rfc-spec (minus the
journaling part)?

-kai

[1] Sustainability of Digital Formats: Planning for Library of Congress
Collections
https://www.loc.gov/preservation/digital/formats/fdd/fdd000461.shtml

[2] Most Widely Deployed and Used Database Engine
https://sqlite.org/mostdeployed.html

[3] Database File Format
https://sqlite.org/fileformat2.html

---

From: Keith Moore <[email-masked]>
Date: Thu, 26 Nov 2020 20:57:56 -0500

On 11/26/20 7:08 PM, kai zhu wrote:
> i'm noob to mailing-list, and if you think there's more appropriate 
> place for subject, please let me know.
>
> motivation:
> as product-developer, i waste huge amount of time reinventing 
> error-prone serializers/revivers to exchange complex-datasets across 
> web.  standardizing sqlite's file-format would reduce 
> integration-headaches between me / teammates / 3rd-parties, since we 
> could more easily agree to use wasm-sqlite's standard 
> serializers/revivers (instead of everyone reinventing their own for 
> the web).
>
> The [U.S.] Library of Congress Recommended Formats Statement (RFS) 
> includes SQLite as a preferred format for datasets 
> the spec for sqlite's file-format is here [3].  i'm no expert at 
> reading it, but am wondering how feasible to translate it to an 
> rfc-spec (minus the journaling part)?
>
> -kai

I've been badly burned by the sqlite library more times than I can 
count.  Unless it's been fixed fairly recently, it is lousy at handling 
concurrent queries, so for that reason alone promoting the file format 
might be a disservice.

But maybe it makes some sense as a database interchange format. Maybe.   
In my experience a lot gets lost in translation between sqlite tables 
and other databases, but maybe not so much that it's useless as a way to 
convey datasets. It's probably a useful format for use in information 
transfer and maybe even simple single-user queries, but beware of using 
the sqlite library to make any kind of services providing concurrent 
access to more than one process.

I'm also not sure whether IETF can attract enough of the right kind of 
expertise to do a good job at this.

But I do suspect there's a need for something like this to be 
standardized, by someone.

Keith

---

From: kai zhu <[email-masked]>
Date: Thu, 26 Nov 2020 23:09:18 -0600

> it is lousy at handling concurrent queries
lousy compared to what? localstorage / indexeddb / redux ?
as frontend-developer, our data-store options all suck at concurrency and
are either 1) too toy-case (localstorage) or 2) too difficult to use vs.
wasm-sqlite queries.

> In my experience a lot gets lost in translation between sqlite tables and
other databases
its not perfect for sure, but say i want to serialize/revive following
datasets in browser:
    - catalog of shop-items with fulltextsearch
    - customer's online shopping-cart
    - [subset of] enron-email with fulltextsearch
    - queryable 10-year historical-data of all s&p500 stocks

if not as [wasm] sqlite-database, what better serialization-options
are there for interchanging above datasets over the web?  also if ietf is
not a suitable forum, let me know if there are better places to raise this
issue.

On Thu, Nov 26, 2020 at 7:58 PM Keith Moore <[email-masked]>
wrote:

> I've been badly burned by the sqlite library more times than I can count.
> Unless it's been fixed fairly recently, it is lousy at handling concurrent
> queries, so for that reason alone promoting the file format might be a
> disservice.
>
> In my experience a lot gets lost in translation between sqlite tables and
> other databases, but maybe not so much that it's useless as a way to convey
> datasets. It's probably a useful format for use in information transfer and
> maybe even simple single-user queries, but beware of using the sqlite
> library to make any kind of services providing concurrent access to more
> than one process.
>

---

From: Keith Moore <[email-masked]>
Date: Fri, 27 Nov 2020 00:29:27 -0500

> lousy compared to what? localstorage / indexeddb / redux ?

Too lousy to use.  I haven't tried everything that exists as a basis for 
comparison, but sqlite should probably not be used by anything that 
requires concurrent access.

Of course that's a criticism of the library and not about the file format.

> and other databases
> are there for interchanging above datasets over the web?

It's possible that sqlite file format is the best choice that currently 
exists.   In its favor, there are sqlite libraries for several 
programming languages that can read and update these files, and also 
front-end clients that are free of charge and open source.   The wide 
availability of libraries make it relatively easy to write translators 
to transfer the sqlite data to or from other databases.   I understand 
that the format is machine-independent and with some care in writing 
it's likely to be far more efficient a representation than any 
text-based format.

> also if ietf is not a suitable forum, let me know if there are better 
> places to raise this issue.

Perhaps others can make a suggestion for a standards organization with 
more expertise in this subject area.

Keith

---

From: Michael Richardson <[email-masked]>
Date: Sat, 28 Nov 2020 01:14:17 -0500

It's an interesting idea.

There are quite a number of other serialization formats out there including:
bincode, msgpack, protobuf, and of course, IETF's CBOR RFC7049.  Also JSON.
For "opendata" we've would up with CSV, which I really dislike.
{So funny that the UK's data problem with COVID rates was due to some
data flow that went CSV->XLS->database, fixed when they went CSV->XLSX->database,
when there was never a reason to use XLS* at all. So perhaps this argues your
case}

The question you likely need to answer are:

1) do the sqlite people wish to turn change control over to the IETF?

2) Given the Library of Congress statement, is there actually a net benefit
   to the world?  Maybe it's already moving to defacto standard anyway.
   Would the IETF process help?  Given the stability, would there be
   any benefit for IANA?

2B) There may be NAFTA Article 10 ("Performance Specification") reasons
    for having an RFP'able specification.

3) Are there enough implementers who are not sqlite.org who would benefit?
   Is the wasm-sqlite from sqlite.org (I don't know).

I looked through the specification, and turning it into an RFC wouldn't be
hard.  You may wish to look at the work in the CELLAR WG, which is doing
something similar for archival formats for AV.

--
Michael Richardson <[email-masked]>   . o O ( IPv6 IøT consulting )
           Sandelman Software Works Inc, Ottawa and Worldwide

---

From: John C Klensin <[email-masked]>
Date: Sat, 28 Nov 2020 05:34:33 -0500

--On Saturday, November 28, 2020 01:14 -0500 Michael Richardson
<[email-masked]> wrote:

> There are quite a number of other serialization formats out
> there including: bincode, msgpack, protobuf, and of course,
> IETF's CBOR RFC7049.  Also JSON. For "opendata" we've would up
> with CSV, which I really dislike. {So funny that the UK's data
> problem with COVID rates was due to some data flow that went
> CSV->XLS->database, fixed when they went CSV->XLSX->database,
> when there was never a reason to use XLS* at all. So perhaps
> this argues your case}
> 
> 2) Given the Library of Congress statement, is there actually
> a net benefit    to the world?  Maybe it's already moving to
> defacto standard anyway.    Would the IETF process help?
> Given the stability, would there be    any benefit for IANA?
> 
> 2B) There may be NAFTA Article 10 ("Performance
> Specification") reasons     for having an RFP'able
> specification.
> 
> 3) Are there enough implementers who are not sqlite.org who
> would benefit?    Is the wasm-sqlite from sqlite.org (I don't
> know).
> 
> I looked through the specification, and turning it into an RFC
> wouldn't be hard.  You may wish to look at the work in the
> CELLAR WG, which is doing something similar for archival
> formats for AV.

I have not studied or used sqlite, but, stepping back from the
details...

Let me add something from a very different perspective -- maybe
useful, maybe not.  If you are looking for a data serialization
and interchange standpoint _and_ expect it to be able to
accommodate the full range of statistical and scientific
databases rather than only nicely rectangular, row-oriented,
commercial databases and things that behave like them, nothing
SQL-compatible is going to do the job.

To save typing in a longer list, several of the papers in
Rafanelli, M., Klensin, J.C., and Svensson, P., eds.,
_Statistical and Scientific Database Management: Fourth
International Working Conference on Statistical and Scientific
Database Management, Rome, Italy, June 1988,  Proceedings_,
Springer-Verlag Lecture Notes in Computer Science #339, 1989.
(And, yes, for those who don't know, I had a life or three
before getting sucked back, almost fully, into Internet work.)

    best,
      john

---

From: kai zhu <[email-masked]>
Date: Sat, 28 Nov 2020 16:01:32 -0600

> nothing SQL-compatible is going to do the job.

we all acknowledge sqlite is not perfect, but do you have something that
could do the job better?  like keith said, its possible sqlite file-format
is the best choice that currently exists (for exchanging
relational-datasets over web).

> bincode, msgpack, protobuf, and of course, IETF's CBOR RFC7049.  Also
JSON.

i've had experience with protobuf, json, (plus json-schema and graphql).
as far as serializer/reviver ergonomics:
1.
json is idiot-proof for non-relational data.  json-schema is soso for
relational-data.
2.
protobuf and graphql are likely idiot-proof *if* you work at
google/facebook (and access to internal-tooling).  but my [frontend]
experience with their opensource serializers/revivers are disappointing and
error-prone.
3.
wasm-sqlite's serializers/revivers are idiot-proof for relational-data:
```javascript
// webassembly serializer:
FS.readFile("/sqlite-database.db") // return <serialized Uint8Array>

// webassembly reviver:
var dbRevived;
var ptr = stackAlloc(4);
FS.createDataFile("/", "sqlite-database.db", <serialized Uint8Array>, true,
true);
sqlite3_open("sqlite-database.db", ptr);
dbRevived = getValue(ptr, "i32"); // revived sqlite-database (in virtual fs)
...
sqlite3_exec(dbRevived, "SELECT * FROM my_table;", <callback>, 0, ptr);
...
```

---

From: "John Levine" <[email-masked]>
Date: 28 Nov 2020 17:02:53 -0500

In article <4866.1606544057@localhost> you write:
-=-=-=-=-=-
>when there was never a reason to use XLS* at all. So perhaps this argues your

The sqlite format is extremely complex. It bundles together a schema,
modified B-trees with overflow and free list pages to store keyed
data, and some multiple access locking flags that the description said
aren't used any more. (Sqlite originally didn't support multiple
access but that was a long time ago.)

It looks to me like the vast majority of sqlite applications use the C
library from sqlite.org and it's not clear that there's another full
implementation. This raises the question of whether the real
definition is the spec or the code.

I think sqlite is fine for what it does but it doesn't make sense as a
serialization format for the Internet. It is simultaneously too
complicated with all of the SQL schema features that support for
database update in place, and too semantically limited as jck pointed out.

There are a lot of widely used data format that the IETF and other
SDOs haven't blessed, like FITS which is a widely used format for
multidimensional numeric data, but whose definition is maintained by
the astronomers who originally designed it. I think sqlite is like
that, fine for what it does, perfectly reasonable to transmit as files
over the Internet, but not appropriate for IETF standardization.

R's,
John

---

From: Carsten Bormann <[email-masked]>
Date: Sat, 28 Nov 2020 23:43:13 +0100

> definition is the spec or the code.

Indeed, it is hard to keep a spec document alive if there is only one implementation and that is moving fast.
Compare the recent discussion of rsync, which (apart from a heroic reimplementation effort, which became necessary because of licensing issues, but seems to be stuck at protocol version 27, ca. 2004) is another one of these single-implementation standards.

> the astronomers who originally designed it.

The 800-pound gorilla in this space is, of course, HDF5.
I don’t think they need the IETF (except that maybe somebody should tell them that text really is always UTF-8 these days).

I think it would be an interesting research project to do a survey of these table-/array-oriented data interchange formats and derive a set of best practices and useful approaches, as well as possibly unexplored avenues.  This would mesh well with various data modeling and semantic interoperability activities we have in IETF and IRTF.  Certainly not something to standardize, and most certainly not https://xkcd.com/927/.

Grüße, Carsten