New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

print Queries even if the database isn't actually present #274

Closed
psibi opened this Issue Aug 28, 2014 · 17 comments

Comments

Projects
None yet
4 participants
@psibi
Member

psibi commented Aug 28, 2014

I have been using persistent basically as a data modelling tool to typecheck my table and in the future use it as an migration tool. For postgreSql, for printing out the Sql queries I have to write the following code:

connStr = "host=localhost dbname=test user=test password=test port=5432"

main :: IO ()
main = withPostgresqlPool connStr 10 $ \pool -> do
  flip runSqlPersistMPool pool $ do
    printMigration migrateAll

The problem with the above code is that if the actual database doesn't exist, it doesn't print any of the queries. I looked into the codebase for a while and wasn't able to write any functions to accomplish this task. Ideally, I would like to have any helper function which generates the sql queries for a particular database (somewhat like parseTest from attoparsec where you can test it interactively). Is this possible ?

@snoyberg

This comment has been minimized.

Show comment
Hide comment
@snoyberg

snoyberg Aug 29, 2014

Member

For persistent-2.0, what about:

flip runReaderT undefined $ printMigration migrateAll

You may need to modify undefined to instead provide some of the fields from the Connection data type (in particular, escaping).

Member

snoyberg commented Aug 29, 2014

For persistent-2.0, what about:

flip runReaderT undefined $ printMigration migrateAll

You may need to modify undefined to instead provide some of the fields from the Connection data type (in particular, escaping).

@psibi

This comment has been minimized.

Show comment
Hide comment
@psibi

psibi Aug 30, 2014

Member

Thanks, Will check this up in two or three days. On a related note, is it worthwhile to add this as an library function ?

Member

psibi commented Aug 30, 2014

Thanks, Will check this up in two or three days. On a related note, is it worthwhile to add this as an library function ?

@snoyberg

This comment has been minimized.

Show comment
Hide comment
@snoyberg

snoyberg Aug 30, 2014

Member

I'm not opposed.

Member

snoyberg commented Aug 30, 2014

I'm not opposed.

@psibi

This comment has been minimized.

Show comment
Hide comment
@psibi

psibi Oct 5, 2014

Member

Ok, so I started working on this and created the following function withing Database.Persist.Postgresql:

dummyPrint :: Migration -> IO ()
dummyPrint fn = runStderrLoggingT $ withPostgresqlPool "" 10 $ \pool -> do
  liftIO $ do
    smap <- newIORef $ Map.empty
    let sqlbend = SqlBackend { connPrepare = error "conn prepare",
                               connInsertSql = error "conn insert sql",
                               connStmtMap = smap,
                               connClose = error "connClose",
                               connMigrateSql = migrate',
                               connBegin = error "connBegin",
                               connCommit = error "connCommit",
                               connRollback = error "connRollback",
                               connEscapeName = escape,
                               connNoLimit = error "connNoLimit",
                               connRDBMS = error "connRDBMD",
                               connLimitOffset = error "connLimitoffset",
                               connLogFunc = error "connLogFunc"} :: SqlBackend
    flip runReaderT sqlbend $ printMigration fn

But this gives me an error of "conn prepare" when I pass a migration to dummyPrint. So, it seems that it is connecting to the database. I would like to get just the sql statements even if the database isn't present. Is this achievable in the current state of the library ? (Also, sorry for replying to this issue so lately.)

Member

psibi commented Oct 5, 2014

Ok, so I started working on this and created the following function withing Database.Persist.Postgresql:

dummyPrint :: Migration -> IO ()
dummyPrint fn = runStderrLoggingT $ withPostgresqlPool "" 10 $ \pool -> do
  liftIO $ do
    smap <- newIORef $ Map.empty
    let sqlbend = SqlBackend { connPrepare = error "conn prepare",
                               connInsertSql = error "conn insert sql",
                               connStmtMap = smap,
                               connClose = error "connClose",
                               connMigrateSql = migrate',
                               connBegin = error "connBegin",
                               connCommit = error "connCommit",
                               connRollback = error "connRollback",
                               connEscapeName = escape,
                               connNoLimit = error "connNoLimit",
                               connRDBMS = error "connRDBMD",
                               connLimitOffset = error "connLimitoffset",
                               connLogFunc = error "connLogFunc"} :: SqlBackend
    flip runReaderT sqlbend $ printMigration fn

But this gives me an error of "conn prepare" when I pass a migration to dummyPrint. So, it seems that it is connecting to the database. I would like to get just the sql statements even if the database isn't present. Is this achievable in the current state of the library ? (Also, sorry for replying to this issue so lately.)

@snoyberg

This comment has been minimized.

Show comment
Hide comment
@snoyberg

snoyberg Oct 5, 2014

Member

Can you try just filling in some dummy implementation for connPrepare?

Member

snoyberg commented Oct 5, 2014

Can you try just filling in some dummy implementation for connPrepare?

@psibi

This comment has been minimized.

Show comment
Hide comment
@psibi

psibi Oct 5, 2014

Member

This is the dummy implementation I did:

mainp :: Migration -> IO ()
mainp fn = runStderrLoggingT $ withPostgresqlPool "" 10 $ \pool -> do
  liftIO $ do
    smap <- newIORef $ Map.empty
    conn <- PG.connectPostgreSQL "host=localhost dbname=test user=postgres password=postgres port=5432"
    let sqlbend = SqlBackend { connPrepare = prepare' conn,
                               connInsertSql = error "conn insert sql",
                               connStmtMap = smap,
                               connClose = error "connClose",
                               connMigrateSql = migrate',
                               connBegin = error "connBegin",
                               connCommit = error "connCommit",
                               connRollback = error "connRollback",
                               connEscapeName = escape,
                               connNoLimit = error "connNoLimit",
                               connRDBMS = error "connRDBMD",
                               connLimitOffset = error "connLimitoffset",
                               connLogFunc = error "connLogFunc"} :: SqlBackend
    flip runReaderT sqlbend $ printMigration fn

Note that I'm actually passing the actual username and password. In case if i pass wrong username or password, it gives out the exception that the given details are wrong. Even in this case, it gives out this exception:

*** Exception: libpq: failed (FATAL:  database "test" does not exist)

Now, probably it will work if I create a database "test". But, that's not what I want. It should also be noted that if the postgresql server isn't installed, it throws an error that it is not able to connect to the server.

Member

psibi commented Oct 5, 2014

This is the dummy implementation I did:

mainp :: Migration -> IO ()
mainp fn = runStderrLoggingT $ withPostgresqlPool "" 10 $ \pool -> do
  liftIO $ do
    smap <- newIORef $ Map.empty
    conn <- PG.connectPostgreSQL "host=localhost dbname=test user=postgres password=postgres port=5432"
    let sqlbend = SqlBackend { connPrepare = prepare' conn,
                               connInsertSql = error "conn insert sql",
                               connStmtMap = smap,
                               connClose = error "connClose",
                               connMigrateSql = migrate',
                               connBegin = error "connBegin",
                               connCommit = error "connCommit",
                               connRollback = error "connRollback",
                               connEscapeName = escape,
                               connNoLimit = error "connNoLimit",
                               connRDBMS = error "connRDBMD",
                               connLimitOffset = error "connLimitoffset",
                               connLogFunc = error "connLogFunc"} :: SqlBackend
    flip runReaderT sqlbend $ printMigration fn

Note that I'm actually passing the actual username and password. In case if i pass wrong username or password, it gives out the exception that the given details are wrong. Even in this case, it gives out this exception:

*** Exception: libpq: failed (FATAL:  database "test" does not exist)

Now, probably it will work if I create a database "test". But, that's not what I want. It should also be noted that if the postgresql server isn't installed, it throws an error that it is not able to connect to the server.

@snoyberg

This comment has been minimized.

Show comment
Hide comment
@snoyberg

snoyberg Oct 5, 2014

Member

That doesn't look like a dummy implementation to me. What about something like:

connPrepare = \sql -> do
    putStrLn $ "DEBUG connPrepare on: " ++ show sql
    return Statement
        { stmtFinalize = return ()
        , stmtReset = return ()
        , stmtExecute = error "stmtExecute"
        , stmtQuery = error "stmtQuery"
        }
Member

snoyberg commented Oct 5, 2014

That doesn't look like a dummy implementation to me. What about something like:

connPrepare = \sql -> do
    putStrLn $ "DEBUG connPrepare on: " ++ show sql
    return Statement
        { stmtFinalize = return ()
        , stmtReset = return ()
        , stmtExecute = error "stmtExecute"
        , stmtQuery = error "stmtQuery"
        }
@psibi

This comment has been minimized.

Show comment
Hide comment
@psibi

psibi Oct 5, 2014

Member

Thanks for the quick prompt, gives this runtime error:

*** Exception: libpq: failed (FATAL:  database "test" does not exist)
Member

psibi commented Oct 5, 2014

Thanks for the quick prompt, gives this runtime error:

*** Exception: libpq: failed (FATAL:  database "test" does not exist)
@snoyberg

This comment has been minimized.

Show comment
Hide comment
@snoyberg

snoyberg Oct 5, 2014

Member

Why are you still trying to create a database connection? The connectPostgreSQL call should be completely unnecessary.

Member

snoyberg commented Oct 5, 2014

Why are you still trying to create a database connection? The connectPostgreSQL call should be completely unnecessary.

@psibi

This comment has been minimized.

Show comment
Hide comment
@psibi

psibi Oct 5, 2014

Member

Sorry, missed that. Yes, I can see some results now. :)

Member

psibi commented Oct 5, 2014

Sorry, missed that. Yes, I can see some results now. :)

@psibi

This comment has been minimized.

Show comment
Hide comment
@psibi

psibi Oct 5, 2014

Member

The actual query which I'm seeing is

DEBUG connPrepare on: "SELECT column_name ,is_nullable ,udt_name ,column_default ,numeric_precision ,numeric_scale FROM information_schema.columns WHERE table_catalog=current_database() AND table_schema=current_schema() AND table_name=? AND column_name <> ?"
*** Exception: stmtQuery

I will look more on this.

Member

psibi commented Oct 5, 2014

The actual query which I'm seeing is

DEBUG connPrepare on: "SELECT column_name ,is_nullable ,udt_name ,column_default ,numeric_precision ,numeric_scale FROM information_schema.columns WHERE table_catalog=current_database() AND table_schema=current_schema() AND table_name=? AND column_name <> ?"
*** Exception: stmtQuery

I will look more on this.

@meteficha

This comment has been minimized.

Show comment
Hide comment
@meteficha

meteficha Oct 5, 2014

Member

In order to know which operations have to be executed as part of the migration, all backends query the current state of the database schema. One would need to create a new code path that assumes the database is clean. Or try to mock every database query (perhaps always returning empty results may work).

Member

meteficha commented Oct 5, 2014

In order to know which operations have to be executed as part of the migration, all backends query the current state of the database schema. One would need to create a new code path that assumes the database is clean. Or try to mock every database query (perhaps always returning empty results may work).

@snoyberg

This comment has been minimized.

Show comment
Hide comment
@snoyberg

snoyberg Jul 19, 2015

Member

Closing out old issues, please reopen if still relevant

Member

snoyberg commented Jul 19, 2015

Closing out old issues, please reopen if still relevant

@snoyberg snoyberg closed this Jul 19, 2015

@psibi

This comment has been minimized.

Show comment
Hide comment
@psibi

psibi Jul 20, 2015

Member

@snoyberg I think only the project owners in github can re-open the issue. That being said, I would like to have this feature in persistent ?

Member

psibi commented Jul 20, 2015

@snoyberg I think only the project owners in github can re-open the issue. That being said, I would like to have this feature in persistent ?

@snoyberg

This comment has been minimized.

Show comment
Hide comment
@snoyberg

snoyberg Jul 20, 2015

Member

Would you be interested in attempting a PR for it then? I'd be happy to
provide any guidance necessary

On Sun, Jul 19, 2015, 5:52 PM Sibi notifications@github.com wrote:

@snoyberg https://github.com/snoyberg I think only the project owners
in github can re-open the issue. That being said, I would like to have this
feature in persistent ?


Reply to this email directly or view it on GitHub
#274 (comment)
.

Member

snoyberg commented Jul 20, 2015

Would you be interested in attempting a PR for it then? I'd be happy to
provide any guidance necessary

On Sun, Jul 19, 2015, 5:52 PM Sibi notifications@github.com wrote:

@snoyberg https://github.com/snoyberg I think only the project owners
in github can re-open the issue. That being said, I would like to have this
feature in persistent ?


Reply to this email directly or view it on GitHub
#274 (comment)
.

@gregwebs gregwebs reopened this Jul 20, 2015

@psibi

This comment has been minimized.

Show comment
Hide comment
@psibi

psibi Jul 20, 2015

Member

@snoyberg Thanks, I will attempt to patch this.

Member

psibi commented Jul 20, 2015

@snoyberg Thanks, I will attempt to patch this.

@psibi

This comment has been minimized.

Show comment
Hide comment
@psibi

psibi Jul 27, 2015

Member

@snoyberg and others, can you review the pull request #436 ?
The sample input and output using the mockMigrate function is demonstrated in this gist: https://gist.github.com/psibi/5ac89accf2e9c76f6d71

Member

psibi commented Jul 27, 2015

@snoyberg and others, can you review the pull request #436 ?
The sample input and output using the mockMigrate function is demonstrated in this gist: https://gist.github.com/psibi/5ac89accf2e9c76f6d71

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment