Other pages

Things I like

Start
Contents
Prev
Next

INSERTing data

INSERT INTO users (id, username, dob, realname, password) 
VALUES (1, 'fredbloggs', '1910-02-01', 'Fred Bloggs', 'secretpass');

Simple insertion, populating rows

The populate method is for inserting chunks of data to pre-populate / initialise a database with a set of known values. In void context it uses DBI's fast "insert_bulk" method.

In scalar or list context populate is a proxy to the create method (on which more below), and returns Row objects.

  1. Create a Schema object representing the database you are working with:

    my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');

  2. Call the populate method for the [Source] you wish to insert data into:

    $schema->populate('User', [
              [ qw/id username, dob, realname, password/ ],
              [ 1, 'fredbloggs', '1910-02-01', 
                    'Fred Bloggs', 'secretpass'],
              ]);

Note that in void context you can skip passing primary key values that will be supplied by the database. However no automatic assignment of foreign key values will be made, nor will any code in your Result classes be run (eg InflateColumn components).

Inserting with Row objects

INSERT INTO users (username, dob, realname, password) 
VALUES ('fredbloggs', '1910-02-01', 'Fred Bloggs', 'secretpass');

In the course of your application, you will often want to retrieve data from a user, insert it into the database, then continue to use or manipulate the resulting object.

  1. Create a Schema object:

    my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');

(ideally you will always have one of these handy, no need to make many connections to the database)

  1. Create a User object:

    my $newuser = $schema->resultset('User')->new({ 
  username  => 'fredbloggs',
  dob       => '1910-02-01',
  realname  => 'Fred Bloggs',
  password  => 'secretpass',
});

    $newuser is now a DBIx::Class::Row object, containing uninserted data. This can be verified by calling $newuser->in_storage, which will return false.

  2. Insert the users data into the database:

    $newuser->insert();

    $newuser has been updated to contain the auto-incremented id value in its primary key field (id). $newuser->in_storage now returns true.

You can also shortcut these two methods if you don't need to build up the Row object before inserting:

    ## new+insert in one
    my $newuser = $schema->resultset('User')->create({ 
      username  => 'fredbloggs',
      dob       => '1910-02-01',
      realname  => 'Fred Bloggs',
      password  => 'secretpass',
    });

Now $newuser is a Row object containing data that represents what is in the database.

Add a post for this user

INSERT INTO posts (user_id, created_date, title, post)
VALUES (1, '2010-03-24T09:00:00', 'First post!', 'Just testing my blog');

Now that we have a user, they would like to submit their first blog post. We already have the user object, from creation, or from the session when they logged in, so we can create posts using it.

  1. Add a post for an existing user:

    ## Remember, create == new and insert.
my $post = $user->create_related('posts', {
  created_date => '2010-03-24T09:00:00',
  title        => 'First post!',
  post         => 'Just testing my blog',
});

This does not require us to dig out the user's database id and pass it to the insert call for the posts table, as it is already contained in the User object.

Insert a new user and their first post

INSERT INTO users (username, dob, realname, password) 
VALUES ('fredbloggs', '1910-02-01', 'Fred Bloggs', 'secretpass');

INSERT INTO posts (user_id, created_date, title, post)
VALUES (1, '2010-03-24T09:00:00', 'First post!', 'Just testing my blog');

This is a somewhat contrived example, as hopefully you'll want to create the user, and confirm who they are via email confirmation or similar, before allowing them to submit a blog post. Maybe it can be used for commenters and comments..

  1. Create a Schema object:

    my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');

(You still have one of these, right?)

  1. Collect the User and first Post data:

    my $user_and_post = { 
  username  => 'fredbloggs',
  dob       => '1910-02-01',
  realname  => 'Fred Bloggs',
  password  => 'secretpass',
  posts     => [ {
     created_date => '2010-03-24T09:00:00',
     title        => 'First post!',
     post         => 'Just testing my blog',
  } ],
});

  2. Create the User object together with the post data:

    my $newuser = $schema->resultset('User')->new( $user_and_post );

  3. Insert the user and post data into the database:

    $newuser->insert();

This also can be shortcut using create:

    ## new+insert in one
    my $newuser = $schema->resultset('User')->create( $user_and_post );

Insert using a SELECT as input:

INSERT INTO users (id, username, dob, realname, password) 
SELECT (1, 'fredbloggs', '1910-02-01', 'Fred Bloggs', 'secretpass')
FROM nowhere;

Last modified: 2012-02-06T12:19:11

Home