Mojo::SQLite && foreign keys support ?

[Luc Larochelle]

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 

);

[Dan Book]

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

--
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 mojolicious...@googlegroups.com.
To post to this group, send email to mojol...@googlegroups.com.
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.

[Luc Larochelle]

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 !

To unsubscribe from this group and stop receiving emails from it, send an email to mojol...@googlegroups.com.

[Veesh Goldman]

On Wed, May 8, 2019, 5:55 PM Dan Book <gri...@gmail.com> wrote:

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

Could you expound on this problem? I thought state is just a shortcut to closing over a lexical variable. 

[Dan Book]

A lexical variable is still bound to a scope, even if it's the file scope. If that scope is run again (such as creating another instance of the same application) then that will have a separate instance of that lexical variable. State variables are initialized once and shared between any access for the rest of that process. It can't know what outer scope you might have wanted to store it in. It can still be useful but you have to make sure that's what you want.

-Dan

--

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 mojolicious...@googlegroups.com.
To post to this group, send email to mojol...@googlegroups.com.
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/CAO-W_8CVM9NnBMXuyvHi_FCT7p8%2B11FxTn6it9%3D%3D2JpUr78nfw%40mail.gmail.com.

[Илья Рассадин]

To clarify Dan's answer about state variables (mostly, for myself).

If you run separate apps in the same process like these.

my $app1 = Test::Mojo->new('MyApp')->app;

my $app2 = Test::Mojo->new('MyApp')->app;

# now $app1->sqlite and $app2->sqlite is the same state variable.

You got two apps in the same process and they share the same state variable.

To view this discussion on the web visit https://groups.google.com/d/msgid/mojolicious/CABMkAVUWJc-rYw4DhThw9YGcp7OdCh3d0djXRoPLpRkq-vESZg%40mail.gmail.com.

[Veesh Goldman]

Ah, I see.

I couldn't figure another case where you'd run two instances in the same process.

To view this discussion on the web visit https://groups.google.com/d/msgid/mojolicious/e9a40bd8-f099-b2b1-d578-41a22e2b7371%40gmail.com.