metadata_sql.h

Go to the documentation of this file.

/***************************************************************************
 *
 * Authors:     J.M. De la Rosa Trevin (jmdelarosa@cnb.csic.es)
 *
 * Unidad de  Bioinformatica of Centro Nacional de Biotecnologia , CSIC
 *
 * This program 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 2 of the License, or
 * (at your option) any later version.
 *
 * This program 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 this program; if not, write to the Free Software
 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA
 * 02111-1307  USA
 *
 *  All comments concerning this program package may be sent to the
 *  e-mail address 'xmipp@cnb.csic.es'
 ***************************************************************************/

/* SQL helpers for MetaDataDb */

#ifndef CORE_METADATASQL_H
#define CORE_METADATASQL_H

#include "xmipp_strings.h"
#include "metadata_label.h"
#include "xmipp_error.h"
#include "metadata_sql_operations.h"
#include "metadata_static.h"
#include "metadata_query.h"

class MDSqlStaticInit;
class MDQuery;
class MetaDataDb;
class MDCache;
class FileName;
class sqlite3_stmt;
class sqlite3_context;
class sqlite3_value;
class sqlite3;

//#include "metadata.h"

/* return number of tables from a metadata file saved as sqlite */
int getBlocksInMetaDataFileDB(const FileName &inFile, StringVector& blockList);

/*support for the REGEXP operator in sqlite*/
void sqlite_regexp(sqlite3_context* context, int argc, sqlite3_value** values);

class MDSql
{
public:
    static void dumpToFile(const FileName &fileName);
    static void sqlTimeOut(int miliSeconds);

    static bool activateMathExtensions(void);

    /* activate regular expressions in sql */
    static bool activateRegExtensions(void);

    bool  deactivateThreadMuting(void);
    bool  activateThreadMuting(void);

private:
    void copyTableToFileDB(const FileName blockname, const FileName &fileName);

    void copyTableFromFileDB(const FileName blockname,
                             const FileName filename,
                             const std::vector<MDLabel> *desiredLabels,
                             const size_t maxRows=0
                             );
    bool createMd();

    bool clearMd();

    size_t getObjId();

    size_t addRow();

    bool addColumn(MDLabel column);

    bool renameColumn(const std::vector<MDLabel> &oldLabel, const std::vector<MDLabel> &newlabel);

    // T is either "const MDObject*" or "MDObject*"
    template <typename T>
    bool setObjectValues(int id, const std::vector<T> &columnValues, const std::vector<MDLabel> *desiredLabels=NULL);

    bool setObjectValue(const int objId, const MDObject &value);

    bool setObjectValue(const MDObject &value);

    bool getObjectsValues(const std::vector<MDLabel> &labels, std::vector<MDObject> &values);

    bool getObjectValue(const int objId, MDObject  &value);

    void selectObjects(std::vector<size_t> &objectsOut, const MDQuery *queryPtr = NULL);

    size_t size(void);
    size_t deleteObjects(const MDQuery *queryPtr = NULL);

    size_t copyObjects(MDSql * sqlOut,
                       const MDQuery *queryPtr = NULL) const;
    size_t copyObjects(MetaDataDb * mdPtrOut,
                       const MDQuery *queryPtr = NULL) const;

    void aggregateMd(MetaDataDb *mdPtrOut,
                     const std::vector<AggregateOperation> &operations,
                     const std::vector<MDLabel> &operateLabel);

    void aggregateMdGroupBy(MetaDataDb *mdPtrOut,
                            const AggregateOperation operation,
                            const std::vector<MDLabel> &groupByLabels ,
                            const  MDLabel operateLabel,
                            const  MDLabel resultLabel);

    double aggregateSingleDouble(const AggregateOperation operation,
                                 MDLabel operateLabel);


    size_t aggregateSingleSizeT(const AggregateOperation operation,
                                MDLabel operateLabel);

    void indexModify(const std::vector<MDLabel> &columns, bool create=true);

    size_t firstRow();
    size_t lastRow();
    size_t nextRow(size_t currentRow);
    size_t previousRow(size_t currentRow);

    int columnMaxLength(MDLabel column);

    void setOperate(MetaDataDb *mdPtrOut, const std::vector<MDLabel> &columns, SetOperation operation);
    void setOperate(const MetaDataDb *mdInLeft, const MetaDataDb *mdInRight, const std::vector<MDLabel> &columnsLeft,
            const std::vector<MDLabel> &columnsRight, SetOperation operation);
    bool operate(const String &expression);

    // FIXME this should not be necessary, as sqlite is build with multi-thread support. However, multiple access
    // to database from multiple threads causes application crash
    bool beThreadSafe;


    MDSql(MetaDataDb *md);
    ~MDSql();

    static int table_counter;
    static sqlite3 *db;

    static MDSqlStaticInit initialization; //Just for initialization

    static bool sqlBegin();
    static void sqlEnd();
    static bool sqlBeginTrans();
    static bool sqlCommitTrans();
    int getUniqueId();

    bool dropTable();
    bool createTable(const std::vector<MDLabel> * labelsVector = NULL, bool withObjID=true);
    bool insertValues(double a, double b);
    bool initializeSelect( bool addWhereObjId, const std::vector<MDLabel> &labels);
    bool initializeInsert(const std::vector<MDLabel> *labels, const std::vector<MDObject*> &values);
    void finalizePreparedStmt(void);
    void prepareStmt(const std::stringstream &ss, sqlite3_stmt *stmt);
    bool execSingleStmt(const std::stringstream &ss);
    bool execSingleStmt(sqlite3_stmt *&stmt, const std::stringstream *ss = NULL);
    size_t execSingleIntStmt(const std::stringstream &ss);
    double execSingleDoubleStmt(const std::stringstream &ss);

    String tableName(const int tableId) const;

    bool    bindStatement( size_t id);
    int     bindValue(sqlite3_stmt *stmt, const int position, const MDObject &valueIn);
    void    extractValue(sqlite3_stmt *stmt, const int position, MDObject &valueOut);

    static char *errmsg;
    static const char *zLeftover;
    static int rc;
    static sqlite3_stmt *stmt;

    static std::stringstream preparedStream;    // Stream.
    static sqlite3_stmt * preparedStmt; // SQL statement.

    int tableId;
    MetaDataDb *myMd;
    MDCache *myCache;

    friend class MDSqlStaticInit;
    friend class MetaDataDb;
    friend class MDIterator;
    bool equals(const MDSql &op);

}
;//close class MDSql


class MDCache
{
public:
    sqlite3_stmt *iterStmt;
    std::map<MDLabel, sqlite3_stmt*> getValueCache;
    std::map<MDLabel, sqlite3_stmt*> setValueCache;
    sqlite3_stmt *addRowStmt;

    MDCache();
    ~MDCache();
    void clear();
};

class MDSqlStaticInit
{
private:
    MDSqlStaticInit()
    {
        MDSql::sqlBegin();
    }//close constructor

    ~MDSqlStaticInit()
    {
        MDSql::sqlEnd();
    }//close destructor

    friend class MDSql;
}
;//close class MDSqlStaticInit

#endif