At Silverpop we're really starting to ramp up our implementation of Chef, and coming up with some really cool ways of doing things. Our application is primarily a set of Java webapps with Oracle providing the databases.
One thing I really wanted to do was auto-discovery of connection strings for the application configs. We have multiple application clusters, each cluster combining to serve one running instance of our application. We call these clusters pods.
Our DBAs maintain a database of all the TNSNames values for all our production databases. It's mainly for their own administrative use, but I wanted to pull that information into Chef. So, I wrote a little REST API that connects to the TNSNames database, and returns the results of a given query in JSON format. I used Bottle as the web framework because it looked quick and easy to setup a one off app like this.
There are three main pieces to the code below - a Daemon class (adapted from http://www.jejik.com/articles/2007/02/a_simple_unix_linux_daemon_in_python), a couple of functions that express the SQL queries and translate the results, and the routes for the API that call those functions. Stuff like URLs and logins have been made all generic and whatnot.
#!/usr/local/bin/python
# dba_api.py
# exposes a REST API that returns connections strings for a pod in JSON format
# can be queried by environment or SID.
# Examples:
# http://dbserver/conn/pod/1
# http://dbserver/conn/stage/5
# http://dbserver/conn/sid/SID
# controlled like an init script, e.g. ./dba_api (stop|start|restart)
import sys, os, time, atexit
import logging
from signal import SIGTERM
import cx_Oracle
import json
import bottle
from bottle import route, run, request, abort
# connection string for the dba database
dbconn = 'USER/PASS@dbserver:PORT/SID'
progname = os.path.basename(sys.argv[0]).split('.')[0]
"""
daemon code from http://www.jejik.com/articles/2007/02/a_simple_unix_linux_daemon_in_python/
"""
class Daemon:
"""
A generic daemon class.
Usage: subclass the Daemon class and override the run() method
"""
def __init__(self, pidfile, stdin='/dev/null', stdout='/dev/null', stderr='/dev/null'):
self.stdin = stdin
self.stdout = stdout
self.stderr = stderr
self.pidfile = pidfile
def daemonize(self):
"""
do the UNIX double-fork magic, see Stevens' "Advanced
Programming in the UNIX Environment" for details (ISBN 0201563177)
http://www.erlenstar.demon.co.uk/unix/faq_2.html#SEC16
"""
try:
pid = os.fork()
if pid > 0:
# exit first parent
sys.exit(0)
except OSError, e:
sys.stderr.write("fork #1 failed: %d (%s)\n" % (e.errno, e.strerror))
sys.exit(1)
# decouple from parent environment
os.chdir("/")
os.setsid()
os.umask(0)
# do second fork
try:
pid = os.fork()
if pid > 0:
# exit from second parent
sys.exit(0)
except OSError, e:
sys.stderr.write("fork #2 failed: %d (%s)\n" % (e.errno, e.strerror))
sys.exit(1)
# redirect standard file descriptors
sys.stdout.flush()
sys.stderr.flush()
si = file(self.stdin, 'r')
so = file(self.stdout, 'a+')
se = file(self.stderr, 'a+', 0)
os.dup2(si.fileno(), sys.stdin.fileno())
os.dup2(so.fileno(), sys.stdout.fileno())
os.dup2(se.fileno(), sys.stderr.fileno())
# write pidfile
atexit.register(self.delpid)
pid = str(os.getpid())
file(self.pidfile,'w+').write("%s\n" % pid)
def delpid(self):
os.remove(self.pidfile)
def start(self):
"""
Start the daemon
"""
# Check for a pidfile to see if the daemon already runs
try:
pf = file(self.pidfile,'r')
pid = int(pf.read().strip())
pf.close()
except IOError:
pid = None
if pid:
message = "pidfile %s already exist. Daemon already running?\n"
sys.stderr.write(message % self.pidfile)
sys.exit(1)
# Start the daemon
self.daemonize()
self.run()
def stop(self):
"""
Stop the daemon
"""
# Get the pid from the pidfile
try:
pf = file(self.pidfile,'r')
pid = int(pf.read().strip())
pf.close()
except IOError:
pid = None
if not pid:
message = "pidfile %s does not exist. Daemon not running?\n"
sys.stderr.write(message % self.pidfile)
return # not an error in a restart
# Try killing the daemon process
try:
while 1:
os.kill(pid, SIGTERM)
time.sleep(0.1)
except OSError, err:
err = str(err)
if err.find("No such process") > 0:
if os.path.exists(self.pidfile):
os.remove(self.pidfile)
else:
print str(err)
sys.exit(1)
def restart(self):
"""
Restart the daemon
"""
self.stop()
self.start()
def run(self):
"""
You should override this method when you subclass Daemon. It will be called after the process has been
daemonized by start() or restart().
"""
def get_db_by_pod( pod, lsite ):
con = cx_Oracle.connect(conn)
cur = con.cursor()
cur.prepare('select sid, vip, port, host, port2 from db_info where product = :pod and businessline = :lsite')
cur.execute(None, {'pod': pod, 'lsite': lsite})
fieldNum = 0
fieldNames = {}
desc = [d[0].lower() for d in cur.description]
results = dict(enumerate([dict(zip(desc,line)) for line in cur.fetchall()]))
cur.close()
con.close()
return results
def get_db_by_sid( sid ):
con = cx_Oracle.connect(conn)
cur = con.cursor()
cur.prepare('select sid, vip, port, host, port2 from db_info where sid = :sid')
cur.execute(None, {'sid': sid})
fieldNum = 0
fieldNames = {}
desc = [d[0].lower() for d in cur.description]
results = dict(enumerate([dict(zip(desc,line)) for line in cur.fetchall()]))
cur.close()
con.close()
return results
@route('/conn/sid/:sid', method='GET')
def get_conn(sid):
sid = sid.upper()
entity = get_db_by_sid(sid)
if not entity:
abort(404, 'No connection information found for %s' % sid)
return entity
@route('/conn/pod/:pod', method='GET')
def get_pod(pod):
pod = 'POD' + pod
entity = get_db_by_pod(pod, pod)
if not entity:
abort(404, 'No connection information found for %s' % pod)
return entity
@route('/conn/stage/:pod', method='GET')
def get_pod(pod):
pod = 'POD' + pod
entity = get_db_by_pod(pod, 'STAGE')
if not entity:
abort(404, 'No connection information found for %s' % pod)
return entity
class MyDaemon(Daemon):
def run(self):
run(host='0.0.0.0', port=9090)
if __name__ == "__main__":
daemon = MyDaemon('/var/run/%s.pid' % progname)
if len(sys.argv) == 2:
if 'start' == sys.argv[1]:
daemon.start()
elif 'stop' == sys.argv[1]:
daemon.stop()
elif 'restart' == sys.argv[1]:
daemon.restart()
else:
print "Unknown command"
sys.exit(2)
sys.exit(0)
else:
print "usage: %s start|stop|restart" % sys.argv[0]
sys.exit(2)
Now, once that was up and running, I needed to be able to consume the JSON returned by the API into Chef. I created a cookbook called "tnsnames", consisting of one recipe. The node[:dba_api][:host] and node[:dba_api][:port] attributes are set in the roles we created for each pod.
ruby_block "tnsnames" do
block do
begin
pod = node[:pod_name] or raise Chef::Exceptions::AttributeNotFound, "Could not determine pod name. Ensure that the pod role is applied to this node."
m = pod.match /(.+)([0-9])/
lsite = m[1]
podnum = m[2]
rest_url = "http://#{node[:dba_api][:host]}:#{node[:dba_api][:port]}"
rest = Chef::REST.new(rest_url)
conn = rest.get_rest("conn/#{lsite}/#{podnum}")
Chef::Log.debug(conn)
node[:tnsnames] = conn
Chef::Log.info("Got TNS names for #{pod} successfully from API at http://#{node[:dba_api][:host]}:#{node[:dba_api][:port]}/conn/#{lsite}/#{podnum}")
rescue Chef::Exceptions::AttributeNotFound => e
Chef::Log.error(e)
rescue StandardError => e
Chef::Log.error("Getting TNS names for #{pod} from DBA API failed: #{e}")
end
end
end
The end result being a node[:tnsnames] attribute which is a hash of the SID, VIP, port number, and server hostname for the database.
