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