3 DBIx::Class::Manual::SQLHackers::INSERT - DBIx::Class for SQL Hackers - INSERT
7 =item L<Introduction|DBIx::Class::Manual::SQLHackers::Introduction>
9 =item L<CREATE|DBIx::Class::Manual::SQLHackers::CREATE>
13 =item L<SELECT|DBIx::Class::Manual::SQLHackers::SELECT>
15 =item L<UPDATE|DBIx::Class::Manual::SQLHackers::UPDATE>
17 =item L<DELETE|DBIx::Class::Manual::SQLHackers::DELETE>
19 =item L<BEGIN, COMMIT|DBIx::Class::Manual::SQLHackers::Transactions>
25 INSERT INTO users (id, username, dob, realname, password)
26 VALUES (1, 'fredbloggs', '1910-02-01', 'Fred Bloggs', 'secretpass');
28 =head2 Simple bulk insertion, populating rows
30 The B<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 optimized "execute_for_fetch" method.
32 In scalar or list context populate is a proxy to the B<create> method (on which more below), and returns Row objects.
36 =item 1. Create a Schema object representing the database you are working with:
38 my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');
40 =item 2. Call the B<populate> method for the ResultSource you wish to insert data into:
42 $schema->populate('User', [
43 [qw(id username dob realname password )],
44 [ 1, 'fredbloggs', '1910-02-01', 'Fred Bloggs', 'secretpass' ],
49 Note that in void context you can skip passing primary key values that will be supplied by the database, and any other values that are allowed to DEFAULT. However no code in your Result classes will be run (eg InflateColumn components).
51 # perhaps "Constructing and inserting Row objects" ?
52 =head2 Inserting with Row objects
54 INSERT INTO users (username, dob, realname, password)
55 VALUES ('fredbloggs', '1910-02-01', 'Fred Bloggs', 'secretpass');
57 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 (the object represents the state of the corresponding database row immediately after creation)
61 # perhaps s/Create/Obtain/ ?
62 =item 1. Create a Schema object:
64 my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');
66 (ideally you will always have one of these handy, no need to make many connections to the database)
68 # perhaps s/Create/Obtain/ ?
69 =item 2. Create a User object:
71 my $newuser = $schema->resultset('User')->new({
72 username => 'fredbloggs',
74 realname => 'Fred Bloggs',
75 password => 'secretpass',
78 $newuser is now a DBIx::Class::Row object, containing uninserted data. This can be verified by calling $newuser->in_storage, which will return false.
80 =item 3. Insert the users data into the database:
84 $newuser has been updated to contain the auto-incremented id value in its primary key field (id). $newuser->in_storage now returns true.
86 You can also shortcut these two methods if you don't need to build up the Row object before inserting:
89 my $newuser = $schema->resultset('User')->create({
90 username => 'fredbloggs',
92 realname => 'Fred Bloggs',
93 password => 'secretpass',
96 Now *$newuser* is a Row object containing data that represents what is in the database.
100 =head2 Add a post for this user
102 INSERT INTO posts (user_id, created_date, title, post)
103 VALUES (1, '2010-03-24T09:00:00', 'First post!', 'Just testing my blog');
105 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.
109 =item 1. Add a post for an existing user:
111 ## Remember, create == new and insert.
112 my $post = $user->create_related('posts', {
113 created_date => '2010-03-24T09:00:00',
114 title => 'First post!',
115 post => 'Just testing my blog',
120 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.
122 =head2 Insert a new user and their first post
124 INSERT INTO users (username, dob, realname, password)
125 VALUES ('fredbloggs', '1910-02-01', 'Fred Bloggs', 'secretpass');
127 INSERT INTO posts (user_id, created_date, title, post)
128 VALUES (1, '2010-03-24T09:00:00', 'First post!', 'Just testing my blog');
130 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..
134 =item 1. Create a Schema object:
136 my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');
138 (You still have one of these, right?)
140 =item 2. Collect the User and first Post data:
142 my $user_and_post = {
143 username => 'fredbloggs',
145 realname => 'Fred Bloggs',
146 password => 'secretpass',
148 created_date => '2010-03-24T09:00:00',
149 title => 'First post!',
150 post => 'Just testing my blog',
154 =item 3. Create the User object together with the post data:
156 my $newuser = $schema->resultset('User')->new( $user_and_post );
158 =item 4. Insert the user and post data into the database:
162 This also can be shortcut using B<create>:
165 my $newuser = $schema->resultset('User')->create( $user_and_post );
169 ### mattp is slowly working on this, do we need to mention it at all?
171 =head2 Insert using a SELECT as input:
173 INSERT INTO users (id, username, dob, realname, password)
174 SELECT (1, 'fredbloggs', '1910-02-01', 'Fred Bloggs', 'secretpass')
177 This is a TODO item for DBIC.