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

• Clear::SQL::Logger
• Clear::SQL::Transaction

Extended Modules

• Clear::SQL

Defined in:

Class Method Summary

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

  Lock completetly a table.

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

Instance Method Summary

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

  connect through a hash/named tuple of connections:

• #delete(table : Symbolic)

  Prepare a DELETE table query

• #escape(x : String | Symbol)

  Escape the expression, double quoting it.

• #execute(connection_name : String, sql)

  Execute a SQL statement without returning a result set

• #execute(sql : String)

  Execute a SQL statement without returning a result set

• #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") `

• #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") `

• #init(connections : Hash(Symbolic, String))

  connect through a hash/named tuple of connections:

• #init(**__named_tuple)

  connect through a hash/named tuple of connections:

• #insert

  Create a new blank INSERT query.

• #insert(table : Symbolic, *args)

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

• #insert(table : Symbolic, args : NamedTuple)

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

• #insert_into(table : Symbolic)

  Prepare a new INSERT INTO table query :ditto:

• #insert_into(table : Symbolic, *args)

  Start an INSERT INTO table query

• #raw(__template, *__args)

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

• #raw(__template, **__keys)

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

• #sanitize(x)

  Sanitize string and convert some literals (e.g.

• #select(*args)

  Start a SELECT ...

• #select(**args)

  Start a SELECT ...

• #unsafe(x)

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

• #update(table : Symbolic)

  Start a UPDATE table query.

Class Method Detail

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

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

Instance Method Detail

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

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

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

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

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

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

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

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

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"))

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"))

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