SOCI/soci

ORM: What is the correct way to bulk add a lot of rows?

Opened this issue · 5 comments

asmwarrior commented

Hi, I would like to build operation on a table by adding many rows to a table, here is a simple code which looks OK

            // try to add some "person" to the table
            // Assume that you have a vector of rows to insert
            std::vector<person> rows = {
                {0, "John", 30},
                {0, "Jane", 25},
                {0, "Bob", 40}
            };

            // Create a statement to insert a row into the table
            person p;
            soci::statement stmt = (sql.prepare << "INSERT INTO testtable (id, name, age) VALUES (:id, :name, :age)", soci::use(p));

            for (int i=0; i<rows.size(); i++)
            {
                // Bind the placeholders to the values of each row
                p = rows[i];

                // Execute the statement to insert the rows into the table
                stmt.execute(true);
            }

The person class has such definition:

class person
{
public:
    person()
    {
        std::cout << "person constructor" << std::endl;
    }

    person(int id_, std::string name_, int age_)
    {
        id = id_;
        name = name_;
        age = age_;
    }

    person(const person & old)
    {
        id = old.id;
        name = old.name;
        age = old.age;
        std::cout << "copy constructor" << std::endl;
    }

    ~person()
    {
        std::cout << "destroy id = " << id << std::endl;
    }
public:
    int id;
    std::string name;
    int age;
};

namespace soci
{
    template<>
    struct type_conversion<person>
    {

        typedef values base_type;

        static void from_base(const values& v, indicator ind, person& row)
        {
            row.id =   v.get<int>("id", -1);
            row.name = v.get<std::string>("name", "");
            row.age =  v.get<int>("age", 0);

        }

        static void to_base(const person& row, values& v, indicator& ind)
        {
            v.set("id",   row.id);
            v.set("name", row.name);
            v.set("age",  row.age);

            ind = i_ok;
        }
    };
}

My question is: is my code the best way to bulk operation of adding several rows from a vector?

Is it possible to avoid the for loop, so that in the statement, I can directly add(use) the whole rows, such as:

soci::statement stmt = (sql.prepare << "INSERT INTO testtable (id, name, age) VALUES (:id, :name, :age)", soci::use(rows));

Any ideas?

Thanks.

zann1x commented

Is it possible to avoid the for loop, so that in the statement, I can directly add(use) the whole rows, such as:

soci::statement stmt = (sql.prepare << "INSERT INTO testtable (id, name, age) VALUES (:id, :name, :age)", soci::use(rows));

No, that's not possible because bulk operations with custom types are currently not supported.

asmwarrior commented

No, that's not possible because bulk operations with custom types are currently not supported.

OK, thanks for the help. So, my current implementation is the correct way(using the for loop) to add the custom types.

asmwarrior commented

From the document, I see some example has extra functions:

soci/docs/statements.md

Lines 224 to 268 in 924d990

## Statement caching
Some backends have some facilities to improve statement parsing and compilation to limit overhead when creating commonly used query.
But for backends that does not support this kind optimization you can keep prepared statement and use it later with new references.
To do such, prepare a statement as usual, you have to use `exchange` to bind new variables to statement object, then `execute` statement and finish by cleaning bound references with `bind_clean_up`.
```cpp
sql << "CREATE TABLE test(a INTEGER)";
{
// prepare statement
soci::statement stmt = (sql.prepare << "INSERT INTO numbers(value) VALUES(:val)");
{
// first insert
int a0 = 0;
// update reference
stmt.exchange(soci::use(a0));
stmt.define_and_bind();
stmt.execute(true);
stmt.bind_clean_up();
}
{
// come later, second insert
int a1 = 1;
// update reference
stmt.exchange(soci::use(a1));
stmt.define_and_bind();
stmt.execute(true);
stmt.bind_clean_up();
}
}
{
std::vector<int> v(10);
sql << "SELECT value FROM numbers", soci::into(v);
for (int i = 0; i < v.size(); ++i)
std::cout << "value " << i << ": " << v[i] << std::endl;
}
```

such as

        // first insert
        int a0 = 0;

        // update reference
        stmt.exchange(soci::use(a0));

        stmt.define_and_bind();
        stmt.execute(true);
        stmt.bind_clean_up();

So, do I need to adding the define_and_bind and bind_clean_up in my for loop in my first post in this ticket?

zann1x commented

No, that's not possible because bulk operations with custom types are currently not supported.

OK, thanks for the help. So, my current implementation is the correct way(using the for loop) to add the custom types.

Yes, it is.

From the document, I see some example has extra functions:

soci/docs/statements.md

Lines 224 to 268 in 924d990

## Statement caching
Some backends have some facilities to improve statement parsing and compilation to limit overhead when creating commonly used query.
But for backends that does not support this kind optimization you can keep prepared statement and use it later with new references.
To do such, prepare a statement as usual, you have to use `exchange` to bind new variables to statement object, then `execute` statement and finish by cleaning bound references with `bind_clean_up`.
```cpp
sql << "CREATE TABLE test(a INTEGER)";
{
// prepare statement
soci::statement stmt = (sql.prepare << "INSERT INTO numbers(value) VALUES(:val)");
{
// first insert
int a0 = 0;
// update reference
stmt.exchange(soci::use(a0));
stmt.define_and_bind();
stmt.execute(true);
stmt.bind_clean_up();
}
{
// come later, second insert
int a1 = 1;
// update reference
stmt.exchange(soci::use(a1));
stmt.define_and_bind();
stmt.execute(true);
stmt.bind_clean_up();
}
}
{
std::vector<int> v(10);
sql << "SELECT value FROM numbers", soci::into(v);
for (int i = 0; i < v.size(); ++i)
std::cout << "value " << i << ": " << v[i] << std::endl;
}
```

such as

        // first insert
        int a0 = 0;

        // update reference
        stmt.exchange(soci::use(a0));

        stmt.define_and_bind();
        stmt.execute(true);
        stmt.bind_clean_up();

So, do I need to adding the define_and_bind and bind_clean_up in my for loop in my first post in this ticket?

As far as I can see, your initial code looks fine already.
The example you're referring to is used in cases where the variable to be bound isn't necessarily available during statement creation. This isn't the case in your example, so there's no need to explicitly call define_and_bind and bind_clean_up.

asmwarrior commented

No, that's not possible because bulk operations with custom types are currently not supported.

OK, thanks for the help. So, my current implementation is the correct way(using the for loop) to add the custom types.

Yes, it is.

Thanks, and sorry a bit late response.

As far as I can see, your initial code looks fine already. The example you're referring to is used in cases where the variable to be bound isn't necessarily available during statement creation. This isn't the case in your example, so there's no need to explicitly call define_and_bind and bind_clean_up.

OK, thanks.

About the bulk operations for custom types, I see a pull request here: add support of bulk operations for ORM in ORACLE and SQLite backends #1053

Will this feature be discussed and merged in the future?