Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2009
    Posts
    335
    Rep Power
    246

    Catalyst -> Model & Schema


    Hi,

    OK, I'm working through the tutorial and so far so good, I have a controller, a view, a template, a model, I'm displaying books from a SQLlite DB, everything is fine and dandy, however.

    I realise that all this is running using a simple DB that is normalised and has all PK/FK/IX defined in an ideal world.

    Our backend DB isn't!

    Perhaps I'm trying to run before I can walk and if I need to be patient and wait till later on in the Catalyst manual please point me in the right direction.

    But how do I do the following...

    Create relationships between tables where column names don't match.

    Retrieve data using column or table aliases.

    Execute stored procedures.

    Use table views
    Why is there even a DB Scheme in the catalyst perl app and why does it auto generate all these models based on relationships Catalyst thinks it has found?

    Why is Catalyst trying to do the job of SQL server?

    Perhaps I'm missing some fundamentals here, after all I am very green to all this, but I can't seem to understand why there are schemas and relationship information in the application , when all this is defined in the MS SQL Server?

    Do I need to edit the schema files and manually create these relationships?

    Do I ignore the Schema and just use DBIx-Class methods to retrieve the data I want with hand rolled SQL statements (which is how my apps currently work)?

    And I assume to achive all this I need to go learn the DBIx-Class module?

    All advice greatly appreciated.

    1DMF.
    Free MP3 Dance Music Downloads

    To err is human; To really balls things up you need Microsoft!
  2. #2
  3. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2009
    Posts
    335
    Rep Power
    246
    Well I think I need to learn how to use Catalyst without ORM!

    I found this site

    http://stormy-robot-92.heroku.com/#1

    That is a slide show presentation at some conference, which asks the same questions I have whirling in my head, even things like what about COUNT/MAX/MIN , agregation, bespoke column selection, stored procedures, etc.. etc..

    I need to learn how to use Catalyst without ORM and re-factor my current SQL module to be a better OOP perl class and drop that in for all my models! - ok , not ideal, but if it aint broke! and at least I know how to use it and it gives me the access to the backend data in a more felxible way!

    I also use HTML::Template in my current web app, so I might as well stick with that , as I already have a tonne of templates that make up the application.

    hmm, Perl's ethos is 'many ways to skin the cat', I sure hope Catalyst continues to bend to this ethos, why still providing a good solid framework and rapid application development?

    It seems the more I look into all this the more complaints and rhetoric I see about how ORM is bad or stubborn, or inefficient or inflexible?

    If so, why do people use them and why do they seem to be the paradigm of all web application frameworks?
    Free MP3 Dance Music Downloads

    To err is human; To really balls things up you need Microsoft!
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    May 2007
    Posts
    765
    Rep Power
    929
    You can subclass Catalyst::Model::DBI to handle the connections and add convenience methods to perform whatever queries you need. You still get the MVC-cuteness without the Catalyst worrying about you database design.

    Code:
    package WebApp::Model::Widgets;
    use base 'Catalyst::Model::DBI';
    
    # ...
    
    sub getList {
        my ($self) = @_;
        $self->dbh->selectall_arrayref("...");
    }
    
    # # # # # #
    
    package WebApp::Controller::Widgets;
    
    sub doPage {
       my ($c) = @_;
       my $list = $c->model('Widgets')->getList;
    }

    ORM is one of those programming in the large things. Until you've reached the scale where it's more painful not to use it, it doesn't seem that useful. The big idea is that Perl can automatically generate the queries to the database (imagine you have several tables with complex joins) and when the DBAs start changing things you just copy in their updated schemas and everything still works.
    Last edited by OmegaZero; October 12th, 2012 at 08:45 AM.
    sub{*{$::{$_}}{CODE}==$_[0]&& print for(%:: )}->(\&Meh);
  6. #4
  7. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2009
    Posts
    335
    Rep Power
    246
    Thanks OmegaZero,

    I'll have to have a play and let you know how I get on.

    I assume from your example, a controller will aways have access to methods in the model?

    So I simply code the model to obtain the records the way I want and use the controller to manipulate the results.

    I think this is all making sense, but correct me if I'm wrong.

    1DMF
    Free MP3 Dance Music Downloads

    To err is human; To really balls things up you need Microsoft!
  8. #5
  9. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2009
    Posts
    335
    Rep Power
    246
    I seem to be having difficlty with your example.

    all the examples I have seen when referecning @_ , use
    Code:
    my ( $self, $c ) = @_;
    and when I use
    Code:
    use base 'Catalyst::Model::DBI';
    in the DB.pm model, I get the following error
    Couldn't load class (MyApp) because: Base class package "Catalyst::Model:: DBI" is empty.
    (Perhaps you need to 'use' the module which defines that package first,
    or make that module available in @INC
    Any ideas?

    Thanks,
    1DMF
    Free MP3 Dance Music Downloads

    To err is human; To really balls things up you need Microsoft!
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    May 2007
    Posts
    765
    Rep Power
    929
    Is Catalyst::Model::DBI installed? (I don't believe it's part of the normal Catalyst distro)

    To create DB.pm, did you use the "xxx_create.pl" script or do it manually?
    sub{*{$::{$_}}{CODE}==$_[0]&& print for(%:: )}->(\&Meh);
  12. #7
  13. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2009
    Posts
    335
    Rep Power
    246
    I used the create script, while following the tutorial.

    I then editited it as per your example to see if I could understand how to circumvent the built in ORM or replace it with my own.

    I replaced the line
    'Catalyst::Model:: DBI::Schema'
    with the one you provded and the server falls over.

    I put the original line back in and added 'use DBI;' after it and it runs OK again.

    What I'm trying to achieve is having 2 x models , one for each SQL server we have as they use separate connection strings / DSN's

    Though when I removed the '__PACKAGE__->config(..._)' from the model it errored with unable to execute method 'config'? So it appears some config with connection string are required in the model.pm modules?

    Do I need to alter some flag to turn off ORM?

    I'm hoping to have a DB.pm model, that is a SQL interface layer, so I could perform aqctions within the controller such as....
    Code:
    my @recs = $c->model('DB')->getSQL("Table","Cols","Where","Order");
    Or perhaps I should really have more separation so their is the inital DB.pm which is simply the connection string (which is what the turotial seems to have created), then give each desired 'function' have a model where the processes go for it, and then the controller that calls those functions.

    The problem I have is some of the data is on one server and some is on another, a model is not simply a table in this environment. A model for a particular function within the app might need to access both servers and different tables within those servers , it's imposible to have an ORM or a catalyst model that simply sees a model as a table and it's relationships within a single DB!

    So I might need to keep some of the logic for certain app functions in the controller so it can get records from both servers to the process / work with ...ie...
    Code:
    my @arrayRecs1 = $c->model('DB1')->getSQL("Table","Cols","Where","Order");
    
    my @arrayRecs2 = $c->model('DB2')->getSQL("Table","Cols","Where","Order");
    
    push @arrayRecs1,@arrayRecs2;
    That's just a basic example, but hopefully you see what I'm trying to achieve?

    Does this break MVC, do I need to separate further?

    Do I need a model that accesses another model?

    (I have 2 x models for DB connections 'DBL.pm' - DB Local & 'DBR.pm' - DB Remote)

    I then have a model for a process called 'Members.pm' that would us the following....

    Code:
    sub getList {
    my ($self,$c) = @_;
    my @arrayRecs1 = $c->model('DBL')->getSQL("Table","Cols","Where","Order");
    
    my @arrayRecs2 = $c->model('DBR::Contacts')->getMembers("Table","Cols","Where","Order");
    
    push @arrayRecs1,@arrayRecs2;
    
    return @arrayRecs1;
    }
    So in my controller Members.pm I use
    Code:
       my ($self,$c) = @_;
       my @list = $c->model('Members')->getList;
    how would I piece this together?
    Last edited by 1DMF; October 16th, 2012 at 03:50 AM.
    Free MP3 Dance Music Downloads

    To err is human; To really balls things up you need Microsoft!
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    May 2007
    Posts
    765
    Rep Power
    929
    When the model is created there's some configuration data written separately (not sure where, I don't have easy access to a catalyst environment at the moment). So I doubt it will be easy to change an ORM-model into something else.

    Catalyst doesn't care whether you use ORM or not (run a cpan search on Catalyst::Model:: to see just how much it doesn't care).

    Try removing the model you already have (not quite sure how you would do that--it's probably safe to ignore it for the moment). And then run the bin\xxx_create.pl script twice, to create a model for each of your databases, specifying "DBI" as the base class.

    I'm not sure how you'd go about making a model that provides an interface over the other two. Catalyst::Model::Adaptor might help, but I don't see a way to easily bring references to the other models into it. Your best bet may be something like this:

    Code:
    package XXX::Util::CombinedModel;
    # ... not really a Catalyst model
    
    sub new {
       my ($class,$c) = @_;
       bless {
          db1 => $c->model('DB1'),
          db2 => $c->model('DB2'),
       }, $class;
    }
    
    sub getStuff {
       my ($self) = @_;
       return $self->{db1}->getStuff(), $self->{db2}->getStuff();
    }
    
    # # # # # # # # #
    
    # In Root.pm
    
    sub combinedModel {
       my ($self) = @_;
       return XXX::Util::CombinedModel->new($self);
    }
    sub{*{$::{$_}}{CODE}==$_[0]&& print for(%:: )}->(\&Meh);
  16. #9
  17. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2009
    Posts
    335
    Rep Power
    246
    Well you've totally lost me with that code!

    I'm also confused as you say "Catalyst doesn't care whether you use ORM or not ", but also say "I'm not sure how you'd go about making a model that provides an interface over the other two".

    Does Catalyst allow for your own SQL interface or not?

    I'm completely bamboozaled how you access a SQL DB using Catalyst without using the inbuilt ORM based on DBIC and schema files?

    It's child's play outside of Catalyst, I must be missing something simple here?

    Also the way you seem to have set up the DB Model.

    Does this mean in my Catalyst model say 'Members.pm' I would access data with
    Code:
    my @recs = $c->model('CombinedModel')->getStuff;
    Which is no good as not always do I want data from both SQL servers?

    So a combined model doesn't work.

    I'll have a play tonight creating some Models/Controllers and try to get this working, but already I'm starting to feel I'm at a brick wall!
    Free MP3 Dance Music Downloads

    To err is human; To really balls things up you need Microsoft!
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    May 2007
    Posts
    765
    Rep Power
    929
    There is no built-in ORM/DBIC/Schema stuff in Catalyst. That all belongs to the Catalyst::Model::DBIC-whatever module that the tutorial probably assumed that you'd use. If you create your models from a different class, you get models based on some other principle.

    E.g., running
    Code:
    bin\xxx_create.pl model SomeData DBIC::Schema SomeSchema
    bin\xxx_create.pl model OtherData DBI dbi:whatever
    creates two models: XXX::Model::SomeData which is based on Catalyst::Model::DBIC::Schema and has all the ORM stuff you don't care about; and XXX::Model::OtherData which is based on Catalyst::Model::DBI and is just a wrapper that loads & maintains a database handle for you. The bold bit determines what Catalyst::Model::* class to use, and you pick whichever one makes sense for your data.


    The place where I'm having trouble & tried to address with that last code snippet is that (if I'm understanding you correctly), you have two physical databases but wish to treat them as a single logical database from your Controller's perspective. I don't know of an easy way to aggregate models--maybe there's no module for it already because it's easily to just create a model use composition--or maybe it's hard and just no one has needed it enough to make a general-purpose module.

    The code I posted is an attempt to fake it by adding a method onto the root object and a helper-class that contains all of the query-both-models functionality. (Looking over that again, the combinedModel method might belong in the XxxApp.pm module, not the Root controller.) In the controller, $m = $c->combinedModel would obtain a helper object and then $m->getStuff could be used to issue queries against both databases.

    (Note) This wouldn't stop you from using the normal $c->model('DB1') or having methods in the CombinedModel class that only query one of the databases.

    OTOH, I could be understanding you wrong. If you're only combining the results of the two databases occasionally and aren't going to end up duplicating code, maybe it would make sense to create two separate models and let the Controller do $local = $c->model('Local') and $remote = $c->model('Remote') and then pull the data together at that point.
    sub{*{$::{$_}}{CODE}==$_[0]&& print for(%:: )}->(\&Meh);
  20. #11
  21. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2009
    Posts
    335
    Rep Power
    246
    Firstly thank you so much for all your help so far it really is appreciated.

    I seem to be making a little headway and am starting to piece things together.

    So I currently have a model DBLocal and inside I have
    Code:
    package Members::Model::DBLOCAL;
    
    use strict;
    use warnings;
    use parent 'Catalyst::Model:: DBI';
    use Moose;
    use namespace::autoclean;
    BEGIN { extends 'Members::Model::Sql' }
    
    __PACKAGE__->config(
        dsn           => 'DBI:ODBC:DSN=MyDSN,
        user          => 'MyUID',
        password      => 'MyPWD',
        options       => {},
    );
    You see I have set it to extend the base model (Sql.pm) that I want to use for either connection/server (LOCAL/REMOTE). As my key SQL interface layer!

    in the Sql model I have
    Code:
    package Members::Model::Sql;
    use Moose;
    use namespace::autoclean;
    
    BEGIN { extends 'Catalyst::Model' }
    
    __PACKAGE__->config(namespace => '');
    
    
    # Get SQL Routine 
    sub getSQL {
    
        my ($self,$c) = @_;
       
    }
    But this is where I get stuck, if I interogate $c it is empty? why isn't it the context object? If i access $self for the attributes passed in from the parent class (DBLOCAL), for example
    Code:
    die $self->{dsn};
    I get the expected result showing the correct value for the dsn attribute.

    But if I try to extrapolate your code above
    sub getList {
    my ($self) = @_;
    $self->dbh->selectall_arrayref("...");
    }
    by trying
    Code:
    my $dbh = $self->dbh;
    It just errors with
    Members::Controller::Root->auto "Can't locate object method "dbh" via package "Members::Model:: DBLOCAL"
    Which is actioned by the root controller method
    Code:
    # always runs first!
    sub auto :Private {
        my ( $self, $c ) = @_;
    
        # Get records
        my @recs = $c->model('DBLOCAL')->getSQL();
    
    }
    So it seems I;ve patched together a model that I can access through the $c (context) var using the 'model' method and even get acccess to the 'getSQL' method in the Sql model.

    I just can't quite finalise the link that enables the above access via my own model?

    I feel I'm close, just can't quite gell it together yet!

    Can't quite work out how I use the Catalyst::Model:: DBI class in my Sql model.

    Any ideas?

    Thanks,
    1DMF.
    Free MP3 Dance Music Downloads

    To err is human; To really balls things up you need Microsoft!
  22. #12
  23. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    May 2007
    Posts
    765
    Rep Power
    929
    No problem,

    Is the space in "use parent 'Catalyst::Model:: DBI';" a typo in the post or in the actual code?

    Regarding the $c in the getSQL method, you aren't passing any arguments when you call getSQL so it's only receiving the 'self' object from the method invocation.

    I wonder if this is a Moose issue--Members::Model::DBLOCAL class has one superclass with Moose semantics and one with ordinary OO-perl semantics. I don't know enough about Moose to know if it rewrites 'use parent' or not. Method resolution might also be a problem since Members::Model::Sql::getSQL needs to call across the inheritance graph to Catalyst::Model::DBI::dbh.

    It might be useful to try using Members::Model::DBLOCAL in isolation--remove the references to Members::Model::Sql & copy the methods into Members::Model::DBLOCAL. Then you'll at least be able to tell if Catalyst::Model::DBI is working.
    sub{*{$::{$_}}{CODE}==$_[0]&& print for(%:: )}->(\&Meh);
  24. #13
  25. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2009
    Posts
    335
    Rep Power
    246
    The space has been deliberately added for this post because this stupid forum keeps inserting a smiley face for colon D!

    I'll have a go with what you suggest, though I don't want to have two version of the same SQL interface code, but like you say it might show that at lease 'DBLOCAL' has access to dbh in DBI.

    Though, I am pulling my hair out currently, as I decided to forget about SQL access for time being and implement session handling, as that will also be needed, only I can't get the default page to load any more.

    I have removed the call in root for 'auto' to the DBLOCAL , yet it is still falling over about unable to access dbh for a line of code that doesn't exist in Root and a line in Sql.pm that is a comment?

    I'm close to throwing this bloody framework out the window!

    What am I doing wrong?

    Caught exception in Members::Controller::Root->auto "Can't locate object method "dbh" via package "Members::Model::DBLOCAL in Sql.pm line 15
    But look at the trace?

    Stack Trace




    Package

    Line

    File



    Members::Model::Sql

    15

    Members\Model\Sql.pm



    12: sub getSQL {
    13:
    14: my ($self,$c) = @_;
    15: #my $dbh = $self->dbh;
    16: #die $dbh;
    17:
    18:



    Members::Controller::Root

    41

    Members\Controller\Root.pm



    38: my ( $self, $c ) = @_;
    39:
    40: # Authenticate
    41:
    42:
    43: }
    44:
    So how come line 41 in Root that doesn't exist is calling getSQL in Sql.pm which is falling over on a line that is commented out?

    This makes no sense what so ever, how can a blank line call a method and that method fall over due to a comment?

    This isn't perl behaviour at all, what gives?
    Free MP3 Dance Music Downloads

    To err is human; To really balls things up you need Microsoft!
  26. #14
  27. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    May 2007
    Posts
    765
    Rep Power
    929
    Re: two version of the same SQL interface code,
    There are ways to tackle this other than inheritance.

    Re: trace,
    Did you restart the catalyst server after making the changes?
    sub{*{$::{$_}}{CODE}==$_[0]&& print for(%:: )}->(\&Meh);
  28. #15
  29. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2009
    Posts
    335
    Rep Power
    246
    When you say restart the catalyst server, what do you mean, in IIS7 restart the website?

    There is no dameon catalyst server running when over FastCGI is there?

    If so where is it and how do I restart it?
    Free MP3 Dance Music Downloads

    To err is human; To really balls things up you need Microsoft!
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo