The Jukebox Project Needed A Database To Cache And Manage The Interface...

I needed a centralized information store to support the jukebox project I'm working on. So I used a database and a very thin client I wrote allowing seeding and querying. Actually... I was surprised at how much access I achieved with so little effort. Not only was I able to organize the scattered media into a coherent collection I was able to create a web interface to search the library and queue to the jukebox server.

//-----------------------------------------------------------------------------
//  dbiface.c
//
//  SQL database interface client helper library implementation.
//  Currently only supports MySql.
//
// Copyright (c) 2014 - Dysfunctional Farms A.K.A. www.smegware.com
//
//  All Smegware software is free; you can redistribute it and/or modify
//  it under the terms of the GNU General Public License as published by
//  the Free Software Foundation; either version 2 of the License, or
//  (at your option) any later version.
//
//  This software is distributed in the hope that it will be useful,
//  but WITHOUT ANY WARRANTY; without even the implied warranty of
//  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
//  GNU General Public License for more details.
//
//-----------------------------------------------------------------------------
//
// History...
//
//   $Source$
//   $Author$
// $Revision$
//
// $Log$
//
//-----------------------------------------------------------------------------

#include <string.h>
#include <my_global.h>
#include <mysql.h>
#include <report.h>

#include "dbiface.h"

//-----------------------------------------------------------------------------

struct _sqlclient {
  MYSQL        *client;
  MYSQL_RES    *result;
  MYSQL_ROW     row;
  unsigned int  fields;
  unsigned long rows;
};

//-----------------------------------------------------------------------------

void SQL_get_version_info(void)
{
  report(dbglevl_none, "Using MySQL client version: %s\n", mysql_get_client_info());
}

//-----------------------------------------------------------------------------

struct _sqlclient *SQL_connect(char *host, char *user, char *pass, char *base)
{
  struct _sqlclient *db = NULL;

  db = malloc(sizeof(struct _sqlclient));
  if(db)
  {
    memset(db, 0, sizeof(struct _sqlclient));
    db->client = mysql_init(NULL);
    if(db->client)
    {
      if(!mysql_real_connect(db->client, host, user, pass, base, 0, NULL, 0))
      {
	// Failed to connect to database.
	report(dbglevl_debug, "SQL_connect():mysql_real_connect() failed - Releasing resources and returning \"NULL\".\n");
	free(db);
	db = NULL;
      }
    }
    else
    {
      // Failed to initialize connection object.
      report(dbglevl_debug, "SQL_connect():mysql_init(NULL) failed - Releasing resources and returning \"NULL\".\n");
      free(db);
      db = NULL;
    }
  }
  else
  {
    report_error("SQL_connect():malloc(%i) failed - ", sizeof(struct _sqlclient));
  }
  if(db)
  {
    report(dbglevl_debug, "SQL_connect() - succeded.\n");
  }
  return db;
}

//-----------------------------------------------------------------------------

void SQL_close(struct _sqlclient *db)
{
  if(db)
  {
    if(db->result)
    {
      report(dbglevl_debug, "SQL_close():mysql_free_result() - releasing final result.\n");
      mysql_free_result(db->result);
    }
    report(dbglevl_debug, "SQL_close():mysql_close() - disconnecting.\n");
    mysql_close(db->client);
    report(dbglevl_debug, "SQL_close():free(me) - terminating context.\n");
    free(db);
  }
}

//-----------------------------------------------------------------------------

int SQL_query(struct _sqlclient *db, const char *query)
{
  int rtn;

  rtn = mysql_query(db->client, query);
  if(!rtn)
  {
    if(db->result)
    {
      // Each client object maintains only one result (serialized).
      report(dbglevl_debug, "SQL_query():mysql_free_result() - releasing previous result.\n");
      mysql_free_result(db->result);
      db->fields = 0;
      db->rows = 0;
    }
    db->result = mysql_store_result(db->client);
    if(db->result)
    {
      db->fields = mysql_num_fields(db->result);
      if(db->fields)
      {
	db->rows = mysql_num_rows(db->result);
      }
    }
    report(dbglevl_debug, "SQL_query(%s) - success : fields=%i rows=%i.\n", query, db->fields, db->rows);
  }
  else
  {
    report(dbglevl_debug, "SQL_query(%s) - %s.\n", query, mysql_error(db->client));
  }
  return rtn;
}

//-----------------------------------------------------------------------------

int SQL_get_next_row(struct _sqlclient *db)
{
  int rtn = -1;

  if(db->result)
  {
    if((db->row = mysql_fetch_row(db->result)))
    {
      rtn = 0;
    }
  }
  return rtn;
}

//-----------------------------------------------------------------------------

int SQL_get_column(struct _sqlclient *db, unsigned int column, char *data, int max)
{
  int rtn = -1;
  unsigned long *lengths;

  lengths = mysql_fetch_lengths(db->result);
  if(lengths)
  {
    if(column <= db->fields)
    {
      rtn = lengths[column] > (max - 1) ? lengths[column] : max - 1;
      memcpy(data, db->row[column], rtn);
      data[rtn] = '\0';
    }
  }
  else
  {
    report(dbglevl_debug, "SQL_get_column():mysql_fetch_lengths() failed - %s.\n", mysql_error(db->client));
  }

  return rtn;
}

//-----------------------------------------------------------------------------
// end: dbiface.c
//

  

The Code Demonstrates The Following Techniques...

• Object oriented Database Interface Written In C
  The code is object oriented by design having a constructor which creates an object handle and a destructor which releases all resources including itself. It hides implementation details to the outside while exposing safe methods of interface to a database.
• Designing An Interface Class
  I have used this simple code for both client and server designs. On the client side it has provided me with the ability to seed and query database information. On the server side it allows me to expose parts of an internal database with very limited and secure access for public use.