module

Clear::SQL

Inherits Clear::SQL::Transaction < Clear::SQL::Logger

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.

Class methods

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

Source
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.
Source

Instance methods

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"
)
Source
delete(table : Symbolic)

Prepare a DELETE table query

Source
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

Source
execute(connection_name : String, sql)

Execute a SQL statement without returning a result set

Usage:

Clear::SQL.execute("NOTIFY listener")
Source
execute(sql : String)

Execute a SQL statement without returning a result set

Usage:

Clear::SQL.execute("NOTIFY listener")
Source
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")
Source
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")
Source
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"
)
Source
init

connect through a hash/named tuple of connections:

Clear::SQL.init(
  default: "postgres://postgres@localhost:5432/database",
  secondary: "postgres://postgres@localhost:5432/secondary"
)
Source
insert(table : Symbolic, args : NamedTuple)

Alias of insert_into, for developers in hurry :ditto:

Source
insert(table : Symbolic, *args)

Alias of insert_into, for developers in hurry :ditto:

Source
insert

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

Source
insert_into(table : Symbolic)

Prepare a new INSERT INTO table query :ditto:

Source
insert_into(table : Symbolic, *args)

Start an INSERT INTO table query

Clear::SQL.insert_into("table", {id: 1, name: "hello"}, {id: 2, name: "World"})
Source
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!)

Source
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!)

Source
sanitize(x)

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

Source
select(*args)

Start a SELECT ... query

Source
select

Start a SELECT ... query

Source
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
Source
update(table : Symbolic)

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

Source

Nested types