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;
}
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;
}
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;
}
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;
}
DBFileEntry::DBFileEntry(Kompex::SQLiteStatement& dbStmt) :
id_(dbStmt.GetColumnInt(0)), usageCount_(dbStmt.GetColumnInt(3)), filepath_(dbStmt.GetColumnString16(1)), existedBefore_(dbStmt.GetColumnBool(2))
{
}
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;
}
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);
// }
}
/*
* 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;
}
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))
{
}
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;
}
}
