Beefy Boxes and Bandwidth Generously Provided by pair Networks
No such thing as a small change
 
PerlMonks  

Re: Class::DBI newbie

by 7stud (Deacon)
on Feb 02, 2013 at 04:44 UTC ( #1016640=note: print w/ replies, xml ) Need Help??


in reply to Class::DBI newbie

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. #


Comment on Re: Class::DBI newbie
Select or Download Code

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://1016640]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others scrutinizing the Monastery: (7)
As of 2014-07-30 00:30 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My favorite superfluous repetitious redundant duplicative phrase is:









    Results (229 votes), past polls