src/Tools/SQL.hs

   1 {-
   2     Tools/SQL.hs
   3
   4     Copyright 2013 Louis-Guillaume Gagnon <louis.guillaume.gagnon@gmail.com>
   5
   6     This program is free software: you can redistribute it and/or modify
   7     it under the terms of the GNU General Public License as published by
   8     the Free Software Foundation, either version 3 of the License, or
   9     (at your option) any later version.
  10
  11     This program is distributed in the hope that it will be useful,
  12     but WITHOUT ANY WARRANTY; without even the implied warranty of
  13     MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  14     GNU General Public License for more details.
  15
  16     You should have received a copy of the GNU General Public License
  17     along with this program.  If not, see <http://www.gnu.org/licenses/>.
  18 -}
  19
  20 module Tools.SQL (
  21     buildSQLAdd,
  22     getAllEntries,
  23     getEntry,
  24     runSQL,
  25     searchEntries
  26 ) where
  27
  28 import Data.List (intersperse)
  29 import Database.HDBC
  30 import Database.HDBC.Sqlite3
  31
  32 import Tools.Constants
  33
  34 buildSQLAdd :: ([String],[String]) -> String
  35 buildSQLAdd (ks,vs) =
  36     "INSERT INTO " ++ tableName ++ " (" ++ keys ++ ") VALUES(" ++ vals ++ ");"
  37     where keys = concat $ intersperse "," ks
  38           vals = "'" ++ (concat (intersperse "','" vs)) ++ "'"
  39
  40 buildSQLSearch :: ([String],[String]) -> String
  41 buildSQLSearch ps = "SELECT * FROM " ++ tableName ++ " WHERE " ++ values ps
  42     where values ((k:[]),(v:[])) = k ++ "='" ++ v ++ "';"
  43           values ((k:ks),(v:vs)) = k ++ "='" ++ v ++ "'" ++ " AND " ++ values (ks,vs)
  44
  45 createdb :: Connection -> IO Connection
  46 createdb conn = do run conn ("CREATE TABLE " ++ tableName ++ "(id       INTEGER PRIMARY KEY,\
  47                                                         \ File     VARCHAR(1000),\
  48                                                         \ Title    VARCHAR(1000),\
  49                                                         \ Authors  VARCHAR(1000),\
  50                                                         \ Keywords VARCHAR(1000),\
  51                                                         \ Journal  VARCHAR(1000),\
  52                                                         \ Volume   VARCHAR(1000),\
  53                                                         \ Year     VARCHAR(1000),\
  54                                                         \ Pages    VARCHAR(1000),\
  55                                                         \ Bookmarked VARCHAR(1000));") []
  56                    commit conn
  57                    return conn
  58
  59 opendb :: IO Connection
  60 opendb = do
  61     conn <- connectSqlite3 dbName
  62     tables <- getTables conn
  63     if not (tableName `elem` tables)
  64         then do createdb conn
  65         else do return conn
  66
  67 runSQL :: String -> IO ()
  68 runSQL sql = do
  69     db <- opendb
  70     run db sql []
  71     commit db
  72     disconnect db
  73     return ()
  74
  75 getFromDB :: String -> IO [[SqlValue]]
  76 getFromDB sql = do
  77     db <- opendb
  78     result <- quickQuery' db sql []
  79     disconnect db
  80     return result
  81
  82 getEntry :: Int -> IO (Either String [String])
  83 getEntry id = do row <- retrieveSqlValues id
  84                  case row of
  85                     Left msg      -> return $ Left msg
  86                     Right sqlVals -> return $ Right $ map fromSqlToString sqlVals
  87
  88 getAllEntries :: IO [[String]]
  89 getAllEntries = do
  90     rows <- retrieveAllRows
  91     return $ map (map fromSqlToString) rows
  92
  93 fromSqlToString :: SqlValue -> String
  94 fromSqlToString SqlNull = ""
  95 fromSqlToString value = fromSql value
  96
  97 retrieveAllRows :: IO [[SqlValue]]
  98 retrieveAllRows = getFromDB $ "SELECT * FROM " ++ tableName
  99
 100 retrieveSqlValues :: Int -> IO (Either String [SqlValue])
 101 retrieveSqlValues id = do
 102     let sql = "SELECT * FROM " ++ tableName ++ " WHERE id = " ++ (show id)
 103     result <- getFromDB sql
 104     case result of
 105         []    -> return $ Left $ "entry no. " ++ (show id) ++ " doesn't exist"
 106         [val] -> return $ Right val
 107
 108 searchEntries :: ([String],[String]) -> IO [[String]]
 109 searchEntries ([],[]) = getAllEntries
 110 searchEntries ps = do
 111     let sql = buildSQLSearch ps
 112     entries <- getFromDB sql
 113     return $ map (map fromSqlToString) entries
 114
 115
 116