module Sequel class Database # Adds a column to the specified table. This method expects a column name, # a datatype and optionally a hash with additional constraints and options: # # DB.add_column :items, :name, :text, :unique => true, :null => false # DB.add_column :items, :category, :text, :default => 'ruby' # # See alter_table. def add_column(table, *args) alter_table(table) {add_column(*args)} end # Adds an index to a table for the given columns: # # DB.add_index :posts, :title # DB.add_index :posts, [:author, :title], :unique => true # # See alter_table. def add_index(table, *args) alter_table(table) {add_index(*args)} end # Alters the given table with the specified block. Here are the currently # available operations: # # DB.alter_table :items do # add_column :category, :text, :default => 'ruby' # drop_column :category # rename_column :cntr, :counter # set_column_type :value, :float # set_column_default :value, :float # add_index [:group, :category] # drop_index [:group, :category] # end # # Note that #add_column accepts all the options available for column # definitions using create_table, and #add_index accepts all the options # available for index definition. # # See Schema::AlterTableGenerator. def alter_table(name, &block) g = Schema::AlterTableGenerator.new(self, &block) alter_table_sql_list(name, g.operations).each {|sql| execute(sql)} end # Creates a table with the columns given in the provided block: # # DB.create_table :posts do # primary_key :id, :serial # column :title, :text # column :content, :text # index :title # end # # See Schema::Generator. def create_table(name, &block) g = Schema::Generator.new(self, &block) create_table_sql_list(name, *g.create_info).each {|sql| execute(sql)} end # Forcibly creates a table. If the table already exists it is dropped. def create_table!(name, &block) drop_table(name) rescue nil create_table(name, &block) end # Creates a view, replacing it if it already exists: # # DB.create_or_replace_view(:cheap_items, "SELECT * FROM items WHERE price < 100") # DB.create_or_replace_view(:ruby_items, DB[:items].filter(:category => 'ruby')) def create_or_replace_view(name, source) source = source.sql if source.is_a?(Dataset) execute("CREATE OR REPLACE VIEW #{name} AS #{source}") end # Creates a view based on a dataset or an SQL string: # # DB.create_view(:cheap_items, "SELECT * FROM items WHERE price < 100") # DB.create_view(:ruby_items, DB[:items].filter(:category => 'ruby')) def create_view(name, source) source = source.sql if source.is_a?(Dataset) execute("CREATE VIEW #{name} AS #{source}") end # Removes a column from the specified table: # # DB.drop_column :items, :category # # See alter_table. def drop_column(table, *args) alter_table(table) {drop_column(*args)} end # Removes an index for the given table and column/s: # # DB.drop_index :posts, :title # DB.drop_index :posts, [:author, :title] # # See alter_table. def drop_index(table, columns) alter_table(table) {drop_index(columns)} end # Drops one or more tables corresponding to the given table names: # # DB.drop_table(:posts, :comments) def drop_table(*names) names.each {|n| execute(drop_table_sql(n))} end # Drops a view: # # DB.drop_view(:cheap_items) def drop_view(name) execute("DROP VIEW #{name}") end # Renames a table: # # DB.tables #=> [:items] # DB.rename_table :items, :old_items # DB.tables #=> [:old_items] def rename_table(*args) execute(rename_table_sql(*args)) end # Renames a column in the specified table. This method expects the current # column name and the new column name: # # DB.rename_column :items, :cntr, :counter # # See alter_table. def rename_column(table, *args) alter_table(table) {rename_column(*args)} end # Sets the default value for the given column in the given table: # # DB.set_column_default :items, :category, 'perl!' # # See alter_table. def set_column_default(table, *args) alter_table(table) {set_column_default(*args)} end # Set the data type for the given column in the given table: # # DB.set_column_type :items, :price, :float # # See alter_table. def set_column_type(table, *args) alter_table(table) {set_column_type(*args)} end end end