diff options
| author | Bibiko <bibiko@eva.mpg.de> | 2011-01-05 12:15:15 +0000 |
|---|---|---|
| committer | Bibiko <bibiko@eva.mpg.de> | 2011-01-05 12:15:15 +0000 |
| commit | b1dc43ce1aa2871753321174239c973a9637a602 (patch) | |
| tree | 2ce3a1e67dc369959484b550d9908fa542cee95c /SharedSupport/Default Bundles/DB Report.spBundle | |
| parent | 2d6c795da8a79126bb25c646f43369df02593f19 (diff) | |
| download | sequelpro-b1dc43ce1aa2871753321174239c973a9637a602.tar.gz sequelpro-b1dc43ce1aa2871753321174239c973a9637a602.tar.bz2 sequelpro-b1dc43ce1aa2871753321174239c973a9637a602.zip | |
• fixed bug for Bundle commands to detect if current textview is the Query Editor
• added some more Default Bundles
Diffstat (limited to 'SharedSupport/Default Bundles/DB Report.spBundle')
3 files changed, 245 insertions, 0 deletions
diff --git a/SharedSupport/Default Bundles/DB Report.spBundle/Support/header.html b/SharedSupport/Default Bundles/DB Report.spBundle/Support/header.html new file mode 100644 index 00000000..b67b633c --- /dev/null +++ b/SharedSupport/Default Bundles/DB Report.spBundle/Support/header.html @@ -0,0 +1,41 @@ +<html> + <head> + <title>Database Report</title> + <style type="text/css"> + hr { + background: #606060; + color: #606060; + border-style: solid; + border-color: #606060; + border-width: 2px 0 0 0; + margin-top: 0; + } + a:hover { + text-decoration: none; + } + a { + text-decoration: none; + } + a:hover:after { + content: " ➪"; + } + .nonVisible { + display:none; + } + </style> + <script type="text/javascript" charset="utf-8"> + function toggle_display(elt) + { + var e; var p; + e = elt.getAttribute("id") + ":data"; + if (window.document.getElementById(e).className != "") { + window.document.getElementById(e).className = ""; + elt.innerHTML = "▼"; + } else { + window.document.getElementById(e).className = "nonVisible"; + elt.innerHTML = "▶"; + } + } + </script> + </head> +<body> diff --git a/SharedSupport/Default Bundles/DB Report.spBundle/Support/processTableData.pl b/SharedSupport/Default Bundles/DB Report.spBundle/Support/processTableData.pl new file mode 100755 index 00000000..7d0b5545 --- /dev/null +++ b/SharedSupport/Default Bundles/DB Report.spBundle/Support/processTableData.pl @@ -0,0 +1,32 @@ +#!/usr/bin/env perl -w + +while(<>) { + + # split tab delimited data + @data = split(/\t/); + + $pid = $ENV{"PID"}; + $db = $ENV{"DB"}; + $res = $ENV{"RES"}; + $itemType = "table"; + + # $data[1] is NULL indicates item is a view + if($data[1] eq "NULL") { + $img = "file://$res/table-view-small-square.tiff"; + $itemType = "view"; + } else { + $img = "file://$res/table-small-square.tiff"; + } + + print <<HTML4; + <tr> + <td align=center width='40px'><img src=\"$img\"></td> + <td><a href=\"sequelpro://$pid\@passToDoc/SelectDatabase/$db/$data[0]/\" title=\"Click to select $itemType “$db.$data[0]”\">$data[0]</a></td> + <td>$data[1]</td> + <td align=right>$data[4]</td> + <td align=right>$data[6]</td> + <td align=right>$data[11]</td> + <td align=right>$data[12]</td> + </tr> +HTML4 +}
\ No newline at end of file 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> |