FAQ

On 30/10/2012, at 11:00 PM, Craig Chant wrote:


"What was the reason for not using DBIC again?"

The non-normalised DB with a missing schema and the fact the data is spread across two SQL servers on separate DSN's.

Be sure to use DBIx::Connector for connection management (https://metacpan.org/search?qÛIx%3A%3AConnector), thus you get one of the compelling DBIC features but still get to use DBI.

I also want my data in a way I can manipulate it, maybe you are all going to fall down in shock and horror, but I get my records and pass them back as an array of hashes (recordset), I then play with them , manipulate them, and do all sorts of stuff with them.

ORM / DBIC doesn't seem to give you this type of access, it wants a DB schema with relationship mapping to dynamically create all the 1-many / many - many etc Models and relationships, which won't work with our system, from what I can tell.

This is my SQL helper class.... (well two methods as an example)

# Get SQL Routine
sub getSQL {

#_0 = Self
#_1 = Table
#_2 = Columns
#_3 = Where
#_4 = Order By

my ($self,$table,$columns,$where,$order) = @_;

# Build SQL Statement
my $sel = "SELECT $columns FROM $table WHERE $where";

# Check for ORDER BY
if(defined $order){$sel .= " ORDER BY $order";}

# Connect
my $dbh = $self->dbh;

# set long read because SQL requires it for ODBC
$dbh->{LongReadLen} = 99999;

# Run SQL Command
my $sth = $db->prepare("$sel") || $self->sql_error("Error in getSQL (Web Server): $sel");
$sth->execute();

# Declare recordset array
my @rs;

# Loop SQL & build recordset
while (my $ref = $sth->fetchrow_hashref()) {
# Build Array of Hashes with SQL Data
$rs[@rs] = \%$ref;
}

# Return record set
@rs;

}

# Count SQL Routine
sub cntSQL {

#_0 = Self
#_1 = Table
#_2 = Where

my ($self,$table,$where) = @_;

#Build SQL Statement
my $sel = "SELECT COUNT(1) as COUNT FROM $table WHERE $where";

# Connect
my $dbh = $self->dbh;

# Run SQL Command
my $sth = $dbh->prepare("$sel") || $self->sql_error("Error in getSQL (Web Server): $sel");
$sth->execute();

# Loop SQL Record Set
while (my $ref = $sth->fetchrow_hashref()) {
# Build Array of Hashes with SQL Data
$rs[@rs] = \%$ref;
}

# Return Count
$rs[0]->{'COUNT'};

}

It's just before the return of the record set or count I was wondering if I need to add '$sth->finish();' or '$dbh->disconnect();' - which I have in my current (non-catalyst) app version of the class (module).

I also believe that DBIC gets all columns from all tables, which I don't want, dunno, perhaps I'm missing something with DBIC, but I understand my data the way I retrieve it and didn't think there was anything wrong with using my SQL class, it has served me well for 10 years, and powers all my current apps.

How would I use DBIC to get records from two separate DSN's and merge recordsets?

In my concrete Catalyst::Model I have...

# Check if system locked
if($self->cntSQL('IsLock',"Locked='yes'")){
$c->response->body('<p>Sorry, system is currently undergoing maintenance.</p><p>Please try again later.</p>' );
return 1;
}

One thing I have found already is the app doesn't seem to see real time SQL updates even if I issue $sth->finish(); & $dbh->disconnect(); at the end of my method.

I make a manual change to SQL (switch the 'Locked' flag between 'yes' & 'no') , refresh the app and it isn't registering the SQL change, so already it seems something is being cached somewhere and I need to stop this, my apps need to see DB changes instantly.

Your advice is appreciated.

Craig.

-----Original Message-----
From: Rob Brown
Sent: 29 October 2012 22:30
To: catalyst@lists.scsys.co.uk
Subject: Re: [Catalyst] Unable to output anything in Root.pm -> 'auto'

basically...

$sth->finish if you've finished with the results of that statement, ie, you've looped through the rows and are now done.

$dbh->disconnect if you've finished with the database connection, tho now you start to think about working in a persistent environment, where you may never disconnect from the database, and/or have some connection caching setup.

This is where DBIx::Class just takes of all this for you - it does sound like you're re-inventing a lot here.

What was the reason for not using DBIC again?



On 10/29/2012 10:10 PM, Craig Chant wrote:
I finally got to grips with extending my own class with the inbuilt $c->dbh.

But am unsure whether I am mean to issue either...

$sth->finish();

or

$dbh->disconnect();

Once I have prepared / executed the SQL and fetched the records I want.

so a little further guidance is appreciated.

Craig

________________________________________
From: Lukas Thiemeier [spamcatcher@thiemeier.net]
Sent: 29 October 2012 20:16
To: catalyst@lists.scsys.co.uk
Subject: Re: [Catalyst] Unable to output anything in Root.pm -> 'auto'

Hi Craig,

Using C::M::DBI is straight forward. Install it from cpan, and run

script/yourapp.pl create model DBI DBI dsn user password

where "dsn" is sth like "dbi:mysql:dbname", depending on your backend.

If it doesn't work you most likely have some typo in your dsn,
username or password.

When this doesn't work, what error is printed?
What was your input (except passwd, of course)?

Something like $c->dbh doesn't exist. The database handler belongs to
the model, and you can create Cat applications with several models, or
without any model at all. (Or with a model which is not a
database-model, or, or ...)

$c->model('DBI')->dbh does the trick.

The name "DBI" is required by C::A::Store::DBI, but besides this, any
model name is fine.

Alternatively, add a shortcut to your main App.pm:

sub dbh{ shift->model("DBI")->dbh }

Now, $c->dbh is available.

Session timeouts et cetera are configurable in the session plugin.
The session keeps track of when it was created, and when it was updated.

Setting extra fields (in the session or in the database), which are
not automatically set by the authentication plugin, can easily be done
by you, even if you use a authentication plugin. You can do this in
the "auto" action, for example.

I can only suggest you to search for existing modules, and use them
whenever possible. Thanks to Moose, extending a catalyst module is
easy in most cases. And it is much less work than writing everything
from scratch.

In my opinion, the biggest benefit of catalyst is (besides its great
backwards compatibility), that it is very modular, dozens of modules
exist, and most modules are extendable and reusable.

If you try to write all your code by yourself, you will not get happy.

Another suggestion: Maybe you should first forget about your real
applicationfor now, and just work through the catalyst tutorial. You
can finish it in some hours (depending on your perl- and web knowledge).
It will give you a great overview about how Catalyst works, what is
possible (almost everything) and what isn't possible (almost nothing)
:)

Or maybe you don't need a monster like catalyst? "Dancer" might be an
option.

Dancer is a perl MVC Framework, too. It is much smaller and simpler
than Catalyst, has less dependencies and less modules and (at least I
was told so) is easier to learn. It does not do as much for you as
catalyst does (in most cases, you have to write more code). But if you
have to reinvent the wheel again and again anyway, it might be a better choice.

Don't get me wrong. I don't suggest you to let catalyst go. Catalyst
rocks! But if most of the benefits are really not an option for you,
you should think about alternatives.


And finally: Don't make unpaid overtime: Go and get a beer! NOW!
Catalyst will be more fun tomorrow!


Ooops, I am writing this while making unpaid overtime. I will go and
get a beer :)

bye, Lukas



On 10/29/2012 08:25 PM, Craig Chant wrote:
Hi Lukas,

I tried to use C::Model::DBI , but I cannot get it to work?

I've ended up refactoring my own SQL module from the exisitng app as
OOP and then as an extension of Catayst::Model

I then create my own Model which extends my SQL module and I've got it working.

No matter what I tried , I couldn't find $c->dbh , I really seem to be struggling at the moment getting my head round Catalyst, but I am persevering!

I was hoping to use the built in DBI functionality, as it is meant to
keep database handles / connections open etc..but $c->dbh didn't
exits in my Model when I created it via myapp_create.pl MODEL DBI

I also appreciate there is a lot of stuff already written such as the authentication mechanism, but will that update my backend DB with the current location of the user?

Does it have a mechanism to enable 'heartbeat' to keep idle sessions open?

Will it keep up-to-date the date / time field so we can see at a glance the length of time a user has been logged on via our admin system?

Or does it simply keep a state of whether the user is loged in or not?

I also know that I should look at ORM and DBIC, but one thing at a time, Rome wasn't built in a day, and playing with Catalyst is simply something I want to do, not a requirement of my Job.

I start a 10 month SQL course in January, where I'm sure I will learn about DB's in far more detail and can then consider ORM / DBIC.

There is only so many hours in the day, then there are only so many I am paid to work and then there is only so much information I can absorb in one go!

I'm already sitting here doing unpaid overtime, but I'm commited to getting Catalyst working, so don't mind putting in the extra hours, sounds sad I know, but I am enjoying it, even if I seem frustrated at times.

Thank for all the input.

Craig.

________________________________________
From: Lukas Thiemeier [spamcatcher@thiemeier.net]
Sent: 29 October 2012 19:03
To: catalyst@lists.scsys.co.uk
Subject: Re: [Catalyst] Unable to output anything in Root.pm -> 'auto'

Hi Craig,

You are NOT wrong. Catalyst allows you to do so. I think it is the
right choice.

For C::A::Store::DBI - What you need is to provide the user_table,
user_key and user_name, and the password_field and password_type for
the credential part. I guess you have this, haven't you?

All the "role" stuff is optional and only required if you want to use
Catalyst::Plugin::Authorization::Roles (which you don't want if I got
you right).

I would suggest you to use as much existing code as possible, and
only rewrite what you really need.

In your case:
Use C::A::Store::DBI for authentication and C::Model::DBI to access
your database.
If you do so, you only have to write your own authorization code
(roles
etc) and your CRUD stuff. You can access your db using DBI, without
ORM or any assumptions to the db layout.

If you can not use C::A::Store::DBI for any reasons, I would still
recommend you to either write your own
Catalyst::Authentication::Store and/or
Catalyst::Authentication::Credential modules (I guess a Store-module
will do the trick). You will not have to deal with user-sessions and
related stuff. Just tell Catalyst how to authenticate the user, and let catalyst itself deal with the session.

Catalyst::Plugin::Authentication::Internals tells you how to write
your own store and credential modules. You will have to read the docs
first, but I am sure that this is less work than writing ALL your
authentication, session handling and authorization code by yourself.

When it comes to reusability, Catalyst is unbeatable :)

If you want or need to write your own authentication code in your
controller classes, you should still use $c->session directly. Don't
fiddle with the session id. Doing so is error-prone, and not required.
You can do it like this:

unless(defined $c->session->{user}){;
my $user = your_auth_code(\%data);
$c->session(user => $user);
}

You can later access it in any controller by saying:

my $user = $c->session->{user}

You can even make a shortcut in your App.pm:

sub is_authenticated{ defined( shift->session->{user}) }

And later check if the user is authenticated like this:

if($c->is_authenticated){
do_some_privileged_stuff();
}


You should consider using DBIx::Class anyway. It doesn't require
normalized databases. Automated model generation might not work
correctly, but in general you can use it on any database. DBIx::Class
is well documented, easy to learn, and it makes database access
simple and safe. Without ORM, you will most likely have to write 10
times more database-code, and you will have to double check it to
ensure that you are not vulnerable to sql injections.

You are not forced to use DBIC relationships et cetera. You can just
use it to update your tables, and only use the rels where you have
them in your db layout.

In my opinion, the reasons not to use DBIC are:

1: it takes some time to install, but you only have to do it once.

2: it slows down the startup time for your application, but unless
you are using plain CGI, this doesn't really matter. (If you use
plain-old-CGI, STOP doing so. Use FastCGI instead.)

3: It sometimes generates more SQL statements than it is required to
fulfill a certain task, but this is only relevant if you are running
a high performance, high traffic site. And IF this is the case, you
can still optimize it.

If you compare it to the benefits I described above, the benefits are
dominant in most cases.

I know that this is not the universal truth (which doesn't exist
anyway). It is my personal opinion. Just think about it.

Additionally: DBIC makes moving from one database system to another
very very easy. You have a SQLite DB, and want to move to Portgresql?
no problem. With DBIC, you are already done :)

Ok. I hope I could help.

Sorry for the DBIC-praising at the end. It is just that I first
didn't want to use DBIC, too. And now I see how much easier my life
is with DBIC, and I think I should have moved to DBIC earlier.

Lukas






On 10/29/2012 06:00 PM, Craig Chant wrote:
Hi Luka,

Perhaps I miss-read the info on
http://search.cpan.org/~janus/Catalyst-Authentication-Store-DBI-0.01
/lib/Catalyst/Authentication/Store/DBI.pm

But from what I can see it expects you to map specific fields in a table as well as have a user role table with specific data mapping?

[quote] __PACKAGE__->config->{'authentication'} = {
'default_realm' => 'default',
'realms' => {
'default' => {
'credential' => {
'class' => 'Password',
'password_field' => 'password',
'password_type' => 'hashed',
'password_hash_type' => 'SHA-1',
},
'store' => {
'class' => 'DBI',
'user_table' => 'login',
'user_key' => 'id',
'user_name' => 'name',
'role_table' => 'authority',
'role_key' => 'id',
'role_name' => 'name',
'user_role_table' => 'competence',
'user_role_user_key' => 'login',
'user_role_role_key' => 'authority',
},
},
},
};[/quote]

Have I read the above incorrectly?

I have a non-normalised DB , with an application that functions in a particular way, I deal with user roles and other such stuff in my own way and I cannot refactor to use catalyst without ensuring all sections of the system function the same along with the back end admin system, I can't rewrite both parts at the same time, this is a live app in production that works currently, I'm simply trying to learn Catalyst& MVC cuteness, not start from scratch.
From what I can see using any of those authentication modules expects certain data I don't have or use nor want.
Please correct me if I'm reading the CPAN documentation incorrectly.

I want to refactor my app to be MVC using Catalyst without being forced to do any other than MVC cuteness and work the way I want to with the a database that already exists, I got the feeling Catalyst allows this unlike ROR or other MVC frameworks.

Again, have I got this wrong?

If to use Catalyst I have to have a normalised DB, use specific modules with data in a particular format, then I will just refactor our systems myself using my own modules and such, best to find this out now before I spend any more time on something that isn't suitable.

Thanks,

Craig.


-----Original Message-----
From: Lukas Thiemeier
Sent: 29 October 2012 16:42
To: catalyst@lists.scsys.co.uk
Subject: Re: [Catalyst] Unable to output anything in Root.pm -> 'auto'


Hey Craig,

I got it. You want to store your credentials in a database, but you don't want to use DBIx::Class?

What about Catalyst::Authentication::Storage::DBI?

If this doesn't help, you might me right. Maybe you have to write your own authentication module. In that case, consider making it a Catalyst::Authentication::Store module, and publish it on cpan. It might be useful for others, too...

By the way: Catalyst::Model::DBI is a ORM-less, raw DBI model for catalyst. So "... whenever I look at how it implements anything to do with DB access, it forces ORM upon you ..." is not correct. There are very few things which are really forced by catalyst. Using DBIx::Class is just considered "good practice". A lot of people use it, thats why it is used in most tutorials and examples.

Lukas


On 10/29/2012 05:09 PM, Craig Chant wrote:
Yes, but I need to keep a backed DB up-to-date with current logins, where in the system they are etc...

So local server disk won't help in this situation.

-----Original Message-----
From: Denny
Sent: 29 October 2012 15:50
To: The elegant MVC web framework
Subject: RE: [Catalyst] Unable to output anything in Root.pm -> 'auto'
On Mon, 2012-10-29 at 15:43 +0000, Craig Chant wrote:
"By the way, what do you need the session-id for? Catalyst handles sessions in a transparent way"

To authenticate users, I don't want to store authentication in the hash and it seems the only other way to do this is via ORM, which I don't want to use either.

I find catalyst whenever I look at how it implements anything to do with DB access, it forces ORM upon you, so I need to write my own authentication code don't I ?
I'm pretty sure the default storage for session stuff is disk-based.



_______________________________________________
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/ This Email and any
attachments contain confidential information and is intended solely
for the individual to whom it is addressed. If this Email has been
misdirected, please notify the author as soon as possible. If you
are not the intended recipient you must not disclose, distribute,
copy, print or rely on any of the information contained, and all
copies must be deleted immediately. Whilst we take reasonable steps
to try to identify any software viruses, any attachments to this
e-mail may nevertheless contain viruses, which our anti-virus
software has failed to identify. You should therefore carry out
your own anti-virus checks before opening any documents. HomeLoan
Partnership will not accept any liability for damage caused by
computer viruses emanating from any attachment or other document
supplied with this e-mail. HomeLoan Partnership reserves the right
to monitor and archive all e-mail communications through its
network. No representative or employee of HomeLoan Partn
ership ha
s the authority to enter into any contract on behalf of HomeLoan Partnership by email. HomeLoan Partnership is a trading name of H L Partnership Limited, registered in England and Wales with Registration Number 5011722. Registered office: 26-34 Old Street, London, EC1V 9QQ. H L Partnership Limited is authorised and regulated by the Financial Services Authority.


_______________________________________________
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/

_______________________________________________
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/ This Email and any
attachments contain confidential information and is intended solely
for the individual to whom it is addressed. If this Email has been
misdirected, please notify the author as soon as possible. If you
are not the intended recipient you must not disclose, distribute,
copy, print or rely on any of the information contained, and all
copies must be deleted immediately. Whilst we take reasonable steps
to try to identify any software viruses, any attachments to this
e-mail may nevertheless contain viruses, which our anti-virus
software has failed to identify. You should therefore carry out your
own anti-virus checks before opening any documents. HomeLoan
Partnership will not accept any liability for damage caused by
computer viruses emanating from any attachment or other document
supplied with this e-mail. HomeLoan Partnership reserves the right
to monitor and archive all e-mail communications through its
network. No representative or employee of HomeLoan Par
t
n
ership ha
s the authority to enter into any contract on behalf of HomeLoan Partnership by email. HomeLoan Partnership is a trading name of H L Partnership Limited, registered in England and Wales with Registration Number 5011722. Registered office: 26-34 Old Street, London, EC1V 9QQ. H L Partnership Limited is authorised and regulated by the Financial Services Authority.
_______________________________________________
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/

_______________________________________________
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/ This Email and any
attachments contain confidential information and is intended solely
for the individual to whom it is addressed. If this Email has been
misdirected, please notify the author as soon as possible. If you are
not the intended recipient you must not disclose, distribute, copy,
print or rely on any of the information contained, and all copies
must be deleted immediately. Whilst we take reasonable steps to try
to identify any software viruses, any attachments to this e-mail may
nevertheless contain viruses, which our anti-virus software has
failed to identify. You should therefore carry out your own
anti-virus checks before opening any documents. HomeLoan Partnership
will not accept any liability for damage caused by computer viruses
emanating from any attachment or other document supplied with this
e-mail. HomeLoan Partnership reserves the right to monitor and
archive all e-mail communications through its network. No
representative or employee of HomeLoan Part
n
ership ha
s the authority to enter into any contract on behalf of HomeLoan Partnership by email. HomeLoan Partnership is a trading name of H L Partnership Limited, registered in England and Wales with Registration Number 5011722. Registered office: 26-34 Old Street, London, EC1V 9QQ. H L Partnership Limited is authorised and regulated by the Financial Services Authority.
_______________________________________________
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/

_______________________________________________
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/ This Email and any attachments
contain confidential information and is intended solely for the
individual to whom it is addressed. If this Email has been
misdirected, please notify the author as soon as possible. If you are
not the intended recipient you must not disclose, distribute, copy,
print or rely on any of the information contained, and all copies must
be deleted immediately. Whilst we take reasonable steps to try to
identify any software viruses, any attachments to this e-mail may
nevertheless contain viruses, which our anti-virus software has failed
to identify. You should therefore carry out your own anti-virus checks
before opening any documents. HomeLoan Partnership will not accept any
liability for damage caused by computer viruses emanating from any
attachment or other document supplied with this e-mail. HomeLoan
Partnership reserves the right to monitor and archive all e-mail
communications through its network. No representative or employee of
HomeLoan Partn
ership has the authority to enter into any contract on behalf of HomeLoan Partnership by email. HomeLoan Partnership is a trading name of H L Partnership Limited, registered in England and Wales with Registration Number 5011722. Registered office: 26-34 Old Street, London, EC1V 9QQ. H L Partnership Limited is authorised and regulated by the Financial Services Authority.
_______________________________________________
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/
--
IntelCompute
Web Design & Online Marketing Experts

http://www.intelcompute.com

_______________________________________________
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/
This Email and any attachments contain confidential information and is intended solely for the individual to whom it is addressed. If this Email has been misdirected, please notify the author as soon as possible. If you are not the intended recipient you must not disclose, distribute, copy, print or rely on any of the information contained, and all copies must be deleted immediately. Whilst we take reasonable steps to try to identify any software viruses, any attachments to this e-mail may nevertheless contain viruses, which our anti-virus software has failed to identify. You should therefore carry out your own anti-virus checks before opening any documents. HomeLoan Partnership will not accept any liability for damage caused by computer viruses emanating from any attachment or other document supplied with this e-mail. HomeLoan Partnership reserves the right to monitor and archive all e-mail communications through its network. No representative or employee of HomeLoan Partnership has the authority to enter into any contract on behalf of HomeLoan Partnership by email. HomeLoan Partnership is a trading name of H L Partnership Limited, registered in England and Wales with Registration Number 5011722. Registered office: 26-34 Old Street, London, EC1V 9QQ. H L Partnership Limited is authorised and regulated by the Financial Services Authority.

_______________________________________________
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/

Search Discussions

Discussion Posts

Previous

Follow ups

Related Discussions

Discussion Navigation
viewthread | post
posts ‹ prev | 21 of 22 | next ›
Discussion Overview
groupcatalyst @
categoriescatalyst, perl
postedOct 29, '12 at 2:23p
activeOct 30, '12 at 10:21p
posts22
users7
websitecatalystframework.org
irc#catalyst

People

Translate

site design / logo © 2021 Grokbase