Data Update | Smartchart开发手册

Use Cases #

Update data based on primary keys

Configuration #

Create a lazy-load dataset:

dataset = {
    "table": "table_name",
    "id": "id",
    "fDict": {"name": "code", "m_age": "age"},
    "sFields": ["age", "update_date"],
    "autoDict": {"updater": "$username", "update_date": "curdate()"}
}

id: Primary key field
fDict: Field mapping between input and database
sFields: Special fields (numeric types, functions)
autoDict: Auto-submitted data

Update Method #

Same as data filling, add update=1: ds_save(0, dataset, 1)

Data Format #

// Single row
dataset = {'id': 123, 'name': 'john'}
// Multiple rows
dataset = [{'id': 123, 'name': 'john'}, {'id': 124, 'name': 'smith'}]

Composite Primary Key #

dataset = {
    "table": "table_name",
    "id": "concat(a,b)",
    "fDict": {"id": "concat(a,b)"},
    "sFields": [],
    "autoDict": {}
}

Auto-Generated Codes #

"autoCode": {"order_no": "SELECT CONCAT('SCDD', DATE_FORMAT(NOW(), '%Y%m%d'), LPAD(IFNULL(CAST(SUBSTRING(MAX(order_no), 13) AS UNSIGNED) + 1, 1), 3, '0')) AS o FROM work_order WHERE order_no LIKE CONCAT('SCDD', DATE_FORMAT(NOW(), '%Y%m%d'), '%')"}

Shorthand: "autoCode": {"order_no": "SCDD"}

Pre/Post Actions #

"preSql": ["truncate table abc"],
"postSql": ["insert into xxx select ...."]

Permission Control #

-- C: Create, U: Update, D: Delete
select 'CUD' from xxxx where username='$username'

"authDict": {"id": "dataset_id", "username": "$username"}

Trigger Workflow #

"flowDict": {"uuid": "4090cba-xx", "username": "$username", "title": "Approval", "remark": "$order_no", "variables": {"a": $qty}}

Custom Python Mode #

When data source is Python, receives 2 parameters:

content: dict or array string
update: 0, 1, 2