INSERT Statement Idea

[Bernardo Ramos]

Sometimes we have a json string containing the list of objects or
values to be inserted in the collection. In these cases it is better
to pass the entire string to the engine than to have to parse and
separate the values to be passed one by one to the engine.

So we could have a variation in the INSERT statement to accept a list
of values.

Then we have the first statement to insert a single value at a time
and the second one to pass a list:

INSERT INTO collection VALUE value

INSERT INTO collection VALUES [value1, value2, value3]

Example:

1. Using one value at a time:

INSERT INTO contacts VALUE {name: "John", email: "a...@email.org"}
INSERT INTO contacts VALUE {name: "Mary", email: "ma...@email.org"}
INSERT INTO contacts VALUE {name: "Billy", email: "bi...@email.org"}

2. Passing the list to the engine:

INSERT INTO contacts VALUES [{name: "John", email: "jo...@email.org"},
{name: "Mary", email: "ma...@email.org"},
{name: "Billy", email: "bi...@email.org"}
]

In this last case the engine may to insert 3 values to the collection.

In this case the last_insert_id function (if it should exist) can
return the id of the first one, or it can return an array with the
ids.

Or we could have 2 functions:

last_insert_id: return a 64 bit integer

last_insert_ids: return a json string containing a list with the ids.

[Richard Hipp]

On Sat, Sep 24, 2011 at 4:33 PM, Bernardo Ramos <be...@gensis.com.br> wrote:

Sometimes we have a json string containing the list of objects or
values to be inserted in the collection. In these cases it is better
to pass the entire string to the engine than to have to parse and
separate the values to be passed one by one to the engine.

Example:

1. Using one value at a time:

INSERT INTO contacts VALUE {name: "John", email: "a...@email.org"}
INSERT INTO contacts VALUE {name: "Mary", email: "ma...@email.org"}
INSERT INTO contacts VALUE {name: "Billy", email: "bi...@email.org"}

2. Passing the list to the engine:

INSERT INTO contacts VALUES [{name: "John", email: "jo...@email.org"},
 {name: "Mary", email: "ma...@email.org"},
 {name: "Billy", email: "bi...@email.org"}
 ]

As currently implemented, the value inserted need not be an object.  You can insert any valid JSON - an atom, an array, or an object - and it goes in as a single document.  So, your example above wouldn't insert three separate documents, each of which is an object.  It would instead create a single document which is an array of objects.

Your suggestion assumes that the database stores only JSON objects - never atoms or arrays.
 

In this last case the engine may to insert 3 values to the collection.

In this case the last_insert_id function (if it should exist) can
return the id of the first one, or it can return an array with the
ids.

Or we could have 2 functions:

last_insert_id: return a 64 bit integer

last_insert_ids: return a json string containing a list with the ids.

--
D. Richard Hipp
d...@sqlite.org

[Bernardo Ramos]

Hi Richard!

Thanks for your answer.

I don't know if you realized that there are 2 different INSERT
commands, the first use the keyword VALUE and the second uses the
keyword VALUES, in plural.

So the first already existing statement should add an array if we use
it like this example:

INSERT INTO contacts VALUE [{name: "John", email: "jo...@email.org"},

{name: "Mary", email: "ma...@email.org"},
{name: "Billy", email: "bi...@email.org"}
]

But this one below is a new idea to be implemented to make easier to
the developers to insert many atoms at a time without the need to
parse the json string first:

INSERT INTO contacts VALUES [{name: "John", email: "jo...@email.org"},
{name: "Mary", email: "ma...@email.org"},
{name: "Billy", email: "bi...@email.org"}
]

Note that this last statement differs from the previous (it uses the
VALUES keyword instead of VALUE) and may insert each atom from the
array separately.

This second implementation does not remove the previous, the idea
would be to have both working. The first may have an behavior, and the
second another.

Note that for us (developers) it is better to pass the json array
string to the engine than to have to parse it, separate each atom, and
then create many INSERT commands like this:

INSERT INTO contacts VALUE {name: "John", email: "jo...@email.org"}

INSERT INTO contacts VALUE {name: "Mary", email: "ma...@email.org"}
INSERT INTO contacts VALUE {name: "Billy", email: "bi...@email.org"}

The engine will already parse it, so we don't need to make it two
times. It would make our life much easier.

[Bernardo Ramos]

Oh, sorry! I misused the term "atom" in my previous explanation.

Here is another example:

This should insert an array:

INSERT INTO collection VALUE [11, 22, 33, 44]

And this should insert 4 objects (note the plural):

INSERT INTO collection VALUES [11, 22, 33, 44]

As this one:

INSERT INTO collection VALUES [true, [1,2,3], {type: "test"}, null]

[Richard Hipp]

On Sat, Sep 24, 2011 at 5:31 PM, Bernardo Ramos <be...@gensis.com.br> wrote:

Hi Richard!

Thanks for your answer.

I don't know if you realized that there are 2 different INSERT
commands, the first use the keyword VALUE and the second uses the
keyword VALUES, in plural.

I didn't notice that.  Which kind of points out why that might not be the best choice of syntax - too easy to confuse VALUE and VALUES.  Perhaps something more distinctive?
 

[Bernardo Ramos]

We could use something like this:

INSERT INTO collection LIST [value1, value2, value3]
INSERT INTO collection ARRAY [value1, value2, value3]

But I think it may to confuse more, someone may think that we must
always use this to insert a list/array.

If we have a good documentation that differs VALUE from VALUES, than
it could be used.

Well, it would be good to have many people giving opinions about that.

This is not urgent, we can live without this by a time. I think that
the rowid issue is far more important by now, and may be solved before
we have a working library.