- Call your Jetelina-API through Web Service
- Test page in HTML for your Jetelina-API
- Special tips for using ivm
- How to write more complex subquery sentence
1.Call your Jetelina-API through Web Service
I wish you are familiar with the Ajax function, whether it is used on the server or the client, in whatever programming language
i.Case Study in PostgreSQL/MySQL
ii.Case Study in redis
iii.Case Study in MongoDB
i.Case Study in PostgreSQL/MySQL
Case 1:
This is the example for executing ‘insert’ Jetelina-API.
table name: ftest
target columns: ftest_name, ftest_age, ftest_ave
api name: ji9
Under these conditions, the JSON form is expected like this.
IN: {“apino”:”ji9″,”ftest_name”:”Jhon”,”ftest_age”:26,”ftest_ave”:0.86}
behind execution SQL
insert into ftest (ftest_name,ftest_age,ftest_ave) values(‘Jhon’,26,0.86);
OUT: {“result”: true or false, “Jetelina”:”[{}]”, “message from Jetelina”:””…..”}
const getAjaxData = (s) => {
let pd = {};
pd["apino"] = "ji9";
pd["ftest_name"] = "Jhon";
pd["ftest_age"] = 26;
pd["ftest_ave"] = 0.86;
let dd = JSON.stringify(pd);
let url = "http://localhost:8000/apiactions"
$.ajax( {
url: url,
type: "post",
data: dd,
contentType: 'application/json',
dataType: "json",
}).done(function(result, textStatus, jqXHR) {
}).fail( function( result ){
}).always(function(result){
});
}
Case 2:
This is the example for executing ‘select’ Jetelina-API.
table name: ftest
require columns: ftest_jt_id, ftest_name, ftest_age, ftest_ave
api name: js10
Under these conditions, the JSON form is expected to be like this.
IN: {“apino”:”js10″,”subquery”:”[‘age’:30]”}
behind execution SQL:
select ftest.ftest_jt_id,ftest.ftest_name,ftest.ftest_age,ftest.ftest_ave from ftest as ftest where ftest.ftest_age < 30;
OUT:{“result”:true or false, “Jetelina”:[{“ftest_jt_id”:1,”ftest_name”:”Jhon”,”ftest_age”:26,”ftest_ave”:0.86}],[{…}],”message from Jetelina”:””…..””}
const getAjaxData = (s) => {
let pd = {};
pd["apino"] = "js10";
pd["subquery"] = "['age':30]"; ← attention at here
let dd = JSON.stringify(pd);
let url = "http://localhost:8000/apiactions"
$.ajax( {
url: url,
type: "post",
data: dd,
contentType: 'application/json',
dataType: "json",
}).done(function(result, textStatus, jqXHR) {
}).fail( function( result ){
}).always(function(result){
});
}
Case 3:
This is the example for executing ‘update’ or ‘delete’ Jetelina-API.
table name: ftest
require columns: ftest_jt_id, ftest_name, ftest_age, ftest_ave
api name: ‘ju11’ for ‘update’ or ‘jd12’ for ‘delete’
IN:
for updating:{“apino”:”ju11″,”ftest_name”:”Jhon”,”ftest_age”:26,”ftest_ave”:0.86,”subquery”:46}
behind execution SQL:
update ftest set ftest_name=’Jhon’, ftest_age=26, ftest_ave=0.86 where ftest_jt_id=46;
for deleting:
{“apino”:”jd12″,”subquery”:46}
behind execution SQL:
update ftest set jetelina_delete_flg=1 where ftest_jt_id=46;
OUT: {“result”: true or false, “Jetelina”:”[{}]”, “message from Jetelina”:””…..”}
Both ‘updating’ and ‘deleteing ‘ are the same.
You can see that the ‘delete’ does not perform physical deletion; it simply makes the data inaccessible by setting ‘jetelina_delete_flg’. Jetelina cleans up this hidden data later.
★in case of update Jetelina-API 'ju11'
const getAjaxData = (s) => {
let pd = {};
pd["apino"] = "ju11";
pd["ftest_name"] = "Jhon";
pd["ftest_age"] = 26;
pd["ftest_ave"] = 0.86;
pd["jt_id"] = 46; ← attention at here
let dd = JSON.stringify(pd);
let url = "http://localhost:8000/apiactions"
$.ajax( {
url: url,
type: "post",
data: dd,
contentType: 'application/json',
dataType: "json",
}).done(function(result, textStatus, jqXHR) {
}).fail( function( result ){
}).always(function(result){
});
}
★in case of delete Jetelina-API 'jd12'
const getAjaxData = (s) => {
let pd = {};
pd["apino"] = "jd12";
pd["jt_id"] = 46; ← attention at here
let dd = JSON.stringify(pd);
let url = "http://localhost:8000/apiactions"
$.ajax( {
url: url,
type: "post",
data: dd,
contentType: 'application/json',
dataType: "json",
}).done(function(result, textStatus, jqXHR) {
}).fail( function( result ){
}).always(function(result){
});
}
ii.Case Study in redis
Attention: there is no Jetelina-API for deleting key data.
Case 1:
In case of the ‘insert’ Jetelina-API,
inserting data in redis is inhibited by using Ajax. You can do it only on Jetelina.
Case 2:
The example for executing ‘select’ Jetelina-API.
key name: Jhonsaccesstimes
api name: ‘js20’
Under these conditions, the JSON form is expected like this.
IN: {“apino”:”js20″}
behind execution:
get jhonsaccesstimes
OUT: {“result”: true or false, “Jetelina”:[{“Jhonsaccesstimes”:”32”}],”message from Jetelina”:””…..””}
behind execution:
get jhonsaccesstimes
const getAjaxData = (s) => {
let pd = {};
pd["apino"] = "js20";
let dd = JSON.stringify(pd);
let url = "http://localhost:8000/apiactions"
$.ajax( {
url: url,
type: "post",
data: dd,
contentType: 'application/json',
dataType: "json",
}).done(function(result, textStatus, jqXHR) {
}).fail( function( result ){
}).always(function(result){
});
}
Case 3:
The example for executing ‘update’ Jetelina-API.
key name: Jhonsaccesstimes
value: 100
api name: ‘ju21’
Under these conditions, the JSON form is expected like this.
IN: {“apino”:”ju21″,”key”:”100″}
behind execution:
set jhonsaccesstimes “100”
OUT: {“result”: true or false, “Jetelina”:”[{}]”,” message from Jetelina”:””…..”}
const getAjaxData = (s) => {
let pd = {};
pd["apino"] = "ju20";
pd["Jhonsaccesstimes"] = "100";
let dd = JSON.stringify(pd);
let url = "http://localhost:8000/apiactions"
$.ajax( {
url: url,
type: "post",
data: dd,
contentType: 'application/json',
dataType: "json",
}).done(function(result, textStatus, jqXHR) {
}).fail( function( result ){
}).always(function(result){
});
}
iii.Case Study in MongoDB
Case 1:
This is the example for executing ‘insert’ Jetelina-API.
Document name: newdoc1
api name: ‘ji30’
Under these conditions, the JSON form is expected like this.
IN: {“apino”: “ji30”, “new document”: {“j_table”: “newdoc1”, “name”: “John”, “age”:26, “ave”:0.86}
behind execution:
inserting
OUT: {“result”: true or false, “Jetelina”:”[{}]”, “message from Jetelina”:””…..”}
const getAjaxData = (s) => {
let dd = '{"apino":"ji30","new document":{"j_table":"newdoc1","name":"jhon","age":"26","ave":"0.86"}}';
let url = "http://localhost:8000/apiactions"
$.ajax( {
url: url,
type: "post",
data: dd,
contentType: 'application/json',
dataType: "json",
}).done(function(result, textStatus, jqXHR) {
}).fail( function( result ){
}).always(function(result){
});
}
Case 2:
This is the example for executing ‘select’ or ‘delete’ Jetelina-API.
api name: ‘js31’ for ‘select’ or ‘jd32’ for ‘delete’
Under these conditions, the JSON form is expected like this.
IN:
for selecting: {“apino”:”js31″}
for deletgin :{“apino”: “jd32”}
OUT:
for selecting:
[“{ \”_id\” : { \”$oid\” : \”…..\” }, \”j_table\” : \”newdoc1\”, \”name\” : \”jhon\”, \”age\” : \”26\”, \”ave\” : \”0.86\” }”]
for deleting:
{“result”: true or false, “Jetelina”:”[{}]”,” message from Jetelina”:””….””}
const getAjaxData = (s) => {
let dd = '{"apino":"js31"}';
let url = "http://localhost:8000/apiactions"
$.ajax( {
url: url,
type: "post",
data: dd,
contentType: 'application/json',
dataType: "json",
}).done(function(result, textStatus, jqXHR) {
}).fail( function( result ){
}).always(function(result){
});
}
Case 3:
This is the example for executing ‘update’ Jetelina-API.
api name: ‘ju33’
Under these conditions, the JSON form is expected like this.
IN: {“apino”:”ju33″:”age”:”36″}
behind execution:
updatting teh ‘age’ data
OUT: {“result”: true or false, “Jetelina”:”[{}]”, “message from Jetelina”:””…..”}
const getAjaxData = (s) => {
let dd = '{"apino":"ju33","age":"36"}';
let url = "http://localhost:8000/apiactions"
$.ajax( {
url: url,
type: "post",
data: dd,
contentType: 'application/json',
dataType: "json",
}).done(function(result, textStatus, jqXHR) {
}).fail( function( result ){
}).always(function(result){
});
}
You see every Jetelina-API is thrown against the same URL, ‘http://localhost:8000/apiactions’, no matter what database. This is the only one post gate way for your web service. And this post URL has alternative names: ‘plzjetelina’ and ‘executionapi’, which work in the same way as ‘apiactions’.
2. Test page in HTML for your Jetelina-API
Jetelina has a Jetelina-API test page. This page is for testing your Jetelina-API by using similar program introduced above. You can access to this page on your site ‘….jetelina/jetelina_apitest.html’. e.g http://localhost:8000/jetelina/jetelina_apitest.html.
3. Special tips for using ivm
First, please refer here to know how working ivm on Jetelina.
Jetelina tries to use ivm as far as a DBI uses multi tables, but there are a few cases not to use ivm.
i. using regulated clauses
ivm has some regulated clauses, ‘as having,union,intersect,except,distinct on,tablesample,value,for update,share’.
ii.using variable parameter
at least one variable parameter that is posted via web in the subquery.
e.g. in 'where' clauser
this is not to be tried to use ivm, because parameters {age1} and {age2} are ordered in posting data
where (ftest.ftest_age between {age1} and {age2}) and (ftest1.ftes1_sex = 'male')
this is to be tried to use ivm
where (ftest.ftest_age between 20 and 50) and (ftest1.ftes1_sex = 'male')iii. using a special definition, ref here
Jetelina does not evaluate your subquery if there were ‘@mydef_start’ and ‘@mydef_end’
e.g. in 'where' clauser
where @mydef_start (ftest.ftest_age between 20 and 50) and (ftest1.ftes1_sex = 'male') @mydef_end
this is to be like below in Jetelina-API
where (ftest.ftest_age between 20 and 50) and (ftest1.ftes1_sex = 'male')
usually, this is to be liked below in Jetelina-API
this is to be tried to use ivm
where ((ftest.jetelina_delete_flg=0 and ftest1.jetelina_delete_flg=0)and(ftest.ftest_age between 20 and 50) and (ftest1.ftes1_sex = 'male'))
*.jetelina_delete_flg=0 indicates alive data.
*.jetelina_delete_flg=1 is deleted data.4. How to write more complex subquery sentence
There is a special key word to write more complicated subquery in there by your own.
‘@mydef_start’ and ‘@mydef_end’ are that.
i. you can order a deleted data.
Jetelina usually handles only alive data.
e.g. in case of 'where ftest.ftest_age = 20'
usually Jetelina handles like this to select only alive data
where ftest.jetelina_delete_flg = 0 and ftest.ftest_age = 20
if you set '@my_def_star' in there, the selected data has all data no matter which were deleted or not
where @mydef_start ftest.ftest_age = 20 @mydef_endii. write a complicated subquery.
I wish you do not get frustrated by overflowing Jetelina’s ability in writing a complicated code.
e.g. in case of 'where ftest.ftest_age = (select ...)'
recommend to use them as
where @mydef_start ftest.ftest_age = (select ...) @mydef_end
this is simply evaluated in Jetelina-API like below
where ftest.ftest_age = (select ...)