예제 #1
0
TempTable::TempTable(char *cmd, size_t len):
    cmd(cmd),
    len(len)
{
    tmpTableName = "tmp" + std::to_string((long long int)getpid()) + std::to_string((long long int)pthread_self());

    Kompex::SQLiteStatement *p;
    try
    {
        p = new Kompex::SQLiteStatement(cfg->pDb->pDatabase);
        sqlite3_snprintf(len, cmd, "CREATE TABLE IF NOT EXISTS %s(id INT8 NOT NULL);",
                         tmpTableName.c_str());
        p->SqlStatement(cmd);
        /*
        sqlite3_snprintf(CMD_SIZE, cmd, "CREATE INDEX IF NOT EXISTS idx_%s_id ON %s(id);",
                         tmpTableName.c_str(), tmpTableName.c_str());
        p->SqlStatement(cmd);
        */
    }
    catch(Kompex::SQLiteException &ex)
    {
        std::clog<<__func__<<" error: create tmp table: %s"<< ex.GetString()<<std::endl;
        exit(1);
    }
    delete p;
}
예제 #2
0
bool TempTable::clear()
{
    Kompex::SQLiteStatement *pStmt;

    pStmt = new Kompex::SQLiteStatement(cfg->pDb->pDatabase);
    sqlite3_snprintf(len,cmd,"DELETE FROM %s;",tmpTableName.c_str());
    try
    {
        pStmt->SqlStatement(cmd);
        pStmt->FreeQuery();
    }
    catch(Kompex::SQLiteException &ex)
    {
        std::clog<<__func__<<" error: " <<ex.GetString()<<std::endl;
    }
    delete pStmt;
    return true;
}
예제 #3
0
int main(int argc, char *argv[])
{
    QCoreApplication app(argc,argv);
    QString pathApp = QCoreApplication::applicationDirPath();

    // check input args
    QStringList inputArgs = app.arguments();
    if(inputArgs.size() < 3)   {
        badInput();
        return -1;
    }
    if(inputArgs[1] == inputArgs[2])   {
        badInput();
        return -1;
    }
    if(inputArgs.size() == 4)   {
        if(inputArgs[3] == "-optimize")   {
            g_optimize = true;
        }
    }

    QDir appDir(pathApp);

    // filter shapefile types
    QStringList filterList;
    filterList << "*.shp" << "*.shx" << "*.dbf" << "*.prj";

    // get shapefiles for admin1
    QDir a1_dir = inputArgs[2];
    QString a1_fileShx,a1_fileShp,a1_fileDbf,a1_filePrj;
    QStringList a1_dirList = a1_dir.entryList(filterList,QDir::Files);

    for(int i=0; i < a1_dirList.size(); i++)   {
        if(a1_dirList[i].contains(".shp"))   {
            a1_fileShp = a1_dir.absoluteFilePath(a1_dirList[i]);
        }
        else if(a1_dirList[i].contains(".shx"))   {
            a1_fileShx = a1_dir.absoluteFilePath(a1_dirList[i]);
        }
        else if(a1_dirList[i].contains(".dbf"))   {
            a1_fileDbf = a1_dir.absoluteFilePath(a1_dirList[i]);
        }
        else if(a1_dirList[i].contains(".prj"))   {
            a1_filePrj = a1_dir.absoluteFilePath(a1_dirList[i]);
        }
    }

    // get dbf file for admin0
    QDir a0_dir = inputArgs[1];
    QString a0_fileDbf;
    QStringList a0_dirList = a0_dir.entryList(QDir::Files);
    for(int i=0; i < a0_dirList.size(); i++)   {
        if(a0_dirList[i].contains(".dbf"))   {
            a0_fileDbf = a0_dir.absoluteFilePath(a0_dirList[i]);
        }
    }

    // get polygons from admin1 shapefile
    QList<QList<Vec2d> > list_a1_polys;

    if(!getPolysFromShapefile(a1_fileShp,list_a1_polys))   {
        return -1;
    }

    // save admin1 polys as east/west images
    appDir.mkpath(pathApp+"/admin1");
    if(!rasterizePolygons(pathApp+"/admin1",list_a1_polys))   {
        return -1;
    }

    // cut images into tiles
    qDebug() << "INFO: Splitting into tiles...";
    QStringList listWestTileFiles;
    QImage * imgWest = new QImage(pathApp+"/admin1/imgW.png");
    appDir.mkpath(pathApp+"/admin1/west");
    if(!splitImageIntoTiles(*imgWest,"admin1/west",listWestTileFiles))   {
        qDebug() << "ERROR: Failed to split image into tiles [west]";
        return -1;
    }
    delete imgWest;

    QStringList listEastTileFiles;
    QImage * imgEast = new QImage(pathApp+"/admin1/imgE.png");
    appDir.mkpath(pathApp+"/admin1/east");
    if(!splitImageIntoTiles(*imgEast,"admin1/east",listEastTileFiles))   {
        qDebug() << "ERROR: Failed to split image into tiles [east]";
        return -1;
    }
    delete imgEast;

    // open new database and create tables
    qDebug() << "INFO: Creating database...";
    Kompex::SQLiteDatabase * pDatabase;
    Kompex::SQLiteStatement * pStmt;

    try   {
        pDatabase = new Kompex::SQLiteDatabase("adminraster.sqlite",
                                               SQLITE_OPEN_READWRITE |
                                               SQLITE_OPEN_CREATE,0);

        pStmt = new Kompex::SQLiteStatement(pDatabase);

        pStmt->SqlStatement("CREATE TABLE IF NOT EXISTS tiles("
                            "id INTEGER PRIMARY KEY NOT NULL UNIQUE,"
                            "png BLOB)");

        pStmt->SqlStatement("CREATE TABLE IF NOT EXISTS admin1("
                            "id INTEGER PRIMARY KEY NOT NULL UNIQUE,"
                            "name TEXT NOT NULL,"
                            "disputed INTEGER NOT NULL,"
                            "admin0 INTEGER,"
                            "sov INTEGER);");

        pStmt->SqlStatement("CREATE TABLE IF NOT EXISTS admin0("
                            "id INTEGER PRIMARY KEY NOT NULL UNIQUE,"
                            "name TEXT NOT NULL);");

        pStmt->SqlStatement("CREATE TABLE IF NOT EXISTS sov("
                            "id INTEGER PRIMARY KEY NOT NULL UNIQUE,"
                            "name TEXT NOT NULL);");
    }
    catch(Kompex::SQLiteException &exception)   {
        qDebug() << "ERROR: SQLite exception creating database:"
                 << QString::fromStdString(exception.GetString());
        return -1;
    }


    // write tile images into database
    qDebug() << "INFO: Writing tiles to database...";
    QStringList listAllTileFiles;
    listAllTileFiles.append(listWestTileFiles);
    listAllTileFiles.append(listEastTileFiles);
    writeTilesToDatabase(listAllTileFiles,pStmt);

    // remove image files
    system("rm -rf admin1");

    // get records from admin0 and admin1 dbf
    qDebug() << "INFO: Writing admin regions to database...";
    writeAdminRegionsToDatabase(a0_fileDbf,a1_fileDbf,pStmt);

    // clean up database
    delete pStmt;
    delete pDatabase;

    return 0;
}
예제 #4
0
int main(int argc, char *argv[])
{
    QCoreApplication myApp(argc, argv);

    // check input args
    QStringList inputArgs = myApp.arguments();

    if(inputArgs.size() < 2)   {
        qDebug() << "Error: No dbf directory: ";
        qDebug() << "Pass the dbf directory as an argument: ";
        qDebug() << "./dbf2sqlite /my/dbfdir";
        return -1;
    }

    // filter shapefile types
    QStringList dbfFilterList;
    dbfFilterList << "*.dbf";

    // check for all files
    QDir dbfDir = inputArgs[1];
    QStringList dbfDirList = dbfDir.entryList(dbfFilterList,
                                              QDir::Files);

    // get all file paths
    QString fileDbf;
    for(int i=0; i < dbfDirList.size(); i++)   {
        if(dbfDirList[i].contains(".dbf"))   {
            fileDbf = dbfDir.absoluteFilePath(dbfDirList[i]);
        }
    }

    // open the database file
    DBFHandle hDBF = DBFOpen(fileDbf.toLocal8Bit().data(),"rb");
    if(hDBF == NULL)   {
        qDebug() << "Error: Could not open dbf file";
        return -1;
    }

    // set fields to keep based on data type
    QStringList listFieldsToKeep;
    QString dbFileName;

    if(ADMIN0)   {
        dbFileName = "admin0.sqlite";
        listFieldsToKeep
                << "ADMIN"          // administrative name of country
                << "ADM0_A3";       // 3 letter abbreviatesion of admin name
    }

    if(ADMIN1)   {
        dbFileName = "admin1.sqlite";
        listFieldsToKeep
                << "OBJECTID"
                << "NAME_1"         // Admin1 region name
                << "VARNAME_1"      // Admin1 alt name (not very reliable)
                << "NL_NAME_1"      // Admin1 region name in national language (not reliable)
                << "Postal";        // 2 Letter Postal Code (not reliable)
    }

    // get number of fields in db
    size_t numRecords = 0;
    numRecords = DBFGetRecordCount(hDBF);

    if(numRecords > 0)
    {   qDebug() << "Info: DBF file has" << numRecords << "records";   }
    else
    {   qDebug() << "Error: DBF file has no records!";   return -1;   }

    // create sqlite database
    qDebug() << "Info: Creating SQLite Database...";
    Kompex::SQLiteDatabase * pDatabase =
            new Kompex::SQLiteDatabase(dbFileName.toStdString(),
                SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE,0);

    Kompex::SQLiteStatement * pStmt =
            new Kompex::SQLiteStatement(pDatabase);

    // create database schema (flat)
    if(ADMIN1)   {
        qDebug() << "Info: Creating database schema for ADMIN1 profile";

        pStmt->SqlStatement("CREATE TABLE IF NOT EXISTS data("
                            "regionid INTEGER PRIMARY KEY NOT NULL UNIQUE,"
                            "name TEXT NOT NULL,"
                            "code TEXT);");

        // regionid <-> internel shape/record id [integer]
        size_t idx_objectid = DBFGetFieldIndex(hDBF,"OBJECTID");

        // name <-> NAME_1 [text]
        size_t idx_name = DBFGetFieldIndex(hDBF,"NAME_1");

        // code <-> Postal [text]
        size_t idx_code = DBFGetFieldIndex(hDBF,"Postal");

        qDebug() << "Info: Writing records to database...";
        pStmt->BeginTransaction();

        for(size_t i=0; i < numRecords; i++)
        {
            QString record_id = QString::number(i+1);
            QString record_name(DBFReadStringAttribute(hDBF,i,idx_name));
            QString record_code(DBFReadStringAttribute(hDBF,i,idx_code));

            QString inStmt = "INSERT INTO data(regionid,name,code) VALUES(\"" +
                    record_id + "\",\"" +record_name + "\",\"" + record_code + "\");";

            pStmt->SqlStatement(inStmt.toUtf8().data());

//            qDebug() << record_name;

            if(i % 1000 == 0)   {
                qDebug() << "Info: Wrote" << i+1 << "/" << numRecords << "records";
            }
        }

        pStmt->CommitTransaction();
        qDebug() << "Info: Done!";
    }

    // close dbf file
    DBFClose(hDBF);

    // clean up database
    delete pStmt;
    delete pDatabase;

    return 0;
}
예제 #5
0
			DBFileEntry::DBFileEntry(Kompex::SQLiteStatement& dbStmt) :
				id_(dbStmt.GetColumnInt(0)), usageCount_(dbStmt.GetColumnInt(3)), filepath_(dbStmt.GetColumnString16(1)), existedBefore_(dbStmt.GetColumnBool(2))
			{
			}
예제 #6
0
int main()
{
	// uncomment to redirect streams to a file
	//Kompex::CerrRedirection cerrRedirection("error.log");
	//Kompex::CoutRedirection coutRedirection("output.log");

	try
	{
		// create and open database
		Kompex::SQLiteDatabase *pDatabase = new Kompex::SQLiteDatabase("test.db", SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, 0);
		// create statement instance for sql queries/statements
		Kompex::SQLiteStatement *pStmt = new Kompex::SQLiteStatement(pDatabase);

		std::cout << "SQLite version: " << pDatabase->GetLibVersionNumber() << std::endl;
		
		// ---------------------------------------------------------------------------------------------------------
		// create table and insert some data
		pStmt->SqlStatement("CREATE TABLE user (userID INTEGER NOT NULL PRIMARY KEY, lastName VARCHAR(50) NOT NULL, firstName VARCHAR(50), age INTEGER, weight DOUBLE)");
		pStmt->SqlStatement("INSERT INTO user (userID, lastName, firstName, age, weight) VALUES (1, 'Lehmann', 'Jamie', 20, 65.5)");
		pStmt->SqlStatement("INSERT INTO user (userID, lastName, firstName, age, weight) VALUES (2, 'Burgdorf', 'Peter', 55, NULL)");
		pStmt->SqlStatement("INSERT INTO user (userID, lastName, firstName, age, weight) VALUES (3, 'Lehmann', 'Fernando', 18, 70.2)");
		pStmt->SqlStatement("INSERT INTO user (userID, lastName, firstName, age, weight) VALUES (4, 'Lehmann', 'Carlene ', 17, 50.8)");

		// ---------------------------------------------------------------------------------------------------------
		// insert some data with Bind..() methods
		pStmt->Sql("INSERT INTO user (userID, lastName, firstName, age, weight) VALUES(?, ?, ?, ?, ?);");
		pStmt->BindInt(1, 5);
		pStmt->BindString(2, "Murahama");
		pStmt->BindString(3, "Yura");
		pStmt->BindInt(4, 28);
		pStmt->BindDouble(5, 60.2);
		// executes the INSERT statement and cleans-up automatically
		pStmt->ExecuteAndFree();

		// ---------------------------------------------------------------------------------------------------------
		// lets have a look on a query which is shown in console
		std::cout << std::endl;
		pStmt->GetTable("SELECT firstName, userID, age, weight FROM user WHERE lastName = 'Lehmann';", 13);
		std::cout << std::endl;

		// some example SQLite aggregate functions
		std::cout << "COUNT(*): " << pStmt->SqlAggregateFuncResult("SELECT COUNT(*) FROM user WHERE lastName = 'Lehmann';") << std::endl;
		std::cout << "COUNT(weight): " << pStmt->SqlAggregateFuncResult("SELECT COUNT(weight) FROM user;") << std::endl;
		std::cout << "MAX(age): " << pStmt->SqlAggregateFuncResult("SELECT MAX(age) FROM user;") << std::endl;
		std::cout << "MIN(age): " << pStmt->SqlAggregateFuncResult("SELECT MIN(age) FROM user;") << std::endl;
		std::cout << "AVG(age): " << pStmt->SqlAggregateFuncResult("SELECT AVG(age) FROM user;") << std::endl;
		std::cout << "SUM(age): " << pStmt->SqlAggregateFuncResult("SELECT SUM(age) FROM user;") << std::endl;
		std::cout << "TOTAL(age): " << pStmt->SqlAggregateFuncResult("SELECT TOTAL(age) FROM user;") << std::endl;

		// ---------------------------------------------------------------------------------------------------------

		// sql query - searching for all people with lastName "Lehmann"
		pStmt->Sql("SELECT firstName FROM user WHERE lastName = 'Lehmann';");

		// after a Sql() call we can get some special information
		std::cout << "\nGetColumnName: " << pStmt->GetColumnName(0) << std::endl;
		std::cout << "GetColumnCount: " << pStmt->GetColumnCount() << std::endl;
		std::cout << "GetColumnDatabaseName: " << pStmt->GetColumnDatabaseName(0) << std::endl;
		std::cout << "GetColumnTableName: " << pStmt->GetColumnTableName(0) << std::endl;
		std::cout << "GetColumnOriginName: " << pStmt->GetColumnOriginName(0) << "\n" << std::endl;

		// do not forget to clean-up
		pStmt->FreeQuery();
		
		// ---------------------------------------------------------------------------------------------------------
		// another sql query
		pStmt->Sql("SELECT * FROM user WHERE firstName = 'Jamie';");

		// after a Sql() call we can get some other special information
		std::cout << "GetColumnName(0): " << pStmt->GetColumnName(0) << std::endl;
		std::cout << "GetColumnName(1): " << pStmt->GetColumnName(1) << std::endl;
		std::cout << "GetColumnName(2): " << pStmt->GetColumnName(2) << std::endl;
		std::cout << "GetColumnName(3): " << pStmt->GetColumnName(3) << std::endl;
		std::cout << "GetColumnName(4): " << pStmt->GetColumnName(4) << std::endl;
		std::cout << "GetColumnCount: " << pStmt->GetColumnCount() << std::endl;	
		std::cout << "GetColumnDeclaredDatatype(0): " << pStmt->GetColumnDeclaredDatatype(0) << std::endl;
		std::cout << "GetColumnDeclaredDatatype(1): " << pStmt->GetColumnDeclaredDatatype(1) << std::endl;
		std::cout << "GetColumnDeclaredDatatype(2): " << pStmt->GetColumnDeclaredDatatype(2) << std::endl;
		std::cout << "GetColumnDeclaredDatatype(3): " << pStmt->GetColumnDeclaredDatatype(3) << std::endl;
		std::cout << "GetColumnDeclaredDatatype(4): " << pStmt->GetColumnDeclaredDatatype(4) << std::endl;

		// process all results
		while(pStmt->FetchRow())
		{
			std::cout << "\nGetDataCount: " << pStmt->GetDataCount() << std::endl;
			std::cout << "SQL query - GetColumnDouble(0): " << pStmt->GetColumnDouble(0) << std::endl;
			std::cout << "SQL query - GetColumnString(1): " << pStmt->GetColumnString(1) << std::endl;
			std::cout << "SQL query - GetColumnString(2): " << pStmt->GetColumnString(2) << std::endl;
			std::cout << "SQL query - GetColumnString(3): " << pStmt->GetColumnString(3) << std::endl;
			std::cout << "SQL query - GetColumnString(4): " << pStmt->GetColumnString(4) << std::endl;
			std::cout << "\nColumnTypes (look at the documentation for the meaning of the numbers):\n";
			std::cout << "GetColumnType(0): " << pStmt->GetColumnType(0) << std::endl;
			std::cout << "GetColumnType(1): " << pStmt->GetColumnType(1) << std::endl;
			std::cout << "GetColumnType(2): " << pStmt->GetColumnType(2) << std::endl;
			std::cout << "GetColumnType(3): " << pStmt->GetColumnType(3) << std::endl;
			std::cout << "GetColumnType(4): " << pStmt->GetColumnType(4) << std::endl;
		}

		// do not forget to clean-up
		pStmt->FreeQuery();

		// ---------------------------------------------------------------------------------------------------------
		// a little example how to get some queried data via column name
		std::cout << "\nGet some queried data via column name:\n";
		pStmt->Sql("SELECT * FROM user WHERE lastName = 'Lehmann';");

		// process all results
		while(pStmt->FetchRow())
		{
			std::cout << "firstName: " << pStmt->GetColumnString("firstName") << std::endl;
			std::cout << "age: " << pStmt->GetColumnInt("age") << std::endl;
		}

		// do not forget to clean-up
		pStmt->FreeQuery();

		// ---------------------------------------------------------------------------------------------------------
		// example for prepared statements - repetitive execution (SELECT)
		std::cout << "\nPrepared statement - repetitive execution (SELECT):\n";
		pStmt->Sql("SELECT * FROM user WHERE userID=@id");
			
		for(int i = 1; i <= 3; ++i)
		{
			// bind an integer to the prepared statement
			pStmt->BindInt(1, i);

			// and now fetch all results
			while(pStmt->FetchRow())
				std::cout << pStmt->GetColumnCString(0) << " " << pStmt->GetColumnCString(1) << std::endl;

			// reset the prepared statement
			pStmt->Reset();
		}
		// do not forget to clean-up
		pStmt->FreeQuery();

		// ---------------------------------------------------------------------------------------------------------
		// example for prepared statements - repetitive execution (INSERT/UPDATE/DELETE)
		std::cout << "\nPrepared statement - repetitive execution (INSERT/UPDATE/DELETE):\n";
		std::cout << "no output here - there are only some INSERTs ;)\n";

		// create a table structure
		pStmt->SqlStatement("CREATE TABLE flower (flowerID INTEGER NOT NULL PRIMARY KEY, name VARCHAR(50) NOT NULL, size DOUBLE)");
		// create the prepared statement
		pStmt->Sql("INSERT INTO flower (flowerID, name, size) VALUES (@flowerID, @name, @size)");
			
		// bind all three values
		pStmt->BindInt(1, 1);					// flowerID
		pStmt->BindString(2, "rose");			// name
		pStmt->BindDouble(3, 50.5);				// size
		// execute the statement and reset the bindings
		pStmt->Execute();
		pStmt->Reset();

		// here we go - the second round
		pStmt->BindInt(1, 2);					// flowerID
		pStmt->BindString(2, "primrose");		// name
		pStmt->BindDouble(3, 6.21);				// size
		// execute the statement and reset the bindings
		pStmt->Execute();
		pStmt->Reset();

		// here we go - the second round
		pStmt->BindInt(1, 3);					// flowerID
		pStmt->BindString(2, "rhododendron");	// name
		pStmt->BindDouble(3, 109.84);			// size
		// execute the statement
		pStmt->Execute();
		// we don't need the prepared statement anymore so we clean-up everything
		pStmt->FreeQuery();
		
		// ---------------------------------------------------------------------------------------------------------
		// two possibilities to update data in the database

		// the first way
		std::cout << "\nUPDATE possibility 1: prepared statement - single execution";
		pStmt->Sql("UPDATE user SET lastName=@lastName, age=@age WHERE userID=@userID");
			
		// bind an integer to the prepared statement
        pStmt->BindString(1, "Urushihara");		// bind lastName
		pStmt->BindInt(2, 56);					// bind age
		pStmt->BindInt(3, 2);					// bind userID

		// execute it and clean-up
        pStmt->ExecuteAndFree();

		// the second way
		std::cout << "\nUPDATE possibility 2: common statement\n";
		pStmt->SqlStatement("UPDATE user SET weight=51.5, age=18 WHERE firstName='Carlene'");

		// ---------------------------------------------------------------------------------------------------------
		// get some instant results
		std::cout << "\nSELECT lastName FROM user WHERE userID = 3;\n" << pStmt->GetSqlResultString("SELECT lastName FROM user WHERE userID = 3");
		std::cout << "\nSELECT age FROM user WHERE userID = 4;\n" <<  pStmt->GetSqlResultInt("SELECT age FROM user WHERE userID = 4");
		std::cout << "\nSELECT weight FROM user WHERE userID = 3;\n" <<  pStmt->GetSqlResultDouble("SELECT weight FROM user WHERE userID = 3");

		// don't forget to delete the pointer for all GetSqlResult%() methods which return a pointer
		const unsigned char *lastName = pStmt->GetSqlResultCString("SELECT lastName FROM user WHERE userID = 2");
		// do something with lastName
		delete[] lastName;

		// ---------------------------------------------------------------------------------------------------------
		// DELETE statement and get afterwards the number of affected rows
		pStmt->SqlStatement("DELETE FROM user WHERE lastName = 'Lehmann'");
		std::cout << "\n\nGetDatabaseChanges: " << pDatabase->GetDatabaseChanges() << std::endl;

		// let's see, how many changes we have done
		std::cout << "GetTotalDatabaseChanges: " << pDatabase->GetTotalDatabaseChanges() << std::endl;
		std::cout << std::endl;

		// ---------------------------------------------------------------------------------------------------------
		// get all metadata from one column
		pStmt->GetTableColumnMetadata("user", "userID");
		std::cout << std::endl;

		// ---------------------------------------------------------------------------------------------------------
		// now we want try a transaction
		// if an error occurs, a rollback is automatically performed
		// note: you must use Transaction()
		pStmt->BeginTransaction();
		pStmt->Transaction("INSERT INTO user (userID, lastName, firstName, age, weight) VALUES (10, 'Kanzaki', 'Makoto', 23, 76.9)");
		FunctionWithLocalVariable(pStmt);
		pStmt->Transaction("INSERT INTO user (userID, lastName, firstName, age, weight) VALUES (12, 'Kanzaki', 'Peter', 63, 101.1)");
		pStmt->CommitTransaction();

		// if you want react on errors by yourself, you can use a own try() and catch() block
		// note: you must use SqlStatement()
		try
		{
			pStmt->BeginTransaction();
			pStmt->SqlStatement("INSERT INTO user (userID, lastName, firstName, age, weight) VALUES (10, 'Kanzaki', 'Makoto', 23, 76.9)");
			pStmt->SqlStatement("INSERT INTO user (userID, lastName, firstName, age, weight) VALUES (11, 'Kanzaki', 'Yura', 20, 56.9)");
			pStmt->SqlStatement("INSERT INTO user (userID, lastName, firstName, age, weight) VALUES (12, 'Kanzaki', 'Peter', 63, 101.1)");
			pStmt->SqlStatement("INSERT INTO user (userID, lastName, firstName, age, weight) VALUES (10, 'Henschel', 'Robert', 10, 34.5)");
			pStmt->CommitTransaction();
		}
		catch(Kompex::SQLiteException &exception) 
		{
			std::cerr << "Exception Occured: " << exception.GetString();
			std::cerr << "SQLite result code: " << exception.GetSqliteResultCode() << std::endl;
			pStmt->RollbackTransaction();
			std::cerr << "Rollback has been executed!" << std::endl;
			std::cerr << "This is our own catch() block!" << std::endl;
		}

		// ---------------------------------------------------------------------------------------------------------
		// Kompex::SQLiteBlob example
		std::cout << "\nKompex::SQLiteBlob example\n";

		// create a little table and fill it with some dummy data 
		// the content of the BLOBs is simple plaintext so that you can see what happens
		pStmt->SqlStatement("CREATE TABLE boarduser (userID INTEGER NOT NULL PRIMARY KEY, username VARCHAR(20), picture BLOB)");
		pStmt->SqlStatement("INSERT INTO boarduser (userID, username, picture) VALUES (1, 'apori', 'abcdefghijklmnopqrstuvwxyz')");
		pStmt->SqlStatement("INSERT INTO boarduser (userID, username, picture) VALUES (2, 'sarina', 'abcdefghijklmnopqrstuvwxyz')");

		// open the existing BLOB for user 'apori'
		Kompex::SQLiteBlob *pKompexBlob = new Kompex::SQLiteBlob(pDatabase, "main", "boarduser", "picture", 1);
		// get the size of the current BLOB
		std::cout << "GetBlobSize(): " << pKompexBlob->GetBlobSize() << std::endl;

		// read the whole BLOB value
		int blobSize = pKompexBlob->GetBlobSize();
		char *readBuffer = new char[blobSize + 1];
		readBuffer[blobSize] = '\0';
		pKompexBlob->ReadBlob(readBuffer, blobSize);
		std::cout << "ReadBlob() output: " << readBuffer << std::endl;
		
		// overwrite a part of the BLOB
		std::cout << "WriteBlob() - change the BLOB data\n";
		char newData[8] = "-HELLO-";
		pKompexBlob->WriteBlob(newData, sizeof(newData) - 1, 10);

		// and read the whole BLOB value again
		pKompexBlob->ReadBlob(readBuffer, blobSize);
		std::cout << "ReadBlob() output: " << readBuffer << std::endl;
		delete readBuffer;

		delete pKompexBlob;

		// ---------------------------------------------------------------------------------------------------------
		// create a sql statement with SQLite functions
		const char *param1 = "It's a happy day.";
		const char *param2 = "Muhahaha!";
		std::cout << "\n" << pStmt->Mprintf("INSERT INTO table VALUES('%q', '%q')", param1, param2) << std::endl;

		// ---------------------------------------------------------------------------------------------------------
		// statistics
		std::cout << "\nGetNumberOfCheckedOutLookasideMemorySlots: " << pDatabase->GetNumberOfCheckedOutLookasideMemorySlots() << std::endl;
		std::cout << "GetHighestNumberOfCheckedOutLookasideMemorySlots: " << pDatabase->GetHighestNumberOfCheckedOutLookasideMemorySlots() << std::endl;
		std::cout << "GetHeapMemoryUsedByPagerCaches: " << pDatabase->GetHeapMemoryUsedByPagerCaches() << std::endl;
		std::cout << "GetHeapMemoryUsedToStoreSchemas: " << pDatabase->GetHeapMemoryUsedToStoreSchemas() << std::endl;
		std::cout << "GetHeapAndLookasideMemoryUsedByPreparedStatements: " << pDatabase->GetHeapAndLookasideMemoryUsedByPreparedStatements() << std::endl;
		std::cout << "GetPagerCacheHitCount: " << pDatabase->GetPagerCacheHitCount() << std::endl;
		std::cout << "GetPagerCacheMissCount: " << pDatabase->GetPagerCacheMissCount() << std::endl;
		std::cout << "GetNumberOfDirtyCacheEntries: " << pDatabase->GetNumberOfDirtyCacheEntries() << std::endl;
		std::cout << "GetNumberOfUnresolvedForeignKeys: " << pDatabase->GetNumberOfUnresolvedForeignKeys() << std::endl;
		std::cout << "GetLookasideMemoryHitCount: " << pDatabase->GetLookasideMemoryHitCount() << std::endl;
		std::cout << "GetLookasideMemoryMissCountDueToSmallSlotSize: " << pDatabase->GetLookasideMemoryMissCountDueToSmallSlotSize() << std::endl;
		std::cout << "GetLookasideMemoryMissCountDueToFullMemory: " << pDatabase->GetLookasideMemoryMissCountDueToFullMemory() << std::endl;

		// ---------------------------------------------------------------------------------------------------------
		// clean-up
		delete pStmt;
		delete pDatabase;
	}
	catch(Kompex::SQLiteException &exception)
	{
		std::cerr << "\nException Occured" << std::endl;
		exception.Show();
		std::cerr << "SQLite result code: " << exception.GetSqliteResultCode() << std::endl;
	}

	/*
	// complete example for the usage of file and memory databases
	// (database layout is only fictitious)

	Kompex::SQLiteDatabase *pDatabase = new Kompex::SQLiteDatabase("scores.db", SQLITE_OPEN_READWRITE, 0);
	// move database to memory, so that we are work on the memory database hence
	pDatabase->MoveDatabaseToMemory();

	Kompex::SQLiteStatement *pStmt = new Kompex::SQLiteStatement(pDatabase);
	// insert some data sets into the memory database
	pStmt->SqlStatement("INSERT INTO score(id, lastScore, avgScore) VALUES(1, 429, 341)");
	pStmt->SqlStatement("INSERT INTO score(id, lastScore, avgScore) VALUES(2, 37, 44)");
	pStmt->SqlStatement("INSERT INTO score(id, lastScore, avgScore) VALUES(3, 310, 280)");

	// save the memory database to a file
	// if you don't do it, all database changes will be lost after closing the memory database
	pDatabase->SaveDatabaseFromMemoryToFile("newScores.db");

	delete pStmt;
	delete pDatabase;
	*/

	std::cin.get();
	return 0;
}
예제 #7
0
int main(int argc, char *argv[])
{
    QCoreApplication app(argc,argv);

    // check input args
    QStringList inputArgs = app.arguments();
    if(inputArgs.size() != 2)   {
        badInput();
        return -1;
    }

    // open osmscout map
    osmscout::DatabaseParameter map_param;
    osmscout::Database map(map_param);
    if(!map.Open(inputArgs[1].toStdString()))   {
        qDebug() << "ERROR: Failed to open osmscout map";
        return -1;
    }
    osmscout::TypeConfig * typeConfig = map.GetTypeConfig();
    osmscout::TypeSet typeSet;
    setTypesForAdminRegions(typeConfig,typeSet);

//    GeoBoundingBox tempbbox;
//    tempbbox.minLon = -83.3203; tempbbox.maxLon = -82.9688;
//    tempbbox.minLat = 42.1875; tempbbox.maxLat = 42.3633;

////    tempbbox.minLon -= 1; tempbbox.maxLon += 1;
////    tempbbox.minLat -= 1; tempbbox.maxLat += 1;

    // create search database
    Kompex::SQLiteDatabase * database;
    Kompex::SQLiteStatement * stmt;

    QString db_file_path = app.applicationDirPath()+"/searchdb.sqlite";
    QFile db_file(db_file_path);
    if(db_file.exists())   {
        if(!db_file.remove())   {
            qDebug() << "ERROR: searchdb.sqlite exists and "
                        "could not be deleted";
            return -1;
        }
    }

    try   {
        database = new Kompex::SQLiteDatabase("searchdb.sqlite",
                                              SQLITE_OPEN_READWRITE |
                                              SQLITE_OPEN_CREATE,0);

        stmt = new Kompex::SQLiteStatement(database);

        stmt->SqlStatement("CREATE TABLE name_lookup("
                           "name_id INTEGER PRIMARY KEY NOT NULL,"
                           "name_lookup TEXT NOT NULL UNIQUE);");

        stmt->SqlStatement("CREATE TABLE admin_regions("
                           "id INTEGER PRIMARY KEY NOT NULL,"
                           "node_offsets BLOB,"
                           "way_offsets BLOB,"
                           "area_offsets BLOB"
                           ");");

        stmt->SqlStatement("CREATE TABLE streets("
                           "id INTEGER PRIMARY KEY NOT NULL,"
                           "node_offsets BLOB,"
                           "way_offsets BLOB,"
                           "area_offsets BLOB"
                           ");");

        stmt->SqlStatement("CREATE TABLE pois("
                           "id INTEGER PRIMARY KEY NOT NULL,"
                           "node_offsets BLOB,"
                           "way_offsets BLOB,"
                           "area_offsets BLOB"
                           ");");
    }
    catch(Kompex::SQLiteException &exception)   {
        qDebug() << "ERROR: SQLite exception creating database:"
                 << QString::fromStdString(exception.GetString());
        return -1;
    }


    // build a tile list for the dataset

//    // world bbox
//    GeoBoundingBox bbox_world;
//    bbox_world.minLon = -180.0; bbox_world.maxLon = 180.0;
//    bbox_world.minLat = -90.0;  bbox_world.maxLat = 90.0;

    // map data bbox
    GeoBoundingBox bbox_map;
    map.GetBoundingBox(bbox_map.minLat,bbox_map.minLon,
                       bbox_map.maxLat,bbox_map.maxLon);

    // generate tile list
    std::vector<Tile*> list_tiles;
    buildTileList(bbox_map,list_tiles);

#ifdef DEBUG_WITH_OSG
    return displayTiles(bbox_map,list_tiles);
#endif


    // [name_id] [name_key]
    int32_t name_id=1;
    boost::unordered_map<std::string,int32_t> table_names;

    // build database tables
    bool opOk=false;

    // admin_regions
    qDebug() << "INFO: Building admin_regions table...";
    setTypesForAdminRegions(typeConfig,typeSet);
    opOk = buildTable(stmt,name_id,table_names,"admin_regions",
                      list_tiles,map,typeSet,false,true,true);
    if(opOk)   {
        qDebug() << "INFO: Finished building admin_regions table";
    }
    else   {
        qDebug() << "ERROR: Failed to build admin_regions table";
        return -1;
    }

    // streets
    qDebug() << "INFO: Building streets table...";
    setTypesForStreets(typeConfig,typeSet);
    opOk = buildTable(stmt,name_id,table_names,"streets",
                      list_tiles,map,typeSet,false,false,false);
    if(opOk)   {
        qDebug() << "INFO: Finished building streets table";
    }
    else   {
        qDebug() << "ERROR: Failed to build streets table";
        return -1;
    }

    // pois
    qDebug() << "INFO: Building pois table...";
    setTypesForPOIs(typeConfig,typeSet);
    opOk = buildTable(stmt,name_id,table_names,"pois",
                      list_tiles,map,typeSet,false,false,false);
    if(opOk)   {
        qDebug() << "INFO: Finished building pois table";
    }
    else   {
        qDebug() << "ERROR: Failed to build pois table";
        return -1;
    }

    // build name_lookup table
    qDebug() << "INFO: Building name_lookup table...";
    opOk = buildNameLookupTable(stmt,table_names);
    if(opOk)   {
        qDebug() << "INFO: Finished building name_lookup table";
    }
    else   {
        qDebug() << "ERROR: Failed to build name_lookup table";
        return -1;
    }

    // vacuum to minimize db
    try   {
        stmt->SqlStatement("VACUUM;");
    }
    catch(Kompex::SQLiteException &exception)   {
        qDebug() << "ERROR: SQLite exception vacuuming:"
                 << QString::fromStdString(exception.GetString());
        return -1;
    }

    // clean up
    for(size_t i=0; i < list_tiles.size(); i++)   {
        delete list_tiles[i];
    }
    list_tiles.clear();
    delete stmt;
    delete database;

//    // ### debug
//    std::map<int64_t,std::string> table_count_names;
//    boost::unordered_map<std::string,qint64>::iterator it;
//    for(it = table_names.begin(); it != table_names.end(); ++it)   {
//        std::map<int64_t,int64_t>::iterator d_it;
//        d_it = g_table_nameid_count.find(it->second);

//        std::pair<int64_t,std::string> data;
//        data.first = d_it->second;
//        data.second = it->first;
//        table_count_names.insert(data);
//    }

//    std::map<int64_t,std::string>::iterator c_it;
//    for(c_it  = table_count_names.begin();
//        c_it != table_count_names.end(); ++c_it)   {
//        qDebug() << QString::fromStdString(c_it->second) << ":" << c_it->first;
//    }

//    // debug
//    boost::unordered_map<std::string,qint64>::iterator it;
//    for(it  = table_names.begin(); it != table_names.end(); ++it)
//    {
//        qDebug() << it->second << ": " << QString::fromStdString(it->first);
//    }
}
예제 #8
0
파일: trainer.cpp 프로젝트: 8l/insieme
/*
 * Reads values form the database and stores the features in in, the targets (mapped according to the set policy) in targets as one-of-n coding
*/
size_t Trainer::readDatabase(Array<double>& in, Array<double>& target) throw(Kompex::SQLiteException) {
	// if no query has been set, use default query
	if(query.size() == 0)
		genDefaultQuery();

	// read the maximum of the column in measurement for which to train
	double max = 0.0, min = 0.0;
	if(genOut != GenNNoutput::ML_KEEP_INT && genOut != GenNNoutput::ML_FUZZY_VECTOR)
		max = getMaximum(trainForName), min = getMinimum(trainForName);

	Kompex::SQLiteStatement *localStmt = new Kompex::SQLiteStatement(pDatabase);
	unsigned int nClasses = model.getOutputDimension();

	localStmt->Sql(query);

	size_t nRows = localStmt->GetNumberOfRows();
	in = Array<double>(nRows, nFeatures());
	LOG(INFO) << "Queried Rows: " << nRows << ", Number of features: " << staticFeatures.size() << " + " << dynamicFeatures.size()  <<
			" + " << pcaFeatures.size() << std::endl;
	if(nRows == 0)
		throw MachineLearningException("No dataset for the requested features could be found");

	std::list<std::pair<double, size_t> > measurements;

	Array<double> oneOfN(nClasses);
	for(Array<double>::iterator I = oneOfN.begin(); I != oneOfN.end() && model.usesOneOfNCoding(); ++I) {
		*I = NEG;
	}

	//Train machine
	size_t i = 0;
	// fetch all results
	while(localStmt->FetchRow()){
//				std::cout << "Result: " << localStmt->GetColumnName(2) << " " << localStmt->GetColumnName(3) << " " << localStmt->GetColumnName(4) << std::endl;
//				std::cout << "Data:   " << localStmt->GetColumnInt(2) << " " << localStmt->GetColumnInt(3) << " " << localStmt->GetColumnInt(4) << std::endl;

//std::cout << "[";
		// construct training vectors
		for(size_t j = 0; j < nFeatures(); ++j) {
			in(i, j) = localStmt->GetColumnDouble(j);
//std::cout << in(i, j) << " ";
		}

		// translate index to one-of-n coding
		if(genOut == ML_MAP_TO_N_CLASSES)
			measurements.push_back(std::make_pair(localStmt->GetColumnDouble(nFeatures()), i));
		else
			appendToTrainArray(target, localStmt, nFeatures(), max, min, oneOfN);

//std::cout << target(i) << "]\n";
		++i;
	}


	if(genOut == ML_MAP_TO_N_CLASSES)
		mapToNClasses(measurements, model.getOutputDimension(), NEG, POS, target);

	// reset the prepared statement
	localStmt->Reset();

	// do not forget to clean-up
	localStmt->FreeQuery();
	delete localStmt;

	FeaturePreconditioner fp;
	featureNormalization = fp.normalize(in, -1, 1);
	return nRows;
}
예제 #9
0
DBPackageEntry::DBPackageEntry(Kompex::SQLiteStatement& dbStmt, int startCol) 
	: id(dbStmt.GetColumnInt(startCol + 0)), filepath(dbStmt.GetColumnString16(startCol + 1)),
	installedTimestamp(dbStmt.GetColumnInt64(startCol + 2)),
	readme(dbStmt.GetColumnString16(startCol + 3)), checksum(dbStmt.GetColumnString16(startCol + 4))
{
}
예제 #10
0
int main(int argc, const char *argv[])
{
    if(argc != 3) {
        std::cout << "Usage: #> ./osg_openctm_sqlite inputdb row\n";
        std::cout << "* draw an openctm mesh stored as a row in an sqlite db with osg\n";
        return 0;
    }

    Kompex::SQLiteDatabase *pDatabase = new Kompex::SQLiteDatabase(argv[1],SQLITE_OPEN_READWRITE,0);
    Kompex::SQLiteStatement *pStmt = new Kompex::SQLiteStatement(pDatabase);

    int cRow = 0;
    int rowId = 0;
    int dbRow = 2;
    bool isEmpty = false;

    std::string rowFileKey;
    pStmt->Sql("SELECT rowid,filekey,empty FROM grid_mesh;");
    while(pStmt->FetchRow())   {
        if(cRow == dbRow)   {
            // row file key
            rowId = pStmt->GetColumnInt(0);
            rowFileKey = pStmt->GetColumnString(1);
            isEmpty = pStmt->GetColumnBool(2);
        }
        cRow++;
    }
    pStmt->FreeQuery();
    Kompex::SQLiteBlob *pKompexBlob;

    if(!isEmpty)   {
        // read in ctm blob to memory
        pKompexBlob=new Kompex::SQLiteBlob(pDatabase,"main","grid_mesh","mesh",1);
        size = pKompexBlob->GetBlobSize();
        memblock = new char[size];
        pKompexBlob->ReadBlob(memblock,size);
        delete pKompexBlob;
        std::cout << "# Read in " << rowFileKey << " (" << size << " bytes)\n";

        // uncompress ctm
        CTMcontext context;
        CTMuint    vertCount, triCount;
        CTMuint const* indices;
        CTMfloat   const * vertices;

        context = ctmNewContext(CTM_IMPORT);

        g_pos = 0;
        ctmLoadCustom(context,ctmReadBlob,(void*)memblock);
        if(ctmGetError(context) == CTM_NONE)
        {
            // access the mesh data
            vertCount = ctmGetInteger(context, CTM_VERTEX_COUNT);
            vertices = ctmGetFloatArray(context, CTM_VERTICES);
            triCount = ctmGetInteger(context, CTM_TRIANGLE_COUNT);
            indices = ctmGetIntegerArray(context, CTM_INDICES);

            std::cout << "# Mesh has " << vertCount << " vertices\n";
            std::cout << "# Mesh has " << triCount << " triangles\n";

            // build up openscenegraph geometry
            osg::ref_ptr<osg::Vec3Array> listVxArray = new osg::Vec3Array(vertCount);
            unsigned int vxIdx=0;
            for(int i=0; i < listVxArray->size(); i++)   {
                osg::Vec3 vertex;
                vertex.x() = vertices[vxIdx]; vxIdx++;
                vertex.y() = vertices[vxIdx]; vxIdx++;
                vertex.z() = vertices[vxIdx]; vxIdx++;
                listVxArray->at(i) = vertex;
            }

            osg::ref_ptr<osg::DrawElementsUInt> listIdxs =
                    new osg::DrawElementsUInt(GL_TRIANGLES,triCount*3);
            for(int i=0; i < listIdxs->size(); i++)   {
                listIdxs->at(i) = indices[i];
            }

            osg::ref_ptr<osg::Geometry> geomMesh = new osg::Geometry;
            geomMesh->setVertexArray(listVxArray.get());
            geomMesh->setNormalArray(listVxArray.get());
            geomMesh->addPrimitiveSet(listIdxs.get());

            osg::ref_ptr<osg::Geode> geodeMesh = new osg::Geode;
            geodeMesh->addDrawable(geomMesh.get());

            osg::ref_ptr<osg::Group> groupRoot = new osg::Group;
            groupRoot->addChild(geodeMesh.get());

            // free ctm memory
            ctmFreeContext(context);

            // start viewer
            osgViewer::Viewer viewer;
            viewer.setThreadingModel(osgViewer::ViewerBase::SingleThreaded);
            viewer.setUpViewInWindow(100,100,800,480);
            viewer.setSceneData(groupRoot.get());
            return viewer.run();
        }
        else
        {
            std::cout << "# Fatal error reading in mesh file! Exiting...\n";
            ctmFreeContext(context);
            return 0;
        }



//        // stuff
        delete[] memblock;
//        delete pStmt;
//        delete pDatabase;
        return 0;
    }
    else   {
        std::cout << "# Row " << rowId << " was empty! Exiting...\n";
        delete pStmt;
        delete pDatabase;
        return 0;
    }
}