Beefy Boxes and Bandwidth Generously Provided by pair Networks
Do you know where your variables are?
 
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 examining the Monastery: (14)
As of 2015-07-31 10:46 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (276 votes), past polls