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>&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>" 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 isDefaultBundle