Notes and updates based on riba's most recent comments

[dbsrgits/DBIx-Class-Manual-SQLHackers.git] / lib / DBIx / Class / Manual / SQLHackers / CREATE.pod

=head1 NAME

DBIx::Class::Manual::SQLHackers::CREATE - DBIx::Class for SQL Hackers - CREATE

=head1 Table of Contents

=over

=item L<Introduction|DBIx::Class::Manual::SQLHackers::Introduction>

=item CREATE

=item L<INSERT|DBIx::Class::Manual::SQLHackers::INSERT>

=item L<SELECT|DBIx::Class::Manual::SQLHackers::SELECT>

=item L<UPDATE|DBIx::Class::Manual::SQLHackers::UPDATE>

=item L<DELETE|DBIx::Class::Manual::SQLHackers::DELETE>

=item L<BEGIN, COMMIT|DBIx::Class::Manual::SQLHackers::Transactions>

=back

=head1 Database structure

To use DBIx::Class, we need to teach it about the layout of the
underlying database. Several methods of doing this are available.  If
you have an existing database the most straightforward way is to use
the module L<DBIx::Class::Schema::Loader>, which will introspect your
database and generate individual classes representing every table and
view in your database.  For new projects one usually writes these
classes by hand as described below. If you find the methods provided
by L<DBIx::Class> core overly verbose, you can try to define your
result classes via the more concise syntax of L<DBIx::Class::Candy>
(the result is fully compatible with L<DBIx::Class>).

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>.

=head2 Using Loader 

Install L<DBIx::Class::Schema::Loader> and decide on a name for your schema classes.

Run the included L<dbicdump> script.

    dbicdump -o dump_directory=./lib \
      -o components='["InflateColumn::DateTime"]' \
      -o preserve_case=1 \
      MyApp::Schema dbi:mysql:database=foo user pass '{ quote_names => 1 }'


=head2 Manual Result class creation (and understanding Loader results)

# note - CREATE INDEX is a bitch these days (requires a deploy hook) - perhaps not mentioning it at all is wise-ish?
# Leaving in as it is possible, and anyway the only index I mentioned so far below was a unique one (really should add the deploy hook stuff, this is about describing the possible, not the simple)
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.

=head3 CREATE TABLE

=head4 Standard basic table creation in SQL:

    CREATE TABLE users (
       id INTEGER AUTO_INCREMENT,
       username VARCHAR(255),
       dob DATE,
       realname VARCHAR(255),
       password VARCHAR(255)
    );

We'll get to tables with references (foreign keys) later, here's the translation to DBIx::Class:

The recommended version:

    package MyDatabase::Schema::Result::User;
    use strict;
    use warnings;

    use base 'DBIx::Class::Core';

    __PACKAGE__->table('users');
    __PACKAGE__->add_columns(
      id => {
        data_type => 'integer',
        is_auto_increment => 1,
      },
      username => {
        data_type => 'varchar',
        size => 255,
      },
      dob => {
        data_type => 'date',
      },
      realname => {
        data_type => 'varchar',
        size => 255,
      },
      password => {
        data_type => 'varchar',
        size => 255,
      },
     );
     __PACKAGE__->set_primary_key('id');
     __PACKAGE__->add_unique_constraint('uniq_username' => ['username']);
     1;

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.

# perhaps "... with declared relations / Declaring relationships" ? "references" doesn't sound right in the context imho
# in SQL land a relation == a table, since I'm talking to SQL heads I don't want to get that wrong.
=head4 Table creation with references:

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.

    CREATE TABLE posts (
      id INTEGER AUTO_INCREMENT,
      user_id INTEGER,
      created_date DATETIME,
      title VARCHAR(255),
      post TEXT,
      INDEX posts_idx_user_id (user_id),
      PRIMARY KEY (id),
      CONSTRAINT posts_fk_user_id FOREIGN KEY (user_id) REFERENCES users (id)
    );

In DBIx::Class this is achieved by adding L<relationship|DBIx::Class::Relationship> definitions to the class:

    package MyDatabase::Schema::Result::Post;
    use strict;
    use warnings;
    use base 'DBIx::Class::Core';

    __PACKAGE__->table('posts');
    __PACKAGE__->add_columns(
        id => {
            data_type => 'integer',
            is_auto_increment => 1,
        },
        user_id => {
          data_type => 'integer',
        },
        created_date => {
          data_type => 'datetime',
        },
        title => {
          data_type => 'varchar',
          size => 255,
        },
        post => {
          data_type => 'text',
        },
     );

    __PACKAGE__->set_primary_key('id');
    __PACKAGE__->belongs_to('user', 'MyDatabase::Schema::Result::User', 'user_id');
    1;

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.

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.

Each relationship declaration in DBIC is one-way only.
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:

    __PACKAGE__->has_many('posts', 'MyDatabase::Schema::Result::Post', 'user_id');

=head3 CREATE VIEW

In SQL, a simple view that returns all users and their posts:

    CREATE VIEW userposts 
    AS
    SELECT posts.user_id, users.username, users.dob, users.realname, posts.createddate, posts.title, posts.post
    FROM users 
    JOIN posts ON (users.id = posts.user_id)

In DBIx::Class this can have a Result Class of its own:

    package MyDatabase::Schema::Result::UserPosts;

    use base qw/DBIx::Class::Core/;

    # Defaults to 'DBIx::Class::ResultSource::Table' unless specified like this
    __PACKAGE__->table_class('DBIx::Class::ResultSource::View');

    __PACKAGE__->table('user_posts');

    # Do not emit SQL DDL for this particular resultsource
    __PACKAGE__->result_source_instance->is_virtual(1);

    __PACKAGE__->result_source_instance->view_definition(
    "SELECT posts.user_id, users.username, users.dob, users.realname, posts.createddate, posts.title, posts.post
    FROM users 
    JOIN posts ON (users.id = posts.user_id)"
    );
    __PACKAGE__->add_columns(
        user_id => {
          data_type => 'integer',
        },
      username => {
        data_type => 'varchar',
        size => 255,
      },
      dob => {
        data_type => 'date',
      },
      realname => {
        data_type => 'varchar',
        size => 255,
      },
      created_date => {
        data_type => 'datetime',
      },
      title => {
        data_type => 'varchar',
        size => 255,
      },
      post => {
        data_type => 'text',
      },

    );
    __PACKAGE__->set_primary_key('user_id, post_id');

=head3 CREATE INDEX

=head4 UNIQUE indexes

    CREATE UNIQUE INDEX username_idx ON user (username);

To add extra unique indexes, add the B<add_unique_constraint> call to your Result Class.

    __PACKAGE__->add_unique_constraint('username_idx' => ['username']);

## Should add how to create a non-unique index via deploy hook here.

=head3 Outputting SQL DDL

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.

=head4 Deploy directly to the database

Create a schema object with the correct database connection, then call B<deploy> on it.

    my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');
    $schema->deploy({add_drop_table => 1});

L<DBIx::Class::Schema/deploy> has the documentation for the deploy method.

=head4 Write out SQL files

Create a schema object with the a database connection (any will do), and call the B<create_ddl_dir> method on it.

    my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');
    $schema->create_ddl_dir(['SQLite', 'MySQL']);

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.

### IIRC this is not true - one can not do diffs without Schema::Versioned
### which is not loaded by default (and will soon be deprecated anyway, given how far frew and jnap have gone)
## No thats rubbish, my usual use-case is to not use versioned at all, but have create_ddl_dir output me the diff files, thats where they come from. (unless someone has stuffed that up and I havent noticed..)

=head4 SQL files for upgrades (ALTER TABLE)

DBIC can also make use of L<SQL::Translator::Diff> to write out ALTER TABLE statements when the schema classes are changed.

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.

After the schema has changed, change the B<$VERSION> value and re-run B<create_ddl_dir>.

    my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');
    $schema->create_ddl_dir(\@databases, undef, '.', '0.1');