ODTUG at Home: RESTful Services Development with ORDS

How to build RESTful Services for your Oracle Database with Oracle REST Data Services.

Overview of good 'RESTful' practices, and how to apply those to your SQL and PL/SQL code to work with your Oracle Database via HTTP(s).

Transcript

1. Master Product Manager Vice President, R&D Jeff.D.Smith@oracle.com Kris.Rice@oracle.com @thatjeffsmith @krisrice Oracle REST Data Services RESTful Services for Oracle Database Jeff Smith Kris Rice
3. • What is REST? • Some quick HTTP basics • RESTful Development In 30 minutes…
4. Architectural Style • Client – Server • Stateless • Cacheable • Uniform Interface Exercising the API requires: • Location of our resource (URL) • Action to be performed (GET, PUT, …) REpresentational Stateless Transfer (REST)
5. Things you can do with REST APIs • Power applications – mobile, desktop, web… • HTTP(S) is ubiquitous • Continuous Integration & Delivery • Automation (BASH + cURL) • OCI comes with REST APIs for all tenancy and DBCS operations
6. ‘Good’ REST • Model resources, not actions: • GET /ords/hr/employees/ - GOOD • GET /ords/hr/delete_emp/ - BAD • DELETE /ords/hr/employees/97 – GOOD • Uniform operations on all resources: GET, POST, PUT, DELETE • Stateless requests, state transitions comm via hyper-links • Throwing HTTP onto your API <> REST
7. • Oracle REST Data Services (ORDS) • Auto REST enable your data tables, views, SQL, PL/SQL • Auto paged results • Auto JSON responses • Complete read-write functionality • Full dev support (GUI, CLI, API) • Oracle Cloud and On-Premises REST Access to your Oracle Database HTTPS/REST JDBC https://www.oracle.com/REST
8. • HTTPS vs SQL • {json} • Paged results • Links for each record • Meta-links for the collection Example: EMPS of DEPT 50 GET HTTPS://.../ords/hr/employees/?={“departmentID”:50}
9. Getting Started with RESTul Services I’m ready to build, now what?
10. Features RESTful Services, AutoREST, SQL Developer Web, DB API, SODA, REST Driver
11. GET https://host/ords/human/peeps/ SELECT * FROM EMP HTTP/1.1 200 OK { "employee_id": 100, "first_name": "Kris" ,"last_name": "Rice", , “email": “krisrice@oracle.com", {json} URI SQL & PLSQL Map & Bind HTTP Request HTTP Response Transform to JSON SQL Result Set / Standard OUT Oracle REST Data Services ORDS REST Request & Response Workflow
12. Request, ask for something Address: https://site.com/ords/thing/some Method: GET Header: Cache-Control: no-cache Body: n/a Response, what you get back Status Code: 200 OK Header: Content-Type: application/json Body: {…} Requests and Responses
13. curl --request POST \ --url http://localhost:8080/ords/hr/_/db-api/19.1.0/database/datapump/export \ --header 'authorization: Basic aHI6b3JhY2xl' \ --header 'content-type: application/json' \ --data '{ "datapump_dir": "DATA_PUMP_DIR", "filter": "HOCKEY_STATS, UNTAPPD", "job_mode": "TABLE", "threads": 2 }' An HTTP Request Body Header Method
14. An HTTP Response Header Body
15. • ords – ords.war, Java servlet • hr – schema (alias!), service handler code runs as this user • odtug – module • media/:id – template • Methods supported • GET, PUT, POST, DELETE Unravelling an ORDS Request URI
16. Install & Configure ORDS REST Enable a SCHEMA Publish a RESTful Service or REST Enable a schema object 1 2 3 Getting Started
17. • Endpoints defined per schema • Services executed as that user REST Enable Schema
18. • Automatic REST (tables, views, stored procs) • RESTful Services Schema Based Services
19. Oracle SQL Developer • Full ORDS Integration • Develop RESTful Services • REST Enable Objects • Manage ORDS Roles and Privileges IDE
20. CLI & PL/SQL APIs
21. Application Express REST Development
22. AUTO REST Advantages • CRUD APIs, no code • Single call to create • Maintained by ORCL • Lots of features • Optimized RESTful Service Advantages • You’re in charge • Inputs, outputs, error handling, response codes, formatting • Full access to SQL/PLSQL • Easily exported, source controlled • Transparent Choose your own adventure!
23. AUTO REST Features
24. • Auto REST Table –Full CRUD API, Data Loading, Metadata (DESC) • Auto REST View –Read interface only (GET) • Auto PL/SQL (RPC) –POST to execute stored PL/SQL –We accept {json} in, map to input params, grab output and {json} out Automatic – ORDS owns the code
25. • GET • PUT • POST • DELETE AutoREST Table /ords/hr/employees/ /ords/hr/employees/:id
27. • No code • SELECT * … • Query Predicates with ?q={…} • Paged results • Links to items GET on a REST Enabled TABLE
28. • Schema level Metadata • Table Metadata • Get ( Select ) • Query ( Filtering/Order/ASOF ) • Insert • Update • Delete • Load CSV
29. • All rows / • One row /:id – PK Value –No PK, default to ROWID –Multi-column PK /x,y,z • Some rows /?q={json} Query the TABLE
30. METHOD : PUT /:PK REQUEST BODY : JSON RESPONSE: 200 OK • Location (Header) • JSON (Body) Table Columns in the POST body {“column_name” : column_value } Update a ROW
31. • We auto-magically handle PL/SQL too • RPC -> POST via HTTPS /ords/hr/procedureA • Responses & Results (OUTs/RETURNs/REFCURSORs), in {JSON} Existing PL/SQL APIs?
32. ORDS takes parameters as JSON, executes PL/SQL, grabs output, sends back down as JSON OUT INTEGER & SYS_REFCURSOR { "total_payroll": 631230, "peeps_numbers": [ { "id": 81, "name": "Dummy4", "salary": 0, "hire_date": "2017-06-20T13:29:00Z" }, { "id": 65, "name": "Bart", "salary": 0, "hire_date": "2017-06-20T13:29:00Z" }, { "id": 79, … } RPC over HTTPS (POST)
33. PL/SQL TABLE API RETURN REFCURSOR
34. RESTful Services
35. 1. Define the URL Template /collection/:item 2. Define the Handler GET 3. Define the DB Work tied to said Request SELECT * FROM 4. Define Parameters for Request/Response Headers read request data, write response output 5. Define the Response format {json} RESTful Service Development Workflow
36. A RESTful Service in ORDS
37. A RESTful Service in ORDS
38. A RESTful Service in ORDS
39. ORDS Request
40. ORDS Response
41. • A collection of RESTful Services • Secured as a group with a privilege • Published or hidden as a group • Default response pagination size Modules (“packages”)
42. /ords/hr/test2/media /ords/hr/test2/media/ /ords/hr/test2/media/:id URI Templates Tip: Model your templates, first!
43. Handlers – inputs, code, & response
44. • Collection Query –Multiple records/paging • Collection Query Item –Single record only/no paging Source Types - Shapes Responses
45. • Feed –Multiple records/paging –First column auto-generates $link • PL/SQL –Executes PL/SQL Block • Media Resource –First Column sets Mime Type –Second Column sets raw content (no json-ification) Feed Source Type Source Types – Determines Responses
46. RESTful Service Examples
47. POST: Upload a file to a TABLE.BLOB
48. The Response
49. RESTful Service Handler Code
50. select title ,content_type ,id "$uri" from gallery order by title GET: Print a list of Rows as LINKs
51. • POST request body • :body (BLOB) or :body_text (CLOB) • Who is accessing the end point • Authenticated user is mapped to :current_user • HTTP Response Status :status_code & Redirects :forward_location • :content_type, :fetch_size, :fetch_offset, :row_count, :page_offset Helpers for your Code, Automatic :binds
52. RESTful Service Source
53. OpenAPI/Swagger
54. Source Control Support • Source Control your RESTful Services via scripts • Liquibase SQLcl makes this easy! Upgrades, rollbacks, dependencies, history tracking and more.
55. Oracle REST Data Services 5 5 2011 2015 APEX Listener • APEX based RESTful Services ORDS v3.0 • APEX no longer required • SODA • AUTO REST 2012 APEX Listener v2 • ORDS based REST Services • Standalone Jetty 2017 ORDS v17 • REST enabled SQL • Type 3 JDBC Driver • Open API (Swagger) 2018 ORDS v18 • PL/SQL Gateway enhancements 2019 ORDS v19 • DB API • SQL Developer Web 2020 ORDS 20.2 • Early Summer ETA • Expanded DB-API • SQL Developer Web feature enhancements
56. • SlideShare • Blogs • GitHub • Articles UKOUG Scene Why REST, and What’s in it or Me? Oracle Mag AUTO REST & REST Enabled SQL • And don’t forget Oracle-Base! ORDS on Other Resources