GeometryCollection::GeometryCollection(SQLite::Column const & blob) : Buffer<GeometryCollectionType>(gaiaFromSpatiaLiteBlobWkb( (const unsigned char*)blob.getBlob(), blob.getBytes()), gaiaFreeGeomColl) { }
int main () { // Basic example (1/6) : try { // Open a database file in readonly mode SQLite::Database db(filename_example_db3); // SQLITE_OPEN_READONLY std::cout << "SQLite database file '" << db.getFilename().c_str() << "' opened successfully\n"; // Test if the 'test' table exists bool bExists = db.tableExists("test"); std::cout << "SQLite table 'test' exists=" << bExists << "\n"; // Get a single value result with an easy to use shortcut std::string value = db.execAndGet("SELECT value FROM test WHERE id=2"); std::cout << "execAndGet=" << value.c_str() << std::endl; // Compile a SQL query, containing one parameter (index 1) SQLite::Statement query(db, "SELECT id as test_id, value as test_val, weight as test_weight FROM test WHERE weight > ?"); std::cout << "SQLite statement '" << query.getQuery().c_str() << "' compiled (" << query.getColumnCount () << " columns in the result)\n"; // Bind the integer value 2 to the first parameter of the SQL query query.bind(1, 2); std::cout << "binded with integer value '2' :\n"; // Loop to execute the query step by step, to get one a row of results at a time while (query.executeStep()) { // Demonstrate how to get some typed column value (and the equivalent explicit call) int id = query.getColumn(0); // = query.getColumn(0).getInt() //const char* pvalue = query.getColumn(1); // = query.getColumn(1).getText() std::string value2 = query.getColumn(1); // = query.getColumn(1).getText() int bytes = query.getColumn(1).getBytes(); double weight = query.getColumn(2); // = query.getColumn(2).getInt() static bool bFirst = true; if (bFirst) { // Show how to get the aliased names of the result columns. std::string name0 = query.getColumn(0).getName(); std::string name1 = query.getColumn(1).getName(); std::string name2 = query.getColumn(2).getName(); std::cout << "aliased result [\"" << name0.c_str() << "\", \"" << name1.c_str() << "\", \"" << name2.c_str() << "\"]\n"; #ifdef SQLITE_ENABLE_COLUMN_METADATA // Show how to get origin names of the table columns from which theses result columns come from. // Requires the SQLITE_ENABLE_COLUMN_METADATA preprocessor macro to be // also defined at compile times of the SQLite library itself. name0 = query.getColumn(0).getOriginName(); name1 = query.getColumn(1).getOriginName(); name2 = query.getColumn(2).getOriginName(); std::cout << "origin table 'test' [\"" << name0.c_str() << "\", \"" << name1.c_str() << "\", \"" << name2.c_str() << "\"]\n"; #endif bFirst = false; } std::cout << "row (" << id << ", \"" << value2.c_str() << "\" " << bytes << " bytes, " << weight << ")\n"; } // Reset the query to use it again query.reset(); std::cout << "SQLite statement '" << query.getQuery().c_str() << "' reseted (" << query.getColumnCount () << " columns in the result)\n"; // Bind the string value "6" to the first parameter of the SQL query query.bind(1, "6"); std::cout << "binded with string value \"6\" :\n"; while (query.executeStep()) { // Demonstrate that inserting column value in a std:ostream is natural std::cout << "row (" << query.getColumn(0) << ", \"" << query.getColumn(1) << "\", " << query.getColumn(2) << ")\n"; } } catch (std::exception& e) { std::cout << "SQLite exception: " << e.what() << std::endl; return EXIT_FAILURE; // unexpected error : exit the example program } //////////////////////////////////////////////////////////////////////////// // Object Oriented Basic example (2/6) : try { // Open the database and compile the query Example example; // Demonstrate the way to use the same query with different parameter values example.ListGreaterThan(8); example.ListGreaterThan(6); example.ListGreaterThan(2); } catch (std::exception& e) { std::cout << "SQLite exception: " << e.what() << std::endl; return EXIT_FAILURE; // unexpected error : exit the example program } // The execAndGet wrapper example (3/6) : try { // Open a database file in readonly mode SQLite::Database db(filename_example_db3); // SQLITE_OPEN_READONLY std::cout << "SQLite database file '" << db.getFilename().c_str() << "' opened successfully\n"; // WARNING: Be very careful with this dangerous method: you have to // make a COPY OF THE result, else it will be destroy before the next line // (when the underlying temporary Statement and Column objects are destroyed) std::string value = db.execAndGet("SELECT value FROM test WHERE id=2"); std::cout << "execAndGet=" << value.c_str() << std::endl; } catch (std::exception& e) { std::cout << "SQLite exception: " << e.what() << std::endl; return EXIT_FAILURE; // unexpected error : exit the example program } //////////////////////////////////////////////////////////////////////////// // Simple batch queries example (4/6) : try { // Open a database file in create/write mode SQLite::Database db("test.db3", SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE); std::cout << "SQLite database file '" << db.getFilename().c_str() << "' opened successfully\n"; // Create a new table with an explicit "id" column aliasing the underlying rowid db.exec("DROP TABLE IF EXISTS test"); db.exec("CREATE TABLE test (id INTEGER PRIMARY KEY, value TEXT)"); // first row int nb = db.exec("INSERT INTO test VALUES (NULL, \"test\")"); std::cout << "INSERT INTO test VALUES (NULL, \"test\")\", returned " << nb << std::endl; // second row nb = db.exec("INSERT INTO test VALUES (NULL, \"second\")"); std::cout << "INSERT INTO test VALUES (NULL, \"second\")\", returned " << nb << std::endl; // update the second row nb = db.exec("UPDATE test SET value=\"second-updated\" WHERE id='2'"); std::cout << "UPDATE test SET value=\"second-updated\" WHERE id='2', returned " << nb << std::endl; // Check the results : expect two row of result SQLite::Statement query(db, "SELECT * FROM test"); std::cout << "SELECT * FROM test :\n"; while (query.executeStep()) { std::cout << "row (" << query.getColumn(0) << ", \"" << query.getColumn(1) << "\")\n"; } db.exec("DROP TABLE test"); } catch (std::exception& e) { std::cout << "SQLite exception: " << e.what() << std::endl; return EXIT_FAILURE; // unexpected error : exit the example program } remove("test.db3"); //////////////////////////////////////////////////////////////////////////// // RAII transaction example (5/6) : try { // Open a database file in create/write mode SQLite::Database db("transaction.db3", SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE); std::cout << "SQLite database file '" << db.getFilename().c_str() << "' opened successfully\n"; db.exec("DROP TABLE IF EXISTS test"); // Exemple of a successful transaction : try { // Begin transaction SQLite::Transaction transaction(db); db.exec("CREATE TABLE test (id INTEGER PRIMARY KEY, value TEXT)"); int nb = db.exec("INSERT INTO test VALUES (NULL, \"test\")"); std::cout << "INSERT INTO test VALUES (NULL, \"test\")\", returned " << nb << std::endl; // Commit transaction transaction.commit(); } catch (std::exception& e) { std::cout << "SQLite exception: " << e.what() << std::endl; return EXIT_FAILURE; // unexpected error : exit the example program } // Exemple of a rollbacked transaction : try { // Begin transaction SQLite::Transaction transaction(db); int nb = db.exec("INSERT INTO test VALUES (NULL, \"second\")"); std::cout << "INSERT INTO test VALUES (NULL, \"second\")\", returned " << nb << std::endl; nb = db.exec("INSERT INTO test ObviousError"); std::cout << "INSERT INTO test \"error\", returned " << nb << std::endl; return EXIT_FAILURE; // unexpected success : exit the example program // Commit transaction transaction.commit(); } catch (std::exception& e) { std::cout << "SQLite exception: " << e.what() << std::endl; // expected error, see above } // Check the results (expect only one row of result, as the second one has been rollbacked by the error) SQLite::Statement query(db, "SELECT * FROM test"); std::cout << "SELECT * FROM test :\n"; while (query.executeStep()) { std::cout << "row (" << query.getColumn(0) << ", \"" << query.getColumn(1) << "\")\n"; } } catch (std::exception& e) { std::cout << "SQLite exception: " << e.what() << std::endl; return EXIT_FAILURE; // unexpected error : exit the example program } remove("transaction.db3"); //////////////////////////////////////////////////////////////////////////// // Binary blob and in-memory database example (6/6) : try { // Open a database file in create/write mode SQLite::Database db(":memory:", SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE); std::cout << "SQLite database file '" << db.getFilename().c_str() << "' opened successfully\n"; db.exec("DROP TABLE IF EXISTS test"); db.exec("CREATE TABLE test (id INTEGER PRIMARY KEY, value BLOB)"); FILE* fp = fopen(filename_logo_png.c_str(), "rb"); if (NULL != fp) { char buffer[16*1024]; void* blob = &buffer; int size = static_cast<int>(fread(blob, 1, 16*1024, fp)); buffer[size] = '\0'; fclose (fp); std::cout << "blob size=" << size << " :\n"; // Insert query SQLite::Statement query(db, "INSERT INTO test VALUES (NULL, ?)"); // Bind the blob value to the first parameter of the SQL query query.bind(1, blob, size); std::cout << "blob binded successfully\n"; // Execute the one-step query to insert the blob int nb = query.exec (); std::cout << "INSERT INTO test VALUES (NULL, ?)\", returned " << nb << std::endl; } else { std::cout << "file " << filename_logo_png << " not found !\n"; return EXIT_FAILURE; // unexpected error : exit the example program } fp = fopen("out.png", "wb"); if (NULL != fp) { const void* blob = NULL; size_t size; SQLite::Statement query(db, "SELECT * FROM test"); std::cout << "SELECT * FROM test :\n"; if (query.executeStep()) { SQLite::Column colBlob = query.getColumn(1); blob = colBlob.getBlob (); size = colBlob.getBytes (); std::cout << "row (" << query.getColumn(0) << ", size=" << size << ")\n"; size_t sizew = fwrite(blob, 1, size, fp); SQLITECPP_ASSERT(sizew == size, "fwrite failed"); // See SQLITECPP_ENABLE_ASSERT_HANDLER fclose (fp); } } else { std::cout << "file out.png not created !\n"; return EXIT_FAILURE; // unexpected error : exit the example program } } catch (std::exception& e) { std::cout << "SQLite exception: " << e.what() << std::endl; return EXIT_FAILURE; // unexpected error : exit the example program } remove("out.png"); std::cout << "everything ok, quitting\n"; return EXIT_SUCCESS; }
TEST(Column, basis) { // Create a new database SQLite::Database db(":memory:", SQLite::OPEN_READWRITE|SQLite::OPEN_CREATE); EXPECT_EQ(SQLite::OK, db.getErrorCode()); EXPECT_EQ(SQLite::OK, db.getExtendedErrorCode()); // Create a new table EXPECT_EQ(0, db.exec("CREATE TABLE test (id INTEGER PRIMARY KEY, msg TEXT, int INTEGER, double REAL, binary BLOB, empty TEXT)")); EXPECT_TRUE(db.tableExists("test")); EXPECT_TRUE(db.tableExists(std::string("test"))); EXPECT_EQ(0, db.getLastInsertRowid()); // Create a first row (autoid: 1) with all kind of data and a null value SQLite::Statement insert(db, "INSERT INTO test VALUES (NULL, \"first\", -123, 0.123, ?, NULL)"); // Bind the blob value to the first parameter of the SQL query const char buffer[] = {'b', 'l', '\0', 'b'}; // "bl\0b" : 4 char, with a null byte inside const int size = sizeof(buffer); // size = 4 const void* blob = &buffer; insert.bind(1, blob, size); // Execute the one-step query to insert the row EXPECT_EQ(1, insert.exec()); EXPECT_EQ(1, db.getLastInsertRowid()); EXPECT_EQ(1, db.getTotalChanges()); EXPECT_THROW(insert.exec(), SQLite::Exception); // exec() shall throw as it needs to be reseted // Compile a SQL query SQLite::Statement query(db, "SELECT * FROM test"); EXPECT_STREQ("SELECT * FROM test", query.getQuery().c_str()); EXPECT_EQ(6, query.getColumnCount ()); query.executeStep(); EXPECT_TRUE (query.hasRow()); EXPECT_FALSE(query.isDone()); // validates every variant of cast operators, and conversions of types { const sqlite3_int64 id1 = query.getColumn(0); // operator int64_t() const int64_t id2 = query.getColumn(0); // operator int64_t() const long long id3 = query.getColumn(0); // operator int64_t() const long id4 = query.getColumn(0); // operator int64_t() or long() depending on compiler/architecture const unsigned int uint1 = query.getColumn(0); // operator uint32_t() const uint32_t uint2 = query.getColumn(0); // operator uint32_t() const char* ptxt = query.getColumn(1); // operator const char*() const std::string msg = query.getColumn(1); // operator std::string() (or const char* with MSVC) const int integer = query.getColumn(2); // operator int() const double real = query.getColumn(3); // operator double() const void* pblob = query.getColumn(4); // operator void*() const std::string sblob = query.getColumn(4); // operator std::string() (or const char* with MSVC) const void* pempty = query.getColumn(5); // operator void*() EXPECT_EQ(1, id1); EXPECT_EQ(1, id2); EXPECT_EQ(1, id3); EXPECT_EQ(1, id4); EXPECT_EQ(1U, uint1); EXPECT_EQ(1U, uint2); EXPECT_STREQ("first", ptxt); EXPECT_EQ("first", msg); EXPECT_EQ(-123, integer); EXPECT_EQ(0.123, real); EXPECT_EQ(0, memcmp("bl\0b", pblob, size)); EXPECT_EQ((size_t)size, sblob.size()); EXPECT_EQ(0, memcmp("bl\0b", &sblob[0], size)); EXPECT_EQ(NULL, pempty); } // validates every variant of explicit getters { int64_t id = query.getColumn(0).getInt64(); const unsigned int uint1 = query.getColumn(0).getUInt(); const uint32_t uint2 = query.getColumn(0).getUInt(); const char* ptxt = query.getColumn(1).getText(); const std::string msg1 = query.getColumn(1).getText(); const std::string msg2 = query.getColumn(1).getString(); const int integer = query.getColumn(2).getInt(); const double real = query.getColumn(3).getDouble(); const void* pblob = query.getColumn(4).getBlob(); const std::string sblob = query.getColumn(4).getString(); EXPECT_EQ(1, id); EXPECT_EQ(1U, uint1); EXPECT_EQ(1U, uint2); EXPECT_STREQ("first", ptxt); EXPECT_EQ("first", msg1); EXPECT_EQ("first", msg2); EXPECT_EQ(-123, integer); EXPECT_EQ(0.123, real); EXPECT_EQ(0, memcmp("bl\0b", pblob, 4)); EXPECT_EQ(0, memcmp("bl\0b", &sblob[0], 4)); } // Validate getBytes(), getType(), isInteger(), isNull()... EXPECT_EQ(SQLite::INTEGER, query.getColumn(0).getType()); EXPECT_EQ(true, query.getColumn(0).isInteger()); EXPECT_EQ(false, query.getColumn(0).isFloat()); EXPECT_EQ(false, query.getColumn(0).isText()); EXPECT_EQ(false, query.getColumn(0).isBlob()); EXPECT_EQ(false, query.getColumn(0).isNull()); EXPECT_STREQ("1", query.getColumn(0).getText()); // convert to string EXPECT_EQ(1, query.getColumn(0).getBytes()); // size of the string "1" without the null terminator EXPECT_EQ(SQLite::TEXT, query.getColumn(1).getType()); EXPECT_EQ(false, query.getColumn(1).isInteger()); EXPECT_EQ(false, query.getColumn(1).isFloat()); EXPECT_EQ(true, query.getColumn(1).isText()); EXPECT_EQ(false, query.getColumn(1).isBlob()); EXPECT_EQ(false, query.getColumn(1).isNull()); EXPECT_STREQ("first", query.getColumn(1).getText()); // convert to string EXPECT_EQ(5, query.getColumn(1).getBytes()); // size of the string "first" EXPECT_EQ(SQLite::INTEGER, query.getColumn(2).getType()); EXPECT_EQ(true, query.getColumn(2).isInteger()); EXPECT_EQ(false, query.getColumn(2).isFloat()); EXPECT_EQ(false, query.getColumn(2).isText()); EXPECT_EQ(false, query.getColumn(2).isBlob()); EXPECT_EQ(false, query.getColumn(2).isNull()); EXPECT_STREQ("-123", query.getColumn(2).getText()); // convert to string EXPECT_EQ(4, query.getColumn(2).getBytes()); // size of the string "-123" EXPECT_EQ(SQLite::FLOAT, query.getColumn(3).getType()); EXPECT_EQ(false, query.getColumn(3).isInteger()); EXPECT_EQ(true, query.getColumn(3).isFloat()); EXPECT_EQ(false, query.getColumn(3).isText()); EXPECT_EQ(false, query.getColumn(3).isBlob()); EXPECT_EQ(false, query.getColumn(3).isNull()); EXPECT_STREQ("0.123", query.getColumn(3).getText()); // convert to string EXPECT_EQ(5, query.getColumn(3).getBytes()); // size of the string "0.123" EXPECT_EQ(SQLite::BLOB, query.getColumn(4).getType()); EXPECT_EQ(false, query.getColumn(4).isInteger()); EXPECT_EQ(false, query.getColumn(4).isFloat()); EXPECT_EQ(false, query.getColumn(4).isText()); EXPECT_EQ(true, query.getColumn(4).isBlob()); EXPECT_EQ(false, query.getColumn(4).isNull()); EXPECT_STREQ("bl\0b", query.getColumn(4).getText()); // convert to string EXPECT_EQ(4, query.getColumn(4).getBytes()); // size of the blob "bl\0b" with the null char EXPECT_EQ(SQLite::Null, query.getColumn(5).getType()); EXPECT_EQ(false, query.getColumn(5).isInteger()); EXPECT_EQ(false, query.getColumn(5).isFloat()); EXPECT_EQ(false, query.getColumn(5).isText()); EXPECT_EQ(false, query.getColumn(5).isBlob()); EXPECT_EQ(true, query.getColumn(5).isNull()); EXPECT_STREQ("", query.getColumn(5).getText()); // convert to string EXPECT_EQ(0, query.getColumn(5).getBytes()); // size of the string "" without the null terminator // Use intermediate Column objects (this is not the recommended way to use the API) { const SQLite::Column id = query.getColumn(0); EXPECT_EQ(1, id.getInt64()); const SQLite::Column msg = query.getColumn(1); EXPECT_EQ("first", msg.getString()); const SQLite::Column integer = query.getColumn(2); EXPECT_EQ(-123, integer.getInt()); const SQLite::Column dbl = query.getColumn(3); EXPECT_EQ(0.123, dbl.getDouble()); } }