[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)

This section covers the common and oft used CREATE DDL statements that DBIx::Class can replace 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.

=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('userposts');

    # 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']);

=head4 NON-UNIQUE indexes

    CREATE INDEX user_email_idx ON user (username, email);

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.

The B<sqlt_deploy_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.

   sub sqlt_deploy_hook {
      my ($self, $sqlt_table) = @_;

      $sqlt_table->add_index(name => 'user_email_idx', fields => ['username', 'email']);
   }


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

=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');
Commit	Line	Data
6c2a4396	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
cd1e0628	23	=back
cd1e0628	24
6c2a4396	25	=head1 Database structure
6c2a4396	26
0e8be732	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>.
6c2a4396	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
cd1e0628	48	dbicdump -o dump_directory=./lib \
6c2a4396	49	-o components='["InflateColumn::DateTime"]' \
6c2a4396	50	-o preserve_case=1 \
cd1e0628	51	MyApp::Schema dbi:mysql:database=foo user pass '{ quote_names => 1 }'
6c2a4396	52
	53
	54	=head2 Manual Result class creation (and understanding Loader results)
	55
1060e1bd	56	This section covers the common and oft used CREATE DDL statements that DBIx::Class can replace 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.
cd1e0628	57
6c2a4396	58	=head3 CREATE TABLE
6c2a4396	59
1060e1bd	60	=head4 Standard basic table creation in SQL
6c2a4396	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
1060e1bd	108	=head4 Table creation with references
6c2a4396	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
2f41b1a9	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.
cd1e0628	156
cd1e0628	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.
6c2a4396	158
2f41b1a9	159	Each relationship declaration in DBIC is one-way only.
6c2a4396	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
cd1e0628	180	# Defaults to 'DBIx::Class::ResultSource::Table' unless specified like this
6c2a4396	181	__PACKAGE__->table_class('DBIx::Class::ResultSource::View');
6c2a4396	182
1060e1bd	183	__PACKAGE__->table('userposts');
cd1e0628	184
cd1e0628	185	# Do not emit SQL DDL for this particular resultsource
6c2a4396	186	__PACKAGE__->result_source_instance->is_virtual(1);
cd1e0628	187
6c2a4396	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	);
1060e1bd	220	__PACKAGE__->set_primary_key('user_id', 'post_id');
6c2a4396	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
7d48ee14	232	=head4 NON-UNIQUE indexes
	233
	234	CREATE INDEX user_email_idx ON user (username, email);
	235
1060e1bd	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.
7d48ee14	237
1060e1bd	238	The B<sqlt_deploy_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.
7d48ee14	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
0e8be732	246
cd1e0628	247	=head3 Outputting SQL DDL
6c2a4396	248
cd1e0628	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.
6c2a4396	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
cd1e0628	262	Create a schema object with the a database connection (any will do), and call the B<create_ddl_dir> method on it.
6c2a4396	263
	264	my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');
	265	$schema->create_ddl_dir(['SQLite', 'MySQL']);
	266
cd1e0628	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.
6c2a4396	268
	269	=head4 SQL files for upgrades (ALTER TABLE)
	270
cd1e0628	271	DBIC can also make use of L<SQL::Translator::Diff> to write out ALTER TABLE statements when the schema classes are changed.
6c2a4396	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');