|
|
|
Introduction
FairCom DB® introduces a new REST API to create, read, update and delete tables, indexes and records.
This tutorial will show you how to use it with a free and open-source command-line tool called curl.
For more about FairCom DB, see the FairCom DB Developers Guide.
|
|
|
http://localhost:8080/ctree/api/v1/openapi
Note: If you have changed the port (using listening_http_port or listening_https_port, as described in HTTP Port), you will need to change the URLs in this tutorial accordingly.
curl --version
All versions of OS X starting with Jaguar (released 2002) come with curl/libcurl installed.
curl ships with Windows 10 build 1803 (released early May 2018).
For older versions of Windows, the official download location is: https://curl.haxx.se/dlwiz/?type=bin&os=Win32&flav=-
curl is usually installed by default on all Linux distributions.
This tutorial assumes that you have the REST API server running on the same machine as you run curl.
If this is not the case, you will have to modify the curl calls and change localhost to the host name or IP where the REST API server runs.
This tutorial assumes that you have the REST API server running without SSL/HTTPS.
If this is not the case you will have to modify the curl calls and change http:// to https://
You may have to adapt the port number, too.
The provided certificate file is self-signed. You will need to add it to your Trusted CA Store or replace it with your own certificate.
|
|
|
To validate your connection and make sure it's working, you can check if you have any existing tables. Depending on if you have any, you will either see an empty result set or your existing tables after executing the following command:
curl -u admin:ADMIN http://localhost:8080/ctree/api/v1/table/ctreeSQL
This should result in something similar to:
{"_tables":[]}
or
{"_tables":["test1","test2","test3"]}
|
|
|
This command changes into the test folder and creates a new file called create_table.txt.
Insert this text into the new file:
{"fields": [
{
"name": "id",
"type": "INTEGER",
"length": 4
},
{
"name": "name",
"type": "VARCHAR",
"length": 128
},
{
"name": "author",
"type": "VARCHAR",
"length": 128
},
{
"name": "country",
"type": "VARCHAR",
"length": 48
},
{
"name": "language",
"type": "VARCHAR",
"length": 548
},
{
"name": "published",
"type": "INTEGER",
"length": 4
},
{
"name": "pages",
"type": "INTEGER",
"length": 4
}
]}
Now execute the following command (in a single line):
curl -u admin:ADMIN -d @create_table.txt http://localhost:8080/ctree/api/v1/table/ctreeSQL/books
Check your database and you should see a new table with the columns specified in the JSON file.
|
|
|
Inside the test folder create a new file called create_index.txt.
Insert this text into the new file:
{"fields":[{"name": "country", "ascending": true}],"unique":false}
Now execute the following command (in a single line):
curl -u admin:ADMIN -d @create_index.txt http://localhost:8080/ctree/api/v1/index/ctreeSQL/books/c_index
Check your table and you should see a new index using the column specified in the JSON file.
|
|
|
Inside the test folder create a new file called create_records.txt.
Insert this text into the new file:
[{
"name": "Anna Karenina",
"author": "Leo Tolstoy",
"country": "Russia",
"language": "Russian",
"published": 1877,
"pages": 864
},
{
"name": "War and Peace",
"author": "Leo Tolstoy",
"country": "Russia",
"language": "Russian, with some French",
"published": 1869,
"pages": 1225
},
{
"name": "Adventures of Huckleberry Finn",
"author": "Mark Twain",
"country": "USA",
"language": "English",
"published": 1884,
"pages": 366
},
{
"name": "To Kill a Mockingbird",
"author": "Harper Lee",
"country": "USA",
"language": "English",
"published": 1960,
"pages": 281
},
{
"name": "Nineteen Eighty-Four",
"author": "George Orwell",
"country": "United Kingdom",
"language": "English",
"published": 1949,
"pages": 368
},
{
"name": "The Great Gatsby",
"author": "F. Scott Fitzgerald",
"country": "USA",
"language": "English",
"published": 1925,
"pages": 180
},
{
"name": "Moby Dick",
"author": "Herman Melville",
"country": "USA",
"language": "English",
"published": 1851,
"pages": 822
}]
Now execute the following command (in a single line):
curl -u admin:ADMIN -d @create_records.txt http://localhost:8080/ctree/api/v1/record/ctreeSQL/books
This should result in:
{"_ids":[1,2,3,4,5,6,7]}
Check your table and you should see 7 records matching the contents of the JSON file.
|
|
|
To read a single record execute the following command:
curl -u admin:ADMIN http://localhost:8080/ctree/api/v1/record/ctreeSQL/books/1
This should result in the following:
{"id":null,"name":"Anna Karenina","author":"Leo Tolstoy","country":"Russia","language":"Russian","published":1877,"pages":864}
|
|
|
Inside the test folder, create a new file called query_records.txt. See the Query section for more about Query and Find.
Insert this text into the new file:
{
"find": {
"country": {
"operator": "=",
"value": "USA"
}
}
}
Now execute the following command (in a single line):
curl -u admin:ADMIN -d @query_records.txt http://localhost:8080/ctree/api/v1/query/ctreeSQL/books/c_index?top=100
This should result in the following:
{
"_records": [{
"_id": 3,
"id": null,
"name": "Adventures of Huckleberry Finn",
"author": "Mark Twain",
"country": "USA",
"language": "English",
"published": 1884,
"pages": 366
}, {
"_id": 4,
"id": null,
"name": "To Kill a Mockingbird",
"author": "Harper Lee",
"country": "USA",
"language": "English",
"published": 1960,
"pages": 281
}, {
"_id": 6,
"id": null,
"name": "The Great Gatsby",
"author": "F. Scott Fitzgerald",
"country": "USA",
"language": "English",
"published": 1925,
"pages": 180
}, {
"_id": 7,
"id": null,
"name": "Moby Dick",
"author": "Herman Melville",
"country": "USA",
"language": "English",
"published": 1851,
"pages": 822
}]
}
See also:
|
|
|
If you would like to delete the demo table, execute the following command (in a single line):
Note: Be very careful to use the exact database and table name. This command is very dangerous. If you use it on a production database please make sure you have a recent and up-to-date backup just to be safe.
curl -u admin:ADMIN -X DELETE http://localhost:8080/ctree/api/v1/table/ctreeSQL/books
