sqlite-simple 0.1.0.0 → 0.1.0.1

raw patch · 7 files changed

+406/−51 lines, 7 files

Files

• Database/SQLite/Simple.hs +303/−33
• Database/SQLite/Simple/ToField.hs +1/−5
• Database/SQLite/Simple/ToRow.hs +3/−0
• Database/SQLite/Simple/Types.hs +0/−11
• README.markdown +94/−0
• sqlite-simple.cabal +3/−1
• test/Simple.hs +2/−1

@@ -16,12 +16,37 @@ ------------------------------------------------------------------------------  module Database.SQLite.Simple (-    -- * Examples of use+    -- ** Examples of use     -- $use++    -- ** The Query type+    -- $querytype++    -- ** Parameter substitution+    -- $subst++    -- *** Type inference+    -- $inference++    -- ** Substituting a single parameter+    -- $only_param++    -- * Extracting results+    -- $result++    -- ** Handling null values+    -- $null++    -- ** Type conversions+    -- $types++    -- * Connections     open   , close+    -- * Queries that return results   , query   , query_+    -- * Statements that do not return results   , execute   , execute_   , field@@ -29,7 +54,6 @@   , Connection   , ToRow   , FromRow-  , In(..)   , Only(..)   , (:.)(..)     -- ** Exceptions@@ -57,37 +81,6 @@ import           Database.SQLite.Simple.ToRow (ToRow(..)) import           Database.SQLite.Simple.FromRow -{- $use-Create a test database by copy&pasting the below snippet to your-shell:--@-sqlite3 test.db \"CREATE TABLE test (id INTEGER PRIMARY KEY, str text);\\-INSERT INTO test (str) VALUES ('test string');\"-@--..and access it from 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-@--}- -- | Exception thrown if a 'Query' was malformed. -- This may occur if the number of \'@?@\' characters in the query -- string does not match the number of parameters provided.@@ -207,3 +200,280 @@                     , fmtQuery = q                     , fmtParams = map (B.pack . show) xs                     }++-- $use+-- Create a test database by copy pasting the below snippet to your+-- shell:+--+-- @+-- sqlite3 test.db \"CREATE TABLE test (id INTEGER PRIMARY KEY, str text); \\+-- INSERT INTO test (str) VALUES ('test string');\"+-- @+--+-- ..and access it from Haskell:+--+-- > {-# LANGUAGE OverloadedStrings #-}+-- >+-- > 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++-- $querytype+--+-- SQL-based applications are somewhat notorious for their+-- susceptibility to attacks through the injection of maliciously+-- crafted data. The primary reason for widespread vulnerability to+-- SQL injections is that many applications are sloppy in handling+-- user data when constructing SQL queries.+--+-- This library provides a 'Query' type and a parameter substitution+-- facility to address both ease of use and security.  A 'Query' is a+-- @newtype@-wrapped 'Text'. It intentionally exposes a tiny API that+-- is not compatible with the 'Text' API; this makes it difficult to+-- construct queries from fragments of strings.  The 'query' and+-- 'execute' functions require queries to be of type 'Query'.+--+-- To most easily construct a query, enable GHC's @OverloadedStrings@+-- language extension and write your query as a normal literal string.+--+-- > {-# LANGUAGE OverloadedStrings #-}+-- >+-- > import Database.SQLite.Simple+-- >+-- > hello = do+-- >   conn <- connect defaultConnectInfo+-- >   query conn "select 2 + 2"+--+-- A 'Query' value does not represent the actual query that will be+-- executed, but is a template for constructing the final query.++-- $subst+--+-- Since applications need to be able to construct queries with+-- parameters that change, this library uses SQLite's parameter+-- binding query substitution capability.+--+-- The 'Query' template accepted by 'query' and 'execute' can contain+-- any number of \"@?@\" characters.  Both 'query' and 'execute'+-- accept a third argument, typically a tuple. When constructing the+-- real query to execute, these functions replace the first \"@?@\" in+-- the template with the first element of the tuple, the second+-- \"@?@\" with the second element, and so on. If necessary, each+-- tuple element will be quoted and escaped prior to substitution;+-- this defeats the single most common injection vector for malicious+-- data.+--+-- For example, given the following 'Query' template:+--+-- > select * from user where first_name = ? and age > ?+--+-- And a tuple of this form:+--+-- > ("Boris" :: String, 37 :: Int)+--+-- The query to be executed will look like this after substitution:+--+-- > select * from user where first_name = 'Boris' and age > 37+--+-- If there is a mismatch between the number of \"@?@\" characters in+-- your template and the number of elements in your tuple, a+-- 'FormatError' will be thrown.+--+-- Note that the substitution functions do not attempt to parse or+-- validate your query. It's up to you to write syntactically valid+-- SQL, and to ensure that each \"@?@\" in your query template is+-- matched with the right tuple element.+--+-- This library restricts parameter substitution to work only with+-- \"@?@\" characters.  SQLite natively supports several other+-- syntaxes for binding query parameters.  Unsupported parameters will+-- be rejected and a 'FormatError' will be thrown.++-- $inference+--+-- Automated type inference means that you will often be able to avoid+-- supplying explicit type signatures for the elements of a tuple.+-- However, sometimes the compiler will not be able to infer your+-- types. Consider a case where you write a numeric literal in a+-- parameter tuple:+--+-- > query conn "select ? + ?" (40,2)+--+-- The above query will be rejected by the compiler, because it does+-- not know the specific numeric types of the literals @40@ and @2@.+-- This is easily fixed:+--+-- > query conn "select ? + ?" (40 :: Double, 2 :: Double)+--+-- The same kind of problem can arise with string literals if you have+-- the @OverloadedStrings@ language extension enabled.  Again, just+-- use an explicit type signature if this happens.++-- $only_param+--+-- Haskell lacks a single-element tuple type, so if you have just one+-- value you want substituted into a query, what should you do?+--+-- The obvious approach would appear to be something like this:+--+-- > instance (Param a) => QueryParam a where+-- >     ...+--+-- Unfortunately, this wreaks havoc with type inference, so we take a+-- different tack. To represent a single value @val@ as a parameter, write+-- a singleton list @[val]@, use 'Just' @val@, or use 'Only' @val@.+--+-- Here's an example using a singleton list:+--+-- > execute conn "insert into users (first_name) values (?)"+-- >              ["Nuala"]++-- $in+--+-- Suppose you want to write a query using an @IN@ clause:+--+-- > select * from users where first_name in ('Anna', 'Boris', 'Carla')+--+-- In such cases, it's common for both the elements and length of the+-- list after the @IN@ keyword to vary from query to query.+--+-- To address this case, use the 'In' type wrapper, and use a single+-- \"@?@\" character to represent the list.  Omit the parentheses+-- around the list; these will be added for you.+--+-- Here's an example:+--+-- > query conn "select * from users where first_name in ?" $+-- >       In ["Anna", "Boris", "Carla"]+--+-- If your 'In'-wrapped list is empty, the string @\"(null)\"@ will be+-- substituted instead, to ensure that your clause remains+-- syntactically valid.++-- $many+--+-- If you know that you have many rows of data to insert into a table,+-- it is much more efficient to perform all the insertions in a single+-- multi-row @INSERT@ statement than individually.+--+-- The 'executeMany' function is intended specifically for helping+-- with multi-row @INSERT@ and @UPDATE@ statements. Its rules for+-- query substitution are different than those for 'execute'.+--+-- What 'executeMany' searches for in your 'Query' template is a+-- single substring of the form:+--+-- > values (?,?,?)+--+-- The rules are as follows:+--+-- * The keyword @VALUES@ is matched case insensitively.+--+-- * There must be no other \"@?@\" characters anywhere in your+--   template.+--+-- * There must one or more \"@?@\" in the parentheses.+--+-- * Extra white space is fine.+--+-- The last argument to 'executeMany' is a list of parameter+-- tuples. These will be substituted into the query where the @(?,?)@+-- string appears, in a form suitable for use in a multi-row @INSERT@+-- or @UPDATE@.+--+-- Here is an example:+--+-- > executeMany conn+-- >   "insert into users (first_name,last_name) values (?,?)"+-- >   [("Boris","Karloff"),("Ed","Wood")]+--+-- The query that will be executed here will look like this+-- (reformatted for tidiness):+--+-- > insert into users (first_name,last_name) values+-- >   ('Boris','Karloff'),('Ed','Wood')++-- $result+--+-- The 'query' and 'query_' functions return a list of values in the+-- 'FromRow' typeclass. This class performs automatic extraction+-- and type conversion of rows from a query result.+--+-- Here is a simple example of how to extract results:+--+-- > import qualified Data.Text as Text+-- >+-- > xs <- query_ conn "select name,age from users"+-- > forM_ xs $ \(name,age) ->+-- >   putStrLn $ Text.unpack name ++ " is " ++ show (age :: Int)+--+-- Notice two important details about this code:+--+-- * The number of columns we ask for in the query template must+--   exactly match the number of elements we specify in a row of the+--   result tuple.  If they do not match, a 'ResultError' exception+--   will be thrown.+--+-- * Sometimes, the compiler needs our help in specifying types. It+--   can infer that @name@ must be a 'Text', due to our use of the+--   @unpack@ function. However, we have to tell it the type of @age@,+--   as it has no other information to determine the exact type.++-- $null+--+-- The type of a result tuple will look something like this:+--+-- > (Text, Int, Int)+--+-- Although SQL can accommodate @NULL@ as a value for any of these+-- types, Haskell cannot. If your result contains columns that may be+-- @NULL@, be sure that you use 'Maybe' in those positions of of your+-- tuple.+--+-- > (Text, Maybe Int, Int)+--+-- If 'query' encounters a @NULL@ in a row where the corresponding+-- Haskell type is not 'Maybe', it will throw a 'ResultError'+-- exception.++-- $only_result+--+-- To specify that a query returns a single-column result, use the+-- 'Only' type.+--+-- > xs <- query_ conn "select id from users"+-- > forM_ xs $ \(Only dbid) -> {- ... -}++-- $types+--+-- Conversion of SQL values to Haskell values is somewhat+-- permissive. Here are the rules.+--+-- * For numeric types, any Haskell type that can accurately represent+--   an SQLite INTEGER is considered \"compatible\".+--+-- * If a numeric incompatibility is found, 'query' will throw a+--   'ResultError'.+--+-- * SQLite's TEXT type is always encoded in UTF-8.  Thus any text+--   data coming from an SQLite database should always be compatible+--   with Haskell 'String' and 'Text' types.+--+-- * SQLite's BLOB type will only be conversible to a Haskell+--   'ByteString'.+--+-- You can extend conversion support to your own types be adding your+-- own 'FromField' / 'ToField' instances.

@@ -29,7 +29,7 @@ import           GHC.Float  import           Database.SQLite3 as Base-import           Database.SQLite.Simple.Types (In(..), Null)+import           Database.SQLite.Simple.Types (Null)  -- | A type that may be used as a single parameter to a SQL query. class ToField a where@@ -44,10 +44,6 @@     toField Nothing  = Base.SQLNull     toField (Just a) = toField a     {-# INLINE toField #-}--instance (ToField a) => ToField (In [a]) where-    toField (In _) =-      error "NOT IMPLEMENTED see https://github.com/nurpax/sqlite-simple/issues/6"  instance ToField Null where     toField _ = Base.SQLNull

@@ -86,5 +86,8 @@         [toField a, toField b, toField c, toField d, toField e, toField f,          toField g, toField h, toField i, toField j] +instance (ToField a) => ToRow [a] where+    toRow = map toField+ instance (ToRow a, ToRow b) => ToRow (a :. b) where     toRow (a :. b) = toRow a ++ toRow b

@@ -20,7 +20,6 @@     (       Null(..)     , Only(..)-    , In(..)     , Query(..)     , (:.)(..)     ) where@@ -91,16 +90,6 @@ newtype Only a = Only {       fromOnly :: a     } deriving (Eq, Ord, Read, Show, Typeable, Functor)---- | Wrap a list of values for use in an @IN@ clause.  Replaces a--- single \"@?@\" character with a parenthesized list of rendered--- values.------ Example:------ > query c "select * from whatever where id in ?" (In [3,4,5])-newtype In a = In a-    deriving (Eq, Ord, Read, Show, Typeable, Functor)  -- | A composite type to parse your custom data structures without -- having to define dummy newtype wrappers every time.

@@ -0,0 +1,94 @@+sqlite-simple: mid-level bindings to the sqlite database+========================================================++This library is a mid-level Haskell binding to the SQLite database.++Sqlite-simple provides a convenient API to sqlite that does some level+of automatic data conversion between the database and Haskell types.+The API has been modeled directly after+[postgresql-simple](http://github.com/lpsmith/postgresql-simple) which+in turn borrows from+[mysql-simple](https://github.com/bos/mysql-simple).++The library has been fairly well unit tested, but I still consider it+somewhat experimental.++Building+--------++The usual cabal/cabal-dev instructions apply.++[![Build Status](https://secure.travis-ci.org/nurpax/sqlite-simple.png)](http://travis-ci.org/nurpax/sqlite-simple)++Examples of use+---------------++Create a test database by copy&pasting the below snippet to your+shell:++```+sqlite3 test.db "CREATE TABLE test (id INTEGER PRIMARY KEY, str text);\+INSERT INTO test (str) VALUES ('test string');"+```++..and access it in Haskell:++```haskell+{-# LANGUAGE OverloadedStrings #-}+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+```++More simple usage examples can be found from [sqlite-simple unit+tests](https://github.com/nurpax/sqlite-simple/blob/master/test/Simple.hs).+++Development+-----------++The development roadmap for sqlite-simple is mostly captured in the+github issue database.++I'm happy to receive bug reports, fixes, documentation enhancements,+and other improvements.++Please report bugs via the+[github issue tracker](http://github.com/nurpax/sqlite-simple/issues).++For general database issues with a Haskell focus, I recommend sending+e-mail to the [database-devel mailing+list](http://www.haskell.org/mailman/listinfo/database-devel).+++Credits+-------++A lot of the code is directly borrowed from+[mysql-simple](http://github.com/bos/mysql-simple) by Bryan O'Sullivan+and from+[postgresql-simple](http://github.com/lpsmith/postgresql-simple) by+Leon P. Smith.  Like Leon in postgresql-simple, I borrow code and+documentation directly from both of these ancestor libraries.++This package builds on top of the+[direct-sqlite](http://hackage.haskell.org/package/direct-sqlite)+package by Irene Knapp.++SQLite is rather weakly-typed and thus the SQL to Haskell type+strictness of the parent projects does not necessarily apply to this+package.

@@ -1,5 +1,5 @@ Name:                sqlite-simple-Version:             0.1.0.0+Version:             0.1.0.1 Synopsis:            Mid-Level SQLite client library Description:     Mid-level SQLite client library, based on postgresql-simple.@@ -20,6 +20,8 @@ Build-type:          Simple  Cabal-version:       >= 1.10++extra-source-files:  README.markdown  Library   Default-language:  Haskell2010

@@ -48,4 +48,5 @@   assertEqual "select params" "test string" row   [Only row] <- query conn "SELECT t FROM testparams WHERE id = ?" (Only (2 :: Int)) :: IO [Only String]   assertEqual "select params" "test2" row-  return ()+  [Only i] <- query conn "SELECT ?+?" [42 :: Int, 1 :: Int] :: IO [Only Int]+  assertEqual "select int param" 43 i