Documentation

URL Scheme

Sequel Pro listens at a URL scheme sequelpro:// which can be used as hyperlink in the HTML output window or in any Bundle command script via the e.g. BASH command open sequel://… which also be used in the built-in JavaScript object system method run() of the HTML output window. A URL scheme command can send actins to Sequel Pro but also return data which is realised by a file hand-shake mechanism.

In order to prevent misusage of these URL scheme commands an authentication is used. These URL scheme commands are only available from inside a Bundle command including the HTML output window. A Bundle command generates randomly during invocation an Universally Unified Identification (UUID) string which will be passed as shell variable SP_PROCESS_ID and has to be used as authentication string and links a Bundle command to the connection window from which the Bundle command was called. Some uncritical URL scheme commands can be used without a passed SP_PROCESS_ID, then these commands will send to the front most connection window.

All data in used commands are UTF-8 encoded. Since these commands are URLs one has to escape parameters according to RFC 1808 specification.

Communication Workflow with Sequel Pro

The following describes the general workflow to pass data to Sequel Pro and to get possible returned data from Sequel Pro if a command not works with parameters only.

Commands

sequelpro://$SP_PROCESS_ID@chooseItemFromList/item1/item2/…
display a menu with the items item1, item2, etc. to choose one item from that list; Sequel Pro will write the file SP_QUERY_RESULT_STATUS_FILE with the content 0 → success or 1 → error if one item was chosen to disk; SP_QUERY_RESULT_FILE will contain the chosen item or if user dismissed the menu it will contain an empty string
sequelpro://SP_PROCESS_ID@passToDoc/SelectDocumentView/a_view
 
sequelpro://passToDoc/SelectDocumentView/a_view (executed on front most connection window)
structure
select Structure view
content
select Content view
query
select Query view
relation
select Relation view
trigger
select Trigger view
sequelpro://SP_PROCESS_ID@passToDoc/SelectDatabase/a_database_name
 
sequelpro://passToDoc/SelectDatabase/a_database_name (executed on front most connection window)
a_database_name
select the passed database
sequelpro://SP_PROCESS_ID@passToDoc/SelectTable/a_table_name
 
sequelpro://passToDoc/SelectTable/a_table_name (executed on front most connection window)
a_table_name
select the passed item (table, view, function, procedure) in the table list
sequelpro://SP_PROCESS_ID@passToDoc/SelectTables/a_table_name1/a_table_name2/…
 
sequelpro://passToDoc/SelectTables/a_table_name1/a_table_name2/… (executed on front most connection window)
a_table_namex
select the passed items (tables, views, functions, procedures) in the table list
sequelpro://SP_PROCESS_ID@passToDoc/SelectRows/a_row_number1/a_row_number2/…
a_row_numberx
select the given row numbers in the current selected data table (in Content or Query view)
sequelpro://SP_PROCESS_ID@passToDoc/ReloadContentTable
reload the Content data table
sequelpro://SP_PROCESS_ID@passToDoc/ReloadTablesList
reload the tables list
sequelpro://SP_PROCESS_ID@passToDoc/ReloadContentTableWithWHEREClause
apply the WHERE clause which has to be saved in the file whose file name is passed as shell variable SP_QUERY_FILE in beforehand
sequelpro://SP_PROCESS_ID@passToDoc/ExecuteQuery/
execute the SQL statement which has to be saved in the file whose file name is passed as shell variable SP_QUERY_FILE in beforehand and return the a possible result as tab-delimited array string (first line contains the column names) which is available at the file path SP_QUERY_RESULT_FILE; due to variable execution time of SQL statements Sequel Pro will write the file SP_QUERY_RESULT_STATUS_FILE with the content 0 → success or 1 → error if the data were written to disk; furthermore it will write the file SP_QUERY_RESULT_META_FILE containing 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
sequelpro://SP_PROCESS_ID@passToDoc/ExecuteQuery/csv
will write a possible result as CSV formatted text into SP_QUERY_RESULT_FILE
sequelpro://SP_PROCESS_ID@passToDoc/CreateSyntaxForTables/an_item_name1/an_item_name2/…/{format}
write the CREATE SYNTAX of all passed an_item_namex (table, view, function, procedure) as plain text into the file SP_QUERY_RESULT_FILE and write SP_QUERY_RESULT_STATUS_FILE if finished
format (optional)
html
output the CREATE SYNTAX syntax highlighted HTML formatted with <font …> tags
htmlcss
output the CREATE SYNTAX syntax highlighted HTML formatted with <span class="class_name"> tags
(available class_names := sp_sql_keyword, sp_sql_quoted, sp_sql_backtick, sp_sql_numeric, sp_sql_comment, sp_sql_variable)
sequelpro://SP_PROCESS_ID@passToDoc/SyntaxHighlighting/format
do a SQL syntax highlighting of the SQL statement which has to be saved in the file whose file name is passed as shell variable SP_QUERY_FILE in beforehand, write the result into the file SP_QUERY_RESULT_FILE and write SP_QUERY_RESULT_STATUS_FILE if finished based on the following formats
html
output the CREATE SYNTAX syntax highlighted HTML formatted with <font …> tags
htmlcss
output the CREATE SYNTAX syntax highlighted HTML formatted with <span class="class_name"> tags
(available class_names := sp_sql_keyword, sp_sql_quoted, sp_sql_backtick, sp_sql_numeric, sp_sql_comment, sp_sql_variable)

 

Examples

For examples how to use these URL scheme commands please have a look at the examples for the Bundle editor.