in reply to Class::DBI newbie
The simplest setup employs one additional file next to your program file:
some_dir/ my_prog.pl Users.pmHere 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.pm1) 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. #