Hello Dan, thank you so much. Now I guess I'll have to deal with error management but you already showed me the way in that other post :)
https://groups.google.com/forum/#!topic/mojolicious/Tfv8RK2UkrI Cheers ! On Wednesday, 8 May 2019 10:55:14 UTC-4, Dan Book wrote: > > 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] > <javascript:>> 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] <javascript:>. >> To post to this group, send email to [email protected] >> <javascript:>. >> 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/4255e2b4-1e39-4242-8bb7-0e7836ccc8d6%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.
