cmd — Command & SQL Templates

Syntax

A cmd template body contains either a bare identifier (naming the command type) or a call expression (naming the operation and its arguments). Bare form — the command type is a single word. The template parameters become the command's parameters.

      cmd [sql] rawQuery(sql: String) {
          sql
      }
    

Call form — the command is a named operation with typed arguments. Arguments can reference template parameters with $${ }.

      cmd [sql] findUser(email: String) {
          SELECT(
              table: users,
              cols:  "id, name, email",
              where: "email = '$${email}'"
          )
      }

      cmd [sql] createUser(name: String, email: String) {
          INSERT(
              table:  users,
              cols:   "name, email",
              values: "'$${name}', '$${email}'"
          )
      }
    

Generating SQL

Call Generator.generate() to produce the SQL string. The SqlGen generator handles all standard SQL operations.

      use TLang.Generator

      cmd [sql] allUsers() {
          SELECT(table: users)
      }

      cmd [sql] userById(id: String) {
          SELECT(
              table: users,
              where: "id = $${id}"
          )
      }

      func main(): String {
          let allSql   = Generator.generate(allUsers())
          let userSql  = Generator.generate(userById("42"))
          Terminal.println(allSql)    // SELECT * FROM users;
          Terminal.println(userSql)   // SELECT * FROM users WHERE id = 42;
          return "done"
      }
    

Supported SQL Operations

SELECT — query rows from a table.

      cmd [sql] search(term: String) {
          SELECT(
              table:  products,
              cols:   "id, name, price",
              where:  "name LIKE '%$${term}%'",
              order:  "name ASC",
              limit:  "20"
          )
      }
    

INSERT — insert new rows.

      cmd [sql] addProduct(name: String, price: String) {
          INSERT(
              table:  products,
              cols:   "name, price",
              values: "'$${name}', $${price}"
          )
      }
    

UPDATE — update existing rows.

      cmd [sql] updatePrice(id: String, price: String) {
          UPDATE(
              table: products,
              set:   "price = $${price}",
              where: "id = $${id}"
          )
      }
    

DELETE — remove rows.

      cmd [sql] removeUser(id: String) {
          DELETE(table: users, where: "id = $${id}")
      }
    

CREATE-TABLE and CREATE-TABLE-IF — schema creation.

      cmd [sql] createUsersTable() {
          CREATE-TABLE-IF(
              table: users,
              cols:  "id BIGSERIAL PRIMARY KEY, name VARCHAR(255) NOT NULL, email VARCHAR(255) UNIQUE"
          )
      }
    

DROP-TABLE, DROP-TABLE-IF, TRUNCATE, CREATE-INDEX, DROP-INDEX, ALTER-ADD, ALTER-DROP are also supported.

      cmd [sql] addColumn(table: String, col: String) {
          ALTER-ADD(table: $${table}, col: $${col})
      }

      cmd [sql] dropColumn(table: String, col: String) {
          ALTER-DROP(table: $${table}, col: $${col})
      }
    

Shell Commands

A bash generator can be registered and cmd templates used to model shell operations.

      cmd [bash] buildProject(dir: String) {
          run(cmd: "cd $${dir} && ./gradlew build")
      }
    

Full Migration Example

Generate a complete schema migration file from model entities.

      use TLang.Generator
      use TLang.File
      use TLang.Leaf
      use TLang.StringBuilder

      cmd [sql] createTable(tableName: String, cols: String) {
          CREATE-TABLE-IF(
              table: $${tableName},
              cols:  $${cols}
          )
      }

      model {
          set users    { table: "users",    cols: "id BIGSERIAL PRIMARY KEY, name TEXT, email TEXT UNIQUE" }
          set products { table: "products", cols: "id BIGSERIAL PRIMARY KEY, name TEXT, price NUMERIC" }
          set orders   { table: "orders",   cols: "id BIGSERIAL PRIMARY KEY, user_id BIGINT REFERENCES users(id), total NUMERIC" }
      }

      func main(): String {
          let sb    = StringBuilder.create()
          let model = Leaf.model()
          for (k in Leaf.keys(model)) {
              let e   = Leaf.get(model, k)
              let sql = Generator.generate(createTable(e.table, e.cols))
              let sb  = StringBuilder.append(sb, sql)
          }
          let migration = StringBuilder.build(sb)
          File.write("db/V1__init.sql", migration, true)
          return "done"
      }