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.
For more options, visit https://groups.google.com/d/optout.
