- database-devel - Haskell.org

Please test esqueleto-2.2
by Felipe Lessa 12 May '15

12 May '15

Hello! tl;dr: Please test esqueleto-2.2 by building from master [1,3], and report back if everything went smoothly [2]. [1] https://github.com/prowdsponsor/esqueleto [2] https://github.com/prowdsponsor/esqueleto/pull/97 [3] If using cabal sandbox, you could try: $ git clone https://github.com/prowdsponsor/esqueleto.git $ cabal sandbox add-source --snapshot esqueleto/ $ cabal install --constraint='esqueleto>=2.2' Philipp Balzarek recently took a stab at improving Esqueleto's handling of JOINs [2]. Thanks again, Philipp! Summary of the changes: (a) JOIN constructors (e.g. LeftOuterJoin) now associate to the left. Previously they associated to the right. (b) Esqueleto's JOIN renderer has been changed to include parenthesis at the right places, corresponding to the parenthesization used on Haskell code. Previously it incorrectly ignored explicit parenthesis on the Haskell code. Now you can write code such as: from $ \(p `LeftOuterJoin` (q `InnerJoin` r)) and: from $ \((p `LeftOuterJoin` q) `InnerJoin` r) and both should work as expected. We think that this new version will not trigger any issues, however: (a) You may be currently relying on the old, bad behavior by accident. If you currently have queries for which the parenthesization is relevant, you may want to have unit tests checking for corner cases. Please double-check any queries with explicit parenthesization. (b) You may be currently explicitly specifying the type of your JOIN, which may break due to the new left-associativity. As this isn't a recommended practice, I don't think anyone is doing it. I'll wait a couple of weeks before releasing esqueleto-2.2 to Hackage. Please test you code base against it [1], bugs are cheaper if they're caught sooner. Thanks! :) -- Felipe.

2 2

automatic connection retry
by Greg Weber 04 Sep '13

04 Sep '13

I would like for database drivers to survive connection drops (these can be created by re-starting the database). Does it make sense to add a withResourceRetry function to the resource-pool library?

1 1

Esquleto : 'ORDER BY random()'?
by Erik de Castro Lopo 12 Jul '13

12 Jul '13

Hi all (and specifically Felipe), I'm wonder who to implement the following SQL in Esqueleto: SELECT * FROM table WHERE field = X ORDER BY RANDOM() LIMIT N I noticed that there is a random_ function and I figured out how to use it by reading the tests, but it doesn't seem to be useful in the above context. Clues? Maybe Esqueleto needs an 'orderByRandom'. Cheers, Erik -- ---------------------------------------------------------------------- Erik de Castro Lopo http://www.mega-nerd.com/

3 4

Request for Feedback on COPY IN/OUT support for postgresql-simple
by Leon Smith 12 Jul '13

12 Jul '13

Although the postgresql-libpq binding has supported both COPY IN and COPY OUT for some time now, this functionality wasn't available to postgresql-simple without mucking around in the Internal module to call libpq functions on your own. I just now fixed that. The interface I went with ultimately mimics the libpq interface rather closely, albeit with many simplifications. I did consider offering fold-like combinators, and while they would certainly be much more convenient for some use cases, they also introduce a number of complications, such as dealing with exceptions. If anybody's interested in reviewing these latest patches, I'd appreciate it. I suppose the one thing I'm most unsure about is exactly which exceptions should be raised under various conditions. https://github.com/lpsmith/postgresql-simple/commit/491247f687fcbfd30c8b0f5… https://github.com/lpsmith/postgresql-simple/commit/4cfcdbee22a0cb1fb798598… Best, Leon

2 5

Announcing postgresql-libpq-0.8.2.3
by Leon Smith 09 Jul '13

09 Jul '13

I just fixed a fairly serious performance problem with postgresql-libpq's binding to PQescapeStringConn; in was exhibiting a non-linear slowdown when more strings are escaped and retained. https://github.com/lpsmith/postgresql-libpq/commit/adf32ff26cdeca0a12fa5965… If you are using postgresql-libpq's escapeStringConn, or a library that uses it (e.g. postgresql-simple, or persistent-postgresql), I do recommend upgrading. You may or may not see a performance improvement, depending on your particular use case, but if you do it can be quite substantial. It's not entirely clear to me what the root cause really is, but it certainly appears as though it's related to the (direct) use of mallocBytes, which was replaced with (indirect) calls to mallocForeignPtrBytes / mallocPlainForeignPtrBytes (through the bytestring package). In this case, it resulted in an asymptotic improvement in time complexity of some algorithms. Best, Leon

2 2

Divinol�ndia de Minas - Lista dos aprovados divulgada
by David Anderson 05 Jun '13

05 Jun '13

Divinolândia de Minas ALINE RAQUEL BEZERRA LEITE, LEANDRO DO NASCIMENTO BATISTA, FERNANDO MONTEIRO DOS SANTOS, NEYLSON CAVALCANTE DOS SANTOS, JOÃO CARLOS MOREIRA DE CARVALHO, CLEVILSON PEDRO FONSECA OLIVEIRA, MARIA DE FATIMA ALEIXO PINHEIRO, IVETE CORDEIRO DE SOUZA. SAMILLE DE OLIVEIRA CAVALCANTE, ELISÂNGELA DOS SANTOS SILVA, MAURY DE SIQUEIRA MELO PEREIRA, JOSEFA LIDIANA ALVES FERREIRA, VITOR HUGO OLIVEIRA SOUZA. Brazabrantes. Wenceslau Guimarães ANA FLÁVIA DA SILVA, LUAN MARLLUS MAIA RAULINO, FRANCISCO MARIO ROCHA DE MELO, PEDRO IGOR GARCIA COSTA, JOÃO CARLOS MOREIRA DE CARVALHO, DANIEL GUIMARAES CORDEIRO, MARIA IVONEIDE DE OLIVEIRA NASCIMENTO, JESICA FREITAS E SILVA. SILVONEIDE DE O, AYSLAN CORDEIRO DO SANTIAGO, LUCIANA SOARES F. DA SILVA, HARIANNE LEITE DE ALENCAR, REJANE SANTA CELESTINA DA SILVA. Cáceres. Alto Rio Doce ALINE RAQUEL BEZERRA LEITE, LEANDRO DO NASCIMENTO BATISTA, FERNANDO MONTEIRO DOS SANTOS, NEYLSON CAVALCANTE DOS SANTOS, JOÃO CARLOS MOREIRA DE CARVALHO, CLEVILSON PEDRO FONSECA OLIVEIRA, MARIA DE FATIMA ALEIXO PINHEIRO, IVETE CORDEIRO DE SOUZA. SAMILLE DE OLIVEIRA CAVALCANTE, ELISÂNGELA DOS SANTOS SILVA, MAURY DE SIQUEIRA MELO PEREIRA, JOSEFA LIDIANA ALVES FERREIRA, VITOR HUGO OLIVEIRA SOUZA. Cáceres.

1 0

Multi-threaded access to an SQLite database
by Janne Hellsten 04 Jan '13

04 Jan '13

Moving the discussion over to database-devel - others may find this interesting / may have run into this. FWIW - I also tried setting up a test case which would have multiple concurrent writers, but couldn't get this to fail. I suspect though that my test case wasn't good enough to hit this problem. Background: This originated from [1], also collected some notes on [2]. [1]: https://github.com/nurpax/snaplet-sqlite-simple/issues/5 [2]: here: http://nurpax.github.com/posts/2013-01-04-note-to-self.html Janne On Sat, Jan 5, 2013 at 12:38 AM, Joey Adams <joeyadams3.14159(a)gmail.com> wrote: > On Fri, Jan 4, 2013 at 4:43 PM, Janne Hellsten <jjhellst(a)gmail.com> wrote: >> >> I was just wondering if you have any past experience on multi-threaded >> use of SQLite? I hit some issues last week, started seeing >> SQLITE_BUSY on inserts and wasn't quite sure what to think about it. >> I wrote some notes about it here >> http://nurpax.github.com/posts/2013-01-04-note-to-self.html. Have you >> by any chance had to deal with such problems before? > > > According to the SQLite FAQ [1], only one writer can access the database at > a time. I haven't run into this myself, though. > > Be careful with transactions. If you allow multiple threads to access a > database handle, and to issue BEGIN/COMMIT on their own terms, then one > thread might issue BEGIN and interfere with other transactions. Thus, make > sure the MVar lock is held for the duration of the transaction. > > PS: Mind CCing this to database-devel(a)haskell.org ? > > [1]: http://www.sqlite.org/faq.html#q5

2 2

(Postgresql-simple) transparently using Transactions/Savepoints
by tsuraan 25 Oct '12

25 Oct '12

One problem that I've noticed in the postgresql-simple API (and probably the other -simple APIs) is that the programmer needs to be careful when using "withTransaction". If you have some function that uses it, then that function must not be called from within some other withTransaction context. This is sort of a SQL issue; calling BEGIN from within a BEGIN doesn't work well (postgres just gives a warning when you do this, which is downright scary). However, from within a transaction, one can use the SAVEPOINT calls; these create mini-transactions that may be rolled back or released without rolling back or committing the surrounding transaction. Moreover, if an error occurs from within a savepoint (such as attempting to violate a uniqueness constraint), one can simply undo the savepoint instead of having to roll back the entire transaction. I've written a small wrapper around postgresql-simple that transparently uses transactions and savepoints where appropriate. The code lives at https://github.com/tsuraan/postgresql-transactions . I'm not thrilled with the API; I think that having transactions be a StateT wrapping IO would be let me use Connections again instead of making my own crappy data type wrapping them, but whenever I try to do that I get confused :( This approach was easy to write, despite the unfortunate API that is exposes. If anybody could show me how to make this API nice, I would be very appreciative. The github README has an example of why I think this transaction/savepoint abstraction is worthwhile; for those who don't like visiting links, I'll paste my example below so that everybody can easily see and critique my motivation. All input will be appreciated! {-# LANGUAGE OverloadedStrings #-} {-| This is a simple motivating example for why postgresql-transactions may be a useful thing to have. Prepare the database with the following: BEGIN; CREATE TABLE Users( userid SERIAL NOT NULL PRIMARY KEY , username TEXT NOT NULL UNIQUE); CREATE TABLE Messages( msgid SERIAL NOT NULL PRIMARY KEY , msg TEXT NOT NULL UNIQUE); CREATE TABLE Logs( logid SERIAL NOT NULL PRIMARY KEY , logged TIMESTAMP NOT NULL DEFAULT now() , userid INT NOT NULL REFERENCES Users , msgid INT NOT NULL REFERENCES Messages ); COMMIT; -} import Database.PostgreSQL.Simple.Transactions import Data.ByteString ( ByteString ) import Control.Exception getOrCreate :: PgTx -> Query -> Query -> ByteString -> IO Int getOrCreate pg get create value = readRow where readRow :: IO Int readRow = do rows <- query pg get (Only value) case rows of [] -> withTransaction pg mkRow `onException` readRow [Only rowid] -> return rowid mkRow :: PgTx -> IO Int mkRow pg' = do [Only r] <- query pg' create (Only value) return r getUserId :: PgTx -> ByteString -> IO Int getUserId pg = getOrCreate pg "SELECT userid FROM Users WHERE username=?" "INSERT INTO Users(username) VALUES(?) RETURNING userid" getMsgId :: PgTx -> ByteString -> IO Int getMsgId pg = getOrCreate pg "SELECT msgid FROM Messages where msg=?" "INSERT INTO Messages(msg) VALUES(?) RETURNING msgid" logMsg :: PgTx -> ByteString -> ByteString -> IO Int logMsg pg username msg = withTransaction pg go where go :: PgTx -> IO Int go pg' = do userid <- getUserId pg' username msgid <- getMsgId pg' msg [Only logid] <- query pg' "INSERT INTO Logs(userid, msgid) VALUES(?, ?) \ \RETURNING logid" (userid, msgid) return logid main = do c <- connect "host=localhost" getUserId c "jim" >>= \jimid -> putStrLn ("jim id is " ++ (show jimid)) logMsg c "al" "login" >>= print logMsg c "bob" "login" >>= print logMsg c "al" "read email" >>= print logMsg c "al" "logout" >>= print logMsg c "bob" "logout" >>= print

1 0

ANNOUNCE: sqlite-simple 0.2.0.0: mid-level API to the sqlite database
by Janne Hellsten 30 Sep '12

30 Sep '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 * * Notable changes since the previous announcement: - Better thought out module exports - this release makes it possible to add user-defined FromField instances (see https://github.com/nurpax/sqlite-simple/blob/master/test/UserInstances.hs) - sqlite-simple was upgraded to use the latest direct-sqlite 2.2 - As part of the release, the Snap sqlite-simple plugin snaplet-sqlite-simple was upgraded to the latest sqlite-simple. See http://hackage.haskell.org/package/snaplet-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!

1 0

[ANNOUNCE] esqueleto, a type-safe EDSL for SQL queries
by Felipe Almeida Lessa 12 Sep '12

12 Sep '12

Hello! In case someone missed it, I've released a new library called esqueleto: http://blog.felipe.lessa.nom.br/?p=68. It's a type-safe EDSL for SQL queries that is very close to SQL itself. Cheers, PS: I just found out that this mailing list exists! =) -- Felipe.

2 2