This is the correct way to set the pragma for each connection. However your
helper is not set up right. You are creating a new Mojo::SQLite object each
time it is called and so the on-connection handler is no longer applied.
You need to declare 'my $sqlite;' outside and only set it in the helper if
it's not defined yet.
my $sqlite;
helper sqlite => sub { $sqlite //= Mojo::SQLite->new... };
Alternatively you can use 'state $sqlite = ' inside the helper but this
approach breaks the ability to run multiple separate apps in the same
process, because it will make one $sqlite per whole process.
-Dan
On Wed, May 8, 2019 at 10:47 AM Luc Larochelle <[email protected]>
wrote:
> Hello everyone,
>
> I've been struggling with this one for a while, so I decided to post here.
>
> I built a crud web application to manage hosts configuration for a backend
> backup application.
>
> There is a parent table (Devices) , which relates to child tables by name
> (authName , promptName ...). I defined foreign keys constraint where delete
> of a child is restricted (if the child is used in the parent table).
>
> Problem is, foreign keys do not seem to be effective from the web app. If
> I open a connection to the db from the cli and enable PRAGMA foreign_keys =
> ON; before attempting a delete, I received the appropriate response
>
> sqlite> delete from Auths where authName = "myAuth";
> Error: foreign key constraint failed
>
> Even if it's being defined in my schema, it's not working.
>
> So I searched the forums for an answer. One good solution was to have the
> PRAGMA defined in ~/.sqliterc, but this is working only for the user.
>
> Finally someone said it had to be applied at each connection, so I added
> the call " on connection " to send the command every time, still no luck.
>
> Below is the relevant piece of code for discussion, can someone please
> point out what's wrong with my approach ?
>
>
>
>
> helper sqlite => sub {my $sql = Mojo::SQLite->new('sqlite:mynewdb2.db')};
>
>
> app->sqlite->on(connection => sub {
> my ($sql, $dbh) = @_;
> $dbh->do('PRAGMA foreign_keys = ON');
> });
>
>
>
> app->sqlite->auto_migrate(1)->migrations->name('ConfigMgt')->from_string(<<EOF
> -- 1 up
> PRAGMA foreign_keys = ON;
>
> create table if not exists Devices
> (
> hostname text ,
> ipaddress text ,
> groupName text ,
> authName text ,
> connectionName text ,
> promptName text ,
> ignoreStatus int default 0,
> dateAdd datetime ,
> dateMod datetime ,
> primary key (hostname, ipaddress),
> constraint deviceAuth foreign key (authName)references Auths(authName)
> on delete restrict on update no action ,
> constraint deviceScript foreign key (groupName)references
> Scripts(scriptName) on delete restrict on update no action ,
> constraint devicePrompt foreign key (promptName)references
> Prompts(promptName) on delete restrict on update no action ,
> constraint deviceConnection foreign key (connectionName)references
> Connections(connectionName) on delete restrict on update no action
> );
>
>
>
>
> --
> You received this message because you are subscribed to the Google Groups
> "Mojolicious" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to [email protected].
> To post to this group, send email to [email protected].
> Visit this group at https://groups.google.com/group/mojolicious.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/mojolicious/f8d536f2-4ea7-4447-9b8e-04cc639bb287%40googlegroups.com
> <https://groups.google.com/d/msgid/mojolicious/f8d536f2-4ea7-4447-9b8e-04cc639bb287%40googlegroups.com?utm_medium=email&utm_source=footer>
> .
> For more options, visit https://groups.google.com/d/optout.
>
--
You received this message because you are subscribed to the Google Groups
"Mojolicious" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/mojolicious.
To view this discussion on the web visit
https://groups.google.com/d/msgid/mojolicious/CABMkAVX2g1rX5vf9-QCrs-X0GH5h%3D-GNFHrJ1BpGp59KQsHnVg%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.
