Pints with Oracle User Group (POUG) and Oracle REST Data Services

An overview of Oracle REST Data Services with all of the examples being BEER themed, thanks to our Polish friends and their awesome event and user group.

Transcript

1. RESTful Web Services for Oracle, POUG Edition ORDS Product Manager https://www.thatjeffsmith.com @thatjeffsmith Jeff Smith 🍻
2. • Presented originally in 2019 for POUG in Wroclav, Poland • Pints with Oracle User Group • Combines two of my favorite things, ORDS & Beer (beeords!) • Please drink responsibly • I have non-alcoholic slides/content on my blog About these slides… Jeff annoys people on twitter @thatjeffsmith
3. • product manager/storyteller • I help/bother people online • Working with Oracle Database since 1999 • Helping build database tools since 2001 • I ‘manage’ these products: whoami Jeff annoys people on twitter @thatjeffsmith
4. Today’s Agenda Jeff annoys people on twitter @thatjeffsmith • Introduction, Terms, & Definitions • ORDS Architecture • Building RESTful Web Services
5. Oracle REST Data Services Included for free with your Oracle Database!!! oracle.com/rest
6. • REpresentational State Transfer • Architectural Style for networked applications • Communicates via HTTP, but.. • Using HTTP doesn’t magically make your APIs RESTful Terms: REST Jeff annoys people on twitter @thatjeffsmith
7. • Model things, not actions • Use VERBS to interact, GET, PUT, POST, DELETE • Stateless, so be sure to use LINKs to guide your users POST beers/ => Adds beer, responds w/link GET beers/:id => Jeff annoys people on twitter @thatjeffsmith RESTful Architecture 🍻
8. • Jay-Sun • Not just for js! • Skinnier than XML • Flexible • Link-friendly Terms: {JSON} Jeff annoys people on twitter @thatjeffsmith
9. {JSON} Bonus Tip! Our tools make it easy to get your database data in JSON format, pretty or RAW Jeff annoys people on twitter @thatjeffsmith …
10. ORDS Features and Architecture Jeff annoys people on twitter @thatjeffsmith • How are RESTful Web Services defined • How is ORDS deployed
11. • Automatic • Your Code • SQL on the fly • Management APIs • SQLDev Web Delivery Options Jeff annoys people on twitter @thatjeffsmith
12. GET https://host/ords/hr/beers/1 SELECT * FROM UNTAPPD WHERE ID = 1 HTTP/1.1 200 OK { “id": 100, “name": “Bud Lite“, “score": “0", “comments": “yuck” } {json} URI SQL & PLSQL Marshalls to Database HTTP Request HTTP Response Transforms to JSON SQL Result Set / Standard OUT Oracle REST Data Services ORDS: HTTP to SQL and back Jeff annoys people on twitter @thatjeffsmith
13. • Webserver layout • Java Servlet • Tomcat or WLS ORDS Architecture Option #1 Tomcat, WLS ORDS Apache Static Files -HTML, CSS, JS /ords/… /db1/hr/emp/ JSON Binary HTML … HTTP(s) Results JDBC pool1 pool2 pool3 Request JDBC JDBC Jeff annoys people on twitter @thatjeffsmith
14. java –jar ords.war standalone • Supported for production! • ORDS is your webserver & web listener for your Oracle Databases ORDS Static Files -HTML , CSS, JS /db1/hr/emp/ JSON Binary HTML … HTTP(s) Response JDBC pool1 pool2 pool3 Request JDBC JDBC Jeff annoys people on twitter @thatjeffsmith ORDS Architecture Option #2 : Standalone
15. • JDBC Conn Pools • 1 Pool Per Database • ORDS_PUBLIC_USER • Proxy Connects Enabled DB Users ORDS & Database Communication Jeff annoys people on twitter @thatjeffsmith
16. • PUT – HTTP Verb • HTTPS – secure, encrypted version of HTTP (recommended!) • Server:8080 – network location where ORDS is running • ords – ords.war, Java servlet • hr – schema alias, where service is defined • examples – module, a collection of services • beers/:id – template, defines the resource Unwinding an ORDS Request Jeff annoys people on twitter @thatjeffsmith PUT https://server:8080/ords/hr/examples/beers/:id
17. Install & Start ORDS REST Enable Application SCHEMA Publish RESTful Service or REST Enable object 1 2 3 Getting Started Steps Jeff annoys people on twitter @thatjeffsmith
18. Jeff annoys people on twitter @thatjeffsmith Step 1, Install ORDS Video: I talk about the process, DO an install, use SQLDev Web to build a RESTful Web Service
19. • Services defined in schema • Services executed as user Step 2. REST Enable Schema Jeff annoys people on twitter @thatjeffsmith Services constrained by Database User:
20. • AUTO REST • RESTful Services Step 3. Define your Service Jeff annoys people on twitter @thatjeffsmith
21. What RESTful Services could look like… Jeff annoys people on twitter @thatjeffsmith
22. Twoje zdrowie! Or Cheers! • UNTAPPD: my personal beer diary • YES, these are MY pictures and MY comments • Examples will show building REST APIs for my diary 🍻 Jeff annoys people on twitter @thatjeffsmith
23. Example – Beers w/pictures SELECT beer_name, brewery_name, created_at, photo_url "$pics", comments FROM untappd WHERE photo_url IS NOT NULL ORDER BY checkin_id DESC Jeff annoys people on twitter @thatjeffsmith GET all beers, with links to pictures
24. Example – Using Query String Parameters ?q={"beer_type":{"$like":"Stout%25"}} Jeff annoys people on twitter @thatjeffsmith SAME SQL, but… ORDS injects the WHERE clause for us. No additional coding required!
25. Example: More Query String Parameters ?q={"beer_type":{"$like":"%25Tripel%25"}} Jeff annoys people on twitter @thatjeffsmith
26. Filtering, Sorting and More ORDS Handles the SQL for you Jeff annoys people on twitter @thatjeffsmith
27. Example: Generating Links “$alias” => LINK Jeff annoys people on twitter @thatjeffsmith
28. Example: Working w/JSON Data in the DB Jeff annoys people on twitter @thatjeffsmith BAD 
29. Escaping JSON ☺ Jeff annoys people on twitter @thatjeffsmith
30. Where/how to code your Services Jeff annoys people on twitter @thatjeffsmith
31. Oracle SQL Developer • Full ORDS Integration • Develop RESTful Services • REST Enable Objects • Manage ORDS Roles and Privileges • Free! OS X, Windows, or Linux IDE Jeff annoys people on twitter @thatjeffsmith
32. Database Actions/SQLDev Web • ORDS Single Page App • Develop RESTful Services • REST Enable Objects • Manage ORDS Roles and Privileges, OAuth2 Clients • Free! Any major browser. Browser Jeff annoys people on twitter @thatjeffsmith
33. CLI & PL/SQL APIs Jeff annoys people on twitter @thatjeffsmith SQLcl Anywhere you can run PL/SQL
34. Or Application Express (APEX) Jeff annoys people on twitter @thatjeffsmith APEX is NOT required
35. Time to start building! Jeff annoys people on twitter @thatjeffsmith
36. Home Brewing or Store Bought? Your Code Someone Else’s Jeff annoys people on twitter @thatjeffsmith
37. AUTOREST • CRUD APIs, no SQL to write • Single ORDS call to create • Maintained by ORCL • Feature Packed • Optimized RESTful Services • You control: • Inputs, outputs, error handling, response codes, formatting • Your SQL/PLSQL code • Easily exported, source controlled • Transparent Choose your own adventure! Jeff annoys people on twitter @thatjeffsmith
38. AUTOREST Database Objects TABLES VIEWS PROCEDURES FUNCTIONS PACKAGES Jeff annoys people on twitter @thatjeffsmith
39. • Auto REST Table – Full CRUD API, Data Loading, Metadata (DESC) • Auto REST View – Read interface (GET), Write (PUT+POST+DELETE w/PK on VIEW) • 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 Jeff annoys people on twitter @thatjeffsmith
40. • GET • PUT • POST • DELETE • DESC • DOCS AutoREST Table /ords/hr/beers/ /ords/hr/beers/:PK Jeff annoys people on twitter @thatjeffsmith
41. Jeff annoys people on twitter @thatjeffsmith REST Enabling a TABLE
42. REST Enabled TABLE Swagger API Doc • Table Metadata • SELECT Query Filtering/Order/ASOF SCN or Timestamp • INSERT • UPDATE • DELETE • BATCHLOAD Jeff annoys people on twitter @thatjeffsmith
43. Jeff annoys people on twitter @thatjeffsmith Two Quick AUTOREST TABLE Examples 1. GET one or more rows with a query 2. PUT updates to an existing row
44. • All rows / • One row /:id – PK Value – No PK, default to ROWID – Multi-column PK /x,y,z • Some rows /?q={json} SELECT (GET) Jeff annoys people on twitter @thatjeffsmith
45. METHOD : PUT /:PK REQUEST BODY : JSON RESPONSE: 200 OK • Location (Header) • JSON (Body) UPDATE (PUT) : adding comments REQUEST BODY Jeff annoys people on twitter @thatjeffsmith RESPONSE
46. Jeff annoys people on twitter @thatjeffsmith We also support your PL/SQL Code! 1. Instant access to all of your application logic 2. ORDS ‘wrappers’ your existing PL/SQL with HTTPS
47. • POST /ords/hr/procA • JSON Responses & Results (OUTs/RETURNs/REFCURSORs) Accessing your PL/SQL Code, Automatically Jeff annoys people on twitter @thatjeffsmith
48. Remote Procedure Call over HTTP (POST) Jeff annoys people on twitter @thatjeffsmith
49. Remote Procedure Call over HTTP (POST) Request p(arg =>input) Procedure output formatted {json} – links and all Jeff annoys people on twitter @thatjeffsmith
50. RESTful Services, write your own code Jeff annoys people on twitter @thatjeffsmith • Modules • Templates • Handlers • Source Types
51. • Base URI • Default pagination size • Can be protected by common privileges Modules Jeff annoys people on twitter @thatjeffsmith
52. /ords/hr/untappd/beers/ /ords/hr/untappd/beers/:id URI Templates Jeff annoys people on twitter @thatjeffsmith
53. Handlers: Mapping Verbs to the SQL/PLSQL Jeff annoys people on twitter @thatjeffsmith
54. • Collection Query – Multiple records/paging • Collection Query Item – Single record only/no paging • Media – Streams the output directly • PL/SQL – Print HTML (HTP.P) or Code the Response yourself (headers and body) Source Types - Shapes Responses Jeff annoys people on twitter @thatjeffsmith
55. RESTful Service Example #1 • a TABLE • a RESTful Webservice… • …that INSERTs a record Implementation • Template => media/ • Handler => POST • Source => PL/SQL Jeff annoys people on twitter @thatjeffsmith
56. INSERT a record w/a BLOB Jeff annoys people on twitter @thatjeffsmith
57. • ORDS defines request body & headers w/ :binds • ORDS defines response body & headers w/ :binds ORDS makes HTTP & SQL, easy! INSERT INTO table (blob) VALUES (:body); :status := 201; :location := ‘../’ || id; Jeff annoys people on twitter @thatjeffsmith
58. • We got the BLOB from the HTTP request via :body • We got the other columns from HTTP request headers • We INSERT the record • We set HTTP LOCATION and STATUS …in ~3 lines of PL/SQL!!! What just happened? Jeff annoys people on twitter @thatjeffsmith
59. RESTful Service Example #2 • a TABLE • a RESTful Webservice… • …that gives list of links Implementation • Template => beers/:style/pics • Handler => GET • Source => SQL Jeff annoys people on twitter @thatjeffsmith
60. Print a list of Rows w/LINKs to pictures Jeff annoys people on twitter @thatjeffsmith
61. • SQL SELECT to get the data • Predicate value pulled from URI via :type bind • We told ORDS to generate a link with an “$alias” What just happened? Jeff annoys people on twitter @thatjeffsmith
62. Time to wind things down, Last Call! Jeff annoys people on twitter @thatjeffsmith • Source code, CI/CD • Documentation • Authorization
63. Backup/Source Control your REST Code • GUI • SQL • CLI/Liquibase Generated via: Jeff annoys people on twitter @thatjeffsmith
64. Document your APIs with OpenAPI • GUI • REST Generated via: Jeff annoys people on twitter @thatjeffsmith
65. • See MY post on OAUTH2 w/ORDS • See Todd’s post on OAUTH2 w/ORDS & Micronaut Jeff annoys people on twitter @thatjeffsmith SECURE Your Services
66. • Slides • Blogs • Articles UKOUG Scene Why REST, and What’s in it or Me? Oracle Magazine AUTO REST & REST Enabled SQL Other Resources Jeff annoys people on twitter @thatjeffsmith