Return to database.h CVS log

Up to [home] / food

added oswd web design and finished validation for add restaurant page

#ifndef DB_H
#define DB_H

#include <mysql++>
#include <iostream>
#include <string>
using namespace std;


const char* DBNAME = "aypwip";
const char* DBADDR = "127.0.0.1";
const char* DBUSER = "ws";
const char* DBPASS = "n1ithg";

class Database
{
private:
    bool isConnected;
    Connection* con;
public:
    Database()
    {
        isConnected = false;
    }
    ~Database()
    {
        if (isConnected)
            delete con;
    }
    
    vector<pair<int, string> > getLocations()
    {
        vector<pair<int, string> > ans;
        try
        {
            Result res = select("select lid, name from food_locations;");
            for (Result::iterator ri = res.begin(); ri != res.end(); ri++)
            {
                Row r = *ri;
                ans.push_back( make_pair((int)(r["lid"]), (string)(r["name"])) );
            }
        } catch(...)
        {
            cout << "SQL exception!" << endl;
        }
        return ans;
    }
    vector<pair<int, string> > getKeywords()
    {
        vector<pair<int, string> > ans;
        try
        {
            Result res = select("select kid, keyword from food_keywords;");
            for (Result::iterator ri = res.begin(); ri != res.end(); ri++)
            {
                Row r = *ri;
                ans.push_back( make_pair((int)(r["kid"]), (string)(r["keyword"])) );
            }
        } catch(...)
        {
            cout << "SQL exception!" << endl;
        }
        return ans;
    }
    void addRestaurant(string name, string location, string about, vector<string> keywords)
    {
        /* for debugging
        cout << "Name: |" + name + "|<br />\n"
             << "Location: |" + location + "|<br />\n"
             << "About: |" + about + "|<br />\n"
             << "Keywords<br />\n";
        for (int i = 0; i < keywords.size(); i++)
        {
            cout << "|" << keywords[i] << "|<br />\n";
        }
        //*/
        
        if (getRestaurantID(name, location) == "-1")
        {
            cout << "Error, restuarant with the name " + name + " already exists.  Please go back and try a different name.";
            return;
        }
        
        // add location
        if (getLocationID(location) == "-1")
            execute("INSERT INTO food_locations(name) VALUES('" + location + "');");
        // add keywords
        for (int i = 0; i < keywords.size(); i++)
        {
            if (getKeywordID(keywords[i]) == "-1")
                execute("INSERT INTO food_keywords(keyword) VALUES('" + keywords[i] + "');");
        }
        // add restaurant
        //execute("INSERT INTO
        
        // add rest_keyword links
        
        cout << "Successfully added <i>" + name + "</i>";
    }
    
    
    string getKeywordID(string keyword)
    {
        return selectSingle("select kid from food_keyword where keyword like '"
                + keyword + "';", "-1");
    }
    string getRestaurantID(string name, string location)
    {
        string locationID = getLocationID(location);
        return selectSingle("select rid from food_restaurants where name like '" + name
                + "' and lid='" + locationID + "';", "-1");
    }
    string getLocationName(string lid)
    {
        return selectSingle("select name from food_locations where lid='" + lid + "';");
    }
    string getLocationID(string name)
    {
        return selectSingle("select name from food_locations where name like '" 
                + name + "';", "-1");
    }
private:
    bool connect()
    {
        try
        {
            if (!isConnected)
            {
                con = new Connection(DBNAME, DBADDR, DBUSER, DBPASS);
                isConnected = true;
            }
        }
        catch (...)
        {
            cout << "Error connecting to database." << endl;
        }
    }
    Result select(string q)
    {
        connect();
        Query query = con->query();
        query << q;
        return query.store();
    }
    
    void execute(string q)
    {
        connect();
        Query query = con->query();
        query << q;
        query.execute();
    }
    
    // empty is returned if nothing is found
    string selectSingle(string q, string empty)
    {
        Result r = select(q);
        Result::iterator ri = r.begin();
        if (r.size() == 0)
            return empty;
        return (string)((*ri)[0]);
    }
    string selectSingle(string q)
    {
        return selectSingle(q, "");
    }
    
};

#endif