[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

### complete paragraph rewording suggestion
###
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?
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
=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']);

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

=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
2f41b1a9	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>).
6c2a4396	35
cd1e0628	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>.
6c2a4396	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
cd1e0628	44	dbicdump -o dump_directory=./lib \
6c2a4396	45	-o components='["InflateColumn::DateTime"]' \
6c2a4396	46	-o preserve_case=1 \
cd1e0628	47	MyApp::Schema dbi:mysql:database=foo user pass '{ quote_names => 1 }'
6c2a4396	48
	49
	50	=head2 Manual Result class creation (and understanding Loader results)
	51
2f41b1a9	52	# note - CREATE INDEX is a bitch these days (requires a deploy hook) - perhaps not mentioning it at all is wise-ish?
cd1e0628	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.
cd1e0628	54
6c2a4396	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
2f41b1a9	105	# perhaps "... with declared relations / Declaring relationships" ? "references" doesn't sound right in the context imho
6c2a4396	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
2f41b1a9	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.
cd1e0628	154
cd1e0628	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.
6c2a4396	156
2f41b1a9	157	Each relationship declaration in DBIC is one-way only.
6c2a4396	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
cd1e0628	178	# Defaults to 'DBIx::Class::ResultSource::Table' unless specified like this
6c2a4396	179	__PACKAGE__->table_class('DBIx::Class::ResultSource::View');
	180
	181	__PACKAGE__->table('user_posts');
cd1e0628	182
cd1e0628	183	# Do not emit SQL DDL for this particular resultsource
6c2a4396	184	__PACKAGE__->result_source_instance->is_virtual(1);
cd1e0628	185
6c2a4396	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
cd1e0628	230	=head3 Outputting SQL DDL
6c2a4396	231
cd1e0628	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.
6c2a4396	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
cd1e0628	245	Create a schema object with the a database connection (any will do), and call the B<create_ddl_dir> method on it.
6c2a4396	246
	247	my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');
	248	$schema->create_ddl_dir(['SQLite', 'MySQL']);
	249
cd1e0628	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.
6c2a4396	251
2f41b1a9	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
6c2a4396	255	=head4 SQL files for upgrades (ALTER TABLE)
6c2a4396	256
cd1e0628	257	DBIC can also make use of L<SQL::Translator::Diff> to write out ALTER TABLE statements when the schema classes are changed.
6c2a4396	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');