So in the original code if I added a NOT INDEXED it would be valid? Also, would an ORDER BY Auth_id ASC fix the issue, since I an not adding any new rows the auth_ids would remain constant?
Wow I did not know that you could call open multiple times on the same database! So the following is valid and safe (considering POSIX locking etc.) sqlite3_open("database1", &db1); sqlite3_open("database1", &db2); // loop start // BEGIN TRANSACTION BOTH dbs // do SELECT on db1 // do UPDATE on db2 // END TRANSACTION // loop end // close db1 // use db2 for rest of the program... I have not seen this pattern within any sqlite3 code before so I think it is not well known that this is a design pattern for searching and updating at the same time. If I were determined to stay within defined behavior AND to only use one connection, would the following updated code attain that. #define SELECT_EXPIRED_IDS \ "SELECT Auth_id FROM AuthTable WHERE Auth_id > ? AND expiration < ? " \ "ORDER BY Auth_id ASC LIMIT 128;" #define UPDATE_SESID_EXPIRED \ "UPDATE AuthTable SET sesCookie=?, expiration=? WHERE Auth_id=?;" static void expire_sesid(void) { int auth_ids[128] = {0}; int i=0, j; /* get raw current time */ current_time = get_current_db_time(false); /* prepare SQL queries */ sqlite3_prepare_v2(db, SELECT_EXPIRED_IDS, -1, &expire_info, NULL); sqlite3_prepare_v2(db, UPDATE_SESID_EXPIRED, -1, &update_ses_expired, NULL); do { sqlite3_bind_int(expire_info, 1, auth_ids[i]); sqlite3_bind_int(expire_info, 2, current_time); /* while there is work to be done */ i=0; while (sqlite3_step(expire_info) == SQLITE_ROW) { auth_ids[i++] = sqlite3_column_int(expire_info, 0); /* auth_id */ } sqlite3_reset(expire_info); for (j=0; j < i; j++) { /* generate new session id */ generate_ses_id(ses_id); /* update ses_id and internal expiration to a year ahead, log in will set it to a month for user log in */ sqlite3_bind_text(update_ses_expired, 1, ses_id, 16, SQLITE_STATIC); sqlite3_bind_int64(update_ses_expired, 2, current_time_plus_year); sqlite3_bind_int(update_ses_expired, 3, auth_ids[j]); sqlite3_step(update_ses_expired); sqlite3_reset(update_ses_expired); } } while ( i == 128 ); /* all work has completed */ sqlite3_finalize(expire_info); sqlite3_finalize(update_ses_expired); return; } On Sun, Sep 1, 2019 at 3:47 PM Keith Medcalf <kmedc...@dessus.com> wrote: > > On Sunday, 1 September, 2019 11:12, Alexander Vega <amve...@mail.usf.edu> > wrote: > > >Thank you Keith for your answer. It has led me to more questions. > > >"though you may or may not have visited all rows" > >From the documentation I did not get the impression that you would > >ever not visit ALL ROWS at least once. Is there a technical reason > >for this? I would assume a full table scan is walking the un-ordered > >leaf pages of the B*tree? > > How do you know that you are doing a table scan? This certainly cannot be > assumed. Perhaps the AuthTable has 57 columns with a total length of > several hundred bytes per row but there also happens to be an index on a > subset of the columns that includes the two columns that you have asked > for. Perhaps you are "table scanning" that covering index instead (because > it is cheaper than reading the actual table)? There are ways to insist on > a table scan (select ... from table NOT INDEXED ...) for example. However, > you left it up to the database engine to choose the most cost effective way > to answer your select (which is how SQL works ... it is a declarative > language ... you declare what you want and the database figures out the > best way to go about giving you what you asked for). > > As a result of updating the first such row thus received the index has now > changed such that the row you are operating on became the last row in the > index being scanned. Therefore there is no "next" row. You will have > visited only one row, even though there might have been millions of rows in > the table. > > >"Your outer query should probably be "select auth_id, expiration from > >AuthTable where expiration <= ? order by +auth_id, +expiration" and > >binding current_time as the parameter since there is no point in > >retrieving rows that you will not be updating is there? " > > >You are correct that does make sense. I guess I was trying avoid any > >ambiguities of a WHERE clause on the SELECT because I do not > >understand its behavior in this circumstance. > > If you cannot understand the behaviour with a WHERE clause, then what > would make you think that one without a WHERE clause would be any more > transparent, especially given that all Relational Databases are designed to > provide you the results you asked for as efficiently as possible? Perhaps > in a few days you will discover that you need to create another index for > some other purpose, and that causes SQLite3 to obtain what you said you > wanted in an entirely different manner. When you make any change to the > database do you re-evaluate the implementation details of every previously > written SQL statement to see if it still compatible with the details you > depended on? What about it you update the version of SQLite3? You should > not be dependent on the peculiarities of the implementation since they > might change at any time. > > >You mentioned two database connections to the same database. Is this > >going to work if I am using Threadsafe mode = 0? > > Yes. Threadsafe mode only affects programs having multiple threads making > calls into the sqlite3 library. These are independent variables (that is > you can have X threads and Y connections, and X is independent of Y) just > because you have 47 connections does not mean that you have more than 1 > thread, nor does having 47 threads mean that you have more than 1 > connection. Threads are commenced with _beginthread (or equivalent for the > OS) calls and connections are commenced with sqlite3_open* calls. The > _beginthread operations result in the creation of a thread and the > sqlite3_open* calls create a database connection -- they are not related to > each other in any way. Also consider that it is entirely possible for a > program to have hundreds of threads yet still only be single-threaded as > far as sqlite3 is concerned if only one of those threads makes use of the > sqlite3 library, and that one thread may use hundreds of database > connections either serially or in parallel or in some combination thereof. > > >Would the second connection be done through an attach? > > No. The attach statement attaches a database to a connection. You have > to have opened the connection first. Connections are created with the > sqlite3_open* functions which return a pointer to a database connection. > > >Does this conversation change if I wrap the whole select and updates > >in one transaction? e.g. BEGIN...END > > No, because isolation is only BETWEEN connections, not WITHIN > connections. And the transaction state is per connection. > > -- > The fact that there's a Highway to Hell but only a Stairway to Heaven says > a lot about anticipated traffic volume. > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users