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

Slide 1

Slide 1 text

RESTful Web Services for Oracle, POUG Edition ORDS Product Manager https://www.thatjeffsmith.com @thatjeffsmith Jeff Smith 🍻

Slide 2

Slide 2 text

• 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

Slide 3

Slide 3 text

• 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

Slide 4

Slide 4 text

Today’s Agenda Jeff annoys people on twitter @thatjeffsmith • Introduction, Terms, & Definitions • ORDS Architecture • Building RESTful Web Services

Slide 5

Slide 5 text

Oracle REST Data Services Included for free with your Oracle Database!!! oracle.com/rest

Slide 6

Slide 6 text

• 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

Slide 7

Slide 7 text

• 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 🍻

Slide 8

Slide 8 text

• Jay-Sun • Not just for js! • Skinnier than XML • Flexible • Link-friendly Terms: {JSON} Jeff annoys people on twitter @thatjeffsmith

Slide 9

Slide 9 text

{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 …

Slide 10

Slide 10 text

ORDS Features and Architecture Jeff annoys people on twitter @thatjeffsmith • How are RESTful Web Services defined • How is ORDS deployed

Slide 11

Slide 11 text

• Automatic • Your Code • SQL on the fly • Management APIs • SQLDev Web Delivery Options Jeff annoys people on twitter @thatjeffsmith

Slide 12

Slide 12 text

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

Slide 13

Slide 13 text

• 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

Slide 14

Slide 14 text

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

Slide 15

Slide 15 text

• JDBC Conn Pools • 1 Pool Per Database • ORDS_PUBLIC_USER • Proxy Connects Enabled DB Users ORDS & Database Communication Jeff annoys people on twitter @thatjeffsmith

Slide 16

Slide 16 text

• 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

Slide 17

Slide 17 text

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

Slide 18

Slide 18 text

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

Slide 19

Slide 19 text

• Services defined in schema • Services executed as user Step 2. REST Enable Schema Jeff annoys people on twitter @thatjeffsmith Services constrained by Database User:

Slide 20

Slide 20 text

• AUTO REST • RESTful Services Step 3. Define your Service Jeff annoys people on twitter @thatjeffsmith

Slide 21

Slide 21 text

What RESTful Services could look like… Jeff annoys people on twitter @thatjeffsmith

Slide 22

Slide 22 text

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

Slide 23

Slide 23 text

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

Slide 24

Slide 24 text

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!

Slide 25

Slide 25 text

Example: More Query String Parameters ?q={"beer_type":{"$like":"%25Tripel%25"}} Jeff annoys people on twitter @thatjeffsmith

Slide 26

Slide 26 text

Filtering, Sorting and More ORDS Handles the SQL for you Jeff annoys people on twitter @thatjeffsmith

Slide 27

Slide 27 text

Example: Generating Links “$alias” => LINK Jeff annoys people on twitter @thatjeffsmith

Slide 28

Slide 28 text

Example: Working w/JSON Data in the DB Jeff annoys people on twitter @thatjeffsmith BAD 

Slide 29

Slide 29 text

Escaping JSON ☺ Jeff annoys people on twitter @thatjeffsmith

Slide 30

Slide 30 text

Where/how to code your Services Jeff annoys people on twitter @thatjeffsmith

Slide 31

Slide 31 text

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

Slide 32

Slide 32 text

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

Slide 33

Slide 33 text

CLI & PL/SQL APIs Jeff annoys people on twitter @thatjeffsmith SQLcl Anywhere you can run PL/SQL

Slide 34

Slide 34 text

Or Application Express (APEX) Jeff annoys people on twitter @thatjeffsmith APEX is NOT required

Slide 35

Slide 35 text

Time to start building! Jeff annoys people on twitter @thatjeffsmith

Slide 36

Slide 36 text

Home Brewing or Store Bought? Your Code Someone Else’s Jeff annoys people on twitter @thatjeffsmith

Slide 37

Slide 37 text

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

Slide 38

Slide 38 text

AUTOREST Database Objects TABLES VIEWS PROCEDURES FUNCTIONS PACKAGES Jeff annoys people on twitter @thatjeffsmith

Slide 39

Slide 39 text

• 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

Slide 40

Slide 40 text

• GET • PUT • POST • DELETE • DESC • DOCS AutoREST Table /ords/hr/beers/ /ords/hr/beers/:PK Jeff annoys people on twitter @thatjeffsmith

Slide 41

Slide 41 text

Jeff annoys people on twitter @thatjeffsmith REST Enabling a TABLE

Slide 42

Slide 42 text

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

Slide 43

Slide 43 text

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

Slide 44

Slide 44 text

• 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

Slide 45

Slide 45 text

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

Slide 46

Slide 46 text

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

Slide 47

Slide 47 text

• POST /ords/hr/procA • JSON Responses & Results (OUTs/RETURNs/REFCURSORs) Accessing your PL/SQL Code, Automatically Jeff annoys people on twitter @thatjeffsmith

Slide 48

Slide 48 text

Remote Procedure Call over HTTP (POST) Jeff annoys people on twitter @thatjeffsmith

Slide 49

Slide 49 text

Remote Procedure Call over HTTP (POST) Request p(arg =>input) Procedure output formatted {json} – links and all Jeff annoys people on twitter @thatjeffsmith

Slide 50

Slide 50 text

RESTful Services, write your own code Jeff annoys people on twitter @thatjeffsmith • Modules • Templates • Handlers • Source Types

Slide 51

Slide 51 text

• Base URI • Default pagination size • Can be protected by common privileges Modules Jeff annoys people on twitter @thatjeffsmith

Slide 52

Slide 52 text

/ords/hr/untappd/beers/ /ords/hr/untappd/beers/:id URI Templates Jeff annoys people on twitter @thatjeffsmith

Slide 53

Slide 53 text

Handlers: Mapping Verbs to the SQL/PLSQL Jeff annoys people on twitter @thatjeffsmith

Slide 54

Slide 54 text

• 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

Slide 55

Slide 55 text

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

Slide 56

Slide 56 text

INSERT a record w/a BLOB Jeff annoys people on twitter @thatjeffsmith

Slide 57

Slide 57 text

• 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

Slide 58

Slide 58 text

• 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

Slide 59

Slide 59 text

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

Slide 60

Slide 60 text

Print a list of Rows w/LINKs to pictures Jeff annoys people on twitter @thatjeffsmith

Slide 61

Slide 61 text

• 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

Slide 62

Slide 62 text

Time to wind things down, Last Call! Jeff annoys people on twitter @thatjeffsmith • Source code, CI/CD • Documentation • Authorization

Slide 63

Slide 63 text

Backup/Source Control your REST Code • GUI • SQL • CLI/Liquibase Generated via: Jeff annoys people on twitter @thatjeffsmith

Slide 64

Slide 64 text

Document your APIs with OpenAPI • GUI • REST Generated via: Jeff annoys people on twitter @thatjeffsmith

Slide 65

Slide 65 text

• 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

Slide 66

Slide 66 text

• 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