Unique id ?

[landeiro]

Hi,
this might be stupid question, but I can't see a unique id associated
to the object inserted.
It will exist in the future?

Thanks.

My test:


.new test.db
CREATE COLLECTION landeiro;
INSERT INTO landeiro VALUE {myValue:3};
INSERT INTO landeiro VALUE {myValue:3};
SELECT FROM landeiro;
--------- query results ---------
{"myValue":3}
{"myValue":3}
---------------------------------

[Bernardo Ramos]

Good question. The MongoDB has one unique id to each added document.

[Bernardo Ramos]

Here are my thoughts about this issue:

In the SQLite, if we explicitly create a field with the INTEGER
PRIMARY KEY type, then it
becomes the rowid and when we select all the fields from the table, it
returns this field.

But as in the UnQL we do not create a schema, we need another way to
tell the engine if
we want the doc_id returned with the other properties from the
document.

Sometimes we want to retrieve the exact document that we stored in the
collection, with no
new added properties. And in other times we want the doc_id (or
whatever it is called) returned, as in a customers
collection...


One way could be to use a pragma:

PRAGMA return_doc_id=1; SELECT * FROM docs


Another way could be putting it explicitly it in the SELECT statement:


The command below should return the doc_id:

SELECT doc_id, * FROM docs


And these ones shouldn't:

SELECT * FROM docs
SELECT FROM docs



But the word "doc_id" is not so good for customers, products, orders,
and so on...

So we could turn the word "id" a reserved one, and if the document
does not have it, the engine add it to the document. And if the
document already have it, then it should not have the same value as
the existing ones in the collection to avoid a index collision. And if
the document has it with a null value, then the engine can change the
value with a new id...

I think that this last idea about the word "id" and the use of a
pragma could be good, at least for the UnQLite.


Or to use another way to inform the engine if the document id may to
be returned or not...

[Bernardo Ramos]

RETURNING THE ROWID WITH THE RESULTS
(related to UnQLite)

Here are some notes:

If the engine would to store only objects ({...}), then the rowid/
docid could be returned inside the object, like this:

[
{rowid: 123, name: "John", email: "so...@email.com"},
{rowid: 124, name: "Mary", email: "ma...@email.com"}
]

But... I am seeing that the engine may accept arrays/lists and single
values too.
Then, how may the rowid be returned in this cases?

One way should be using a single json string containing the rowid with
each value returned, like this:

[
[123, value1],
[124, value2]
]

Example:

[
[123, 12500],
[124, 1.25],
[125, true],
[126, false],
[127, null],
[128, [1,2,3]],
[129, {name: "John", email: "so...@email.com"}]
]

or if we use a PRAGMA to disable the returning of the rowid, then it
should return only the list with the values:

[
12500,
1.25,
true,
false,
null,
[1,2,3],
{name: "John", email: "so...@email.com"}
]

I think that returning the values as a single json string makes it
better to be handled by the majority of the languages than using
another structure.

BUT...

I still think that having a way to fill an id field inside an object
when we store it should be great. In my case, I will use the engine to
store only objects ({...}) and then it would be better to have the
rowid returned INSIDE the object...
Then we could use code like this to retrieve the data:

id = customers[3].id
name = customers[3].name
email = customers[3].email

Instead of:

id = customers[3][0]
name = customers[3][1].name
email = customers[3][1].email

Or:

id = customers[3][0]
customer = customers[3][1]
name = customer.name
email = customer.email

Well, maybe this discussion is not linked to the UnQL language itself,
and each engine can retrieve these values in a different way. If there
is some better place to discuss about the UnQLite, please tell me.

[Bernardo Ramos]

NEW IDEAS:

1. To use the "uid" keyword to identify the unique id.

2. To use a PRAGMA to make the engine (UnQLite) to store the uid with
each object when we add them to the collection. This option should be
activated by collection, so in some collections of objects we can
activate the option (using the pragma or in the CREATE COLLECTION
statement) to add the uid to them, and in other collections we may not
use this.

The option could be called:

use_uid -- this one may not be right since the engine may use an uid
anyway, even internally
expose_uid
include_uid
insert_uid
force_uid

or another...


Examples:


--- CREATING THE COLLECTION ----------------------------------

This one will tell the engine to retrieve the uid as a property INSIDE
the objects: (it is only for the retrieval time)

CREATE COLLECTION Contacts OPTIONS insert_uid


These ones doesn't:

CREATE COLLECTION Messages

CREATE COLLECTION SampleValues



--- INSERTING DATA -------------------------------------------

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

INSERT INTO Messages VALUE {user: "John", message: "testing this new
technology"}
INSERT INTO Messages VALUE {type: "note", message: "Hi there!"}

INSERT INTO SampleValues VALUE 55.468
INSERT INTO SampleValues VALUE 47.022


--- RETRIEVING DATA - MODE 1 - PRAGMA return_uid=0 -----------
(Default)

SELECT FROM Contacts

Should return:

[
{uid: 1, name: "John", email: "jo...@email.org"},
{uid: 2, name: "Mary", email: "ma...@email.org"}
]

SELECT FROM Messages

Should return:

[
{user: "John", message: "testing this new technology"},
{type: "note", message: "Hi there!"}
]

SELECT FROM SampleValues

Should return:

[
55.468,
47.022
]


--- RETRIEVING DATA - MODE 2 - PRAGMA return_uid=1 -----------

SELECT FROM Contacts

Should return:

[
[1, {uid: 1, name: "John", email: "jo...@email.org"}],
[2, {uid: 2, name: "Mary", email: "ma...@email.org"}]
]

SELECT FROM Messages

Should return:

[
[1, {user: "John", message: "testing this new technology"}],
[2, {type: "note", message: "Hi there!"}]
]

SELECT FROM SampleValues

Should return:

[
[1, 55.468],
[2, 47.022]
]


--------------------------------------------------------------

Or we could have 3 values for the PRAGMA:

0 - Never return the uid

1 - Return the uid only inside objects in which the collection option
was specified (default mode)

2 - Always return the uid in a array, being it the first value


--------------------------------------------------------------

Notes:

For the engine, the unique id always exists, linked to each document,
even if it does not expose it (It is what I think).

What we are discussing here is an easy and portable way to retrieve
this value.

The unique id does not need to be stored inside the json object ({})
(note that some 'documents' are arrays and some are simple values), it
can be inserted on the objects only at the retrieval time.


--------------------------------------------------------------

Well, we are open to community opinions.

You're welcome!

[Bernardo Ramos]

ANOTHER CONTRIBUTION:

I think that the option and the pragma above are sufficient to solve
the unique id problem, at least for json documents (if someone has
some different ideas, please post them).

But...

If we want to return only some fields from the document, including the
uid, we could use a SELECT query like this:


SELECT uid, type FROM collection


The response should contain the uid as a property, like this:

[
{uid: 1, type: "..."},
{uid: 2, type: "..."}
]

Because if the field names where supplied, then it means that it is
expected that the collection holds only (json) objects.

This type of query would not be expected to be used in a collection
that contains arrays and single values as documents/records.



And the result would be the same if the return_uid pragma is 0 or 1.
If it is 2, then it could return it outside the document too, like
this:

[
[1, {uid: 1, type: "..."}],
[2, {uid: 2, type: "..."}]
]