Melbis Shop Guide: contents

3. MCP Server Reference › 3.3 Engine › Database › engine_db_execute

Changes data as a pool of steps: any queries, inserts, tree nodes, the sweep and the marks for the storefront’s cache. The reading steps select and dependent work here too.

The Right

“Direct access → Database → Modify data” (AGENT_ENGINE_DB_EXECUTE).

Parameters

Either pool or pool_source is needed, but not both.

The Steps

Any step can have a name: then its value or insert_id is lent to the following steps as @name.

generate takes an id from the generator. A generator belongs to the tables for which engine_db_tables shows gen; for the rest the id is issued by the DBMS itself through AUTO_INCREMENT, and the step refuses for them. There is a generator without a table too: store_clann issues the number of a product’s clan for the field clann of the table store.

insert takes rows — a list of rows, each an object with the same columns. One INSERT holds no more than 65 535 values — the rows multiplied by the columns; more than that goes in several insert steps.

The id and the parent_id of the tree steps are a whole number or @name. A word refuses and is not read as a zero: a zero is the top of the tree.

Trees

A tree is any table with the fields tindex, tlevel and absindex: the catalog topic, the reference directories info, the alternative catalogs topic_alt, the attributes *_key and others.

• The shape of a tree is changed by the tree_* steps only. They recount these three fields over the whole table, and an ordinary INSERT, UPDATE or DELETE will not repeat that.
• The service trees are closed: oper, orders_client_field and the users’ personal tables u_*.
• tree_add creates an empty row — only the id and the place in the tree. The fields are filled by the modify that follows, by the lent id.
• A new node of topic or info is seen by the administrator only, while the node has no rows in topic_right or info_right.
• The root of the catalog is id = 1. parent_id = 0 puts a node on the top level; that is how the first node of an empty reference directory info is created.
• A tree step marks its own table for the cache.

The Sweep

clear_dependent sweeps away the rows of the dependent tables that have been left without an owner, and goes on along the chain: after info the tails of info_value are cleaned. The answer cleared names, per table, how many rows were taken off; a busy table the step passes by and writes busy at it.

• A row with an empty key, if the field may stand empty, points at nobody by design, and the sweep does not touch it.
• The tables of people user and user_group are closed to the sweep.
• While the pool holds tables, the sweep refuses: the tables it has taken are busy too. It is put after unlock or run as a separate pool.
• The sweep itself marks the tables it has cleaned.

The Marks for the Cache

The storefront learns of a change to a table by a mark. The change step marks the named tables; the tree steps and the sweep mark their own themselves. The marks are put once at the end of the pool — a thousand inserts costs one mark. A table changed by a query without change is not marked, and the storefront goes on giving out the old.

Locks in a Pool

• lock takes all the named tables or none. A lock it waits out for a moment, and a table busy longer it refuses — even if it is a window of the program under the same login that holds it.
• unlock releases only what this pool has taken.
• At the end the pool releases everything it has taken — an unlock as the last step is not needed. What has been released is listed in unlocked.

The Answer

{"ok": false,
 "steps": [
   {"num": 1, "do": "lock", "tables": ["store"]},
   {"num": 2, "do": "generate", "value": 4187, "name": "new_id"},
   {"num": 3, "do": "insert", "error": "Duplicate entry '4187' for key 'PRIMARY'"}
 ],
 "changed": [], "unlocked": ["store"], "ms": 14}

Refusals

A step that did not pass stops the pool. The answer comes with a mark of error and the same JSON; the steps before the one that stopped are carried out, their changes stay.

The refusals before the pool starts are the same as at engine_db_select.

The common refusals — “Answers and Refusals”.