OpenNebula

     *  Execute an INSERT or UPDATE Sql query.

     *    @param update Execute an INSERT or a UPDATE

    int insert_update(SqlDB *db, bool update);

     *  Execute an INSERT or UPDATE Sql query.

     *    @param update Execute an INSERT or a UPDATE

    int insert_update(SqlDB *db, bool update);

     *  Execute an INSERT or UPDATE Sql query.

     *    @param update Execute an INSERT or a UPDATE

    int insert_update(SqlDB *db, bool update);

     *  Execute an INSERT or UPDATE Sql query.

     *    @param update Execute an INSERT or a UPDATE

    int insert_update(SqlDB *db, bool update);

/* -------------------------------------------------------------------------- */

/* Copyright 2002-2010, OpenNebula Project Leads (OpenNebula.org)             */

/*                                                                            */

/* Licensed under the Apache License, Version 2.0 (the "License"); you may    */

/* not use this file except in compliance with the License. You may obtain    */

/* a copy of the License at                                                   */

/*                                                                            */

/* http://www.apache.org/licenses/LICENSE-2.0                                 */

/*                                                                            */

/* Unless required by applicable law or agreed to in writing, software        */

/* distributed under the License is distributed on an "AS IS" BASIS,          */

/* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.   */

/* See the License for the specific language governing permissions and        */

/* limitations under the License.                                             */

/* -------------------------------------------------------------------------- */

#ifndef PGSQL_DB_H_

#define PGSQL_DB_H_

#include <string>

#include <sstream>

#include <stdexcept>

#include <sys/time.h>

#include <sys/types.h>

#include <unistd.h>

#include "NebulaLog.h"

#include "SqlDB.h"

#include "ObjectSQL.h"

using namespace std;

#ifdef PGSQL_DB

#include <libpq-fe.h>

/**

 * PQDB class. Provides a wrapper to the postgresql database interface.

 */

class PGSqlDB : public SqlDB

{

public:

    PGSqlDB(const string& server,

            const string& user,

            const string& password,

            const string& database);

    ~PGSqlDB();

    /**

     *  Wraps the PQexec function call

     *    @param cmd the SQL command

     *    @param obj Callbackable obj to call if the query succeeds

     *    @return 0 on success

     */

    int exec(ostringstream& cmd, Callbackable* obj=0);

    /**

     *  This function returns a legal SQL string that can be used in an SQL

     *  statement. The string is encoded to an escaped SQL string, taking into

     *  account the current character set of the connection.

     *    @param str the string to be escaped

     *    @return a valid SQL string or NULL in case of failure

     */

    char * escape_str(const string& str);

    /**

     *  Frees a previously scaped string

     *    @param str pointer to the str

     */

    void free_str(char * str);

private:

    /**

     * The PostgreSQL connection handler

     */

    PGconn *             db;

    /**

     *  Fine-grain mutex for DB access

     */

    pthread_mutex_t     mutex;

    /**

     *  Function to lock the DB

     */

    void lock()

    {

        pthread_mutex_lock(&mutex);

    };

    /**

     *  Function to unlock the DB

     */

    void unlock()

    {

        pthread_mutex_unlock(&mutex);

    };

};

#else

//CLass stub

class PGSqlDB : public SqlDB

{

public:

    PGSqlDB(

            string server,

            string user,

            string password,

            char * database)

    {

        throw runtime_error("Aborting oned, PostgreSQL support not compiled!");

    };

    ~PGSqlDB(){};

    int exec(ostringstream& cmd, Callbackable* obj=0){return -1;};

    char * escape_str(const string& str){return 0;};

    void free_str(char * str){};

};

#endif

#endif /*PGSQL_DB_H_*/

     *  Execute an INSERT or UPDATE Sql query.

     *    @param update Execute an INSERT or a UPDATE

    int insert_update(SqlDB *db, bool update);

     *  Execute an INSERT or UPDATE Sql query.

     *    @param update Execute an INSERT or a UPDATE

    int insert_update(SqlDB *db, bool update);

     *  Execute an INSERT or UPDATE Sql query.

     *    @param update Execute an INSERT or a UPDATE

    int insert_update(SqlDB *db, bool update);

# PostgreSQL

pgsql=ARGUMENTS.get('pgsql', 'no')

if pgsql=='yes':

    main_env.Append(pgsql='yes')

    main_env.Append(CPPFLAGS=["-DPGSQL_DB"])

    main_env.Append(CPPPATH=["/usr/include/postgresql"])

else:

    main_env.Append(pgsql='no')

#

# Sample configuration for PostgreSQL

# DB = [ backend = "pgsql",

#        server  = "localhost",

#        user    = "oneadmin",

#        passwd  = "oneadmin",

#        db_name = "opennebula" ]

    "CREATE TABLE cluster_pool ("

const char * Host::db_bootstrap = "CREATE TABLE host_pool ("

    rc = insert_update(db, false);

    rc = insert_update(db, true);

int Host::insert_update(SqlDB *db, bool update)

    if(update)

        oss << "UPDATE " << table << " SET "

        << "host_name='" << sql_hostname << "',"

        << "state=" << state << ","

        << "im_mad='" << sql_im_mad_name << "',"

        << "vm_mad='" << sql_vmm_mad_name << "',"

        << "tm_mad='" << sql_tm_mad_name << "',"

        << "last_mon_time=" << last_monitored << ","

        << "cluster='" << sql_cluster << "',"

        << "template='" << sql_template << "' "

        << "WHERE oid=" << oid;

        oss << "INSERT"

        << " INTO " << table << " " << db_names << " VALUES ("

        << oid << ","

        << "'" << sql_hostname << "',"

        << state << ","

        << "'" << sql_im_mad_name << "',"

        << "'" << sql_vmm_mad_name << "',"

        << "'" << sql_tm_mad_name << "',"

        << last_monitored << ","

        << "'" << sql_cluster << "',"

        << "'" << sql_template << "')";

const char * HostShare::db_bootstrap = "CREATE TABLE host_shares("

    rc = insert_update(db, false);

    rc = insert_update(db, true);

int HostShare::insert_update(SqlDB *db, bool update)

    if(update)

        oss << "UPDATE " << table << " SET "

        << "disk_usage=" << disk_usage << ","

        << "mem_usage=" << mem_usage << ","

        << "cpu_usage=" << cpu_usage << ","

        << "max_disk=" << max_disk << ","

        << "max_mem=" << max_mem << ","

        << "max_cpu=" << max_cpu << ","

        << "free_disk=" << free_disk << ","

        << "free_mem=" << free_mem << ","

        << "free_cpu=" << free_cpu << ","

        << "used_disk=" << used_disk << ","

        << "used_mem=" << used_mem << ","

        << "used_cpu=" << used_cpu << " "

        << "WHERE hid=" << hsid;

        oss << "INSERT"

        << " INTO " << table << " "<< db_names <<" VALUES ("

    }

const char * Image::db_bootstrap = "CREATE TABLE image_pool ("

    rc = insert_update(db, false);

    return insert_update(db, true);;

int Image::insert_update(SqlDB *db, bool update)

    if(update)

        oss << "UPDATE " << table << " SET "

        << "uid=" << uid << ","

        << "name='" << sql_name << "',"

        << "type=" << type << ","

        << "public=" << public_img << ","

        << "persistent=" << persistent_img << ","

        << "regtime=" << regtime << ","

        << "source='" << sql_source << "',"

        << "state=" << state << ","

        << "running_vms=" << running_vms << ","

        << "template='" << sql_template << "' "

        << "WHERE oid=" << oid;

        oss << "INSERT"

        <<" INTO " << table << " " << db_names << " VALUES ("

        << oid << ","

        << uid << ","

        << "'" << sql_name << "',"

        << type << ","

        << public_img << ","

        << persistent_img << ","

        << regtime << ","

        << "'" << sql_source << "',"

        << state << ","

        << running_vms << ","

        << "'" << sql_template << "')";

#include "PGSqlDB.h"

        bool   db_is_mysql = false;

        bool   db_is_pgsql = false;

            if (value == "mysql"||value == "pgsql")

                if (value == "mysql")

                    db_is_mysql = true;

                if (value == "pgsql")

                    db_is_pgsql = true;

            string db_name = var_location + "one.db";

        if ( db_is_mysql )

        if ( db_is_pgsql )

        {

            db = new PGSqlDB(server,user,passwd,db_name);

        }

        ostringstream oss;

        oss << "SELECT oid FROM vm_pool";

        rc = db->exec(oss);

        if ( rc != 0 )

        {

            NebulaLog::log("ONE",Log::INFO,"Table not found, running for the first time?");

            NebulaLog::log("ONE",Log::INFO,"Bootstraping OpenNebula database.");

            VirtualMachinePool::bootstrap(db);

            HostPool::bootstrap(db);

            VirtualNetworkPool::bootstrap(db);

            UserPool::bootstrap(db);

            ImagePool::bootstrap(db);

        }

if env['pgsql']=='yes':

    env.Append(LIBS=['pq'])

#include <cstring>

    if ( values[0] != 0 && strcmp(values[0], "") != 0)

    oss << "UPDATE " << table << " SET "

        << "number="<< number << ","

        << "text='" << sql_text << "' "

        << "WHERE oid=" << oid;

/* -------------------------------------------------------------------------- */

/* Copyright 2002-2010, OpenNebula Project Leads (OpenNebula.org)             */

/*                                                                            */

/* Licensed under the Apache License, Version 2.0 (the "License"); you may    */

/* not use this file except in compliance with the License. You may obtain    */

/* a copy of the License at                                                   */

/*                                                                            */

/* http://www.apache.org/licenses/LICENSE-2.0                                 */

/*                                                                            */

/* Unless required by applicable law or agreed to in writing, software        */

/* distributed under the License is distributed on an "AS IS" BASIS,          */

/* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.   */

/* See the License for the specific language governing permissions and        */

/* limitations under the License.                                             */

/* -------------------------------------------------------------------------- */

#include "PGSqlDB.h"

/*********

 * Doc: http://www.postgresql.org/docs/8.3/static/libpq.html

 ********/

/* -------------------------------------------------------------------------- */

PGSqlDB::PGSqlDB(

        const string& server,

        const string& user,

        const string& password,

        const string& database)

{

    // Initialize a connection handler and connect to the server

    ostringstream oss;

    oss << "dbname=" << database << " host=" << server.c_str()

            << " user=" << user.c_str() << " password=" << password.c_str();

    string str = oss.str();

    const char *conninfo = str.c_str();

    db = PQconnectdb(conninfo);

    // Connect to the server

    if (PQstatus(db) != CONNECTION_OK)

    {

        PQfinish(db);

        throw runtime_error("Could not open database.");

    }

    pthread_mutex_init(&mutex,0);

}

/* -------------------------------------------------------------------------- */

PGSqlDB::~PGSqlDB()

{

    // Close the connection to the PostgreSQL server

    PQfinish(db);

    pthread_mutex_destroy(&mutex);

}

/* -------------------------------------------------------------------------- */

int PGSqlDB::exec(ostringstream& cmd, Callbackable* obj)

{

    PGresult * res;

    ExecStatusType status;

    const char * c_str;

    string       str;

    int   (*callback)(void*,int,char**,char**);

    void * arg;

    str   = cmd.str();

    c_str = str.c_str();

    callback = 0;

    arg      = 0;

    lock();

    res = PQexec(db, c_str);

    status = PQresultStatus(res);

    if (status != PGRES_COMMAND_OK && status != PGRES_TUPLES_OK)

    {

        ostringstream   oss;

        const char *    err_msg = PQerrorMessage(db);

        oss << "SQL command was: " << c_str;

        oss << ", error " << err_msg;

        NebulaLog::log("ONE",Log::ERROR,oss);

        PQclear(res);

        unlock();

        return -1;

    }

    if ( (obj != 0) && (obj->isCallBackSet()) )

    {

        int        num_fields;

        if (status != PGRES_TUPLES_OK)

        {

            ostringstream   oss;

            const char *    err_msg = PQerrorMessage(db);

            oss << "SQL command was: " << c_str;

            oss << ", error " << err_msg;

            NebulaLog::log("ONE",Log::ERROR,oss);

            PQclear(res);

            unlock();

            return -1;

        }

        // Fetch the names of the fields

        num_fields  = PQnfields(res);

        char ** names = new char*[num_fields];

        for(int i = 0; i < num_fields; i++)

        {

            names[i] = PQfname(res, i);

        }

        // Fetch each row, and call-back the object waiting for them

        char ** row = new char*[num_fields];

        for (int r = 0; r < PQntuples(res); r++)

        {

            for (int i = 0; i < num_fields; i++)

                row[i] = PQgetvalue(res, r, i);

            obj->do_callback(num_fields, row, names);

        }

        // Free the result object

        PQclear(res);

        delete[] names;

    }

    unlock();

    return 0;

}

/* -------------------------------------------------------------------------- */

char * PGSqlDB::escape_str(const string& str)

{

    char * result = new char[str.size()*2+1];

    PQescapeStringConn(db, result, str.c_str(), str.size(), NULL);

    return result;

}

/* -------------------------------------------------------------------------- */

void PGSqlDB::free_str(char * str)

{

    delete[] str;

}

/* -------------------------------------------------------------------------- */

if env['pgsql']=='yes':

    source_files.append('PGSqlDB.cc')

env.StaticLibrary(lib_name, source_files)

const char * User::db_bootstrap = "CREATE TABLE user_pool ("

    rc = insert_update(db, false);

    rc = insert_update(db, true);

int User::insert_update(SqlDB *db, bool update)

    if(update)

        oss << "UPDATE " << table << " SET "

        << "user_name='" << sql_username << "',"

        << "password='" << sql_password << "',"

        << "enabled='" << str_enabled << "' "

        << "WHERE oid=" << oid;

        oss << "INSERT"

        << " INTO " << table << " "<< db_names <<" VALUES ("

    }

const char * History::db_bootstrap = "CREATE TABLE "

    rc = insert_update(db, false);

    rc = insert_update(db, true);

int History::insert_update(SqlDB *db, bool update)

    if(update)

        oss << "UPDATE " << table << " SET "

        << "seq=" << seq << ","

        << "host_name='" << sql_hostname << "',"

        << "vm_dir='" << sql_vm_dir << "',"

        << "hid=" << hid << ","

        << "vm_mad='" << sql_vmm_mad_name << "',"

        << "tm_mad='" << sql_tm_mad_name << "',"

        << "stime=" << stime << ","

        << "etime=" << etime << ","

        << "pstime=" << prolog_stime << ","

        << "petime=" << prolog_etime << ","

        << "rstime=" << running_stime << ","

        << "retime=" << running_etime << ","

        << "estime=" << epilog_stime << ","

        << "eetime=" << epilog_etime << ","

        << "reason=" << reason << " "

        << "WHERE vid=" << oid;

        oss << "INSERT"

        << " INTO " << table << " "<< db_names <<" VALUES ("

        << oid << ","

        << seq << ","

        << "'" << sql_hostname << "',"

        << "'" << sql_vm_dir << "',"

        << hid << ","

        << "'" << sql_vmm_mad_name << "',"

        << "'" << sql_tm_mad_name  << "',"

        << stime << ","

        << etime << ","

        << prolog_stime  << ","

        << prolog_etime  << ","

        << running_stime << ","

        << running_etime << ","

        << epilog_stime  << ","

        << epilog_etime  << ","

        << reason << ")";

const char * VirtualMachine::db_bootstrap = "CREATE TABLE "

    rc = insert_update(db, false);

    return insert_update(db, true);

int VirtualMachine::insert_update(SqlDB *db, bool update)

    if(update)

        oss << "UPDATE " << table << " SET "

        << "uid=" << uid << ","

        << "name='" << sql_name << "',"

        << "last_poll=" << last_poll << ","

        << "state=" << state << ","

        << "lcm_state=" << lcm_state << ","

        << "stime=" << stime << ","

        << "etime=" << etime << ","

        << "deploy_id='" << sql_deploy_id << "',"

        << "memory=" << memory << ","

        << "cpu=" << cpu << ","

        << "net_tx=" << net_tx << ","

        << "net_rx=" << net_rx << ","

        << "last_seq=" << last_seq << ","

        << "template='" << sql_template << "' "

        << "WHERE oid=" << oid;

        oss << "INSERT"

        << " INTO " << table << " " << db_names << " VALUES ("

        << oid << ","

        << uid << ","

        << "'" << sql_name        << "',"

        << last_poll << ","

        << state << ","

        << lcm_state << ","

        << stime << ","

        << etime << ","

        << "'" << sql_deploy_id << "',"

        << memory << ","

        << cpu << ","

        << net_tx << ","

        << net_rx << ","

        << last_seq << ","

        << "'" << sql_template << "')";

const char * Leases::db_bootstrap = "CREATE TABLE leases ("

const char * VirtualNetwork::db_bootstrap = "CREATE TABLE "

    "network_pool ("

    "oid INTEGER PRIMARY KEY, uid INTEGER, name VARCHAR(256), type INTEGER, "

    "bridge TEXT, public INTEGER, template TEXT, UNIQUE(name))";

    rc = insert_update(db, false);

    return insert_update(db, true);

int VirtualNetwork::insert_update(SqlDB *db, bool update)

    if(update)

        oss << "UPDATE " << table << " SET "

        << "uid=" << uid << ","

        << "name='" << sql_name << "',"

        << "type=" << type << ","

        << "bridge='" << sql_bridge << "',"

        << "public=" << public_vnet << ","

        << "template='" << sql_template << "' "

        << "WHERE oid=" << oid;

        oss << "INSERT"

        << " INTO " << table << " " << db_names << " VALUES ("

        << oid << ","

        << uid << ","

        << "'" <<   sql_name << "',"

        << type << ","

        << "'" << sql_bridge << "',"

        << public_vnet << ","

        << "'" << sql_template << "')";

    cmd << "," << VirtualNetwork::table << ".uid";

    cmd << "," << VirtualNetwork::table << ".name";

    cmd << "," << VirtualNetwork::table << ".type";

    cmd << "," << VirtualNetwork::table << ".bridge";

    cmd << "," << VirtualNetwork::table << ".public";

    cmd << "," << VirtualNetwork::table << ".template";

    cmd << ",user_pool.user_name";