1package DBIx::Admin::CreateTable; 2 3use strict; 4use warnings; 5 6use Moo; 7 8has db_vendor => 9( 10 is => 'rw', 11 default => sub{return ''}, 12 required => 0, 13); 14 15has dbh => 16( 17 is => 'rw', 18 isa => sub{die "The 'dbh' parameter to new() is mandatory\n" if (! $_[0])}, 19 default => sub{return ''}, 20 required => 0, 21); 22 23has primary_index_name => 24( 25 is => 'rw', 26 default => sub{return {} }, 27 required => 0, 28); 29 30has sequence_name => 31( 32 is => 'rw', 33 default => sub{return {} }, 34 required => 0, 35); 36 37has verbose => 38( 39 is => 'rw', 40 default => sub{return 0}, 41 required => 0, 42); 43 44our $VERSION = '2.11'; 45 46# ----------------------------------------------- 47 48sub BUILD 49{ 50 my($self) = @_; 51 52 $self -> db_vendor(uc $self -> dbh -> get_info(17) ); # SQL_DBMS_NAME. 53 54 print STDERR __PACKAGE__, '. Db vendor ' . $self -> db_vendor . ". \n" if ($self -> verbose); 55 56} # End of BUILD. 57 58# -------------------------------------------------- 59 60sub create_table 61{ 62 my($self, $sql, $arg) = @_; 63 my($table_name) = $sql; 64 $table_name =~ s/^\s*create\s+table\s+([a-z_0-9]+).+$/$1/is; 65 66 $arg = {} if (! defined $arg); 67 $$arg{$table_name} = {} if (! defined $$arg{$table_name}); 68 $$arg{$table_name}{no_sequence} = 0 if (! defined $$arg{$table_name}{no_sequence}); 69 70 if (! $$arg{$table_name}{no_sequence}) 71 { 72 my($sequence_name) = $self -> generate_primary_sequence_name($table_name); 73 74 if ($sequence_name) 75 { 76 my($sql) = "create sequence $sequence_name"; 77 78 $self -> dbh -> do($sql); 79 80 print STDERR __PACKAGE__, ". SQL: $sql. \n" if ($self -> verbose); 81 82 if ($self -> dbh -> errstr() ) 83 { 84 return $self -> dbh -> errstr(); # Failure. 85 } 86 87 print STDERR __PACKAGE__, ". Created sequence '$sequence_name'. \n" if ($self -> verbose); 88 } 89 } 90 91 $self -> dbh -> do($sql); 92 93 print STDERR __PACKAGE__, ". SQL: $sql. \n" if ($self -> verbose); 94 95 if ($self -> dbh -> errstr() ) 96 { 97 return $self -> dbh -> errstr(); # Failure. 98 } 99 100 print STDERR __PACKAGE__, ". Created table '$table_name'. \n" if ($self -> verbose); 101 102 return ''; # Success. 103 104} # End of create_table. 105 106# -------------------------------------------------- 107 108sub drop_table 109{ 110 my($self, $table_name, $arg) = @_; 111 my($sequence_name) = $self -> generate_primary_sequence_name($table_name); 112 113 # Turn off RaiseError so we don't error if the sequence and table being deleted do not exist. 114 # We do this by emulating local $$dbh{RaiseError}. 115 116 my($dbh) = $self -> dbh; 117 my($raise_error) = $$dbh{RaiseError}; 118 $$dbh{RaiseError} = 0; 119 120 $self -> dbh($dbh); 121 122 $arg = {} if (! defined $arg); 123 $$arg{$table_name} = {} if (! defined $$arg{$table_name}); 124 $$arg{$table_name}{no_sequence} = 0 if (! defined $$arg{$table_name}{no_sequence}); 125 126 my($sql); 127 128 # For Oracle, drop the sequence before dropping the table. 129 130 if ( ($self -> db_vendor eq 'ORACLE') && ! $$arg{$table_name}{no_sequence}) 131 { 132 $sql = "drop sequence $sequence_name"; 133 134 $self -> dbh -> do($sql); 135 136 print STDERR __PACKAGE__, ". SQL: $sql. \n" if ($self -> verbose); 137 print STDERR __PACKAGE__, ". Dropped sequence '$sequence_name'. \n" if ($self -> verbose); 138 } 139 140 $sql = "drop table $table_name"; 141 142 $self -> dbh -> do($sql); 143 144 print STDERR __PACKAGE__, ". SQL: $sql. \n" if ($self -> verbose); 145 print STDERR __PACKAGE__, ". Dropped table '$table_name'. \n" if ($self -> verbose); 146 147 # For Postgres, drop the sequence after dropping the table. 148 149 if ( ($self -> db_vendor eq 'POSTGRESQL') && ! $$arg{$table_name}{no_sequence}) 150 { 151 $sql = "drop sequence $sequence_name"; 152 153 $self -> dbh -> do($sql); 154 155 print STDERR __PACKAGE__, ". SQL: $sql. \n" if ($self -> verbose); 156 print STDERR __PACKAGE__, ". Dropped sequence '$sequence_name'. \n" if ($self -> verbose); 157 } 158 159 # Undo local $$dbh{RaiseError}. 160 161 $$dbh{RaiseError} = $raise_error; 162 163 $self -> dbh($dbh); 164 165 return ''; 166 167} # End of drop_table. 168 169# -------------------------------------------------- 170 171sub generate_primary_index_name 172{ 173 my($self, $table_name) = @_; 174 my($hashref) = $self -> primary_index_name; 175 176 if (! $$hashref{$table_name}) 177 { 178 $$hashref{$table_name} = $self -> db_vendor eq 'POSTGRESQL' 179 ? "${table_name}_pkey" 180 : ''; # MySQL, Oracle, SQLite. 181 182 $self -> primary_index_name($hashref); 183 } 184 185 return $$hashref{$table_name}; 186 187} # End of generate_primary_index_name. 188 189# -------------------------------------------------- 190 191sub generate_primary_key_sql 192{ 193 my($self, $table_name) = @_; 194 my($sequence_name) = $self -> generate_primary_sequence_name($table_name); 195 my($primary_key) = 196 ($self -> db_vendor eq 'MYSQL') 197 ? 'integer primary key auto_increment' 198 : ($self -> db_vendor eq 'SQLITE') 199 ? 'integer primary key autoincrement' 200 : $self -> db_vendor eq 'ORACLE' 201 ? 'integer primary key' 202 : "integer primary key default nextval('$sequence_name')"; # Postgres. 203 204 return $primary_key; 205 206} # End of generate_primary_key_sql. 207 208# -------------------------------------------------- 209 210sub generate_primary_sequence_name 211{ 212 my($self, $table_name) = @_; 213 my($hashref) = $self -> sequence_name; 214 215 if (! $$hashref{$table_name}) 216 { 217 $$hashref{$table_name} = $self -> db_vendor =~ /(?:MYSQL|SQLITE)/ 218 ? '' 219 : "${table_name}_id_seq"; # Oracle, Postgres. 220 221 $self -> sequence_name($hashref); 222 } 223 224 return $$hashref{$table_name}; 225 226} # End of generate_primary_sequence_name. 227 228# ----------------------------------------------- 229# Assumption: This code is only called in the case 230# of Oracle and Postgres, and after importing data 231# for all tables from a XML file (say). 232# The mechanism used to import from XML does not 233# activate the sequences because the primary keys 234# are included in the data being imported. 235# So, we have to reset the current values of the 236# sequences up from their default values of 1 to 237# the number of records in the corresponding table. 238# If not, then the next call to nextval() would 239# return a value of 2, which is already in use. 240 241sub reset_all_sequences 242{ 243 my($self, $arg) = @_; 244 245 if ($self -> db_vendor ne 'MYSQL') 246 { 247 $self -> reset_sequence($_, $arg) for keys %{$self -> sequence_name}; 248 } 249 250} # End of reset_all_sequences. 251 252# ----------------------------------------------- 253 254sub reset_sequence 255{ 256 my($self, $table_name, $arg) = @_; 257 258 $arg = {} if (! defined $arg); 259 $$arg{$table_name} = {} if (! defined $$arg{$table_name}); 260 $$arg{$table_name}{no_sequence} = 0 if (! defined $$arg{$table_name}{no_sequence}); 261 262 if (! $$arg{$table_name}{no_sequence}) 263 { 264 my($sequence_name) = $self -> generate_primary_sequence_name($table_name); 265 my($sth) = $self -> dbh -> prepare("select count(*) from $table_name"); 266 267 $sth -> execute(); 268 269 my($max) = $sth -> fetch(); 270 $max = $$max[0] || 0; 271 my($sql) = "select setval('$sequence_name', $max)"; 272 273 $sth -> finish(); 274 $self -> dbh -> do($sql); 275 276 print STDERR __PACKAGE__, ". SQL: $sql. \n" if ($self -> verbose); 277 print STDERR __PACKAGE__, ". Reset table '$table_name', sequence '$sequence_name' to $max. \n" if ($self -> verbose); 278 } 279 280} # End of reset_sequence. 281 282# -------------------------------------------------- 283 2841; 285 286=head1 NAME 287 288DBIx::Admin::CreateTable - Create and drop tables, primary indexes, and sequences 289 290=head1 Synopsis 291 292 #!/usr/bin/env perl 293 294 use strict; 295 use warnings; 296 297 use DBI; 298 use DBIx::Admin::CreateTable; 299 300 # ---------------- 301 302 my($dbh) = DBI -> connect(...); 303 my($creator) = DBIx::Admin::CreateTable -> new(dbh => $dbh, verbose => 1); 304 my($table_name) = 'test'; 305 306 $creator -> drop_table($table_name); 307 308 my($primary_key) = $creator -> generate_primary_key_sql($table_name); 309 310 $creator -> create_table(<<SQL); 311 create table $table_name 312 ( 313 id $primary_key, 314 data varchar(255) 315 ) 316 SQL 317 318See also xt/author/fk.t in L<DBIx::Admin::TableInfo>. 319 320=head1 Description 321 322C<DBIx::Admin::CreateTable> is a pure Perl module. 323 324Database vendors supported: MySQL, Oracle, Postgres, SQLite. 325 326Assumptions: 327 328=over 4 329 330=item Every table has a primary key 331 332=item The primary key is a unique, non-null, integer 333 334=item The primary key is a single column 335 336=item The primary key column is called 'id' 337 338=item If a primary key has a corresponding auto-created index, the index is called 't_pkey' 339 340This is true for Postgres, where declaring a column as a primary key automatically results in the creation 341of an associated index for that column. The index is named after the table, not after the column. 342 343=item If a table 't' (with primary key 'id') has an associated sequence, the sequence is called 't_id_seq' 344 345This is true for both Oracle and Postgres, which use sequences to populate primary key columns. The sequences 346are named after both the table and the column. 347 348=back 349 350=head1 Constructor and initialization 351 352new(...) returns an object of type C<DBIx::Admin::CreateTable>. 353 354This is the class contructor. 355 356Usage: DBIx::Admin::CreateTable -> new(). 357 358This method takes a set of parameters. Only the dbh parameter is mandatory. 359 360For each parameter you wish to use, call new as new(param_1 => value_1, ...). 361 362=over 4 363 364=item dbh 365 366This is a database handle, returned from the DBI connect() call. 367 368This parameter is mandatory. 369 370There is no default. 371 372=item verbose 373 374This is 0 or 1, to turn off or on printing of progress statements to STDERR. 375 376This parameter is optional. 377 378The default is 0. 379 380=back 381 382=head1 Method: create_table($sql, $arg) 383 384Returns '' (empty string) if successful and DBI errstr() if there is an error. 385 386$sql is the SQL to create the table. 387 388$arg is an optional hash ref of options per table. 389 390The keys are table names. The only sub-key at the moment is... 391 392=over 4 393 394=item no_sequence 395 396 $arg = {$table_name_1 => {no_sequence => 1}, $table_name_2 => {no_sequence => 1} }; 397 398can be used to tell create_table not to create a sequence for the given table. 399 400You would use this on a CGI::Session-type table called 'sessions', for example, 401when using Oracle or Postgres. With MySQL there would be no sequence anyway. 402 403You would also normally use this on a table called 'log'. 404 405The reason for this syntax is so you can use the same hash ref in a call to reset_all_sequences. 406 407=back 408 409Usage with CGI::Session: 410 411 my($creator) = DBIx::Admin::CreateTable -> new(dbh => $dbh, verbose => 1); 412 my($table_name) = 'sessions'; 413 my($type) = $creator -> db_vendor() eq 'ORACLE' ? 'long' : 'text'; 414 415 $creator -> drop_table($table_name); 416 $creator -> create_table(<<SQL, {$table_name => {no_sequence => 1} }); 417 create table $table_name 418 ( 419 id char(32) primary key, 420 a_session $type not null 421 ) 422 SQL 423 424Typical usage: 425 426 my($creator) = DBIx::Admin::CreateTable -> new(dbh => $dbh, verbose => 1); 427 my($table_name) = 'test'; 428 my($primary_key) = $creator -> generate_primary_key_sql($table_name); 429 430 $creator -> drop_table($table_name); 431 $creator -> create_table(<<SQL); 432 create table $table_name 433 ( 434 id $primary_key, 435 data varchar(255) 436 ) 437 SQL 438 439The SQL generated by this call to create_table() is spelled-out in the (SQL) table below. 440 441Action: 442 443 Method: create_table($table_name, $arg). 444 Comment: Creation of tables and sequences. 445 Sequence: See generate_primary_sequence_name($table_name). 446 +----------|---------------------------------------------------+ 447 | | Action for $$arg{$table_name} | 448 | Vendor | {no_sequence => 0} | {no_sequence => 1} | 449 +----------|------------------------------|--------------------+ 450 | MySQL | Create table | Create table | 451 +----------|------------------------------|--------------------+ 452 | Oracle | Create sequence before table | Create table | 453 +----------|------------------------------|--------------------+ 454 | Postgres | Create sequence before table | Create table | 455 +----------|------------------------------|--------------------+ 456 | SQLite | Create table | Create table | 457 +----------|------------------------------|--------------------+ 458 459SQL: 460 461 Method: create_table($table_name, $arg). 462 Comment: SQL generated. 463 Sequence: See generate_primary_sequence_name($table_name). 464 +----------|-------------------------------------------------------------------------------------+ 465 | | SQL for $$arg{$table_name} | 466 | Vendor | {no_sequence => 0} | {no_sequence => 1} | 467 +----------|------------------------------------------|------------------------------------------+ 468 | MySQL | create table $table_name | create table $table_name | 469 | | (id integer primary key | (id integer auto_increment | 470 | | auto_increment, | primary key, | 471 | | data varchar(255) ) | data varchar(255) ) | 472 +----------|------------------------------------------|------------------------------------------+ 473 | Oracle | create sequence ${table_name}_id_seq & | | 474 | | create table $table_name | create table $table_name | 475 | | (id integer primary key, | (id integer primary key, | 476 | | data varchar(255) ) | data varchar(255) ) | 477 +----------|------------------------------------------|------------------------------------------+ 478 | Postgres | create sequence ${table_name}_id_seq & | | 479 | | create table $table_name | create table $table_name | 480 | | (id integer primary key | (id integer primary key | 481 | | default nextval("${table_name}_id_seq"), | default nextval("${table_name}_id_seq"), | 482 | | data varchar(255) ) | data varchar(255) ) | 483 +----------|------------------------------------------|------------------------------------------+ 484 | SQLite | create table $table_name | create table $table_name | 485 | | (id integer primary key | (id integer autoincrement | 486 | | autoincrement, | primary key, | 487 | | data varchar(255) ) | data varchar(255) ) | 488 +----------|------------------------------------------|------------------------------------------+ 489 490=head1 Method: db_vendor() 491 492Returns an upper-case string identifying the database vendor. 493 494Return string: 495 496 Method: db_vendor(db_vendor). 497 Comment: Value returned. 498 +----------|------------+ 499 | Vendor | String | 500 +----------|------------+ 501 | MySQL | MYSQL | 502 +----------|------------+ 503 | Oracle | ORACLE | 504 +----------|------------+ 505 | Postgres | POSTGRESQL | 506 +----------|------------+ 507 | SQLite | SQLITE | 508 +----------|------------+ 509 510=head1 Method: drop_table($table_name, $arg) 511 512Returns '' (empty string). 513 514$table_name is the name of the table to drop. 515 516$arg is an optional hash ref of options, the same as for C<create_table()>. 517 518Action: 519 520 Method: drop_table($table_name, $arg). 521 Comment: Deletion of tables and sequences. 522 Sequence: See generate_primary_sequence_name($table_name). 523 +----------|-------------------------------------------------+ 524 | | Action for $$arg{$table_name} | 525 | Vendor | {no_sequence => 0} | {no_sequence => 1} | 526 +----------|----------------------------|--------------------+ 527 | MySQL | Drop table | Drop table | 528 +----------|----------------------------|--------------------+ 529 | Oracle | Drop sequence before table | Drop table | 530 +----------|----------------------------|--------------------+ 531 | Postgres | Drop sequence after table | Drop table | 532 +----------|----------------------------|--------------------+ 533 | SQLite | Drop table | Drop table | 534 +----------|----------------------------|--------------------+ 535 536SQL: 537 538 Method: drop_table($table_name, $arg). 539 Comment: SQL generated. 540 Sequence: See generate_primary_sequence_name($table_name). 541 +----------|---------------------------------------------------------------+ 542 | | SQL for $$arg{$table_name} | 543 | Vendor | {no_sequence => 0} | {no_sequence => 1} | 544 +----------|--------------------------------------|------------------------+ 545 | MySQL | drop table $table_name | drop table $table_name | 546 +----------|--------------------------------------|------------------------+ 547 | Oracle | drop sequence ${table_name}_id_seq & | | 548 | | drop table $table_name | drop table $table_name | 549 +----------|--------------------------------------|------------------------+ 550 | Postgres | drop table $table_name & | drop table $table_name | 551 | | drop sequence ${table_name}_id_seq | | 552 +----------|--------------------------------------|------------------------+ 553 | SQLite | drop table $table_name | drop table $table_name | 554 +----------|--------------------------------------|------------------------+ 555 556Note: drop_table() turns off RaiseError so we do not error if the sequence and table being deleted do not exist. 557This is new in V 2.00. 558 559=head1 Method: generate_primary_index_name($table_name) 560 561Returns the name of the index corresponding to the primary key for the given table. 562 563The module does not call this method. 564 565SQL: 566 567 Method: generate_primary_index_name($table_name). 568 Comment: Generation of name of the index for the primary key. 569 +----------|--------------------+ 570 | Vendor | SQL | 571 +----------|--------------------+ 572 | MySQL | | 573 +----------|--------------------+ 574 | Oracle | | 575 +----------|--------------------+ 576 | Postgres | ${table_name}_pkey | 577 +----------|--------------------+ 578 | SQLite | | 579 +----------|--------------------+ 580 581=head1 Method: generate_primary_key_sql($table_name) 582 583Returns partial SQL for declaring the primary key for the given table. 584 585See the Synopsis for how to use this method. 586 587SQL: 588 589 Method: generate_primary_key_sql($table_name). 590 Comment: Generation of partial SQL for primary key. 591 Sequence: See generate_primary_sequence_name($table_name). 592 +----------|-----------------------------------------------------+ 593 | Vendor | SQL | 594 +----------|-----------------------------------------------------+ 595 | MySQL | integer primary key auto_increment | 596 +----------|-----------------------------------------------------+ 597 | Oracle | integer primary key | 598 +----------|-----------------------------------------------------+ 599 | Postgres | integer primary key default nextval($sequence_name) | 600 +----------|-----------------------------------------------------+ 601 | SQLite | integer primary key autoincrement | 602 +----------|-----------------------------------------------------+ 603 604=head1 Method: generate_primary_sequence_name($table_name) 605 606Returns the name of the sequence used to populate the primary key of the given table. 607 608SQL: 609 610 Method: generate_primary_sequence_name($table_name). 611 Comment: Generation of name for sequence. 612 +----------|----------------------+ 613 | Vendor | SQL | 614 +----------|----------------------+ 615 | MySQL | | 616 +----------|----------------------+ 617 | Oracle | ${table_name}_id_seq | 618 +----------|----------------------+ 619 | Postgres | ${table_name}_id_seq | 620 +----------|----------------------+ 621 | SQLite | | 622 +----------|----------------------+ 623 624=head1 Method: reset_all_sequences($arg) 625 626Returns nothing. 627 628Resets the primary key sequence for all tables, except those marked by $arg as not having a sequence. 629 630Note: This method only works if called against an object which knows the names of all tables and sequences. 631This means you must have called at least one of these, for each table: 632 633=over 634 635=item create_table 636 637=item drop_table 638 639=item generate_primary_key_sql 640 641=item generate_primary_sequence_name 642 643=back 644 645$arg is an optional hash ref of options, the same as for C<create_table()>. 646 647Summary: 648 649 Method: reset_all_sequences($arg). 650 Comment: Reset all sequences. 651 +----------|-------------------------------------------------------+ 652 | Vendor | Action | 653 +----------|-------------------------------------------------------+ 654 | MySQL | Do nothing | 655 +----------|-------------------------------------------------------+ 656 | Oracle | Call reset_sequence($table_name, $arg) for all tables | 657 +----------|-------------------------------------------------------+ 658 | Postgres | Call reset_sequence($table_name, $arg) for all tables | 659 +----------|-------------------------------------------------------+ 660 | SQLite | Do nothing | 661 +----------|-------------------------------------------------------+ 662 663=head1 Method: reset_sequence($table_name, $arg) 664 665Returns nothing. 666 667Resets the primary key sequence for the given table, except if it is marked by $arg as not having a sequence. 668 669$arg is an optional hash ref of options, the same as for C<create_table()>. 670 671Summary: 672 673 Method: reset_sequence($table_name, $arg). 674 Comment: Reset one sequence. 675 Sequence: The value of the sequence is set to the number of records in the table. 676 +----------|-----------------------------------------+ 677 | | Action for $$arg{$table_name} | 678 | Vendor | {no_sequence => 0} | {no_sequence => 1} | 679 +----------|--------------------|--------------------+ 680 | MySQL | Do nothing | Do nothing | 681 +----------|--------------------|--------------------+ 682 | Oracle | Set sequence value | Do nothing | 683 +----------|--------------------|--------------------+ 684 | Postgres | Set sequence value | Do nothing | 685 +----------|--------------------|--------------------+ 686 | SQLite | Do nothing | Do nothing | 687 +----------|--------------------|--------------------+ 688 689=head1 FAQ 690 691=head2 Which versions of the servers did you test? 692 693 Versions as at 2014-03-07 694 +----------|------------+ 695 | Vendor | V | 696 +----------|------------+ 697 | MariaDB | 5.5.36 | 698 +----------|------------+ 699 | Oracle | 10.2.0.1.0 | (Not tested for years) 700 +----------|------------+ 701 | Postgres | 9.1.12 | 702 +----------|------------+ 703 | SQLite | 3.7.17 | 704 +----------|------------+ 705 706=head2 Do all database servers accept the same 'create table' commands? 707 708No. You have been warned. 709 710References for 'Create table': 711L<MySQL|https://dev.mysql.com/doc/refman/5.7/en/create-table.html>. 712L<Postgres|http://www.postgresql.org/docs/9.3/interactive/sql-createtable.html>. 713L<SQLite|https://sqlite.org/lang_createtable.html>. 714 715Consider these: 716 717 create table one 718 ( 719 id integer primary key autoincrement, 720 data varchar(255) 721 ) $engine 722 723 create table two 724 ( 725 id integer primary key autoincrement, 726 one_id integer not null, 727 data varchar(255), 728 foreign key(one_id) references one(id) 729 ) $engine 730 731Putting the 'foreign key' clause at the end makes it a table constraint. Some database servers, e.g. MySQL and Postgres, 732allow you to attach it to a particular column, as explained next. 733 734=over 4 735 736=item o MySQL 737 738The creates work as given, where $engine eq 'engine = innodb'. 739 740Further, you can re-order the clauses in the 2nd create: 741 742 create table two 743 ( 744 id integer primary key autoincrement, 745 one_id integer not null, 746 foreign key(one_id) references one(id), 747 data varchar(255) 748 ) $engine 749 750This also works, where $engine eq 'engine = innodb'. 751 752However, if you use: 753 754 create table two 755 ( 756 id integer primary key autoincrement, 757 one_id integer not null references one(id), 758 data varchar(255) 759 ) $engine 760 761Then the 'references' (foreign key) clause is parsed but discarded, even with 'engine = innodb'. 762 763=item o Postgres 764 765The creates work as given, where $engine = ''. 766 767And you can re-order the clauses, as in the first example for MySQL. 768 769=item o SQLite 770 771The creates work as given, where $engine = ''. 772 773But if you re-order the clauses: 774 775 create table two 776 ( 777 id integer primary key autoincrement, 778 one_id integer not null, 779 foreign key(one_id) references one(id), 780 data varchar(255) 781 ) $engine 782 783Then you get a syntax error. 784 785However, if you use: 786 787 create table two 788 ( 789 id integer primary key autoincrement, 790 one_id integer not null references one(id), 791 data varchar(255) 792 ) $engine 793 794Then the 'references' (foreign key) clause is parsed, and it does create a foreign key relationship. 795 796=back 797 798Do not forget this when using SQLite: 799 800 $dbh -> do('pragma foreign_keys = on') if ($dsn =~ /SQLite/i); 801 802=head2 Do I include the name of an auto-populated column in an insert statement? 803 804Depends on the server. Some databases, e.g. Postgres, do I<not> want the name of the primary key 805in the insert statement if the server is to generate a value for a column. 806 807SQL for insert: 808 809 Comment: SQL for insertion of rows containing auto-populated values. 810 Sequence: See generate_primary_sequence_name($table_name). 811 +----------|-----------------------------------------------------------------------+ 812 | Vendor | SQL | 813 +----------|-----------------------------------------------------------------------+ 814 | MySQL | insert into $table_name (data) values (?) | 815 +----------|-----------------------------------------------------------------------+ 816 | Oracle | insert into $table_name (id, data) values ($sequence_name.nextval, ?) | 817 +----------|-----------------------------------------------------------------------+ 818 | Postgres | insert into $table_name (data) values (?) | 819 +----------|-----------------------------------------------------------------------+ 820 | SQLite | insert into $table_name (id, data) values (undef, ?) | 821 +----------|-----------------------------------------------------------------------+ 822 823=head2 Do I have to use a sequence to populate a primary key? 824 825Well, no, actually. See next question. 826 827=head2 How to I override the auto-populated value for a primary key column? 828 829By including the name and the value in the insert statement. 830 831SQL for insert: 832 833 Comment: SQL for insertion of rows overriding auto-populated values. 834 +----------|--------------------------------------------------+ 835 | Vendor | SQL | 836 +----------|--------------------------------------------------+ 837 | MySQL | insert into $table_name (id, data) values (?, ?) | 838 +----------|--------------------------------------------------+ 839 | Oracle | insert into $table_name (id, data) values (?, ?) | 840 +----------|--------------------------------------------------+ 841 | Postgres | insert into $table_name (id, data) values (?, ?) | 842 +----------|--------------------------------------------------+ 843 | SQLite | insert into $table_name (id, data) values (?, ?) | 844 +----------|--------------------------------------------------+ 845 846=head2 Are primary keys always not null and unique? 847 848Yes. All servers document primary key as meaning both non null and unique. 849 850=head2 See Also 851 852L<DBIx::Admin::DSNManager>. 853 854L<DBIx::Admin::TableInfo>. 855 856=head1 Version Numbers 857 858Version numbers < 1.00 represent development versions. From 1.00 up, they are production versions. 859 860=head1 Repository 861 862L<https://github.com/ronsavage/DBIx-Admin-CreateTable> 863 864=head1 Support 865 866Bugs should be reported via the CPAN bug tracker at 867 868L<https://github.com/ronsavage/DBIx-Admin-CreateTable/issues> 869 870=head1 Author 871 872C<DBIx::Admin::CreateTable> was written by Ron Savage I<E<lt>ron@savage.net.auE<gt>> in 2006. 873 874L<http://savage.net.au/> 875 876=head1 Copyright 877 878 Australian copyright (c) 2006, Ron Savage. 879 All Programs of mine are 'OSI Certified Open Source Software'; 880 you can redistribute them and/or modify them under the terms of 881 the Artistic or the GPL licences, copies of which is available at: 882 http://www.opensource.org/licenses/index.html 883 884=cut 885 886