1use warnings; 2use strict; 3 4package Jifty::Schema; 5use base qw/Jifty::Object/; 6use SQL::ReservedWords; 7 8=head1 NAME 9 10Jifty::Schema - generates and upgrades your application's schema 11 12=cut 13 14Jifty::Module::Pluggable->import( 15 require => 1, 16 search_path => ["SQL::ReservedWords"], 17 sub_name => '_sql_dialects', 18); 19 20our %_SQL_RESERVED = (); 21our @_SQL_RESERVED_OVERRIDE = qw(value); 22foreach my $dialect ( 'SQL::ReservedWords', &_sql_dialects ) { 23 foreach my $word ( $dialect->words ) { 24 push @{ $_SQL_RESERVED{ lc($word) } }, $dialect->reserved_by($word); 25 } 26} 27 28# XXX TODO: QUESTIONABLE ENGINEERING DECISION 29# The SQL standard forbids columns named 'value', but just about everone on the planet 30# actually supports it. Rather than going about scaremongering, we choose 31# not to warn people about columns named 'value' 32 33delete $_SQL_RESERVED{ lc($_) } for (@_SQL_RESERVED_OVERRIDE); 34 35=head2 new 36 37Returns a new Jifty::Schema. Takes no arguments. Will automatically figure out and initialize the models defined in the application's source. 38 39=cut 40 41sub new { 42 my $class = shift; 43 my $self = {}; 44 bless $self, $class; 45 $self->_init_model_list(); 46 return $self; 47 48} 49 50=head2 _init_model_list 51 52Reads in our application class from the config file and finds all our application's models. 53 54=head2 models 55 56Returns a list of Models available to your application. This includes 57your Models, Collections and those that come from core Jifty and 58plugins. 59 60=cut 61 62sub _init_model_list { 63 my $self = shift; 64 65 # Plugins can have models too 66 my @plugins = map { (ref $_).'::Model' } Jifty->plugins; 67 68 # This creates a sub "models" which when called, finds packages under 69 # the application's ::Model, requires them, and returns a list of their 70 # names. 71 Jifty::Module::Pluggable->import( 72 require => 1, 73 except => qr/\.#/, 74 search_path => [ "Jifty::Model", Jifty->app_class("Model"), @plugins ], 75 sub_name => 'models', 76 ); 77} 78 79=head2 serialize_current_schema 80 81Returns a serialization of the models in the app 82 83=cut 84 85sub serialize_current_schema { 86 my $self = shift; 87 88 my @models = $self->model_classes; 89 my $serialized_models = {}; 90 foreach my $model (@models) { 91 $serialized_models->{$model->_class_name} = $model->serialize_metadata; 92 } 93 94 return $serialized_models; 95 96} 97 98 99=head2 upgrade_schema 100 101Looks at the current schema as defined by the source code and the database and updates the database by adding, dropping, and renaming columns. 102 103=cut 104 105sub upgrade_schema { 106 my $self = shift; 107 108 109 # load the database schema version 110 111 # hashref 112 my $old_tables = $self->current_db_schema; 113 114 # hashref 115 my $new_tables = $self->new_db_schema; 116 117 my $add_tables = {}; 118 my $remove_tables ={}; 119 my $add_columns = {}; 120 my $remove_columns = {}; 121 122 # diff the current schema version and the database schema version 123 foreach my $table ( keys %$old_tables ) { 124 unless ( $new_tables->{$table} ) { 125 $remove_tables->{$table} = $old_tables->{$table}; 126 next; 127 } 128 129 foreach my $column ( @{ $old_tables->{$table}->columns } ) { 130 131 # if the column isn't in the new table as well, then mark it for deletion 132 unless ( $new_tables->{$table}->column($column) ) { 133 push @{ $remove_columns->{$table} }, $column; 134 } 135 136 # XXX TODO: compare the column definitions and alter them if necessary 137 138 } 139 } 140 141 foreach my $table ( keys %$new_tables ) { 142 unless ( $old_tables->{$table} ) { 143 $add_tables->{$table} = $new_tables->{$table}; 144 next; 145 } 146 147 foreach my $column ( @{ $new_tables->{$table}->columns } ) { 148 149 # if the column isn't in the old table as well, then mark it for addition 150 unless ( $old_tables->{$table}->column($column) ) { 151 push @{ $add_columns->{$table} }, $column; 152 } 153 154 # XXX TODO: compare the column definitions and alter them if necessary 155 156 } 157 } 158 159 # Run all "Rename" rules 160 $self->run_upgrade_rules('before_all_renames'); 161 my $table_renames = Jifty->upgrade->table_renames; 162 my $column_renames = Jifty->upgrade->column_renames; 163 $self->run_upgrade_rules('after_column_renames'); 164 165 $self->_add_tables($add_tables); 166 $self->_add_columns($add_columns); 167 $self->_drop_tables($remove_tables); 168 $self->_drop_columns($remove_columns); 169 170 171} 172 173 174sub _add_tables { 175 my $self = shift; 176 my $add_tables = shift; 177 178 179 # add all new tables 180 $self->run_upgrade_rules('before_table_adds'); 181 foreach my $table ( values %$add_tables ) { 182 $self->run_upgrade_rules( 'before_add_table_' . $table ); 183 $add_tables->{$table}->create_table_in_db(); 184 $self->run_upgrade_rules( 'after_add_table_' . $table ); 185 } 186 $self->run_upgrade_rules('after_table_adds'); 187} 188 189 190sub _add_columns { 191 my $self = shift; 192 my $add_columns = shift; 193 194 $self->run_upgrade_rules('before_column_adds'); 195 foreach my $table ( values %$add_columns ) { 196 $self->run_upgrade_rules( 'before_add_columns_to_table_' . $table ); 197 my @cols = @{ $add_columns->{$table} }; 198 foreach my $col (@cols) { 199 $self->run_upgrade_rules( 'before_add_column_' . $col->name . '_to_table_' . $table ); 200 $add_columns->{$table}->add_column_in_db($col); 201 $self->run_upgrade_rules( 'after_add_column_' . $col->name . '_to_table_' . $table ); 202 } 203 $self->run_upgrade_rules( 'after_add_columns_to_table_' . $table ); 204 } 205 $self->run_upgrade_rules('after_add_columns'); 206 207} 208 209 210 211 212sub _drop_tables { 213 my $self =shift; 214 my $remove_tables = shift; 215 216 217 $self->run_upgrade_rules('before_drop_tables'); 218 219 foreach my $table ( values %$remove_tables ) { 220 $self->run_upgrade_rules( 'before_drop_table_' . $table ); 221 $remove_tables->{$table}->drop_table_in_db(); 222 $self->run_upgrade_rules( 'after_drop_table_' . $table ); 223 } 224 $self->run_upgrade_rules('after_drop_tables'); 225 226} 227 228sub _drop_columns { 229 my $self = shift; 230 my $remove_columns = shift; 231 232 $self->run_upgrade_rules('before_drop_columns'); 233 234 foreach my $table ( values %$remove_columns ) { 235 $self->run_upgrade_rules( 'before_drop_columns_from_' . $table ); 236 my @cols = @{ $remove_columns->{$table} }; 237 foreach my $col (@cols) { 238 $self->run_upgrade_rules( 'before_drop_column' . $col->name . '_from_' . $table ); 239 $remove_columns->{$table}->drop_column_in_db($col); 240 $self->run_upgrade_rules( 'after_drop_column_' . $col->name . '_from_' . $table ); 241 } 242 $self->run_upgrade_rules( 'after_drop_columns_from_' . $table ); 243 } 244 $self->run_upgrade_rules('after_drop_columns'); 245 246} 247 248 249=head2 run_upgrade_rules rule_name 250 251This method runs all upgrade rules for the rule named C<rule_name>. 252 253=cut 254 255sub run_upgrade_rules { 256 my $self = shift; 257 my $rule_name = shift; 258 259 my $upgrade_object = Jifty->app_class('Upgrade'); 260 $upgrade_object->call_trigger($rule_name); 261} 262 263 264 265sub _check_reserved { 266 my $self = shift; 267 my $model = shift; 268 my $log = Log::Log4perl->get_logger("SchemaTool"); 269 foreach my $col ( $model->columns ) { 270 if ( exists $_SQL_RESERVED{ lc( $col->name ) } ) { 271 $log->error( 272 $model . ": " 273 . $col->name 274 . " is a reserved word in these SQL dialects: " 275 . join( ', ', 276 _classify_reserved_words( @{ $_SQL_RESERVED{ lc( $col->name ) } } ) ) 277 ); 278 } 279 } 280} 281 282sub _classify_reserved_words { 283 my %dbs; 284 285 # Guess names of databases + their versions by breaking on last space, 286 # e.g., "SQL Server 7" is ("SQL Server", "7"), not ("SQL", "Server 7"). 287 push @{ $dbs{ $_->[0] } }, $_->[1] 288 for map { [ split /\s+(?!.*\s)/, $_, 2 ] } @_; 289 return 290 map { join " ", $_, __parenthesize_sql_variants( @{ $dbs{$_} } ) } sort keys %dbs; 291} 292 293sub __parenthesize_sql_variants { 294 if ( not defined $_[0] ) { return () } 295 if ( @_ == 1 ) { return $_[0] } 296 return "(" . ( join ", ", @_ ) . ")"; 297} 298 299=head2 connect_to_db_for_management 300 301Returns a database handle suitable for direct manipulation. 302 303=cut 304 305sub connect_to_db_for_management { 306 my $handle = Jifty::Handle->new(); 307 308 my $driver = Jifty->config->framework('Database')->{'Driver'}; 309 310 # Everything but the template1 database is assumed 311 my %connect_args; 312 $connect_args{'database'} = 'template1' 313 if ( $handle->isa("Jifty::DBI::Handle::Pg") ); 314 $connect_args{'database'} = '' 315 if ( $handle->isa("Jifty::DBI::Handle::mysql") ); 316 for ( 1 .. 50 ) { 317 my $counter = $_; 318 eval { $handle->connect(%connect_args); }; 319 my $err = $@; 320 last if ( !$err || $err =~ /does not exist/i ); 321 sleep 1; 322 } 323 return $handle; 324} 325 326 3271; 328