Data Actions
Data actions read from or write to XINA databases.
Read Actions
SELECT
The primary read action in XINA. It closely mirrors the MySQL SELECT
query, and returns data as a table of values. The full syntax for the SELECT
object is available here.
Property | Value | Required | Default |
---|---|---|---|
action | "select" |
yes | |
select | select | yes | |
rows | integer |
no | 1000 |
use_strings | boolean |
no | false |
The server response to a SELECT
action will start with a header packet, containing a JSON array of JSON object(s) indicating the name
of each column as a string
and the XINA data type of each column as a string
. This will be followed by packet(s) containing the data, as a JSON array of of JSON array(s) of values. The optional rows
property sets the limit of rows per packet. If the use_strings
property is true
, all values will be stored as JSON strings instead of their associated JSON type.
Example
Given a table t
with two columns, a
(int(4)
), and b
(utf8text
), and three rows:
a | b |
---|---|
0 |
"x" |
1 |
"y" |
2 |
"z" |
The following SELECT
action:
{
"action": "select",
"select": {
"from": "t"
},
"rows": 2
}
Would return three server packets.
First, the header information:
100
[
{
"name": "a",
"type": "int(4)"
},
{
"name": "b",
"type": "utf8text"
}
]
Second, the first two rows (limited to two by the rows
property):
100
[
[ 0, "x" ],
[ 1, "y" ]
]
Third, the last remaining row (with the status code 200
indicating the end of the data):
200
[
[ 2, "z" ]
]
FETCH
Under Construction
The FETCH
action reads data from the database in a more structured format than the SELECT
action. The syntax and response format differs depending on what type of data is being fetched.
Records
Fetches one or more records from a database.
Property | Value |
---|---|
action |
"fetch" |
fetch |
"records" |
database |
database |
records |
records (optional) |
where |
expression (optional) |
order |
array of order terms (optional) |
limit |
expression (optional) |
offset |
expression (optional) |
Fetched records are returned as JSON objects. Unlike the SELECT
action there is no header packet. Each packet
will contain a JSON array of JSON object representations of records, with a 100
code if more are available, or a 200
code if all records have been sent.
DOWNLOAD
Under Construction
Write Actions
INSERT
The INSERT
action inserts one or more records into a XINA database.
By default, the action will fail if any records being inserted have duplicate key values already in the database. If a
different on_duplicate
property is set, duplicate records will be updated according to the rules in the table. Only
fields explicitly set in the INSERT
will be changed. This is analogous to an INSERT ... ON DUPLICATE KEY UPDATE
MySQL query.
Property | Value |
---|---|
action |
"insert" |
database |
database |
records |
records |
on_duplicate |
"fail" or "update" (optional, default "fail" ) |
Examples
Given a starting database containing key field k
, fields v1
, v2
, and v3
, with tags enabled, containing the
following two records:
k |
v1 |
v2 |
v3 |
tags |
---|---|---|---|---|
a | 1 | 2 | 3 | t1 |
b | 1 | 2 | 3 | t1 |
And inserting records:
[
{ "k": "a", "v1": 4, "v2": null, "tags": ["t2"] },
{ "k": "c", "v1": 1, "v2": null, "tags": ["t2"] }
]
"on_duplicate": "fail"
Action fails due to duplicate key value "a"
. No change occurs.
"on_duplicate": "update"
Record with key value "a"
is updated, and record with key value "c"
is inserted. Note that field v3
of "a"
is
unaffected because no inserted records specified an explicit value for v3
.
k |
v1 |
v2 |
v3 |
tags |
---|---|---|---|---|
a | 4 | null | 3 | t1, t2 |
b | 1 | 2 | 3 | t1 |
c | 1 | null | null | t2 |
REPLACE
The REPLACE
action inserts one or more records into a XINA database and overwrites any existing records with
duplicate keys.
Property | Value |
---|---|
action |
"replace" |
database |
database |
records |
records |
on_duplicate |
"update" , "delete" , or "trash" (if trash enabled for database) (optional, default "update" ) |
Examples
Given a starting database containing key field k
, fields v1
, v2
, and v3
, with tags enabled, containing the
following two records:
k |
v1 |
v2 |
v3 |
tags |
---|---|---|---|---|
a | 1 | 2 | 3 | t1 |
b | 1 | 2 | 3 | t1 |
And replacing records:
[
{ "k": "a", "v1": 4, "v2": null, "tags": ["t2"] },
{ "k": "c", "v1": 1, "v2": null, "tags": ["t2"] }
]
"on_duplicate": "update"
Record with key value "a"
is updated, and record with key value "c"
is inserted. Note that v3
of "a"
is now
null
and t1
is removed because all fields are overridden by the incoming record.
k |
v1 |
v2 |
v3 |
tags |
---|---|---|---|---|
a | 4 | null | null | t2 |
b | 1 | 2 | 3 | t1 |
c | 1 | null | null | t2 |
"on_duplicate": "trash"
or "on_duplicate": "delete"
Existing record with key value "a"
is deleted (or trashed), and new records "a"
and "c"
are inserted.
k |
v1 |
v2 |
v3 |
tags |
---|---|---|---|---|
b | 1 | 2 | 3 | t1 |
a | 4 | null | null | t2 |
c | 1 | null | null | t2 |
If "trash"
is used, the trash table now contains the original "a"
record.
k |
v1 |
v2 |
v3 |
tags |
---|---|---|---|---|
a | 1 | 2 | 3 | t1 |
SET
The SET
action sets a database to contain the provided records.
Property | Value |
---|---|
action |
"set" |
database |
database |
records |
records |
on_duplicate |
"update" , "delete" , or "trash" (if trash enabled for database) (optional, default "update" ) |
on_remove |
"delete" or "trash" (if trash enabled for database) (optional, default "trash" if enabled) |
Examples
Given a starting database containing key field k
, fields v1
, v2
, and v3
, with tags enabled, containing the
following two records:
k |
v1 |
v2 |
v3 |
tags |
---|---|---|---|---|
a | 1 | 2 | 3 | t1 |
b | 1 | 2 | 3 | t1 |
And setting records:
[
{ "k": "a", "v1": 4, "v2": null, "tags": ["t2"] },
{ "k": "c", "v1": 1, "v2": null, "tags": ["t2"] }
]
"on_duplicate": "update"
Record "a"
is updated, record "c"
is inserted, and record "b"
is deleted (or trashed, depending on on_remove
).
Note that v3
of "a"
is now null
and t1
is removed because all fields are overridden by the incoming record.
k |
v1 |
v2 |
v3 |
tags |
---|---|---|---|---|
a | 4 | null | null | t2 |
c | 1 | null | null | t2 |
"on_duplicate": "trash"
or "on_duplicate": "delete"
All existing records are deleted (or trashed, depending on on_remove
), and new records "a"
and "c"
are inserted.
k |
v1 |
v2 |
v3 |
tags |
---|---|---|---|---|
a | 4 | null | null | t2 |
c | 1 | null | null | t2 |
UPDATE
Under Construction
DELETE
The DELETE
action deletes one or more records from a database.
Deleted records and all associated data are permanently deleted and cannot be restored.
This action requires the DELETE
privilege.
Property | Value |
---|---|
action |
"delete" |
database |
database |
records |
records |
TRASH
The TRASH
action moves one or more records into the trash table of a database. This is only available in databases
with the trash feature enabled, otherwise the action will fail.
This action requires the TRASH
privilege.
Property | Value |
---|---|
action |
"trash" |
database |
database |
records |
records |
RESTORE
The RESTORE
action moves one or more records from the trash table of a database into the record table. This is only
available in databases with the trash feature enabled, otherwise the action will fail.
If any records being restored have duplicate keys as other records currently in the database the action will fail.
This action requires the TRASH
privilege.
Property | Value |
---|---|
action |
"restore" |
database |
database |
records |
records |
DISPOSE
The DISPOSE
action deletes one or more records from the trash table of a database.
Deleted records and all associated data are permanently deleted and cannot be restored.
This action requires the DELETE
privilege.
Property | Value |
---|---|
action |
"dispose" |
database |
database |
records |
records |