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 underlying database. Several methods are supported. The built-in method involves defining your database structure as a set of perl classes, one per resultsource (table, view, etc). The other oft used method is to import the definitions from an existing database using the module
  28 L<DBIx::Class::Schema::Loader>.
  29
  30 Once a DBIx::Class schema (set of classes describing the database) has been created, built-in methods can be used to export it as SQL DDL using L<SQL::Translator>.
  31
  32 =head2 Using Loader
  33
  34 Install L<DBIx::Class::Schema::Loader> and decide on a name for your schema classes.
  35
  36 Run the included L<dbicdump> script.
  37
  38     dbicdump -o dump_directory=./lib \
  39       -o components='["InflateColumn::DateTime"]' \
  40       -o preserve_case=1 \
  41       MyApp::Schema dbi:mysql:database=foo user pass '{ quote_names => 1 }'
  42
  43
  44 =head2 Manual Result class creation (and understanding Loader results)
  45
  46 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.
  47
  48 =head3 CREATE TABLE
  49
  50 =head4 Standard basic table creation in SQL:
  51
  52     CREATE TABLE users (
  53        id INTEGER AUTO_INCREMENT,
  54        username VARCHAR(255),
  55        dob DATE,
  56        realname VARCHAR(255),
  57        password VARCHAR(255)
  58     );
  59
  60 We'll get to tables with references (foreign keys) later, here's the translation to DBIx::Class:
  61
  62 The recommended version:
  63
  64     package MyDatabase::Schema::Result::User;
  65     use strict;
  66     use warnings;
  67
  68     use base 'DBIx::Class::Core';
  69
  70     __PACKAGE__->table('users');
  71     __PACKAGE__->add_columns(
  72       id => {
  73         data_type => 'integer',
  74         is_auto_increment => 1,
  75       },
  76       username => {
  77         data_type => 'varchar',
  78         size => 255,
  79       },
  80       dob => {
  81         data_type => 'date',
  82       },
  83       realname => {
  84         data_type => 'varchar',
  85         size => 255,
  86       },
  87       password => {
  88         data_type => 'varchar',
  89         size => 255,
  90       },
  91      );
  92      __PACKAGE__->set_primary_key('id');
  93      __PACKAGE__->add_unique_constraint('uniq_username' => ['username']);
  94      1;
  95
  96 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.
  97
  98 =head4 Table creation with references:
  99
 100 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.
 101
 102     CREATE TABLE posts (
 103       id INTEGER AUTO_INCREMENT,
 104       user_id INTEGER,
 105       created_date DATETIME,
 106       title VARCHAR(255),
 107       post TEXT,
 108       INDEX posts_idx_user_id (user_id),
 109       PRIMARY KEY (id),
 110       CONSTRAINT posts_fk_user_id FOREIGN KEY (user_id) REFERENCES users (id)
 111     );
 112
 113 In DBIx::Class this is achieved by adding L<relationship|DBIx::Class::Relationship> definitions to the class:
 114
 115     package MyDatabase::Schema::Result::Post;
 116     use strict;
 117     use warnings;
 118     use base 'DBIx::Class::Core';
 119
 120     __PACKAGE__->table('posts');
 121     __PACKAGE__->add_columns(
 122         id => {
 123             data_type => 'integer',
 124             is_auto_increment => 1,
 125         },
 126         user_id => {
 127           data_type => 'integer',
 128         },
 129         created_date => {
 130           data_type => 'datetime',
 131         },
 132         title => {
 133           data_type => 'varchar',
 134           size => 255,
 135         },
 136         post => {
 137           data_type => 'text',
 138         },
 139      );
 140
 141     __PACKAGE__->set_primary_key('id');
 142     __PACKAGE__->belongs_to('user', 'MyDatabase::Schema::Result::User', 'user_id');
 143     1;
 144
 145 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. The joined colums on the remote side are taken from the remote PRIMARY KEY value, if not specified, as in this example.
 146
 147 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.
 148
 149 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:
 150
 151     __PACKAGE__->has_many('posts', 'MyDatabase::Schema::Result::Post', 'user_id');
 152
 153 =head3 CREATE VIEW
 154
 155 In SQL, a simple view that returns all users and their posts:
 156
 157     CREATE VIEW userposts
 158     AS
 159     SELECT posts.user_id, users.username, users.dob, users.realname, posts.createddate, posts.title, posts.post
 160     FROM users
 161     JOIN posts ON (users.id = posts.user_id)
 162
 163 In DBIx::Class this can have a Result Class of its own:
 164
 165     package MyDatabase::Schema::Result::UserPosts;
 166
 167     use base qw/DBIx::Class::Core/;
 168
 169     # Defaults to 'DBIx::Class::ResultSource::Table' unless specified like this
 170     __PACKAGE__->table_class('DBIx::Class::ResultSource::View');
 171
 172     __PACKAGE__->table('user_posts');
 173
 174     # Do not emit SQL DDL for this particular resultsource
 175     __PACKAGE__->result_source_instance->is_virtual(1);
 176
 177     __PACKAGE__->result_source_instance->view_definition(
 178     "SELECT posts.user_id, users.username, users.dob, users.realname, posts.createddate, posts.title, posts.post
 179     FROM users
 180     JOIN posts ON (users.id = posts.user_id)"
 181     );
 182     __PACKAGE__->add_columns(
 183         user_id => {
 184           data_type => 'integer',
 185         },
 186       username => {
 187         data_type => 'varchar',
 188         size => 255,
 189       },
 190       dob => {
 191         data_type => 'date',
 192       },
 193       realname => {
 194         data_type => 'varchar',
 195         size => 255,
 196       },
 197       created_date => {
 198         data_type => 'datetime',
 199       },
 200       title => {
 201         data_type => 'varchar',
 202         size => 255,
 203       },
 204       post => {
 205         data_type => 'text',
 206       },
 207
 208     );
 209     __PACKAGE__->set_primary_key('user_id, post_id');
 210
 211 =head3 CREATE INDEX
 212
 213 =head4 UNIQUE indexes
 214
 215     CREATE UNIQUE INDEX username_idx ON user (username);
 216
 217 To add extra unique indexes, add the B<add_unique_constraint> call to your Result Class.
 218
 219     __PACKAGE__->add_unique_constraint('username_idx' => ['username']);
 220
 221 =head3 Outputting SQL DDL
 222
 223 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.
 224
 225 =head4 Deploy directly to the database
 226
 227 Create a schema object with the correct database connection, then call B<deploy> on it.
 228
 229     my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');
 230     $schema->deploy({add_drop_table => 1});
 231
 232 L<DBIx::Class::Schema/deploy> has the documentation for the deploy method.
 233
 234 =head4 Write out SQL files
 235
 236 Create a schema object with the a database connection (any will do), and call the B<create_ddl_dir> method on it.
 237
 238     my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');
 239     $schema->create_ddl_dir(['SQLite', 'MySQL']);
 240
 241 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.
 242
 243 =head4 SQL files for upgrades (ALTER TABLE)
 244
 245 DBIC can also make use of L<SQL::Translator::Diff> to write out ALTER TABLE statements when the schema classes are changed.
 246
 247 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.
 248
 249 After the schema has changed, change the B<$VERSION> value and re-run B<create_ddl_dir>.
 250
 251     my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');
 252     $schema->create_ddl_dir(\@databases, undef, '.', '0.1');