Script for opening a mysqlshell by passing it the path to a TYPO3 instance

README.md

Script for opening a mysql shell to TYPO3

This script allows to easily open a mysql shell to the database being used by a TYPO3 instance. Simply install this script in any executable path like $HOME/bin.

Then "cd" to a TYPO3 webroot and there type (possibly using bash shell expansion):

typo3-instance-sqlshell.sh .

The "." (dot) tells the script where to look for the TYPO3 instance. You can of course also point it to some absolute path or use "typo3-instance-sqlshell.sh" in your scripts:

echo "INSERT INTO pages SET title='New page';" | typo3-instance-sqlshell.sh /var/www/some-typo3/htdocs

Installing on MAC OS

If you are on MAC OS you probably need to install some mysql client library "brew install mysql".

Additional scripts

Another nice script which is based on typo3-instance-sqlshell.sh is the "clearCache.sh" script. It allows to clear ALL cached of a TYPO3 instance (4.5 - 8.7) directly from the commandline.

typo3-instance-sqlshell.sh

#!/bin/bash
#
# TYPO3 script for invoking a mysql usage
# Version: 0.4 - http://webconsulting.at/typo3_instance-sqlshell.sh.txt
# Copyright (c) 2014-2017 Bernhard Kraft <kraft@webconsulting.at>
#
# based on:
# Version: 0.9 - http://www.abaton.at/support/typo3/typo3-update-script/
# Copyright (c) 2011 Christoph Jaeger <jaeger@abaton.at>
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 2 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
#=====================================================================

TYPO3_LOCALCONF="INSTANCEDIR/typo3conf/localconf.php"
TYPO3_LOCALCONF_NEW="INSTANCEDIR/typo3conf/LocalConfiguration.php"

# 1. Are we on MAC OS?
OS="linux"
if [ `echo $OSTYPE | grep -c darwin` -gt 0 ]; then
	OS="darwin"
fi

if [ $OS == "darwin" ]; then
	# Try if MAMP is installed.
	# In this case "mysql" won't be in an executable PATH
	# but still should be the mysql binary being used.
	MYSQL_COMMAND="/Applications/MAMP/Library/bin/mysql"
fi

if [ ! -x "$MYSQL_COMMAND" ]; then
	MYSQL_COMMAND=`which mysql 2> /dev/null`
fi
if [ -z "$MYSQL_COMMAND" ]; then
	MYSQL_COMMAND="/usr/bin/mysql"
fi
if [ ! -x "$MYSQL_COMMAND" ]; then
	echo "No mysql shell available!"
	exit
fi

#=====================================================================
# Options documentation
#=====================================================================

# Since version 8 it is possible to have different database
# connections defined in LocalConfiguration.php. This option
# configures which connection is to be used.
# Feature-TODO: If set to "*choose*" a dialog should get displayed
# if there is more than one connection which can get used.
USE_CONNECTION="Default"
#
#=====================================================================

#=====================================================================
# Change Log
#=====================================================================
#
# VER 0.4 - (2017-08-22)
#           Support for TYPO3 version 8.x
#           Support for MAC OS
#
# VER 0.3 - (2016-01-01)
#           Support the "socket" configuration option.
#				Invoking a mysql shell with a server not running on the
#				default mysql unix socket works properly now.
#
# VER 0.2 - (2015-08-12)
#           Improved replacement of INSTANCEDIR placeholder.
#				Calling with absolute path works properly now.
#
# VER 0.1 - (2014-04-03)
#           Initial release
#=====================================================================


if [ -z "$1" ]
then
  printf "Usage: `basename $0` [pathToTYPO3-Instance]\n\n"
  exit 1
fi

if [ ! -d "$1" ]
then
  printf "Usage: `basename $0` [pathToTYPO3-Instance]\n\n"
  printf "pathToTYPO3-Instance must be a directory\n\n"
  exit 1
fi

INSTANCEDIR="$1"

TYPO3_LOCALCONF=${TYPO3_LOCALCONF/INSTANCEDIR/$INSTANCEDIR}
TYPO3_LOCALCONF_NEW=${TYPO3_LOCALCONF_NEW/INSTANCEDIR/$INSTANCEDIR}

typo3_check_basics() {
	# check if mysqldump command exists
	if [ ! -x $MYSQL_COMMAND ]; then
	  printf "ERROR: $MYSQL_COMMAND does not exist or is not executeable\n\n"
	  exit 1
	fi

	if [ ! -f "$TYPO3_LOCALCONF" ] && [ ! -f "$TYPO3_LOCALCONF_NEW" ]  ; then
	  printf "ERROR: TYPO3 config file $TYPO3_LOCALCONF or $TYPO3_LOCALCONF_NEW does not exist\n\n"
	  exit 1
	fi
}

typo3_invoke_mysql() {
	# check if database exists
	if [ -n "$TYPO3_DB_SOCKET" ]; then
		$MYSQL_COMMAND -S $TYPO3_DB_SOCKET -u $TYPO3_DB_USERNAME --password="$TYPO3_DB_PASSWORD" -e "show tables;" $TYPO3_DATABASE > /dev/null 2>&1
	else
		$MYSQL_COMMAND -h $TYPO3_DB_HOST -u $TYPO3_DB_USERNAME --password="$TYPO3_DB_PASSWORD" -e "show tables;" $TYPO3_DATABASE > /dev/null 2>&1
	fi
	RETVAL=$?
	if [ $RETVAL -eq 0 ]; then
		if [ -n "$TYPO3_DB_SOCKET" ]; then
			$MYSQL_COMMAND -S $TYPO3_DB_SOCKET -u $TYPO3_DB_USERNAME --password="$TYPO3_DB_PASSWORD" $TYPO3_DATABASE
		else
			$MYSQL_COMMAND -h $TYPO3_DB_HOST -u $TYPO3_DB_USERNAME --password="$TYPO3_DB_PASSWORD" $TYPO3_DATABASE
		fi
	else
		printf "ERROR: Database $TYPO3_DATABASE does not exist\n\n"
		exit 1
	fi
}

typo3_get_credentials() {

	# get database name
	TYPO3_DATABASE=`grep -i '^$typo_db =' ${USE_LOCALCONF} | cut -d ";" -f 1 | cut -d "'" -f 2 | tail -1`

	# get database user
	TYPO3_DB_USERNAME=`grep -i '^$typo_db_username =' ${USE_LOCALCONF} | cut -d ";" -f 1 | cut -d "'" -f 2 | tail -1`

	# get database password
	TYPO3_DB_PASSWORD=`grep -i '^$typo_db_password =' ${USE_LOCALCONF} | cut -d ";" -f 1 | cut -d "'" -f 2 | tail -1`

	# get database host
	TYPO3_DB_HOST=`grep -i '^$typo_db_host =' ${USE_LOCALCONF} | cut -d ";" -f 1 | cut -d "'" -f 2 | tail -1 | cut -d ":" -f 1`

	# get database socket
	TYPO3_DB_SOCKET=`grep -i '^$typo_db_socket =' ${USE_LOCALCONF} | cut -d ";" -f 1 | cut -d "'" -f 2 | tail -1 | cut -d ":" -f 1`

}

typo3_get_credentials_new() {

	# get database name
	TYPO3_DATABASE=`grep -i "'database' =>" ${USE_LOCALCONF} | cut -d ">" -f 2 | cut -d "'" -f 2 | tail -1`

	# get database user
	TYPO3_DB_USERNAME=`grep -i "'username' =>" ${USE_LOCALCONF} | cut -d ">" -f 2 | cut -d "'" -f 2 | tail -1`

	# get database password
	TYPO3_DB_PASSWORD=`grep -i "'password' =>" ${USE_LOCALCONF} | cut -d ">" -f 2 | cut -d "'" -f 2 | tail -1`

	# get database host
	TYPO3_DB_HOST=`grep -i "'host' =>" ${USE_LOCALCONF} | cut -d ">" -f 2 | cut -d "'" -f 2 | tail -1`

	# get database socket
	TYPO3_DB_SOCKET=`grep -i "'socket' =>" ${USE_LOCALCONF} | cut -d ">" -f 2 | cut -d "'" -f 2 | tail -1`

}
 
typo3_get_credentials_v8() {

	CONFIG=`cat "$USE_LOCALCONF" | grep -B 0 -A 200 "'Connections'" | tail -n +2 | grep -B 0 -A 10 "'$USE_CONNECTION'" | tail -n +2`

	# get database name
	TYPO3_DATABASE=`echo "$CONFIG" | grep -i "'dbname' =>" | cut -d ">" -f 2 | cut -d "'" -f 2 | tail -1`

	# get database user
	TYPO3_DB_USERNAME=`echo "$CONFIG" | grep -i "'user' =>" | cut -d ">" -f 2 | cut -d "'" -f 2 | tail -1`

	# get database password
	TYPO3_DB_PASSWORD=`echo "$CONFIG" | grep -i "'password' =>" | cut -d ">" -f 2 | cut -d "'" -f 2 | tail -1`

	# get database host
	TYPO3_DB_HOST=`echo "$CONFIG" | grep -i "'host' =>" | cut -d ">" -f 2 | cut -d "'" -f 2 | tail -1`

	# get database socket
	TYPO3_DB_SOCKET=`echo "$CONFIG" | grep -i "'unix_socket' =>" | cut -d ">" -f 2 | cut -d "'" -f 2 | tail -1`

}

determine_version_v8() {

	NEW_CONF=`cat "$USE_LOCALCONF" | grep -B 0 -A 200 "'Connections'" | tail -n +2 | grep -B 0 -A 0 "'$USE_CONNECTION'" | wc -c`

	if [ $NEW_CONF -ne 0 ]; then
		VERSION="8-or-later"
	else
		VERSION="6-or-7"
	fi

}
 
typo3_check_basics

if [ -f "$TYPO3_LOCALCONF" ]; then
	USE_LOCALCONF="$TYPO3_LOCALCONF"
	typo3_get_credentials
fi

if [ -f "$TYPO3_LOCALCONF_NEW" ]; then
	USE_LOCALCONF="$TYPO3_LOCALCONF_NEW"
	determine_version_v8

	if [ "$VERSION" == "8-or-later" ]; then
		typo3_get_credentials_v8
	else
		typo3_get_credentials_new
	fi
fi

typo3_invoke_mysql