[Catalyst] Session::Store::DBIC and session table (postgres)
Hi, I'm having this issue with my catalyst app where the session table is not fully qualified in the generated SQL statement: [error] Caught exception in engine DBIx::Class::ResultSet::delete(): DBI Exception: DBD::Pg::st execute failed: ERROR: relation sessions does not exist [for Statement DELETE FROM sessions WHERE ( id = ? ) with ParamValues: 1='session:c7d07ff1ec06ebf2c6fc37c3cb8f36b117053384'] at /usr/local/lib/perl5/site_perl/5.8.9/Catalyst/Plugin/Session/Store/DBIC.pm line 207 The sessions table lives in my PostgreSQL database in a separate schema 'webapp'. So I would expect the statement to be: DELETE FROM webapp.sessions WHERE ( id = ? ) I also have the table name fully qualified in the corresponding DBIC schema class (GUS/Webapp/Sessions.pm): package GUS::Webapp::Sessions; use strict; use warnings; use base 'DBIx::Class'; __PACKAGE__-load_components(qw/Core PK::Auto/); __PACKAGE__-table(webapp.sessions); [...] And in my YAML configuration I have set the dbic_class accordingly: session: dbic_class: GUS::Webapp::Sessions cookie_name: tcsnp_development_cookie cookie_expires: 0 Is this a bug in Session::Store::DBIC? Anyone has an idea of why this is failing or how to fix it? So far all other session related functionality works as expected (storing and reading session data). And this same error goes away if I delete the corresponding cookie in my browser. Thanks in advance! -- fernan ___ List: Catalyst@lists.scsys.co.uk Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/ Dev site: http://dev.catalyst.perl.org/
[Catalyst] Catalyst-based SCADA Project
Hi there, We built a simple POC of what could be a pretty cool project for building a Web-based SCADA on top of Catalyst. I can gladly participate in the project but we have no funds to continue it's development internally. So would like to know if anyone here would like to participate/lead it's further development as an OpenSource project in a license TBD. What we got so far is a POC of the HMI that takes an SVG image made with InkScape and then uses a RESTful Web Service to fetch values from the DB and updates the image on the browser using the JQuery SVG Plugin. The POC is quite simple but impressive potential IMHO. You can post your interest here or contact me directly at work: aim...@yabarana.com Cheers, -- Alejandro Imass ___ List: Catalyst@lists.scsys.co.uk Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/ Dev site: http://dev.catalyst.perl.org/
Re: [Catalyst] Session::Store::DBIC and session table (postgres)
On Wed, Nov 14, 2012 at 7:45 AM, Fernan Aguero fernan.agu...@gmail.comwrote:
Hi,
I'm having this issue with my catalyst app where the session table is
not fully qualified in the generated SQL statement:
[...]
The sessions table lives in my PostgreSQL database in a separate
schema 'webapp'.
So I would expect the statement to be:
DELETE FROM webapp.sessions WHERE ( id = ? )
I also have the table name fully qualified in the corresponding DBIC
schema class (GUS/Webapp/Sessions.pm):
[...]
DBIC works well with schemas.
The first obvious question is permissions. Have you tried manually
connecting as the app user using pgsql and then selecting the table using
the fq name?
Pg requires grants on an object by object basis not like MySQL where you
can do something like grant all on *.db. You have to grant one by one to
the Catalyst DB user to the objects in the particular schema, unless of
course the user is the owner of the schema and in that case you don't even
need to fq the object names.
Also, if you are creating your Result classes with loader have you tried
{loader_options={db_schema='foo'}}
Best,
--
Alejandro Imass
___
List: Catalyst@lists.scsys.co.uk
Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/
Dev site: http://dev.catalyst.perl.org/
Re: [Catalyst] Session::Store::DBIC and session table (postgres)
On Wed, Nov 14, 2012 at 11:36 AM, Alejandro Imass alejandro.im...@gmail.com
wrote:
On Wed, Nov 14, 2012 at 7:45 AM, Fernan Aguero fernan.agu...@gmail.comwrote:
Hi,
I'm having this issue with my catalyst app where the session table is
not fully qualified in the generated SQL statement:
[...]
The sessions table lives in my PostgreSQL database in a separate
schema 'webapp'.
So I would expect the statement to be:
DELETE FROM webapp.sessions WHERE ( id = ? )
I also have the table name fully qualified in the corresponding DBIC
schema class (GUS/Webapp/Sessions.pm):
[...]
DBIC works well with schemas.
I know. The app works so far OK with all schemas in the DB. The only one
giving problems is the sessions table.
The first obvious question is permissions. Have you tried manually
connecting as the app user using pgsql and then selecting the table using
the fq name?
Of course, I can reproduce succesfully what DBIC is trying to do on the Pg
terminal. E.g. for the following error:
[error] Scheduler: Error executing /cron/remove_sessions:
DBIx::Class::ResultSet::delete(): DBI Exception: DBD::Pg::st execute
failed: ERROR: relation sessions does not exist [for Statement DELETE
FROM sessions WHERE ( id = ? ) with ParamValues:
1='session:c7d07ff1ec06ebf2c6fc37c3cb8f36b117053384']
I can fix the query and run it (using the same userid/credentials of the
catalyst app) without issues:
tcsnp3= DELETE FROM webapp.sessions WHERE ( id =
'session:c7d07ff1ec06ebf2c6fc37c3cb8f36b117053384' );
DELETE 1
Pg requires grants on an object by object basis not like MySQL where you
can do something like grant all on *.db. You have to grant one by one to
the Catalyst DB user to the objects in the particular schema, unless of
course the user is the owner of the schema and in that case you don't even
need to fq the object names.
The owner of all schemas is 'dba'.
The catalyst user has 'arwdxt' privileges on the webapp schema (this schema
contains the sessions and users tables). Essentially it has all privileges
except TRUNCATE:
http://www.postgresql.org/docs/8.4/static/sql-grant.html
Also, if you are creating your Result classes with loader have you tried
{loader_options={db_schema='foo'}}
I am creating the Result classes in this way for larger schemas. However,
for this particular schema that isolates the catalyst-specific tables, I've
manually created the classes (there are only two tables), and I've checked
that it follows the same convention as those created by loader, e.g.
__PACKAGE__-table(schemaname.tablename);
Best,
--
Alejandro Imass
Thanks Alejandro for the quick response. I still think that this is
exposing a bug in Session::Store::DBIC or Session::Store::Delegate. They
should use whatever qualified name is given in the DBIC class ... but I'm
not sure where to go next ...
--
fernan
___
List: Catalyst@lists.scsys.co.uk
Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/
Dev site: http://dev.catalyst.perl.org/
Re: [Catalyst] Session::Store::DBIC and session table (postgres)
On Wed, Nov 14, 2012 at 10:07 AM, Fernan Aguero fernan.agu...@gmail.comwrote: [...] Thanks Alejandro for the quick response. I still think that this is exposing a bug in Session::Store::DBIC or Session::Store::Delegate. They should use whatever qualified name is given in the DBIC class ... but I'm not sure where to go next ... Could be. I've used schemas with Pg and DBIC with Cat but usually just to a single one being the app user the same owner, so the are no fq names in my Result files. Maybe as you say it's specific to the Session Store. Good luck. maybe look at source and contact the module's author directly? -- Alejandro Imass ___ List: Catalyst@lists.scsys.co.uk Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/ Dev site: http://dev.catalyst.perl.org/
Re: [Catalyst] Session::Store::DBIC and session table (postgres)
Did you use the dbicdump script (or myapp_create.pl model) command to create the schema files? I would suggest you look to see how the 'sessions' table was created and use that as the name for the relation. regards On Nov 14, 2012, at 7:07 AM, Fernan Aguero fernan.agu...@gmail.com wrote: Of course, I can reproduce succesfully what DBIC is trying to do on the Pg terminal. E.g. for the following error: [error] Scheduler: Error executing /cron/remove_sessions: DBIx::Class::ResultSet::delete(): DBI Exception: DBD::Pg::st execute failed: ERROR: relation sessions does not exist [for Statement DELETE FROM sessions WHERE ( id = ? ) with ParamValues: 1='session:c7d07ff1ec06ebf2c6fc37c3cb8f36b117053384'] I can fix the query and run it (using the same userid/credentials of the catalyst app) without issues: tcsnp3= DELETE FROM webapp.sessions WHERE ( id = 'session:c7d07ff1ec06ebf2c6fc37c3cb8f36b117053384' ); DELETE 1 Francisco Obispo Director of Applications and Services - ISC email: fobi...@isc.org Phone: +1 650 423 1374 || INOC-DBA *3557* NOC PGP KeyID = B38DB1BE ___ List: Catalyst@lists.scsys.co.uk Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/ Dev site: http://dev.catalyst.perl.org/
Re: [Catalyst] Session::Store::DBIC and session table (postgres)
On Wed, Nov 14, 2012 at 3:09 PM, Francisco Obispo fobi...@isc.org wrote: Did you use the dbicdump script (or myapp_create.pl model) command to create the schema files? I would suggest you look to see how the 'sessions' table was created and use that as the name for the relation. regards I did not use the dbicdump script originally (these were tables from an old app). I generated all schema classes using perl -MDBIx::Class::Schema::Loader=dump_to_dir:lib/ -Mschemaname -e1 where schemaname was a DBIx::Class::Schema::Loader class specifying connection and loader options (e.g. schema). However, I've now tried the newer dbicdump script, and still get the same error: [debug] Scheduler: Executing /cron/remove_sessions [error] Scheduler: Error executing /cron/remove_sessions: DBIx::Class::ResultSet::delete(): DBI Exception: DBD::Pg::st execute failed: ERROR: relation sessions does not exist [for Statement DELETE FROM sessions WHERE ( expires ? ) with ParamValues: 1='1352925249'] at /home/fernan/perl5/lib/perl5/Catalyst/Plugin/Session/Store/DBIC.pm The new classes are slightly different: lib/GUS/Webapp/Sessions.pm (old) lib/GUS/Result/WebappSession.pm (new) but still they both call __PACKAGE__-table(webapp.sessions); and function with no issues for creating new sessions in the Pg storage, updating the session_data, etc. They're only failing at the DELETE statement, and from the error it is clear that they're failing to fully qualify the table name. I'm now CCing FLORA to see if he's aware of this behaviour and/or can help see if it falls into the Session::Store::DBIC realm ... Thanks again for all responses! -- fernan ___ List: Catalyst@lists.scsys.co.uk Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/ Dev site: http://dev.catalyst.perl.org/
Re: [Catalyst] Session::Store::DBIC and session table (postgres)
Use:
$c-model('GUS::WebappSession') as the name.
or in the config:
Plugin::Session
dbic_class GUS::WebappSession
id_field id
storage_field session_data
expires_field expires
need_commit 0
expires 3600
verify_address 1
/Plugin::Session
francisco
On Nov 14, 2012, at 12:51 PM, Fernan Aguero fernan.agu...@gmail.com wrote:
The new classes are slightly different:
lib/GUS/Webapp/Sessions.pm (old)
lib/GUS/Result/WebappSession.pm (new)
Francisco Obispo
Director of Applications and Services - ISC
email: fobi...@isc.org
Phone: +1 650 423 1374 || INOC-DBA *3557* NOC
PGP KeyID = B38DB1BE
___
List: Catalyst@lists.scsys.co.uk
Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/
Dev site: http://dev.catalyst.perl.org/
