#!/bin/sh
#----------------------------------------------------------------------------
# /var/install/bin/apache2-config-modules-phpmyadmin-common-tools-pma-db
#
# Creation:     2007-01-22 starwarsfan
# Last Update:  $Id$
#
# Copyright (c) 2007-2009 the eisfair team, team(at)eisfair(dot)org
# Copyright (c) 2015-@@YEAR@@ Holger Bruenjes, holgerbruenjes(at)gmx(dot)net
#
# 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.
#----------------------------------------------------------------------------

# include eislib
. /var/install/include/eislib

#debug=true

if ${debug:-false}
then
    exec 2>/tmp/$(basename ${0})-trace$$.log
    set -x
    ask_debug=true
    export ask_debug
fi

# ---------------------------------------------------------------------------
# Create Header for the SQL-Script
# ---------------------------------------------------------------------------
createSQLScriptHeader()
{
givenHost=$1
givenDBName=$2
givenControluser=$3

cat >${tmpSQLScript} <<EOF
-- --------------------------------------------------------
-- SQL Commands to set up the pmadb as described in Documentation.html.
--
-- Modified for usage in the eisfair package
--
-- This file is meant for use with MySQL 5 and above!
--
-- This script expects the user pma to already be existing. If we would put a
-- line here to create him too many users might just use this script and end
-- up with having the same password for the controluser.
--
-- This user "pma" must be defined in config.inc.php (controluser/controlpass)
--
-- Please don't forget to set up the tablenames in config.inc.php
--
-- Original-Id: create_tables.sql 10684 2007-09-30 12:15:08Z lem9
-- based on:
-- $Id$

-- --------------------------------------------------------

EOF

}


# ---------------------------------------------------------------------------
# Create scriptpart to drop pmaDB first
# ---------------------------------------------------------------------------
createSQLScriptDropDB()
{
givenHost=$1
givenDBName=$2
givenControluser=$3

cat >>${tmpSQLScript} <<EOF

--
-- Database : \`${givenDBName}\`
--
DROP DATABASE IF EXISTS \`${givenDBName}\`;
CREATE DATABASE \`${givenDBName}\`
  DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;
USE ${givenDBName};

-- --------------------------------------------------------

EOF

}


# ---------------------------------------------------------------------------
# Create scriptpart to create pmaDB
# ---------------------------------------------------------------------------
createSQLScriptCreateDB()
{
givenHost=$1
givenDBName=$2
givenControluser=$3

cat >>${tmpSQLScript} <<EOF

--
-- Database : \`${givenDBName}\`
--
CREATE DATABASE IF NOT EXISTS \`${givenDBName}\`
  DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;
USE ${givenDBName};

-- --------------------------------------------------------

EOF

}


# ---------------------------------------------------------------------------
# Create scriptpart to grant privileges
# ---------------------------------------------------------------------------
createSQLScriptGrantPrivileges()
{
givenHost=$1
givenDBName=$2
givenControluser=$3

cat >>${tmpSQLScript} <<EOF
--
-- Privileges
--
GRANT SELECT, INSERT, DELETE, UPDATE, ALTER ON \`${givenDBName}\`.* TO
   '${givenControluser}'@'${givenHost}';

-- --------------------------------------------------------


EOF

}



# ---------------------------------------------------------------------------
# Create scriptpart to create pmaDB tables
# ---------------------------------------------------------------------------
createSQLScriptCreateTables()
{
givenHost=$1
givenDBName=$2
givenControluser=$3

cat >>${tmpSQLScript} <<EOF
--
-- Table structure for table \`pma_bookmark\`
--

CREATE TABLE IF NOT EXISTS \`pma_bookmark\` (
  \`id\` int(11) NOT NULL auto_increment,
  \`dbase\` varchar(255) NOT NULL default '',
  \`user\` varchar(255) NOT NULL default '',
  \`label\` varchar(255) COLLATE utf8_general_ci NOT NULL default '',
  \`query\` text NOT NULL,
  PRIMARY KEY  (\`id\`)
)
  COMMENT='Bookmarks'
  DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table \`pma_column_info\`
--

CREATE TABLE IF NOT EXISTS \`pma_column_info\` (
  \`id\` int(5) unsigned NOT NULL auto_increment,
  \`db_name\` varchar(64) NOT NULL default '',
  \`table_name\` varchar(64) NOT NULL default '',
  \`column_name\` varchar(64) NOT NULL default '',
  \`comment\` varchar(255) COLLATE utf8_general_ci NOT NULL default '',
  \`mimetype\` varchar(255) COLLATE utf8_general_ci NOT NULL default '',
  \`transformation\` varchar(255) NOT NULL default '',
  \`transformation_options\` varchar(255) NOT NULL default '',
  \`input_transformation\` varchar(255) NOT NULL default '',
  \`input_transformation_options\` varchar(255) NOT NULL default '',
  PRIMARY KEY  (\`id\`),
  UNIQUE KEY \`db_name\` (\`db_name\`,\`table_name\`,\`column_name\`)
)
  COMMENT='Column information for phpMyAdmin'
  DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table \`pma_history\`
--

CREATE TABLE IF NOT EXISTS \`pma_history\` (
  \`id\` bigint(20) unsigned NOT NULL auto_increment,
  \`username\` varchar(64) NOT NULL default '',
  \`db\` varchar(64) NOT NULL default '',
  \`table\` varchar(64) NOT NULL default '',
  \`timevalue\` timestamp NOT NULL,
  \`sqlquery\` text NOT NULL,
  PRIMARY KEY  (\`id\`),
  KEY \`username\` (\`username\`,\`db\`,\`table\`,\`timevalue\`)
)
  COMMENT='SQL history for phpMyAdmin'
  DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table \`pma_pdf_pages\`
--

CREATE TABLE IF NOT EXISTS \`pma_pdf_pages\` (
  \`db_name\` varchar(64) NOT NULL default '',
  \`page_nr\` int(10) unsigned NOT NULL auto_increment,
  \`page_descr\` varchar(50) COLLATE utf8_general_ci NOT NULL default '',
  PRIMARY KEY  (\`page_nr\`),
  KEY \`db_name\` (\`db_name\`)
)
  COMMENT='PDF relation pages for phpMyAdmin'
  DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;


-- --------------------------------------------------------

--
-- Table structure for table \`pma_recent\`
--

CREATE TABLE IF NOT EXISTS \`pma_recent\` (
  \`username\` varchar(64) NOT NULL,
  \`tables\` text NOT NULL,
  PRIMARY KEY (\`username\`)
)
  COMMENT='Recently accessed tables'
  DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table \`pma_favorite\`
--

CREATE TABLE IF NOT EXISTS \`pma_favorite\` (
  \`username\` varchar(64) NOT NULL,
  \`tables\` text NOT NULL,
  PRIMARY KEY (\`username\`)
)
  COMMENT='Favorite tables'
  DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table \`pma_table_uiprefs\`
--

CREATE TABLE IF NOT EXISTS \`pma_table_uiprefs\` (
  \`username\` varchar(64) NOT NULL,
  \`db_name\` varchar(64) NOT NULL,
  \`table_name\` varchar(64) NOT NULL,
  \`prefs\` text NOT NULL,
  \`last_update\` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (\`username\`,\`db_name\`,\`table_name\`)
)
  COMMENT='Tables'' UI preferences'
  DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table \`pma_relation\`
--

CREATE TABLE IF NOT EXISTS \`pma_relation\` (
  \`master_db\` varchar(64) NOT NULL default '',
  \`master_table\` varchar(64) NOT NULL default '',
  \`master_field\` varchar(64) NOT NULL default '',
  \`foreign_db\` varchar(64) NOT NULL default '',
  \`foreign_table\` varchar(64) NOT NULL default '',
  \`foreign_field\` varchar(64) NOT NULL default '',
  PRIMARY KEY  (\`master_db\`,\`master_table\`,\`master_field\`),
  KEY \`foreign_field\` (\`foreign_db\`,\`foreign_table\`)
)
  COMMENT='Relation table'
  DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table \`pma_table_coords\`
--

CREATE TABLE IF NOT EXISTS \`pma_table_coords\` (
  \`db_name\` varchar(64) NOT NULL default '',
  \`table_name\` varchar(64) NOT NULL default '',
  \`pdf_page_number\` int(11) NOT NULL default '0',
  \`x\` float unsigned NOT NULL default '0',
  \`y\` float unsigned NOT NULL default '0',
  PRIMARY KEY  (\`db_name\`,\`table_name\`,\`pdf_page_number\`)
)
  COMMENT='Table coordinates for phpMyAdmin PDF output'
  DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table \`pma_table_info\`
--

CREATE TABLE IF NOT EXISTS \`pma_table_info\` (
  \`db_name\` varchar(64) NOT NULL default '',
  \`table_name\` varchar(64) NOT NULL default '',
  \`display_field\` varchar(64) NOT NULL default '',
  PRIMARY KEY  (\`db_name\`,\`table_name\`)
)
  COMMENT='Table information for phpMyAdmin'
  DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table \`pma_designer_coords\`
--

CREATE TABLE IF NOT EXISTS \`pma_designer_coords\` (
  \`db_name\` varchar(64) NOT NULL default '',
  \`table_name\` varchar(64) NOT NULL default '',
  \`x\` INT,
  \`y\` INT,
  \`v\` TINYINT,
  \`h\` TINYINT,
  PRIMARY KEY (\`db_name\`,\`table_name\`)
)
  COMMENT='Table coordinates for Designer'
  DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table \`pma_tracking\`
--

CREATE TABLE IF NOT EXISTS \`pma_tracking\` (
  \`db_name\` varchar(64) NOT NULL,
  \`table_name\` varchar(64) NOT NULL,
  \`version\` int(10) unsigned NOT NULL,
  \`date_created\` datetime NOT NULL,
  \`date_updated\` datetime NOT NULL,
  \`schema_snapshot\` text NOT NULL,
  \`schema_sql\` text,
  \`data_sql\` longtext,
  \`tracking\` set('UPDATE','REPLACE','INSERT','DELETE','TRUNCATE','CREATE DATABASE','ALTER DATABASE','DROP DATABASE','CREATE TABLE','ALTER TABLE','RENAME TABLE','DROP TABLE','CREATE INDEX','DROP INDEX','CREATE VIEW','ALTER VIEW','DROP VIEW') default NULL,
  \`tracking_active\` int(1) unsigned NOT NULL default '1',
  PRIMARY KEY  (\`db_name\`,\`table_name\`,\`version\`)
)
  COMMENT='Database changes tracking for phpMyAdmin'
  DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table \`pma_userconfig\`
--

CREATE TABLE IF NOT EXISTS \`pma_userconfig\` (
  \`username\` varchar(64) NOT NULL,
  \`timevalue\` timestamp NOT NULL,
  \`config_data\` text NOT NULL,
  PRIMARY KEY  (\`username\`)
)
  COMMENT='User preferences storage for phpMyAdmin'
  DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;


-- --------------------------------------------------------

--
-- Table structure for table \`pma_users\`
--

CREATE TABLE IF NOT EXISTS \`pma_users\` (
  \`username\` varchar(64) NOT NULL,
  \`usergroup\` varchar(64) NOT NULL,
  PRIMARY KEY (\`username\`,\`usergroup\`)
)
  COMMENT='Users and their assignments to user groups'
  DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table \`pma_usergroups\`
--

CREATE TABLE IF NOT EXISTS \`pma_usergroups\` (
  \`usergroup\` varchar(64) NOT NULL,
  \`tab\` varchar(64) NOT NULL,
  \`allowed\` enum('Y','N') NOT NULL DEFAULT 'N',
  PRIMARY KEY (\`usergroup\`,\`tab\`,\`allowed\`)
)
  COMMENT='User groups with configured menu items'
  DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table \`pma_navigationhiding\`
--

CREATE TABLE IF NOT EXISTS \`pma_navigationhiding\` (
  \`username\` varchar(64) NOT NULL,
  \`item_name\` varchar(64) NOT NULL,
  \`item_type\` varchar(64) NOT NULL,
  \`db_name\` varchar(64) NOT NULL,
  \`table_name\` varchar(64) NOT NULL,
  PRIMARY KEY (\`username\`,\`item_name\`,\`item_type\`,\`db_name\`,\`table_name\`)
)
  COMMENT='Hidden items of navigation tree'
  DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table \`pma_savedsearches\`
--

CREATE TABLE IF NOT EXISTS \`pma_savedsearches\` (
  \`id\` int(5) unsigned NOT NULL auto_increment,
  \`username\` varchar(64) NOT NULL default '',
  \`db_name\` varchar(64) NOT NULL default '',
  \`search_name\` varchar(64) NOT NULL default '',
  \`search_data\` text NOT NULL,
  PRIMARY KEY  (\`id\`),
  UNIQUE KEY \`u_savedsearches_username_dbname\` (\`username\`,\`db_name\`,\`search_name\`)
)
  COMMENT='Saved searches'
  DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table \`pma_central_columns\`
--

CREATE TABLE IF NOT EXISTS \`pma_central_columns\` (
  \`db_name\` varchar(64) NOT NULL,
  \`col_name\` varchar(64) NOT NULL,
  \`col_type\` varchar(64) NOT NULL,
  \`col_length\` text,
  \`col_collation\` varchar(64) NOT NULL,
  \`col_isNull\` boolean NOT NULL,
  \`col_extra\` varchar(255) default '',
  \`col_default\` text,
  PRIMARY KEY (\`db_name\`,\`col_name\`)
)
  COMMENT='Central list of columns'
  DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table \`pma_designer_settings\`
--

CREATE TABLE IF NOT EXISTS \`pma_designer_settings\` (
  \`username\` varchar(64) NOT NULL,
  \`settings_data\` text NOT NULL,
  PRIMARY KEY (\`username\`)
)
  COMMENT='Settings related to Designer'
  DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table \`pma_export_templates\`
--

CREATE TABLE IF NOT EXISTS \`pma_export_templates\` (
  \`id\` int(5) unsigned NOT NULL AUTO_INCREMENT,
  \`username\` varchar(64) NOT NULL,
  \`export_type\` varchar(10) NOT NULL,
  \`template_name\` varchar(64) NOT NULL,
  \`template_data\` text NOT NULL,
  PRIMARY KEY (\`id\`),
  UNIQUE KEY \`u_user_type_template\` (\`username\`,\`export_type\`,\`template_name\`)
)
  COMMENT='Saved export templates'
  DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

EOF

}



# ---------------------------------------------------------------------------
# Create Header for the SQL-Script
# ---------------------------------------------------------------------------
createSQLScriptAlterDB()
{
givenHost=$1
givenDBName=$2
givenControluser=$3

cat >${tmpSQLScript} <<EOF
-- -------------------------------------------------------------
-- SQL Commands to upgrade pmadb for normal phpMyAdmin operation
-- with MySQL 4.1.2 and above.
--
-- Modified for usage in the eisfair package
--
-- This file is meant for use with MySQL 4.1.2 and above!
-- For older MySQL releases, please use create_tables.sql
--
-- If you are running one MySQL 4.1.0 or 4.1.1, please create the tables using
-- create_tables.sql, then use this script.
--
-- Please don't forget to set up the tablenames in config.inc.php
--
-- Original-Id: upgrade_tables_mysql_4_1_2+.sql 10212 2007-03-27 13:53:14Z cybot_tm
-- based on:
-- $Id$

-- --------------------------------------------------------

--
-- Database : \`${givenDBName}\`
--
ALTER DATABASE \`${givenDBName}\`
  DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;
USE ${givenDBName};

-- --------------------------------------------------------

--
-- Table structure for table \`pma_bookmark\`
--
ALTER TABLE \`pma_bookmark\`
  DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

ALTER TABLE \`pma_bookmark\`
  CHANGE \`dbase\` \`dbase\` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '';
ALTER TABLE \`pma_bookmark\`
  CHANGE \`user\` \`user\` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '';
ALTER TABLE \`pma_bookmark\`
  CHANGE \`label\` \`label\` VARCHAR( 255 )  CHARACTER SET utf8 utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '';
ALTER TABLE \`pma_bookmark\`
  CHANGE \`query\` \`query\` TEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL;

-- --------------------------------------------------------

--
-- Table structure for table \`pma_column_info\`
--

ALTER TABLE \`pma_column_info\`
  DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

ALTER TABLE \`pma_column_info\`
  CHANGE \`db_name\` \`db_name\` VARCHAR(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '';
ALTER TABLE \`pma_column_info\`
  CHANGE \`table_name\` \`table_name\` VARCHAR(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '';
ALTER TABLE \`pma_column_info\`
  CHANGE \`column_name\` \`column_name\` VARCHAR(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '';
ALTER TABLE \`pma_column_info\`
  CHANGE \`comment\` \`comment\` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '';
ALTER TABLE \`pma_column_info\`
  CHANGE \`mimetype\` \`mimetype\` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '';
ALTER TABLE \`pma_column_info\`
  CHANGE \`transformation\` \`transformation\` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '';
ALTER TABLE \`pma_column_info\`
  CHANGE \`transformation_options\` \`transformation_options\` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '';

-- --------------------------------------------------------

--
-- Table structure for table \`pma_history\`
--
ALTER TABLE \`pma_history\`
  DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

ALTER TABLE \`pma_history\`
  CHANGE \`username\` \`username\` VARCHAR(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '';
ALTER TABLE \`pma_history\`
  CHANGE \`db\` \`db\` VARCHAR(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '';
ALTER TABLE \`pma_history\`
  CHANGE \`table\` \`table\` VARCHAR(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '';
ALTER TABLE \`pma_history\`
  CHANGE \`sqlquery\` \`sqlquery\` TEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL;

-- --------------------------------------------------------

--
-- Table structure for table \`pma_pdf_pages\`
--

ALTER TABLE \`pma_pdf_pages\`
  DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

ALTER TABLE \`pma_pdf_pages\`
  CHANGE \`db_name\` \`db_name\` VARCHAR(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '';
ALTER TABLE \`pma_pdf_pages\`
  CHANGE \`page_descr\` \`page_descr\` VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL default '';

-- --------------------------------------------------------

--
-- Table structure for table \`pma_relation\`
--
ALTER TABLE \`pma_relation\`
  DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

ALTER TABLE \`pma_relation\`
  CHANGE \`master_db\` \`master_db\` VARCHAR(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '';
ALTER TABLE \`pma_relation\`
  CHANGE \`master_table\` \`master_table\` VARCHAR(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '';
ALTER TABLE \`pma_relation\`
  CHANGE \`master_field\` \`master_field\` VARCHAR(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '';
ALTER TABLE \`pma_relation\`
  CHANGE \`foreign_db\` \`foreign_db\` VARCHAR(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '';
ALTER TABLE \`pma_relation\`
  CHANGE \`foreign_table\` \`foreign_table\` VARCHAR(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '';
ALTER TABLE \`pma_relation\`
  CHANGE \`foreign_field\` \`foreign_field\` VARCHAR(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '';

-- --------------------------------------------------------

--
-- Table structure for table \`pma_table_coords\`
--

ALTER TABLE \`pma_table_coords\`
  DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

ALTER TABLE \`pma_table_coords\`
  CHANGE \`db_name\` \`db_name\` VARCHAR(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '';
ALTER TABLE \`pma_table_coords\`
  CHANGE \`table_name\` \`table_name\` VARCHAR(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '';

-- --------------------------------------------------------

--
-- Table structure for table \`pma_table_info\`
--

ALTER TABLE \`pma_table_info\`
  DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

ALTER TABLE \`pma_table_info\`
  CHANGE \`db_name\` \`db_name\` VARCHAR(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '';
ALTER TABLE \`pma_table_info\`
  CHANGE \`table_name\` \`table_name\` VARCHAR(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '';
ALTER TABLE \`pma_table_info\`
  CHANGE \`display_field\` \`display_field\` VARCHAR(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '';

-- --------------------------------------------------------

--
-- Table structure for table \`pma_designer_coords\`
--

CREATE TABLE IF NOT EXISTS \`pma_designer_coords\` (
  \`db_name\` varchar(64) NOT NULL default '',
  \`table_name\` varchar(64) NOT NULL default '',
  \`x\` INT,
  \`y\` INT,
  \`v\` TINYINT,
  \`h\` TINYINT,
  PRIMARY KEY (\`db_name\`,\`table_name\`)
)
  COMMENT='Table coordinates for Designer'
  DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;


EOF

}





# ---------------------------------------------------------------------------
# create the sql script and execute it
# ---------------------------------------------------------------------------
db_operation()
{
    _server=${1}

    # check if entered server is active
    eval active='${'${project_name}'_SERVER_'${_server}'_ACTIVE}'

    if [ "${active}" = "yes" ]
    then
        # check if advanced features are activated
        eval pmadb_use='${'${project_name}'_SERVER_'${_server}'_PMADB_USE}'

        if [ "${pmadb_use}" = "yes" ]
        then
            eval host='${'${project_name}'_SERVER_'${_server}'_HOST}'
            eval port='${'${project_name}'_SERVER_'${_server}'_PORT}'
            eval pmadb_name='${'${project_name}'_SERVER_'${_server}'_PMADB_NAME}'
            eval controluser='${'${project_name}'_SERVER_'${_server}'_CONTROLUSER}'
            eval controlpass='${'${project_name}'_SERVER_'${_server}'_CONTROLPASS}'

            if [ -z "${port}" ]
            then
                port=3306
            fi

            case "${host}" in
            localhost|127.0.0.1)

                get_defaults
                defaults_ret=${?}

                if [ ${defaults_ret:-1} -eq 0 ]
                then
                    check_status localhost
                    database_option="--socket=${mysql_socket}"
               else
                    echo
                    mecho --error "MariaDB/MySQL Server is not running on localhost."
                    echo
                    anykey
                    select_server
                fi
                ;;
             *)
                check_status externhost
                database_option="--host=${host} --port=${port}"
                get_admin_and_passwd
                ;;
            esac

            foundPMADB=$(${database_exec} \
                        ${database_option} \
                        -u ${admin_user} ${admin_pass} \
                        -e "SHOW DATABASES" |
                        grep -c "^${pmadb_name}$")
            clrhome
            echo
            mecho " host:  '${host}'"
            mecho " port:  '${port}'"
            mecho " pmadb: '${pmadb_name}'"
            mecho " found: '${foundPMADB}'"

            tmpSQLScript=$(${MKTEMP} -t .XXXXXXXXXXXXX.sql)

            if [ ${foundPMADB} -eq 1 ]
            then
                # pmadb exists, ask for next steps
                mecho
                mecho -n "Database '"
                mecho -n --info "${pmadb_name}"
                mecho "' exists!"
                mecho
                techo --begin '3 2 2 33'
                techo --row '' '1' '-' 'Remove database and create New'
                techo --row '' '2' '-' 'Alter existing database'
                techo --row '' '3' '-' 'Drop database'
                techo --row '' '4' '-' 'Update tables'
                techo --end
                echo

                _ask_tmpfile=$(${MKTEMP} -t .XXXXXXXXXXXXX)
                ${ASK} "Select" "" "1-4" "^$=Return" "0=Exit" >${_ask_tmpfile}
                rc=${?}
                read option < ${_ask_tmpfile}
                ${RM} -f ${_ask_tmpfile}

                # if ask break, ask returned 255
                if [ ${rc} = 255 ]
                then
                    check_executable
                    exit 127
                fi

                case ${option} in
                '') select_server   ;;
                0)
                    check_executable
                    exit 127
                    ;;
                1)
                     # drop db and create new
                    mecho
                    mecho -n "Creating SQL script for server '"
                    mecho -n --info "${host}"
                    mecho "' ... "
                    createSQLScriptHeader ${host} ${pmadb_name} ${controluser}
                    createSQLScriptDropDB ${host} ${pmadb_name} ${controluser}
               #    createSQLScriptGrantPrivileges ${host} ${pmadb_name} ${controluser}
                    createSQLScriptCreateTables ${host} ${pmadb_name} ${controluser}
                    print_result ${?}
                    mecho -n "Executing SQL script for server '"
                    mecho -n --info "${host}"
                    mecho "' ... "
                    ${database_exec} \
                           ${database_option} \
                           -u ${admin_user} ${admin_pass} < ${tmpSQLScript}
                    print_result ${?}
                    mecho 'Removing SQL script ...'
                    ${RM} -f ${tmpSQLScript}
                    print_result ${?}
                    ;;
                2)
                   # alter existing pma database
                    mecho
                    mecho -n "Creating SQL script for server '"
                    mecho -n --info "${host}"
                    mecho "' to alter pma database ... "
                    createSQLScriptAlterDB ${host} ${pmadb_name} ${controluser}
                    print_result ${?}
                    mecho -n "Executing SQL script for server '"
                    mecho -n --info "${host}"
                    mecho "' ... "
                    ${database_exec} \
                           ${database_option} \
                           -u ${admin_user} ${admin_pass} < ${tmpSQLScript}
                    print_result ${?}
                    mecho 'Removing SQL script ...'
                    ${RM} -f ${tmpSQLScript}
                    print_result ${?}
                    ;;
                3)
                    # drop pma database
                    mecho
                    mecho -n "Removing pma database '"
                    mecho -n --info "${pmadb_name}"
                    mecho -n "' on server '"
                    mecho -n --info "${host}"
                    mecho "' ... "
                    ${database_exec} \
                           ${database_option} \
                           -u ${admin_user} ${admin_pass} \
                           -e "DROP DATABASE ${pmadb_name};"
                    print_result ${?}
                    ;;
                4)
                    # update tables
                    mecho
                    mecho -n "Creating SQL script for server '"
                    mecho -n --info "${host}"
                    mecho "' ... "
                    createSQLScriptHeader ${host} ${pmadb_name} ${controluser}
                    createSQLScriptCreateDB ${host} ${pmadb_name} ${controluser}
                    createSQLScriptCreateTables ${host} ${pmadb_name} ${controluser}
                    print_result ${?}
                    mecho -n "Executing SQL script for server '"
                    mecho -n --info "${host}"
                    mecho  "' ... "
                    ${database_exec} \
                           ${database_option} \
                           -u ${admin_user} ${admin_pass} < ${tmpSQLScript}
                    print_result ${?}
                    mecho  "Removing SQL script ... "
                    ${RM} -f ${tmpSQLScript}
                    print_result ${?}

                esac
            else
                # pmadb not found, create new
                mecho
                mecho -n "Creating SQL script for server '"
                mecho -n --info "${host}"
                mecho  "' ... "
                createSQLScriptHeader ${host} ${pmadb_name} ${controluser}
                createSQLScriptCreateDB ${host} ${pmadb_name} ${controluser}
            #    createSQLScriptGrantPrivileges ${host} ${pmadb_name} ${controluser}
                createSQLScriptCreateTables ${host} ${pmadb_name} ${controluser}
                print_result ${?}
                mecho -n "Executing SQL script for server '"
                mecho -n --info "${host}"
                mecho  "' ... "
                ${database_exec} \
                       ${database_option} \
                       -u ${admin_user} ${admin_pass} < ${tmpSQLScript}
                print_result ${?}

                mecho "Removing SQL script ... "
                ${RM} -f ${tmpSQLScript}
                print_result ${?}
            fi

        else    # advanced features not activated
            mecho
            mecho -n --info 'phpMyAdminDB on server '
            mecho -n --std  "'${_server}' "
            mecho --info    'is not active.'
            mecho
        fi        # end of advanced features active check
    else    # server is not active
        mecho
        mecho -n --info 'Server '
        mecho -n --std  "'${_server}' "
        mecho --info    'is not active.'
        mecho
    fi        # end of server active check

    # clearup
    rm -f ${tmpSQLScript}

    anykey
}

# ---------------------------------------------------------------------------
# select server
# ---------------------------------------------------------------------------
select_server()
{
    clrhome

    mecho
    mecho 'This script will create, alter or delete the pma database. To do'
    mecho 'this you have to choose one of the available servers and enter name'
    mecho 'and password of a user with admin rights on the choosen server.'
    mecho

    list_configured_servers

    case ${server} in
    '')
        check_executable
        exit 0
        ;;
    0)
        check_executable
        exit 127
        ;;
    *)
        db_operation ${server}
        select_server
        ;;
    esac
}

# ---------------------------------------------------------------------------
# main
# ---------------------------------------------------------------------------
main()
{
    alias="${1}"
    project_name=$(echo ${alias} | tr [:lower:] [:upper:])

    # include config
    . /etc/config.d/${alias}
    # include helper function
    . /var/install/bin/apache2-config-modules-phpmyadmin-common-tools-helper

    select_server
    check_executable

    exit 0
}

# ---------------------------------------------------------------------------
# call function main
# ---------------------------------------------------------------------------
main "${@}"

# ---------------------------------------------------------------------------
# end
#----------------------------------------------------------------------------