Version: 1.0
Initial release: 2015-07-07
Latest updated: 2015-07-29
Leandro Silva
Copyright © 2015 Leandro Silva
This projected is licensed under the terms of the MIT license.
PHP >= 5.3.3
This RESTful service allows users to perform CRUD operations on whitelisted tables of a MySQL database.
api/[version]/[table]?[queries]
fields The fields (in a comma delimited format) that you want returned in your response.describe Describes the table.limit A ‘LIMIT’ clause. Defaults to 10. To remove the limit completely, set it to null.offset An ‘OFFSET’ clause.group A ‘GROUP BY’ clause.sort Various ‘SORT’ types are accepted:
sort An ascending ‘ORDER BY’ clause (e.g. sort=age).sort-desc A descending ‘ORDER BY’ clause (e.g. sort-desc=age).where Various ‘WHERE’ types are accepted:
where A ‘WHERE’ equals clause (e.g. where(sex)=male).where-not A ‘WHERE’ not equals clause (e.g. where-not(sex)=male).where-gt A ‘WHERE’ greater than clause.where-gte A ‘WHERE’ greater than or equals clause.where-lt A ‘WHERE’ less than clause.where-lte A ‘WHERE’ less than or equals clause.where-like A ‘WHERE’ like clause.where-notlike A ‘WHERE’ not like clause.and Various ‘AND’ types are accepted:
and An ‘AND’ equals clause (e.g. where(sex)=male&and(age)=18).and-not An ‘AND’ not equals clause (e.g. where(sex)=male&and-not(age)=18).and-gt An ‘AND’ greater than clause.and-gte An ‘AND’ greater than or equals clause.and-lt An ‘AND’ less than clause.and-lte An ‘AND’ less than or equals clause.and-like An ‘AND’ like clause.and-notlike An ‘AND’ not like clause.or Various ‘OR’ types are accepted:
or An ‘OR’ equals clause (e.g. where(sex)=male&or(age)=18).or-not An ‘OR’ not equals clause (e.g. where(sex)=male&or-not(age)=18).or-gt An ‘OR’ greater than clause.or-gte An ‘OR’ greater than or equals clause.or-lt An ‘OR’ less than clause.or-lte An ‘OR’ less than or equals clause.or-like An ‘OR’ like clause.or-notlike An ‘OR’ not like clause.fn Run MySQL functions. Various function types are accepted:
count A ‘COUNT’ function (e.g. fn(count)=*).avg A ‘AVG’ function (e.g. fn(avg)=age).sum A ‘SUM’ function.min A ‘MIN’ function.max A ‘MAX’ function.debug Returns the SQL statement.path Returns the ‘apiPath’ property with the results. Defaults to true.X-Pretty-Print While this is defaults to true, to receive a response that is not pretty-printed, send your request with the ‘Pretty-Print’ header set to false (X-Pretty-Print: 0).
If-None-Match Use an ETag to check if your request has been modified. An ETag is returned with a successful GET request on a record. Note that it is not returned when requesting the API version (api/v1) nor when a response has no content (status code 204). Learn more on how to use an ETags here.
Accept-Encoding This API supports the gzip compression option.
This API uses Digest Authentication, so never send your API secret over the wire. RFC2617
To simplify authentication, I’ve created some helper files for PHP, shell script, and JavaScript (with browser and Node support). Reach out if you would like me to share them with you.
Note that unless you’re using this API over a secure connection (SSL/TLS), your request and response body can be easily retrieved.
In an effort to maintain database integrity, this API is strict on updating and deleting records; it only allows one record to be updated/deleted per request.
This API expects your table to have an id column (as “id” or “ID”). If an id column is present, an ‘apiPath’ property (a URI that represents its row) will be returned with each result. The id is assumed to be a unique value.
Except for successful GET requests (GETs that return a 200 status code), all responses include a ‘status’ property.
By default, numeric queries will be converted into numbers. The below will search for the number 1.0:
api/v1/my_table?where(version)=1.0
To explicitly search for a string, wrap your value in single quotes. The below example searches for the string 1.0:
api/v1/my_table?where(version)='1.0'
Unless explicitly stringified (see above), boolean queries will be converted into proper booleans:
api/v1/my_table?where(male)=true
If you receive unexpected results from a search, use the debug query parameter to confirm your statement is being properly created.
As a shorthand, you can express inline ‘OR’ and ‘AND’ clauses in query values by using semicolon and/or comma delimiters respectively. Inline clauses are smart and will match the clause type where the delimiter was used. For example, a where(id)=1;2;3 query will use an inline ‘OR’ equals clause whereas a where-not(id)=1;2;3 query will use an inline ‘OR’ not equals clause. See below.
You can express an inline ‘OR’ type clause in a query value by using a semicolon (";"):
api/my_table?where(user)=foo;bar - is the same as - api/my_table?where(user)=foo&or(user)=bar
To negate an inline ‘OR’ type clause, wrap your value in single quotes. In other words, to use a semicolon in a search:
api/my_table?where(user)='foo;bar'
You can express an inline ‘AND’ type clause in a query value by using a comma (","). Note that since the comma is used in a where-not query parameter, the inline ‘AND’ clause becomes an ‘AND’ not clause:
api/my_table?where-not(user)=foo,bar - is the same as - api/my_table?where-not(user)=foo&and-not(user)=bar
To negate an inline ‘AND’ type clause, wrap your value in single quotes. In other words, to use a comma in a search:
api/my_table?where-not(user)='foo,bar'
In order to use aliases, separate your field/function name and its alias with a tilde ("~"). Note that in order to run the same SQL function (via fn) multiple times, you must use aliases. See below.
The query api/v1/people_table?limit=1&fields=id,user_age will return:
{
"id": 1,
"user_age": 18,
"apiPath": "api/v1/people_table/where(id)=1"
}
While the same query with aliases (api/v1/people_table?limit=1&fields=id~personId,user_age~age) will return:
{
"personId": 1,
"age": 18,
"apiPath": "api/v1/people_table/where(id)=1"
}
–
The query api/v1/people_table?fn(avg)=height&fn(avg)=age&fn(sum)=age will return:
*Note that the height average gets overwritten by the age average since they share the same property name (avg).
{
"avg": 29,
"sum": 97,
}
While the same query with aliases (api/v1/people_table?fn(avg~avgHeight)=height&fn(avg~avgAge)=age&fn(sum~totalAges)=age) will return:
{
"avgHeight": 5.9,
"avgAge": 29,
"totalAges": 97,
}
api
GET = (200) Returns available versions.
{
"apiVersions": {
"v1": "api/v1"
}
}
POST = (403) Forbidden.
PUT = (403) Forbidden.
DELETE = (403) Forbidden.
HEAD = (200) Returns HTTP headers.
–
api/v1
GET = (200) Returns version information.
{
"version": 1,
"released": "2015-07-07",
"lastUpdated": "2015-07-10",
"doc": "api/v1/doc",
"moreInfo": "api",
"availableTables": [
"my_table",
"my_table2"
],
"apiPath": "api/v1"
}
POST = (403) Forbidden.
PUT = (403) Forbidden.
DELETE = (403) Forbidden.
HEAD = (200) Returns HTTP headers.
–
api/v1/my_table
GET = (200) Returns data from the specified table. As noted in the queries section, there is a default LIMIT value.
[
{
"id": 1,
"first": "Foo",
"mid": "Bar",
"last": "Baz",
"apiPath": "api/v1/my_table/where(id)=1"
},
{
"id": 2,
"first": "John",
"mid": "Jay",
"last": "Doe",
"apiPath": "api/v1/my_table/where(id)=2"
}
]
POST = (403) Forbidden.
PUT = (201) Creates a new record in the specified table.
Request:
{
"first": "John",
"mid": "Jocab-Jingleheimer",
"last": "Schmidt"
}
Response:
{
"msg": "Your resource was successfully created.",
"status": 201,
"apiPath": "api/v1/my_table/where(id)=3"
}
DELETE = (403) Forbidden.
HEAD = (200) Returns HTTP headers.
–
api/v1/my_table?fields=first&limit=null
GET = (200) Returns all the specified column data from the specified table.
[
{
"first": "Foo",
"apiPath": "api/v1/my_table/where(id)=1"
},
{
"first": "John",
"apiPath": "api/v1/my_table/where(id)=2"
},
{
"first": "John",
"apiPath": "api/v1/my_table/where(id)=3"
}
]
POST = (403) Forbidden.
PUT = (403) Forbidden.
DELETE = (403) Forbidden.
HEAD = (200) Returns HTTP headers.
–
api/v1/my_table?where(id)=3
GET = (200) Returns the specified record.
[
{
"id": 3,
"first": "John",
"mid": "Jocab-Jingleheimer",
"last": "Schmidt",
"apiPath": "api/v1/my_table/where(id)=3"
}
]
POST = (200) Updates the specified record.
Request:
{
"first": "Joanna",
"mid": "Jay",
"last": "Doe"
}
Response:
{
"msg": "Your resource was successfully updated.",
"status": 200,
"apiPath": "api/v1/my_table/where(id)=3",
}
PUT = (403) Forbidden.
DELETE = (204) Deletes the specified record.
HEAD = (200) Returns HTTP headers.
–
api/v1/my_table?where(first)=John&fields=first,last
GET = (200) Returns the first and last name from records where the first name is John from the specified table.
[
{
"first": "John",
"last": "Doe",
"apiPath": "api/v1/my_table/where(id)=2"
},
{
"first": "John",
"last": "Schmidt",
"apiPath": "api/v1/my_table/where(id)=3"
}
]
POST = (403) Expects a URI without queries.
PUT = (403) Expects a URI without queries.
DELETE = (403) Expects a URI without queries.
HEAD = (200) Returns HTTP headers.
–
api/v1/my_table?where(first)=John&fields=first,last&limit=1&offset=2
GET = (200) Returns the first and last name from a record where the first name is John from the specified table; using a limit and offset.
[
{
"first": "John",
"last": "Schmidt",
"apiPath": "api/v1/my_table/where(id)=3"
}
]
POST = (403) Expects a URI without queries.
PUT = (403) Expects a URI without queries.
DELETE = (403) Expects a URI without queries.
HEAD = (200) Returns HTTP headers.
–
api/v1/my_table?fn(count)=first
GET = (200) Returns the total records by counting the “first” column.
{
"count": 3
}
POST = (403) Expects a URI without queries.
PUT = (403) Expects a URI without queries.
DELETE = (403) Expects a URI without queries.
HEAD = (200) Returns HTTP headers.
–
api/v1/my_table?where(first)=John&and(last)=Doe;Schmidt&fields=first,last
GET = (200) Returns the first and last name from records where the first name is John and the last name is Doe or Schmidt from the specified table.
[
{
"first": "John",
"last": "Doe",
"apiPath": "api/v1/my_table/where(id)=2"
},
{
"first": "John",
"last": "Schmidt",
"apiPath": "api/v1/my_table/where(id)=3"
}
]
POST = (403) Expects a URI without queries.
PUT = (403) Expects a URI without queries.
DELETE = (403) Expects a URI without queries.
HEAD = (200) Returns HTTP headers.
Select CU numbers from the CU table where the type is ‘SPR’ and the ‘show_in_library’ value is set to anything but true.
api/cu?where(type)=SPR&and-not(show_in_lib)=true&fields=cu_num
–
Select CU numbers from the CU table where the type is ‘SPR’ or the ‘show_in_library’ value is set to true.
api/cu?where(type)=SPR&or(show_in_lib)=true&fields=cu_num
–
Select CU numbers from the CU table where the type is ‘SPR’ or ‘CTV.’
api/cu?where(type)=SPR;CTV&fields=cu_num
–
Select ‘stateDMA’ and ‘09/15/14’ records from the booked-amount table where the ‘stateDMA’ has the value 504 in it.
api/ab_booked_amount?where-like(stateDMA)=%504%&fields=09/15/14,stateDMA
–
Count the data from the gb table where records are dated between ‘2014-01-01’ and ‘2014-12-31’.
api/gb?where-gte(timestamp)=2014-01-01&and-lte(timestamp)=2014-12-31&fn(count)=*
–
Return 20 ‘start-date’ records from the workflow-benchmarks table where the start date is ‘11/15/2014,’ the end date is ‘01/24/2015,’ and the calculation mode is ‘desktop.’
api/ab_workflowBenchmarks?where(calcMode)=desktop&and(dateStart)=11/15/2014&and(dateEnd)=01/24/2015=&fields=dateStart&limit=20
–
Return all records from the CU table where the ‘member’ name begins with ‘lsilva’ and the ‘type’ does not have terms like ‘Pre,’ ‘PR,’ and ‘iRoll.’
api/v1/cu?limit=null&where-like(member)=lsilva%&and-notlike(type)=%Pre%,PR%,%iRoll%
–
Describe the users table.
api/users?describe
–
Return multiple averages from the people table. Note that in order to run the same function multiple times, you must give each subsequent function name an alias.
api/v1/people?fn(avg)=age&fn(avg~avgHeight)=height&fn(avg~avgWeight)=weight
–
Count the distinct ‘age’ records from the people table.
api/v1/people?fn(count)=distinct age
–
Return a list of unique ‘type’ records along with a count of rows in each type.
api/v1/cu?limit=null&fields=type,count(type)~count&group=type
/api to see available versions./api/v# to see available tables.api/v#/my_table).