Basic RRM version 2

HEAD (Meta-data)

PUT (Create)

GET (Read)

POST (Update)

DELETE (Delete)

---

1. select * from {table} - limited to 0 rows
   select count(*) from {table}
2. select * from {table} where id={id} - limited to 0 rows
   select count(*) from {table}
3. select * from {table} where {field1}={value1} and ... and {fieldN}={valueN} - limited to 0 rows
   select count(*) from {table}

1. HEAD /{table}

2. HEAD /{table}/{id}

3. HEAD /{table}/{field1}/{value1}/.../{fieldN}/{valueN}/_keys

{
  "total rows": ?,
  "columns": [
    "field1",
    "field2",
    ...
    "fieldn"
  ],
  "types": [
    "TYPE1",
    "TYPE2",
    ...
    "TYPEN"
  ]
}

---

1. insert into {table} ({field1},...,{fieldN}) values ({value1},...,{valueN})

1. PUT /{table}?field1=value1&...&fieldN=valueN

{"count": ?, "keys": [?,...,?]}

---

1. select * from {table}
2. select * from {table} - limit to top {limit} rows
3. select * from {table} where id={id}
4. select * from {table} where id>{id} - limit to top {limit} rows
5. select * from {table} where {field1}={value1} and ... and {fieldN}={valueN}
6. select * from {table1}, {table2}
   where {table1}.{key} = {table2}.{key}
   and {table*}.{field1}={value1} and ... and {table*}.{fieldN}={valueN} 
7. All can have the following select and sort clauses attached:
   select {table*}.{field1},...,{table*}.{fieldN} ... 
   order by (ASC|DESC) {table*}.{field1},...,{table*}.{fieldN}

1. GET /{table}

2. GET /{table}/_{limit}

3. GET /{table}/{id}

4. GET /{table}/{id}/_{limit}

5. GET /{table}/{field1}/{value1}/.../{fieldN}/{valueN}[/{key}]/_keys

6. GET /{table1}/{field1}/{value1}/.../{fieldN}/{valueN}/{key}/_keys/ {table2}/{field1}/{value1}/.../{fieldN}/{valueN}/{key}/_keys

7. All the above can have the following URL Paramaters ?[_def.sort=reverse&][_s.][_j.]{table*}={field1}&...&[_s.][_j.]{table*}={fieldN}

The _s. marks fields to be sorted by, if _def.sort=reverse is specified in the params then the sorting is DESC not ASC. All fields specified will be shown. Note we could also add a _def.join=<join type> where <join type> might be inner, outer etc., but I'll have to think about that for the future, as each database tends to have different syntax for joining tables in these ways.

Note that in 5. and 6. the last field before _keys can be a {key} with no value, this will be used for the {id} if supplied. Also in 6. this {key} field must be supplied, and is used as the join key between the two tables. Also note that in 6. if the same table name is used in both _keys sections then a self join is created. The _j. marks the fields as coming from the second table when a self join is being used. Also the /{id}, /_{limit}, and /{id}/_{limit} endings can be used in conjunction with 5. and 6. as well.

Need some way to do <>,<,>,<=,>=,in,like etc. comparisons. At the moment {field}/{value} means {field}={value}. Perhaps could do {field}/_c.cond/{value} where cond is ne,lt,gt,le,ge,in,lk etc. not sure how in will work, as can't do lists, perhaps use a name as the value, and then in the params use ?_c.{name}={value1}&...&_c.{name}={valueN} though could perhaps just do a comma separated list in the URL {field}/_c.in/{value1},...,{valueN}. Need to work out how to escape characters in {value} sections, as the value could be anything - just use URL encoding?

{
  "limit": ?,
  "count": ?,
  "more rows": <true/false>,
  "name": {
    "field1": <column1 int>,
    "field2": <column2 int>,
    ...
    "fieldn": <columnn int>
  },
  "type": {
    "field1": "SQL TYPE1",
    "field2": "SQL TYPE2",
    ...
    "fieldn": "SQL TYPEN"
  },
  "rows": [[
    value1,
    value2,
    ...
    valuen
  ],
  ...
  [
    value1,
    value2,
    ...
    valuen
  ]]
}

The "name" attribute is there so that you can retrieve the column you want from the row using the columns database name e.g. if the returned JSON is stored in an object called db, and the second column in the database is called info, then you can get the second column from the third row by either db.rows[2][1], db.rows[2][db.name["info"]] or db.rows[2][db.name.info] in javaScript.

---

1. update {table} set {field1}={value1},...,{fieldN}={valueN}
2. update {table} set {field1}={value1},...,{fieldN}={valueN} where id={id}
3. update {table} set {field1}={value1},...,{fieldN}={valueN} 
   where {key1}={kvalue1} and ... and {keyN}={kvalueN}

1. POST /{table}?field1=value1&...&fieldN=valueN

2. POST /{table}/{id}?field1=value1&...&fieldN=valueN

3. POST /{table}/{key1}/{kvalue1}/.../{keyN}/{kvalueN}/_keys?field1=value1&...&fieldN=valueN

{"count": ?}

---

1. delete from {table}
2. delete from {table} where id={id}
3. delete from {table} where {field1}={value1} and ... and {fieldN}={valueN}

1. DELETE /{table}

2. DELETE /{table}/{id}

3. DELETE /{table}/{field1}/{value1}/.../{fieldN}/{valueN}/_keys

{"count": ?}