I've been reading this thread with great interest. It parallels the project I've been working on: Andl.
Andl is A New Database Language. Andl does what SQL does, but it is not SQL. Andl has been developed as a fully featured database programming language following the principles set out by Date and Darwen in The Third Manifesto. It includes a full implementation of the Relational Model published by E.F. Codd in 1970, an advanced extensible type system, database updates and other SQL-like capabilities in a novel and highly expressive syntax. The intended role of Andl is to be the implementation language for the data model of an application. It is already possible to code the business model of an application in an SQL dialect, but few people do this because of limitations in SQL. Andl aims to provide a language free of these problems that works on all these platforms. The current implementation on SQLite uses a mixture of generated SQL and a runtime VM. User-defined types are blobs, which the VM understands. A future implementation could generate SQLite VM code directly instead of SQL, which would save some overhead. The website is andl.org. The GitHub project is https://github.com/davidandl/Andl. It's a work in progress. Any feedback welcomed. Regards David M Bennett FACS Andl - A New Database Language - andl.org -----Original Message----- From: sqlite-users-boun...@mailinglists.sqlite.org [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Darko Volaric Sent: Thursday, 4 June 2015 8:55 AM To: General Discussion of SQLite Database; ott at mirix.org Subject: Re: [sqlite] User-defined types 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 > _______________________________________________ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
