On Thu, Jun 04, 2015 at 10:54:16AM +0200, Dominique Devienne wrote: > On Thu, Jun 4, 2015 at 10:20 AM, Christopher Vance <cjsvance at gmail.com> > wrote: > > If you really want your own types, you could always bundle with ASN.1 and > > store the result as a blob.
FYI, Heimdal has a very nice, small, simple, featureful, and easily separable ASN.1 compiler with a BSD-type license. https://github.com/heimdal/heimdal/tree/master/lib/asn1 > Or Protobuf, or ... But you're back to option 1, you must store somewhere > that knowledge, and it's an app-convention, SQL and SQLite don't cooperate. I've never understood why Protocol Buffers. Its authors made all the same sorts of design choices that make DER a lousy encoding for ASN.1... Anyways, the biggest problem with any kind of encoding (ASN.1, JSON, whatever), is that if you want to be able to use decoded values in SQL statements, you end up having to decode and re-encode in every UDF. This could be avoided if SQLite3 had a sort of UDT that's just an opaque handle for a value and which includes an interface for coercing it to a native SQLite3 type (e.g., blobs): this way re-encoding can be avoided as much as possible. > You can also use a self-describing format like AVRO (many others I'm sure), > but you still need to know "a-priori" which blobs are avro encoded, and > even assuming that, that doesn't enforce a "schema" (structure) on the > column (i.e. "static typing"), which I think is necessary. Actually, maybe > a CHECK constraint works here. I've never tried to use a > custom-SQL-function-based check constraint in SQLite. Does that work? --DD Yes, it does. Nico --
