Skip to content

README.md

Latest commit

 

History

History
230 lines (210 loc) · 15.7 KB

README.md

File metadata and controls

230 lines (210 loc) · 15.7 KB
Raw

KMLeon Toolkit (APEX 26.1 / APEXLang)

An APEX 26.1 application that doubles as development toolkit for KMLeon: build jobs from GeoJSON or a SELECT, watch them run sync / async, preview geometries on a map, inspect logs, manage cleanup settings, download results, and integrate KMLeon into your own PL/SQL via the Query helper. Lives in the repo under apex_toolkit/. Built on top of a real frontend-exported APEXLang app so the format/version matches APEX 26.1 exactly.

Pages

  • 1 Jobs (dashboard) — one page, top to bottom:
    • a Cards KPI strip (total / completed / failed / pending+running) top-left and a fixed Map region (geojson layer, all geometry types, auto-zoom) top-right at the same height (region columnSpan 8 / 4, startNewRow: false);
    • an IR over KML_JOBS (status badge) full width below — clicking a row's link sets P1_JOB_ID and reloads focused on that job;
    • an Assets IR below, filtered to the selected job (else latest COMPLETED). The KPI strip also shows the latest created / completed / failed / cleanup timestamps (from KML_CONFIG metrics). Buttons: New job + Process PENDING are always shown; Re-run, Cancel and Download appear only when a job is selected (serverSideCondition → itemIsNotNull P1_JOB_ID). Actions run via Dynamic Actions → PCK_KML_*.
  • 3 New job — modern form (floating labels, format dropdown) + Create from GeoJSON & run / Create from SELECT & run.
  • 11 Editor — a map-based geometry + style workbench. Draw geometries freehand (point / line / polygon via a custom MapLibre layer using the map's getMapObject()), style them with live preview (line/fill color, width, opacity), attach extended data (a JSON object rendered as the placemark's <ExtendedData>), then Save asset to a job (pick a DRAFT job or any job that still has assets — the editor creates a draft on first save). The job's existing assets are listed in an Interactive Grid; click a row and its geometry loads back onto the map and its style into the form — edit and Save asset to update it in place (via PCK_KML_JOB_ASSETS_DML.upd). New / clear starts a fresh feature. Build code emits four ready-to-paste outputs: a PCK_KML_JOB_API.add_asset(...) call, the raw KML <Style> XML, a curl to POST this asset via REST (/jobs/{id}/features), and a full curl sequence to re-create the whole selected job over REST (create → add all features → run → download). Build snippet & KML copies a ready-made PCK_KML_JOB_API.add_asset(...) call and the raw KML <Style> fragment for hardcoding into your own app. Run job + Download render and fetch the KML/KMZ. (Styling/outputs use PCK_KMLEON_TOOLS.style_outputs; stored colors load back into the pickers via kml_to_rgb/kml_alpha. Note: Run job finalises the DRAFT and, per the global DELETE_ASSETS_AFTER_SUCCESS setting, may clear its assets afterwards — turn that off on the Settings page to keep editing.)
  • 10 Query helper — paste a candidate QUERY SELECT (+ optional binds JSON), click Analyze: the page parses it with DBMS_SQL, describes every column and matches each alias to its KMLeon role (geometry/name/folder/style/… or ExtendedData), then emits two ready-to-paste PL/SQL snippets — a one-line l_query CLOB := q'<delim>…<delim>'; and a full declare ... PCK_KML_JOB_API.create_job_from_query ... run_async block (safe q-quoting that picks a delimiter the query does not contain). A second button Create test job and run async runs the same query end-to-end so you can verify it produces a valid KML/KMZ before integrating.
  • 8 Async playground — build a deliberately slow QUERY job (points × seconds, via the pck_kmleon_tools.row_sleep per-row sleep helper; set seconds = 0 to run as fast as possible) and run it via a Run mode toggle to compare: Async (submit_job(p_async => true), returns instantly, status report auto-refreshes every 2 s PENDING → RUNNING → COMPLETED) vs Sync (run_now, blocks the request until done). A Scheduler jobs running now table (USER_SCHEDULER_JOBS + USER_SCHEDULER_RUNNING_JOBS, auto-refreshing) shows whether two async jobs run in parallel (RUNNING) or one is throttled (SCHEDULED).
  • 5 Logs — IR over KML_LOG.
  • 7 Settings — edit the cleanup job (CLEANUP_* in KML_CONFIG): enabled, interval, retention, statuses. Save and apply schedule writes via PCK_KML_CONFIG_DML + PCK_KML_MAINTENANCE.apply_schedule; Run cleanup now calls PCK_KML_MAINTENANCE.run_cleanup(p_force => true). Plus an IR over all config.
  • 9 Download — streams result_kmz/result_kml as a file (before-header process).
  • 100 Public editor — a public, link-based editor for end users of your other apps. Not in the nav and not behind IST_ADMIN; it is gated by the JOB_ACCESS_VALID authorization scheme, which only grants access when the URL's P100_JOB_ID and P100_ACCESS_KEY match a row in KML_JOBS. As defence in depth, every data query, save, load and download on the page is also bound to the access_key, so access does not depend on authorization-scheme caching. The user draws/styles/edits the features of that one job and downloads the KML/KMZ. Download renders on the fly via PCK_KML_ENGINE.build_kml (no run_job, no asset cleanup), so the job stays a DRAFT and stays editable. Features can be deleted straight from the grid (a per-row button calls the DELETE_ASSET ajax process, which re-validates the access_key and routes through PCK_KML_JOB_ASSETS_DML.del); saving a feature ends the draw mode but keeps it loaded for further edits. See Public editor flow below.
  • 200 Public editor v2 — same contract as page 100 (public, JOB_ACCESS_VALID-gated on P200_JOB_ID/P200_ACCESS_KEY; the auth scheme coalesces P200/P100 so both editors share it), but a fully hand-built UI with no external JS libraries — the only third-party piece is the APEX-native map region. A three-pane app shell (custom HTML/CSS/JS): a feature list with live search, a floating glass draw toolbar over the map with a live coordinate readout and keyboard shortcuts (P/L/G/Z/Enter/Esc), and a styled inspector with collapsible sections and live style preview. All I/O runs through page ajax processes (LIST_ASSETS/LOAD_ASSET/SAVE_ASSET/DELETE_ASSET/DOWNLOAD); save posts one JSON payload parsed server-side with JSON_VALUE, with the access_key re-validated in every process. Light/dark compatible: all chrome colours derive from currentColor (the live Universal Theme text colour) so it tracks the active theme style (e.g. Vita / Vita Dark) without external switches. Page access protection is unrestricted (not just the items): the access_key (via JOB_ACCESS_VALID) is the sole gate, so an external app can deep-link a user straight in without any APEX checksum — just …/ords/r/<ws>/<app>/public-editor-v2?p200_job_id=<id>&p200_access_key=<key> (or the classic f?p=<APP>:200:0::NO::P200_JOB_ID,P200_ACCESS_KEY:<id>,<key>). No REST endpoint is needed to build the link. (Note: apex validate flags SECURITY_BASELINE_REQUIRED_001 for the non-checksum setting — that is intentional for this deliberately public, token-gated page.)
  • 110 My maps (native base) — a public landing page (the app's home page) that lists a user's jobs from a plain native classic report filtered by User id + User tab (both editable; default to &APP_USER. / APP_USER). A declarative Open editor link column targets the classic editor (page 100) with P100_JOB_ID + P100_ACCESS_KEY so APEX builds the URL with the right checksum. All job-creating pages tag new jobs with user_tab => 'APP_USER', user_id => v('APP_USER') so they show up here. This page is kept deliberately 100% native APEX as the baseline variant.
  • 210 My maps v2 — the same landing rebuilt in the v2 design language (custom HTML/CSS/JS, no external libs): a job card grid (status badge, format, feature count, date) fed by an ajax LIST_JOBS process, a styled filter, and — for authenticated users only — a New map dialog (CREATE_JOB) that creates a job and jumps straight into the v2 editor (page 200). Cards open the v2 editor. Same currentColor-based light/dark theming as page 200.

The side navigation is split into a public My maps entry (top) and an Admin group holding every other page; the Admin group and its links are gated by apex_authorization.is_authorized('IST_ADMIN') (via serverSideCondition), so they vanish without the privilege. All toolkit pages except the two public ones (100, 110) and login require the IST_ADMIN authorization scheme (currently return 1=1; — replace with real logic to lock the toolkit down). The public pages also set navigation { warnOnUnsavedChanges: false } so navigating away (Open editor / Download) never prompts.

Actions are wired as Dynamic Actions running server-side PL/SQL (no page branches); the download uses a before-header streaming process. The map layer omits point-only styling so it renders points, lines and polygons from one geojson source.

Public editor flow (external integration)

Let users of your other APEX apps polish a KMLeon export themselves, then download it — without giving them access to the toolkit. Your app creates the job and hands the user a signed-by-token deep link:

declare
  l_job number;
  l_key varchar2(64);
  l_n   number;
begin
  l_job := pck_kml_job_api.create_job(p_document_name => 'Customer map');
  l_n   := pck_kml_job_api.add_features_geojson(l_job, '<your FeatureCollection>'); -- or add_asset
  commit;
  l_key := pck_kml_job_api.get_access_key(l_job);   -- generated on create
  -- redirect the user to the public editor (same APEX instance):
  --   f?p=<TOOLKIT_APP_ID>:100:0::::P100_JOB_ID,P100_ACCESS_KEY:<l_job>,<l_key>
end;
  • KML_JOBS.access_key is generated automatically on insert (64 hex chars, PCK_KML_JOBS_DML.gen_access_key). Read it back with PCK_KML_JOB_API.get_access_key(job_id); the REST POST /jobs also returns it.
  • REST callers don't have to build the link themselves: POST /jobs returns a ready editor_url, and GET /jobs/{id}/editor-link?key=<access_key> re-fetches it any time. The URL is never configured/stored — the relative part comes from APEX itself (PCK_KMLEON_TOOLS.public_editor_url: bootstrap APEX_SESSION + APEX_PAGE.GET_URL, friendly URL with workspace/app/page aliases) and scheme, host and ORDS context root are derived from the incoming request's CGI environment. The key parameter is required and must match — the endpoint only formats what the caller already knows, so it cannot be used to harvest tokens.
  • The two link items are sessionStateProtection: unrestricted, so the link needs no APEX checksum — the unguessable access_key is the access control.
  • The job is not submitted/rendered by your app; the user finishes it in the editor and downloads on demand. The job stays DRAFT throughout, so it can be reopened/edited with the same link any number of times.

Helper package: PCK_KMLEON_TOOLS

Functions the toolkit pages call live in the package ../sql/packages/pck_kmleon_tools.sql (alongside the core KMLeon packages; installed by setup.sql, owned by the KMLeon schema):

Member Purpose
row_sleep(p_seconds) per-row sleep used by the Async playground SELECT
qstring(p_text) wrap a CLOB in a safe q'<delim>...<delim>' literal
qstring_inline(p_text, p_inline_names) like qstring, but split around caller-resolved placeholders so the output reads `q'prefix'
role_of(p_alias) map a SELECT column alias to its KMLeon role
type_name(p_type, p_len) friendly name for a DBMS_SQL column-type code
engine_schema the schema names resolve to here (the engine schema; package is AUTHID DEFINER)
query_helper(p_query, p_binds, …, p_inline_binds, …) parse + describe + produce snippets (Query helper page); p_inline_binds lists caller-resolved placeholders
style_outputs(p_geojson, …style…) build an add_asset(...) snippet + raw KML <Style> XML from a geometry + style choices (Editor page)
rest_outputs(p_job_id, p_geojson, …style…, p_base_url) build curl snippets: POST one asset via REST, and a full sequence to re-create the whole job over the ORDS API (Editor page)
kml_to_rgb(p_kml) / kml_alpha(p_kml) convert a stored KML aabbggrr color back to #RRGGBB / its 0–255 alpha (Editor loads asset colors into the pickers)

Cross-schema notes: the Query helper validates the SELECT as the engine schema (PCK_KMLEON_TOOLS is AUTHID DEFINER), so a successful parse means the engine can run it. If a table lives in another schema, qualify it (OTHER_SCHEMA.TABLE), create a synonym in the engine schema, or use a DB link — the parser will tell you when it cannot resolve a name.

Two kinds of binds in a QUERY source — auto-classified:

  • Engine binds — whatever names you declare in source_binds JSON (e.g. {"region":"DE"}); the engine binds them at job time.
  • Inline binds (caller-resolved, default) — every other :NAME the helper finds in the query (e.g. :P200_ID for an APEX page item), resolved by the calling session at submit time.

The Inline binds field on the page is just an explicit override; leave it empty for auto-detection. The Status line reports both sets and the chosen mode. (Validation does not need to strip a WHERE :Px = ... clause — DBMS_SQL.parse checks syntax + names, not bind values.)

Bind mode controls where inline binds are baked in:

Mode source_query source_binds
JSON (default, safer) q'~... where id = :P200_ID ...~' (intact) `'{"region":"DE","P200_ID":"'
QUERY (opt-in) `q'...'

JSON (the default) keeps the stored SQL clean, benefits from Oracle's plan cache because the engine binds the value at run time, and never concatenates a caller value into the SQL text. QUERY is simpler (value visible on the job row) but bakes the value in literally — only use it for trusted, non-user-controlled values. Inline values are wrapped as JSON strings; Oracle converts to NUMBER on bind when the target column type requires it.

Key format facts (learned from the reference export)

  • The app id lives in deployments/default.json (app.id), not in application.apx. There is no workspace block — the workspace comes from the logged-in session / connection. (App id: 1100. APEX reserves app IDs 3000–8999 and 40000–49999 — pick outside those, and avoid collisions.)
  • Content regions on a @/standard page use slot: body (not contentBody).
  • .apex/apexlang.json mmdVersion must match the instance (26.1.0+3102).

Install (App Builder or SQLcl)

  • SQLcl (run from the repo root, connected as the schema that owns the KMLeon objects): 
    apex validate -input apex_toolkit
apex import   -input apex_toolkit
  • App Builder: zip the app folder and import the zip (App Builder ▸ Import).

Then install the helper package and a few sample jobs:

@apex_toolkit/setup.sql

This creates PCK_KMLEON_TOOLS (the page helpers) and seeds a few sample jobs via PCK_KML_JOB_API so the reports have data immediately. Requires the CREATE JOB privilege for the Async playground (run_async) and the cleanup scheduler.