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.



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

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.



57
58
59
 
# File 'lib/sequel_core/schema/sql.rb', line 57

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.



63
64
65
 
# File 'lib/sequel_core/schema/sql.rb', line 63

def auto_increment_sql
  AUTOINCREMENT
end

#column_definition_sql(column) ⇒ Object

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



68
69
70
71
72
73
74
75
76
77
78
79
 
# File 'lib/sequel_core/schema/sql.rb', line 68

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.



83
84
85
 
# File 'lib/sequel_core/schema/sql.rb', line 83

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



88
89
90
91
92
93
94
 
# File 'lib/sequel_core/schema/sql.rb', line 88

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.



97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
 
# File 'lib/sequel_core/schema/sql.rb', line 97

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, options = {}) ⇒ 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.



117
118
119
120
121
 
# File 'lib/sequel_core/schema/sql.rb', line 117

def create_table_sql_list(name, columns, indexes = nil, options = {})
  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.



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

def default_index_name(table_name, columns)
  schema, table = schema_and_table(table_name)
  "#{"#{schema}_" if schema and schema != default_schema}#{table}_#{columns.map{|c| c.is_one_of?(String, Symbol) ? c : literal(c).gsub(/\W/, '_')}.join(UNDERSCORE)}_index"
end

#drop_index_sql(table, op) ⇒ Object

The SQL to drop an index for the table.



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

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.



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

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.



141
142
143
 
# File 'lib/sequel_core/schema/sql.rb', line 141

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.



147
148
149
150
151
152
153
154
155
156
 
# File 'lib/sequel_core/schema/sql.rb', line 147

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.



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

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.



165
166
167
 
# File 'lib/sequel_core/schema/sql.rb', line 165

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.



179
180
181
182
183
184
185
186
187
188
189
190
191
192
 
# File 'lib/sequel_core/schema/sql.rb', line 179

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.



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

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



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

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.



205
206
207
 
# File 'lib/sequel_core/schema/sql.rb', line 205

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.

Raises:



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
 
# File 'lib/sequel_core/schema/sql.rb', line 221

def schema(table = nil, opts={})
  raise(Error, 'schema parsing is not implemented on this database') unless respond_to?(:schema_parse_table, true)

  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
  
  raise(Error, '#tables does not exist, you must provide a specific table to #schema') if table.nil? && !respond_to?(:tables, true)

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

  if table_name
    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
    tables.each{|t| @schemas[quote_schema_table(t)] = schema_parse_table(t.to_s, opts)}
    @schemas
  end
end

#schema_utility_dataset ⇒ Object

The dataset to use for proxying certain schema methods.



263
264
265
 
# File 'lib/sequel_core/schema/sql.rb', line 263

def schema_utility_dataset
  @schema_utility_dataset ||= dataset
end