KDFAPI-SQL
From KallestadWiki
SQL Level API commands interact with the database directly and bypass any defined workflow. SQL Commands are only available to administrators by default.
Contents |
Inserting Data
Inserting data directly via SQL
do command : sqli
pv object :
table : name of table in which you want to insert data
fv : hash of fieldnames and field values
The sqli command is intended to insert a single row into a single table. For bulk insertions and object insertions, other methods are available that are more appropriate.
Alternatively for a strict SOAP request
do command : nq table : table in which you are inserting data f1...fn : fields to insert v1...vn : values to insert mapped, of course, to the appropriate fn value
Response Object:
The response object will return the value returned by the database driver. Success/Failure will be indicated by the status value with appropriate error information set in the Errno and Error values per KDF Standard.
Updating Data
Updating a record directly via SQL
The sqlui command assumes a column named 'id', and that the 'id' column is a unique identifier.
do command : sqlui
pv object :
table : name of table in which you want to update data
fv : hash of fieldnames and field values to update
id : id of record to update
The sqlui command is intended to update a single row into a single table by id. For query based updates, other methods are available.
Alternatively for a strict SOAP request
do command : sqlui table : table in which you are inserting data f1...fn : fields to update v1...vn : values to update mapped, of course, to the appropriate fn value id : id of record to be updated
Response Object:
The response object will return the value returned by the database driver. Success/Failure will be indicated by the status value with appropriate error information set in the Errno and Error values per KDF Standard.
Deleting Data
Deleting a record directly via SQL.
The sqldi command assumes a column named 'id', and that the 'id' column is a unique identifier.
do command : sqldi
pv object :
table : name of table in which you want to insert data
id : id of record to delete
The sqldi command is intended to delete a single row into a single table by id. For query based updates, other methods are available.
Alternatively for a strict SOAP request
do command : sqldi table : table in which you are deleting data id : id of record to be deleted
Response Object:
The response object will return the value returned by the database driver. Success/Failure will be indicated by the status value with appropriate error information set in the Errno and Error values per KDF Standard.
Listing Tables
Lists tables within a given database context, defaulting to the main database connection
do command : sqlvt pv object :
Creating Tables
Creating a Table (MySQL Specific).
do command : sqlct
pv object :
table : name of table
tt : table type
col : table collation
fields : array of table columns
name : name of field
dt : datatype of field
null : whether or not the field is nullable
lv : Length (varchar) or Values (enum) of field
default : default value of field
ai : auto-increment field
index : create an index on this field (pk,ft,unique)
JSON Example
The following json pv value would create a kdf_ct_test MYISAM table with 3 columns - col1, col2, and col3 - each column defined as a VARCHAR(255)
{
"table":"kdf_ct_test",
"tt":"MYISAM",
"fields":[
{"name":"col1",
"dt":"varchar"},
{"name":"col3",
"dt":"varchar"},
{"name":"col2",
"dt":"varchar"}
]
}
Relevant Information
Storage Engine
tt defaults to innoDB if not present
other values include:
- MyISAM
- HEAP
- MEMORY
- MERGE
- MRG_MYISAM
- ISAM
- innoDB
- INNOBASE
The sqldi command is intended to delete a single row into a single table by id. For query based updates, other methods are available.
Response Object:
The response object will return the value returned by the database driver. Success/Failure will be indicated by the status value with appropriate error information set in the Errno and Error values per KDF Standard.
Altering Tables
adding columns
removing columns
changing columns
index management
adding indexes
removing indexes
Flushing Tables
Remove all records from a table completely from the database
do command : sqlflush pv object : name : Name of table to empty
Dropping Tables
Remove a table completely from the database
do command : sqldrp pv object : name : Name of table to drop
Kallestad Development Framework Topics
- KDF Home
- Project Overview
- Technical Information
- Licensing
