Hello Dennis, please ignore my previous response - it is all wrong because I used the wrong table definitions and data (see below for corrections). My appologies for this, and thanks for holding on to sqlite3_column_rowid.
>I like your proposal, and I understand what you want it for (at least I think >I do :-) ). Judging from your writing I am sure you understand perfectly well! >I just think that the cases where SQLite can't determine a unique source for >the column data, even though it may be taking the data directly from a >particular table column, should be treated the same as a calculated value and >return a sentinel rowid value of -1. I agree: whenever SQLite cannot determine a unique combination of database, table, origin and RowID - for whatever reason - sqlite3_column_rowid should return -1 or an optional error code (with the appropriate API) and set an error message, if possible. >I'm just saying that you can't update the columns used to join tables by >editing the results of a query that joins two tables using either of these >mechanisms. You can edit the other columns that result from the join, but to >edit the joined columns you would need to use a different query. I would be very happy with this, especially since joined columns are likely to be primary index columns which hardly ever require manual changes anyway. Even if sqlite3_column_rowid can sometimes not exactly identify a data field, it can still do so reliably with the majority of queries IMHO. It could at least do so much better than any other work-around I can think of. This would finally enable applications to allow users to edit most of their custom queries and views which are not already build into the application. If at all possible, I would really, really love to see sqlite3_column_rowid or some similar functionality to become part of SQLite. Are there any views from the main developers? I'd be very much interested in their feedback! Thanks & Regards, Ralf --------------------- For completeness, here are the correct test results: CREATE TABLE a (c1, c2); INSERT INTO a VALUES (1, 2); CREATE TABLE b (c1, c3); INSERT INTO b VALUES (1, 3); ---------- Select statement 1: SELECT c1, c2, c3 FROM a NATURAL JOIN b; Results: sqlite_column_int(0) returns 1 sqlite_column_table_name(0) returns 'a' sqlite_column_origin_name(0) returns 'c1' sqlite_column_int(1) returns 2 sqlite_column_table_name(1) returns 'a' sqlite_column_origin_name(1) returns 'c2' sqlite_column_int(2) returns 3 sqlite_column_table_name(2) returns 'b' sqlite_column_origin_name(2) returns 'c3' ---------- Select statement 2: SELECT c1, c2, c3 FROM a JOIN b USING(c1); Results: sqlite_column_int(0) returns 1 sqlite_column_table_name(0) returns 'a' sqlite_column_origin_name(0) returns 'c1' sqlite_column_int(1) returns 2 sqlite_column_table_name(1) returns 'a' sqlite_column_origin_name(1) returns 'c2' sqlite_column_int(2) returns 3 sqlite_column_table_name(2) returns 'b' sqlite_column_origin_name(2) returns 'c3' ---------- Select statement 3: SELECT * FROM a JOIN b on a.c1 = b.c1; Results: sqlite_column_int(0) returns 1 sqlite_column_table_name(0) returns 'a' sqlite_column_origin_name(0) returns 'c1' sqlite_column_int(1) returns 2 sqlite_column_table_name(1) returns 'a' sqlite_column_origin_name(1) returns 'c2' sqlite_column_int(2) returns 1 sqlite_column_table_name(2) returns 'b' sqlite_column_origin_name(2) returns 'c1' sqlite_column_int(3) returns 3 sqlite_column_table_name(3) returns 'b' sqlite_column_origin_name(3) returns 'c3' --------- Select statement 4: SELECT c1, c2, c3 FROM a JOIN b on a.c1 = b.c1; Results: Error SQLITE_ERROR: ambiguous column name: c1 ---------- Select statement 5: SELECT b.c1, c2, c3 FROM a JOIN b on a.c1 = b.c1; Results: sqlite_column_int(0) returns 1 sqlite_column_table_name(0) returns 'b' sqlite_column_origin_name(0) returns 'c1' sqlite_column_int(1) returns 2 sqlite_column_table_name(1) returns 'a' sqlite_column_origin_name(1) returns 'c2' sqlite_column_int(2) returns 3 sqlite_column_table_name(2) returns 'b' sqlite_column_origin_name(2) returns 'c3'
