Class::DBI newbie

[rodbass63]

Hi, I want to start learning Class::DBI and put the default DBI aside for a while. I created this method that does a simple select, I want to learn how to do it the Class::DBI way. Here's my method doing it the old fashion way.

sub test
{
# DATA SOURCE NAME
my $dsn = "dbi:mysql:$dbname:localhost:3306";

# PERL DBI CONNECT
my $connect = DBI->connect($dsn, $user, $pw);

my $query = "SELECT username, password, created FROM TempAccountRegist
+ry";
my $query_handle = $connect->prepare($query);

# EXECUTE THE QUERY
$query_handle->execute();

# BIND TABLE COLUMNS TO VARIABLES
$query_handle->bind_columns(\$username, \$password, \$created);

# LOOP THROUGH RESULTS
while($query_handle->fetch()) {
print DB "$username, $password, $created\n";
 }

}

I have another file with Class::DBI

use base 'Class::DBI';

DataStore::DAO::Base->connection("dbi:$source:$dbname", $user, $passwo
+rd);

# I would like to add the method doing it the Class::DbI 
# way here.

Where I work, they strive on Class:DBI, so I'm eager to learn it. Thanks for your help. usaims

[7stud]

The simplest setup employs one additional file next to your program file:

some_dir/
   my_prog.pl
   Users.pm

Here is the table I'm using:

CREATE TABLE TempAccountRegistry ( 
    id INT(12) not null auto_increment primary key, 
    username VARCHAR(255), 
    password VARCHAR(50), 
    created TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

1) Create the file Users.pm, and put this code in it:

#This is file: Users.pm

package Users;  #Class name

use strict;
use warnings;
use base 'Class::DBI';  
    #Users inherits from Class::DBI

#Database specific info comes next:
Users->connection('dbi:mysql:my_db', 'root', '');
#Format is: (dbi:<database_type>:<db_name>, <username>, <password>)

#Connect your class to a table:
Users->table('TempAccountRegistry');

#Declare the columns in the table(a primary key is required):
Users->columns(All => qw/id username password created/);

1;

2) Now you are ready to write a program that uses the features of Class::DBI. Create a program file called my_prog.pl in the same directory as Users.pm, and put this code in it:

#This is file: my_prog.pl

use strict;
use warnings;
use 5.012;  #depending on your version of perl

use Users;

my $user1 =  Users->insert(
    {
        username => 'Joe',
        password => '123',
    }
);

say $user1->username;  #Joe

Here is the effect on your database:

mysql> select * from TempAccountRegistry;
+----+----------+----------+---------------------+
| id | username | password | created             |
+----+----------+----------+---------------------+
|  1 | Joe      | 123      | 2013-02-02 00:12:00 |
+----+----------+----------+---------------------+
1 row in set (0.00 sec)

You can retrieve a row like this:

my $id = 1; 
my $user = Users->retrieve($id);
say $user->username;    #Joe

If you insert another row:

my $user2 = Users->insert(
    {
        username => 'Jim',
        password => '456',
    }
);

mysql> select * from TempAccountRegistry;
+----+----------+----------+---------------------+
| id | username | password | created             |
+----+----------+----------+---------------------+
|  1 | Joe      | 123      | 2013-02-02 00:12:00 |
|  2 | Jim      | 456      | 2013-02-02 00:12:00 |
+----+----------+----------+---------------------+
2 rows in set (0.00 sec)

...you can retrieve all the rows like this:

my @users = Users->retrieve_all;

for my $user (@users) {
    say $user->username;
    say $user->password;
    say $user->created;
}

--output:--
Joe
123
2013-02-02 00:12:00
Jim
456
2013-02-02 00:12:00

You can interpolate method calls into a string like this:

for my $user (@users) {
    printf "%s %s %s \n",
           $user->username,
           $user->password,
           $user->created;
}

--output:--
Joe 123 2013-02-02 00:12:00
Jim 456 2013-02-02 00:12:00

You can change the information in a row like this:

$user2->username("Kathy");
$user2->update;

mysql> select * from TempAccountRegistry;
+----+----------+----------+---------------------+
| id | username | password | created             |
+----+----------+----------+---------------------+
|  1 | Joe      | 123      | 2013-02-02 00:12:00 |
|  2 | Kathy    | 456      | 2013-02-02 00:12:00 |
+----+----------+----------+---------------------+
2 rows in set (0.00 sec)

Finally, you can delete a row like this:

$user1->delete;

mysql> select * from TempAccountRegistry;
+----+----------+----------+---------------------+
| id | username | password | created             |
+----+----------+----------+---------------------+
|  2 | Kathy    | 456      | 2013-02-02 00:12:00 |
+----+----------+----------+---------------------+
1 row in set (0.00 sec)

Read the docs for the various ways you can search the database and update info.

Also, according to the docs:

It's usually wise to set up a "top level" class for your entire application to inherit from, rather than have each class inherit directly from Class::DBI. This gives you a convenient point to place system-wide overrides and enhancements to Class::DBI's behavior.

If you want to do that, here are the files you'll need:

/some_dir
    my_prog.pl
    MyDBIDatabase.pm
    Users.pm

1) Create the file MyDBIDatabase.pm. Then put this code in MyDBIDatabase.pm:

#This is file: MyDBIDatabase.pm

package MyDBIDatabase;  #Class name

use strict;
use warnings;
use base 'Class::DBI';  
    #MyDBIDatabase inherits from Class::DBI

#Database specific info comes next:
MyDBIDatabase->connection('dbi:mysql:my_db', 'root', '');
#Format is: (dbi:<database_type>:<db_name>, <username>, <password>)

1;   #Don't forget this!

2) Create another file called Users.pm. Put this code in Users.pm:

#This is file: Users.pm

package Users;  #Class name

use strict;
use warnings;
use base 'MyDBIDatabase';  
    #Users inherits from MyDBIDatabase;

#Connect your class to a table:
Users->table('TempAccountRegistry');


#Declare the columns in the table(a primary key is required):
Users->columns(All => qw/id username password created/);

1;

Any additional classes that you want to map to tables will also inherit from MyDBIDatabase instead of Class::DBI.

3) You can use the same code as above for my_prog.pl.

If you have a larger project, you can use a more sophisticated directory structure:

some_dir/
   my_prog.pl
   MyApp/
       MyDBIDatabase.pm
       Users.pm

1) Create the subdirectory MyApp.

2) cd into the MyApp directory, and create the file MyDBIDatabase.pm (can be any name with a .pm extension). Then put this code in MyDBIDatabase.pm:

#This is file: MyApp/MyDBIDatabase.pm

package MyApp::MyDBIDatabase;  
#Class name, which mimics MyApp/MyDBIDatabase.pm

use strict;
use warnings;
use base 'Class::DBI';  
    #MyApp::MyDBIDatabase, inherits from Class::DBI

#Database specific info comes next:
MyApp::MyDBIDatabase->connection('dbi:mysql:my_db', 'root', '');
#Format is: (dbi:<database_type>:<db_name>, <username>, <password>)

1;   #Don't forget this!

3) Create another file called Users.pm (also in the MyApp directory, can be any name with a .pm extension). Put this code in Users.pm:

#This is file: MyApp/Users.pm

package MyApp::Users;  
#lass name, which mimics MyApp/Users.pm

use strict;
use warnings;
use base 'MyApp::MyDBIDatabase';  
    #MyApp::Users inherits from MyApp::MyDBIDatabase;

#Connect your class to a table:
MyApp::Users->table('TempAccountRegistry');


#Declare the columns in the table(a primary key is required):
MyApp::Users->columns(All => qw/id username password created/);

1;

4) Change directories back to the higher directory from which you came( cd ..).

5) Create a program file called my_prog.pl (can be any name), and put this code in it:

#This is file: my_prog.pl

use strict;
use warnings;
use 5.012;  #depending on your version of perl

use MyApp::Users;

my $user1 =  MyApp::Users->insert(
    {
        username => 'Joe',
        password => '123',
    }
);

say $user1->username;  #Joe

#etc.
#etc.
#Same as my_prog.pl above with 'Users' replaced by 'MyApp::Users'
#everywhere in the code.
#