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
