Accessing SQLite database from two different modules

[mimosinnet]

After writing a Mojolicious application in Mojo::SQLite, some transactions doesn't seem to be immediately committed to the database. I wonder if this is related to SQLite Write-Ahead Logging or how I have defined Mojo::SQLite (I am afraid it is the latter  :(  ). This is the logic I have followed:

--> As some modules need to access the database, I have defined a module that loads the database:

package GestioMaster::Obj::DB;

use Mojo::Base -base;

use Mojo::SQLite;

use GestioMaster::Model::DB;

has sqlite => sub { Mojo::SQLite->new('sqlite:db/master.db') };

has model  => sub {

my $self = shift;

return GestioMaster::Model::DB->new( sqlite =>  $self->sqlite );

};

--> This module is used when loading the application:

package GestioMaster;

use Mojo::Base 'Mojolicious';

use GestioMaster::Obj::DB;

sub startup {

  my $self = shift;

  my $config = $self->plugin('Config');

  $self->helper( model => sub { 

state $model = GestioMaster::Obj::DB->new->model;

   });

...

--> Accessing the database is defined in the Model: 

package GestioMaster::Model::DB;

use Mojo::Base -base;

has 'sqlite';

...

sub un_modul {

my ($self, $modul) = @_;

return $self->sqlite->db->select('moduls',undef,{ modul => $modul})->hash;

}

...

--> The same module is also used to define registers from the database:

package GestioMaster::Obj::Modul;

use Mojo::Base -base;

# Object that makes database available

use GestioMaster::Obj::DB;

has _model => sub { GestioMaster::Obj::DB->new->model  };

has modul => "";

has un_modul => sub { 

my $self = shift;

return $self->_model->un_modul( $self->modul );

};

has ident => sub { shift->un_modul->{'ident'} };

has coord => sub { shift->un_modul->{'coord'} };

has nom_modul => sub { shift->un_modul->{'nom_modul'}; };

...

--> This builds an array-ref of registers:

package GestioMaster::Controller::Moduls::Modul;

use Mojo::Base -base;

use GestioMaster::Obj::Modul;

has moduls => sub {

my $self = shift;

my @moduls = qw/M1 M2a M2b M3a M3b M3d M3e M4 M5a M5b M5c M5d M6/;

my $mod_obj = [];

foreach my $mod ( @moduls  ) {

push @{$mod_obj}, GestioMaster::Obj::Modul->new( modul => $mod );

}

return $mod_obj;

};

d) Used in the controller:

package GestioMaster::Controller::Public;

use GestioMaster::Controller::Moduls::Modul;

state $moduls = GestioMaster::Controller::Moduls::Modul->new->moduls;

sub inici  { shift->render( moduls => $moduls ) }

e) That promised straightforward templates: 

% foreach my $mod ( @{ $moduls } ) {

<li><a href="<%= '/pub/modul/' . $mod->modul %>"><%= $mod->modul . ": " . $mod->nom_modul %></a></li>

% }

So far so good, till the application started to write to the database and noticed some transactions were not immediately committed :O

My questions:

a) Opening the database in two different modules has an effect on how transactions are committed?

b) Could this be solved by "Application-Initiated Checkpoints"?

c) If the application has to be rewritten, how can the database defined in package GestioMaster be accessed from GestioMaster::Obj::Modul?

Thanks! 

[Dan Book]

Even with write-ahead logging, committing a transaction or running a data update outside a transaction should be immediately reflected in any other readers -- as long as they run the read query after the commit/update has completed. The ability to read while another connection is writing (and see the state unaffected by the transaction) is the primary purpose of WAL mode.

I don't see any issue with what you presented so far, but more important would be to see how you are running updates or transactions.

-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+unsubscribe@googlegroups.com.
To post to this group, send email to mojol...@googlegroups.com.
Visit this group at https://groups.google.com/group/mojolicious.
For more options, visit https://groups.google.com/d/optout.

[mimosinnet]

El dimecres, 25 abril de 2018 0:54:53 UTC+2, Dan Book va escriure:

Even with write-ahead logging, committing a transaction or running a data update outside a transaction should be immediately reflected in any other readers -- as long as they run the read query after the commit/update has completed. The ability to read while another connection is writing (and see the state unaffected by the transaction) is the primary purpose of WAL mode.

I don't see any issue with what you presented so far, but more important would be to see how you are running updates or transactions.

Thanks a lot! This helps me to focus the attention to other issues in order to debug this issue!

Cheers!

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