GeometryCollection::GeometryCollection(SQLite::Column const & blob) :
     Buffer<GeometryCollectionType>(gaiaFromSpatiaLiteBlobWkb(
                                        (const unsigned char*)blob.getBlob(),
                                        blob.getBytes()),
                                    gaiaFreeGeomColl)
 {
 }
Пример #2
0
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;
}
Пример #3
0
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());
    }
}