class CQL::Table

Overview

Represents a table in the database: its columns, primary key, indexes and constraints, and the DDL that creates, drops or truncates it.

Tables are declared in a Schema.define block or with Schema#table, and read back by name.

Usage

schema.table :tags do
  primary :id, Int64
  column :name, String
end

tags = schema.tags
tags.create_sql # => "CREATE TABLE IF NOT EXISTS tags (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL)"
tags.create!
tags.truncate!
tags.drop!

Included Modules

Direct Known Subclasses

Defined in:

table.cr

Constant Summary

Log = CQL.config.logger

Constructors

Macro Summary

Instance Method Summary

Constructor Detail

def self.new(table_name : Symbol, schema : Schema, as_name : String | Nil = nil) #

Creates a new table instance. Applications declare tables with Schema#table instead.

  • @param table_name [Symbol] The name of the table
  • @param schema [Schema] The schema this table belongs to
  • @param as_name [String, nil] An optional alias for the table
  • @raise [Error] If the table name is invalid

[View source]

Macro Detail

macro method_missing(call) #

[View source]

Instance Method Detail

def add_index(columns : Array(Symbol), unique : Bool = false, name : String | Nil = nil) : Index #

Declares an index that #create! and Schema#build create after the table. Declaring an index with the same name again replaces the earlier one.

  • @param columns [Array(Symbol)] the columns to be indexed
  • @param unique [Bool] whether the index should be unique (default: false)
  • @param name [String, nil] the index name (default: idx_<table>_<columns>)
  • @return [Index] the new index

Example Adding a new index

schema.table :accounts do
  column :email, String
  column :username, String
  add_index([:email], unique: true)
  add_index([:email, :username], unique: true, name: "idx_accounts_login")
end

[View source]
def as_name : String | Nil #

[View source]
def bigint(name : Symbol, as as_name : String | Nil = nil, null : Bool = false, default : DB::Any = nil, unique : Bool = false, index : Bool = false) #

Adds a new column to the table.

  • @param name [Symbol] the name of the column to be added
  • @param as [String, nil] an optional alias for the column
  • @param null [Bool] whether the column allows null values (default: false)
  • @param default [DB::Any, nil] the default value for the column (default: nil)
  • @param unique [Bool] whether the column should have a unique constraint (default: false)
  • @param index [Bool] whether the column should be indexed (default: false)
  • @return [Column] the new column

Example Adding a new column with default options

schema.table :pages do
  bigint :views
  bigint :external_id, null: true, unique: true, index: true
end

[View source]
def blob(name : Symbol, as as_name : String | Nil = nil, null : Bool = false, default : DB::Any = nil, unique : Bool = false, size : Int32 | Nil = nil, index : Bool = false) #

Adds a new column to the table.

  • @param name [Symbol] the name of the column to be added
  • @param as [String, nil] an optional alias for the column
  • @param null [Bool] whether the column allows null values (default: false)
  • @param default [DB::Any, nil] the default value for the column (default: nil)
  • @param unique [Bool] whether the column should have a unique constraint (default: false)
  • @param index [Bool] whether the column should be indexed (default: false)
  • @return [Column] the new column

Example Adding a new column with default options

schema.table :attachments do
  blob :data
  blob :thumbnail, null: true
end

[View source]
def boolean(name : Symbol, as as_name : String | Nil = nil, null : Bool = false, default : DB::Any = nil, unique : Bool = false, index : Bool = false) #

Adds a new column to the table.

  • @param name [Symbol] the name of the column to be added
  • @param as [String, nil] an optional alias for the column
  • @param null [Bool] whether the column allows null values (default: false)
  • @param default [DB::Any, nil] the default value for the column (default: nil)
  • @param unique [Bool] whether the column should have a unique constraint (default: false)
  • @param index [Bool] whether the column should be indexed (default: false)
  • @return [Column] the new column

Example Adding a new column with default options

schema.table :accounts do
  boolean :active
  boolean :admin, default: false, index: true
end

[View source]
def check_constraint(condition : String, name : String | Nil = nil) #

Adds a CHECK constraint to the table.

  • @param condition [String] The SQL condition for the check constraint.
  • @param name [String, nil] Optional name for the constraint.
  • @return [CheckConstraint] The created check constraint object.

Example

schema.table :products do
  column :price, Float64
  column :email, String
  check_constraint "price > 0"
  check_constraint "email LIKE '%@%'", name: "chk_email_format"
end

[View source]
def check_constraints : Array(CheckConstraint) #

[View source]
def column(name : Symbol, type : T.class, as as_name : String | Nil = nil, null : Bool = false, default : DB::Any = nil, unique : Bool = false, size : Int32 | Nil = nil, index : Bool = false) forall T #

Adds a new column to the table.

  • @param name [Symbol] the name of the column to be added
  • @param type [T.class] the data type of the column
  • @param as [String, nil] an optional alias for the column
  • @param null [Bool] whether the column allows null values (default: false)
  • @param default [DB::Any, nil] the default value for the column (default: nil)
  • @param unique [Bool] whether the column should have a unique constraint (default: false)
  • @param size [Int32, nil] the size of the column (default: nil)
  • @param index [Bool] whether the column should be indexed (default: false)
  • @return [Column] the new column

Example Adding a new column with default options

column :email, String

[View source]
def columns : Hash(Symbol, BaseColumn) #

[View source]
def columns_changed! : Nil #

[View source]
def columns_revision : Int32 #

Bumped whenever Schema#alter changes the columns, so SQL rendered from them can be redone.


[View source]
def create! #

Creates the table in the database. Goes through Schema#ddl_transaction.

  • @return [Nil]

Example

schema.users.create!

[View source]
def create_index_sqls(generator : Expression::Generator = schema.new_generator) : Array(String) #

Generates one CREATE INDEX statement per declared index, skipped when the index already exists (except on MySQL, which has no IF NOT EXISTS for indexes).


[View source]
def create_sql #

Generates the SQL to create the table. Includes column definitions and foreign key constraints.


[View source]
def create_statements : Array(String) #

The statements that create the table and its indexes, in execution order.


[View source]
def date(name : Symbol, as as_name : String | Nil = nil, null : Bool = false, default : DB::Any = nil, unique : Bool = false, index : Bool = false) #

Adds a new column to the table.

  • @param name [Symbol] the name of the column to be added
  • @param as [String, nil] an optional alias for the column
  • @param null [Bool] whether the column allows null values (default: false)
  • @param default [DB::Any, nil] the default value for the column (default: nil)
  • @param unique [Bool] whether the column should have a unique constraint (default: false)
  • @param index [Bool] whether the column should be indexed (default: false)
  • @return [Column] the new column

Example Adding a new column with default options

schema.table :employees do
  date :birthday
  date :hired_on, null: true
end

[View source]
def double(name : Symbol, as as_name : String | Nil = nil, null : Bool = false, default : DB::Any = nil, unique : Bool = false, index : Bool = false) #

Adds a new column to the table.

  • @param name [Symbol] the name of the column to be added
  • @param as [String, nil] an optional alias for the column
  • @param null [Bool] whether the column allows null values (default: false)
  • @param default [DB::Any, nil] the default value for the column (default: nil)
  • @param unique [Bool] whether the column should have a unique constraint (default: false)
  • @param index [Bool] whether the column should be indexed (default: false)
  • @return [Column] the new column

Example Adding a new column with default options

schema.table :places do
  double :latitude
  double :longitude, null: true, default: 0.0
end

[View source]
def drop! #

Drops the table from the database. Goes through Schema#ddl_transaction.

  • @return [Nil]

Example

schema.users.drop!

[View source]
def drop_sql #

Generates the SQL to drop the table.

  • @return [String] the SQL query

Example

schema.users.drop_sql # => "DROP TABLE IF EXISTS users"

[View source]
def expression #

Gets table expression for Sql query generation

  • @return [Expression::Table] the table expression

Example

schema.users.expression

[View source]
def float(name : Symbol, as as_name : String | Nil = nil, null : Bool = false, default : DB::Any = nil, unique : Bool = false, index : Bool = false) #

Adds a new column to the table.

  • @param name [Symbol] the name of the column to be added
  • @param as [String, nil] an optional alias for the column
  • @param null [Bool] whether the column allows null values (default: false)
  • @param default [DB::Any, nil] the default value for the column (default: nil)
  • @param unique [Bool] whether the column should have a unique constraint (default: false)
  • @param index [Bool] whether the column should be indexed (default: false)
  • @return [Column] the new column

Example Adding a new column with default options

schema.table :products do
  float :rating
  float :weight, null: true, default: 0.0
end

[View source]
def foreign_key(columns local_columns : Array(Symbol), references references_table : Symbol, references_columns : Array(Symbol) | Symbol | Nil = nil, name : String | Nil = nil, on_delete : Symbol = :no_action, on_update : Symbol = :no_action) #

Adds a foreign key constraint to the table.

  • @param columns [Array(Symbol), Symbol] The column(s) in this table.
  • @param references_table [Symbol] The table the foreign key references.
  • @param references_columns [Array(Symbol), Symbol, nil] The column(s) in the referenced table. Defaults to the primary key of the referenced table if nil.
  • @param name [String, nil] Optional name for the constraint.
  • @param on_delete [Symbol] Action on delete (:cascade, :restrict, :set_null, :no_action). Default :no_action.
  • @param on_update [Symbol] Action on update (:cascade, :restrict, :set_null, :no_action). Default :no_action.
  • @return [ForeignKey] The created foreign key object.

Example

schema.table :comments do
  primary :id, Int64
  column :post_id, Int64
  column :author_id, Int64

  # References the primary key of `posts`
  foreign_key :post_id, references: :posts

  # Explicit referenced column and ON DELETE CASCADE
  foreign_key [:author_id], references: :users, references_columns: [:id], on_delete: :cascade
end

Pass several columns in the array for a composite foreign key.


[View source]
def foreign_key(column local_column : Symbol, references references_table : Symbol, references_columns : Array(Symbol) | Symbol | Nil = nil, name : String | Nil = nil, on_delete : Symbol = :no_action, on_update : Symbol = :no_action) #

Overload to accept a single column symbol


[View source]
def foreign_keys : Array(ForeignKey) #

[View source]
def index(columns : Array(Symbol), unique : Bool = false, name : String | Nil = nil) : Index #

[View source]
def indexes : Array(Index) #

[View source]
def integer(name : Symbol, as as_name : String | Nil = nil, null : Bool = false, default : DB::Any = nil, unique : Bool = false, index : Bool = false) #

Adds a new column to the table.

  • @param name [Symbol] the name of the column to be added
  • @param as [String, nil] an optional alias for the column
  • @param null [Bool] whether the column allows null values (default: false)
  • @param default [DB::Any, nil] the default value for the column (default: nil)
  • @param unique [Bool] whether the column should have a unique constraint (default: false)
  • @param index [Bool] whether the column should be indexed (default: false)
  • @return [Column] the new column

Example Adding a new column with default options

schema.table :people do
  integer :age
  integer :score, as: "points", null: true, default: 0, index: true
end

[View source]
def interval(name : Symbol, as as_name : String | Nil = nil, null : Bool = false, default : DB::Any = nil, unique : Bool = false, index : Bool = false) #

Adds a new column to the table.

  • @param name [Symbol] the name of the column to be added
  • @param as [String, nil] an optional alias for the column
  • @param null [Bool] whether the column allows null values (default: false)
  • @param default [DB::Any, nil] the default value for the column (default: nil)
  • @param unique [Bool] whether the column should have a unique constraint (default: false)
  • @param index [Bool] whether the column should be indexed (default: false)
  • @return [Column] the new column

Example Adding a new column with default options

schema.table :subscriptions do
  interval :duration
  interval :grace_period, null: true
end

[View source]
def json(name : Symbol, as as_name : String | Nil = nil, null : Bool = false, default : DB::Any = nil, unique : Bool = false, index : Bool = false) #

Adds a new JSON column (JSON::Any) to the table.

  • @param name [Symbol] the name of the column to be added
  • @param as [String, nil] an optional alias for the column
  • @param null [Bool] whether the column allows null values (default: false)
  • @param default [DB::Any, nil] the default value for the column (default: nil)
  • @param unique [Bool] whether the column should have a unique constraint (default: false)
  • @param index [Bool] whether the column should be indexed (default: false)
  • @return [Column] the new column

Example Adding a new column with default options

schema.table :accounts do
  json :metadata
  json :settings, null: true
end

[View source]
def lock_version(name : Symbol = :version, as as_name : String | Nil = nil, null : Bool = false, default : DB::Any = 1, index : Bool = false) #

Adds a lock_version column for optimistic locking. Lock version is used to prevent race conditions when multiple users are updating the same record. It is a counter that is incremented each time the record is updated. If the record is updated by another user, the lock version will be different and the update will fail. The user will then need to retry the operation.

  • @param name [Symbol] the name of the column to be added (default: :version)
  • @param as [String, nil] an optional alias for the column
  • @param null [Bool] whether the column allows null values (default: false)
  • @param default [DB::Any] the default value for the column (default: 1)
  • @param index [Bool] whether the column should be indexed (default: false)
  • @return [Column] the new version column

Example Adding a version column for optimistic locking

lock_version :version

[View source]
def primary(name : Symbol, type : T.class = Int32, auto_increment : Bool = true) forall T #

Adds a new primary key column to the table.

  • @param name [Symbol] the name of the column to be added
  • @param type [T.class] the data type of the column (default: Int32)
  • @param auto_increment [Bool] whether the column should auto increment (default: true)
  • @return [PrimaryKey(T)] the new primary key column

Example Adding a new primary key column

schema.table :tags do
  primary :id, Int64
end

schema.table :countries do
  primary :id, Int32, auto_increment: false
end

[View source]
def primary : BaseColumn #

A bare #primary in a table block declares the default id (Int32) key; once a key is declared it returns that key. Query building uses #primary_key, which never declares one.


[View source]
def primary_key : BaseColumn | Nil #

The declared primary key, or nil. Unlike #primary, reading it never changes the table.


[View source]
def real(name : Symbol, as as_name : String | Nil = nil, null : Bool = false, default : DB::Any = nil, unique : Bool = false, index : Bool = false) #

Adds a new REAL column to the table (SQLite floating point type).

  • @param name [Symbol] the name of the column to be added
  • @param as [String, nil] an optional alias for the column
  • @param null [Bool] whether the column allows null values (default: false)
  • @param default [DB::Any, nil] the default value for the column (default: nil)
  • @param unique [Bool] whether the column should have a unique constraint (default: false)
  • @param index [Bool] whether the column should be indexed (default: false)
  • @return [Column] the new column

Example Adding a new column with default options

schema.table :products do
  real :price
  real :discount, null: true, default: 0.0
end

[View source]
def referenced_columns(references_table : Symbol, references_columns : Array(Symbol) | Symbol | Nil) : Array(Symbol) #

The columns a foreign key to references_table points at: references_columns when given, otherwise that table's primary key.


[View source]
def schema : Schema #

[View source]
def table_name : Symbol #

[View source]
def table_name=(table_name : Symbol) #

[View source]
def text(name : Symbol, as as_name : String | Nil = nil, null : Bool = false, default : DB::Any = nil, unique : Bool = false, size : Int32 | Nil = nil, index : Bool = false) #

Adds a new String column to the table.

  • @param name [Symbol] the name of the column to be added
  • @param as [String, nil] an optional alias for the column
  • @param null [Bool] whether the column allows null values (default: false)
  • @param default [DB::Any, nil] the default value for the column (default: nil)
  • @param unique [Bool] whether the column should have a unique constraint (default: false)
  • @param size [Int32, nil] the declared length, VARCHAR(size) (default: nil)
  • @param index [Bool] whether the column should be indexed (default: false)
  • @return [Column] the new column

Example Adding a new column with default options

schema.table :profiles do
  text :title
  text :bio, null: true, default: ""
end

[View source]
def timestamp(name : Symbol, as as_name : String | Nil = nil, null : Bool = false, default : DB::Any = nil, unique : Bool = false, index : Bool = false) #

Adds a new column to the table.

  • @param name [Symbol] the name of the column to be added
  • @param as [String, nil] an optional alias for the column
  • @param null [Bool] whether the column allows null values (default: false)
  • @param default [DB::Any, nil] the default value for the column (default: nil)
  • @param unique [Bool] whether the column should have a unique constraint (default: false)
  • @param index [Bool] whether the column should be indexed (default: false)
  • @return [Column] the new column

Example Adding a new column with default options

schema.table :events do
  timestamp :starts_at
  timestamp :cancelled_at, null: true, index: true
end

[View source]
def timestamps #

Adds a new column to the table.

Example Adding timestamps to the table

timestamps

[View source]
def truncate! #

Truncates the table in the database. Goes through Schema#ddl_transaction.

  • @return [Nil]

Example

schema.users.truncate!

[View source]
def truncate_sql #

Generates the SQL to truncate the table (DELETE FROM on SQLite, which has no TRUNCATE).

  • @return [String] the SQL query

Example

schema.users.truncate_sql # => "DELETE FROM users"

[View source]
def unique_constraint(columns : Array(Symbol), name : String | Nil = nil) #

Adds a UNIQUE constraint to the table.

  • @param columns [Array(Symbol)] The column(s) to include in the constraint.
  • @param name [String, nil] Optional name for the constraint.
  • @return [UniqueConstraint] The created unique constraint object.

Example

schema.table :people do
  column :first_name, String
  column :last_name, String
  column :email, String
  unique_constraint [:email]
  unique_constraint [:first_name, :last_name], name: "uk_person_name"
end

[View source]
def unique_constraints : Array(UniqueConstraint) #

[View source]
def varchar(name : Symbol, as as_name : String | Nil = nil, null : Bool = false, default : DB::Any = nil, unique : Bool = false, size : Int32 | Nil = nil, index : Bool = false) #

Adds a new column to the table.

  • @param name [Symbol] the name of the column to be added

[View source]
def version_columns #

Returns all columns in the table that are marked as version columns

  • @return [Array(Column)] an array of version columns

[View source]