/home/frank/projects/kitlist/src/kitlistpgsqldao.cpp

Go to the documentation of this file.

/*

    This file is part of Kitlist, a program to maintain a simple list
    of items and assign items to one or more categories.

    Copyright (C) 2008,2009 Frank Dean

    Kitlist is free software: 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 3 of the License, or
    (at your option) any later version.

    Kitlist 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.

    You should have received a copy of the GNU General Public License
    along with Kitlist.  If not, see <http://www.gnu.org/licenses/>.

*/

#include <cassert>
#include <iostream>
#include <sstream>
#include "kitlistpgsqldao.hpp"

#ifdef HAVE_LIBPQXX

using namespace std;
using namespace PGSTD;
using namespace pqxx;


class AddItem : public transactor<> {
    Item*  m_item;
    long m_new_id;
public:
    AddItem(Item* item) : transactor<>("AddItem"), m_item(item) {}

    void operator()(argument_type &T) {
        T.exec("INSERT INTO item (description, checked) values('" +
               T.esc(m_item->get_description()) + "', " +
               (m_item->get_checked() ? "true" : "false") + ")");
        result R(T.exec("SELECT lastval()"));
        R.front()[0].to(m_new_id);
    }

    void on_commit() {
        m_item->set_id(m_new_id);
    }

};


class AssociateItemWithCategory : public transactor<> {
    long m_id;
    long m_cat_id;
public:
    AssociateItemWithCategory(long id, long cat_id) :
        transactor<>("AssociateItemWithCategory"), m_id(id), m_cat_id(cat_id) {}

    void operator()(argument_type &T) {
        ostringstream os;
        os << "INSERT INTO category_item (item, category) VALUES('" << m_id << "', '" << m_cat_id << "')";
        T.exec(os.str());
    }

};


class DeleteItem : public transactor<> {
    long m_id;
public:
    DeleteItem(long id) : transactor<>("DeleteItem"), m_id(id) {}

    void operator()(argument_type &T) {
        ostringstream os;
        os << "DELETE FROM item WHERE id='" << m_id << '\'';
        T.exec(os.str());
    }

};


class UpdateItemCheckedState : public transactor<> {
    ItemContainer& m_items;
public:
    UpdateItemCheckedState(ItemContainer& items)
        : transactor<>("UpdateItemCheckedState"), m_items(items) {}

    void operator()(argument_type &T) {
        for (ItemIter i = m_items.begin(); i != m_items.end(); ++i) {
            ostringstream os;
            os << "UPDATE item SET checked="
               << ((*i)->get_checked() ? "true" : "false")
               << " WHERE id=" << (*i)->get_id();
            T.exec(os.str());
        }
    }

};


class RemoveItemFromCategory : public transactor<> {
    long m_id;
    long m_cat_id;
public:
    RemoveItemFromCategory(long id, long cat_id) :
        transactor<>("RemoveItemFromCategory"), m_id(id), m_cat_id(cat_id) {}

    void operator()(argument_type &T) {
        ostringstream os;
        os << "DELETE FROM category_item WHERE item='"
           << m_id << "' AND category='" << m_cat_id << '\'';
        T.exec(os.str());
    }

};


class SetItemFlag : public transactor<> {
    long m_id;
    bool m_flag;
public:
    SetItemFlag(long id, bool flag) : transactor<>("SetItemFlag"), m_id(id), m_flag(flag) {}

    void operator()(argument_type &T) {
        ostringstream os;
        os << "UPDATE item SET checked=" << (m_flag ? "true" : "false") << " WHERE id='" << m_id << '\'';
        T.exec(os.str());
    }

};


class SetCategoryFlag : public transactor<> {
    long m_cat_id;
    bool m_flag;
public:
    SetCategoryFlag(long cat_id, bool flag) : transactor<>("SetCategoryFlag"), m_cat_id(cat_id), m_flag(flag) {}

    void operator()(argument_type &T) {
        ostringstream os;
        os << "UPDATE item SET checked=" << (m_flag ? "true" : "false") << " FROM category_item AS c WHERE item.id=c.item AND c.category='" << m_cat_id << "'";
        T.exec(os.str());
    }

};


class SetAllFlags : public transactor<> {
    bool m_flag;
public:
    SetAllFlags(bool flag) : transactor<>("SetAllFlags"), m_flag(flag) {}

    void operator()(argument_type &T) {
        ostringstream os;
        os << "UPDATE item SET checked=" << (m_flag ? "true" : "false");
        T.exec(os.str());
    }

};


class DeleteCategory : public transactor<> {
    long m_id;
public:
    DeleteCategory(long id) : transactor<>("DeleteCategory"), m_id(id) {}

    void operator()(argument_type &T) {
        ostringstream os;
        os << "DELETE FROM category WHERE id='" << m_id << '\'';
        T.exec(os.str());
    }

};


class NewCategory : public transactor<> {
    string m_name;
    long* m_new_id;
    result R;
public:
    NewCategory(string name, long* id) : transactor<>("NewCategory"), m_name(name), m_new_id(id) {}

    void operator()(argument_type &T) {
        T.exec("INSERT INTO category (NAME) values('" +  T.esc(m_name) + "')");
        R = T.exec("SELECT lastval()");
    }

    void on_commit() {
        R.front()[0].to(*m_new_id);
    }

};


class AddCategoryItem : public transactor<> {
    Item* m_item;
    long m_cat_id;
    long m_new_id;
public:
    AddCategoryItem(Item* item, long cat_id)
        : transactor<>("AddCategoryItem"), m_item(item), m_cat_id(cat_id) {}

    void operator()(argument_type &T) {
        T.exec("INSERT INTO item (description, checked) values('" +
               T.esc(m_item->get_description()) + "', " +
               (m_item->get_checked() ? "true" : "false") + ")");
        result R(T.exec("SELECT lastval()"));
        result::reference ref = R.front();
        m_new_id =ref[0].as(long());
        ostringstream os;
        os << "INSERT INTO category_item (category, item) VALUES('"
           << m_cat_id << "', '" << m_new_id << "')";
        T.exec(os.str());
    }

    void on_commit() {
        m_item->set_id(m_new_id);
    }

};



class CopyItemsToCategory : public transactor<> {
    long m_from_cat_id;
    long m_to_cat_id;
    item_choice m_item_choice;
public:
    CopyItemsToCategory(long from_cat_id, long to_cat_id, item_choice choice)
        : transactor<>("CopyItemsToCategory"),
          m_from_cat_id(from_cat_id),
          m_to_cat_id(to_cat_id),
          m_item_choice(choice) {}

    void operator()(argument_type &T) {
        ostringstream os;
        // Insert element
        os << "INSERT INTO category_item (item, category) ";

        // Selection statement for list of items
        if (m_from_cat_id >= 0) {
            switch (m_item_choice) {
            case CHECKED_ITEMS:
            case UNCHECKED_ITEMS:
                os << "SELECT ci.item AS item, '" << m_to_cat_id << "' AS category FROM category_item AS ci "
                   << "JOIN item i ON ci.item=i.id ";
                    break;
            default:
                os << "SELECT ci.item AS item, '" << m_to_cat_id << "' AS category FROM category_item AS ci ";
            }
            os << "WHERE ci.category='" << m_from_cat_id << "' ";

            switch (m_item_choice) {
            case CHECKED_ITEMS:
                os << "AND i.checked=true ";
                break;
            case UNCHECKED_ITEMS:
                os << "AND i.checked=false ";
                break;
            }
            os << "AND ci.item NOT IN ";
        } else {
            os << "SELECT i.id AS item, '" << m_to_cat_id << "' FROM item AS i WHERE ";
            switch (m_item_choice) {
            case CHECKED_ITEMS:
                os << "i.checked=true AND ";
                break;
            case UNCHECKED_ITEMS:
                os << "i.checked=false AND ";
                break;
            }
            os << "i.id NOT IN ";
        }

        // Inner select, avoid items already in the list
        os << "(SELECT c2.item FROM category_item AS c2 WHERE c2.category='" << m_to_cat_id << "')";

        T.exec(os.str());
    }

};


class GetNextItemId : public transactor<> {
    long* m_id;
    result R;
public:
    GetNextItemId(long* id) : transactor<>("GetNextItemId"), m_id(id) {}

    void operator()(argument_type &T) {
        R = T.exec("SELECT nextval('item_sequence')");
    }

    void on_commit() {
        R.front()[0].to(*m_id);
    }

};


class GetNextCategoryId : public transactor<> {
    long* m_cat_id;
    result R;
public:
    GetNextCategoryId(long* cat_id) : transactor<>("GetNextCategoryId"), m_cat_id(cat_id) {}

    void operator()(argument_type &T) {
        R = T.exec("SELECT nextval('category_sequence')");
    }

    void on_commit() {
        R.front()[0].to(*m_cat_id);
    }

};




class GetAllItems : public transactor<nontransaction> {
    ItemContainer* m_items;
    item_choice m_item_choice;
    result R;
public:
    GetAllItems(ItemContainer* items, item_choice choice)
        : transactor<nontransaction>("GetAllItems"), m_items(items), m_item_choice(choice) {}

    void operator()(argument_type &T) {
        // Perform a query on the database, storing result tuples in R.
        ostringstream os;
        os << "SELECT id, description, checked FROM item";
        switch (m_item_choice) {
        case CHECKED_ITEMS:
            os << " WHERE checked=true";
            break;
        case UNCHECKED_ITEMS:
            os << " WHERE checked=false";
            break;
        }
        os << " ORDER BY id";
        R = T.exec(os.str());
    }

    void on_commit() {
        // Process each successive result tuple
        for (result::const_iterator c = R.begin(); c != R.end(); ++c) {
            Item* item = new Item;
            item->set_id(c[0].as(long()));
            item->set_description(c[1].as(string()));
            item->set_checked(c[2].as(bool()));
            m_items->push_back(item);
        }
    }

};


class LoadModel : public pqxx::transactor<pqxx::nontransaction> {
    KitModel& m_kitmodel;
    CategoryMap* m_categories;
    pqxx::result R;
public:
    LoadModel(KitModel& kitmodel) : pqxx::transactor<pqxx::nontransaction>("LoadModel"), m_kitmodel(kitmodel) {}

    void operator()(argument_type &T) {
        R = T.exec("SELECT i.id AS item_id, c.id AS category_id, i.description, c.name AS category, i.checked FROM item AS i FULL OUTER JOIN category_item AS ci ON i.id=ci.item FULL OUTER JOIN category AS c ON c.id=ci.category");
    }

    void on_commit() {
        for (result::const_iterator c = R.begin(); c != R.end(); ++c) {
            // item id will be null where there is an empty category
            ModelItem* item = 0;
            if (!c[0].is_null()) {
                long item_id = c[0].as(long());
                // Create a new item if we do not already have it
                item = m_kitmodel.find_item(item_id);
                if (!item) {
                    item = new ModelItem;
                    item->set_id(item_id);
                    item->set_description(c[2].as(string()));
                    item->set_checked(c[4].as(bool()));
                    m_kitmodel.add_item(item);
                }
            }

            // Do we have a category?
            if (!c[1].is_null()) {
                long cat_id = c[1].as(long());
                // Create a new category if we do not already have one
                ModelCategory* category = 0;
                category = m_kitmodel.find_category(cat_id);
                if (!category) {
                    category = new ModelCategory;
                    category->set_id(cat_id);
                    category->set_name(c[3].as(string()));
                    m_kitmodel.add_category(category);
                }
                if (item)
                    category->add_item(item);
            }

        } // for
        m_kitmodel.reset();
    }

};


class SaveModel : public transactor<> {
private:
    ModelItemContainer m_items;
    ModelCategoryContainer m_categories;
protected:
    KitModel* m_model;
public:
    SaveModel(KitModel* model) : transactor<>("SaveModel"), m_model(model), m_items(0) {}


    bool on_foreach_item(ModelItem& item) {
        m_items.push_back(&item);
        return false;
    }


    bool on_foreach_category(ModelCategory& category) {
        m_categories.push_back(&category);
        return false;
    }


    void operator()(argument_type &T) {

        // Extract the list of items from the model
        m_model->foreach_item( sigc::mem_fun(*this, &SaveModel::on_foreach_item) );

        // Insert/Update/Delete items
        for (ModelItemIter i = m_items.begin(); i != m_items.end(); ++i) {
            ModelItem* item = (*i);
            if (item->is_dirty()) {
                ostringstream os;
                if (item->is_new()) {
                    os << "INSERT INTO item (id, description, checked) values("
                       << item->get_id() << ", '"
                       << T.esc(item->get_description()) << "', "
                       << (item->get_checked() ? "true" : "false") << ")";
                } else if (item->is_deleted()) {
                    os << "DELETE FROM item WHERE id="
                       << item->get_id();
                } else {                    
                    os << "UPDATE item SET description='"
                       << T.esc(item->get_description()) << "', checked="
                       << (item->get_checked() ? "true" : "false")
                       << " WHERE id=" << item->get_id();
                }
                cout << "Query: " << os.str() << endl;
                T.exec(os.str());
            }
        }

        // Extract a list of categories from the model
        m_model->foreach_category( sigc::mem_fun(*this, &SaveModel::on_foreach_category) );

        // Insert/Update/Delete categories
        for (ModelCategoryIter i = m_categories.begin(); i != m_categories.end(); ++i) {
            ModelCategory* cat = (*i);
            if (cat->is_dirty()) {
                ostringstream os;
                if (cat->is_new()) {
                    os << "INSERT INTO category (id, name) values("
                       << cat->get_id() << ", '"
                       << T.esc(cat->get_name()) << "')";
                } else if (cat->is_deleted()) {
                    os << "DELETE FROM category WHERE id="
                       << cat->get_id();
                } else {
                    os << "UPDATE category SET name='"
                       << T.esc(cat->get_name())
                       << "' WHERE id=" << cat->get_id();
                }
                cout << "Query: " << os.str() << endl;
                T.exec(os.str());
            }
            // Any item or category deletes will have cascaded to the
            // relationship table, category_item, so just need to deal
            // with potential items added to the category
            if (!cat->is_deleted()) {
                ItemMap* items = cat->get_removed_children();
                for (ItemMapIter i2 = items->begin(); i2 != items->end(); i2++) {
                    ModelItem* item = (*i2).second;
                    ostringstream os;
                    os << "DELETE FROM category_item WHERE item="
                       << item->get_id()
                       << " AND category="
                       << cat->get_id();
                    cout << "Query: " << os.str() << endl;
                    T.exec(os.str());
                }
                items = cat->get_added_children();
                for (ItemMapIter i2 = items->begin(); i2 != items->end(); i2++) {
                    ModelItem* item = (*i2).second;
                    if (!item->is_deleted()) {
                        ostringstream os;
                        os << "INSERT INTO category_item (item, category) values ("
                           << item->get_id() << ", "
                           << cat->get_id() << ")";
                        cout << "Query: " << os.str() << endl;
                        T.exec(os.str());
                    }
                }
            }
        }// for
    
    }


    void on_commit() {
        // Remove deleted items from categories etc.
        m_model->purge();
        // Clear the dirty, deleted and new flags etc.
        m_model->reset();
    }

};



class LoadCategory : public transactor<nontransaction> {
    Category *m_category;
    item_choice m_item_choice;
    result R;
public:
    LoadCategory(Category *category, item_choice choice)
        : transactor<nontransaction>("LoadCategory"), m_category(category), m_item_choice(choice) {}

    void operator()(argument_type &T) {
        // Perform a query on the database, storing result tuples in R.
        ostringstream os;
        os << "SELECT id, description, checked FROM item AS i "
           << "JOIN category_item AS ci ON i.id=ci.item "
           << "WHERE ci.category = '" << m_category->get_id() << "'";

        switch (m_item_choice) {
        case CHECKED_ITEMS:
            os << " AND i.checked=true";
            break;
        case UNCHECKED_ITEMS:
            os << " AND i.checked=false";
            break;
        }

        os << " ORDER BY i.id";
        R = T.exec(os.str());
    }

    void on_commit() {
        // Process each successive result tuple
        for (result::const_iterator c = R.begin(); c != R.end(); ++c) {
            Item* i = new Item;
            i->set_id(c[0].as(long()));
            i->set_description(c[1].as(string()));
            i->set_checked(c[2].as(bool()));
            m_category->add_item(i); 
        }
    }

};



class GetCategories : public transactor<nontransaction> {
    result m_result;
    CategoryContainer* m_categories;
public:
    GetCategories(CategoryContainer* categories)
        : transactor<nontransaction>("GetCategories"), m_categories(categories) {}

    void operator()(argument_type &T) {
        m_result = T.exec("SELECT id, name FROM category ORDER BY id");
    }

    void on_commit() {
        if (!m_result.empty()) {
            for (result::const_iterator c = m_result.begin(); c != m_result.end(); ++c) {
                Category* cat = new Category;
                cat->set_id(c[0].as(long()));
                cat->set_name(c[1].as(string()));
                m_categories->push_back(cat);
            }
        }
    }

};


KitListPgsqlDao::KitListPgsqlDao(int verbose) : KitListDao(verbose) {
    connect();
}

KitListPgsqlDao::KitListPgsqlDao(string dbname, int verbose) : KitListDao(verbose), m_dbname(dbname) {
    connect();
}

KitListPgsqlDao::KitListPgsqlDao(string dbname, string user, string port, int verbose)
    : KitListDao(verbose), m_dbname(dbname), m_user(user), m_port(port) {
    connect();
}

KitListPgsqlDao::~KitListPgsqlDao() {
    delete m_db_connection;
    if (is_verbose())
        cout << "Connection closed" << endl;
}

void KitListPgsqlDao::connect() {
    // Set up a connection to the backend.
    string cs;
    if (m_dbname.size() > 0) cs += "dbname=" + m_dbname;
    if (m_user.size() > 0) cs += " user=" + m_user;
    if (m_port.size() > 0) cs += " port=" + m_port;
    if (is_verbose()) {
        cout << "Connect string: \"" << cs << '"' << endl;
    }
    m_db_connection = new connection(cs);
    if (is_verbose()) {
        cout << "Connected to database: " <<  m_db_connection->dbname() << endl
             << "Username: " << m_db_connection->username() << endl
             << "Hostname: " << (m_db_connection->hostname() ? m_db_connection->hostname() : "(null)") << endl
             << "Socket: " << m_db_connection->sock() << endl
             << "Backend PID: " << m_db_connection->backendpid() << endl
             << "Port: " << m_db_connection->port() << endl
             << "Backend version: " << m_db_connection->server_version() << endl
             << "Protocol version: " << m_db_connection->protocol_version() << endl << endl;
    }
}


Category* KitListPgsqlDao::get_category(long cat_id, item_choice choice) {
    Category* retval = new Category;
    retval->set_id(cat_id);
    m_db_connection->perform(LoadCategory(retval, choice));
    return retval;
}


ItemContainer* KitListPgsqlDao::get_all_items(item_choice choice) {
    ItemContainer* retval = new ItemContainer;
    GetAllItems g = GetAllItems(retval, choice);
    m_db_connection->perform(g);
    return retval;
}


long KitListPgsqlDao::add_item(const std::string name) {
    Item item;
    item.set_description(name);
    m_db_connection->perform(AddItem(&item));
    return item.get_id();
}


long KitListPgsqlDao::add_item(const std::string name, long cat_id) {
    Item item;
    item.set_description(name);
    m_db_connection->perform(AddCategoryItem(&item, cat_id));
    return item.get_id();
}


void KitListPgsqlDao::append_items_to_category(long to_cat_id, long from_cat_id, item_choice choice) {
    m_db_connection->perform(CopyItemsToCategory(from_cat_id, to_cat_id, choice));
}


void KitListPgsqlDao::associate_item_with_category(long id, long cat_id) {
    m_db_connection->perform(AssociateItemWithCategory(id, cat_id));
}


CategoryContainer KitListPgsqlDao::get_categories() {
    CategoryContainer retval;
    m_db_connection->perform(GetCategories(&retval));
    return retval;
}


long KitListPgsqlDao::new_category(const std::string name) {
    long retval;
    m_db_connection->perform(NewCategory(name, &retval));
    return retval;
}


void KitListPgsqlDao::delete_item(long id) {
    m_db_connection->perform(DeleteItem(id));
}


void KitListPgsqlDao::update_item_checked_state(ItemContainer& items) {
    m_db_connection->perform(UpdateItemCheckedState(items));
}


void KitListPgsqlDao::remove_item_from_category(long id, long cat_id) {
    m_db_connection->perform(RemoveItemFromCategory(id, cat_id));
}


long KitListPgsqlDao::get_next_item_id() {
    long retval;
    m_db_connection->perform(GetNextItemId(&retval));
    return retval;
}


long KitListPgsqlDao::get_next_category_id() {
    long retval;
    m_db_connection->perform(GetNextCategoryId(&retval));
    return retval;
}


void KitListPgsqlDao::delete_category(long id) {
    m_db_connection->perform(DeleteCategory(id));
}


void KitListPgsqlDao::set_item_flag(long id) {
    m_db_connection->perform(SetItemFlag(id, true));
}


void KitListPgsqlDao::unset_item_flag(long id) {
    m_db_connection->perform(SetItemFlag(id, false));
}


void KitListPgsqlDao::set_category_flag(long id) {
    m_db_connection->perform(SetCategoryFlag(id, true));
}


void KitListPgsqlDao::unset_category_flag(long id) {
    m_db_connection->perform(SetCategoryFlag(id, false));
}


void KitListPgsqlDao::set_all_flags() {
    m_db_connection->perform(SetAllFlags(true));
}


void KitListPgsqlDao::unset_all_flags() {
    m_db_connection->perform(SetAllFlags(false));
}


KitModel* KitListPgsqlDao::get_model() {
    KitModel* retval = new KitModel();
    m_db_connection->perform(LoadModel(*retval));
    return retval;
}


void KitListPgsqlDao::save_model(KitModel* model) {
    m_db_connection->perform(SaveModel(model));
}

#endif //HAVE_LIBPQXX

---