C++ driver to cockroachDB

[bertini...@googlemail.com]

Hi guys,
I was wondering what could be the best way to write a C++ driver to connect and communicate
with cockroachDB servers ( go world).
So far i got some ideas

1/ - try to write a client combining C++ functions wrapper with swig
2/ - using Messaging to send universal serialized format ( ex. ProtoBuf)
to communicate between C++ and go world

So my question to the experts: which solution a priori is preferable and
if existing, which other solution would you suggest ?

Thanks,
Denis

PS: I use an C++ program to communicate with SQL database, and would like to do the same
with cockroachDB.

[Ben Darnell]

We're working towards implementing the PostgreSQL network protocol. It's not quite ready yet, but once it's there you'll be able to use a PostgreSQL driver from any language to connect to CockroachDB.

-Ben

--
You received this message because you are subscribed to the Google Groups "Cockroach DB" group.
To unsubscribe from this group and stop receiving emails from it, send an email to cockroach-db...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[bertini...@googlemail.com]

Hi
Great news !

What is the time schedule for this new PostgreSQL network protocol ?
Very important for me as well : do you plan to support
AUTO INCREMENT or SERIAL data type. I know it is quite
problematic in a distributed database environment ...
Or may be there will be another way to achieve the functionality in cockroachdb?

Regards,
Denis

[Peter Mattis]

We do not have plans to support AUTO INCREMENT or SERIAL. As you noticed, they are problematic to implement in a performant manner in a distributed environment. As an alternative, we support 3 different builtin functions for probabilistically unique random keys: experimental_unique_bytes(), experimental_unique_int() and experimental_uuid_v4(). The experimental_unique_bytes() and experimental_unique_int() builtins are composed of a timestamp and nonce and will be mostly sorted according to their allocation order. See snowflake ids for a similar id allocation scheme.

If AUTO INCREMENT or SERIAL is a hard requirement then perhaps you can provide details of your need so we can see if there are any other alternatives on our end. 

[bertini...@googlemail.com]

Hi

I just need the database to have the possibility to generate a unique id ( sequence number ) when adding any row on a table ( and it should be an int ). The sequence number is used later on like a pointer to join tables ... ( i avoid like this explicit consuming SQL join .... )
Cheers,
Denis

[Peter Mattis]

A monotonically increasing generator like AUTO INCREMENT or SERIAL is quite a bit more restrictive than a unique id. If you don't require strict monotonicity, then experimental_unique_int() should do what you want. If you do require strict monotonicity, then I'd need to understand a bit more about your application and schema to give better advice. 

[bertini...@googlemail.com]

Hi Peter,
May be it is better to explain with table pictures.

Go to the links to see an example of how i use sequence number in my tables.

Validation data table use first to find an appropriate version of the data
i want to fetch from the data base.

http://i1075.photobucket.com/albums/w433/denis8/entry_val_zpshyqly0cz.png

On the right hand side ( first column is the seq number define as AUTO_INCREMENT)
From the validation data table the user asks " give me the data validated for time=t )
the seqno is retrieved from the validation table and use ( as a pointer ) to fetch the corresponding
data in the following table:

http://i1075.photobucket.com/albums/w433/denis8/entry_data_zpsj0vtvgoo.png

So the seqno is used as a pointer to fetch the data according to a special version ( time Period)

This design works with standard SQL database using AUTO_INCREMENT or SERIAL data type.
The point is how to achieve this design in an distributed database? One should invent a data type
which will create a global unique Id but then incrementation of this global Id should be centralized
....

[Peter Mattis]

It would be easier and more concrete to understand your schema and queries as SQL (i.e. the table definitions and the corresponding queries you're performing).

I'm not quite clear on your requirement for an AUTO INCREMENT type. If you're querying your "validation data table" to find data validated for time=t, can't you keep some ID for each row there that can be used to fetch the data from your second table. AUTO INCREMENT or SERIAL is necessary if you have a table that is being appended to and consumers that need to read the data in exactly the order it was added. Doing this in a distributed database is necessarily problematic because the "tail" of this table is a contention point and bottleneck. One solution is to shard the table into multiple pieces so that instead of 1 table being appended to you have N. The upside is that you move from 1 bottleneck to N. The downside is that the reader has to read from all N of the tables and merge. Note that there doesn't actually need to be N distinct tables. It could be implemented by having some sort of "shard" key as the first component of the primary key.

[bertini...@googlemail.com]

Hi Peter,
Take the picture i sent in my previous mail and here is the corresponding
table SQL description. In the validity table my actual design uses
the AUTO_INCREMENT data type (mysql).


CREATE TABLE FAIRDBLOGENTRYVAL(
SEQNO INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
TIMESTART DATETIME NOT NULL,
TIMEEND DATETIME NOT NULL,
DETID TINYINT,
DATAID TINYINT,
VERSION INT,
COMPOSITEID INT,
TIMEINCR DATETIME NOT NULL,
TIMETRANS DATETIME NOT NULL
);

CREATE TABLE FAIRDBLOGENTRY(
SEQNO INT,
ROW_ID INT,
TABLENAME TEXT,
MIN_SEQNO INT NOT NULL,
MAX_SEQNO INT NOT NULL,
N_SEQNO INT NOT NULL,
USERNAME TEXT,
PROCESS_ID TEXT,
HOSTNAME TEXT,
SERVERNAME TEXT,
LOG_TITLE TEXT
);

A typical query to search for a proper SEQNO in that data table would be something like ( another Validity table is used here STSQASENSORPARVAL);


[2015/12/02 09:30:32] -I- FairDb: FairDbProxy.cxx::QueryValidity():[300] $ db_id: 0 SQL query: select * from STSQASENSORPARVAL where TimeStart <= '2015-12-11 18:00:00' and TimeEnd > '2015-11-21 18:00:00' and DETID & 32 and DATAID & 1 and Version = 0 order by TIMEINCR desc;
[2015/12/02 09:30:32] -I- FairDb: FairDbStatement.cxx::ExecuteQuery():[72] $ Server:r3b SQL:select * from STSQASENSORPARVAL where TimeStart <= '2015-12-11 18:00:00' and TimeEnd > '2015-11-21 18:00:00' and DETID & 32 and DATAID & 1 and Version = 0 order by TIMEINCR desc;

Of course other select statements are used in combination with these above to find the corresponding SEQNO for a particular validity interval definition.
With the SEQNO number one can than fetch all rows from the corresponding data table. The concept works fine in any standard SQL database.

My question is what happens if the two tables xxDATA and xxVAL are now define in cockroach ? When i query on the VAL table i get a SEQNO, with this SEQNO i
need to fetch the rows in a second table ( kind of handmade JOIN if you want ) . To be consistent in the procedure may the application itself
has to then define a global SEQNO ?


Cheers,
Denis