mysql: prefetching, tests, order independent privileges

This commit implements prefetching for the mysql_database and
the mysql_user types. This enables them to e.g. set the password_hash
right when creating the user.

There is now a directory with tests.

Privileges are now order independent and do not cause spurious notices
anymore.

23 files changed, 290 insertions, 45 deletions

diff --git a/plugins/puppet/provider/mysql_database/mysql.rb b/plugins/puppet/provider/mysql_database/mysql.rb
index b12d042..2b70e04 100644
--- a/plugins/puppet/provider/mysql_database/mysql.rb
+++ b/plugins/puppet/provider/mysql_database/mysql.rb
@@ -1,7 +1,41 @@
-Puppet::Type.type(:mysql_database).provide(:mysql) do
+require 'puppet/provider/package'
+
+Puppet::Type.type(:mysql_database).provide(:mysql,
+		:parent => Puppet::Provider::Package) do
+
 	desc "Use mysql as database."
 	commands :mysqladmin => '/usr/bin/mysqladmin'
-	commands :mysqlshow => '/usr/bin/mysqlshow'
+	commands :mysql => '/usr/bin/mysql'
+
+	# retrieve the current set of mysql users
+	def self.instances
+		dbs = []
+
+		cmd = "#{command(:mysql)} mysql -NBe 'show databases'"
+		execpipe(cmd) do |process|
+			process.each do |line|
+				dbs << new( { :ensure => :present, :name => line.chomp } )
+			end
+		end
+		return dbs
+	end
+
+	def query
+		result = {
+			:name => @resource[:name],
+			:ensure => :absent
+		}
+
+		cmd = "#{command(:mysql)} mysql -NBe 'show databases'"
+		execpipe(cmd) do |process|
+			process.each do |line|
+				if line.chomp.eql?(@resource[:name])
+					result[:ensure] = :present
+				end
+			end
+		end
+		result
+	end
 
 	def create
 		mysqladmin "create", @resource[:name]
@@ -9,8 +43,9 @@ Puppet::Type.type(:mysql_database).provide(:mysql) do
 	def destroy
 		mysqladmin "-f", "drop", @resource[:name]
 	end
+
 	def exists?
-		if /\| #{@resource[:name]} /.match(mysqlshow)
+		if mysql("mysql", "-NBe", "show databases").match(/^#{@resource[:name]}$/)
 			true
 		else
 			false
diff --git a/plugins/puppet/provider/mysql_grant/mysql.rb b/plugins/puppet/provider/mysql_grant/mysql.rb
index 5122d2e..9d900ec 100644
--- a/plugins/puppet/provider/mysql_grant/mysql.rb
+++ b/plugins/puppet/provider/mysql_grant/mysql.rb
@@ -3,8 +3,11 @@
 # 	user@host => global
 # 	user@host/db => per-db
 
+require 'puppet/provider/package'
+
 Puppet::Type.type(:mysql_grant).provide(:mysql) do
-	desc "Use mysql as database."
+
+	desc "Uses mysql as database."
 
 	commands :mysql => '/usr/bin/mysql'
 
@@ -28,36 +31,38 @@ Puppet::Type.type(:mysql_grant).provide(:mysql) do
 		end
 	end
 
-	def create
-		name = split_name(@resource[:name])
-		case name[:type]
-		when :user
-			mysql "mysql", "-e", "INSERT INTO user (host, user, %s) VALUES ('%s', '%s', %s)" % [
-				@resource.should(:privileges).join(","),
-				name[:host], name[:user],
-				@resource.should(:privileges).map do |m| "'Y'" end.join(",")
-			]
-		when :db
-			mysql "mysql", "-e", "INSERT INTO db (host, user, db, %s) VALUES ('%s', '%s', '%s', %s)" % [
-				@resource.should(:privileges).join(","),
-				name[:host], name[:user], name[:db],
-				@resource.should(:privileges).map do |m| "'Y'" end.join(",")
-			]
+	def create_row
+		unless @resource.should(:privileges).empty?
+			name = split_name(@resource[:name])
+			case name[:type]
+			when :user
+				mysql "mysql", "-e", "INSERT INTO user (host, user) VALUES ('%s', '%s')" % [
+					name[:host], name[:user],
+				]
+			when :db
+				mysql "mysql", "-e", "INSERT INTO db (host, user, db) VALUES ('%s', '%s', '%s')" % [
+					name[:host], name[:user], name[:db],
+				]
+			end
 		end
 	end
+
 	def destroy
-		#mysql "mysql", "-e", "REVOKE %s ON '%s'.* FROM '%s@%s'" % [ @resource[:privileges], @resource[:database], @resource[:name], @resource[:host] ]
+		mysql "mysql", "-e", "REVOKE ALL ON '%s'.* FROM '%s@%s'" % [ @resource[:privileges], @resource[:database], @resource[:name], @resource[:host] ]
 	end
 	
-	def exists?
+	def row_exists?
 		name = split_name(@resource[:name])
 		fields = [:user, :host]
 		if name[:type] == :db
 			fields << :db
 		end
-		mysql( "mysql", "-Be", 'SELECT user FROM %s WHERE %s' % [ name[:type], fields.map do |f| "%s = '%s'" % [f, name[f]] end.join(' AND ')])
+		not mysql( "mysql", "-NBe", 'SELECT "1" FROM %s WHERE %s' % [ name[:type], fields.map do |f| "%s = '%s'" % [f, name[f]] end.join(' AND ')]).empty?
 	end
 
+	# privileges "exist" always, it's just the setting we are interested in
+	# def exists?  @resource.should( end
+
 	def privileges 
 		name = split_name(@resource[:name])
 		privs = ""
@@ -98,9 +103,11 @@ Puppet::Type.type(:mysql_grant).provide(:mysql) do
 			:create_view_priv, :show_view_priv, :create_routine_priv,
 			:alter_routine_priv, :execute_priv ]
 
-		begin
+		unless row_exists?
+			create_row
+		end
 
-				  puts "Setting privs: ", privs.join(", ")
+		# puts "Setting privs: ", privs.join(", ")
 		name = split_name(@resource[:name])
 		stmt = ''
 		where = ''
@@ -116,16 +123,12 @@ Puppet::Type.type(:mysql_grant).provide(:mysql) do
 			all_privs = db_privs
 		end
 	
-		puts "stmt:", stmt
+		# puts "stmt:", stmt
 		set = all_privs.collect do |p| "%s = '%s'" % [p, privs.include?(p) ? 'Y' : 'N'] end.join(', ')
-		puts "set:", set
+		# puts "set:", set
 		stmt = stmt << set << where
 
 		mysql "mysql", "-Be", stmt
-		rescue NoMethodError => detail
-			puts detail.backtrace
-			puts detail.to_s
-		end
 	end
 end
 
diff --git a/plugins/puppet/provider/mysql_user/mysql.rb b/plugins/puppet/provider/mysql_user/mysql.rb
index 1c48d71..8238620 100644
--- a/plugins/puppet/provider/mysql_user/mysql.rb
+++ b/plugins/puppet/provider/mysql_user/mysql.rb
@@ -1,27 +1,68 @@
-Puppet::Type.type(:mysql_user).provide(:mysql) do
+require 'puppet/provider/package'
+
+Puppet::Type.type(:mysql_user).provide(:mysql,
+		# T'is funny business, this code is quite generic
+		:parent => Puppet::Provider::Package) do
+
 	desc "Use mysql as database."
 	commands :mysql => '/usr/bin/mysql'
 
+	# retrieve the current set of mysql users
+	def self.instances
+		users = []
+
+		cmd = "#{command(:mysql)} mysql -NBe 'select concat(user, \"@\", host), password from user'"
+		execpipe(cmd) do |process|
+			process.each do |line|
+				users << new( query_line_to_hash(line) )
+			end
+		end
+		return users
+	end
+
+	def self.query_line_to_hash(line)
+		fields = line.chomp.split(/\t/)
+		{
+			:name => fields[0],
+			:password_hash => fields[1],
+			:ensure => :present
+		}
+	end
+
+	def query
+		result = {}
+
+		cmd = "#{command(:mysql)} -NBe 'select concat(user, \"@\", host), password from user where concat(user, \"@\", host) = \"%s\"'" % @resource[:name]
+		execpipe(cmd) do |process|
+			process.each do |line|
+				unless result.empty?
+					raise Puppet::Error,
+						"Got multiple results for user '%s'" % @resource[:name]
+				end
+				result = query_line_to_hash(line)
+			end
+		end
+		result
+	end
+
 	def create
-		mysql "mysql", "-e", "create user '%s@%s' identified by '%s'" % [ @resource[:name], @resource[:host], @resource[:password] ]
+		mysql "mysql", "-e", "create user '%s' identified by PASSWORD '%s'" % [ @resource[:name].sub("@", "'@'"), @resource.should(:password_hash) ]
 	end
+
 	def destroy
-		mysql "mysql", "-e", "drop user '%s@%s'" % [ @resource[:name], @resource[:host] ]
+		mysql "mysql", "-e", "drop user '%s'" % @resource[:name].sub("@", "'@'")
 	end
+
 	def exists?
-		if /^#{@resource[:name]}@#{@resource[:host]}$/.match( mysql( "mysql", "-Be", 'SELECT CONCAT(user, "@", host) FROM user' ) )
-			true
-		else
-			false
-		end
+		not mysql("mysql", "-NBe", "select '1' from user where CONCAT(user, '@', host) = '%s'" % @resource[:name]).empty?
 	end
 
 	def password_hash
-		mysql("mysql", "-NBe", "select password from user where user='#{@resource[:name]}' and host='#{@resource[:host]}'").chomp
+		@property_hash[:password_hash]
 	end
 
 	def password_hash=(string)
-		mysql "mysql", "-e", "SET PASSWORD FOR '#{@resource[:name]}'@'#{@resource[:host]}' = '#{string}'"
+		mysql "mysql", "-e", "SET PASSWORD FOR '%s' = '%s'" % [ @resource[:name].sub("@", "'@'"), string ]
 	end
 end
 
diff --git a/plugins/puppet/type/mysql_grant.rb b/plugins/puppet/type/mysql_grant.rb
index cc0c0e8..8a858e6 100644
--- a/plugins/puppet/type/mysql_grant.rb
+++ b/plugins/puppet/type/mysql_grant.rb
@@ -1,7 +1,17 @@
 # This has to be a separate type to enable collecting
 Puppet::Type.newtype(:mysql_grant) do
 	@doc = "Manage a database user's rights."
-	ensurable
+	#ensurable
+
+	autorequire :mysql_user do
+		reqs = []
+		matches = self[:name].match(/^([^@]+)@([^\/]+)$/)
+		unless matches.nil?
+			reqs << "%s@%s" % [ matches[0], matches[1] ]
+		end
+		reqs
+	end
+
 	newparam(:name) do
 		desc "The primary key: either user@host for global privilges or user@host/database for database specific privileges"
 	end
@@ -10,6 +20,38 @@ Puppet::Type.newtype(:mysql_grant) do
 		munge do |v|
 			symbolize(v)
 		end
+
+		def should_to_s(newvalue = @should)
+			if newvalue
+				unless newvalue.is_a?(Array)
+					newvalue = [ newvalue ]
+				end
+				newvalue.collect do |v| v.to_s end.sort.join ", "
+			else
+				nil
+			end
+		end
+
+		def is_to_s(currentvalue = @is)
+			if currentvalue
+				unless currentvalue.is_a?(Array)
+					currentvalue = [ currentvalue ]
+				end
+				currentvalue.collect do |v| v.to_s end.sort.join ", "
+			else
+				nil
+			end
+		end
+
+		# use the sorted outputs for comparison
+		def insync?(is)
+			if defined? @should and @should
+				self.is_to_s(is) == self.should_to_s
+			else
+				true
+			end
+		end
+
 	end
 end
 
diff --git a/plugins/puppet/type/mysql_user.rb b/plugins/puppet/type/mysql_user.rb
index d0756de..d92b610 100644
--- a/plugins/puppet/type/mysql_user.rb
+++ b/plugins/puppet/type/mysql_user.rb
@@ -3,10 +3,7 @@ Puppet::Type.newtype(:mysql_user) do
 	@doc = "Manage a database user."
 	ensurable
 	newparam(:name) do
-		desc "The name of the user."
-	end
-	newparam(:host) do
-		desc "The host from where to connect."
+		desc "The name of the user. This uses the 'username@hostname' form."
 	end
 	newproperty(:password_hash) do
 		desc "The password hash of the user. Use mysql_password() for creating such a hash."
diff --git a/tests/001_create_database.pp b/tests/001_create_database.pp
new file mode 100644
index 0000000..4e489cc
--- /dev/null
+++ b/tests/001_create_database.pp
@@ -0,0 +1,4 @@
+
+err("Will create 'test_db'")
+mysql_database { "test_db": ensure => present }
+
diff --git a/tests/010_create_user.pp b/tests/010_create_user.pp
new file mode 100644
index 0000000..a45ed5b
--- /dev/null
+++ b/tests/010_create_user.pp
@@ -0,0 +1,7 @@
+
+err("Will create user 'test_user@%' with password 'blah'")
+
+mysql_user{ "test_user@%":
+	password_hash => mysql_password("blah"),
+	ensure => present
+}
diff --git a/tests/012_change_password.pp b/tests/012_change_password.pp
new file mode 100644
index 0000000..7bf7f02
--- /dev/null
+++ b/tests/012_change_password.pp
@@ -0,0 +1,6 @@
+
+err("Changing password for user 'test_user@%'")
+mysql_user{ "test_user@%":
+	password_hash => mysql_password("foo"),
+	ensure => present
+}
diff --git a/tests/100_create_user_grant.pp b/tests/100_create_user_grant.pp
new file mode 100644
index 0000000..1d3dca8
--- /dev/null
+++ b/tests/100_create_user_grant.pp
@@ -0,0 +1,9 @@
+err("Grant SELECT, INSERT and UPDATE to test_user@%")
+
+mysql_grant {
+	"test_user@%":
+		privileges => [ "select_priv", 'insert_priv', 'update_priv' ],
+		tag => test;
+}
+
+
diff --git a/tests/101_remove_user_privilege.pp b/tests/101_remove_user_privilege.pp
new file mode 100644
index 0000000..6b7029e
--- /dev/null
+++ b/tests/101_remove_user_privilege.pp
@@ -0,0 +1,8 @@
+err("Revoke UPDATE from test_user@%")
+
+mysql_grant {
+	"test_user@%":
+		privileges => [ "select_priv", 'insert_priv' ],
+}
+
+
diff --git a/tests/102_add_user_privilege.pp b/tests/102_add_user_privilege.pp
new file mode 100644
index 0000000..849cd3a
--- /dev/null
+++ b/tests/102_add_user_privilege.pp
@@ -0,0 +1,8 @@
+err("Grant DELETE to test_user@%")
+
+mysql_grant {
+	"test_user@%":
+		privileges => [ "select_priv", 'insert_priv', 'delete_priv' ],
+}
+
+
diff --git a/tests/103_change_user_grant.pp b/tests/103_change_user_grant.pp
new file mode 100644
index 0000000..fa860a3
--- /dev/null
+++ b/tests/103_change_user_grant.pp
@@ -0,0 +1,8 @@
+err("Replace DELETE with UPDATE grant for test_user@%")
+
+mysql_grant {
+	"test_user@%":
+		privileges => [ "select_priv", 'insert_priv', 'update_priv' ],
+}
+
+
diff --git a/tests/104_mix_user_grants.pp b/tests/104_mix_user_grants.pp
new file mode 100644
index 0000000..d0dc512
--- /dev/null
+++ b/tests/104_mix_user_grants.pp
@@ -0,0 +1,8 @@
+err("Change the order of the defined privileges")
+
+mysql_grant {
+	"test_user@%":
+		privileges => [ "update_priv", 'insert_priv', 'select_priv' ],
+}
+
+
diff --git a/tests/150_create_db_grant.pp b/tests/150_create_db_grant.pp
new file mode 100644
index 0000000..f2b52f9
--- /dev/null
+++ b/tests/150_create_db_grant.pp
@@ -0,0 +1,9 @@
+err("Create a db grant")
+
+mysql_grant {
+	"test_user@%/test_user":
+		privileges => [ "select_priv", 'insert_priv', 'update_priv' ],
+		tag => test;
+}
+
+
diff --git a/tests/151_remove_db_privilege.pp b/tests/151_remove_db_privilege.pp
new file mode 100644
index 0000000..4eae44b
--- /dev/null
+++ b/tests/151_remove_db_privilege.pp
@@ -0,0 +1,8 @@
+err("Revoke UPDATE from test_user@%/test_user")
+
+mysql_grant {
+	"test_user@%/test_user":
+		privileges => [ "select_priv", 'insert_priv'],
+}
+
+
diff --git a/tests/152_add_db_privilege.pp b/tests/152_add_db_privilege.pp
new file mode 100644
index 0000000..21dae54
--- /dev/null
+++ b/tests/152_add_db_privilege.pp
@@ -0,0 +1,8 @@
+err("Grant DELETE to test_user@%/test_user")
+
+mysql_grant {
+	"test_user@%/test_user":
+		privileges => [ "select_priv", 'insert_priv', 'delete_priv'],
+}
+
+
diff --git a/tests/153_change_db_priv.pp b/tests/153_change_db_priv.pp
new file mode 100644
index 0000000..a317052
--- /dev/null
+++ b/tests/153_change_db_priv.pp
@@ -0,0 +1,8 @@
+err("Change DELETE to UPDATE privilege for test_user@%/test_user")
+
+mysql_grant {
+	"test_user@%/test_user":
+		privileges => [ "select_priv", 'insert_priv', 'update_priv'],
+}
+
+
diff --git a/tests/996_remove_db_grant.pp b/tests/996_remove_db_grant.pp
new file mode 100644
index 0000000..e05aea8
--- /dev/null
+++ b/tests/996_remove_db_grant.pp
@@ -0,0 +1,5 @@
+err("Remove the db grant")
+
+mysql_grant { "test_user@%/test_user": privileges => [ ] }
+
+
diff --git a/tests/997_remove_user_grant.pp b/tests/997_remove_user_grant.pp
new file mode 100644
index 0000000..fcdc490
--- /dev/null
+++ b/tests/997_remove_user_grant.pp
@@ -0,0 +1,5 @@
+err("Removing the user grant")
+
+mysql_grant { "test_user@%": privileges => [] }
+
+
diff --git a/tests/998_remove_user.pp b/tests/998_remove_user.pp
new file mode 100644
index 0000000..649e739
--- /dev/null
+++ b/tests/998_remove_user.pp
@@ -0,0 +1,3 @@
+
+err("Removing user 'test_user@%'")
+mysql_user{ "test_user@%": ensure => absent }
diff --git a/tests/999_remove_database.pp b/tests/999_remove_database.pp
new file mode 100644
index 0000000..34e224d
--- /dev/null
+++ b/tests/999_remove_database.pp
@@ -0,0 +1,4 @@
+
+err("Will remove 'test_db' again")
+mysql_database { "test_db": ensure => absent }
+
diff --git a/tests/README b/tests/README
new file mode 100644
index 0000000..7ef1421
--- /dev/null
+++ b/tests/README
@@ -0,0 +1,6 @@
+Execute these testfile in asciibetical order to check the functioning of the
+types and providers.
+
+They try to create databases, users, grants, check for their existance, change
+attributes, and remove them again.
+
diff --git a/tests/run_tests b/tests/run_tests
new file mode 100755
index 0000000..1ae6c42
--- /dev/null
+++ b/tests/run_tests
@@ -0,0 +1,13 @@
+#!/bin/bash
+
+export RUBYLIB=${RUBYLIB:-../plugins}
+OPTIONS="$*"
+OPTIONS="${OPTIONS:---trace}"
+
+find -iname \*.pp | sort | while read current; do
+	echo "Running $current"
+	puppet $OPTIONS $current
+	echo "Running $current again"
+	puppet $OPTIONS $current
+	echo
+done