Documentation

Creating a Bundle

Outline

For each Bundle command one can pass data (via stdin) like selected text, current word, selected table row data CSV formatted etc. to process these data and specify what should be done with the result (stdout) like insert as text, replace selection, show as tooltip, show in HTML window, etc. In addition in each Bundle command script there are several shell variables like SP_BUNDLE_PATH, SP_CURRENT_QUERY, SP_ALL_FUNCTIONS, SP_CURRENT_HOST, SP_SELECTED_ROW_INDICES, etc. available.

Have you made a bundle?

Let us know about it!

Submit a Bundle

Accessorily in any scripting language one can use Sequel Pro's URL scheme commands. These sequelpro:// URL scheme commands allow you e.g. to execute any SQL statement in the front most connection window and process the result, select tables, databases, and table rows, change the current view, reload the Content view table by a passed WHERE clause etc.

A Bundle command has to be defined for one of theses scopes (a GUI type which has the key focus) “Input Field” – any text input field including table cells while edited, “Data Table” – the data tables in Content and Query view, and “General” – neither text input fields nor data tables.

Each new generated Bundle will have an Universally Unique Identifier (UUID) to make it possible to identify (mainly for updating) Bundles regardless the Bundle spBundle name.

Because a bundle is just a specially named folder it is possible to include any additional files that are necessary to perform the action.

Stored Bundle commands will be populated in the main menu under “Bundles” grouped into the submenus “Input Field” and “Data Table”, commands saved with the scope “General” will be added successively. By defining a menu category it is possible to group commands inside a scope which will be displayed by themselves as submenus. In addition commands for the scopes “Input Field” and “Data Table” will be appended to the corresponding context menus (right-click).

Each Bundle command passes data to the script and returns data back to Sequel Pro UTF-8 encoded.

Bundle Editor

The new bundle editor allows you to manage the bundles you have installed and to share your bundles. As you write a new Bundle it will dynamically update its location in the Bundles list according to the Bundle Scope you have specified (and optionally the Menu Category you have entered).

The names in the list directly match the name of any installed *.spBundle files names so make sure to also use unique and valid file names, otherwise we will rename your file to avoid conflicts with existing Bundles.



The following list describes the possible settings of each Bundle command.

Hint Available settings for Input, Output, and Trigger are scope-depended and Input fallback and BLOB settings depend on the setting for Input.

Menu Label 
Enter the menu item label which will appear in the Main Menu > Bundles and in the context menu due to the given scope
Scope 
Choose on basis of which scope (a GUI element which has the current focus) the script should be executed; if one changes the scope the Bundle will be moved in the outline view automatically
General
any GUI element but not a text field input Input Field or data table Data Table has the focus
Input Field
any text input field including editing cells inside of tables has the focus
Data Table
the Content or Query data table has the focus
Meta Data
show or hide the meta data (Author, Contact, and Description) input panel
Input
specify which data due to the set scope should be piped to the script (available as stdin)
None
no data will be available at stdin
Selected Rows (TSV)
tab-delimited string array of the content of the selected data table rows (due to BLOB setting) whereby the first line contains the actual column names (tab characters are replaced by ⇥ U+21E5 UTF-8: E287A5 and newline characters by ↵ U+21B5 UTF-8: E286B5)
Selected Rows (CSV)
CSV formatted string of the content of the selected data table rows (due to BLOB setting) whereby the first line contains the actual column names
Selected Rows (SQL)
INSERT INTO … (…) VALUES string of the content of the selected data table rows (due to BLOB setting)
Table Content (TSV)
tab-delimited string array of the content of all data table rows (due to BLOB setting) whereby the first line contains the actual column names (tab characters are replaced by ⇥ U+21E5 UTF-8: E287A5 and newline characters by ↵ U+21B5 UTF-8: E286B5)
Table Content (CSV)
CSV formatted string of the content of all data table rows (due to BLOB setting) whereby the first line contains the actual column names
Table Content (SQL)
INSERT INTO … (…) VALUES string of the content of all data table rows (due to BLOB setting)
exclude BLOB
any BLOB fields will be replaced by an empty string
include BLOB
any BLOB fields will be outputted as plain text
save BLOB as image file
try to interpret the content of any BLOB field as image which will saved as rowNumebr_columnNumber.tif</pre> file into the folder of the shell variable SP_BUNDLE_BLOB_FILES_DIRECTORY and output the absolute path to the corresponding image file
save BLOB as dat file
the content of any BLOB field will saved as it is as "rowNumber_columnNumber.dat" file into the folder of the shell variable SP_BUNDLE_BLOB_FILES_DIRECTORY and output the absolute path to the corresponding dat file
Selected Text
the content of the current selection in the text input field (due to following fallback settings)
None
if no selection nothing will be passed to stdin
Current Word
if no selection pass the current word due to caret's position
Current Line
if no selection pass the current line due to caret's position
Current Query
if no selection pass the current highlighted query (only applicable in the Query Editor otherwise it passes the current line)
Entire Content
if no selection pass the entire content of the text input field
Entire Content
then entire content of the text input field
Command
enter the script written in any scripting language like BASH (default), Ruby, Python, Perl, AppleScript, etc. (specified by using the shebang notation – e.g. #!/usr/bin/ruby)
Disable Command
if checked the Bundle command won't appear in the Main Menu > Bundles and corresponding context menu
Output
specify the action how the result of the script should be processed by Sequel Pro
None
ignore the output
Show as Text Tooltip
the output will be displayed in a tooltip interpreted as plain text
Show as HTML Tooltip
the output will be displayed in a tooltip interpreted as HTML source code
Show as HTML
the output will be displayed in a separate HTML output window interpreted as HTML source code
Insert as Text
the output will be inserted into the current text input field
Insert as Snippet
the current selection of the Query Editor (depending on the Input settings) will be replaced by the output whereby the output will be interpreted as a snippet
Replace Selection
the current selection (depending on the Input settings) will be replaced by the output
Replace Entire Content
the entire content of the text input field will be replaced by the output
Menu Category
enter a menu category (a submenu name) under which the Bundle command will appear due to scope for grouping similar commands; if one changes the category name the Bundle will be moved in the outline view accordingly
Key Equivalent
click into the field to record a keyboard equivalent for the Bundle command; not every key equivalent will be available due to global or Sequel Pro's settings and an alert will be displayed; if one wants to use the same key equivalent which will not be used globally one can duplicate the Bundle easily to bind similar Bundle commands at the same key equivalent since if there are Bundle commands of the same scope bound to the same key equivalent a context menu will appear to be able to choose the desired one
Tooltip
enter the tooltip text which will be displayed as tooltip in a menu item
Trigger
None
the Bundle command will not be triggered by a Sequel Pro action
Database changed
the corresponding Bundle command will be executed whenever the current database was changed; if the Output action is set to Show HTML it will be only triggered if the associated HTML output window is already visible i.e. one can switch on/off the triggering
Table changed
the corresponding Bundle command will be executed whenever the current selected item in the Tables list was changed; if the Output action is set to Show HTML it will be only triggered if the associated HTML output window is already visible i.e. one can switch on/off the triggering
Table Row changed
the corresponding Bundle command will be executed whenever the current selected row of the data table was changed; if the Output action is set to Show HTML it will be only triggered if the associated HTML output window is already visible i.e. one can switch on/off the triggering

Bundle Info

The Bundle info button will open a dialog for editing your name, contact information and a description of how the bundle functions.

Command Scripts

As default the content of the command will be interpreted as BASH script. If one wants to write to script in another scripting language one has to write into the first line the so-called shebang notation – e.g. #!/usr/bin/ruby (for Ruby) or #!/usr/bin/env perl -w (for Perl) or #!/usr/bin/osascript (for an AppleScript) etc.

Export/Share Bundles

It is possible to save the entire selected Bundle to the disk by choosing from the gear menu Export Bundle…" which can also be done by dragging a Bundle from the outline view to the Finder, to Mail's icon in the Dock, or to another place. Please note, that the Bundle has to be saved with the file extension "spBundle".

Reveal the Bundle in Finder

In the gear menu you will find a menu item "Reveal Bundle in Finder" bound to ⇧⌘O. This command will reveal the selected Bundle in the Finder. By using the Finder's context menu item "Show Package Contents" you will see its files and directories.

Shell Variables

With the invocation of a Bundle command the following shell variables depending on the scope will be set. Each variable content is UTF-8 encoded. For convenience the shell variable names are available in the completion list (⎋ ⌘. F5) of the Bundle command editor.

Shell Variable Type Scope
SP_ALL_PROCEDURES tab-delimited string all
all user-defined procedure names available from the front most connection window

Restriction: if front most connection window exists
SP_ALL_TABLES tab-delimited string all
all table names (excl. views) available from the front most connection window

Restriction: if front most connection window exists
SP_ALL_VIEWS tab-delimited string all
all view names available from the front most connection window

Restriction: if front most connection window exists
SP_APP_RESOURCES_DIRECTORY string all
Sequel Pro's Resources path (for e.g. using its images etc.)
SP_BUNDLE_EXIT_INSERT_AS_SNIPPET number all
Exit code of a script to redirect its output to insert it as snippet into the currently activated Query Editor

Restriction: only applicable if the target is the Query Editor
SP_BUNDLE_EXIT_INSERT_AS_TEXT number all
Exit code of a script to redirect its output to insert it into the currently activated text input field

Restriction: only applicable if the target is an editable text input field
SP_BUNDLE_EXIT_NONE number all
Exit code of a script to dismiss its output
SP_BUNDLE_EXIT_REPLACE_CONTENT number all
Exit code of a script to redirect its output to replace the entire content of currently activated text input field by it

Restriction: only applicable if the target is an editable text input field
SP_BUNDLE_EXIT_REPLACE_SELECTION number all
Exit code of a script to redirect its output to replace the current selection of currently activated text input field by it

Restriction: only applicable if the target is an editable text input field
SP_BUNDLE_EXIT_SHOW_AS_HTML number all
Exit code of a script to to display its output as HTML representation in a built-in full featured web view window

Restriction: Exit code number for a script
SP_BUNDLE_EXIT_SHOW_AS_HTML_TOOLTIP number all
Exit code of a script to display its output as HTML representation as tooltip
SP_BUNDLE_EXIT_SHOW_AS_TEXT_TOOLTIP number all
Exit code of a script to display its output as a text tooltip
SP_BUNDLE_INPUT string all
file path whose file contains the passed input data which are piped to the script (available via stdin) as well
SP_BUNDLE_INPUT_TABLE_METADATA tab-delimited text array Data Table

file path whose file contains the passed table meta data with the following structure

type of col_1
(as string)		 type grouping of col_1
(as string)		 length of col_1
(as number)		 col_1 is unsigned
(as 0/1 boolean)		 col_1 is auto_increment
(as 0/1 boolean)		 col_1 is primary key
(as 0/1 boolean)		 comment of col_1
(as string)
type of col_2
(as string)		 type grouping of col_2
(as string)		 length of col_2
(as number)		 col_2 is unsigned
(as 0/1 boolean)		 col_2 is auto_increment
(as 0/1 boolean)		 col_2 is primary key
(as 0/1 boolean)		 comment of col_2
(as string)
...
type → SQL data types like INT, VARCHAR, TEXT, etc.
type grouping → string, integer, float, bit, geometry, date, blobdata, textdata, enum
SP_BUNDLE_PATH string all
file path of the current running Bundle path (the script by itself starts at that path as default to make it possible to find other Bundle command resource files by using relative paths)
SP_BUNDLE_SCOPE string all
the scope of the running Bundle command (datatable, inputfield, general)
SP_CURRENT_EDITED_COLUMN_NAME string Input Field
the original column name of the just edited table data cell

Restriction: only applicable if called from a table data cell's editor or from the Field Editor Sheet
SP_CURRENT_EDITED_TABLE string Input Field
the original table name of the just edited table data cell

Restriction: only applicable if called from a table data cell's editor or from the Field Editor Sheet
SP_CURRENT_HOST string all
the connection host of the front most connection window

Restriction: if front most connection window exists
SP_CURRENT_LINE string Input Field
the current line relatively to the caret's position
SP_CURRENT_PORT string all
the connection port of the front most connection window

Restriction: if front most connection window exists
SP_CURRENT_QUERY string Input Field
the current SQL query relatively to the caret's position

Restriction: only applicable if called from the Query Editor otherwise it will be set with content of the current line
SP_CURRENT_USER string all
the connection user of the front most connection window

Restriction: if front most connection window exists
SP_CURRENT_WORD string Input Field
the current word relatively to the caret's position
SP_DATA_TABLE_SOURCE string Data Table, Input Field (if called from table cell's editor)
from which data table and table cell editor resp. the command was called (content or query)
SP_DATABASE_ENCODING string Input Field
the currently used connection encoding of the front most connection window

Restriction: if front most connection window exists
SP_ICON_FILE string all
file path of Sequel Pro's icon file

Restriction: if front most connection window exists
SP_QUERY_FILE string all
file path whose content is that SQL statement which will be executed by the sequelpro URL scheme command sequelpro://$SP_PROCESS_ID@passToDoc/ExecuteQuery
SP_QUERY_RESULT_FILE string all
file path whose content is the result of the via the sequelpro URL scheme commandsequelpro://$SP_PROCESS_ID@passToDoc/ExecuteQuery sent SQL statement
SP_QUERY_RESULT_META_FILE tab-delimited text array Data Table

file path whose file contains the table meta data with the following structure of the result of the via the sequelpro URL scheme command sequelpro://$SP_PROCESS_ID@passToDoc/ExecuteQuery sent SQL statement 

type of col_1
(as string)		 type grouping of col_1
(as string)		 length of col_1
(as number)		 col_1 is unsigned
(as 0/1 boolean)		 col_1 is auto_increment
(as 0/1 boolean)		 col_1 is primary key
(as 0/1 boolean)		 comment of col_1
(as string)
type of col_2
(as string)		 type grouping of col_2
(as string)		 length of col_2
(as number)		 col_2 is unsigned
(as 0/1 boolean)		 col_2 is auto_increment
(as 0/1 boolean)		 col_2 is primary key
(as 0/1 boolean)		 comment of col_2
(as string)
...
type → SQL data types like INT, VARCHAR, TEXT, etc.
type grouping → string, integer, float, bit, geometry, date, blobdata, textdata, enum
SP_QUERY_RESULT_STATUS_FILE string all
file path which will be written to disk after finishing the s sequelpro URL scheme commandsequelpro://$SP_PROCESS_ID@passToDoc/ExecuteQuery with the content 0 ⇢ success or 1 ⇢ error
SP_RDBMS_TYPE string all
the used Relational Database Management System (RDBMS) type of the front most connection window

Restriction: if front most connection window exists
SP_RDBMS_VERSION string all
the used Relational Database Management System (RDBMS) version string of the front most connection window

Restriction: if front most connection window exists
SP_SELECTED_DATABASE string all
the currently selected database name

Restriction: if front most connection window exists
SP_SELECTED_ROW_INDICES string Data Table
the currently selected row indices of the data table from which the Bundle command was called
SP_SELECTED_TABLE tab-delimited numbers all
the currently selected item (table, view, function, or procedure) name

Restriction: set only if only one item is selected and if front most connection window exists
SP_SELECTED_TABLES tab-delimited string all
the currently selected items (table, view, function, or procedure) names

Restriction: if front most connection window exists
SP_SELECTED_TEXT string Input Field
the currently selected text
SP_SELECTED_TEXT_RANGE string Input Field
the currently absolute selected text range as "{start_index, length}" whereby start_index starts at 0
SP_USED_QUERY_FOR_TABLE string Data Table
the used SQL statement whose result is shown in the data table from which the Bundle command was called



 

HTML Output Window and JavaScript support

The HTML output window displays the output of a script in a full-featured web view browser window supporting the possibility to interact with Sequel Pro via the sequelpro:// URL scheme commands (as hyperlink references) and a special JavaScript object system. Furthermore it can be used as a kind of a monitor by using JavaScripts timer functions.

As default this window will be shown on top of all other windows whereby the the focus will remain at the calling instance. If there is a need to make this HTML window the key window one can do it via the JavaScript command window.system.makeHTMLOutputWindowKeyWindow() – e.g. by calling that function inside the body tag specified in the unload event. The HTML output window allows to open links in new windows, to view the source code (via context menu), to print the HTML page, to save the HTML page, and to use WebKit's WebInspector for debugging.

Each HTML window is linked to its calling Bundle command script i.e. that the result of the same script will displayed in the same HTML window whereby another script will open its own HTML window.

In addition inside the HTML output window the following URL schemes are defined:

sp-reveal-file://an_url

the file with the path an_url will be revealed in Finder

sp-open-file://an_url

the file with the path an_url will be opened with the default application

JavaScript additions

In order to make it possible to interact with Sequel Pro and the underlying UNIX operational system from inside JavaScript an HTML output window implements the JavaScript object system with the following methods:

closeHTMLOutputWindow()

closes the HTML output window
HTML Example
<button onclick='window.system.closeHTMLOutputWindow()'>Close</button>

getShellEnvironmentForName(a_key_name_as_string)

returns the run-time content of the Sequel Pro shell variable a_key_name; depending on the current first responder

insertText(string)

inserts string into the current first responder if it is a input text field

makeHTMLOutputWindowKeyWindow()

make the HTML output window the key window
HTML Example
<body onload="window.system.makeHTMLOutputWindowKeyWindow()">

run(a_command_as_string)

returns the result of the BASH command a_command_as_string which will evaluated in a new shell whose environment will inherit the authentication string SP_PROCESS_ID from the original calling Bundle script to allow to use sequelpro:// URL scheme commands and other shell variables set with the current Sequel Pro state
JS Example
var currentDate = window.system.run('date'); 
var ret = window.system.run("'$SP_BUNDLE_PATH/bin/query.sh' '" + str + "'");

Hint – all shell variables beginning with $SP_* will be evaluated inside the script (here written for BASH) which outputted the HMTL code

setSelectedTextRange(position,length as_string)

sets the selection of the current first responder if it is a input text field; the following are valid ranges: "{2,100}", "0,10", "3"

setText(string)

sets content of the current first responder to string if it is a input text field

Bundle command workflow

The execution of a Bundle command is realized via a temporary file hand-shake mechanism. Each necessary file name will be appended by an Universally Unified Identification (UUID) to make it possible to run more than one Bundle command at the same time. All temporary files will be stored in the "/tmp" folder whose names begin with "SP_"

Firstly the desired Input data will save as UTF-8 encoded file SP_BUNDLE_INPUT_an_uuid.

Then a given Bundle command script will be parsed for the first line to get the shebang notation – e.g. #!/usr/bin/ruby (for Ruby) or #!/usr/bin/env perl -w (for Perl) or #!/usr/bin/osascript (for an AppleScript) etc. If the first line does not begin with #! Sequel Pro interprets the script content as a BASH script. The script content will be save as temporary SP_BUNDLE_SCRIPT_COMMAND_an_uuid file.

A BASH task will be invoked via /bin/bash, preset with available shell variables, and its launching path will be set to the Bundle path to allow to use relative paths inside the scripts. That BASH task starts by itself a task according to the used shebang (/bin/sh for BASH) with the arguments "-c SP_BUNDLE_SCRIPT_COMMAND_an_uuid" and stdin and stdout will be redirected to files via "< SP_BUNDLE_INPUT_an_uuid" and "> SP_BUNDLE_OUTPUT_an_uuid" resp.

Example if no shebang notation was used
/bin/bash -c "/bin/sh 'SP_BUNDLE_SCRIPT_COMMAND_an_uuid' > 'SP_BUNDLE_OUTPUT_an_uuid' < 'SP_BUNDLE_OUTPUT_an_uuid'"
Example if with shebang notation #!/usr/bin/perl -w
/bin/bash -c "/usr/bin/perl -w 'SP_BUNDLE_SCRIPT_COMMAND_an_uuid' > 'SP_BUNDLE_OUTPUT_an_uuid' < 'SP_BUNDLE_OUTPUT_an_uuid'"

After the execution which can be cancelled by pressing ⌘. or using the Activities pane the content of SP_BUNDLE_OUTPUT_an_uuid will be read by Sequel Pro assuming UTF-8 encoded data and the desired Output action will be performed.

Under normal execution conditions all temporary files will be deleted immediately after finishing the Bundle command. If the executed Bundle command script gave an error the content of stderr will be alerted this means that a Bundle command will give an error if something is written to stderr including e.g. "curl" which passes progress information to stderr. In such a case one has to redirect stderr to e.g. /dev/null or to another pipe.

There is possibility for Bundle command scripts to change its Output on run-time. This will be achieved by exiting the script with a specific exit (error) codes. These error codes will be caught by Sequel Pro.

Cancel the execution of Bundle command

A running Bundle command can be cancelled by pressing ⌘. or by using the Activities pane (show/hide it via double-clicking at the header of TABLE INFORMATION pane). If there are running more than one Bundle command ⌘. will cancel always the last started Bundle command successively.

Examples

Input Field

ROT13

The task is to take the entire content of the current selected text input field and replace it by its ROT13representation:

Scope 
Input Field
Input 
Entire Content
Command (BASH) 
tr 'a-zA-Z' 'n-za-mN-ZA-M'
Output 
Replace Entire Content
Explanations
The entire text input field content will be passed to the BASH command. tr will read these data from stdin, performs the ROT13 conversion and outputs the result on stdout. The data on stdout will be read by Sequel Pro and the content of the current text input field will be replaced by it. ROT13 is its own inverse; that is, to undo ROT13, the same algorithm is applied, so the same action can be used for encoding and decoding.

Data Table

Table Data Viewer

This Bundle command is intended to be an example how to visualize data table row(s) user-definable as HTML page. It can be invoked by the key equivalent ⇧+SPACE. Once the HTML page is visible each change of the selected row(s) will update the HTML page.

Download: here

To install it, simply download it, unzip it, and double-click at it in Finder.

It ships with a default viewer which displays the selected row(s) vertically. It will display columns names and their SQL types and the actual data. If the data are stored as BLOBs it tries to display them according the type, i.e. it will display images, play videos (also in full screen mode), sound files, show RTF data, HTML data, PDF data, spatial GEOMETRY data will be shown as PDF images. Furthermore binary data will be displayed as hex dump, zipped data will be shown unzipped, if a field has the tag "<unix_timestamp>" in its comment field it will show the UNIX timestamp in an human readable form, if a field has the tag "<json_data>" in its comment field it will pretty print the JSON data, etc. By defining such comment tags the user can control the output quite easily.

If the chosen table is "ROUTINES" of the database "information_schema" at the host connection "127.0.0.1" (default port) these data will be shown in another layout whereby the field "ROUTINE_DEFINITION" will be syntax highlighted.

As said this is an example which should show what a Bundle can do, you can modify it to fulfill your needs. If someone came up with improvements we would be very appreciated if you are willing to share them.

EXPLAIN Current Query

This bundle takes the current query in the Query Editor or the selection and outputs the result of the SQL statement:

EXPLAIN EXTENDED the_query

as horizontal table in a tooltip whereby the current query or selection will be displayed syntax highlighted.

Download: here

To install it, simply download it, unzip it, and double-click at it in Finder.