August 2012 - database-devel

Re: [database-devel] Arrays and postgresql-simple
by Jason Dusek 19 Aug '12

19 Aug '12

I have integrated Bas's type cache modifications with a more robust parser and pretty printer, which handle quoted strings and nested arrays. https://github.com/solidsnack/postgresql-simple/tree/arrays In an earlier email, Leon P. Smith raised some concerns about the types of arrays of arrays. 2012-07-30 21:00:34 UTC, leon.p.smith(a)gmail.com: > Now, presumably, postgresql does actually support arrays of > arrays, but I'm guessing that you need to create the array of > array of string type before you can actually run this query... > > The reason I ask is that Bas van Dijk has done some work on > adding support for arrays to postgresql-simple. And he's > modified the type cache from TypeOID -> IO TypeName to become > a TypeOID -> IO TypeInfo, where the TypeInfo type is defined > as follows: > > data NamedOid = NamedOid { typoid :: !PQ.Oid > , typname :: !ByteString > } deriving Show > > data TypeInfo = TypeInfo { typ :: !NamedOid > , typelem :: !(Maybe NamedOid) > } deriving Show > > I think this is a perfectly reasonable first attempt, but I > don't think it's correct, because I'm pretty sure that > PostgreSQL does actually support arrays of arrays if you know > what you are doing. So I think that TypeInfo needs to look > something more like > > data TypeInfo = Plain { typ :: !NamedOid } > | Array { typ :: !NamedOid > , typelem :: !TypeInfo } > deriving Show > > The real issue here is one dealing with purity and effects: In > some sense, Bas's first attempt carries the same information, > but you might need to do some IO in order to retrieve it. > (Though this assumes that one can query the type cache > directly, which is something I should probably add anyway...) > But the fromField method, which is is the most likely consumer > of this information, isn't allowed to do IO. So I think we > really want to change this type. Because a Postgres array is multi-dimensional, it would stand to reason that the Haskell types [Int4], [[Int4]] and [[[Int4]]] would all map to _int4 in Postgres. The array parsing code linked to above relies on that assumption. -- Jason Dusek pgp // solidsnack // C1EBC57DC55144F35460C8DF1FD4C6C1FED18A2B

3 2

Arrays and postgresql-simple
by Leon Smith 19 Aug '12

19 Aug '12

Ok, Bas van Dijk has done some preliminary work in adding support for PostgreSQL's array types to postgresql-simple, and I have some questions about arrays and types that I'm hoping Jeff Davis can answer. Here's a link, and a simple experiment in psql: https://github.com/basvandijk/postgresql-simple/commit/eb04ca39c5c22e3f4d08… => create table strings (x text); => insert into strings values ('foo','bar','baz','hello world','goodbye cruel world'); => select array_agg(x) from strings group by x like ('% %'); array_agg --------------------------------------- {foo,bar,baz} {"hello world","goodbye cruel world"} (2 rows) So far so good, but what if we try creating an array of arrays? => select array_agg(*) from (select array_agg(x) from strings group by x like ('% %')) q; ERROR: function array_agg() does not exist LINE 1: select array_agg(*) from (select array_agg(x) from strings g... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. Now, presumably, postgresql does actually support arrays of arrays, but I'm guessing that you need to create the array of array of string type before you can actually run this query... The reason I ask is that Bas van Dijk has done some work on adding support for arrays to postgresql-simple. And he's modified the type cache from TypeOID -> IO TypeName to become a TypeOID -> IO TypeInfo, where the TypeInfo type is defined as follows: data NamedOid = NamedOid { typoid :: !PQ.Oid , typname :: !ByteString } deriving Show data TypeInfo = TypeInfo { typ :: !NamedOid , typelem :: !(Maybe NamedOid) } deriving Show I think this is a perfectly reasonable first attempt, but I don't think it's correct, because I'm pretty sure that PostgreSQL does actually support arrays of arrays if you know what you are doing. So I think that TypeInfo needs to look something more like data TypeInfo = Plain { typ :: !NamedOid } | Array { typ :: !NamedOid , typelem :: !TypeInfo } deriving Show The real issue here is one dealing with purity and effects: In some sense, Bas's first attempt carries the same information, but you might need to do some IO in order to retrieve it. (Though this assumes that one can query the type cache directly, which is something I should probably add anyway...) But the fromField method, which is is the most likely consumer of this information, isn't allowed to do IO. So I think we really want to change this type. Also, this type makes it more natural to add support for the new range types available in PostgreSQL 9.2. (Which Jeff is largely responsible for, by the way...) The extended TypeInfo might look like this: data TypeInfo = Plain { typ :: !NamedOid } | Array { typ :: !NamedOid , typelem :: !TypeInfo } | Range { typ :: !NamedOid , typelem :: !TypeInfo } deriving Show So, I think that's enough issues to talk about for the time being. Best, Leon

3 5

ANNOUNCE: sqlite-simple 0.1.0.0: mid-level API to the sqlite database
by Janne Hellsten 15 Aug '12

15 Aug '12

sqlite-simple provides a convenient API to sqlite that performs automatic data conversion between the database and Haskell types. The API has been modeled directly after postgresql-simple which in turn borrows from mysql-simple. The library is available on hackage at http://hackage.haskell.org/package/sqlite-simple * * Example usage First create a test database.. --------------- sqlite3 test.db "CREATE TABLE test (id INTEGER PRIMARY KEY, str text);\ INSERT INTO test (str) VALUES ('test string');" --------------- ..and access it in Haskell: --------------- import Control.Applicative import Database.SQLite.Simple import Database.SQLite.Simple.FromRow data TestField = TestField Int String deriving (Show) instance FromRow TestField where fromRow = TestField <$> field <*> field main :: IO () main = do conn <- open "test.db" execute conn "INSERT INTO test (str) VALUES (?)" (Only ("test string 2" :: String)) r <- query_ conn "SELECT * from test" :: IO [TestField] mapM_ print r close conn --------------- * * A lot of the code is directly borrowed from mysql-simple by Bryan O'Sullivan and from postgresql-simple by Leon P. Smith. Like Leon in postgresql-simple, I've borrowed code and documentation directly from both of these ancestor libraries. This package builds on top of the direct-sqlite package by Irene Knapp. Thanks to all of the above for helpful comments, features and code review. * * Bugs, feature requests? I'm happy to receive bug reports via github on: https://github.com/nurpax/sqlite-simple/issues For discussion, I recommend the database-devel mailing list: http://www.haskell.org/mailman/listinfo/database-devel Enjoy! Janne

1 0

ByteString and Text - any conventions?
by Janne Hellsten 14 Aug '12

14 Aug '12

While working on https://github.com/nurpax/sqlite-simple there have been occasions when I've tried to decide whether to use ByteStrings or Text strings. I note that postgresql-simple and mysql-simple use ByteStrings exclusively in the API. Has a convention formed in the Haskell community on which strings should be used in APIs that pass strings around? In my case, blobs will anyway be passed around as ByteStrings. But SQLite's C strings are UTF8 and some might prefer to Text over ByteStrings. Or support both in the API? This matters in the low-level bindings too, where result accessors need to return either ByteStrings or Text objects for TEXT fields. Currently direct-sqlite uses the String type but Irene is thinking of changing the type to a more efficient representation (https://github.com/IreneKnapp/direct-sqlite/issues/3) Sqlite-simple links against both bytestring and text already, so from a purely package dependency point of view the choice doesn't really matter. But sqlite-direct will need to choose one or the other for its string type in the SQLText s constructor. Any thoughts? Cheers, Janne

4 4

New mid-level API for sqlite: sqlite-simple
by Janne Hellsten 12 Aug '12

12 Aug '12

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(a)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

2 2

Re: [database-devel] postgresql-simple - how hard would it be to do the same for sqlite?
by Leon Smith 02 Aug '12

02 Aug '12

To clarify what I meant by a low-level binding: On Tue, Jul 31, 2012 at 5:25 PM, Janne Hellsten <jjhellst(a)gmail.com> wrote: > Put into SQLite API context, here's how a typical SELECT might work, > using some sort of pseudo-C to make it easier to read > > conn = sqlite3_open("..."); > // create a prepared statement: > stmt = sqlite3_prepare(conn, "SELECT * from test_table"); > > // process all rows in the result > while (sqlite3_step(stmt) != SQLITE_DONE) > { > // access the current row > char* colValue = sqlite3_column_text(stmt, columnNdx); > // there are separate column accessors for different types, like > sqlite_column_blob, text, etc. > } > sqlite3_finalize(stmt); > sqlite3_close(conn); > Basically, the aim of the low level binding is to create this sort of pseudo-C in Haskell, though it would be preferable to not have to explicitly finalize the statement. (Though I did end up adding unsafeFreeResult to postgresql-libpq to explicitly trigger the finalizer that calls PQclear, for better or worse. I did so at the request of Felipe Lessa for use in persistent-postgresql.) Best, Leon

1 0

sqlite3-simple?
by Janne Hellsten 02 Aug '12

02 Aug '12

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

2 1