DBIx::Class::Tutorial::GettingStarted - First steps in creating and retrieving database entries
See "CONTENTS" in DBIx::Class::Tutorial
If you've been through Creating Result classes from scratch or Creating Result classes from the database, and now you'd like to actually insert some data into your database using DBIx::Class, then this is the chapter for you.
Some terms needed for this chapter:
"Schema" in DBIx::Class::Manual::Glossary
"ResultSet" in DBIx::Class::Manual::Glossary
"Row" in DBIx::Class::Manual::Glossary
"Primary key" in DBIx::Class::Manual::Glossary
So you're collecting information about a new user of your app in some registration form, and you need to commit that to the database so you can verify them later when they login.
To connect your schema to a specific database, you call the connect method on your schema class, it will return you a schema object.
use MyBlog::Schema;
my $schema = MyBlog::Schema->connect('dbi:SQLite:/usr/src/myapp/blogs.db');The schema object is used for accessing all your database tables, creating, editing, deleting and searching rows.
NB: Do not let the schema object go out of scope while you are still using row or resultset objects or they will fail, DBIx::Class does not keep a reference to the schema object internally.
For other ways to pass the connection info to connect, see "connect" in DBIx::Class::Schema.
Each Result class we created for a table in DBIx::Class::Tutorial::FromScratch is loaded by the Schema class when you use it. To deal with a particular table, you first retrieve the DBIx::Class::ResultSet associated with that table using the name of the Result class. To get one for the users table, we do:
my $users_rs = $schema->resultset('User');Having fetched some new user data from our potential user, we want to create the new user row and write it straight to the database, so we use the create method on our ResultSet object.
my $newuser = $users_rs->create(
{
realname => $realname,
username => $username,
password => $password,
email => $email,
}
);(NB: for better security, you should probably be encrypting the stored passwords, with something like Digest::SHA1, not storing them in plain text!)
Notice we skipped the id column in the create. The database will be supplying that value automatically for new rows. DBIx::Class assumes this for primary key fields, and will fetch the value of that field from the database backend for us, if we don't supply it. Create is documented at "create" in DBIx::Class::ResultSet.
So we can ask our $newuser object, which is now a DBIx::Class::Row, for the id of the new user row. Since this is a brand new database, it's probably 1.
print $newuser->id();
Having created the new user, you'll probably want to use it in some more code, for example to display a confirmation message saying you have sent them an activation email. Or extract the email address to send that email to.
Like the id accessor method, all the columns we described in the Result class are available to retrieve the user data, so to fetch the email address we can just use this in the code:
my $eaddress = $newuser->email();Sometime later, many users have signed up to your application. Now one of them has returned and wishes to login, since we only allow users to create blog posts or comments when they are logged in to the application.
To login you ask the user for their username and password, and look up the user in the database using the username_idx unique constraint we added, using the same $users_rs ResultSet as before.
my $user = $users_rs->find(
{
username => $username,
},
{
key => 'username_idx',
}
);Since we specified that username was a unique column, this can return exactly one result, or none at all. If the user mistyped their username in the login form, the result will be undef, so we can tell the user they failed:
if(!$user) {
print "No such user $username\n";
}Then of course we should compare the password they typed in with the stored one, don't forget to encrypt the one from the login form before you compare it if you are using encryption.
if($user->password() eq $password) {
print "Found you, $username!\n";
}This $user object is just like the $newuser object we got when we created the user originally, it's another instance with the same data; the email accessor can be used just as before:
print $user->email();Eventually, you might want to add functionality to your application to allow your users to search for each other (crazy, I know). So now you'll need to be able to take a string that represents part of a name (or other piece of info) and look for all the matching users.
You get the user to enter the first part of the name of the user they are looking for, and run a search on the users table:
my $userlist = $user_rs->search({
realname => { like => $searchstring . '%' }
});NB: We're using an SQL keyword LIKE here, which does a search using wildcards, % means any number of characters, _ can be used to match any one character.
NB2: In some database systems, LIKE matches case-insensitively, and in some it doesn't, look it up in your database manual to confirm.
The result of a search is not a single row, but a set of rows, a ResultSet. The resultset can be iterated over to retrieve all the results and output them:
while( my $user = $userlist->next() ) {
print "Search matched user: ", $user->realname, "\n";
}NB: In list context search() returns a list of the matching row objects, not a ResultSet.
The resultset keeps track of which row you looked at last, so next always returns the next one, starting with the first, and returns undef when it gets to the end.
Note that next returns a single DBIx::Class::Row object, just like find did.
We didn't specify a sort order, so the rows will come out in a random-looking order. If we want to sort them, we can add a order_by attribute to the search:
my $userlist = $user_rs->search({
realname => { like => $searchstring . '%' }
}, {
order_by => 'realname',
});For more information on the search method, look in "search" in DBIx::Class::ResultSet. The possible attributes like order_by are listed in "ATTRIBUTES" in DBIx::Class::ResultSet.
Now that we've found the user again, we can let them change their email address, or their password. We can easily show them the existing values, using the accessors, and retrieve new values to update the database with.
To set those values, we just call the accessors on the $user object and call update:
$user->email($new_email_address);
$user->password($new_password);
$user->update();NB: Don't forget to rehash the password, and present the user with two password fields to make sure they don't mistype it.
NB2: Verify the new email address too, by sending them an email they must respond to.
The update method issues an SQL UPDATE statement, setting the new column values, using the primary key to match the row.
The update is only done if any of the column values have changed since the last update or creation of the object.
This example covers all the techniques outlined above, in a straight forward command-line based program. To prompt the user for information, we're using Term::Prompt. Install it using cpan Term::Prompt in your console.
/examples/manipulating_a_user.pl
You can now create new rows in the database tables, find them again and change their values.
Enhancing users and making posts
Patches and suggestions welcome.
Jess Robinson <castaway@desert-island.me.uk>
Last modified: