Rrm thoughts and ideas

Thoughts and Ideas

Notes on the JAX-RS PathSegments defined through @Path injection

@Path("/{name : regex}")

e.g.

@Path("/{table : .+}/_end/{limit}")

then inject with

@PathParam("table") List<PathSegment> tables;

Notes on database column and tablename escape characters

• Access and SQL Server uses brackets: [my column name]
• MySQL uses ticks: `my column name`
• SQL-99 standard and Oracle, PostgreSQL and SQlite, uses quotes: "my column name"

NOTE: MySQL, SQL Server can use quotes if they set up for it - MySQL must be in ANSI mode and SQL Server only supports it when QUOTED_IDENTIFIER is ON.

Need to add two connections.json attributes: "open column quote": "" "close column quote": "" if not supplied they would default to quotes i.e. "open column quote": "\"" "close column quote": "\"". I could use an attribute like "db type": "" instead, which would be one of "SQL99","MYSQL", or "MSSQL" and would use the appropriate column escape characters, it would default to "db type": "SQL99" if not supplied. I think I prefer the last attribute, and if SQL Server or MySQL are using quotes then just use the "SQL99" value instead.

Basics first - single table CRUD + Meta-data

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 count(*),{field1},...{fieldn} from {table} - limited to 0 rows
The rest are the same as for the GET method minus the _join, _sort and _reverse ones.

1. HEAD /{table}

2. HEAD /{table}/{field1}/.../{fieldn}/_filter

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

---

1. insert into {table} ({field1},{field2},...,{fieldn}) values ({value1},{value2},...,{valuen})

1. PUT /{table}?field1=value1&field2=value2&...&fieldn=valuen

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

---

1. select * from {table}
2. select * from {table1},{table2} where {table1}.{field1}={table2}.{field2}
3. select {field1},...{fieldn} from {table}
4. select * from {table} order by [asc/desc] {field1},..., {field2}
5. select {ffield1},...{ffieldn} from {table} order by [asc/desc] {sfield1},..., {sfield2}
6. Same as 1. but limit to top {limit} rows
7. select * from {table} where id={id}
8. select * from {table} where {field1}={value1} and {field2}={value2} and ... and {fieldn}={valuen}
9. Same as 4. but limit to top {limit} rows

1. GET /{table}

2. GET /{table1}/{table2}/{field1}/{field2}/_join

3. GET /{table}/{field1}/.../{fieldn}/_filter

4. GET /{table}/{field1}/.../{fieldn}/_sort GET /{table}/{field1}/.../{fieldn}/_reverse

5. GET /{table}/{field1}/.../{fieldn}/_filter/{field1}/.../{fieldn}/_sort GET /{table}/{field1}/.../{fieldn}/_filter/{field1}/.../{fieldn}/_reverse

6. GET /{table}/{limit}

7. GET /{table}/_{id}

8. GET /{table}?field1=value1&field2=value2&...&fieldn=valuen

9. GET /{table}/{limit}?field1=value1&field2=value2&...&fieldn=valuen

{
  "limit": ?,
  "more rows": <true/false>,
  "rows": [[
    value1,
    value2,
    ...
    valuen
  ],
  ...
  [
    value1,
    value2,
    ...
    valuen
  ]]
}

---

1. update {table} set {field1}={value1},{field2}={value2},...,{fieldn}={valuen}
2. update {table} set {field1}={value1},{field2}={value2},...,{fieldn}={valuen} where id={id}

1. POST /{table}?field1=value1&field2=value2&...&fieldn=valuen

2. POST /{table}/_{id}?field1=value1&field2=value2&...&fieldn=valuen

{"count": ?}

---

1. delete from {table}
2. delete from {table} where id={id}
3. delete from {table} where {field1}={value1} and {field2}={value2} and ... and {fieldn}={valuen}

1. DELETE /{table}

2. DELETE /{table}/_{id}

3. DELETE /{table}?field1=value1&field2=value2&...&fieldn=valuen

{"count": ?}

RRM Other ideas

/{table}
/{table}/{limit}

select *
from {table}

---

/{table}?{field 1}={value 1}&{field 2}={value 2}&...&{field n}={value n}
/{table}/{limit}?{field 1}={value 1}&{field 2}={value 2}&...&{field n}={value n}

select *
from {table}
where {field 1} = {value 1}
and {field 2} = {value 2}
...
and {field n} = {value n}

---

/{table 1}_{alias 1}/.../{table n}_{alias n}?{table 1}.{field 1}={value 1}&...&{table n}.{field n}={value n}
/{table 1}_{alias 1}/.../{table n}_{alias n}/{limit}?{table ?}.{field 1}={value 1}&...&{table ?}.{field n}={value n}

select *
from {table 1} {alias 1}, {table 2} {alias 2}, ... , {table n} {alias n}
where {table ?}.{field 1} = {value 1}
...
and {table ?}.{field n} = {value n}

Additional thoughts

In the main URL - which equates to the from and select sections of an SQL statement:

{table}... from {table}

{table}.../_{field}... select {field} from {table}

{table}.../_{table}.{field}... select {table}.{field} from {table}

{table}_{alias}... from {table} {alias}

{limit}

In the query string - which equates to the where section of an SQL statement:

{field}={value}... where {field} = {value}

{table}.{field}={value}... where {table}.{field} = {value}

{table 1}_{table 2}={field 1}_{field 2}... where {table 1}.{field 1} = {table 2}.{field 2}

Do it completely in the URL section and have no query string section

Note: not RESTful as not using GET, PUT, POST, DELETE methods

/table1/table2/_select/table1.field1/field2/_where/table1.field1/_eq/table2.field2/_and/field3/_eq/value

select table1.field1, field2
from table1, table2
where table1.field1 = table2.field2
and field3 = value

_eq, _and, _or, _select, _all, _where