module Clear::SQL

Overview

Clear::SQL

Clear is made like an onion:

+------------------------------------+
|           THE ORM STACK            +
+------------------------------------+
|  Model | DB Views | Migrations     | < High level things
+---------------+--------------------+
|  Columns | Validation | Converters | < Mapping stuff
+---------------+--------------------+
|  Clear::SQL   | Clear::Expression  | < Low level SQL builder
+------------------------------------+
|  Crystal DB   | Crystal PG         | < Libs we deal with
+------------------------------------+

On the bottom stack, Clear offer SQL query building. Features provided are then used by top level parts of the engine.

The SQL module provide a simple API to generate #delete, #insert, #select and #update methods.

Each requests can be duplicated then modified and executed.

Note: Each request object is mutable. Therefore, to keep a request prior to modification, you must use manually the dup method.

Included Modules

Extended Modules

Defined in:

clear/sql/connection_pool.cr
clear/sql/errors.cr
clear/sql/fragment/column.cr
clear/sql/fragment/fragment.cr
clear/sql/fragment/from.cr
clear/sql/fragment/join.cr
clear/sql/lock.cr
clear/sql/query/connection.cr
clear/sql/query/from.cr
clear/sql/sql.cr
clear/sql/truncate.cr

Class Method Summary

Instance Method Summary

Instance methods inherited from module Clear::SQL::Transaction

after_commit(connection : String = "default", &block : DB::Connection -> Nil) after_commit, in_transaction?(connection : String = "default") in_transaction?, rollback(to = nil) rollback, rollback_transaction rollback_transaction, transaction(connection : String = "default", level : Level = Level::Serializable, &) transaction, with_savepoint(sp_name : Symbolic | Nil = nil, connection_name : String = "default", &) with_savepoint

Instance methods inherited from module Clear::SQL::Logger

log_query(sql : String, &) log_query

Class methods inherited from module Clear::SQL::Logger

colorize : Bool colorize, colorize=(colorize : Bool) colorize=, colorize_query(qry : String) colorize_query, display_mn_sec(x : Float64) : String display_mn_sec, display_time(x : Float64) : String display_time

Class Method Detail

def self.lock(table : String | Symbol, mode = "ACCESS EXCLUSIVE", connection = "default", &) #

Lock completetly a table.

Clear::SQL.lock("my_table") do
end

Optional parameter mode allow you to decide over the lock level Modes are:

  • ACCESS EXCLUSIVE (default)
  • ACCESS SHARE
  • ROW SHARE
  • ROW EXCLUSIVE
  • SHARE UPDATE EXCLUSIVE
  • SHARE
  • SHARE ROW EXCLUSIVE
  • EXCLUSIVE

See Official PG documentation for more informations


[View source]
def self.truncate(tablename : Clear::Model.class | String | Symbol, restart_sequence : Bool = false, cascade : Bool = false, truncate_inherited : Bool = true, connection_name : String = "default") forall T #

Truncate a table or a model

User.query.count          # => 200
Clear::SQL.truncate(User) # equivalent to Clear::SQL.truncate(User.table, connection_name: User.connection)
User.query.count          # => 0

SEE https://www.postgresql.org/docs/current/sql-truncate.html for more information.

  • restart_sequence set to true will append RESTART IDENTITY to the query
  • cascade set to true will append CASCADE to the query
  • truncate_inherited set to false will append ONLY to the query
  • connection_name will be: Model.connection or default unless optionally defined.

[View source]

Instance Method Detail

def add_connection(name : String, url : String) #

connect through a hash/named tuple of connections:

Clear::SQL.init(
  default: "postgres://postgres@localhost:5432/database",
  secondary: "postgres://postgres@localhost:5432/secondary"
)

[View source]
def delete(table : Symbolic) #

Prepare a DELETE table query


[View source]
def escape(x : String | Symbol) #

Escape the expression, double quoting it.

It allows use of reserved keywords as table or column name

NOTE Escape is used for escaping postgresql keyword. For example if you have a column named order (which is a reserved word), you want to escape it by double-quoting it.

For escaping STRING value, please use Clear::SQL.sanitize


[View source]
def execute(connection_name : String, sql) #

Execute a SQL statement without returning a result set

Usage:

Clear::SQL.execute("NOTIFY listener")

[View source]
def execute(sql : String) #

Execute a SQL statement without returning a result set

Usage:

Clear::SQL.execute("NOTIFY listener")

[View source]
def init(name : String, url : String) #

Initialize a new connection to a specific database Use "default" connection if no name is provided:

init("postgres://postgres@localhost:5432/database") # use "default" connection
init("secondary_db", "postgres://postgres@localhost:5432/secondary_db")

[View source]
def init(url : String) #

Initialize a new connection to a specific database Use "default" connection if no name is provided:

init("postgres://postgres@localhost:5432/database") # use "default" connection
init("secondary_db", "postgres://postgres@localhost:5432/secondary_db")

[View source]
def init(connections : Hash(Symbolic, String)) #

connect through a hash/named tuple of connections:

Clear::SQL.init(
  default: "postgres://postgres@localhost:5432/database",
  secondary: "postgres://postgres@localhost:5432/secondary"
)

[View source]
def init(**__named_tuple) #

connect through a hash/named tuple of connections:

Clear::SQL.init(
  default: "postgres://postgres@localhost:5432/database",
  secondary: "postgres://postgres@localhost:5432/secondary"
)

[View source]
def insert(table : Symbolic, args : NamedTuple) #

Alias of #insert_into, for developers in hurry :ditto:


[View source]
def insert(table : Symbolic, *args) #

Alias of #insert_into, for developers in hurry :ditto:


[View source]
def insert #

Create a new blank INSERT query. See Clear::SQL::InsertQuery


[View source]
def insert_into(table : Symbolic) #

Prepare a new INSERT INTO table query :ditto:


[View source]
def insert_into(table : Symbolic, *args) #

Start an INSERT INTO table query

Clear::SQL.insert_into("table", {id: 1, name: "hello"}, {id: 2, name: "World"})

[View source]
def raw(__template, *__args) #

This provide a fast way to create SQL fragment while escaping items, both with ? and :key system:

query = Mode.query.select(Clear::SQL.raw("CASE WHEN x=:x THEN 1 ELSE 0 END as check", x: "blabla"))
query = Mode.query.select(Clear::SQL.raw("CASE WHEN x=? THEN 1 ELSE 0 END as check", "blabla"))

note than returned string is tagged as unsafe and SQL inject is possible (so beware!)


[View source]
def raw(__template, **__keys) #

This provide a fast way to create SQL fragment while escaping items, both with ? and :key system:

query = Mode.query.select(Clear::SQL.raw("CASE WHEN x=:x THEN 1 ELSE 0 END as check", x: "blabla"))
query = Mode.query.select(Clear::SQL.raw("CASE WHEN x=? THEN 1 ELSE 0 END as check", "blabla"))

note than returned string is tagged as unsafe and SQL inject is possible (so beware!)


[View source]
def sanitize(x) #

Sanitize string and convert some literals (e.g. Time)


[View source]
def select(*args) #

Start a SELECT ... query


[View source]
def select(**args) #

Start a SELECT ... query


[View source]
def unsafe(x) #

Create an unsafe expression, which can be used in many places in Clear as substitute for string

  select.where("x = ?", Clear::SQL.unsafe("y")) # SELECT ... WHERE x = y

[View source]
def update(table : Symbolic) #

Start a UPDATE table query. See Clear::SQL::UpdateQuery


[View source]