# Create a mysql database
mysql_database 'wordpress-cust01' do
  connection(
    :host     => '127.0.0.1',
    :username => 'root',
    :password => node['wordpress-cust01']['mysql']['initial_root_password']
  )
  action :create
end

# Create a mysql database on a named mysql instance
mysql_database 'oracle_rools' do
  connection(
    :host     => '127.0.0.1',
    :username => 'root',
    :socket   => "/var/run/mysql-#{instance-name}/mysqld.sock"
    :password => node['mysql']['server_root_password']
  )
  action :create
end

# Create a sql server database
sql_server_database 'mr_softie' do
  connection(
    :host     => '127.0.0.1',
    :port     => node['sql_server']['port'],
    :username => 'sa',
    :password => node['sql_server']['server_sa_password'],
    :options  => { 'ANSI_NULLS' => 'ON', 'QUOTED_IDENTIFIER' => 'OFF' }
  )
  action :create
end

# create a postgresql database
postgresql_database 'mr_softie' do
  connection(
    :host      => '127.0.0.1',
    :port      => 5432,
    :username  => 'postgres',
    :password  => node['postgresql']['password']['postgres']
  )
  action :create
end

# create a postgresql database with additional parameters
postgresql_database 'mr_softie' do
  connection(
    :host     => '127.0.0.1',
    :port     => 5432,
    :username => 'postgres',
    :password => node['postgresql']['password']['postgres']
  )
  template 'DEFAULT'
  encoding 'DEFAULT'
  tablespace 'DEFAULT'
  connection_limit '-1'
  owner 'postgres'
  action :create
end

# Externalize conection info in a ruby hash
mysql_connection_info = {
  :host     => '127.0.0.1',
  :username => 'root',
  :password => node['mysql']['server_root_password']
}

sql_server_connection_info = {
  :host     => '127.0.0.1',
  :port     => node['sql_server']['port'],
  :username => 'sa',
  :password => node['sql_server']['server_sa_password']
}

postgresql_connection_info = {
  :host     => '127.0.0.1',
  :port     => node['postgresql']['config']['port'],
  :username => 'postgres',
  :password => node['postgresql']['password']['postgres']
}

# Same create commands, connection info as an external hash
mysql_database 'foo' do
  connection mysql_connection_info
  action :create
end

sql_server_database 'foo' do
  connection sql_server_connection_info
  action     :create
end

postgresql_database 'foo' do
  connection postgresql_connection_info
  action     :create
end

# Create database, set provider in resource parameter
database 'bar' do
  connection mysql_connection_info
  provider   Chef::Provider::Database::Mysql
  action     :create
end

database 'bar' do
  connection sql_server_connection_info
  provider   Chef::Provider::Database::SqlServer
  action     :create
end

database 'bar' do
  connection postgresql_connection_info
  provider   Chef::Provider::Database::Postgresql
  action     :create
end



# Drop a database
mysql_database 'baz' do
  connection mysql_connection_info
  action    :drop
end



# Query a database
mysql_database 'flush the privileges' do
  connection mysql_connection_info
  sql        'flush privileges'
  action     :query
end


# Query a database from a sql script on disk
mysql_database 'run script' do
  connection mysql_connection_info
  sql { ::File.open('/path/to/sql_script.sql').read }
  action :query
end



# Vacuum a postgres database
postgresql_database 'vacuum databases' do
  connection      postgresql_connection_info
  database_name 'template1'
  sql 'VACUUM FULL VERBOSE ANALYZE'
  action :query
end

# Create, Insert, Query a SQLite database
# Note that inserting anything in to the database will create it automaticly.
sqlite_database 'mr_softie' do
  database_name '/path/to/database.db3'
  sql "sql command"
  action :query
end

# Delete the database, will remove the file
sqlite_database 'mr_softie' do
  database_name '/path/to/database.db3'
  action :drop
end

# create connection info as an external ruby hash
mysql_connection_info = {
  :host     => '127.0.0.1',
  :username => 'root',
  :password => node['mysql']['server_root_password']
}

postgresql_connection_info = {
  :host     => '127.0.0.1',
  :port     => node['postgresql']['config']['port'],
  :username => 'postgres',
  :password => node['postgresql']['password']['postgres']
}

sql_server_connection_info = {
  :host     => '127.0.0.1',
  :port     => node['sql_server']['port'],
  :username => 'sa',
  :password => node['sql_server']['server_sa_password']
}

# Create a mysql user but grant no privileges
mysql_database_user 'disenfranchised' do
  connection mysql_connection_info
  password   'super_secret'
  action     :create
end

# Do the same but pass the provider to the database resource
database_user 'disenfranchised' do
  connection mysql_connection_info
  password   'super_secret'
  provider   Chef::Provider::Database::MysqlUser
  action     :create
end

# Create a postgresql user but grant no privileges
postgresql_database_user 'disenfranchised' do
  connection postgresql_connection_info
  password   'super_secret'
  action     :create
end

# The same as above but utilizing hashed password string instead of
# plain text one
postgresql_database_user 'disenfranchised' do
  connection    postgresql_connection_info
  password      hashed_password('md5eacdbf8d9847a76978bd515fae200a2a')
  action        :grant
end

# Do the same but pass the provider to the database resource
database_user 'disenfranchised' do
  connection postgresql_connection_info
  password   'super_secret'
  provider   Chef::Provider::Database::PostgresqlUser
  action     :create
end

# Create a sql server user but grant no privileges
sql_server_database_user 'disenfranchised' do
  connection sql_server_connection_info
  password   'super_secret'
  action     :create
end

# Drop a mysql user
mysql_database_user 'foo_user' do
  connection mysql_connection_info
  action     :drop
end

# Bulk drop sql server users
%w(disenfranchised foo_user).each do |user|
  sql_server_database_user user do
    connection sql_server_connection_info
    action     :drop
  end
end

# Grant SELECT, UPDATE, and INSERT privileges to all tables in foo db from all hosts
mysql_database_user 'foo_user' do
  connection    mysql_connection_info
  password      'super_secret'
  database_name 'foo'
  host          '%'
  privileges    [:select,:update,:insert]
  action        :grant
end

# The same as above but utilizing hashed password string instead of
# plain text one
mysql_database_user 'foo_user' do
  connection    mysql_connection_info
  password      hashed_password('*664E8D709A6EBADFC68361EBE82CF77F10211E52')
  database_name 'foo'
  host          '%'
  privileges    [:select,:update,:insert]
  action        :grant
end

# Grant all privileges on all databases/tables from 127.0.0.1
mysql_database_user 'super_user' do
  connection mysql_connection_info
  password   'super_secret'
  action     :grant
end

# grant all privileges on all tables, sequences and functions in public schema of foo db
postgresql_database_user 'foo_user' do
  connection    postgresql_connection_info
  database_name 'foo'
  schema_name 'public'
  tables [:all]
  sequences [:all]
  functions [:all]
  privileges    [:all]
  action        [:grant, :grant_schema, :grant_table, :grant_sequence, :grant_function]
end

# grant select,update,insert privileges to all tables in foo db
sql_server_database_user 'foo_user' do
  connection    sql_server_connection_info
  password      'super_secret'
  database_name 'foo'
  privileges    [:select,:update,:insert]
  action        :grant
end