<!-- 
/*
Copyright 2020 apHarmony

This file is part of jsHarmony.

jsHarmony is free software: you can redistribute it and/or modify
it under the terms of the GNU Lesser General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.

jsHarmony is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU Lesser General Public License for more details.

You should have received a copy of the GNU Lesser General Public License
along with this package.  If not, see <http://www.gnu.org/licenses/>.
*/
-->
<script type="text/x-tutorial-info">
{
  "Title": "SQL Objects",
  "ID": "reference_sqlobjects",
  "Menu": ["Language Reference"]
}
</script>

<h3 class="tutorials_intro">SQL Objects</h3>

<pre>
sqlobject := {
  "type": "table", //table, view, code, code2
  "caption": ["Branch", "Branches"],
  "columns": [
    { //Column 
      "name": "c_id", 
      "type": "varchar",  //bigint, varchar, date, decimal, float, int, datetime, char, smallint, tinyint, time, boolean, binary
      "length":255, 
      "precision": [12,6], 
      "precision": 53, 
      "key": true, 
      "foreignkey": { "table_name": "field_name" }, 
      "foreignkey": { "table_name": { "column": "field_name", "on_delete": "cascade" } }, 
      "foreignkey": { "table_name": { "column": "field_name", "on_delete": "null" } }, 
      "identity": true, 
      "null": false,
      "unique": true,
      "actions": ["prevent_update"],

      "default": "ACTIVE",
      "default": { "sql": "jsh.map.timestamp" }
    }
  ],
  "foreignkeys": [
    { "columns": ["col1","col2"], "foreign_table": "tbl", "foreign_columns": ["fcol1","fcol2"] }
  ],
  "unique": [
    ["branch_name","branch_user_id"] //Multi-unique
  ],
  "dependencies": ["tbl1", "tbl2"], //Dependencies are used to order create statements in situations such as views with custom SQL
  "index": [
    { "columns": ["environment_id"] },
    { "columns": ["environment_id", "series_code"] } //Multi-index
  ],
  "sql_create": "...", //Override create statement, or set to empty string to ignore during create
  "sql_drop": "...", //Override drop statement, or set to empty string to ignore during drop

  //For views only - used to generate sql statement
  "tables": [ 
    "table1": {
      "columns": [
        "column1",
        "column2",
        { "name": "column3", "type": "varchar", "length": -1, "sqlselect": "concat(column1, column2)" }, //Custom column expression
      ]
    }
    "table2": {
      "columns": [
        "column4"
      ],
      "join_type": "left",  //left, right, inner

      "join_columns": {
        "table1.column1", "table2.column1",
        "table1.column2", "table2.column2"
      }
      //--or--
      "join_columns": [
        "SQL EXPR1",
        "SQL EXPR2"
      ]
    },
    "customsql_table": {
      "columns": [
        "custom_column_name",
      ],
      "sql": [
        "select column_expr as custom_column_name from ..."
      ],
    },
    "table_alias": {
      "table": "source_table_name",
    }
  ],
  //For views only - used to generate sql statement
  "distinct": true,
  "where": "column1='value1'",
  "group_by": "column1, columm2",
  "having": "column1='value1'",
  "order_by": "column1, columm2",
  "with": {
    "table_name": "sql_expression"
  },
  "with": {
    "table_name": {
      "recursive": ["var_name1", "var_name2", ...],
      "sql": "sql_expression"
    }
  },

  //Triggers for tables and views:
  //SQL Server - *AFTER* validate_insert, validate_update, insert, update, delete
  //PostgreSQL - *BEFORE* validate_insert, validate_update
  //PostgreSQL - *AFTER* insert, update, delete
  //SQLite - *BEFORE* validate_insert, validate_update, delete
  //SQLite - *AFTER* insert, update
  "triggers": [
    { "on": ["update", "insert","delete","validate_insert","validate_update"],
      "prefix": "{schema}_", //Optional - prefix for trigger name, to create unique standalone trigger
      "exec": [
        "set(branch_mtstmp,jsh.map.timestamp);"
        //set(field, value)          :: Set field to value
        //setif(cond, field, value)  :: Set field to value if condition met
        //update(col)                :: Return true if column value changed
        //top1(sql)                  :: Returns only first result of SQL expression
        //null(value)                :: Return true if value is null
        //errorif(cond, msg)         :: Generate Application Error if condition met
        //inserted(col)              :: Returns new value of column
        //deleted(col)               :: Returns previous value of column
        //with_insert_identity(table, col, insert_statement, ...)
        //                           :: Insert and get resulting table.col value as @@INSERT_ID for use in the ... statements
        //increment_changes(cnt)     :: Increment number of modified rowcount by cnt.  Default cnt=1
        //return_insert_key(tbl, col, sqlwhere) :: Return the inserted key in "instead of" view triggers
      ]
    }
  ],
  "init": [
    { "sql": "..." }
  ],
  "data_keys": ["column1", "column2"],
  "init_data": [
    { "field1": "value1", "field2": "value2" }
  ],
  "sample_data": [
    { "field1": "value1", "field2": "value2" }
  ]
}
</pre>