Usage

Under Development

1 URL scheme sequelpro:// Usage

URL scheme sequelpro:// Usage

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.

remove files SP_QUERY_RESULT_FILE, SP_QUERY_RESULT_STATUS_FILE (optionally SP_QUERY_FILE, SP_QUERY_RESULT_META_FILE)
write the to be passed data into the file SP_QUERY_FILE
call sequelpro:// URL scheme command (via e.g. BASH "open 'sequelpro://$SP_PROCESS_ID@passToDoc/…/…'")
wait until file SP_QUERY_RESULT_STATUS_FILE was written to disk
check the content of the file SP_QUERY_RESULT_STATUS_FILE for success (0) or error (1)
if error remove files SP_QUERY_RESULT_FILE, SP_QUERY_RESULT_STATUS_FILE, SP_QUERY_FILE, SP_QUERY_RESULT_META_FILE (it is actually not necessary since Sequel Pro will do it but it is a good programming bahavior) and process the error
if success Sequel Pro wrote the returned data into the file SP_QUERY_RESULT_FILE which can be processed further and remove files SP_QUERY_RESULT_FILE, SP_QUERY_RESULT_STATUS_FILE, SP_QUERY_FILE, SP_QUERY_RESULT_META_FILE (it is actually not necessary since Sequel Pro will do it but it is a good programming bahavior)

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
Note This command does not need a connection window

sequelpro://SP_PROCESS_ID@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 SQL statements highlighted HTML formatted with <font …> tags
htmlcss: output the SQL statements 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)

Note This command does not need a connection window

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/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/InsertText/a_string

sequelpro://passToDoc/InsertText/a_string (executed on front most connection window)

a_string: insert a_string into the current first responder if the it is a text input field

sequelpro://SP_PROCESS_ID@passToDoc/ReloadContentTable

reload the Content data table

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/ReloadTablesList

reload the tables list

sequelpro://SP_PROCESS_ID@passToDoc/RunQueryInQueryEditor/

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 display the result if any in the data table of the Query Editor
Note: This command will not change the current view, thus please make usage of the scheme command sequelpro://passToDoc/SelectDocumentView/a_view in beforehand

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/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/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/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/SetSelectedTextRange/a_range

sequelpro://passToDoc/SetSelectedTextRange/a_range (executed on front most connection window)

a_string: set the selection of the first responder if the it is a text input field; valid range settings are "{2,3}", "10,0", "100"

sequelpro://SP_PROCESS_ID@passToDoc/SetText/a_string

sequelpro://passToDoc/SetText/a_string (executed on front most connection window)

a_string: set the content of the first responder to a_string if the it is a text input field

Examples

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

Use Sequel Pro to do a syntax highlighting of a given SQL query HTML formatted

BASH script (Author Hans-J. Bibiko):

#!/bin/sh

# set up temp file names

SP_QUERY_FILE="/tmp/SP_QUERY_"

SP_QUERY_RESULT_STATUS_FILE="/tmp/SP_QUERY_RESULT_STATUS_"

SP_QUERY_RESULT_FILE="/tmp/SP_QUERY_RESULT_"

# remove temp files for safety reasons

rm -f $SP_QUERY_RESULT_FILE

rm -f $SP_QUERY_RESULT_STATUS_FILE

# check if Sequel Pro runs

# SPRUNS == 2 -> it runs; SPRUNS == 1 -> it doesn't run

SPRUNS=$(ps -ax | grep 'Sequel Pro' | wc | awk '{print $1;}')

# send query to Sequel Pro

cat <<SQL > "$SP_QUERY_FILE"

SELECT \`TABLE_SCHEMA\` AS \`Name\`, FORMAT((SUM(DATA_LENGTH)+SUM(INDEX_LENGTH))/1000, 1) AS \`Size (kiB) incl. indices\`

FROM \`information_schema\`.\`TABLES\`

GROUP BY \`TABLE_SCHEMA\`

SQL

# do syntax highlighting

open "sequelpro://SyntaxHighlighting/html"

# wait for Sequel Pro; status file will be written to disk if query was finished

while [ 1 ]

[[ -e "$SP_QUERY_RESULT_STATUS_FILE" ]] && break

sleep 0.01

done

# print HTML syntax highlighted query

cat "$SP_QUERY_RESULT_FILE"

# remove temp files

rm -f $SP_QUERY_RESULT_FILE

rm -f $SP_QUERY_RESULT_STATUS_FILE

# quit Sequel Pro if it didn't run at script start

if [ $SPRUNS == 1 ]; then

osascript -e 'tell app "Sequel Pro" to quit'