Magellan Linux

  • Back to homepage
    • /[alx-svn]/alx-src/branches/alxconf-060/functions/mysqlfunctions

    Contents of /alx-src/branches/alxconf-060/functions/mysqlfunctions

    Parent Directory Parent Directory | Revision Log Revision Log

    Revision 2152 - (show annotations) (download)
    Tue May 17 21:33:57 2011 UTC (12 years, 11 months ago) by niro
    File size: 3698 byte(s) 
    -eval already exports the variable - no need to do it twice, wich produces escaping issues
    1 # $Id$
    2 # mysql functions for bash
    3
    4 mysql_command()
    5 {
    6
    7 local SQL_USER
    8 local SQL_PASS
    9 local SQL_HOST
    10 local SQL_DB
    11 local SQL_COMMAND
    12
    13 SQL_USER="$1"
    14 SQL_PASS="$2"
    15 SQL_HOST="$3"
    16 SQL_DB="$4"
    17 SQL_COMMAND="$5"
    18
    19 # fallback (SQL_OPTS not, they are optional )
    20 if [ -z "${SQL_USER}" \
    21 -o -z "${SQL_PASS}" \
    22 -o -z "${SQL_HOST}" \
    23 -o -z "${SQL_DB}" \
    24 -o -z "${SQL_COMMAND}" ]
    25 then
    26 echo
    27 echo "Usage:"
    28 echo -e " mysql_command user pass host db \"sql-command\""
    29 echo
    30 return 1
    31 fi
    32
    33 mysql \
    34 --user="${SQL_USER}" \
    35 --password="${SQL_PASS}" \
    36 --host="${SQL_HOST}" \
    37 --database="${SQL_DB}" \
    38 --batch \
    39 --skip-column-names \
    40 --execute="${SQL_COMMAND}" \
    41 || return 1
    42
    43 return 0
    44 }
    45
    46 mysql_enum_colums()
    47 {
    48 local SQL_USER
    49 local SQL_PASS
    50 local SQL_HOST
    51 local SQL_DB
    52 local SQL_TABLE
    53
    54 local SHOWTABLES
    55 local i
    56 local read
    57 local field
    58 local type
    59 local null
    60 local key
    61 local default
    62 local extra
    63
    64 SQL_USER=$1
    65 SQL_PASS=$2
    66 SQL_HOST=$3
    67 SQL_DB=$4
    68 SQL_TABLE=$5
    69
    70 # show the column names ?
    71 if [ -n "${6}" -a "${6}" == "show" ]
    72 then
    73 SHOWTABLES=true
    74 else
    75 SHOWTABLES=false
    76 fi
    77
    78 # fallback (SQL_OPTS not, they are optional )
    79 if [ -z "${SQL_USER}" \
    80 -o -z "${SQL_PASS}" \
    81 -o -z "${SQL_HOST}" \
    82 -o -z "${SQL_DB}" \
    83 -o -z "${SQL_TABLE}" ]
    84 then
    85 echo
    86 echo "Usage:"
    87 echo -e " mysql_enum_columns user pass host db table [show]"
    88 echo
    89 return 1
    90 fi
    91
    92 declare -i i=0
    93 while read field type null key default extra
    94 do
    95 i=${i}+1
    96 [ "${SHOWTABLES}" == true ] && echo "${field}"
    97 done << EOF
    98 $(mysql_command ${SQL_USER} ${SQL_PASS} ${SQL_HOST} ${SQL_DB} "show columns from ${SQL_TABLE};")
    99 EOF
    100 [ "${SHOWTABLES}" == true ] || echo "${i}"
    101 }
    102
    103 beep3x()
    104 {
    105 echo -en "\a"
    106 sleep 0.7
    107 echo -en "\a"
    108 sleep 0.7
    109 echo -en "\a"
    110 }
    111
    112 reach_mysql_server()
    113 {
    114 # set connection timeout to 20 secs
    115 mysql --connect_timeout=20 \
    116 --user="${SQL_USER}" \
    117 --password="${SQL_PASS}" \
    118 --host="${SQL_HOST}" \
    119 --database="${SQL_DB}" \
    120 --batch \
    121 --execute="" &> /dev/null && return 0 || \
    122 (
    123 echo -e ${COLRED}
    124 echo "Cannot reach a mysql-server at '${SQL_HOST}'."
    125 echo "None of the current settings of this client will be touched."
    126 echo "Please inform your system-administrator."
    127 echo -e ${COLDEFAULT}
    128 beep3x
    129 sleep 5
    130 return 1
    131 )
    132 }
    133
    134 mysqldo()
    135 {
    136 mysql_command \
    137 "${SQL_USER}" \
    138 "${SQL_PASS}" \
    139 "${SQL_HOST}" \
    140 "${SQL_DB}" \
    141 "$@" \
    142 && return 0 || return 1
    143 }
    144
    145 # read tables and evaluate all variables
    146 # cmd: evaluate_table cfg_network
    147 # result: cfg_network_hostname=DUMMY_HOSTNAME
    148 evaluate_table()
    149 {
    150 local table="$1"
    151 local where_statement
    152
    153 if [[ -z $2 ]]
    154 then
    155 where_statement="where serial='${ALX_SERIAL}'"
    156 else
    157 where_statement="$2"
    158 fi
    159
    160 eval $(mysql \
    161 --user="${SQL_USER}" \
    162 --password="${SQL_PASS}" \
    163 --host="${SQL_HOST}" \
    164 --database="${SQL_DB}" \
    165 --xml \
    166 --execute="select * from ${table} ${where_statement}" \
    167 | xml sel -T -t -m //row/field \
    168 -v "concat('${table}_', @name, '=', '\"', self::field, '\"')" -n)
    169 }
    170
    171 # runs a statement and evaluate the resulting columns
    172 # cmd: evaluate_command "select serial from cfg_serial where serial=10"
    173 # result: serial=10
    174 # or with a prefix given
    175 # cmd: evaluate_command "select serial from cfg_serial where serial=10" "cfg_serial"
    176 # result: cfg_serial_serial=10
    177 evaluate_statement()
    178 {
    179 local statement="$1"
    180 local prefix="$2"
    181
    182 if [[ ! -z ${prefix} ]]
    183 then
    184 prefix="${prefix}_"
    185 fi
    186
    187 eval $(mysql \
    188 --user="${SQL_USER}" \
    189 --password="${SQL_PASS}" \
    190 --host="${SQL_HOST}" \
    191 --database="${SQL_DB}" \
    192 --xml \
    193 --execute="${statement}" \
    194 | xml sel -T -t -m //row/field \
    195 -v "concat('${prefix}', @name, '=', '\"', self::field, '\"')" -n)
    196 }

    Properties

    Name Value
    svn:executable *