Merge pull request 'docs(troubleshooting): document the browser_info / browser_url_vars support diagnostics' (#9) from docs/support-diagnostics into main

Reviewed-on: #9

docs/dcc-validations.md

@@ -23,17 +23,17 @@ It is possible to configure a number of other rules by updating the MPE_VALIDATI
 
 ## Configurable Checks
 
-Check back frequently as we keep growing this list of checks.
+Check back frequently as we keep growing this list of checks.  For how each rule treats a special missing value (`.A`-`.Z`, `._`), see [Special Missing Values](#special-missing-values).
 
 |Rule Type|Example Value |Description|
 |---|---|---|
 |CASE|UPCASE|Will enforce the case of cell values.  Valid values: UPCASE, LOWCASE, PROPCASE|
-|NOTNULL|(defaultval)|Will prevent submission if null values are present.  Optional - provide a default value.|
-|MINVAL|1|Defines a minimum value for a numeric cell|
-|MAXVAL|1000000|Defines a maximum value for a numeric cell|
+|NOTNULL|(defaultval)|Will prevent submission if null values are present - a special missing counts as null.  Optional - provide a default value.|
+|MINVAL|1|Defines a minimum value for a numeric cell.  A special missing value sorts below every number, so it fails a numeric floor, and the floor itself may be a special missing - see [Special Missing Values](#special-missing-values).|
+|MAXVAL|1000000|Defines a maximum value for a numeric cell.  A special missing value sorts below every number, so it passes a numeric ceiling, and the ceiling itself may be a special missing - see [Special Missing Values](#special-missing-values).|
 |READONLY|(defaultval) |Renders the column read-only in the editor.  The defaultval is used when rows are added. |
 |HIDDEN|(defaultval) |Hides the column from the editor grid while still submitting its data. The defaultval is used when rows are added. |
-|ROUND|2 |Rounds numeric input on paste/edit. Positive digits round to number of decimal places (eg 2 rounds to 0.01) Negative digits round to the nearest ten/hundred etc.  Half-away-from-zero rounding is applied so `-0.5 → -1` and `2.5 → 3`. |
+|ROUND|2 |Rounds numeric input on paste/edit. Positive digits round to number of decimal places (eg 2 rounds to 0.01) Negative digits round to the nearest ten/hundred etc.  Half-away-from-zero rounding is applied so `-0.5 -> -1` and `2.5 -> 3`. |
 |NUMBER_FORMAT|`{"style":"currency","currency":"GBP"}` |Display-only [`Intl.NumberFormat` renderer](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Intl/NumberFormat).  RULE_VALUE is the JSON options object passed straight to `Intl.NumberFormat`.  Does not change the stored value. |
 |HARDFORMULA|`= PRICE * VOLUME`|The cell displays a value computed from a formula, using other columns in the same row.  The column is read-only - the user cannot override the result.  See [Formula Rules](#formula-rules) below.|
 |SOFTFORMULA|`= if( DC.ROW_STATUS != 'U', DC.USER_NAME, DC.ORIG_VALUE )`|Like HARDFORMULA, but the user can override the computed value and type their own.  See [Formula Rules](#formula-rules) below.|
@@ -45,6 +45,34 @@ Check back frequently as we keep growing this list of checks.
 |[SOFTSELECT_HOOK](/dynamic-cell-dropdown)|/physical/path/program.sas|A SAS service (STP or Viya Job) or a path to a SAS program on the filesystem.  User-provided values may (or may not) be in this list.  Cannot be used alongside a HARDSELECT_HOOK.|
 
 
+## Special Missing Values
+
+A SAS numeric variable has 28 missing values: the regular `.` and 27 special ones (`.A`-`.Z` and `._`).  Data Controller accepts them in a numeric cell as a letter - `a` to `z` - or an underscore, with or without a leading period (`a`, `.a`, `_`, `._`), and stores the matching special missing in SAS.  The period is optional, and a plain `.` stores the regular missing.
+
+A numeric column that carries a date, datetime or time format is the exception.  Those cells are edited through a date picker rather than the numeric editor, and the picker accepts only a date - so a special missing cannot be typed into one of them.
+
+The rules treat a special missing as a value for some checks and as a missing value for others - and the range rules compare it in the order SAS gives it, below every number but ordered among the other missing values:
+
+|Rule|What happens to a special missing|
+|---|---|
+|NOTNULL|Fails.  A special missing is a missing value, so a NOT NULL (or primary key) constraint rejects it, and a real SAS NOT NULL constraint on the target table rejects it too.|
+|MINVAL|Fails if the minimum is a number - a missing sorts below every number, so it is below any numeric floor.  A minimum that is itself a special missing is compared in the order the missing values have among themselves, and can accept one.|
+|MAXVAL|Passes - a missing sorts below every number, so it is below any numeric ceiling.  A ceiling that is itself a special missing is compared in the missing values' own order, and can reject one.|
+|CASE|Not applicable - it is a character rule.|
+|ROUND|Ignored - ROUND only rounds values that are numbers, so a special missing is left as typed.|
+|HARDREGEX / SOFTREGEX|Checked against the pattern like any other value.  Unlike blanks and the plain `.`, special missings are **not** exempt.  See [Regex Rules](#regex-rules).|
+|SOFTSELECT|Supports special missings - a soft dropdown never blocks a value.|
+|HARDSELECT|Supports special missings - the value is matched against the dropdown list like any other value.|
+|HARDFORMULA / SOFTFORMULA|The formula returns `#VALUE!` for that row instead of a number, and that is what gets submitted.|
+
+In short, a special missing is rejected by NOTNULL, by a primary key column, and by a MINVAL whose floor is a number; a formula returns `#VALUE!` for it.  It is fine in a column that carries a MAXVAL, a regex, a dropdown, or ROUND - and it can satisfy a MINVAL when the floor is expressed as a special missing.
+
+A range rule compares in the order SAS itself uses, so a range written in special missings means what SAS would mean by it.  That order puts every missing below every non-missing value, and orders the missing values as `._`, then the regular missing, then `.A` through `.Z`.  So `MINVAL .A` with `MAXVAL .C` accepts `.B` and rejects `.D`, and a blank - the regular missing - fails a floor of `.A` because it sorts below it.  A number sorts above every missing, so it passes a floor of `.A` and fails a ceiling of `.C`.
+
+A primary key column is treated as NOT NULL whether or not the target table carries a physical constraint, and whether or not MPE_VALIDATIONS has a NOTNULL rule for it - a key identifies the row, so a blank and a special missing are both rejected there.
+
+A range rule's own value is read as a number when it looks like one and as a missing value when it is a special missing.  A value that is neither - a typo such as `..` or `AB` - satisfies nothing, so every cell in the column fails until the rule is corrected.
+
 ## Formula Rules
 
 HARDFORMULA and SOFTFORMULA let you configure a column so that its value is automatically calculated from other columns in the same row - just like a spreadsheet formula.  When a user opens the editor, the formula is evaluated live and the result is shown in each cell.

docs/dci-deploysas9.md

@@ -14,6 +14,8 @@ There are two ways to deploy Data Controller on SAS 9:
 * Full Deployment (preferred)
 * Streaming (for quick demos)
 
+Both fetch a deployment program from the [releases page](https://git.datacontroller.io/dc/dc/releases) - see the [downloads page](/downloads) for what each asset contains and how to verify it.
+
 ### Full Deployment
 
 #### 1 - Deploy Stored Processes
@@ -27,16 +29,16 @@ filename dc url "https://git.datacontroller.io/dc/dc/releases/download/latest/sa
 %inc dc;
 ```
 
-If you don't have internet access from SAS, download `sas9.sas` from [here](https://git.datacontroller.io/dc/dc/releases), and change the initial `compiled_apploc` and `compiled_serverName` macro variable assignments as necessary.
+If you don't have internet access from SAS, download `sas9.sas` from [here](https://git.datacontroller.io/dc/dc/releases), verify it against the release `SHA256SUMS` file ([downloads page](/downloads/#verifying-a-download)), and change the initial `compiled_apploc` and `compiled_serverName` macro variable assignments as necessary.
 
 #### 2 - Deploy the Frontend
 
-The Data Controller frontend comes pre-built, and ready to deploy to the root of the SAS Web Server (mid-tier).
+The Data Controller frontend comes pre-built, and ready to deploy to the SAS Web Server (mid-tier).
 
 Deploy as follows:
 
-1.  Download the `frontend.zip` file from: [https://git.datacontroller.io/dc/dc/releases](https://git.datacontroller.io/dc/dc/releases)
-2. Unzip and place in the [htdocs folder of your SAS Web Server](https://sasjs.io/frontend-deployment/#sas9-deploy) - typically a subdirectory of: `!SASCONFIG/LevX/Web/WebServer/htdocs`.
+1.  Download the `frontend.zip` file from: [https://git.datacontroller.io/dc/dc/releases](https://git.datacontroller.io/dc/dc/releases) (verify it against the release `SHA256SUMS` file - see the [downloads page](/downloads/#verifying-a-download))
+2. Create a folder for Data Controller in the [htdocs folder of your SAS Web Server](https://sasjs.io/frontend-deployment/#sas9-deploy) - typically a subdirectory of: `!SASCONFIG/LevX/Web/WebServer/htdocs` - and unzip the archive **into** that folder. The archive holds the frontend files at its root, so `index.html` sits directly inside your folder (alongside the `images` folder and the compiled asset files) - for example `htdocs/dc/index.html`.
 3. Open the `index.html` file and update the values in the `<sasjs>` tag as follows:
 
   * `appLoc` - same as per SAS code in the section above
@@ -47,7 +49,7 @@ Deploy as follows:
 
 The remaining properties are not relevant for a SAS 9 deployment and can be **safely ignored**.
 
-You can now open the app at `https://YOURWEBSERVER/unzippedfoldername`  (step 2 above) and follow the configuration steps (DC Physical Location and Admin Group) to complete deployment.
+You can now open the app at `https://YOURWEBSERVER/<yourfoldername>`  (the folder created in step 2 above) and follow the configuration steps (DC Physical Location and Admin Group) to complete deployment.
 
 #### 3 - Run the Configurator
 
@@ -93,7 +95,7 @@ filename dc url "https://git.datacontroller.io/dc/dc/releases/download/vX.X.X/de
 %inc dc;
 ```
 
-If you don't have internet access from your SAS environment, just download `demostream_sas9.sas` from [https://git.datacontroller.io/dc/dc/releases](https://git.datacontroller.io/dc/dc/releases) and modify the `appLoc` on line 2, as follows:
+If you don't have internet access from SAS, download `demostream_sas9.sas` from [https://git.datacontroller.io/dc/dc/releases](https://git.datacontroller.io/dc/dc/releases) (verify it against the release `SHA256SUMS` file - see the [downloads page](/downloads/#verifying-a-download)) and modify the `appLoc` on line 2, as follows:
 
 ![](img/sas9_apploc.png)
 

docs/dci-troubleshooting.md

@@ -108,8 +108,129 @@ This can happen if you enter the wrong `serverName` when deploying the SAS progr
 
 The error may also be thrown due to an encoding issue - changing to a UTF-8 server has helped at least one customer.
 
+## Displayed timestamps are in UTC (or the wrong timezone)
+
+Data Controller records timestamps (such as the SUBMITTED column on the Submitted screen, and the audit history) using the SAS session clock, via the `datetime()` function. That function returns the time of the operating system, adjusted by the [TIMEZONE= system option](https://documentation.sas.com/doc/en/pgmsascdc/default/lesysoptsref/p15siqs0s00e50n1wuuvygzkr14r.htm) if it is set. The value is then displayed in the frontend exactly as stored, without conversion.
+
+On Viya, the SAS compute sessions that run Data Controller jobs are started inside Kubernetes containers whose clock is UTC by default, and which inherit no timezone from the host machine. If the TIMEZONE= option is not set for the compute context used by Data Controller, every timestamp DC records and displays is UTC. Other SAS products can appear unaffected because clients such as SAS Studio create their own compute sessions and pass the browser timezone / locale, whereas Data Controller submits jobs to its own shared compute context, which gets the raw container clock.
+
+To confirm the current state, run the following in a SAS session under the Data Controller compute context (or check the DC job log, where `_DEBUG` output shows the same values):
+
+```sas
+proc options option=timezone;
+run;
+%put &=SYSTIMEZONEIDENT &=SYSTIMEZONEOFFSET;
+```
+
+If TIMEZONE is blank (and SYSTIMEZONEOFFSET is 0), the session is on UTC. To fix it, ask your Viya administrator to set the timezone for the compute context used by Data Controller (in SAS Environment Manager, edit the context's Autoexec, or set the context's SAS options):
+
+```sas
+options timezone='Europe/Berlin';
+```
+
+Then recycle any existing compute sessions - hot sessions keep the old setting until they terminate.
+
+!!! note
+    Use a region/area time zone ID such as `Europe/Berlin` rather than a fixed offset such as `GMT+2`. Fixed offsets do not follow daylight saving time, so the display would be 1 hour off in winter (CET = UTC+1).
+
+!!! note
+    Changing the timezone affects new timestamps only. Previously recorded submissions keep the UTC values that were stored when they were created.
+
 ## Determining Application Version
 
 The app version is bundled into the frontend during the release, and is visible by clicking your username in the top right.
 
 You can also determine the app version (and SASjs Version, and build time) by opening browser Development Tools and running `appinfo()` in the console.
+
+## Support Diagnostics
+
+The frontend sends two input tables with the startup service and with the services that execute customer-provided code - the [hook scripts](macros.md) (pre/post edit and approve hooks) and the dynamic cell dropdown programs. They are available in those services as `work.browser_info` and `work.browser_url_vars`, so a support ticket can be diagnosed from the job log without asking the user follow-up questions, and a hook can adapt to its context. Other services do not receive them.
+
+The services that receive the tables are: `public/startupservice`, `editors/getdata`, `editors/getdynamiccolvals`, `editors/stagedata`, `editors/loadfile`, `editors/restore` and `auditors/postdata`.
+
+`editors/loadfile` is the exception in practice: the app reaches it through the adapter's file-upload call (multipart), which carries no input tables, so the service - and the post edit hook it runs - does not receive them in normal use. It is on the list for completeness, and would receive them if the service were ever invoked through the ordinary request path.
+
+### browser_info
+
+A single-row table with these columns:
+
+| Column | Description |
+|---|---|
+| `url` | The URL of the Data Controller page itself (the iframe), not the document embedding it. When the editor is embedded in a report, the embedding URL is visible here. |
+| `referrer` | The embedding document, from `document.referrer`. For an embedded report this is the report URL, so the embedding report can be told apart from the editor URL. |
+| `timezone` | The browser's IANA timezone name, e.g. `Europe/Berlin`. |
+| `tz_offset` | The browser's UTC offset in minutes, as `Date.getTimezoneOffset()` returns it (positive west of UTC, so `Europe/Berlin` in summer is -120). |
+| `locale` | The browser locale, e.g. `en-GB`. |
+| `dc_version` | The Data Controller build, as shown by `appinfo()`. |
+| `adapter_version` | The `@sasjs/adapter` version the client was built with. |
+| `browser` | The browser family, parsed from the user agent. |
+| `browser_version` | The browser version. |
+| `platform` | The operating system, parsed from the user agent. |
+| `user_agent` | The raw `navigator.userAgent` string. |
+
+### browser_url_vars
+
+The page URL parameters as one row per parameter, so a SAS program can read a parameter by name rather than parse the `url` string:
+
+| Column | Description |
+|---|---|
+| `name` | The URL parameter name. |
+| `value` | The URL parameter value. |
+
+Parameters from both the search string and the hash query string (Angular routes carry them after the `#`) are included; on a name collision the search string value wins. The table is sent only when the page URL has at least one parameter.
+
+The values in both tables are supplied by the client, so treat them as diagnostics hints rather than a security boundary.
+
+To see the tables, run any of the services above with debug on. The debug value depends on the path: `&_debug=131` on the Compute API and SAS 9, or `&_debug=128` on the Viya web (JES) path with `runAsTask` enabled - which is what the frontend sends there, so turning debug on in the app is enough. Either value makes the session initialisation write the tables to the job log:
+
+```
+NOTE: MPEINIT: work.browser_url_vars:
+name=embed value=va
+NOTE: MPEINIT: work.browser_info:
+url=... referrer=... timezone=Europe/Berlin tz_offset=-120 locale=en-GB
+dc_version=7.15.0 adapter_version=4.19.0 browser=Chrome browser_version=120.0
+platform=Linux user_agent=...
+```
+
+A service called directly (for instance from a URL, or by a script), or by an older frontend, has no `work.browser_info` - the log then records that fact instead.
+
+### Reading the tables from a hook
+
+A [hook script](macros.md) is `%include`d into the running service, so it shares the service's WORK library and can read the tables directly. Take an embedded editor opened at:
+
+```
+https://datacontroller.io/#/editor/SASHELP.CLASS?labels=true&embed=va
+```
+
+`browser_url_vars` then holds a row per parameter - `labels=true` and `embed=va` - so a hook can pick up either by name:
+
+```sas
+%let labels=N;
+%let embed=N;
+
+%if %sysfunc(exist(work.browser_url_vars)) %then %do;
+  proc sql noprint;
+    select value into :labels
+    from work.browser_url_vars
+    where name = 'labels';
+    select value into :embed
+    from work.browser_url_vars
+    where name = 'embed';
+  quit;
+%end;
+```
+
+To branch on what the client reports:
+
+```sas
+%if %sysfunc(exist(work.browser_info)) %then %do;
+  data _null_;
+    set work.browser_info;
+    call symputx('dc_timezone', timezone);
+    call symputx('dc_locale', locale);
+  run;
+%end;
+```
+
+!!! warning
+    Guard with `%sysfunc(exist())` before reading. A service called directly, or by a frontend older than this feature, has no `work.browser_info` at all, so an unguarded `set` or `select` fails the job.

docs/dcu-tableviewer.md

@@ -16,6 +16,18 @@ Row and Column level security can also be applied in VIEW mode, as can additiona
 
 A single search box can be used to make a full table search on any character or numeric value, using this [macro](https://core.sasjs.io/mp__searchdata_8sas.html).
 
+The search covers **every** column of the table at once, so you do not need to know which column holds the value you are looking for:
+
+- **Character search** (the default) is a case sensitive CONTAINS match against every character column, so `Smith` finds `Smithson` and `smith` finds `Goldsmith`, but `smith` will not find `Smithson`.
+- **Numeric search** (tick the Numeric box) is an exact match against every numeric column, so `42` will not find `420` or `4210`.
+- If a filter is applied, the search runs within the filtered rows rather than the whole table.
+- The grid is capped by the [`DC_MAXOBS_WEBVIEW`](/dcc-options/#dc_maxobs_webview) option (500 rows by default).
+- For a search, the cap applies to the row count as well: a search that matches more rows than the cap returns 500 of them, and the count shown next to the table name is the number of rows returned, not the total number of matches.
+- Without a search, the count shown next to the table name is the full number of rows in the filtered view, so it can be higher than the number of rows displayed - a table of 1,200 rows reports `1,200 rows` above a grid holding the first 500.
+- A search that matches nothing shows a "No data found with given conditions" panel rather than an empty grid.
+
+![Full table search](img/full-table-search.png)
+
 <iframe width="560" height="315" src="https://www.youtube.com/embed/i27w-xq85WQ" title="YouTube video player" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture" allowfullscreen></iframe>
 
 ## Options

docs/downloads.md

@@ -0,0 +1,52 @@
+---
+layout: article
+title: DC Downloads
+description: Data Controller release assets and how to verify them
+---
+
+# Downloads
+
+Data Controller releases are published on the [releases page](https://git.datacontroller.io/dc/dc/releases) of the source repository. Every release carries the same set of assets:
+
+| Asset | Use |
+|---|---|
+| `frontend.zip` | The pre-built web frontend, for deployments that serve the frontend from a web server (SAS 9 full deploy, Viya separated deploy) |
+| `sas9.sas` | SAS 9 full deployment program (stored processes + frontend registration) |
+| `demostream_sas9.sas` | SAS 9 streaming deployment program (demos and evaluations) |
+| `viya.sas` | Viya streaming deployment program (backend + frontend) |
+| `viya_noweb.sas` | Viya separated deployment program (backend only) |
+| `viya_noweb.json` | The Viya backend services as a SASjs Drive JSON deployment bundle |
+| `sasjs_server.json.zip` | SASjs Server deployment bundle (all services, for [SASjs Server](https://server.sasjs.io) deployments) |
+| `SHA256SUMS` | SHA-256 checksums for every asset above |
+
+Which asset you need depends on the deployment route - see [SAS 9 Deployment](/dci-deploysas9) or [SAS Viya Deployment](/deploy-viya).
+
+## Verifying a download
+
+Every asset is covered by the `SHA256SUMS` file, which the release pipeline generates over the exact files it uploads. To verify a download:
+
+1. Download the asset (for example `frontend.zip`) from the [releases page](https://git.datacontroller.io/dc/dc/releases).
+2. Download `SHA256SUMS` from the same release into the same folder.
+3. Run the check:
+
+```bash
+sha256sum --check SHA256SUMS --ignore-missing
+```
+
+Each asset that matches reports `OK`. The `--ignore-missing` flag means you only need the files you actually downloaded - the check skips assets you did not fetch.
+
+On Windows, `certutil` can compute the hash of a single file for comparison against the value listed in `SHA256SUMS`:
+
+```
+certutil -hashfile frontend.zip SHA256
+```
+
+### What the checksum proves
+
+A matching checksum confirms the asset is byte-for-byte the file the release pipeline uploaded. It catches corrupted and tampered downloads - interrupted transfers, proxies or mirrors that altered the file in transit.
+
+It is not a cryptographic signature. The hashes travel in the same release as the files they cover, so they protect the download path, not the release itself: a compromise of the forge would allow both the asset and its checksum to be altered together. For download integrity - the typical concern when fetching deployment artifacts over the internet - it is the standard check.
+
+## Integrity inside SAS
+
+The SAS deployment programs embed the frontend and services as generated SAS code. Once `%inc`'d, their integrity is established by the check above at download time. They deploy the stored processes or jobs into the SAS Folder you nominate (`appLoc`) - a metadata location, not physical disk - and the only physical location they write to is the Data Controller location (`dcLoc`) you configure. The programs can be [reviewed in full](https://code.datacontroller.io) (or in the [source repository](https://git.datacontroller.io/dc/dc)) before you run them.

mkdocs.yml

@@ -71,6 +71,7 @@ nav:
 - Macros: macros.md
 - Installation:
     - System Requirements: dci-requirements.md
+    - Downloads: downloads.md
     - SAS Viya: deploy-viya.md
     - SAS 9 EBI: dci-deploysas9.md
     - SAS 9 STP Hardening: dci-stpinstance.md