lib/DBIx/Class/Manual/SQLHackers/CREATE.pod

   1 =head1 NAME
   2
   3 DBIx::Class::Manual::SQLHackers::CREATE - DBIx::Class for SQL Hackers - CREATE
   4
   5 =head1 Table of Contents
   6
   7 =over
   8
   9 =item L<Introduction|DBIx::Class::Manual::SQLHackers::Introduction>
  10
  11 =item CREATE
  12
  13 =item L<INSERT|DBIx::Class::Manual::SQLHackers::INSERT>
  14
  15 =item L<SELECT|DBIx::Class::Manual::SQLHackers::SELECT>
  16
  17 =item L<UPDATE|DBIx::Class::Manual::SQLHackers::UPDATE>
  18
  19 =item L<DELETE|DBIx::Class::Manual::SQLHackers::DELETE>
  20
  21 =item L<BEGIN, COMMIT|DBIx::Class::Manual::SQLHackers::Transactions>
  22
  23 =back
  24
  25 =head1 Database structure
  26
  27 To use DBIx::Class, we need to teach it about the layout of the
  28 underlying database. Several methods of doing this are available.  If
  29 you have an existing database the most straightforward way is to use
  30 the module L<DBIx::Class::Schema::Loader>, which will introspect your
  31 database and generate individual classes representing every table and
  32 view in your database.  For new projects one usually writes these
  33 classes by hand as described below. If you find the methods provided
  34 by L<DBIx::Class> core overly verbose, you can try to define your
  35 result classes via the more concise syntax of L<DBIx::Class::Candy>
  36 (the result is fully compatible with L<DBIx::Class>).
  37
  38 Once a DBIx::Class schema (set of classes describing the database) has
  39 been created, built-in methods can be used to export it as SQL DDL
  40 using L<SQL::Translator>.
  41
  42 =head2 Using Loader
  43
  44 Install L<DBIx::Class::Schema::Loader> and decide on a name for your schema classes.
  45
  46 Run the included L<dbicdump> script.
  47
  48     dbicdump -o dump_directory=./lib \
  49       -o components='["InflateColumn::DateTime"]' \
  50       -o preserve_case=1 \
  51       MyApp::Schema dbi:mysql:database=foo user pass '{ quote_names => 1 }'
  52
  53
  54 =head2 Manual Result class creation (and understanding Loader results)
  55
  56 This section covers the common and oft used CREATE DDL statements that DBIx::Class can replaces with Perl classes: B<CREATE TABLE>, B<CREATE VIEW> and B<CREATE INDEX>. The classes can be used to write the actual SQL DDL to the database or disc, if required.
  57
  58 =head3 CREATE TABLE
  59
  60 =head4 Standard basic table creation in SQL:
  61
  62     CREATE TABLE users (
  63        id INTEGER AUTO_INCREMENT,
  64        username VARCHAR(255),
  65        dob DATE,
  66        realname VARCHAR(255),
  67        password VARCHAR(255)
  68     );
  69
  70 We'll get to tables with references (foreign keys) later, here's the translation to DBIx::Class:
  71
  72 The recommended version:
  73
  74     package MyDatabase::Schema::Result::User;
  75     use strict;
  76     use warnings;
  77
  78     use base 'DBIx::Class::Core';
  79
  80     __PACKAGE__->table('users');
  81     __PACKAGE__->add_columns(
  82       id => {
  83         data_type => 'integer',
  84         is_auto_increment => 1,
  85       },
  86       username => {
  87         data_type => 'varchar',
  88         size => 255,
  89       },
  90       dob => {
  91         data_type => 'date',
  92       },
  93       realname => {
  94         data_type => 'varchar',
  95         size => 255,
  96       },
  97       password => {
  98         data_type => 'varchar',
  99         size => 255,
 100       },
 101      );
 102      __PACKAGE__->set_primary_key('id');
 103      __PACKAGE__->add_unique_constraint('uniq_username' => ['username']);
 104      1;
 105
 106 The fully descriptive version is required if you want to have DBIx::Class create your CREATE TABLE sql for you later. Many DBIC components also use settings in the column info hashrefs to decide how to treat the data for those columns.
 107
 108 =head4 Table creation with references:
 109
 110 A relational database isn't worth much if we don't actually use references and constraints, so here is an example which constrains the B<user_id> column to only contain B<id> values from the *users* table.
 111
 112     CREATE TABLE posts (
 113       id INTEGER AUTO_INCREMENT,
 114       user_id INTEGER,
 115       created_date DATETIME,
 116       title VARCHAR(255),
 117       post TEXT,
 118       INDEX posts_idx_user_id (user_id),
 119       PRIMARY KEY (id),
 120       CONSTRAINT posts_fk_user_id FOREIGN KEY (user_id) REFERENCES users (id)
 121     );
 122
 123 In DBIx::Class this is achieved by adding L<relationship|DBIx::Class::Relationship> definitions to the class:
 124
 125     package MyDatabase::Schema::Result::Post;
 126     use strict;
 127     use warnings;
 128     use base 'DBIx::Class::Core';
 129
 130     __PACKAGE__->table('posts');
 131     __PACKAGE__->add_columns(
 132         id => {
 133             data_type => 'integer',
 134             is_auto_increment => 1,
 135         },
 136         user_id => {
 137           data_type => 'integer',
 138         },
 139         created_date => {
 140           data_type => 'datetime',
 141         },
 142         title => {
 143           data_type => 'varchar',
 144           size => 255,
 145         },
 146         post => {
 147           data_type => 'text',
 148         },
 149      );
 150
 151     __PACKAGE__->set_primary_key('id');
 152     __PACKAGE__->belongs_to('user', 'MyDatabase::Schema::Result::User', 'user_id');
 153     1;
 154
 155 The B<belongs_to> relation creates a B<user> method which returns the user object, as well as storing JOIN information to be used when querying with JOINs. When not explicitly specified (as in this example), the columns for the JOIN clause default to the remote PRIMARY KEY column set.
 156
 157 Relationships may also be specified with completely custom JOIN conditions, using any columns, whether the database has them defined as constraints or not, or literal values.
 158
 159 Each relationship declaration in DBIC is one-way only.
 160 To allow access from the B<user> object back to the posts they have written, we need to define another relationship in the User class:
 161
 162     __PACKAGE__->has_many('posts', 'MyDatabase::Schema::Result::Post', 'user_id');
 163
 164 =head3 CREATE VIEW
 165
 166 In SQL, a simple view that returns all users and their posts:
 167
 168     CREATE VIEW userposts
 169     AS
 170     SELECT posts.user_id, users.username, users.dob, users.realname, posts.createddate, posts.title, posts.post
 171     FROM users
 172     JOIN posts ON (users.id = posts.user_id)
 173
 174 In DBIx::Class this can have a Result Class of its own:
 175
 176     package MyDatabase::Schema::Result::UserPosts;
 177
 178     use base qw/DBIx::Class::Core/;
 179
 180     # Defaults to 'DBIx::Class::ResultSource::Table' unless specified like this
 181     __PACKAGE__->table_class('DBIx::Class::ResultSource::View');
 182
 183     __PACKAGE__->table('user_posts');
 184
 185     # Do not emit SQL DDL for this particular resultsource
 186     __PACKAGE__->result_source_instance->is_virtual(1);
 187
 188     __PACKAGE__->result_source_instance->view_definition(
 189     "SELECT posts.user_id, users.username, users.dob, users.realname, posts.createddate, posts.title, posts.post
 190     FROM users
 191     JOIN posts ON (users.id = posts.user_id)"
 192     );
 193     __PACKAGE__->add_columns(
 194         user_id => {
 195           data_type => 'integer',
 196         },
 197       username => {
 198         data_type => 'varchar',
 199         size => 255,
 200       },
 201       dob => {
 202         data_type => 'date',
 203       },
 204       realname => {
 205         data_type => 'varchar',
 206         size => 255,
 207       },
 208       created_date => {
 209         data_type => 'datetime',
 210       },
 211       title => {
 212         data_type => 'varchar',
 213         size => 255,
 214       },
 215       post => {
 216         data_type => 'text',
 217       },
 218
 219     );
 220     __PACKAGE__->set_primary_key('user_id, post_id');
 221
 222 =head3 CREATE INDEX
 223
 224 =head4 UNIQUE indexes
 225
 226     CREATE UNIQUE INDEX username_idx ON user (username);
 227
 228 To add extra unique indexes, add the B<add_unique_constraint> call to your Result Class.
 229
 230     __PACKAGE__->add_unique_constraint('username_idx' => ['username']);
 231
 232 =head4 NON-UNIQUE indexes
 233
 234     CREATE INDEX user_email_idx ON user (username, email);
 235
 236 These are not created or used by DBIx::Class itself, but can be added so that deploying (creating DDL SQL from your Schema) can include them.
 237
 238 The B<sqlt_deply_hook> method allows you to add L<SQL::Translator> code to your Result class. It is called with the SQL::Translator::Schema::Table object, and allows you to amend the Table before it is converted into SQL.
 239
 240    sub sqlt_deploy_hook {
 241       my ($self, $sqlt_table) = @_;
 242
 243       $sqlt_table->add_index(name => 'user_email_idx', fields => ['username', 'email']);
 244    }
 245
 246
 247 =head3 Outputting SQL DDL
 248
 249 Once the DBIC schema has been defined, you can outout the SQL DDL needed to create the schema in your database (using the RDBMS-specific flavor of SQL DDL) in one of several ways.
 250
 251 =head4 Deploy directly to the database
 252
 253 Create a schema object with the correct database connection, then call B<deploy> on it.
 254
 255     my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');
 256     $schema->deploy({add_drop_table => 1});
 257
 258 L<DBIx::Class::Schema/deploy> has the documentation for the deploy method.
 259
 260 =head4 Write out SQL files
 261
 262 Create a schema object with the a database connection (any will do), and call the B<create_ddl_dir> method on it.
 263
 264     my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');
 265     $schema->create_ddl_dir(['SQLite', 'MySQL']);
 266
 267 If called with no arguments, this method will create an SQL file each for MySQL, PostgreSQL and SQLite. More databases are supported by L<SQL::Translator> if necessary.
 268
 269 =head4 SQL files for upgrades (ALTER TABLE)
 270
 271 DBIC can also make use of L<SQL::Translator::Diff> to write out ALTER TABLE statements when the schema classes are changed.
 272
 273 To do this, make sure that you set a B<$VERSION> in your main Schema class, and run B<create_ddl_dir> on the initial version  to provide a baseline.
 274
 275 After the schema has changed, change the B<$VERSION> value and re-run B<create_ddl_dir>.
 276
 277     my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');
 278     $schema->create_ddl_dir(\@databases, undef, '.', '0.1');