return 'SELECT last_insert_rowid()' result executed just before transaction commit about rqlite

[otoolep]

Thanks, let me see what I can do. I am re-working some rqlite (though the API should remain the same, I plan to just rework the distributed consensus part).

[otoolep]

What do you see if you issue the query today? I have not tested it.

[zmedico]

What do you see if you issue the query today? I have not tested it.

The last_insert_rowid() function works fine as it is, for example:

$ curl -L -XPOST 'localhost:4001/db?pretty&explain&transaction' -d '
CREATE TABLE foo (id integer not null primary key, name text);
INSERT INTO foo(name) VALUES("fiona");
SELECT last_insert_rowid() as rowid;
'
{
    "time": "1.254978ms",
    "failures": []
}

Since the above doesn't return the row id, it has to be retrieved as a separate transaction:

$ curl -L -G 'localhost:4001/db?pretty&explain' --data-urlencode 'q=SELECT last_insert_rowid() as rowid'
{
    "time": "282.159µs",
    "failures": [],
    "rows": [
        {
            "rowid": "1"
        }
    ]
}

[otoolep]

OK, I believe I know why this is -- it's because rqlite isn't built to support statements that mutate the database and return results, in the same POST body.

I should be able to fix that, I've never been too happy about it, and know better now.

[otoolep]

Let me try to fix it as part of the work I am doing in this branch: https://github.com/otoolep/rqlite/tree/hrqlite

[zmedico]

It's also useful to return number of rows affected by an UPDATE statement. This value can be queried using SELECT changes() as changes. If have tested this function with rqlite, and it also works:

$  curl -L -G 'localhost:4001/db?pretty&explain' --data-urlencode 'q=
SELECT changes() as changes, last_insert_rowid() as last_insert_rowid'
{
    "time": "225.588µs",
    "failures": [],
    "rows": [
        {
            "changes": "1",
            "last_insert_rowid": "1"
        }
    ]
}

I can use this to implement python's Cursor.rowcount attribute.

EDIT: Now I see https://github.com/otoolep/rqlite/issues/4.

[otoolep]

This should now be fully supported on master. Please take a look @zmedico and let me know what you think.

[zmedico]

It works great, thank you!

[otoolep]

@zmedico -- are you planning to make your Python driver generally available? If so, I'd be interested in seeing it.

[otoolep]

I guess by "release soon" you do mean to make it available to general use.

[zmedico]

Yeah, I'm releasing it with MIT license:

https://github.com/zmedico/pyrqlite

Lots of things can be simplified thanks to the rqlite-2.0 API.

[zmedico]

I also made an sqlalchemy dialect that I used to test pyrqlite:

https://github.com/zmedico/sqlalchemy-rqlite

[otoolep]

Nice.

Depending on your seriousness, we should agree to lock down the API pretty soon. I think it's close, but I'd be interested in any feedback.

[zmedico]

I'm pretty happy with the 2.0 API as it currently is, but I think that ideally the URI would include a version component so that multiple versions can be supported simultaneously. Each version doesn't have to be supported forever. I'm not particularly invested in the 1.0 API, so I don't really care if it's dropped now (though it shouldn't be terribly difficult to continue supporting it).

What are your thoughts on adding a version component to the URI?

[otoolep]

I'm going to resist versioning the API for now, since the 1.0 API was badly done, and should never have existed in that form (after spending a couple of years building databases I know better now, you see). I think versioning might be overkill for my goals for rqlite.

As for the API, I've made some final changes. Check out the https://github.com/otoolep/rqlite/blob/master/README.md for full details.

[zmedico]

As for the API, I've made some final changes.

Looks good.

[zmedico]

Now pyrqlite uses the rqlite 2.0 API.