BroadSQL BroadSQL Downloads
On this page
Documentation / Scripting: Scripts Library

Scripts Library

The Scripts Library is the folder of reusable Scripts: scripts/ by default, set by the ScriptsLibrary key of BroadSQL.ini (see Application settings). This page covers managing it.

Metadata header

Any Script can start with a short header of metadata lines. A file without them is simply untagged.

-- @description: Monthly revenue by country
-- @instance: MYSAP
-- @environment: PROD
-- @tags: finance, monthly, revenue
-- @status: stable
-- @params: year
select country, sum(amount) from revenue where year = ${year} group by country;

Both -- @key value and -- @key: value are recognized, in -- line comments or /* ... */ block comments, so select '@instance WRONG'; is never mistaken for metadata. If a key is declared more than once with conflicting values, the first occurrence wins; @instance, @environment, @tags and @params may instead be repeated on purpose to accumulate several values.

The @instance tag and the grid's Group column predate the Database Group rename and keep that name in the file format: they refer to the current connection's Database Group.

KeyMeaning
@descriptionA short, one line description, shown by search and in the Editor.
@instanceThe Database Group(s) this Script is about: an id such as MYSAP, several ids separated by commas, or ALL. Absent, the Script is untagged (shown as NONE) and always applies.
@environmentThe environment(s) this Script applies to, in the same forms.
@tagsComma separated free text tags, matched by LIB FIND.
@statusdraft, stable or deprecated. Purely informational.
@paramsThe arguments each call must pass (see Required arguments).

Metadata describes a Script; it never finds one. Nothing is looked up by description, tag or status, and an old @alias line is an ordinary comment.

What @instance and @environment do and do not enforce

Both tags are safeguards, not access control. They apply to every Script that runs, including a called Script and a Script run through an explicit path. When a Script starts, its tags are compared with the current connection; a mismatch prints one warning line per mismatched dimension, then the Script runs anyway. With no connection open, nothing is compared.

  • Nothing is blocked by a mismatch, whatever the connection.
  • The Production flag of an environment has no connection to these tags.
  • Browsing uses them too: LIB LIST without ALL hides a Script that does not match both the current Database Group and environment (or is tagged ALL, or untagged).
  • Only the current connection matters; other connections are never opened or compared.

Browsing and searching

LIB LIST shows every Script in the library, including Scripts in subfolders, under their library path such as maintenance/cleanup.bsql. The grid has one row per Script: File, Group, Environment, Status and Modified. By default the list is scoped to the current connection's Database Group and environment; LIB LIST ALL lifts both. A search term filters by a case insensitive part of the path.

LIB LIST;
LIB LIST revenue;
LIB LIST ALL;

LIB FIND <term> searches the full text of every Script, header included, so a description or tag matches too. It always searches the whole library. Search and listing are the only places a partial name means anything; running, showing, editing and deleting always use the exact path.

LIB SHOW <script> prints a Script's full content and its declared parameters. Only text files are listed; a binary file in the folder is ignored.

Editing

LIB EDIT <script> and EDIT <script> open the Script in the BroadSQL Editor, which shows the Scripts Library as a folder tree. Opening does not save anything. If the Script does not exist, the Editor offers to create it; a new Script named without an extension gets .bsql and starts with -- @status: draft.

Deleting safely

LIB DEL <script> asks for a y/n confirmation, then moves the Script to the archives/ folder of the library instead of deleting it. The Editor's Delete does exactly the same, and its Recently Deleted window lists the same archive. Nothing is purged automatically. To bring a Script back:

  • LIB UNDO restores the Script archived most recently.
  • LIB RESTORE <script> restores a specific archived Script by the exact path it had.

Both refuse, rather than overwrite, when a Script exists at that path again. A restored Script's revision history in the Editor continues; a new file created at the same path after a deletion starts a new history. Browse the archive with LIB LIST ARCHIVES.

Checking consistency: LIB LINT

LIB LINT, optionally followed by one Script's path, reports without changing anything: an @instance id that matches no Database Group, an @environment id that matches no environment, an invalid, reserved or repeated name in -- @params:, every %1 to %9 left from the removed positional parameters (a LIKE '%1%' pattern is reported too: check it), and a ${name} written inside a quoted string, where it is not replaced (except in ECHO). It never depends on the variables of the session.

Encoding

BroadSQL text files are UTF-8. New Scripts are written as UTF-8 without a byte order mark. BroadSQL reads a Script with a byte order mark using that encoding, otherwise as UTF-8 if the whole file is valid UTF-8, and otherwise as Windows-1252 (the Western "ANSI" code page of Windows editors). The result never depends on the operating system or on the Java file.encoding setting. Save Scripts containing other writing systems (Greek, Cyrillic, Asian languages) as UTF-8. The same rule applies to the files read by <@fileName>. When you save an existing file in the Editor, it is written back in the encoding it was read in; if the text cannot be represented in that encoding, the save is refused and the file is not changed.

A file whose beginning contains a NUL byte or mostly control characters is not text: it is not listed, cannot be opened in the Editor, and cannot be run.