module Clear::SQL
Overview
Clear::SQL
Clear is made like an onion:
+------------------------------------+
| THE ORM STACK +
+------------------------------------+
| Model | DB Views | Migrations | < High Level Tools
+---------------+--------------------+
| Columns | Validation | Converters | < Mapping system
+---------------+--------------------+
| Clear::SQL | Clear::Expression | < Low Level SQL Builder
+------------------------------------+
| Crystal DB | Crystal PG | < Low Level connection
+------------------------------------+
On the bottom stack, Clear offer SQL query building. Theses features 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 update and store a request,
you must use manually the dup
method.
Included Modules
Extended Modules
Defined in:
clear/sql/connection_pool.crclear/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
-
.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)
-
#delete(table : Symbolic)
Start a DELETE table query
-
#escape(x : String | Symbol)
Escape the expression, double quoting it.
-
#execute(connection_name : String, sql)
Execute a SQL statement on a specific connection.
-
#execute(sql)
Execute a SQL statement.
- #init(name : String, url : String)
- #init(url : String)
- #init(connections : Hash(Symbolic, String))
- #insert(table, args : NamedTuple)
-
#insert(table, *args)
Alias of
#insert_into
, for hurry developers -
#insert
Create a new INSERT query
-
#insert_into(table : Symbolic)
Prepare a new INSERT INTO table query :ditto:
-
#insert_into(table : Symbolic, *args)
Start an INSERT INTO table query
-
#raw(x, *params)
This provide a fast way to create SQL fragment while escaping items, both with
?
and:key
system: - #raw(__template, **params)
-
#raw_enum(x, params : Enumerable(T)) forall T
See
self.raw
Can pass an array to this version -
#rollback
Raise a rollback, in case of transaction
-
#sanitize(x)
Sanitize string and convert some literals (e.g.
-
#select(*args)
Start a SELECT FROM table query
- #unsafe(x)
-
#update(table)
Start a UPDATE table query
-
#with_savepoint(connection_name = "default", &)
Create a transaction, but this one is stackable using savepoints.
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, &)
log_query
Class methods inherited from module Clear::SQL::Logger
colorize=(colorize : Bool)
colorize=,
colorize? : Bool
colorize?,
colorize_query(qry : String)
colorize_query,
display_mn_sec(x) : String
display_mn_sec,
display_time(x) : String
display_time
Class Method Detail
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
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 appendRESTART IDENTITY
to the querycascade
set to true will appendCASCADE
to the querytruncate_inherited
set to false will appendONLY
to the queryconnection_name
will be:Model.connection
ordefault
unless optionally defined.
Instance Method Detail
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
Execute a SQL statement on a specific connection.
Usage: Clear::SQL.execute("seconddatabase", "SELECT 1 FROM users")
Start an INSERT INTO table query
Clear::SQL.insert_into("table", {id: 1, name: "hello"}, {id: 2, name: "World"})
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"))
Create a transaction, but this one is stackable using savepoints.
Example:
Clear::SQL.with_savepoint do
# do something
Clear::SQL.with_savepoint do
rollback # < Rollback only the last `with_savepoint` block
end
end