Haskell, HDBC.Sqlite3 - How to add a column if it doesn't exist already?

Question

I have a function that given an Int returns a list of lists of Strings.

fetchParts :: Int -> [[String]]

This is what the output looks like

[["title", "some title"], ["rate", "2.4"], ["dist", "some string"], ["tr", "1"], ["td, "2"] ..]]

The length of the output can be variable. Only the first 3 lists can be present 100% of the time.

The later part of the list can be

["a", "1"], ["b", "2"] ..

or

["some", "1"], ["part", "2"], ["of", "3"] ..]

or

["ex1", "a"], ["ex2", "b"], ..]

or some other combination of strings.

And I want to add this output to a sqlite3 database file. I'm using HDBC and HDBC.Sqlite3 for this.

To add something to a database file I'm running functions like these

  initialConnection <- connectSqlite3 "src/parts.db"
  run initialConnection partsEntry []
  commit initialConnection
  disconnect initialConnection

where partsEntry is a simple SQL String like this

partsEntry = "INSERT INTO PARTSDATA ( title, rate, dist, ...) VALUES ( "some title", "2.4", "some string", ...)

where

( title, rate, dist, ...) are from head <$> fetchParts 1

and

("some title", "2.4", "some string" ...) are from last <$> fetchParts 1

The problem is say if "some" column doesn't exists, code will throw errors.

What I want to do is something like this

if column "abc" doesn't exists, add column "abc" and insert "this" value at the current row
if column "abc" exists, just insert "this" value at the current row

But I'm not sure how to go about doing that.

score 0 · Accepted Answer · answered Dec 21 '18 at 16:02

I was able to solve the problem.

First use describeTable function from HDBC package. The function will return column names and type. If you just need the names like I did, this is what you can do

getColumnsInTable :: conn -> String -> IO [String]
getColumnsInTable conn tableName = do
  d <- describeTable conn tableName
  return $ fst <$> d

The return will have all the columns' names.

Scan through the list to see if it contains all the columns you wish. If it doesn't use a function like the following to alter the table, i.e. add a new column with INT type.

createNewColumn conn columnName = do
  let stmt = "ALTER TABLE FantasyBooks ADD COLUMN " ++ columnName ++ " INT;"
  run conn stmt []

Haskell, HDBC.Sqlite3 - How to add a column if it doesn't exist already?

1 Answers1