Ask Your Question
0

How to make SQL queries against MySQL/MSSQL databases for insert/updating rows?

asked 2014-11-21 19:02:53 -0500

John Ericson gravatar image

I'm in need of finding a good way to query existing databases to make sure rows/values exists in tables and if not inserting/updating them. As I'm working against several different database vendors such as MySQL and MSSQL I would prefer an abstracted layer for doing this so it's easier to learn and use.

Has anyone done something similar and knows a good way of doing it? Something like https://supermarket.getchef.com/cookb... in the Puppet world would be really nice.

Thanks in advance, John

edit retag flag offensive close merge delete

1 Answer

Sort by ยป oldest newest most voted
1

answered 2014-11-24 08:13:47 -0500

drdam gravatar image

We are using an Oracle DB, but I guess this could be ported to any database you want to use:

I created a puppet package on the database (stored procedures). This package has some functions (userexists, tablespaceexists, synonymexists, ....) and some procedures (usercreate, tablespacecreate, synonymcreate, ...). These functions and procedures contain all statements necessary to create the wanted object in the database.

In our oradb module we have some defined resources for all database objects we want to manage via puppet.

define oradb::user (
  $user       = $title,
  $oracle_sid = undef,) {
  # step 1: get passwords from hiera
  $user_password = hiera($user)
  $puppet_db_user = hiera(puppet_db_user)

  # step 2: create tablespace
  exec { "create_use${user}":
    command => "/bin/su - oracle -c \"export ORACLE_SID=${oracle_sid};export ORAENV_ASK=NO;. oraenv; echo \"exec pck_puppet.user_create('${user}','${user_password}')\" | sqlplus -s puppet/${puppet_db_user}",
    onlyif  => "/bin/su - oracle -c \"export ORACLE_SID=${oracle_sid};export ORAENV_ASK=NO;. oraenv; echo \"select pck_puppet.user_exists('${user}') from dual;\" | sqlplus -s puppet/${puppet_db_user}\" | grep \b*[0]$",
  }
}

When the Oradb::User resource is used in a manifest, I first run the onlyif command. This will use the userexists function in my puppet package on the database. If the count returns 0, then the user doesn't exist and the usercreate procedure is called in the commandparameter.

This works similar with other database objects.

The above exapmle could be modified to work with multiple databases.

define db::user (
  $user       = $title,
  $oracle_sid = undef,) {
  # step 1: get passwords from hiera
  $user_password = hiera($user)
  $puppet_db_user = hiera(puppet_db_user)

  # step 2: create tablespace
  case $database {
    'oracle': {
      # Exec for oracle database
    }
    'MySQL': {
      # Exec for MySQL database
    }
    'MSSQL': {
      # Exec for MSSQL database
    }
   default: {
      # Exec for default database
    }
  }
}

you can chose for a custom fact to determine the database used on the host (this is the most transparant way) or you could pass the database via a parameter in the define. The only requirement is you can use a command line tool to access your DB.

edit flag offensive delete link more

Your Answer

Please start posting anonymously - your entry will be published after you log in or create a new account.

Add Answer

Question Tools

1 follower

Stats

Asked: 2014-11-21 19:02:53 -0500

Seen: 895 times

Last updated: Nov 24 '14