Module: Sqlyzer::Sql::Create

Included in:
Request
Defined in:
lib/sqlyzer/create.rb

Overview

Generate SQL commands for tables creation.

Instance Method Summary collapse

Instance Method Details

#sql_alter_table(name, &block) ⇒ Object

Generate a basic Sql ALTER command on table called name. Concat block return value.



106
107
108
 
# File 'lib/sqlyzer/create.rb', line 106

def     sql_alter_table(name, &block)
  Create::_sql_alter('TABLE', name, &block)
end

#sql_alter_table_fk(source, dest, from, to = nil, cascade = true) ⇒ Object Also known as: sql_alter_foreign_key

Generate a FOREIGN KEY constraint on fields from from table called source to another called dest on fields to. If to is nil, assumed that the fields have the same name in both source and dest tables. If cascade is true, rows will be deleted on reference break. from and to are assumed to be a Sqlyzer::Parameter::List.



172
173
174
175
176
177
178
179
180
181
182
 
# File 'lib/sqlyzer/create.rb', line 172

def     sql_alter_table_fk(source, dest, from, to = nil, cascade = true)
  to = from unless to
  cascade = cascade ? "CASCADE" : "RESTRICT"
  sql_alter_table(source) {
    "ADD CONSTRAINT FK_#{source.to_s.upcase}_TO_#{dest.to_s.upcase} FOREIGN KEY (\n\t" +
    from.join(",\n\t") +
    "\n) REFERENCES #{dest} (\n\t" +
    to.join(",\n\t") +
    "\n) ON UPDATE #{cascade} ON DELETE #{cascade}"
  }
end

#sql_alter_table_pk(name, keys) ⇒ Object Also known as: sql_alter_primary_key

Generate a PRIMARY KEY constraint on fields keys from table called name. keys is assumed to be a Sqlyzer::Parameter::List.



155
156
157
158
159
160
161
 
# File 'lib/sqlyzer/create.rb', line 155

def     sql_alter_table_pk(name, keys)
  sql_alter_table(name) {
    "ADD CONSTRAINT PK_#{name.to_s.upcase} PRIMARY KEY (\n\t" +
    keys.to_a.join(",\n\t") +
    "\n)"
  }
end

#sql_create_table(name, keys, values, inherit_from = nil) ⇒ Object

Create a new table called name, with Sqlyzer::Parameter::List keys as primary keys and values as regular fields. The fresh table may be optionally inherited from an other table call inherit_from if not nil.



88
89
90
91
92
93
94
95
96
97
98
99
100
 
# File 'lib/sqlyzer/create.rb', line 88

def     sql_create_table(name, keys, values, inherit_from = nil)
  Create::_sql_create('TABLE', name) {
    content = keys.clone.concat(values)
    content.collect! { |p|
      "#{p}\t#{p.to_sql_type}\t NOT NULL"
    }
    if inherit_from
      "(\n#{content.join ",\n"}\n) INHERITS (#{inherit_from})"
    else
      "(\n#{content.join ",\n"}\n)"
    end
  }
end

#sql_create_table_index(name, keys) ⇒ Object

Create an index on fields keys from table called name. keys is assumed to be a Sqlyzer::Parameter::List.



128
129
130
131
132
133
134
 
# File 'lib/sqlyzer/create.rb', line 128

def     sql_create_table_index(name, keys)
  Create::_sql_create('INDEX', "#{name.to_s.upcase}_PK") {
    "ON #{name} (\n\t" +
    keys.to_a.join(",\n\t") +
    "\n)"
  }
end

#sql_create_table_unique_index(name, keys) ⇒ Object Also known as: sql_create_table_index!

Create an unique index on primary keys keys from table called name. keys is assumed to be a Sqlyzer::Parameter::List.



140
141
142
143
144
145
146
 
# File 'lib/sqlyzer/create.rb', line 140

def     sql_create_table_unique_index(name, keys)
  Create::_sql_create('UNIQUE INDEX', "#{name.to_s.upcase}_PK") {
    "ON #{name} (\n\t" +
    keys.to_a.join(",\n\t") +
    "\n)"
  }
end

#sql_drop_table(name, cascade = true) ⇒ Object

Drop the table called name. The method will remove linked Sql objects if cascade is true.



114
115
116
117
118
119
120
 
# File 'lib/sqlyzer/create.rb', line 114

def     sql_drop_table(name, cascade = true)
  if cascade
    Create::_sql_drop('TABLE', name, 'CASCADE')
  else
    Create::_sql_drop('TABLE', name)
  end
end