Hi, On Sat, Aug 11, 2012 at 11:31 PM, Leon Smith 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 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 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@haskell.org http://www.haskell.org/mailman/listinfo/database-devel