I've tackled this problem from a couple of different angles. My goal was to allow arbitrary user defined types, based on the builtin types (essentially subtypes of the existing types), with a minimum of work and minimum disruption of the normal/existing use of the database and API.
The approaches I considered were: - encoding the user type codes for each data column in a separate column dedicated to the purpose. This is a low impact but cumbersome, for instance using a function that interprets the user type would have to have the user type passed in for each argument, along with the actual data. - modifying the data file format to carry user type information. There is space in the record/row header where you can encode this information in a backwards compatible way, but the source code for data record access is not friendly, basically a dense blob of code with a lot of integer literals which are all very important, but it's hard to be sure what they entail and that you haven't introduced a subtle bug and ultimately data corruption. Additionally the user type would have to be passed around internally - for example in the sqlite3_value object - and tracking down all of those reliably is a bit of work. - using blobs. Although using text representation is friendly when looking at the data with standard tools, it's slower and takes up more memory in various places. I found that encoding some user types as blobs with a type marker at their start (a single byte with extensions) and interpreting them was a simple and low impact approach. I also split the standard integer type four ways (negative and positive, odd and even) to get the scalar user types I needed. User defined functions and collations need to be defined for interpreting these user types of course. The first option isn't very practical. The second option is the fastest and most robust solution and my long term approach which I will be going back to after development has progressed a bit more. Currently I'm using the third approach as an interim measure. I'm supporting arbitrary prec ints and reals, arrays and tuples and other types this way. On Wed, May 27, 2015 at 3:48 AM, Matthias-Christian Ott <ott at mirix.org> wrote: > I want to define user-defined types, i.e. types not SQLite has not > built-in and make sure that I didn't overlook something. Is it correct > that values of user-defined types should be stored as text and have a > collation defined if there is an order relation for the type if the type > cannot be represented as a subset of integer or float? > > Example: > Suppose I want to store arbitrary precision integers in SQLite. I would > create a column with text affinity, (uniquely) serialize and deserialize > the integers to text (e.g. by converting them into decimal > representation) and define and declare a collation that deserializes the > texts to arbitrary integers and compares the integers. > > Is there another way to define user-defined types despite this method > and virtual tables? > > - Matthias-Christian > > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
