Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Masterclass: REST APIs and the Oracle Converged Database for DBAs

Masterclass: REST APIs and the Oracle Converged Database for DBAs

What YOU WILL LEARN in this session:
+ What does it mean for a service or API to be 'RESTful?'
+ HTTPS and JSON basics, and how they come together to make REST APIs possible
+ Best Practices for deploying Oracle REST Data Services (ORDS) for Oracle Database
+ How to build a SQL and PL/SQL based REST API
+ How to use AUTO features for existing tables, views, and PL/SQL
+ How to keep your APIs performant and secure
+ How to make your APIs developer friendly

More details at thatjeffsmith.com

thatjeffsmith

June 16, 2021
Tweet

More Decks by thatjeffsmith

Other Decks in Programming

Transcript

  1. REST APIs and the Oracle Converged Database Distinguished Product Manager

    [email protected] Tweets: @thatjeffsmith Blogs: https://www.thatjeffsmith.com Jeff Smith
  2. • 75 minutes lecture • Hands-On Lab introduction • Questions?

    Zoom Q&A • Recorded? YES • SLIDES? YES Format
  3. • What makes an API, RESTful? • Why should we

    care? • How it works/Architecture • examples • Oracle Converged Database • Database Management APIs Agenda
  4. • Representational Stateless Transfer • Architectural Style for networked applications

    • Communicated via HTTPS, but • HTTPS <> REST REST
  5. None
  6. • Predictable, familiar for developers • Model nouns not verbs

    • Proper Responses • LINKS RESTful APIs RESTful Ugh! POST things/ GET delete_things/ GET things/:id POST things/new_thing RESTful Ugh! Request: POST things/ Request: POST things/ Response: 201 Created Response: 200 OK Location /things/123
  7. • Everything is HTTPS/JSON • Developers will go around you

    • Database needs CI/CD, automation, mon… • Microservices! Cool, but why do I care?
  8. HTTPS > SQL > HTTPS GET https://host/ords/hr/beers/1 SELECT * FROM

    EMPS WHERE ID = 1 HTTP/1.1 200 OK { "id": 1, "name": "Jeff", "job": "PM", "salary": 100 } {json} URI SQL & PLSQL Marshalls to Database HTTP Request HTTP Response Transforms to JSON SQL Result Set / Standard OUT Oracle REST Data Services
  9. • Webserver layout • Java Servlet • Tomcat/WLS or •

    Standalone Java app 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 ORDS Architecture
  10. • Each database gets a connection pool • CDBs, PDBs,

    or single instances • Each database gets a mapping pattern ords/ ords/db2/… ords/db3/… TIP 1: 3 ORDS front-ended with a load balancer TIP 2: Data Guard/Scan Listener for your DB, ORDS will immediately pick up DatabaseS and HA Tips
  11. • JDBC Conn Pools • Default Size: 10 • 1st

    pool => ords/ • ORDS_PUBLIC_USER .. Tip 1 : Customers MUST tune connection pools Tip 2: Bigger Pools <> FASTER! Whitepaper: Real World Perf Sizing Guide Video: Office Hours Session Connection Pools
  12. • CLI for configuration changes or.. • Edit pool XML

    and/or Standalone properties files Configuration
  13. Watch Later…

  14. ORDS & Your Database • ORDS_PUBLIC_USER • ORDS_METADATA • ORDS.ENABLE_SCHEMA()

    • APIs published to SCHEMA • APIs exec as USER Modeling ORDS_METADATA with Database Actions (ORDS)
  15. Where APIs come from 1. REST Enable Schema 2. REST

    Enable Object OR 2. Publish a REST Module
  16. REST Enabling BEGIN ORDS.ENABLE_SCHEMA( p_enabled => TRUE, p_schema => 'HR',

    p_url_mapping_type => 'BASE_PATH', p_url_mapping_pattern => 'peeps', p_auto_rest_auth => TRUE); commit; END;
  17. Step 1…you’re not done

  18. Built-in Dev Tools

  19. None
  20. AUTOREST •CRUD APIs, no code •Maintained by ORCL •Feature rich

    •Optimized RESTful Service •Your code - •Inputs, outputs, error handling, response codes, formatting •Full access to SQL/PLSQL •Easily exported, source controlled •Transparent No or Low Code
  21. AUTOREST: TABLE

  22. CRUD APIs, for free

  23. POST => INSERT

  24. PUT => UPDATE

  25. DELETE => DELETE

  26. POST…BatchLoad

  27. Oracle Confidential – Internal/Restricted/Highly Restricted Batchloading 10M rows

  28. GET /?q=…

  29. None
  30. Like coding? Modules!

  31. RESTful Service •Your code - •Inputs, outputs, error handling, response

    codes, formatting •Full access to SQL/PLSQL •Easily exported, source controlled •Transparent Techniques/tricks
  32. REST Module: 2+Tables

  33. SQL: JOIN

  34. Binds in the URI

  35. SQL: CURSOR

  36. SQL & Paging

  37. ALT: AUTOREST a VIEW

  38. REST is LINK DRIVEN FAIL

  39. GENERATING LINKS

  40. Building your Templates Avoid dead-ends

  41. POST vs GET

  42. POST/PUT Tips • Request Mime Type: Application/JSON • :x –

    looks for {"x" :…} • Parameters • :bind is the VAR in the blog, name is the HEADER label • Use to work with REQUEST or RESPONSE elements of your API
  43. PL/SQL 1. ORDS Enable PL/SQL (package, proc, func) 2. OR

    Publish a REST Module AUTO • RPC for PL/SQL program • Input params in req body • OUT/RETURNs transformed and returned as JSON response RESTful Service • Your code - • Inputs, outputs, error handling, response codes, formatting • Print HTML, return JSON, download files…
  44. PL/SQL & REFCURSORs 1. SELECT – func with OUT sys_refcursor

    2. ANON Block 3. Call SP, send OUT to a RESULTSET OUT PARAM 4. AUTO
  45. AUTO PL/SQL REFCUR

  46. ANON Block REFCUR

  47. PL/SQL Download File CREATE OR REPLACE PROCEDURE download_file ( media_id

    NUMBER ) AS vMIMETYPE VARCHAR2(256); vLENGTH NUMBER; vFILENAME VARCHAR2(2000); vBLOB BLOB; BEGIN SELECT file_name, content_type, content INTO vFILENAME, vMIMETYPE, VBLOB FROM media WHERE id = media_id; vLENGTH := DBMS_LOB.GETLENGTH(vBLOB); owa_util.mime_header(NVL(vMIMETYPE, 'application/octet'), FALSE); htp.p('Content-length: ' || vLENGTH); htp.p('Content-Disposition: attachment; filename=' || SUBSTR(vFILENAME, INSTR(vFILENAME, '/') + 1) || ‘’); owa_util.http_header_close; wpg_docload.download_file(vBLOB); END download_file; /
  48. Uploading Files - BLOBS

  49. None
  50. Converged Database • One DB engine – ALL data &

    workloads • Support for Microservices, Events, REST, SaaS… • Accessed/Managed with SQL * PL/SQL Read more on Converged Databases with Maria Colgan
  51. GET HTTPS://.../ords/hr/soda/latest/thatjeffsmith/123456... JSON Documents POST HTTPS://.../ords/hr/soda/latest/thatjeffsmith/

  52. Spatial, GeoJSON

  53. XML

  54. Analytics & ML

  55. None
  56. • Requests must be authenticated & authorized! • APIs are

    protected via required roles • Authentication can be managed by Web Server OR Use ORDS BASIC Auth Database Auth Built-in OAuth2 Workflow Secure APIS
  57. Secure APIS

  58. Security OAuth2

  59. DBA. Me. Want. Stuff! • Provide access, easily to your

    DBs • Automation • Monitoring
  60. DB-API: 500+ Endpoints Performance Monitoring PDB Lifecyle General Data Dictionary

    Reports • tables/, tables/{table} • indexes/… Multitenant Management • Create • Clone • Change State • Drop • Reports Problematic Activity • Sessions • Locks • Waits • Alert Log What isn’t running well? • ASH • AWR • RTSM • Top SQL Database Operations • DBCA • Data Pump
  61. DB-API: Turn it on <entry key="database.api.enabled">true</entry>

  62. Oracle Docs OpenAPI.JSON Endpoint DB-API: Docs

  63. Service Catalog

  64. None
  65. Data Pump via HTTPS { "datapump_dir":"DATA_PUMP_DIR", "filter" :"HOCKEY_STATS", "job_mode" :"TABLE",

    "threads" : 2 } POST BODY Response
  66. Web Tooling for Users

  67. Homework

  68. Extra Credit!

  69. Resources • SlideShare/SpeakerDeck • Blogs (thatjeffsmith.com) • 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!