BroadSQL Download
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
-- @access: read
-- @dialect: PostgreSQL, H2
-- @param year INTEGER "Calendar 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, @dialect and @params may instead be repeated on purpose to accumulate several values, and @param is written once per parameter.

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.
@accessread (the Script changes no database) or write. Declared by the author, never verified or enforced when the Script runs; see What @access and @dialect do.
@dialectThe connection type(s) the Script is written for (PostgreSQL, H2, Oracle...), or ANY.
@paramOne parameter with its type, optional default and description (see Typed parameters).
@paramsThe arguments each call must pass (see Required arguments).

Metadata describes a Script; it never identifies one. Running, showing and editing a Script always use its path, never its description, tags or status. For finding Scripts, LIB FIND matches metadata as text, and LIB LIST ... BY TAG (or BY STATUS, BY DIALECT...) groups the list by it. 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.

What @access and @dialect do

Both are information written by the Script's author, never a permission or a block.

  • @access: read says the Script is meant to change no database. LIB SHOW shows it as Access: read (declared, not verified), and LIB LINT checks it: a read Script containing an INSERT, an UPDATE, a CREATE, a LOAD, a DUMP ... AS H2 or a connection management command is reported. Running the Script is never changed by it: no confirmation is skipped, no safeguard relaxed. What a function called inside a query does cannot be checked.
  • @dialect names the connection types the Script is written for. A name matches a type exactly, ignoring case, or as its first word: DERBY matches DERBY Embedded and DERBY Client. With no connection, or a connection without a type, nothing is compared. ANY declares a portable Script. Versions (PostgreSQL 15) are not part of a dialect.

When the current connection has another type than the declared dialects, running or showing the Script prints a warning and runs it anyway:

The script reports/sessions.sql is written for Oracle; the current connection's type is H2

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, access and dialects. Only text files are listed; a binary file in the folder is ignored.

Grouping: LIB LIST ... BY

BY at the end of LIB LIST or LIB FIND shows the same Scripts in groups, by TAG, GROUP, ENVIRONMENT, DIALECT, STATUS, ACCESS or FOLDER:

LIB LIST ALL BY TAG;
Scripts Library (every Database Group/environment), grouped by tag:
geo (3)
|----------------------|-----|-----------|------|----------------|
|File                  |Group|Environment|Status|Modified        |
|----------------------|-----|-----------|------|----------------|
|cities/capitals.sql   |NONE |NONE       |draft |2026-10-15 17:00|
|countries/by-codes.sql|NONE |NONE       |      |2026-10-15 17:00|
|countries/largest.sql |NONE |NONE       |      |2026-10-15 17:00|
|----------------------|-----|-----------|------|----------------|

reference (1)
...
(no tag) (3)
...

A Script with two tags appears in both groups; Scripts without a value come last. Without BY, the list is the usual one, and LIB LIST BY alone still searches for BY in the paths.

The folder tree: LIB TREE

LIB TREE shows the folders with the number of Scripts each holds, then their Scripts with their descriptions, in the same scope as LIB LIST (ALL for every Script):

LIB TREE ALL;
Scripts Library tree (every Database Group/environment, 6 Scripts):
admin/ (2)
  cleanup.bsql         [deprecated] Removes test rows
  oracle_sessions.sql  Open sessions
cities/ (1)
  capitals.sql         [draft] Capital of each country
countries/ (3)
  by-codes.sql         Countries by ISO code
  copy-of-largest.sql  A copy someone made
  largest.sql          Largest countries of a continent

LIB TREE countries shows one folder. Folders are the library's real subfolders; there is no other grouping.

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, a @dialect that names no connection type;
  • an invalid, reserved or repeated name in -- @params:; a -- @param line that cannot be read, a parameter declared twice, a default that is not a value of its type; as hints, a declared parameter the Script never uses and a ${name} that no declaration and no LET of the Script defines;
  • an @access value other than read or write, and in a read Script, every statement that may write;
  • 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);
  • duplicate Scripts: identical SQL, or the same SQL apart from layout, comments and letter case.
LIB LINT;
LIB LINT found 2 issue(s):
- admin/cleanup.bsql: declared @access read, statement 1 is a DELETE
- same SQL apart from layout, comments and letter case: countries/copy-of-largest.sql, countries/largest.sql (their @access declarations differ)

Duplicates are compared as text only: two queries that give the same result in different ways are not reported, and a different value ('FR' and 'fr') or parameter name makes two Scripts different. Nothing is merged or deleted: compare them with LIB SHOW and remove one with LIB DEL if you wish (LIB UNDO brings it back). LIB LINT 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.

Found something wrong or unclear on this page? , with the page address.