Yesod, Sqlite, Enabling Foreign Key Support

Anton Cheshkov

26 Apr 2011 26 Apr '11

6:31 a.m.

Hi Michael. Please show the way to enable foreign key support from sqlite connection. Now foreign key constrints does not work for sqlite from Yesod. Thanks. http://www.sqlite.org/foreignkeys.html -- Best regards, Cheshkov Anton Phone: +7 909 005 18 82 Skype: cheshkov_anton

Attachments:

attachment.html (text/html — 553 bytes)

Show replies by date

Anton Cheshkov

26 Apr 26 Apr

8:58 a.m.

I have pushed branch to fix it. probably it should be helpful. https://github.com/acheshkov/persistent https://github.com/acheshkov/persistentThanks. 2011/4/26 Anton Cheshkov

...

Hi Michael.

Please show the way to enable foreign key support from sqlite connection.

Now foreign key constrints does not work for sqlite from Yesod.

Thanks.

http://www.sqlite.org/foreignkeys.html

-- Best regards, Cheshkov Anton Phone: +7 909 005 18 82 Skype: cheshkov_anton

-- Best regards, Cheshkov Anton Phone: +7 909 005 18 82 Skype: cheshkov_anton

Greg Weber

11:08 a.m.

Thanks! Please send a pull request to Michael. On Tue, Apr 26, 2011 at 5:58 AM, Anton Cheshkov wrote:

...

I have pushed branch to fix it. probably it should be helpful. https://github.com/acheshkov/persistent

https://github.com/acheshkov/persistentThanks.

2011/4/26 Anton Cheshkov

...
Hi Michael.

Please show the way to enable foreign key support from sqlite connection.

Now foreign key constrints does not work for sqlite from Yesod.

Thanks.

http://www.sqlite.org/foreignkeys.html

-- Best regards, Cheshkov Anton Phone: +7 909 005 18 82 Skype: cheshkov_anton

-- Best regards, Cheshkov Anton Phone: +7 909 005 18 82 Skype: cheshkov_anton

_______________________________________________ web-devel mailing list web-devel@haskell.org http://www.haskell.org/mailman/listinfo/web-devel

Anton Cheshkov

11:47 a.m.

Ok. I recompiled persistent with new changes. All works how i want and expect. But now *insertBy* function are not so safe as earlier. *The insertBy function* checks only unique constrains defined on yesod framework level via Unique... constructions . Now *insertBy* function throws exception if we try to insert 'bad' value to column with foreign constraint. Please show me how to catch exception. I think it would something like: * * *import qualified Control.Exception.Control as E* *...* *do* * res <- runDB $ E.catch (insertBy offer) (\_ -> return Left ())* But my exception handler is wrong. Thanks. 2011/4/26 Greg Weber

...

Thanks!

Please send a pull request to Michael.

On Tue, Apr 26, 2011 at 5:58 AM, Anton Cheshkov wrote:

...
I have pushed branch to fix it. probably it should be helpful. https://github.com/acheshkov/persistent

https://github.com/acheshkov/persistentThanks.

2011/4/26 Anton Cheshkov

...
Hi Michael.

Please show the way to enable foreign key support from sqlite connection.

Now foreign key constrints does not work for sqlite from Yesod.

Thanks.

http://www.sqlite.org/foreignkeys.html

-- Best regards, Cheshkov Anton Phone: +7 909 005 18 82 Skype: cheshkov_anton

-- Best regards, Cheshkov Anton Phone: +7 909 005 18 82 Skype: cheshkov_anton

_______________________________________________ web-devel mailing list web-devel@haskell.org http://www.haskell.org/mailman/listinfo/web-devel

-- Best regards, Cheshkov Anton Phone: +7 909 005 18 82 Skype: cheshkov_anton

Anton Cheshkov

noon

I can not catch exception *user error (SQLite3 returned ErrorConstraint while attempting to perform step.*) * * I do it as: *res <- runDB $ E.catch (insertBy offer) (\(e :: E.SomeException) -> return $ Left undefined) * 2011/4/26 Anton Cheshkov

...

Ok.

I recompiled persistent with new changes. All works how i want and expect. But now *insertBy* function are not so safe as earlier.

*The insertBy function* checks only unique constrains defined on yesod framework level via Unique... constructions . Now *insertBy* function throws exception if we try to insert 'bad' value to column with foreign constraint.

Please show me how to catch exception. I think it would something like: * * *import qualified Control.Exception.Control as E* *...* *do* * res <- runDB $ E.catch (insertBy offer) (\_ -> return Left ())*

But my exception handler is wrong.

Thanks.

2011/4/26 Greg Weber

...
Thanks!

Please send a pull request to Michael.

On Tue, Apr 26, 2011 at 5:58 AM, Anton Cheshkov wrote:

...
I have pushed branch to fix it. probably it should be helpful. https://github.com/acheshkov/persistent

https://github.com/acheshkov/persistentThanks.

2011/4/26 Anton Cheshkov

...
Hi Michael.

Please show the way to enable foreign key support from sqlite connection.

Now foreign key constrints does not work for sqlite from Yesod.

Thanks.

http://www.sqlite.org/foreignkeys.html

-- Best regards, Cheshkov Anton Phone: +7 909 005 18 82 Skype: cheshkov_anton

-- Best regards, Cheshkov Anton Phone: +7 909 005 18 82 Skype: cheshkov_anton

_______________________________________________ web-devel mailing list web-devel@haskell.org http://www.haskell.org/mailman/listinfo/web-devel

-- Best regards, Cheshkov Anton Phone: +7 909 005 18 82 Skype: cheshkov_anton

-- Best regards, Cheshkov Anton Phone: +7 909 005 18 82 Skype: cheshkov_anton

Anton Cheshkov

27 Apr 27 Apr

1:08 a.m.

I do not understand what have happened . But today all works ;) * * *res <- runDB $ E.catch (insertBy offer) (\(e :: E.SomeException) -> return $ Left undefined)* * * But sometimes i see in console follow: * * *Attempting to interpret your app...* *wai-handler-devel: user error (SQLite3 returned ErrorBusy while attempting to perform close: unable to close due to unfinalised statements)* * * 2011/4/26 Anton Cheshkov

...

I can not catch exception *user error (SQLite3 returned ErrorConstraint while attempting to perform step.*) * * I do it as: *res <- runDB $ E.catch (insertBy offer) (\(e :: E.SomeException) -> return $ Left undefined) *

2011/4/26 Anton Cheshkov

...
Ok.

I recompiled persistent with new changes. All works how i want and expect. But now *insertBy* function are not so safe as earlier.

*The insertBy function* checks only unique constrains defined on yesod framework level via Unique... constructions . Now *insertBy* function throws exception if we try to insert 'bad' value to column with foreign constraint.

Please show me how to catch exception. I think it would something like: * * *import qualified Control.Exception.Control as E* *...* *do* * res <- runDB $ E.catch (insertBy offer) (\_ -> return Left ())*

But my exception handler is wrong.

Thanks.

2011/4/26 Greg Weber

...
Thanks!

Please send a pull request to Michael.

On Tue, Apr 26, 2011 at 5:58 AM, Anton Cheshkov wrote:

...
I have pushed branch to fix it. probably it should be helpful. https://github.com/acheshkov/persistent

https://github.com/acheshkov/persistentThanks.

2011/4/26 Anton Cheshkov

...
Hi Michael.

Please show the way to enable foreign key support from sqlite connection.

Now foreign key constrints does not work for sqlite from Yesod.

Thanks.

http://www.sqlite.org/foreignkeys.html

-- Best regards, Cheshkov Anton Phone: +7 909 005 18 82 Skype: cheshkov_anton

-- Best regards, Cheshkov Anton Phone: +7 909 005 18 82 Skype: cheshkov_anton

_______________________________________________ web-devel mailing list web-devel@haskell.org http://www.haskell.org/mailman/listinfo/web-devel

-- Best regards, Cheshkov Anton Phone: +7 909 005 18 82 Skype: cheshkov_anton

-- Best regards, Cheshkov Anton Phone: +7 909 005 18 82 Skype: cheshkov_anton

-- Best regards, Cheshkov Anton Phone: +7 909 005 18 82 Skype: cheshkov_anton

Michael Snoyman

1:11 a.m.

I'm not sure we want to enable this by default. What about just providing a helper function that enables the pragma? There's already enough available from the public API to execute arbitrary SQL statements. I don't use SQLite for production deployments personally, so if others have an opinion on this, I'd like to hear it. Michael On Tue, Apr 26, 2011 at 1:31 PM, Anton Cheshkov wrote:

...

Hi Michael. Please show the way to enable foreign key support from sqlite connection. Now foreign key constrints does not work for sqlite from Yesod. Thanks.

http://www.sqlite.org/foreignkeys.html -- Best regards, Cheshkov Anton Phone: +7 909 005 18 82 Skype: cheshkov_anton

Anton Cheshkov

2:12 a.m.

I think helper function is good variant. I wanted to make it on yesod level but i do not see hackage documentation for newly packages. When documentation will refreshed i can try to make 'foreign enabling' on framework level ;) Thanks. 2011/4/27 Michael Snoyman

...

I'm not sure we want to enable this by default. What about just providing a helper function that enables the pragma? There's already enough available from the public API to execute arbitrary SQL statements. I don't use SQLite for production deployments personally, so if others have an opinion on this, I'd like to hear it.

Michael

On Tue, Apr 26, 2011 at 1:31 PM, Anton Cheshkov wrote:

...
Hi Michael. Please show the way to enable foreign key support from sqlite connection. Now foreign key constrints does not work for sqlite from Yesod. Thanks.

http://www.sqlite.org/foreignkeys.html -- Best regards, Cheshkov Anton Phone: +7 909 005 18 82 Skype: cheshkov_anton

-- Best regards, Cheshkov Anton Phone: +7 909 005 18 82 Skype: cheshkov_anton

Michael Snoyman

2:14 a.m.

Looks like the docs just got generated, at least for persistent. You'll likely want the execute function from Data.Persist.GenericSql.Raw: http://hackage.haskell.org/packages/archive/persistent/0.5.0/doc/html/Databa... . Michael On Wed, Apr 27, 2011 at 9:12 AM, Anton Cheshkov wrote:

...

I think helper function is good variant. I wanted to make it on yesod level but i do not see hackage documentation for newly packages. When documentation will refreshed i can try to make 'foreign enabling' on framework level ;) Thanks.

2011/4/27 Michael Snoyman

...
I'm not sure we want to enable this by default. What about just providing a helper function that enables the pragma? There's already enough available from the public API to execute arbitrary SQL statements. I don't use SQLite for production deployments personally, so if others have an opinion on this, I'd like to hear it.

Michael

On Tue, Apr 26, 2011 at 1:31 PM, Anton Cheshkov wrote:

...
Hi Michael. Please show the way to enable foreign key support from sqlite connection. Now foreign key constrints does not work for sqlite from Yesod. Thanks.

http://www.sqlite.org/foreignkeys.html -- Best regards, Cheshkov Anton Phone: +7 909 005 18 82 Skype: cheshkov_anton

-- Best regards, Cheshkov Anton Phone: +7 909 005 18 82 Skype: cheshkov_anton

Anton Cheshkov

5:38 a.m.

I trying this *helperSqliteForeignON :: MonadIO m => SqlPersist m ()* *helperSqliteForeignON = execute "PRAGMA foreign_keys = ON" []* * * *-- How to run database actions.* *instance YesodPersist Pomoguy where* * type YesodDB Pomoguy = SqlPersist* * runDB db = liftIOHandler* * $ fmap connPool getYesod >>= Settings.runConnectionPool (helperSqliteForeignON >> db)* * * But it does not work. I do not understand why What is the second argument for *'execute'* function ? Thanks. 2011/4/27 Michael Snoyman

...

Looks like the docs just got generated, at least for persistent. You'll likely want the execute function from Data.Persist.GenericSql.Raw:

http://hackage.haskell.org/packages/archive/persistent/0.5.0/doc/html/Databa... .

Michael

On Wed, Apr 27, 2011 at 9:12 AM, Anton Cheshkov wrote:

...
I think helper function is good variant. I wanted to make it on yesod level but i do not see hackage documentation for newly packages. When documentation will refreshed i can try to make 'foreign enabling' on framework level ;) Thanks.

2011/4/27 Michael Snoyman

...
I'm not sure we want to enable this by default. What about just providing a helper function that enables the pragma? There's already enough available from the public API to execute arbitrary SQL statements. I don't use SQLite for production deployments personally, so if others have an opinion on this, I'd like to hear it.

Michael

On Tue, Apr 26, 2011 at 1:31 PM, Anton Cheshkov wrote:

...
Hi Michael. Please show the way to enable foreign key support from sqlite connection. Now foreign key constrints does not work for sqlite from Yesod. Thanks.

http://www.sqlite.org/foreignkeys.html -- Best regards, Cheshkov Anton Phone: +7 909 005 18 82 Skype: cheshkov_anton

-- Best regards, Cheshkov Anton Phone: +7 909 005 18 82 Skype: cheshkov_anton

-- Best regards, Cheshkov Anton Phone: +7 909 005 18 82 Skype: cheshkov_anton

Michael Snoyman

5:46 a.m.

The second argument is the list of replacements for question marks. Passing in an empty list is the correct thing to do here. Can you clarify what you mean by not working? Is it a compile time error, a runtime exception, or everything goes along just fine but foreign keys are not really enabled? Michael On Wed, Apr 27, 2011 at 12:38 PM, Anton Cheshkov wrote:

...

I trying this helperSqliteForeignON :: MonadIO m => SqlPersist m () helperSqliteForeignON = execute "PRAGMA foreign_keys = ON" [] -- How to run database actions. instance YesodPersist Pomoguy where type YesodDB Pomoguy = SqlPersist runDB db = liftIOHandler $ fmap connPool getYesod >>= Settings.runConnectionPool (helperSqliteForeignON >> db) But it does not work. I do not understand why What is the second argument for 'execute' function ? Thanks. 2011/4/27 Michael Snoyman

...
Looks like the docs just got generated, at least for persistent. You'll likely want the execute function from Data.Persist.GenericSql.Raw:

http://hackage.haskell.org/packages/archive/persistent/0.5.0/doc/html/Databa... .

Michael

On Wed, Apr 27, 2011 at 9:12 AM, Anton Cheshkov wrote:

...
I think helper function is good variant. I wanted to make it on yesod level but i do not see hackage documentation for newly packages. When documentation will refreshed i can try to make 'foreign enabling' on framework level ;) Thanks.

2011/4/27 Michael Snoyman

...
I'm not sure we want to enable this by default. What about just providing a helper function that enables the pragma? There's already enough available from the public API to execute arbitrary SQL statements. I don't use SQLite for production deployments personally, so if others have an opinion on this, I'd like to hear it.

Michael

On Tue, Apr 26, 2011 at 1:31 PM, Anton Cheshkov wrote:

...
Hi Michael. Please show the way to enable foreign key support from sqlite connection. Now foreign key constrints does not work for sqlite from Yesod. Thanks.

http://www.sqlite.org/foreignkeys.html -- Best regards, Cheshkov Anton Phone: +7 909 005 18 82 Skype: cheshkov_anton

-- Best regards, Cheshkov Anton Phone: +7 909 005 18 82 Skype: cheshkov_anton

-- Best regards, Cheshkov Anton Phone: +7 909 005 18 82 Skype: cheshkov_anton

5137

Age (days ago)

5138

Last active (days ago)

List overview

Download

10 comments

3 participants

participants (3)

Anton Cheshkov
Greg Weber
Michael Snoyman

Yesod, Sqlite, Enabling Foreign Key Support

tags

participants (3)