tail -f with highlighting

If you want to highlight something when doing ‘tail -f’ you can use the following command:

tail -f /var/log/logfile | perl -p -e 's/(something)/\033[7;1m$1\033[0m/g;'

or if your terminal supports colours, e.g. linux terminal, you can use this:

tail -f /var/log/logfile | perl -p -e 's/(something)/\033[46;1m$1\033[0m/g;'

If you need to highlight multiple words you can use something like this:

tail -f /var/log/logfile | perl -p -e 's/\b(something|something_else)\b/\033[46;1m$1\033[0m/g;'

and if you want it to beep on a match use this:

tail -f /var/log/logfile | perl -p -e 's/(something)/\033[46;1m$1\033[0m\007/g;'

If you find that perl is too heavy for this you can use sed:

tail -f /var/log/logfile | sed "s/\(something\)/\x1b[46;1m\1\x1b[0m/g"

Note, that in the last example you have to actually type “cntl-v cntl-[” in place of “^[”
\x1b character can also be used as the escape character.

For the full list of control characters on Linux you can look at:

man console_codes

Get list of foreign keys in MySQL

Here's a simple query for displaying all foreign keys and their references in a MySQL DB:

select
concat(table_name, '.', column_name) as 'foreign key',
concat(referenced_table_name, '.', referenced_column_name) as 'references'
from
information_schema.key_column_usage
where
referenced_table_name is not null;

Easy visualisation of database schemas

This is easy using SQLFairy, under Ubuntu, as simple as:

sudo apt-get install sqlfairy

Next, dump your database tables, e.g. for MySQL:

mysqldump -u username -p -d mydatabase > mydatabase.sql

Finally, for a PNG image of your schema:

sqlt-graph -f MySQL -o mydatabase.png -t png mydatabase.sql

If your schema lacks explicit foreign keys, try the –natural-join options (man sqlt-graph, man sqlt-diagram)

Here's an example for a SQLite DB:

Get the schema dump:

echo ".schema" | sqlite3 ~/.liferea_1.4/liferea.db >> liferea.sql

Generate a SVG diagram with:

sqlt-graph -c --natural-join --from=SQLite -t svg -o liferea_schema.svg liferea.sql

MySQL Export to CSV

If you need the data from a table or a query in a CSV fiel so that you can open it on any spreadsheet software, like Excel you can use something like the following:

SELECT id, name, email INTO OUTFILE '/tmp/result.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
ESCAPED BY ‘\\’
LINES TERMINATED BY '\n'
FROM users WHERE 1

Or you can use sed:

mysql -u username -ppassword database -B -e "SELECT * FROM table;" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > filename.csv

Explanation:

username is your mysql username
password is your mysql password
database is your mysql database
table is the table you want to export

The -B option will delimit the data using tabs and each row will appear on a new line.
The -e option denotes the MySQL command to run, in our case the "SELECT" statement.
The "sed" command used here contains three sed scripts:

s/\t/","/g;s/^/"/ - this will search and replace all occurences of 'tabs' and replace them with a ",".

s/$/"/; - this will place a " at the start of the line.

s/\n//g - this will place a " at the end of the line.

You can find the exported CSV file in the current directory. The name of the file is filename.csv.

However if there are a lot of tables that you need to export, you'll need a script like this:

#!/bin/bash
#### Begin Configuration ####
DB="mydb"
MYSQL_USER="root"
MYSQL_PASSWD='mypass'
MYSQL_HOST="127.0.0.1"
MYSQL_PORT="3306"
MYSQL="/usr/bin/mysql"
#### End Configuration ####
MYSQL_CMD="$MYSQL -u $MYSQL_USER -p$MYSQL_PASSWD -P $MYSQL_PORT -h $MYSQL_HOST"
TABLES=`$MYSQL_CMD --batch -N -D $DB -e "show tables"`
for TABLE in $TABLES
do
SQL="SELECT * FROM $TABLE;"
OUTFILE=$TABLE.csv
$MYSQL_CMD --database=$DB --execute="$SQL" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > $OUTFILE
done

Just be sure to change the configuration section to meet your needs.
Name the file something like: export_csv.sh and be sure to make it executable. In Linux, do something like:

chmod +x ./export_csv.sh

If you want to have all of the exported files in a certain directory, you could either modify the script or just make the cirectory, "cd" into it, and then run the script. It assumes you want to create the files in the current working directory.
To change that behavior, you could easily modify the "OUTFILE" variable to something like:

OUTFILE="/my_path/$TABLE.csv"

Get Schema from SQLite DB

In SQLite, schemas are stored in the table SQLITE_MASTER. You can easily retrieve it with a command like:

echo ".schema" | sqlite3 ~/.liferea_1.4/liferea.db >> liferea.sql

How to set the timezone on Ubuntu Server

You can check your current timezone by just running

$ date
Mon Sep 3 18:03:04 WEST 2012

Or checking the timezone file with:

$ cat /etc/timezone
Europe/Lisbon

So to change it just run

$ sudo dpkg-reconfigure tzdata
And follow on screen instructions. Easy.

Also be sure to restart cron as it won’t pick up the timezone change and will still be running on UTC.

$ /etc/init.d/cron stop
$ /etc/init.d/cron start

you might also want to install ntp to keep the correct time:

aptitude install ntp

Anti-Spam Email server

In this post I'll show you how to install an anti-spam smart host relay server, based on Ubuntu 12.04 LTS, that will include:

Postfix w/Bayesian Filtering and Anti-Backscatter (Relay Recipients via look-ahead), Apache2, Mysql, Dnsmasq, MailScanner (Spamassassin, ClamAV, Pyzor, Razor, DCC-Client), Baruwa, SPF Checks, FuzzyOcr, Sanesecurity Signatures, PostGrey, KAM, Scamnailer, FireHOL (Iptables Firewall) and Relay Recipients Script.

Continue reading for the instructions.

Changing the Alfresco Site Manage Permissions Action

To change the behavior of the Manage Permissions action to be the same as that available from the Share Repository button.

In:

/opt/alfresco/tomcat/webapps/share/WEB-INF/classes/alfresco/share-documentlibrary-config.xml

Replace:

<!-- Manage permissions (site roles) -->
<action id="document-manage-site-permissions" type="javascript" icon="document-manage-permissions" label="actions.document.manage-permissions">
<param name="function">onActionManagePermissions</param>
<permissions>
<permission allow="true">ChangePermissions</permission>
</permissions>
<evaluator>evaluator.doclib.action.siteBased</evaluator>
</action>

With:

<!-- Manage permissions (site roles) -->
<action id="document-manage-site-permissions" type="pagelink" icon="document-manage-permissions" label="actions.document.manage-permissions">
<param name="page">manage-permissions?nodeRef={node.nodeRef}</param>
<permissions>
<permission allow="true">ChangePermissions</permission>
</permissions>
<evaluator>evaluator.doclib.action.siteBased</evaluator>
</action>

Reload Alfresco and you should now have the more granular permissions from the Alfresco Share repository browser in the Site Document Library browser.

Hide Alfresco Share Repository Browser button

To hide the repository link from non-admin users

open this file:

/opt/alfresco/tomcat/webapps/share/WEB-INF/classes/alfresco/share-config.xml

find this line within the <header> tags

<item type="link" id="repository" condition="conditionRepositoryRootNode">/repository</item>

Change it to:

<item type="link" id="repository" permission="admin" condition="conditionRepositoryRootNode">/repository</item>

Reload Alfresco. Now only admin users will see that link.
Note that the url will still be accessible, this will only hide the link button.