Module: Sequel::Schema::SQL

Included in:
Database
Defined in:
lib/sequel_core/schema/sql.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
 
# File 'lib/sequel_core/schema/sql.rb', line 22

def alter_table_sql(table, op)
  quoted_table = quote_identifier(table)
  quoted_name = quote_identifier(op[:name]) if op[:name]
  case op[:op]
  when :add_column
    "ALTER TABLE #{quoted_table} ADD COLUMN #{column_definition_sql(op)}"
  when :drop_column
    "ALTER TABLE #{quoted_table} DROP COLUMN #{quoted_name}"
  when :rename_column
    "ALTER TABLE #{quoted_table} RENAME COLUMN #{quoted_name} TO #{quote_identifier(op[:new_name])}"
  when :set_column_type
    "ALTER TABLE #{quoted_table} ALTER COLUMN #{quoted_name} TYPE #{op[:type]}"
  when :set_column_default
    "ALTER TABLE #{quoted_table} ALTER COLUMN #{quoted_name} SET DEFAULT #{literal(op[:default])}"
  when :add_index
    index_definition_sql(table, op)
  when :drop_index
    "DROP INDEX #{default_index_name(table, op[:columns])}"
  when :add_constraint
    "ALTER TABLE #{quoted_table} ADD #{constraint_definition_sql(op)}"
  when :drop_constraint
    "ALTER TABLE #{quoted_table} DROP CONSTRAINT #{quoted_name}"
  else
    raise Error, "Unsupported ALTER TABLE operation"
  end
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.



51
52
53
 
# File 'lib/sequel_core/schema/sql.rb', line 51

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.



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

def auto_increment_sql
  AUTOINCREMENT
end

#column_definition_sql(column) ⇒ Object

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



62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
 
# File 'lib/sequel_core/schema/sql.rb', line 62

def column_definition_sql(column)
  return constraint_definition_sql(column) if column[:type] == :check
  sql = "#{quote_identifier(column[:name])} #{type_literal(TYPES[column[:type]])}"
  column[:size] ||= 255 if column[:type] == :varchar
  elements = column[:size] || column[:elements]
  sql << literal(Array(elements)) if elements
  sql << UNSIGNED if column[:unsigned]
  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]
  if column[:table]
    sql << " REFERENCES #{quote_identifier(column[:table])}"
    sql << "(#{quote_identifier(column[:key])})" if column[:key]
    sql << " ON DELETE #{on_delete_clause(column[:on_delete])}" if column[:on_delete]
  end
  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.



85
86
87
 
# File 'lib/sequel_core/schema/sql.rb', line 85

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

#constraint_definition_sql(constraint) ⇒ Object

SQL DDL fragment specifying a constraint on a table.



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

def constraint_definition_sql(constraint)
  sql = constraint[:name] ? "CONSTRAINT #{quote_identifier(constraint[:name])} " : ""
  sql << "CHECK #{filter_expr(constraint[:check])}"
  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.



99
100
101
102
103
 
# File 'lib/sequel_core/schema/sql.rb', line 99

def create_table_sql_list(name, columns, indexes = nil)
  sql = ["CREATE TABLE #{quote_identifier(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.



107
108
109
 
# File 'lib/sequel_core/schema/sql.rb', line 107

def default_index_name(table_name, columns)
  "#{table_name}_#{columns.join(UNDERSCORE)}_index"
end

#drop_table_sql(name) ⇒ Object

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



112
113
114
 
# File 'lib/sequel_core/schema/sql.rb', line 112

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

#filter_expr(*args, &block) ⇒ Object

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



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

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.



123
124
125
126
127
128
129
130
131
132
 
# File 'lib/sequel_core/schema/sql.rb', line 123

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 #{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.



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

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.



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

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.



155
156
157
158
159
160
161
162
163
164
165
166
167
168
 
# File 'lib/sequel_core/schema/sql.rb', line 155

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.



171
172
173
 
# File 'lib/sequel_core/schema/sql.rb', line 171

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

#rename_table_sql(name, new_name) ⇒ Object

SQL DDL statement for renaming a table.



176
177
178
 
# File 'lib/sequel_core/schema/sql.rb', line 176

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

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

Parse the schema from the database using the SQL standard INFORMATION_SCHEMA. 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.



190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
 
# File 'lib/sequel_core/schema/sql.rb', line 190

def schema(table_name = nil, opts={})
  if opts[:reload] && @schemas
    if table_name
      @schemas.delete(table_name)
    else
      @schemas = nil
    end
  end

  if table_name
    return @schemas[table_name] if @schemas && @schemas[table_name]
  else
    return @schemas if @schemas
  end

  if table_name
    @schemas ||= {}
    @schemas[table_name] ||= schema_parse_table(table_name, opts)
  else
    @schemas = schema_parse_tables(opts)
  end
end

#schema_utility_dataset ⇒ Object

The dataset to use for proxying certain schema methods.



214
215
216
 
# File 'lib/sequel_core/schema/sql.rb', line 214

def schema_utility_dataset
  @schema_utility_dataset ||= dataset
end

#type_literal(t) ⇒ Object

SQL fragment specifying the type of a given column.



219
220
221
 
# File 'lib/sequel_core/schema/sql.rb', line 219

def type_literal(t)
  t.is_a?(Symbol) ? t.to_s : literal(t)
end