34.204.169.76 |    

Navigation

Google Advertisement

Little mysql library for bash

mysql.inc.sh
  1. # *********************************************************
  2. # file: mysql.inc.sh
  3. # date: 2008-11-03 14:56
  4. # author: (c) by Marko Schulz - <info@tuxnet24.de>
  5. # description: Little mysql library for bash.
  6. # *********************************************************
  7. #
  8. #
  9. # SYNOPSIS
  10. #
  11. #    # Load this file in your main script:
  12. #    test -f ./mysql.inc.sh && source ./mysql.inc.sh || exit 1
  13. #
  14. #    # Update table 'users'.
  15. #    if ! rc_mysql_query "UPDATE" "$dbHostname" "$dbUsername" \
  16. #                                 "$dbPassword" "$dbDatabase" \
  17. #                                 "UPDATE users SET email='info@test.de' WHERE id=1" ; then
  18. #        echo "MySQL Statement fehlgeschlagen!"
  19. #    fi
  20. #
  21. #    # Select all rows from table 'users'.
  22. #    rows=$( rc_mysql_query "SELECT" "$dbHostname" \
  23. #                           "$dbUsername" "$dbPassword" \
  24. #                           "$dbDatabase" "SELECT * FROM users ORDER BY id ASC" )
  25. #
  26. #
  27. #    # Convert the result '$rows' in an array row[].
  28. #    mysql_fetch_rows "$rows"
  29. #
  30. #    # Loop array row[] and split line by pipe character.
  31. #    for (( i=0; i<${#row[@]}; i++ )); do
  32. #        id=$( echo ${row[$i]} | cut -d'|' -f1 )
  33. #        username=$( echo ${row[$i]} | cut -d'|' -f3 )
  34. #        echo "\$id: $id; \$username: $username"
  35. #    done
  36. #
  37. #    # This sample get the next insert id of the auto_increment field of table 'users'.
  38. #    nextid=$( rc_mysql_nextid "$dbHostname" "$dbUsername" "$dbPassword" "$dbDatabase" "users" )
  39. #
  40. #
  41. #
  42. # *********************************************************
  43. # This function send a MySQL query. For type of SQL statements
  44. # like, INSERT, UPDATE, DELETE, DROP, etc, rc_mysql_query()
  45. # returns TRUE on success or FALSE on error. For other type
  46. # of SQL statements like SELECT, SHOW, DESCRIBE, EXPLAIN
  47. # rc_mysql_query() returning the result.
  48.  
  49. function rc_mysql_query () {
  50.  
  51. local status=$1
  52. local hostname=${2:-"localhost"}
  53. local username=$3
  54. local password=$4
  55. local database=$5
  56. local query=$6
  57. local error="/tmp/mysql_query.err"
  58.  
  59. # Execute the statement INSERT|UPDATE|REPLACE|DELETE|DROP and return the number of affected rows from ROW_COUNT().
  60. if [ "$status" = "INSERT" -o "$status" = "UPDATE" -o "$status" = "REPLACE" -o "$status" = "DELETE" -o "$status" = "DROP" ]; then
  61. 	local res=$( mysql	--user=$username \
  62. 						--password=$password \
  63. 						--database=$database \
  64. 						--host=$hostname \
  65. 						--line-numbers --no-beep -Bse "$query; SELECT ROW_COUNT();" 2>$error )
  66.  
  67. 	# Return true if affected rows greater than 0.
  68. 	##[ "$res" -ne 0 ] && true || false
  69.  
  70. 	# Return true if no error in error file.
  71. 	if [ "$( cat $error | wc -l )" -ne 0 ]; then
  72. 		[ -f "$error" ] && rm -f $error; false
  73. 	else
  74. 		[ -f "$error" ] && rm -f $error; true
  75. 	fi
  76. else
  77.  
  78. 	# Execute the statement and return the result.
  79. 	local res=$( mysql --user=$username \
  80. 					--password=$password \
  81. 					--database=$database \
  82. 					--host=$hostname \
  83. 					--line-numbers --no-beep --column-names -Bse "$query" 2>$error )
  84.  
  85. 	# Return the result if no error in error file.
  86. 	if [ "$( cat $error 2>/dev/null | wc -l )" -ne 0 ]; then
  87. 		if [ -f "$error" ]; then
  88. 			cat $error; rm -f $error; exit 1
  89. 		fi
  90. 	else
  91. 		echo -e "$res"
  92. 	fi
  93.  
  94.  
  95. fi
  96.  
  97. }
  98.  
  99. # *********************************************************
  100. # This function call the MySQL statement (SHOW TABLE STATUS LIKE '<TABLENAME>')
  101. # and return the value of a defined key (e.g. auto_increment).
  102.  
  103. function rc_mysql_status () {
  104.  
  105. local hostname=${1:-"localhost"}
  106. local username=$2
  107. local password=$3
  108. local database=$4
  109. local table=$5
  110. local object=$6
  111. local error="/tmp/mysql_status.err"
  112.  
  113. # Execute the statement and return the result.
  114. local res=$( mysql --user=$username \
  115. 					--password=$password \
  116. 					--database=$database \
  117. 					--host=$hostname \
  118. 					--column-names --skip-auto-rehash -Be "SHOW TABLE STATUS LIKE '$table'" 2>$error )
  119.  
  120. # Return the value of the defined key if no error in file.
  121. if [ "$( cat $error 2>/dev/null | wc -l )" -ne 0 ]; then
  122. 	if [ -f "$error" ]; then
  123. 		cat $error; rm -f $error; exit 1
  124. 	fi
  125. else
  126. 	echo -e "$res" | \
  127. 		perl -ne '
  128. 		BEGIN {
  129. 		use strict;
  130. 		use vars qw( $obj @data );
  131. 		$obj="'$object'";
  132. 		}
  133.  
  134. 		my @tmp = split( /\s+/, $_ );
  135. 		push( @data, [@tmp] );
  136.  
  137. 		END {
  138.  
  139. 		my $index=0;
  140. 		for my $x ( 0 .. ($#data+1) ) {
  141. 			foreach my $y( 0 .. (@{$data[$x]}+1) ) {
  142. 				$index = $y if ( lc($data[0][$y]) eq "$obj" );
  143. 			}
  144. 		}
  145.  
  146. 		print $data[1][$index]."\n";
  147.  
  148. 		}'
  149. fi
  150.  
  151. }
  152.  
  153. # *********************************************************
  154. # This function convert the raw returned mysql data as
  155. # an numeric array row[]. This is an simulated two
  156. # dimensional array seperated by pipe character.
  157. #
  158. # EXAMPLE:
  159. #	row[0]="1|Fred Feuerstein|ffeuerstein|ffeuerstein@feuerstein.com"
  160. #	row[1]="2|Barney Geroellheimer|bgeroellheimer|bgeroellheimer@feuerstein.com"
  161.  
  162. function mysql_fetch_rows () {
  163.  
  164. local data="$*"
  165.  
  166. # This perl snippet convert the raw data in array row[].
  167. array=$( echo -e "$data" | perl -ne '
  168. BEGIN { $i=0; }
  169.  
  170. # We jump to the next line if this is a empty line.
  171. next if $_ =~ m/^$/;
  172.  
  173. # Remove newlines, spaces and other charcters.
  174. $_ = conv_chars( $_ );
  175.  
  176. # Convert the entry like this: "one","two","tree"
  177. $_ =~ s/\t+/\|/g;
  178.  
  179. # Print out to STDOUT.
  180. print "row[".$i."]=\"".$_."\"\n";
  181.  
  182. $i++;
  183.  
  184. sub conv_chars {
  185.  
  186. my $txt=shift;
  187. $txt =~ s/\cM\n//g;
  188. $txt =~ s/\n\cM//g;
  189. $txt =~ s/\cM//g;
  190. $txt =~ s/\s+$//g;
  191. return $txt;
  192.  
  193. }
  194. ' )
  195.  
  196. # To use the array row[] in this script
  197. # we have to eval this ;-)
  198. eval $array
  199.  
  200. }
  201.  
  202. # *********************************************************
  203. # This function return the next insert id of an
  204. # auto_increment field of the defined mysql table.
  205.  
  206. function rc_mysql_nextid () {
  207.  
  208. local hostname=${1:-"localhost"}
  209. local username=$2
  210. local password=$3
  211. local database=$4
  212. local table=$5
  213.  
  214. rc_mysql_status  "$dbHostname" "$dbUsername" "$dbPassword" "$dbDatabase" "$table" "auto_increment"
  215.  
  216. }
  217.  
  218. # *********************************************************
  219. # EOF
Parsed in 0.009 seconds at 666.34 KB/s

Search
 
Full text search by name and content of a snippet.

User online
There are 3 users online.

Tags Cloud

Latest snippets
last day of last month
(Bash::Snippets)
crypt_apr1_md5
(PHP::Function)
crypt_apr1_md5
(Perl::Function)
transparent
(CSS)
rfc2822Toiso8601
(PHP::Function)
STDERR
(Bash::Function)
radius
(CSS)
shadow
(CSS)
rotate
(CSS)
create_function
(JavaScript::Snippets)