Database Report

From b1dc43ce1aa2871753321174239c973a9637a602 Mon Sep 17 00:00:00 2001 From: Bibiko Date: Wed, 5 Jan 2011 12:15:15 +0000 Subject: =?UTF-8?q?=E2=80=A2=20fixed=20bug=20for=20Bundle=20commands=20to?= =?UTF-8?q?=20detect=20if=20current=20textview=20is=20the=20Query=20Editor?= =?UTF-8?q?=20=E2=80=A2=20added=20some=20more=20Default=20Bundles?= MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit --- .../DB Report.spBundle/Support/header.html | 41 +++++ .../DB Report.spBundle/Support/processTableData.pl | 32 ++++ .../DB Report.spBundle/command.plist | 172 +++++++++++++++++++++ 3 files changed, 245 insertions(+) create mode 100644 SharedSupport/Default Bundles/DB Report.spBundle/Support/header.html create mode 100755 SharedSupport/Default Bundles/DB Report.spBundle/Support/processTableData.pl create mode 100644 SharedSupport/Default Bundles/DB Report.spBundle/command.plist (limited to 'SharedSupport/Default Bundles/DB Report.spBundle') 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 @@ + + + Database Report + + + + 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 < +

+ $data[0] + $data[1] + $data[4] + $data[6] + $data[11] + $data[12] + +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 @@ + + + + + author + Hans-Jörg Bibiko + category + Report + command + +# 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>  </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>" + + contact + znvy@ovovxb.qr + description + Display a report about all databases of the current connection including the possibility to select a database or table via hyperlink. + +Version 1.0 + internalKeyEquivalent + + characters + A + keyCode + 0 + modifierFlags + 1835008 + + keyEquivalent + ^~@a + name + Database Report + output + showashtml + scope + general + tooltip + Display report about all databases of the current connection + uuid + AC45C093-9157-4E18-9683-C94415995935 + + -- cgit v1.2.3