Documentation

FairCom REST API Reference

Previous Topic

Next Topic

FairCom DB REST API Tutorial

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.

Previous Topic

Next Topic

Preparation

  1. Make sure your FairCom Server is running.
    You can test this using the FairCom SQL or ISAM Explorer.
  2. The default database name for FairCom DB is ctreeSQL. If you have changed this, replace ctreeSQL in the URLs shown in this demo with your database name.
  3. Make sure your FairCom REST server is running on port 8080.
    You can test this by using this URL in your browser:

    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.

  4. Make sure curl is installed and working.
    You can test his typing in the command line:

    curl --version

  5. Create a test folder where you can create some JSON files.

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.

Previous Topic

Next Topic

Validating Your Connection

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"]}

Previous Topic

Next Topic

Creating a Table

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.

Previous Topic

Next Topic

Creating an Index

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.

Previous Topic

Next Topic

Inserting Records

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.

Previous Topic

Next Topic

Reading a Single Record

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}

Previous Topic

Next Topic

Querying Records

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:

Previous Topic

Next Topic

Deleting a Table

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

TOCIndex