Add way to execute multiple commands from file

[chshersh]

My use case is that I have schema.sql file with ~10 tables of different sizes. But when I read this file as a String and execute it via execute_ command it doesn't work properly. I can create my schema only by writing 10 calls directly in Haskell code (which is not that convenient).

It would be really nice if mysql-haskell library could support execute_ that can execute multiple commands from .sql file.

[winterland1989]

Can you provide a segment of your sql commands? I believe there should be no problem to run them within one Query, let me see if there're any issues here.

[chshersh]

@winterland1989 Will try to come up with the example.

[chshersh]

@winterland1989 Here is the minimal example:

#! /usr/bin/env cabal
{- cabal:
build-depends: base >= 4.11 && < 4.12
             , mysql-haskell ^>= 0.8.3.0
             , io-streams
-}

{-# LANGUAGE OverloadedStrings #-}

module Main where

import Data.String (fromString)
import Database.MySQL.Base
import Database.MySQL.Connection (utf8mb4_unicode_ci)

import qualified System.IO.Streams as Stream


createSchema :: Query
createSchema = fromString $ unlines
    [ "DROP TABLE IF EXISTS users;"
    , "CREATE TABLE users"
    , "    ( name TEXT NOT NULL"
    , "    ) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8mb4;"
    ]

main :: IO ()
main = do
    (greet, conn) <- connectDetail ConnectInfo
        { ciHost = "127.0.0.1"
        , ciPort = 3306
        , ciUser = "root"
        , ciPassword = "password"
        , ciDatabase = "test_db"
        , ciCharset = utf8mb4_unicode_ci
        }
    print greet

    print =<< execute_ conn createSchema
    print =<< execute_ conn "INSERT INTO users VALUES ('Ivan')"

    (_defs, is) <- query_ conn "SELECT * FROM users"
    print =<< Stream.toList is

When I run this code, I see the following output:

Greeting {greetingProtocol = 10, greetingVersion = "5.7.20", greetingConnId = 36, greetingSalt1 = "i\RS_iF#i+", greetingCaps = 3254779903, greetingCharset = 8, greetingStatus = 2, greetingSalt2 = "n\NAK0&ZK~~\FSZ\ETX7", greetingAuthPlugin = "mysql_native_password"}
OK {okAffectedRows = 0, okLastInsertID = 0, okStatus = 10, okWarningCnt = 0}
OK {okAffectedRows = 0, okLastInsertID = 0, okStatus = 2, okWarningCnt = 0}
[[]]

which is not what expected. You can see that the createSchema query contains two commands. However, if I execute those two commands separately in the following way:

dropSchema :: Query
dropSchema = "DROP TABLE IF EXISTS users"

createSchema :: Query
createSchema = fromString $ unlines
    [ "CREATE TABLE users"
    , "    ( name TEXT NOT NULL"
    , "    ) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8mb4"
    ]

...

    print =<< execute_ conn dropSchema
    print =<< execute_ conn createSchema

everything works as expected and I see the following result:

OK {okAffectedRows = 0, okLastInsertID = 0, okStatus = 2, okWarningCnt = 0}
OK {okAffectedRows = 0, okLastInsertID = 0, okStatus = 2, okWarningCnt = 0}
OK {okAffectedRows = 1, okLastInsertID = 0, okStatus = 2, okWarningCnt = 0}
[[MySQLText "Ivan"]]

[winterland1989]

The issue here is not we can't send multiple statements within on query command: The problem is that we have to read all the responds (two OK packets here) otherwise the next query is affected. You can prove it by adding an extra waitCommandReply:

#! /usr/bin/env cabal
{- cabal:
-}

{-# LANGUAGE OverloadedStrings #-}

module Main where

import Data.String (fromString)
import Database.MySQL.Base
import Database.MySQL.Connection

import qualified System.IO.Streams as Stream


createSchema :: Query
createSchema = fromString $ unlines
    [ "DROP TABLE IF EXISTS users;"
    , "CREATE TABLE users"
    , "    ( name TEXT NOT NULL"
    , "    ) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8mb4;"
    ]

main :: IO ()
main = do
    (greet, conn) <- connectDetail ConnectInfo
        { ciHost = "127.0.0.1"
        , ciPort = 3306
        , ciUser = "testMySQLHaskell"
        , ciPassword = ""
        , ciDatabase = "testMySQLHaskell"
        , ciCharset = utf8mb4_unicode_ci
        }
    print greet

    print =<< execute_ conn createSchema
    -- we manually consume all OK packets so that following query sequence is unaffected
    print =<< waitCommandReply (mysqlRead conn)
    print =<< execute_ conn "INSERT INTO users VALUES ('Ivan')"

    (_defs, is) <- query_ conn "SELECT * FROM users"
    print =<< Stream.toList is

And the response is：

Greeting {greetingProtocol = 10, greetingVersion = "5.7.23-0ubuntu0.18.04.1", greetingConnId = 27, greetingSalt1 = "'kof\EOTFC\DC2", greetingCaps = 2181036031, greetingCharset = 8, greetingStatus = 2, greetingSalt2 = "\\N6\\:3\t?wDT#", greetingAuthPlugin = "mysql_native_password"}
OK {okAffectedRows = 0, okLastInsertID = 0, okStatus = 10, okWarningCnt = 0}
OK {okAffectedRows = 0, okLastInsertID = 0, okStatus = 2, okWarningCnt = 0}
OK {okAffectedRows = 1, okLastInsertID = 0, okStatus = 2, okWarningCnt = 0}
[[MySQLText "Ivan"]]

Which is expected.

So the question becomes how do we know the number of OK packets we should expect from a multiple statement query, or to find a way to tell if there's still OK packets left to be read.

From MySQL C client API there should be a way to do the later, I'll dig it out.

[chshersh]

@winterland1989 As a side effect: I'm observing different results when executing query "DROP TABLE IF EXISTS users;" and "DROP TABLE IF EXISTS users" (because of the semicolon at the end). Should be careful to not add it by accident.

[winterland1989]

It turned out to be an easy job: the okStatus is bit-field, we can use .|. 0x08 to check if there's more packet to be read.

[winterland1989]

I have pushed a new version which has executeMany_ to suit your need. Before pushing to hackage, can you help me to confirm the master works for you?

[chshersh]

@winterland1989 Sure, will try to test it now.

[chshersh]

@winterland1989 Just tested on my project. Can confirm: works as expected 👍

[winterland1989]

Release on hackage as v0.8.4.0, closing.