SQLite Forum

I think what the respondents are trying to say that your question is out of scope here. You need a PhP tutorial on sqlite use, and clearly the usual suspects nor me can help you there.

It is certainly possible to store an image as a blob.

The first duckduck for "call sqlite from php blob":

https://www.sqlitetutorial.net/sqlite-php/blob/

Thanks for your considered answer. I appreciate it. I am just posting to follow up for completeness.

Yes, we did build the unmodified version first and that worked fine, confirming it is not our setup.

We created a C-style function that accepts a db name, opens it, attaches a "shared" database file, installs a preupdate callback, and returns the open sqlite3* to the user. Relatively standard and works like a charm. The reason we want this is because our code needs both the "local" and the "shared" db present, and this is the method we prefer.

Then we compiled sqlite into a custom library where our function masks/replaces the existing sqlite3_open() and sqlite3_open_v2() functions if the filename string starts with e.g. ourthing://. This works very well as well. (We happened to have done this using callbacks, but the above explains the intent.)

It stops working when we then involve the AAR and libsqliteX.so. Almost everything works except the attach step.

As was suggested: our current work-around is to skip the attach step before returning the open sqlite3*. We then use the java interface to attach the shared db, and it seems to work nicely.

If we do figure out why this happens I'll make sure to post an update here. Thanks for the constructive replies.

W

What we did: add a callback to the sqlite3 code that allows us to do things to the sqlite db if a certain pattern matches in the file (basically custom URI).

We compile this as a custom libsqliteX.so and use AAR to talk to it under android.

We also attach a database to the main db file that is opened. When this attaching db is empty: no problem. When it has tables in it: everything hangs - attach never returns.

All this happens before our modified sqlite3_open_v2() returns. It works gloriously on linux and android when using only native code.

I realize that we probably should have gone the VFS route, but this is a product in development. We will certainly look at that in the near future.

Can anyone point me to something that might be stopping us from attaching the db?

Thanks! Willem

Thank you for all the answers - that helped. We use sqlite as a buffer and interface, relying largely on the preupdate hook, but also authorizer and commit. On these tables we enforce presence of INTEGER PRIMARY KEY.

The problem I am trying to solve is how to create rows that are guaranteed to be unique across different locations that are not in direct communication.

The elegant answer I believe is to create WITHOUT ROWID tables and use an integer column and a location column as index. The location column would be set to the designation of the location. This would require some work, so I was wondering if I could dedicate the upper 32bits of the rowid to a location number, effectively creating subranges specific to each node.

All this is moot since I cannot do this with callbacks. The triggers are an option and I may look into that as a short-term fix.

Thank you!

Hi all,

Is it possible to modify the rowid in a rowid table during an insert through the C-API?

Thanks!

Thanks! We reread the API and it is indeed all there. It also makes sense.

This is two related issues. We use the C-api to interact with sqlite.

We want to force the user to have one column in each table that is marked as INTEGER PRIMARY KEY (IPK). We do this because we want to connect database records to a secondary store that uses the same integer indices. By creating a column we also ensure that e.g. VACUUM does not change the rowid values. We believe that enforcing the presence of an IPK column is the way to do this.

How we do this: If a table is created we flag this in the authorizer hook. We then verify in the commit hook that the table indeed has an IPK column using a SQL command:

SELECT * FROM pragma_table_info('table') WHERE pk = 1

Problem here is that we can only do this through the file handle the commit happens on, and I am worried about this, since I am "overwriting" the statement pointer we are committing for by preparing the new SQL query. I cannot use a separate db handle, because the db is locked during commit, of course.

Super interested on comments on immutable index approach, force the presence of IPK, and our method for doing so. Any insight is appreciated.

Oh I see. Yes, that's right. As far as I know you cannot "tag along" with a process that opened the db file separately. Triggers would probably be the way to do that.

I do think the better alternative is to have your client interact via an instrumented handle, but that is probably because I have limited experience with implementing triggers.

Thanks! That is really cool.

"Processes", so different threads. Compile for multi-threading and use the pre-update hook.

See also:

https://sqlite.org/threadsafe.html

https://www.sqlite.org/capi3ref.html#sqlite3_preupdate_count

Some more results - using shorthand. On each INSERT there is a call to `PRAGMA table_info() for that table:

CREATE A
CREATE B
CREATE C
INSERT A
INSERT B
INSERT C

** all good **

CREATE A
INSERT A
CREATE B
CREATE C
INSERT B
INSERT C

** INSERT B and INSERT C fail **

CREATE A
CREATE B
INSERT A
CREATE C
INSERT B
INSERT C

** all good **

CREATE B
INSERT B
CREATE A
INSERT A
CREATE C
INSERT C

** INSERT A and INSERT C fail **

Basically, if a table is created, then an insert in that table (with call to PRAGMA table_info), the second and subsequent calls to PRAGMA table_info() fail. But... all these succeed if PRAGMA ... is replaced with SELECT * FROM pragma_table_info().

The plot thickens.

Swapping the order of create+insert does not matter, and results in the same error on the second table.

But: creating both tables first, and then inserting data in either - that does make a difference, and the aberrant behaviour is not present.

Note that in our implementation we only respond to data being added (in preupdate), and if it is in a table/column we don't know about, we use PRAGMA table_info() to get the information.

What this means is that it works if: - multiple tables are created - then first table, on insert: pragma table_info works - next table, on insert: pragma table_info works

As opposed to previously: - create table A - on insert in A: pragma table_info works - create table B - on insert in B: pragma table_info does not work

Yes - the types - I know. We are also testing how to represent and retrieve different types and track the declared type for our application. Thanks about the comment for the quotes!

No, not as yet. And we use the C API directly. All this is submitted using the prepare/step functions. The test scripts are submitted through a test fixture we created.

I can't help but think it is a quirk in the implementation because select works and gives exactly the same information. What is also telling is that the result code is SQLITE_DONE, not some kind of error, suggesting the API simply believes the table should be empty.

I should add that the failure occurs on the first insert command after the last create table statement.

I retested it and the problem remains the same: works with select, fails with pragma.

Putting a breakpoint in the code on where the result code returns (SQLITE_DONE where SQLITE_ROW is expected), and opening the file with sqlite3 shell, pragma table_info(people); works as expected.

It is as if the route by which "people" is associated with the table data is subtly different depending on whether I use select or pragma.

Thank you for the follow up!

It is during pre-update - INSERT.

This is the sequence of commands that produce the result, starting with an empty db file (the content is arbitrary and part of a test script):

CREATE TABLE dogs (tag INTEGER PRIMARY KEY, name VARCHAR, age INT, height REAL);
INSERT INTO dogs (name, age, height) VALUES ("Scooby", 8, 0.8);
INSERT INTO dogs (name, age, height) VALUES ("Scrappy", 6, 0.4);

CREATE TABLE people (rowid INTEGER PRIMARY KEY, name VARCHAR, age INT, height REAL);

The described failure occurs on the second CREATE TABLE, performing PRAGMA tableinfo(people) in the preupdate hook. For the first CREATE it works fine.

We open separate db handles for the client side (submitting the test script commands) and running the PRAGMA table_info; the preupdate hook is installed on the client handle.

After the error and in my debugging I carefully went over all statements and made sure everything was finalized properly. The command completes normally. This is supported by the fact that replacing the sequence PRAGMA tableinfo... with SELECT * FROM pragma_table_info... it does work as expected.

I did some refactoring yesterday and will try again with the PRAGMA version to see if something has changed.

We use the preupdate hook to track changes made to the database. In the implementation, in the preupdate hook, we used (via the API):

  PRAGMA table_info(tablename)

We found that in a test script this worked the first time, but not the second. Replacing the PRAGMA statement with the following solved the issue:

  SELECT * FROM pragma_table_info(tablename)

I have little insight into how or why one works and the other doesn't, or why this happens only the second time. Careful review of the code shows that previous statements are finalized when we get to this part of the code.