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 ### complete paragraph rewording suggestion
  28 ###
  29 To use DBIx::Class, we need to teach it about the layout of the underlying database. Several methods of doing this are available.
  30 If you have an existing database the most straightforward way is to use the module L<DBIx::Class::Schema::Loader>, which
  31 will introspect your database and generate individual classes representing every table and view in your database.
  32 For new projects one usually writes these classes by hand as described below. If you find the methods provided by
  33 L<DBIx::Class> core overly verbose, you can try to define your result classes via the more concise syntax of
  34 L<DBIx::Class::Candy> (the result is fully compatible with L<DBIx::Class>).
  35
  36 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>.
  37
  38 =head2 Using Loader
  39
  40 Install L<DBIx::Class::Schema::Loader> and decide on a name for your schema classes.
  41
  42 Run the included L<dbicdump> script.
  43
  44     dbicdump -o dump_directory=./lib \
  45       -o components='["InflateColumn::DateTime"]' \
  46       -o preserve_case=1 \
  47       MyApp::Schema dbi:mysql:database=foo user pass '{ quote_names => 1 }'
  48
  49
  50 =head2 Manual Result class creation (and understanding Loader results)
  51
  52 # note - CREATE INDEX is a bitch these days (requires a deploy hook) - perhaps not mentioning it at all is wise-ish?
  53 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.
  54
  55 =head3 CREATE TABLE
  56
  57 =head4 Standard basic table creation in SQL:
  58
  59     CREATE TABLE users (
  60        id INTEGER AUTO_INCREMENT,
  61        username VARCHAR(255),
  62        dob DATE,
  63        realname VARCHAR(255),
  64        password VARCHAR(255)
  65     );
  66
  67 We'll get to tables with references (foreign keys) later, here's the translation to DBIx::Class:
  68
  69 The recommended version:
  70
  71     package MyDatabase::Schema::Result::User;
  72     use strict;
  73     use warnings;
  74
  75     use base 'DBIx::Class::Core';
  76
  77     __PACKAGE__->table('users');
  78     __PACKAGE__->add_columns(
  79       id => {
  80         data_type => 'integer',
  81         is_auto_increment => 1,
  82       },
  83       username => {
  84         data_type => 'varchar',
  85         size => 255,
  86       },
  87       dob => {
  88         data_type => 'date',
  89       },
  90       realname => {
  91         data_type => 'varchar',
  92         size => 255,
  93       },
  94       password => {
  95         data_type => 'varchar',
  96         size => 255,
  97       },
  98      );
  99      __PACKAGE__->set_primary_key('id');
 100      __PACKAGE__->add_unique_constraint('uniq_username' => ['username']);
 101      1;
 102
 103 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.
 104
 105 # perhaps "... with declared relations / Declaring relationships" ? "references" doesn't sound right in the context imho
 106 =head4 Table creation with references:
 107
 108 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.
 109
 110     CREATE TABLE posts (
 111       id INTEGER AUTO_INCREMENT,
 112       user_id INTEGER,
 113       created_date DATETIME,
 114       title VARCHAR(255),
 115       post TEXT,
 116       INDEX posts_idx_user_id (user_id),
 117       PRIMARY KEY (id),
 118       CONSTRAINT posts_fk_user_id FOREIGN KEY (user_id) REFERENCES users (id)
 119     );
 120
 121 In DBIx::Class this is achieved by adding L<relationship|DBIx::Class::Relationship> definitions to the class:
 122
 123     package MyDatabase::Schema::Result::Post;
 124     use strict;
 125     use warnings;
 126     use base 'DBIx::Class::Core';
 127
 128     __PACKAGE__->table('posts');
 129     __PACKAGE__->add_columns(
 130         id => {
 131             data_type => 'integer',
 132             is_auto_increment => 1,
 133         },
 134         user_id => {
 135           data_type => 'integer',
 136         },
 137         created_date => {
 138           data_type => 'datetime',
 139         },
 140         title => {
 141           data_type => 'varchar',
 142           size => 255,
 143         },
 144         post => {
 145           data_type => 'text',
 146         },
 147      );
 148
 149     __PACKAGE__->set_primary_key('id');
 150     __PACKAGE__->belongs_to('user', 'MyDatabase::Schema::Result::User', 'user_id');
 151     1;
 152
 153 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.
 154
 155 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.
 156
 157 Each relationship declaration in DBIC is one-way only.
 158 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:
 159
 160     __PACKAGE__->has_many('posts', 'MyDatabase::Schema::Result::Post', 'user_id');
 161
 162 =head3 CREATE VIEW
 163
 164 In SQL, a simple view that returns all users and their posts:
 165
 166     CREATE VIEW userposts
 167     AS
 168     SELECT posts.user_id, users.username, users.dob, users.realname, posts.createddate, posts.title, posts.post
 169     FROM users
 170     JOIN posts ON (users.id = posts.user_id)
 171
 172 In DBIx::Class this can have a Result Class of its own:
 173
 174     package MyDatabase::Schema::Result::UserPosts;
 175
 176     use base qw/DBIx::Class::Core/;
 177
 178     # Defaults to 'DBIx::Class::ResultSource::Table' unless specified like this
 179     __PACKAGE__->table_class('DBIx::Class::ResultSource::View');
 180
 181     __PACKAGE__->table('user_posts');
 182
 183     # Do not emit SQL DDL for this particular resultsource
 184     __PACKAGE__->result_source_instance->is_virtual(1);
 185
 186     __PACKAGE__->result_source_instance->view_definition(
 187     "SELECT posts.user_id, users.username, users.dob, users.realname, posts.createddate, posts.title, posts.post
 188     FROM users
 189     JOIN posts ON (users.id = posts.user_id)"
 190     );
 191     __PACKAGE__->add_columns(
 192         user_id => {
 193           data_type => 'integer',
 194         },
 195       username => {
 196         data_type => 'varchar',
 197         size => 255,
 198       },
 199       dob => {
 200         data_type => 'date',
 201       },
 202       realname => {
 203         data_type => 'varchar',
 204         size => 255,
 205       },
 206       created_date => {
 207         data_type => 'datetime',
 208       },
 209       title => {
 210         data_type => 'varchar',
 211         size => 255,
 212       },
 213       post => {
 214         data_type => 'text',
 215       },
 216
 217     );
 218     __PACKAGE__->set_primary_key('user_id, post_id');
 219
 220 =head3 CREATE INDEX
 221
 222 =head4 UNIQUE indexes
 223
 224     CREATE UNIQUE INDEX username_idx ON user (username);
 225
 226 To add extra unique indexes, add the B<add_unique_constraint> call to your Result Class.
 227
 228     __PACKAGE__->add_unique_constraint('username_idx' => ['username']);
 229
 230 =head3 Outputting SQL DDL
 231
 232 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.
 233
 234 =head4 Deploy directly to the database
 235
 236 Create a schema object with the correct database connection, then call B<deploy> on it.
 237
 238     my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');
 239     $schema->deploy({add_drop_table => 1});
 240
 241 L<DBIx::Class::Schema/deploy> has the documentation for the deploy method.
 242
 243 =head4 Write out SQL files
 244
 245 Create a schema object with the a database connection (any will do), and call the B<create_ddl_dir> method on it.
 246
 247     my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');
 248     $schema->create_ddl_dir(['SQLite', 'MySQL']);
 249
 250 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.
 251
 252 ### IIRC this is not true - one can not do diffs without Schema::Versioned
 253 ### which is not loaded by default (and will soon be deprecated anyway, given how far frew and jnap have gone)
 254
 255 =head4 SQL files for upgrades (ALTER TABLE)
 256
 257 DBIC can also make use of L<SQL::Translator::Diff> to write out ALTER TABLE statements when the schema classes are changed.
 258
 259 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.
 260
 261 After the schema has changed, change the B<$VERSION> value and re-run B<create_ddl_dir>.
 262
 263     my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');
 264     $schema->create_ddl_dir(\@databases, undef, '.', '0.1');