From ea8f3f0a3765883c993cdd1c28ae958f097d2632 Mon Sep 17 00:00:00 2001 From: David Heinemeier Hansson Date: Fri, 22 Jul 2005 19:58:03 +0000 Subject: Added migration support for SQLite (using temporary tables to simulate ALTER TABLE) #1771 [Sam Stephenson] git-svn-id: http://svn-commit.rubyonrails.org/rails/trunk@1893 5ecf4fe2-1ee6-0310-87b1-e25e094e27de --- activerecord/CHANGELOG | 2 + .../connection_adapters/abstract_adapter.rb | 46 ++++++--- .../connection_adapters/sqlite_adapter.rb | 114 ++++++++++++++++++++- activerecord/test/copy_table_sqlite.rb | 64 ++++++++++++ 4 files changed, 209 insertions(+), 17 deletions(-) create mode 100644 activerecord/test/copy_table_sqlite.rb diff --git a/activerecord/CHANGELOG b/activerecord/CHANGELOG index 07fd379b88..3bf9ba5fcf 100644 --- a/activerecord/CHANGELOG +++ b/activerecord/CHANGELOG @@ -1,5 +1,7 @@ *SVN* +* Added migration support for SQLite (using temporary tables to simulate ALTER TABLE) #1771 [Sam Stephenson] + * Remove extra definition of supports_migrations? from abstract_adaptor.rb [Nicholas Seckar] * Fix acts_as_list so that moving next-to-last item to the bottom does not result in duplicate item positions diff --git a/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb b/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb index e9473c1ca5..ff34c63fa1 100755 --- a/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb +++ b/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb @@ -380,10 +380,11 @@ module ActiveRecord table_definition.primary_key(options[:primary_key] || "id") unless options[:id] == false yield table_definition - create_sql = "CREATE TABLE #{name} (" + create_sql = "CREATE#{' TEMPORARY' if options[:temporary]} TABLE " + create_sql << "#{name} (" create_sql << table_definition.to_sql create_sql << ") #{options[:options]}" - + execute create_sql end @@ -494,6 +495,20 @@ module ActiveRecord end end + class ColumnDefinition < Struct.new(:base, :name, :type, :limit, :default) + def to_sql + column_sql = "#{name} #{type_to_sql(type.to_sym, limit)}" + column_sql << " DEFAULT '#{default}'" if default + column_sql + end + alias to_s :to_sql + + private + def type_to_sql(name, limit) + base.type_to_sql(name, limit) rescue name + end + end + class TableDefinition attr_accessor :columns @@ -503,29 +518,28 @@ module ActiveRecord end def primary_key(name) - @columns << "#{name} #{native[:primary_key]}" + return unless column = self[name] + column.type = native[:primary_key] self end + + def [](name) + @columns.find {|column| column.name == name} + end def column(name, type, options = {}) - limit = options[:limit] || native[type.to_sym][:limit] - - column_sql = "#{name} #{type_to_sql(type.to_sym, options[:limit])}" - column_sql << " DEFAULT '#{options[:default]}'" if options[:default] - @columns << column_sql + column = self[name] || ColumnDefinition.new(@base, name, type) + column.limit = options[:limit] || native[type.to_sym][:limit] + column.default = options[:default] + @columns << column unless @columns.include? column self end - + def to_sql - @columns.join(", ") - end - - private - - def type_to_sql(name, limit) - @base.type_to_sql(name, limit) + @columns * ', ' end + private def native @base.native_database_types end diff --git a/activerecord/lib/active_record/connection_adapters/sqlite_adapter.rb b/activerecord/lib/active_record/connection_adapters/sqlite_adapter.rb index fbf4aa398f..212e11de4e 100644 --- a/activerecord/lib/active_record/connection_adapters/sqlite_adapter.rb +++ b/activerecord/lib/active_record/connection_adapters/sqlite_adapter.rb @@ -103,6 +103,10 @@ module ActiveRecord } end + def supports_migrations? + true + end + def execute(sql, name = nil) #log(sql, name, @connection) { |connection| connection.execute(sql) } log(sql, name) { @connection.execute(sql) } @@ -157,6 +161,11 @@ module ActiveRecord } end + def primary_key(table_name) + column = table_structure(table_name).find {|field| field['pk'].to_i == 1} + column ? column['name'] : nil + end + def quote_string(s) @connection.class.quote(s) end @@ -169,10 +178,113 @@ module ActiveRecord 'SQLite' end + def remove_index(table_name, column_name) + execute "DROP INDEX #{table_name}_#{column_name}_index" + end + + def add_column(table_name, column_name, type, options = {}) + alter_table(table_name) do |definition| + definition.column(column_name, type, options) + end + end + + def remove_column(table_name, column_name) + alter_table(table_name) do |definition| + definition.columns.delete(definition[column_name]) + end + end + + def change_column_default(table_name, column_name, default) + alter_table(table_name) do |definition| + definition[column_name].default = default + end + end + + def change_column(table_name, column_name, type, options = {}) + alter_table(table_name) do |definition| + definition[column_name].instance_eval do + self.type = type + self.limit = options[:limit] if options[:limit] + self.default = options[:default] if options[:default] + end + end + end + + def rename_column(table_name, column_name, new_column_name) + alter_table(table_name, :rename => {column_name => new_column_name}) + end + protected def table_structure(table_name) - execute "PRAGMA table_info(#{table_name})" + returning structure = execute("PRAGMA table_info(#{table_name})") do + raise ActiveRecord::StatementInvalid if structure.empty? + end + end + + def indexes(table_name) + execute("PRAGMA index_list(#{table_name})").map do |index| + index_info = execute("PRAGMA index_info(#{index['name']})") + { + :name => index['name'], + :unique => index['unique'].to_i == 1, + :columns => index_info.map {|info| info['name']} + } + end + end + + def alter_table(table_name, options = {}) #:nodoc: + altered_table_name = "altered_#{table_name}" + caller = lambda {|definition| yield definition if block_given?} + + transaction do + move_table(table_name, altered_table_name, + options.merge(:temporary => true), &caller) + move_table(altered_table_name, table_name, &caller) + end + end + + def move_table(from, to, options = {}, &block) #:nodoc: + copy_table(from, to, options, &block) + drop_table(from) + end + + def copy_table(from, to, options = {}) #:nodoc: + create_table(to, options) do |@definition| + columns(from).each do |column| + column_name = options[:rename][column.name] if + options[:rename][column.name] if options[:rename] + + @definition.column(column_name || column.name, column.type, + :limit => column.limit, :default => column.default) + end + @definition.primary_key(primary_key(from)) + yield @definition if block_given? + end + + copy_table_indexes(from, to) + copy_table_contents(from, to, + @definition.columns.map {|column| column.name}, + options[:rename] || {}) + end + + def copy_table_indexes(from, to) #:nodoc: + indexes(from).each do |index| + type = index[:unique] ? 'UNIQUE' : '' + add_index(to, index[:columns], type) + end + end + + def copy_table_contents(from, to, columns, rename = {}) #:nodoc: + column_mappings = Hash[*columns.map {|name| [name, name]}.flatten] + rename.inject(column_mappings) {|map, a| map[a.last] = a.first; map} + + @connection.execute "SELECT * FROM #{from}" do |row| + sql = "INSERT INTO #{to} VALUES (" + sql << columns.map {|col| quote row[column_mappings[col]]} * ', ' + sql << ')' + @connection.execute sql + end end end diff --git a/activerecord/test/copy_table_sqlite.rb b/activerecord/test/copy_table_sqlite.rb new file mode 100644 index 0000000000..f3f5f1ce46 --- /dev/null +++ b/activerecord/test/copy_table_sqlite.rb @@ -0,0 +1,64 @@ +require 'abstract_unit' + +class CopyTableTest < Test::Unit::TestCase + fixtures :companies, :comments + + def setup + @connection = ActiveRecord::Base.connection + class << @connection + public :copy_table, :table_structure, :indexes + end + end + + def test_copy_table(from = 'companies', to = 'companies2', options = {}) + assert_nothing_raised {copy_table(from, to, options)} + assert_equal row_count(from), row_count(to) + + if block_given? + yield from, to, options + else + assert_equal column_names(from), column_names(to) + end + + @connection.drop_table(to) rescue nil + end + + def test_copy_table_renaming_column + test_copy_table('companies', 'companies2', + :rename => {'client_of' => 'fan_of'}) do |from, to, options| + assert_equal column_values(from, 'client_of').compact.sort, + column_values(to, 'fan_of').compact.sort + end + end + + def test_copy_table_with_index + test_copy_table('comments', 'comments_with_index') do + @connection.add_index('comments_with_index', ['post_id', 'type']) + test_copy_table('comments_with_index', 'comments_with_index2') do + assert_equal table_indexes_without_name('comments_with_index'), + table_indexes_without_name('comments_with_index2') + end + end + end + +protected + def copy_table(from, to, options = {}) + @connection.copy_table(from, to, {:temporary => true}.merge(options)) + end + + def column_names(table) + @connection.table_structure(table).map {|column| column['name']} + end + + def column_values(table, column) + @connection.select_all("SELECT #{column} FROM #{table}").map {|row| row[column]} + end + + def table_indexes_without_name(table) + @connection.indexes('comments_with_index').delete(:name) + end + + def row_count(table) + @connection.select_one("SELECT COUNT(*) AS count FROM #{table}")['count'] + end +end -- cgit v1.2.3