Quick way to set up columns with Class::DBI ?

[jfrm]

Just embarking on Class::DBI and to start, one has to define the database including the fields in each table e.g:

Music::CD->columns(All => qw/cdid artist title year/);

But the list of fields is gettable from the database anyway. I already happen to have a function which does a simple list of table columns. So I have a constructive question. Firstly why can't Class::DBI set this up automatically for you.

And secondly and more practically, is there any reason why I can't do something like: my @colnames = @{get_colnames(table)}; Music::CD->columns(All => \@colnames);

to save writing the fields out (and then having to change them whenever I modify my table)?

I do apologise if this is a naive question.

thanks for any tips,

James Masters.

Edited by planetscape - added code tags

( keep:1 edit:26 reap:0 )

[friedo]

Which database are you using? The queries to get table meta-data are largely database-dependant, so Class::DBI tries to avoid them. There is Class::DBI::mysql which does exactly what you want, though, if you're using MySQL. (See the set_up_table method.)

I think there are similar modules for other databases.

[davidrw]

is Class::DBI::Loader what you're looking for?

[perrin]

I don't recommend starting a new project with Class::DBI at this point unless your needs are very basic. You'd be better off using a more full-featured database mapping tool like Rose::DB::Object or DBIx::Class.

[jfrm]

Wow. Having read up on those, looks like you have saved me a lot of hassle, thanks. Everytime I ask a question I get an suggestion that doesn't answer it but points me somewhere else better!

Now I need to work out which is best for me. I've read some comparison discussions that seem to concentrate on things I'm not so fussed about like performance. However, I'm presently liking the look of Rose better for 2 reasons:
1. the documentation seems larger and easier to understand
2. It is obvious how to automatically configure the database which saves me a lot of time. It isn't clear to me that DBIx::Class even does this, does it?

My objective is to create some generic routines that will simply generate web forms that will allow me to BREAD several disparate databases of mine. Does Rose sound good for this?

Actually as I've got this far on the deep voyage of discovery I am undergoing this week, does anyone know of good packages to help me generate the web forms?

As ever I am very grateful for all pointers and advice.

[perrin]

DBIx::Class is also capable of auto-configuration from your database. Both will work for BREAD. There are some auto-BREAD/CRUD things for Catalyst that use DBIx::Class, but I haven't personally tried them.

[siracusa]

My objective is to create some generic routines that will simply generate web forms that will allow me to BREAD several disparate databases of mine. Does Rose sound good for this?

It sure does :) Take a look at Rose::HTML::Objects as well for the form generation. It's pretty easy to auto-generate both things: the RDBO classes from the database (with the Loader/auto_initialize) and then auto-generate the HTML forms using something like what's described in this mailing list post. (Hop on the mailing lists for Rose::DB::Object and Rose::HTML::Objects if you have any questions.)

(Whoops, link corrected.)

[jfrm]

OK, thanks v. much for the answers which hit the nail on the head, I think. If only I knew what all the modules did, I'm sure I would write half the code I currently do... Incidentally, I use MySQL.

Anyway, I have tried for about an hour to install Class-DBI-MySQL. PPM3, CPAN, CPANPLUS and finally tried to nmake it manually. Failed - I am on win32; perhaps that's why.

Then I tried Class-DBI-Loader which the documentation says works with MySQL. This instlled with PPM3 immediately.

So Q1, why has someone written Class-DBI-Mysql when Class-DBI-Loader exists and seems to do the same thing?

Q2, if they both do the same thing, is one better than the other?

Q3, I do try to read and understand this stuff before troubling other monks with questions but in doing this for these modules, I noticed Class-DBI-Loader-MySQL. The documentation for this is cryptic and so I am entirely clueless as to it's purpose. Do I need this with Class-DBI-Loader in order to work with MySQL? Or is it a standalone thing i.e. a third alternative for me to use? And if it's a third alternative, which is the best of the 3 modules to use?

[gellyfish]

Class::DBI::Loader::mysql (along with Class::DBI::mysql) provide MySQL specific over-rides for the inspection of the DB schema that is necessary for Class::DBI::Loader to do what it does: for the most part you shouldn't need to concern yourself with the documentation for the DB specific modules as Class::DBI::Loader does the right thing for the most part. But no they are not standalone alternatives, and Class::DBI::Loader::msyql will be installed as part of the Class::DBI::Loader installation.

/J\

[jfrm]

And this is my attempt to test it manually from the cmd line. Still on the machine running mysql and with the env. variables set:



\SWARCH~1\PERL\Class-DBI-mysql\Class-DBI-mysql-1.00>nmake test

Microsoft (R) Program Maintenance Utility   Version 1.50
Copyright (c) Microsoft Corp 1988-94. All rights reserved.

        C:\Perl\bin\perl.exe "-MExtUtils::Command::MM" "-e" "test_harn
+ess(0, 'blib\lib', 'blib\arch')" t\mysql.t t\pod-coverage.t t\pod.t
t\mysql...........ok 8/8Attempt to free unreferenced scalar: SV 0x1c47
+6a0, Perl interpreter: 0x2240dc at C:/Perl/lib/Test/Builder.pm line 1
+329.
t\mysql...........dubious
        Test returned status 5 (wstat 1280, 0x500)
        after all the subtests completed successfully
t\pod-coverage....skipped
        all skipped: Test::Pod::Coverage 1.00 required for testing POD
+ coverage
t\pod.............skipped
        all skipped: Test::Pod 1.00 required for testing POD
Failed Test Stat Wstat Total Fail  Failed  List of Failed
----------------------------------------------------------------------
+---------
t\mysql.t      5  1280     8    0   0.00%  ??
2 tests and 2 subtests skipped.
Failed 1/3 test scripts, 66.67% okay. 0/8 subtests failed, 100.00% oka
+y.
NMAKE : fatal error U1077: 'C:\WINDOWS\system32\cmd.exe' : return code
+ '0xff'
Stop.



It looks like it's nearly working but not quite.

[jfrm]

Thanks. So to confirm: in order to get Class::DBI:: Loader to work, I need both these other modules to work. Please correct me if wrong.
As you say Class::DBI:: Loader::mysql installs with Class::DBI:: Loader but I cannot get Class::DBI::mysql to install and was hoping that some beneficial soul might be able to assist my lowly self in this matter.
I guess it's because the test isn't working - if the test fails, you can't install - is that right? I didn't realise this before.

I see that the test requires some mysql to be installed and env. variables to be setup so I'll have a play with them and try again.

However, this represents a problem in my house of worship because mysql runs on a server but I write all code on another machine which does not have mysql installed. But in order to compile and test the code, I will obviously need to have Class::DBI::mysql working on this machine too. Is there a dodgy workaround??

thanks for any advice - I'm very unfamiliar with the nittygritty of module installations having always used CPAN or PPM or DPM up to now.

[jfrm]

Here are the test results on the server using CPAN:



Writing Makefile for Class::DBI::mysql
    -- OK
Running make test
'test' is not recognized as an internal or external command,
operable program or batch file.
   test -- NOT OK
Running make install
  make test had returned bad status, won't install without force