diff options
Diffstat (limited to 'SharedSupport/Default Bundles/DB Report.spBundle/command.plist')
| -rw-r--r-- | SharedSupport/Default Bundles/DB Report.spBundle/command.plist | 172 |
1 files changed, 172 insertions, 0 deletions
diff --git a/SharedSupport/Default Bundles/DB Report.spBundle/command.plist b/SharedSupport/Default Bundles/DB Report.spBundle/command.plist new file mode 100644 index 00000000..03e50adf --- /dev/null +++ b/SharedSupport/Default Bundles/DB Report.spBundle/command.plist @@ -0,0 +1,172 @@ +<?xml version="1.0" encoding="UTF-8"?> +<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd"> +<plist version="1.0"> +<dict> + <key>author</key> + <string>Hans-Jörg Bibiko</string> + <key>category</key> + <string>Report</string> + <key>command</key> + <string> +# Since this command will execute SQL statements at the front most +# document window's connection check for a passed process ID to cancel +# this command by displaying a tooltip. Otherwise the loop after +# “open "sequelpro://$SP_PROCESS_ID@passToDoc/ExecuteQuery"” +# won't break. +if [ -z $SP_PROCESS_ID ]; then + echo "<font color=red>No front most connection window found!</font>" + exit $SP_BUNDLE_EXIT_SHOW_AS_HTML_TOOLTIP +fi + +# 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 + +# execute the SQL statement; the result will be available in the file $SP_QUERY_RESULT_FILE +open "sequelpro://$SP_PROCESS_ID@passToDoc/ExecuteQuery" + +# wait for Sequel Pro; status file will be written to disk if query was finished +while [ 1 ] +do + [[ -e "$SP_QUERY_RESULT_STATUS_FILE" ]] && break + sleep 0.01 +done + +# Prepair HTML code +cat "$SP_BUNDLE_PATH/Support/header.html" +cat <<HTML1 +<center> +<h3><font color=blue>Connection: ‘$SP_CURRENT_HOST’ – $SP_RDBMS_TYPE ($SP_RDBMS_VERSION)</font></h3> +<table width=80%> + <tr> + <td align=center><img width='96px' src='file://$SP_ICON_FILE'></td> + <td> + <table style='border-collapse:collapse'> + <tr> + <th align=left> +HTML1 + +# Check for possible MySQL error +if [ `cat "$SP_QUERY_RESULT_STATUS_FILE"` == 1 ]; then + + # If error + echo "<i>No global summary available</i>" + +else + + # $SP_QUERY_RESULT_FILE contains the file path to the query result + + # First line contains the column names - wrap them into <th> tags + cat "$SP_QUERY_RESULT_FILE" | head -n 1 |perl -pe 's!\t!</th><th></th><th align=right>!g;s!$!</th></tr><tr><td>!' + + # Output all row except the first one and wrap them into <tr><td> tags + cat "$SP_QUERY_RESULT_FILE" | sed '1d' | perl -pe 's!\t!</td><td>&nbsp;&nbsp;</td><td align='right'>!g;s!$!</tr><tr><td>!' + +fi +cat <<HTML2 + </table> + </td> + </tr> +</table> +<hr> +</center> +HTML2 + +# Clear hand-shake files for further usage +rm -f "$SP_QUERY_FILE" +rm -f "$SP_QUERY_RESULT_STATUS_FILE" +rm -f "$SP_QUERY_RESULT_FILE" +rm -f "$SP_QUERY_RESULT_META_FILE" + +# Read all databases into a BASH array; $SP_ALL_DATABASES provides them as a tab delimited string +# Set the Internal Field Separator IFS to 'tab' and process the array by using the new IFS due to +# the fact that a database name could contain 'spaces' +OLDIFS="$IFS" +IFS=" " +dbs=("$SP_ALL_DATABASES") + +# Loop through all databases +cnt=1 +for db in $dbs +do + + cat <<HTML3 + <br> + <table width=100% style='background-color:#ECECEC;'> + <tr> + <td width=20px><span id="$cnt" onclick=toggle_display(this) style="cursor:pointer;color:gray;font-size:smaller;" title="Toggle visibility">▼</span></td> + <td align=center width='40px'><img src='file://$SP_APP_RESOURCES_DIRECTORY/database-small.png'></td> + <td><big><a href='sequelpro://$SP_PROCESS_ID@passToDoc/SelectDatabase/$db' title='Click to select database “$db”'>$db</big></a></td> + </tr> + </table> + <table id="$cnt:data" border=1 style='border-collapse:collapse' width=100%> + <tr> + <th></th><th>Name</th><th>Engine</th><th>Rows</th><th>Size</th><th>Created</th><th>Updated</th> + </tr> +HTML3 + + # Query for table status + echo "SHOW TABLE STATUS IN \`$db\`" > "$SP_QUERY_FILE" + open "sequelpro://$SP_PROCESS_ID@passToDoc/ExecuteQuery" + + # wait for Sequel Pro; status file will be written to disk if query was finished + while [ 1 ] + do + [[ -e "$SP_QUERY_RESULT_STATUS_FILE" ]] && break + sleep 0.01 + done + + cnt=$((cnt+1)) + + export DB="$db" + export PID="$SP_PROCESS_ID" + export RES="$SP_APP_RESOURCES_DIRECTORY"; + cat "$SP_QUERY_RESULT_FILE" | sed '1d' | perl "$SP_BUNDLE_PATH/Support/processTableData.pl" + + echo "</table>" + + # Clear hand-shake files + rm -f "$SP_QUERY_FILE" + rm -f "$SP_QUERY_RESULT_STATUS_FILE" + rm -f "$SP_QUERY_RESULT_FILE" + rm -f "$SP_QUERY_RESULT_META_FILE" + +done + +# Reset IFS +IFS="$OLDIFS" + +echo "</body></html>" +</string> + <key>contact</key> + <string>znvy@ovovxb.qr</string> + <key>description</key> + <string>Display a report about all databases of the current connection including the possibility to select a database or table via hyperlink. + +Version 1.0</string> + <key>internalKeyEquivalent</key> + <dict> + <key>characters</key> + <string>A</string> + <key>keyCode</key> + <integer>0</integer> + <key>modifierFlags</key> + <integer>1835008</integer> + </dict> + <key>keyEquivalent</key> + <string>^~@a</string> + <key>name</key> + <string>Database Report</string> + <key>output</key> + <string>showashtml</string> + <key>scope</key> + <string>general</string> + <key>tooltip</key> + <string>Display report about all databases of the current connection</string> + <key>uuid</key> + <string>AC45C093-9157-4E18-9683-C94415995935</string> +</dict> +</plist> |