[database-devel] New mid-level API for sqlite: sqlite-simple

[Janne Hellsten]

Hi,

On Sat, Aug 11, 2012 at 11:31 PM, Leon Smith <leon.p.smith at gmail.com> wrote:
> In the Readme,  you mention the type issue is less relevant to sqlite-simple
> than the others.
>
> Now,  I've not actually used SQLite myself, but I have done some reading
> about it.   I do understand that values of any type can be stored at any
> time in any column (except for the primary key,  IIRC),   but what happens
> to the declared type of a column when you create a table?   Is that
> completely ignored?   Can it be retrieved?   Is it also returned somehow
> with the results?

I'm glad this caught your attention.  This is one of the (IMO) murky
corners of the API/implementation and a bit of brainstorming will help
to get the details right.  I may not have complete understanding of
all the aspects of sqlite types as my practical experience with sqlite
hasn't been at the low-level API level but using ORM libraries like
SQLAlchemy.  Anyway..

Sqlite3 has only 5 native column types: SQLITE_INTEGER, SQLITE_FLOAT,
SQLITE_TEXT, SQLITE_BLOB and SQLITE_NULL.

As I understand it, when you create a table, the column types from
your CREATE TABLE statement are stored.  However, I think at this
point column types are also quantized to the above 5 native types.  So
when you store data, the data gets cast into one of the native types.
When you later retrieve the data, you will only know the native types
of the data in your result set.  (Although I suspect if you really
want to get the full type information, one could perhaps inspect the
db schema for more accurate type information.)

There's also some amount of implicit type casting involved -- this is
quite compactly described on
http://www.sqlite.org/c3ref/column_blob.html.

> I mean, it may still be a good idea to be able to check that somehow.
> Though this does add the complication that you still want to be able to use
> sqlite-simple with existing databases that don't necessarily follow the
> typing rules.

I agree.  I haven't completely settled on my approach yet, but I was
thinking that I would do at least a little bit of type checking in
FromField.  So for example, it sounds like a good idea to do these
types of check:

if the database type is an int, require the Haskell type to be numeric
if the database type is a float require Haskell type to be float
if the database type is a string, reject Haskell numeric types, allow
strings, dates, etc.

I need to do a bit more poking around with the low-level sqlite3 API
to figure out what'd be a more comprehensive list of typing
requirements.

Janne

> On Sat, Aug 11, 2012 at 2:39 PM, Janne Hellsten <jjhellst at gmail.com> wrote:
>>
>> Hi again!
>>
>> I finally found some time to work on the below idea of creating a
>> sqlite-simple package that's modeled after postgresql-simple &
>> mysql-simple.
>>
>> A working prototype can be found here:
>> https://github.com/nurpax/sqlite-simple
>>
>> I still need to do a bit of work on it to clean things up and add a
>> bit more functionality.  In particular, FromField instances are
>> currently limited to only Ints and Strings - this is obviously not
>> adequate for real use.
>>
>> I consider this to be ready for first release on hackage once I've
>> done some more testing and closed the issues on
>> https://github.com/nurpax/sqlite-simple/issues?milestone=1&state=open
>> - but of course I may have missed something obvious.
>>
>> Comments, concerns?  Let me know!
>>
>> Cheers,
>>
>> Janne
>>
>> On Wed, Aug 1, 2012 at 9:41 PM, Janne Hellsten <jjhellst at gmail.com> wrote:
>> > Hi list,
>> >
>> > I've been looking for better Haskell sqlite bindings for few months now.
>> >
>> > So far I've tried or investigated the following sqlite packages:
>> >
>> > - hdbc-sqlite3
>> > - sqlite
>> > - direct-sqlite
>> >
>> > At the same time I've played around a bit with postgresql-simple.
>> > I've come to quite like the postgresql-simple API and would like to
>> > have a access API for sqlite.
>> >
>> > Unfortunately, all the existing sqlite bindings seem to offer a much
>> > lower level interface.
>> >
>> > I'm thinking that if no *-simple style Haskell library exists for
>> > sqlite3, I'd like to create one.
>> >
>> > I've exchanged a few e-mails about this with Leon and with his help
>> > have a few ideas on how to go about it.
>> >
>> > Leon suggested that the design should be two libraries: one low-level
>> > library that can be unsafe to use directly and another medium-level
>> > package that's built on the low-level library.  The medium part would
>> > borrow pretty directly from existing *-simple packages.
>> >
>> > I dabbled a bit with my own low-level sqlite bindings but later came
>> > to realize that the direct-sqlite package
>> > (http://ireneknapp.com/software/) seems to be pretty close to what I'd
>> > need.  So I'm thinking that could be the low-level part of
>> > sqlite-simple.
>> >
>> > Does this seem like a useful thing to build? (I know I'd need it.)
>> >
>> > Does something like this already exist?
>> >
>> > Thanks!
>> >
>> > Janne
>>
>> _______________________________________________
>> database-devel mailing list
>> database-devel at haskell.org
>> http://www.haskell.org/mailman/listinfo/database-devel
>
>