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; }
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; }