Module: Sequel::Schema::SQL

Included in:
Database
Defined in:
lib/sequel_core/schema/sql.rb,
lib/sequel_core/adapters/shared/mysql.rb

Constant Summary collapse

AUTOINCREMENT = 
'AUTOINCREMENT'.freeze
CASCADE = 
'CASCADE'.freeze
COMMA_SEPARATOR = 
', '.freeze
NO_ACTION = 
'NO ACTION'.freeze
NOT_NULL = 
' NOT NULL'.freeze
NULL = 
' NULL'.freeze
PRIMARY_KEY = 
' PRIMARY KEY'.freeze
RESTRICT = 
'RESTRICT'.freeze
SET_DEFAULT = 
'SET DEFAULT'.freeze
SET_NULL = 
'SET NULL'.freeze
TYPES = 
Hash.new {|h, k| k}
UNDERSCORE = 
'_'.freeze
UNIQUE = 
' UNIQUE'.freeze
UNSIGNED = 
' UNSIGNED'.freeze

Instance Method Summary collapse

Instance Method Details

#alter_table_sql(table, op) ⇒ Object

The SQL to execute to modify the DDL for the given table name. op should be one of the operations returned by the AlterTableGenerator.



22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
 
# File 'lib/sequel_core/schema/sql.rb', line 22

def alter_table_sql(table, op)
  quoted_name = quote_identifier(op[:name]) if op[:name]
  alter_table_op = case op[:op]
  when :add_column
    "ADD COLUMN #{column_definition_sql(op)}"
  when :drop_column
    "DROP COLUMN #{quoted_name}"
  when :rename_column
    "RENAME COLUMN #{quoted_name} TO #{quote_identifier(op[:new_name])}"
  when :set_column_type
    "ALTER COLUMN #{quoted_name} TYPE #{type_literal(op)}"
  when :set_column_default
    "ALTER COLUMN #{quoted_name} SET DEFAULT #{literal(op[:default])}"
  when :set_column_null
    "ALTER COLUMN #{quoted_name} #{op[:null] ? 'DROP' : 'SET'} NOT NULL"
  when :add_index
    return index_definition_sql(table, op)
  when :drop_index
    return drop_index_sql(table, op)
  when :add_constraint
    "ADD #{constraint_definition_sql(op)}"
  when :drop_constraint
    "DROP CONSTRAINT #{quoted_name}"
  else
    raise Error, "Unsupported ALTER TABLE operation"
  end
  "ALTER TABLE #{quote_schema_table(table)} #{alter_table_op}"
end

#alter_table_sql_list(table, operations) ⇒ Object

Array of SQL DDL modification statements for the given table, corresponding to the DDL changes specified by the operations.



53
54
55
 
# File 'lib/sequel_core/schema/sql.rb', line 53

def alter_table_sql_list(table, operations)
  operations.map{|op| alter_table_sql(table, op)}
end

#auto_increment_sql ⇒ Object

The SQL string specify the autoincrement property, generally used by primary keys.



59
60
61
 
# File 'lib/sequel_core/schema/sql.rb', line 59

def auto_increment_sql
  AUTOINCREMENT
end

#column_definition_sql(column) ⇒ Object

SQL DDL fragment containing the column creation SQL for the given column.



64
65
66
67
68
69
70
71
72
73
74
75
 
# File 'lib/sequel_core/schema/sql.rb', line 64

def column_definition_sql(column)
  return constraint_definition_sql(column) if column[:type] == :check
  sql = "#{quote_identifier(column[:name])} #{type_literal(column)}"
  sql << UNIQUE if column[:unique]
  sql << NOT_NULL if column[:null] == false
  sql << NULL if column[:null] == true
  sql << " DEFAULT #{literal(column[:default])}" if column.include?(:default)
  sql << PRIMARY_KEY if column[:primary_key]
  sql << " #{auto_increment_sql}" if column[:auto_increment]
  sql << column_references_sql(column) if column[:table]
  sql
end

#column_list_sql(columns) ⇒ Object

SQL DDL fragment containing the column creation SQL for all given columns, used instead a CREATE TABLE block.



79
80
81
 
# File 'lib/sequel_core/schema/sql.rb', line 79

def column_list_sql(columns)
  columns.map{|c| column_definition_sql(c)}.join(COMMA_SEPARATOR)
end

#column_references_sql(column) ⇒ Object Also known as: default_column_references_sql

SQL DDL fragment for column foreign key references



84
85
86
87
88
89
90
 
# File 'lib/sequel_core/schema/sql.rb', line 84

def column_references_sql(column)
  sql = " REFERENCES #{quote_schema_table(column[:table])}"
  sql << "(#{Array(column[:key]).map{|x| quote_identifier(x)}.join(COMMA_SEPARATOR)})" if column[:key]
  sql << " ON DELETE #{on_delete_clause(column[:on_delete])}" if column[:on_delete]
  sql << " ON UPDATE #{on_delete_clause(column[:on_update])}" if column[:on_update]
  sql
end

#constraint_definition_sql(constraint) ⇒ Object

SQL DDL fragment specifying a constraint on a table.



93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
 
# File 'lib/sequel_core/schema/sql.rb', line 93

def constraint_definition_sql(constraint)
  sql = constraint[:name] ? "CONSTRAINT #{quote_identifier(constraint[:name])} " : ""
  case constraint[:constraint_type]
  when :primary_key
    sql << "PRIMARY KEY #{literal(constraint[:columns])}"
  when :foreign_key
    sql << "FOREIGN KEY #{literal(constraint[:columns])}"
    sql << column_references_sql(constraint)
  when :unique
    sql << "UNIQUE #{literal(constraint[:columns])}"
  else
    check = constraint[:check]
    sql << "CHECK #{filter_expr((check.is_a?(Array) && check.length == 1) ? check.first : check)}"
  end
  sql
end

#create_table_sql_list(name, columns, indexes = nil) ⇒ Object

Array of SQL DDL statements, the first for creating a table with the given name and column specifications, and the others for specifying indexes on the table.



113
114
115
116
117
 
# File 'lib/sequel_core/schema/sql.rb', line 113

def create_table_sql_list(name, columns, indexes = nil)
  sql = ["CREATE TABLE #{quote_schema_table(name)} (#{column_list_sql(columns)})"]
  sql.concat(index_list_sql_list(name, indexes)) if indexes && !indexes.empty?
  sql
end

#default_index_name(table_name, columns) ⇒ Object

Default index name for the table and columns, may be too long for certain databases.



121
122
123
124
 
# File 'lib/sequel_core/schema/sql.rb', line 121

def default_index_name(table_name, columns)
  schema, table = schema_and_table(table_name)
  "#{"#{schema}_" if schema and schema != default_schema}#{table}_#{columns.join(UNDERSCORE)}_index"
end

#drop_index_sql(table, op) ⇒ Object

The SQL to drop an index for the table.



127
128
129
 
# File 'lib/sequel_core/schema/sql.rb', line 127

def drop_index_sql(table, op)
  "DROP INDEX #{quote_identifier(op[:name] || default_index_name(table, op[:columns]))}"
end

#drop_table_sql(name) ⇒ Object

SQL DDL statement to drop the table with the given name.



132
133
134
 
# File 'lib/sequel_core/schema/sql.rb', line 132

def drop_table_sql(name)
  "DROP TABLE #{quote_schema_table(name)}"
end

#filter_expr(*args, &block) ⇒ Object

Proxy the filter_expr call to the dataset, used for creating constraints.



137
138
139
 
# File 'lib/sequel_core/schema/sql.rb', line 137

def filter_expr(*args, &block)
  schema_utility_dataset.literal(schema_utility_dataset.send(:filter_expr, *args, &block))
end

#index_definition_sql(table_name, index) ⇒ Object

SQL DDL statement for creating an index for the table with the given name and index specifications.



143
144
145
146
147
148
149
150
151
152
 
# File 'lib/sequel_core/schema/sql.rb', line 143

def index_definition_sql(table_name, index)
  index_name = index[:name] || default_index_name(table_name, index[:columns])
  if index[:type]
    raise Error, "Index types are not supported for this database"
  elsif index[:where]
    raise Error, "Partial indexes are not supported for this database"
  else
    "CREATE #{'UNIQUE ' if index[:unique]}INDEX #{quote_identifier(index_name)} ON #{quote_identifier(table_name)} #{literal(index[:columns])}"
  end
end

#index_list_sql_list(table_name, indexes) ⇒ Object

Array of SQL DDL statements, one for each index specification, for the given table.



156
157
158
 
# File 'lib/sequel_core/schema/sql.rb', line 156

def index_list_sql_list(table_name, indexes)
  indexes.map{|i| index_definition_sql(table_name, i)}
end

#literal(v) ⇒ Object

Proxy the literal call to the dataset, used for default values.



161
162
163
 
# File 'lib/sequel_core/schema/sql.rb', line 161

def literal(v)
  schema_utility_dataset.literal(v)
end

#on_delete_clause(action) ⇒ Object

SQL DDL ON DELETE fragment to use, based on the given action. The following actions are recognized:

  • :cascade - Delete rows referencing this row.

  • :no_action (default) - Raise an error if other rows reference this row, allow deferring of the integrity check.

  • :restrict - Raise an error if other rows reference this row, but do not allow deferring the integrity check.

  • :set_default - Set columns referencing this row to their default value.

  • :set_null - Set columns referencing this row to NULL.



175
176
177
178
179
180
181
182
183
184
185
186
187
188
 
# File 'lib/sequel_core/schema/sql.rb', line 175

def on_delete_clause(action)
  case action
  when :restrict
    RESTRICT
  when :cascade
    CASCADE
  when :set_null
    SET_NULL
  when :set_default
    SET_DEFAULT
  else
    NO_ACTION
  end
end

#quote_identifier(v) ⇒ Object

Proxy the quote_identifier method to the dataset, used for quoting tables and columns.



196
197
198
 
# File 'lib/sequel_core/schema/sql.rb', line 196

def quote_identifier(v)
  schema_utility_dataset.quote_identifier(v)
end

#quote_schema_table(table) ⇒ Object

Proxy the quote_schema_table method to the dataset



191
192
193
 
# File 'lib/sequel_core/schema/sql.rb', line 191

def quote_schema_table(table)
  schema_utility_dataset.quote_schema_table(table)
end

#rename_table_sql(name, new_name) ⇒ Object

SQL DDL statement for renaming a table.



201
202
203
 
# File 'lib/sequel_core/schema/sql.rb', line 201

def rename_table_sql(name, new_name)
  "ALTER TABLE #{quote_schema_table(name)} RENAME TO #{quote_schema_table(new_name)}"
end

#schema(table = nil, opts = {}) ⇒ Object

Parse the schema from the database. If the table_name is not given, returns the schema for all tables as a hash. If the table_name is given, returns the schema for a single table as an array with all members being arrays of length 2. Available options are:

  • :reload - Get fresh information from the database, instead of using cached information. If table_name is blank, :reload should be used unless you are sure that schema has not been called before with a table_name, otherwise you may only getting the schemas for tables that have been requested explicitly.

  • :schema - An explicit schema to use. It may also be implicitly provided via the table name.



217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
 
# File 'lib/sequel_core/schema/sql.rb', line 217

def schema(table = nil, opts={})
  if table
    sch, table_name = schema_and_table(table)
    quoted_name = quote_schema_table(table)
  end
  opts = opts.merge(:schema=>sch) if sch && !opts.include?(:schema)
  if opts[:reload] && @schemas
    if table_name
      @schemas.delete(quoted_name)
    else
      @schemas = nil
    end
  end

  if @schemas
    if table_name
      return @schemas[quoted_name] if @schemas[quoted_name]
    else
      return @schemas
    end
  end
  
  @schemas ||= Hash.new do |h,k|
    quote_name = quote_schema_table(k)
    h[quote_name] if h.include?(quote_name)
  end

  if table_name
    if respond_to?(:schema_parse_table, true)
      cols = schema_parse_table(table_name, opts)
      raise(Error, 'schema parsing returned no columns, table probably doesn\'t exist') if cols.blank?
      @schemas[quoted_name] = cols
    else
      raise Error, 'schema parsing is not implemented on this database'
    end
  else
    if respond_to?(:schema_parse_tables, true)
      @schemas.merge!(schema_parse_tables(opts))
    elsif respond_to?(:schema_parse_table, true) and respond_to?(:tables, true)
      tables.each{|t| @schemas[quote_identifier(t)] = schema_parse_table(t.to_s, opts)}
      @schemas
    else
      @schemas = nil
      raise Error, 'schema parsing is not implemented on this database'
    end
  end
end

#schema_utility_dataset ⇒ Object

The dataset to use for proxying certain schema methods.



266
267
268
 
# File 'lib/sequel_core/schema/sql.rb', line 266

def schema_utility_dataset
  @schema_utility_dataset ||= dataset
end