"Fossies" - the Fresh Open Source Software Archive

Member "augustus-3.3.3/src/sqliteDB.cc" (22 May 2019, 6495 Bytes) of package /linux/misc/augustus-3.3.3.tar.gz:


As a special service "Fossies" has tried to format the requested source page into HTML format using (guessed) C and C++ source code syntax highlighting (style: standard) with prefixed line numbers and code folding option. Alternatively you can here view or download the uninterpreted source code file. For more information about "sqliteDB.cc" see the Fossies "Dox" file reference documentation and the latest Fossies "Diffs" side-by-side code changes report: 3.3.2_vs_3.3.3.

    1 /*
    2  * sqliteDB.cc
    3  *
    4  * License: Artistic License, see file LICENSE.TXT or 
    5  *          https://opensource.org/licenses/artistic-license-1.0
    6  * 
    7  * Description: wrapper class around the SQLite interface
    8  */
    9 
   10 #include "sqliteDB.hh"
   11 #include "hints.hh"
   12 
   13 #include <iostream>
   14 #include <stdlib.h>
   15 
   16 void SQLiteDB::open(OpenMode mode){
   17     
   18     int flag = SQLITE_OPEN_READONLY;
   19     if(mode == rw)
   20     flag = SQLITE_OPEN_READWRITE;
   21     else if(mode == crw)
   22     flag = SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE;
   23 
   24     cout <<  "Trying to open database " << dbfile << endl;
   25     if(sqlite3_open_v2(dbfile, &database, flag, NULL) != SQLITE_OK){ 
   26     cerr << "Could not open database "<< dbfile << endl;
   27     cerr << error() << endl;
   28     exit(1);
   29     }
   30 }
   31 
   32 void SQLiteDB::exec(const char *sql){
   33 
   34     Statement statement(this);
   35     statement.prepare(sql);
   36     statement.step();
   37 }
   38 
   39 void SQLiteDB::beginTransaction(){
   40     exec("BEGIN TRANSACTION");
   41 }
   42 
   43 void SQLiteDB::endTransaction(){
   44     exec("END TRANSACTION");
   45 }
   46 
   47 
   48 void SQLiteDB::close(){
   49     sqlite3_close(database);   
   50 }
   51 
   52 bool SQLiteDB::tableExists(string table_name){
   53 
   54     Statement stmt(this);
   55     stmt.prepare("SELECT count(*) FROM sqlite_master WHERE type='table' AND name=?1;");
   56     stmt.bindText(1,table_name.c_str());
   57     if(stmt.nextResult()){
   58         if (stmt.boolColumn(0)){
   59         return true;
   60     }
   61     }
   62     return false;
   63 }
   64 
   65 /*
   66  * create table `genomes` if it does not already exist
   67  * the offset is the start position in the file
   68  * the streamsize is the chunk length in the file (e.g. the
   69  * length of the sequence segment including newlines)
   70  */
   71 void SQLiteDB::createTableGenomes(){
   72     const char *sql="CREATE TABLE IF NOT EXISTS genomes ( \
   73         seqid INTEGER PRIMARY KEY AUTOINCREMENT, \
   74         seqnr INTEGER NOT NULL, \
   75         speciesid INTEGER NOT NULL, \
   76         start INTEGER NOT NULL, \
   77         end INTEGER NOT NULL, \
   78         offset UNSIGNED BIG INT NOT NULL, \
   79         streamsize INTEGER NOT NULL, \
   80         FOREIGN KEY (speciesid,seqnr) REFERENCES seqnames(speciesid,seqnr) );";
   81     exec(sql);
   82 }
   83 /*
   84  * create table 'speciesnames' if it does not already exist
   85  */
   86 void SQLiteDB::createTableSpeciesnames(){
   87     const char* sql = "CREATE TABLE IF NOT EXISTS speciesnames (\
   88         speciesid INTEGER PRIMARY KEY AUTOINCREMENT,\
   89         speciesname TEXT UNIQUE NOT NULL);";
   90     exec(sql);
   91 }
   92 /*
   93  * create table 'seqnames' if it does not already exist     
   94  */
   95 void SQLiteDB::createTableSeqnames(){
   96     const char* sql = "CREATE TABLE IF NOT EXISTS seqnames ( \
   97          seqnr INTEGER PRIMARY KEY AUTOINCREMENT, \
   98          speciesid INTEGER NOT NULL REFERENCES speciesnames(speciesid), \
   99          seqname TEXT NOT NULL, \
  100          UNIQUE(speciesid,seqname));";
  101     exec(sql);
  102 }
  103 
  104 /*
  105  * create table 'hints' if it does not already exist
  106  */
  107 void SQLiteDB::createTableHints(){
  108     const char* sql = "CREATE TABLE IF NOT EXISTS hints (\
  109          hintid INTEGER PRIMARY KEY AUTOINCREMENT, \
  110          speciesid INTEGER NOT NULL, \
  111          seqnr INTEGER NOT NULL, \
  112          source TEXT, \
  113          start INTEGER NOT NULL, \
  114          end INTEGER NOT NULL, \
  115          score REAL DEFAULT 0.0, \
  116          type INTEGER CHECK( type >=0 AND type < 17) NOT NULL, \
  117          strand TEXT CHECK( strand IN ('+','-','.') ) DEFAULT '.', \
  118          frame TEXT CHECK( frame IN ('0','1','2','.') ) DEFAULT '.', \
  119          priority INTEGER DEFAULT -1, \
  120          grp TEXT DEFAULT '', \
  121          mult INTEGER CHECK( mult >= 1) DEFAULT 1, \
  122          esource TEXT NOT NULL, \
  123          FOREIGN KEY (speciesid,seqnr) REFERENCES seqnames(speciesid,seqnr));";
  124     exec(sql);
  125 }
  126 
  127 void SQLiteDB::createTableFeatureTypes(){
  128 
  129     const char* sql = "CREATE TABLE IF NOT EXISTS featuretypes (\
  130          typeid INTEGER PRIMARY KEY, \
  131          typename TEXT CHECK( typename IN ('start','stop','ass','dss','tss','tts','exonpart','exon','intronpart','intron', \
  132          'irpart','CDS','CDSpart','UTR','UTRpart','nonexonpart','genicpart') ) NOT NULL);";
  133     exec(sql);
  134 
  135     Statement statement(this);
  136     
  137     beginTransaction();
  138     statement.prepare("INSERT OR IGNORE INTO featuretypes VALUES (?1,?2);");
  139     for(int i = 0; i < NUM_FEATURE_TYPES; i++){
  140     const char* name = featureTypeNames[i];
  141     statement.bindInt(1,i);
  142     statement.bindText(2,name);
  143     statement.step();
  144     statement.reset();
  145     }
  146     endTransaction();
  147 }
  148 
  149 int SQLiteDB::getSpeciesID(string species, bool clean, bool noInsert){
  150 
  151     Statement stmt(this);
  152     stmt.prepare("SELECT speciesid FROM speciesnames WHERE speciesname=?1;");
  153     stmt.bindText(1,species.c_str());
  154     if(stmt.nextResult()){
  155         int id = stmt.intColumn(0);
  156         if(clean){ // remove all existing entries for that species
  157         deleteHints(id);
  158         deleteGenome(id);
  159         deleteSeqNames(id);
  160         cout << "Deleted existing genome + hints for " << species << " from database " << endl;
  161         }      
  162         return id;
  163     }
  164     else{
  165         if(noInsert)
  166       return -1;
  167         string sql = "INSERT INTO speciesnames (speciesname) VALUES (\"" + species + "\")";
  168         exec(sql.c_str());
  169         return lastInsertID();
  170     }
  171 }
  172 
  173 void SQLiteDB::deleteHints(int speciesid){
  174     string sql = "DELETE FROM hints WHERE speciesid = (\"" + itoa(speciesid) + "\")";
  175     exec(sql.c_str());
  176 }
  177 
  178 void SQLiteDB::deleteGenome(int speciesid){
  179     string sql = "DELETE FROM genomes WHERE speciesid = (\"" + itoa(speciesid) + "\")";
  180     exec(sql.c_str());
  181 
  182 }
  183 
  184 void SQLiteDB::deleteSeqNames(int speciesid){
  185     string sql = "DELETE FROM seqnames WHERE speciesid = (\"" + itoa(speciesid) + "\")";
  186     exec(sql.c_str());
  187 }
  188 
  189 void Statement::prepare(const char *sql){
  190 
  191     if (sqlite3_prepare_v2(database, sql, -1, &stmt, 0) != SQLITE_OK){
  192         throw error();
  193     }
  194 }
  195 
  196 void Statement::step(){
  197 
  198     if (sqlite3_step(stmt) != SQLITE_DONE){
  199         throw error();
  200     }
  201 }
  202 
  203 void Statement::bindInt(int idx, int x){
  204 
  205     if(sqlite3_bind_int(stmt, idx, x) != SQLITE_OK){
  206     throw error();
  207     }
  208 }
  209 
  210 void Statement::bindInt64(int idx, uint64_t x){
  211 
  212     if(sqlite3_bind_int64(stmt, idx, x) != SQLITE_OK){
  213     throw error();
  214     }
  215 }
  216 
  217 void Statement::bindDouble(int idx, double d){
  218 
  219     if(sqlite3_bind_double(stmt, idx, d) != SQLITE_OK){
  220     throw error();
  221     }
  222 }
  223 
  224 void Statement::bindText(int idx, const char* text){
  225 
  226     if(sqlite3_bind_text(stmt, idx, text, strlen(text), NULL) != SQLITE_OK){
  227     throw error();
  228     }
  229 }
  230 
  231 bool Statement::nextResult(){
  232     int msg = sqlite3_step(stmt);
  233     if( msg != SQLITE_DONE && msg != SQLITE_ROW )
  234     throw error();
  235     return (msg == SQLITE_ROW);
  236 }