examples

Examples

Runnable scripts for SQLRules.

Setup: install the packages needed by the examples

pip install "sqlrules>=2,<3" "sqlrules-sqlite>=2,<3"
python examples/basic_compile.py
python examples/select_usage.py

pip install "sqlrules-postgresql>=2,<3"
python examples/postgresql_pattern.py

From this repository (contributors)

make install
make examples

Captured output

Run from the repository root. Each output below is generated by the script as written; SQL is compiled locally and no database is contacted.

basic_compile.py

fields: ['age', 'name']
SELECT users.age, users.name
FROM users
WHERE CASE WHEN (CASE WHEN (users.age IS NOT NULL AND CASE WHEN (typeof(users.age) = :typeof_1 OR typeof(users.age) = :typeof_2 AND users.age = CAST(users.age AS BIGINT) AND users.age >= :age_1 AND users.age <= :age_2 OR typeof(users.age) = :typeof_3 AND (users.age REGEXP :age_3) AND length(replace(replace(replace(replace(replace(replace(replace(replace(users.age, :replace_1, :replace_2), :replace_3, :replace_4), :replace_5, :replace_6), :replace_7, :replace_8), :replace_9, :replace_10), :replace_11, :replace_12), :replace_13, :replace_14), :replace_15, :replace_16)) <= :length_1) THEN true ELSE false END = true AND CASE WHEN (typeof(users.age) = :typeof_1) THEN users.age WHEN (typeof(users.age) = :typeof_2 AND users.age = CAST(users.age AS BIGINT)) THEN CAST(users.age AS BIGINT) WHEN (typeof(users.age) = :typeof_3 AND (users.age REGEXP :age_3)) THEN CAST(users.age AS BIGINT) ELSE CAST(NULL AS BIGINT) END >= :param_1 AND CASE WHEN (typeof(users.age) = :typeof_1) THEN users.age WHEN (typeof(users.age) = :typeof_2 AND users.age = CAST(users.age AS BIGINT)) THEN CAST(users.age AS BIGINT) WHEN (typeof(users.age) = :typeof_3 AND (users.age REGEXP :age_3)) THEN CAST(users.age AS BIGINT) ELSE CAST(NULL AS BIGINT) END <= :param_2) THEN true ELSE false END = true AND CASE WHEN (users.name IS NOT NULL AND CASE WHEN (typeof(users.name) = :typeof_4) THEN true ELSE false END = true AND sqlrules_char_length(users.name) >= :sqlrules_char_length_1) THEN true ELSE false END = true) THEN true ELSE false END = true

select_usage.py

SELECT items.qty, items.status
FROM items
WHERE CASE WHEN (CASE WHEN (items.qty IS NOT NULL AND CASE WHEN (typeof(items.qty) = :typeof_1 OR typeof(items.qty) = :typeof_2 AND items.qty = CAST(items.qty AS BIGINT) AND items.qty >= :qty_1 AND items.qty <= :qty_2 OR typeof(items.qty) = :typeof_3 AND (items.qty REGEXP :qty_3) AND length(replace(replace(replace(replace(replace(replace(replace(replace(items.qty, :replace_1, :replace_2), :replace_3, :replace_4), :replace_5, :replace_6), :replace_7, :replace_8), :replace_9, :replace_10), :replace_11, :replace_12), :replace_13, :replace_14), :replace_15, :replace_16)) <= :length_1) THEN true ELSE false END = true AND CASE WHEN (typeof(items.qty) = :typeof_1) THEN items.qty WHEN (typeof(items.qty) = :typeof_2 AND items.qty = CAST(items.qty AS BIGINT)) THEN CAST(items.qty AS BIGINT) WHEN (typeof(items.qty) = :typeof_3 AND (items.qty REGEXP :qty_3)) THEN CAST(items.qty AS BIGINT) ELSE CAST(NULL AS BIGINT) END >= :param_1 AND CASE WHEN (typeof(items.qty) = :typeof_1) THEN items.qty WHEN (typeof(items.qty) = :typeof_2 AND items.qty = CAST(items.qty AS BIGINT)) THEN CAST(items.qty AS BIGINT) WHEN (typeof(items.qty) = :typeof_3 AND (items.qty REGEXP :qty_3)) THEN CAST(items.qty AS BIGINT) ELSE CAST(NULL AS BIGINT) END <= :param_2) THEN true ELSE false END = true AND CASE WHEN (items.status IS NOT NULL AND CASE WHEN (typeof(items.status) = :typeof_4) THEN true ELSE false END = true AND items.status IN (__[POSTCOMPILE_status_1])) THEN true ELSE false END = true) THEN true ELSE false END = true

postgresql_pattern.py

with plugin fields: ['name', 'meta']
SELECT rows.name, rows.meta
FROM rows
WHERE CASE WHEN (CASE WHEN (rows.name IS NOT NULL AND (rows.name ~ :name_1)) THEN true ELSE false END = true AND CASE WHEN (rows.meta IS NOT NULL AND (rows.meta @> :meta_1)) THEN true ELSE false END = true) THEN true ELSE false END = true

Application users should install from PyPI, not from packages/.