SQLite Forum

Timeline
Login

50 most recent forum posts

2022-01-28
02:24 Reply: Include extensions at compile time (artifact: 4eb852e31c user: anonymous)

Anyways, I managed to include my extension at compile time without SQLITE_EXTRA_INIT. I got a checkout of the latest source, and I simply modified the src/shell.c.in includes, and added a call to the init function near the blocks of other init functions at roughly line 4767 of src/shell.c.in.

I'm just putting this here, if anyone would like to know how it's done.

01:32 Reply: SQLITE_FCNTL_LAST_ERRNO (artifact: aa065866b1 user: RandomCoder)

It appears to just get the errno from last I/O error.

"No longer in use" is for SQLITE_FCNTL_SYNC_OMITTED. The other oddball "Used by the cksmvfs VFS module only" is "SQLITE_FCNTL_CKSM_FILE"

2022-01-27
23:46 Post: SQLITE_FCNTL_LAST_ERRNO (artifact: c1bd305cbc user: anonymous)

What does SQLITE_FCNTL_LAST_ERRNO do? It is not documented on the page with the rest of the op codes. https://www.sqlite.org/c3ref/c_fcntl_begin_atomic_write.html

Also, there is a bullet point on that page that just says "No longer in use." but does not indicate which op code it's referring to.

22:41 Reply: Include extensions at compile time (artifact: 8162973f1d user: jake)

One way is to use the undocumented compile option SQLITE_EXTRA_INIT as described here by Keith Medcalf.

22:40 Reply: Doc: Lack of punctuation leads to confusion (artifact: 08b9897156 user: cuz)

Had a chuckle on this observation - reminded me of the old programmer joke:

You know your partner is a programmer when...
You write in the shopping list: "Bring 2 loaves of bread, and if they have eggs, bring a dozen.", to then find the diligently returned shopping basket contains 12 loaves.

As to the suggested change, I would even go so far as to prefer the sentences to read:

The right-hand operand of an IN or NOT IN operator has no affinity if the operand is a list. If the operand is a SELECT, it has the same affinity as the result-set expression.

But that is the most minor nit and any clear statement would honestly be fine.

21:42 Post: Doc: Lack of punctuation leads to confusion (artifact: 565a090ab5 user: hanche)

In the page on data types, section 3.2. Affinity Of Expressions, first bullet point:

The right-hand operand of an IN or NOT IN operator has no affinity if the operand is a list and has the same affinity as the affinity of the result set expression if the operand is a SELECT.
[my boldface]

That sentence needs at least a comma after the words “is a list”.

I had to read this several times to make sense of it, because when I arrived at “is a list and” my brain figured out that there are two criteria to be fulfilled for the expression to have no affinity, namely (a) it is a list, and (b) it has the same affinity as … oh wait, if it has affinity, then it supposed not to have affinity? Makes no sense. Backtrack and read it again. Nope, that is what it says. So I read the rest of the sentence, and realise that I am looking at two different statements. Once confusion has set in, it can be amazingly difficult to get unconfused sometimes.

I suggest making it two sentences:

The right-hand operand of an IN or NOT IN operator has no affinity if the operand is a list. It has the same affinity as the affinity of the result set expression if the operand is a SELECT.

20:47 Reply: Docs: julianday() returns a real number, not a string (artifact: 9a39328a66 user: kmedcalf)

The strftime(...) also accepts date-time strings with submilliseconds and returns them faithfully (it just needs to repeat the input):

sqlite> SELECT strftime('2022-01-27 07:34:42.1239');
2022-01-27 07:34:42.1239

In the same vein, strftime accepts are returns as a result what was given as the format string, if it contains no valid "formatting specifiers" -- therefore it would appear that it also accepts dates in "descriptive format" and returns them unmolested ...

select strftime('yapapalooza sex party');

All builtin date/time handling in SQLite3 is limited to millisecond precision (though the accuracy may be considerably more or considerably less -- that is an Operating System restriction and not due to SQLite3) because datetime values are stored and computed internally using an internally maintained "Offset from the Epoch" which is stored as an integer number of milliseconds since the Julian Epoch. It can be manipulated and presented in a multiplicity of formats however it can never express more accuracy than one millisecond in whatever format it is presented.

17:19 Reply: empty DB created if DB file missing - is this inherent to SQLite (artifact: fe4b885906 user: ahansen)

I don't see a way to edit my previous post so I'm adding a reply ...

Just in case somebody ends up here despite the title of this thread ...

The above code is not for System.Data.SQLite. It is for Microsoft.Data.SQLite and you'll need the Microsoft.Data.SQLite NuGet package.

For System.Data.SQLite the following returns a list a table's contents:

public static List<ActiveSaves> LoadActives()

{ var csb = new SQLiteConnectionStringBuilder

{
	DataSource = BURS_Path.DB(),
	FailIfMissing = true,   
};
using (var dbc = new SQLiteConnection(csb.ConnectionString))

{
	var input = dbc.Query<ActiveSaves>($"select * from {DBtbl.Actives}");

	try
	{
		dbc.Open();
	}

	catch (Exception ex)
	{
		ErrMsg(ex);
	}
	return input.ToList();
}

}

Obviously RandomCoder pointed me down the correct path ... Thanx again.

16:05 Post: Include extensions at compile time (artifact: afcde048be user: anonymous)

How can I add extensions to SQLite at compile time without having to run .load in the shell, or configure ~/.sqliterc and add .load commands to it?

Should I modify the INCLUDE ../ext/ statements in src/shell.c.in?

15:57 Reply: Error in UPDATE FROM examples? "GROUP BY 2)"? (artifact: e5c8b2dabd user: stephan)

Or maybe it is some sort of syntax I don't understand?

It means to group by the 2nd column. That syntax also works for ORDER BY.

15:54 Post: Error in UPDATE FROM examples? "GROUP BY 2)"? (artifact: a05a2dff69 user: anonymous)
In your "UPDATE" page (https://sqlite.org/lang_update.html), at the section of "2.2 UPDATE FROM", there is this "GROUP BY 2)" in the example:

 UPDATE inventory
   SET quantity = quantity - daily.amt
  FROM (SELECT sum(quantity) AS amt, itemId FROM sales GROUP BY 2) AS daily
 WHERE inventory.itemId = daily.itemId;

Is this "2)" should be "itemId"? Or maybe it is some sort of syntax I don't understand?

It also appears in the other two examples in 2.2.1.
14:42 Reply: Proposed JSON enhancements. (artifact: 05687574ab user: anonymous)

If somebody want to use an expressions there is extract. It would anyway already a complex expression. What I would expect is somthing like:

[?1]

13:26 Reply: Multi-connection access to database on USB drive (artifact: 1d6752587a user: slavin)

The way Flash actually works is very slow. It takes a long time – many milliseconds – to write to one part of storage, and a SQLite update requires writing to many parts of storage. As a consequence the drive accepts many 'write' commands, responds to each one instantly as 'I have completed this', then does the writing in its own time, in whatever order it finds convenient ("out-of-order writing").

If a Flash drive waited for each write command to be complete before it responded 'that write is complete', you would find the drive unusablly slow. The fact that it takes a long time for writes to be actually complete is the reason why you have to tell the OS "eject drive" and then wait for a while before it tells you it's safe to eject the drive. The OS is waiting for the drive to tell it that all writes are really complete.

There is no way SQLite can be ACID under conditions like this, since the storage system is not only lying to it about which writes are complete, but doesn't even do the writes in the right order.

You may think you've found a pattern of writing, and some SQLite settings, which result in uncorrupted databases, but you've actually just found something that works under one scenario. Use different hardware. Or have another program writing at the same time (even to a different drive). Or have the caches start full instead of empty, or have the caches start empty instead of full. Use a faster or slower Flash drive. Do updates faster or slower. Then pull the Flash drive while it's being written to. Any of these can result in a corrupt database.

Sorry, but consumer-grade Flash drives, hardware failure (e.g. pulling the drive before the OS says its ready) and ACID don't mix. Pick any two.

13:23 Reply: below function will free the heap memory for running application or not (artifact: 830946a98e user: gunter_hick)
Use the MEMSYS5 allocator as described on the linked page to limit SQLite memory use to a predefined maximum.
13:20 Reply: Docs: julianday() returns a real number, not a string (artifact: a67535fdad user: hanche)

perfect is the enemy of the good, Voltaire

… who was quoting an old Italian proverb, Le meglio è l'inimico del bene, in which it is the better that is the enemy of the good.

I like the Shakespeare version, though:

Were it not sinful then, striving to mend,
To mar the subject that before was well?

(Source: Wikipedia.)

Sorry for the off-off-topic tangent. To get back to the merely off-topic tangent, every floating point number, being a dyadic rational, has a finite decimal expansion. I would not dream of suggesting one should print that, but in a “better” world, one could print enough digits so that reading it back would yield the exact same number. I believe Common Lisp implementations strive to achieve this goal, possible at a loss of performance. There is a large literature on the efficient and exact printing of floating point numbers, apparently a tricky subject. Conversely, can one assume that reading the decimal representation of a number would result in the exact float that is closest to the given number, subject to appropriate rounding behaviour? I suspect not.

Everything in the previous paragraph is indeed the enemy of the current perfectly(!) good state of affairs, and striving to mend it seems sinful indeed.

11:19 Reply: below function will free the heap memory for running application or not (artifact: 067d0500f0 user: V16be1p11)

Can you elaborate some more. Because I am not understanding what you have replied.

11:06 Reply: Regarding initialization of sqlite_pcache_method2 (artifact: 1a4ab5d10b user: gunter_hick)
SQLite already has a page cache. But you may build your own.

See https://sqlite.org/c3ref/pcache_methods2.html

You need to initialize all members of the structure with the addresses of your routines that provide the required services. Then call sqlite3_config as stated on the linked page.

Configuring SQLite to use MEMSYS5 is probably a very much simpler way to limit the size of the page cache.
11:00 Reply: below function will free the heap memory for running application or not (artifact: e266aa65a2 user: gunter_hick)
Maybe. The effect will most likely be to evict currently unused pages from the page cache, which will make SQLite have to read them again.

Perhaps you should consider using the MEMSYS5 zero-malloc memory allocator (which is designed for use in embedded systems) to limit SQLite to a defined maximum. See https://sqlite.org/malloc.html
09:35 Edit reply: Docs: julianday() returns a real number, not a string (artifact: b9f1b38ddd user: stephancb)

Investigating the "conversion error" reveals:

The precision of IEEE745 double precision (used for SQLite REALs in most/all? implementations) when representing a fractional Julian day corresponds to a bit less than 0.1 milliseconds for contemporary times (it is higher for times way back in the past and slowly detoriating towards the future). The julianday(...) functions accepts date-time strings having submillisecond precision, but seems to ignore the submillisecond part:

sqlite> SELECT julianday('2022-01-27 07:34:42.1239')-julianday('2022-01-27 07:34:42.123');
0.0
sqlite> SELECT julianday('2022-01-27 07:34:42.124')-julianday('2022-01-27 07:34:42.123');
1.16415321826935e-08

even though the double precision would be (just) enough for also taking into account the digit representing hundred microseconds. One could argue that the documention cites %f fractional seconds: SS.SSS, i.e. no precision below millisecond.

The strftime(...) also accepts date-time strings with submilliseconds and returns them faithfully (it just needs to repeat the input):

sqlite> SELECT strftime('2022-01-27 07:34:42.1239');
2022-01-27 07:34:42.1239

Just an observation, I'm not suggesting to change anything (perfect is the enemy of the good, Voltaire)

09:23 Edit reply: Docs: julianday() returns a real number, not a string (artifact: 50e2b4d142 user: hanche)

I don’t see it in the current online docs

It’s right here.

I see only the modifier ’unixepoch’ there, not the function.

I guess the changes to the source have not made it to the online docs yet, or did my browser cache an ancient version?

09:21 Reply: Docs: julianday() returns a real number, not a string (artifact: 565ecc4639 user: hanche)

I don’t see it in the current online docs

It’s right here.

I see only the modifier ’unixepoch’ there, not the function.

09:15 Post: Regarding initialization of sqlite_pcache_method2 (artifact: a71b0cf2b9 user: Vijay_123)

Hi All,

Can any one let me know how to initialize the structure sqlite_pcache_method2?

And if I want to initialize sqlite3 for page cache mechanism how to do that? What and all parameters i should initialize?

Thanks in advance...

08:51 Edit reply: Docs: julianday() returns a real number, not a string (artifact: e4fbd181f6 user: wyoung)

I don’t see it in the current online docs

It’s right here.

must be a recent addition.

It appears to go back about two months.

However, there’s an older facility going by that name in SQLite going back to at least 2003.

08:50 Reply: Docs: julianday() returns a real number, not a string (artifact: 03dba7c847 user: wyoung)

I don’t see it in the current online docs

It’s right here.

must be a recent addition.

It appears to go back about two months.

However, there’s an older facility going by that name in SQLite going back to at least 2003.

08:43 Reply: Docs: julianday() returns a real number, not a string (artifact: 9c833e4a7e user: stephancb)

Investigating the "conversion error" reveals:

The precision of IEEE745 double precision (used for SQLite REALs in most/all? implementations) when representing a fractional Julian day corresponds to a bit less than 0.1 milliseconds for contemporary times (it is higher for times way back in the past and slowly detoriating towards the future). The julianday(...) functions accepts date-time strings having submillisecond precision, but seems to ignore the submillisecond part:

sqlite> SELECT julianday('2022-01-27 07:34:42.1239')-julianday('2022-01-27 07:34:42.123');
0.0
sqlite> SELECT julianday('2022-01-27 07:34:42.124')-julianday('2022-01-27 07:34:42.123');
1.16415321826935e-08

even though the double precision would be (just) enough for also taking into account the digit representing hundred microseconds. One could argue that the documention cites %f fractional seconds: SS.SSS, i.e. no precision below millisecond.

The strftime(...) also accepts date-time strings with submilliseconds and returns them faithfullY (it just needs to repeat the input):

sqlite> SELECT strftime('2022-01-27 07:34:42.1239');
2022-01-27 07:34:42.1239

Just an observation, I'm not suggesting to change anything (perfection is the enemy of the good, Voltaire)

08:42 Reply: Multi-connection access to database on USB drive (artifact: adfd4264a5 user: hanche)

Worse, I have seen reports that cheap USB drives lie; You send the command to flush data to disk, and it reports back that it has done so, before actually doing it. That way lies data corruption and mayhem if the computer crashes or power goes out at the wrong moment. (Admittedly, this information is old and possibly outdated.)

I suppose solid state drives are more reliable in that respect, since there is no reason to reorder writes on them. But I wouldn’t know for sure.

08:33 Edit reply: Docs: julianday() returns a real number, not a string (artifact: 8e23fbd124 user: hanche)

That’s good. And thanks for the bonus link! It’s been too long since I read any of Aesop’s fables.

PS. Glad to see the unixepoch function too. I don’t see it in the current online docs, so it (or its mention in the documentation) must be a recent addition.

08:27 Reply: Docs: julianday() returns a real number, not a string (artifact: d17f2b6b99 user: hanche)

That’s good. And thanks for the bonus link! It’s been too long since I read any of Aesop’s fables.

08:09 Reply: Multi-connection access to database on USB drive (artifact: 9e95582214 user: deonb)

Windows and macOS.

These are just consumer-grade magnetic USB drives formatted with either ExFat, HFS+ or NTFS.

07:42 Post: below function will free the heap memory for running application or not (artifact: ba8bab8bd7 user: V16be1p11)

I have concern regarding free the heap memory while lots of records we get through the query.

my code is look like below when sqlite query i use

if(app::dbmanager::SqliteManager::GetDBInstance()->openDatabase() == SUCCESS)
{

	sql<<"select intTicketTypeID,substr(strTicketNo,7),dteTicketDateTime,TR.intFromStationID,TR.intToStationID" \
			<<",(select strStageCode  from TBLROUTESTATIONMASTER RSM where  RSM.intRouteID = TR.intRouteID and RSM.intStationID = TR.intFromStationID AND RSM.bActive=1 AND RSM.bDeleted=0 )  as FromStaionStageCode"\
			<<",(select strStageCode  from TBLROUTESTATIONMASTER RSM where  RSM.intRouteID = TR.intRouteID and RSM.intStationID = TR.intToStationID   AND RSM.bActive=1 AND RSM.bDeleted=0 )  as ToStaionStageCode"\
			<<",sum(intFullTickets) as AdultQty "\
			<<",sum(intHalfTickets)  as HalfQty "\
			<<",sum(intTotalTicketAmount/100)  as TotalTicketAmount "\
			<<" from tblTicketTransactions TR "\
			<<" where strWayBillNo ='"<<strWaybillNo<<"' "\
			<<" AND BSUCCESSTXNFORCARD = 0 "\
			<<" AND intTripID='"<<strInspectionReportTripID<<"' "\
			<<" group by strTicketNo "\
			<<" order by dteTicketDateTime asc;";

	strquery =  sql.str();
	DBGF_TRACE("\n InspectionReportStep::execute(): strquery = %s\n",strquery);
	result = sqlite3_exec(app::dbmanager::SqliteManager::GetDBInstance()->handle, strquery.c_str(), &callBackTicketDataList, 0, &errMsg);
	DBGF_TRACE("AFCS:InspectionReportStep::execute()::result=%d",result);
	if(result == SQLITE_OK)
	{
		DBGF_TRACE("AFCS:InspectionReportStep::execute()::intTotalTicketCount=%d",intTotalTicketCount);
		if(intTotalTicketCount>0)
		{
			app::dbmanager::SqliteManager::GetDBInstance()->closeDatabase();

			setDataContainerValues();

			//return boomer::transaction::trOk;
		}
		else
		{
			app::dbmanager::SqliteManager::GetDBInstance()->closeDatabase();
			//boomer::ui::vc::AlertVC::displayError("ERROR","Ticket Data Not found.");
			//return boomer::transaction::trCancel;
		}
	}
	else
	{
		app::dbmanager::SqliteManager::GetDBInstance()->closeDatabase();
		boomer::ui::vc::AlertVC::displayError("ERROR",sqlite3_errmsg(app::dbmanager::SqliteManager::GetDBInstance()->handle));
		//Insert Error Log
		app::Header::AFCSErrorLogs afcsErrorLogs(pTransactionData);
		afcsErrorLogs.InsertErrorlog("","",0,sqlite3_errmsg(app::dbmanager::SqliteManager::GetDBInstance()->handle),"InspectionReportStep::execute()");
		return boomer::transaction::trCancel;
	}
}

In my application i am using below function to free the heap memory.

will below function will free the heap memory ?


sqlite3_int64 intBeforeReleaseInBG = sqlite3_memory_used(); printf("intBeforeReleaseInBG = %lldn", intBeforeReleaseInBG);

int intReleaseMemory = sqlite3_release_memory(10000);
printf("intReleaseMemory = %dn", intReleaseMemory);

sqlite3_int64 intAfterReleaseInBG = sqlite3_memory_used();

//sqlite3_db_release_memory(StructSql);

sqlite3_int64 intSoftHeap = sqlite3_soft_heap_limit64(250000); printf("intSoftHeap = %lldn", intSoftHeap); printf("intAfterReleaseInBG = %lldn", intAfterReleaseInBG);


07:32 Reply: Sqlite error 7 handling when delete the record (artifact: 30bad029b0 user: V16be1p11)

ans 1 :how to know which sqlite release ? is there any function is there to identify it?

ans 2 & 3: my peer has installed this lib in verix evo/s os.

ans 4,5 and 6: you can see it from below code :

LogoutStep::DeleteRecordsB4ExtDaysFromTicketDB() { DBGF_TRACE("AFCS:LogoutStep::DeleteRecordsB4ExtDaysFromTicketDB()"); //Delete no of days(get from tblsetting) data from tblticketTransaction which are already uploaded to server std::string deleteRecordsB4ExtDaysQueryForTicketDB=""; int Result = 0;

//Reprint Transaction
if(Result == 0)
{
	deleteRecordsB4ExtDaysQueryForTicketDB = "DELETE FROM TBLREPRINTTICKETTRANSACTIONS WHERE DATE(DTEREPRINTTICKETDATETIME) <='"+strDateBeforeExtensionDays+"' and BUPLOADED = 1 and BMOVED=1 and STRREPRINTTICKETNO NOT IN ( " \
			" SELECT distinct TRT.STRREPRINTTICKETNO " \
			" from TBLREPRINTTICKETTRANSACTIONS  TRT " \
			" INNER JOIN  tblTicketTransactions TR " \
			" ON TRT.STRTICKETNO = TR.STRTICKETNO " \
			" where " \
			" TR.strWayBillNo ='" + strWaybillNo + "');";
	Result += DeleteUploadedRecordsBeforeExtensionDaysForTicketDB(deleteRecordsB4ExtDaysQueryForTicketDB,_DeletetblReprintTransationDB2);
	DBGF_TRACE("AFCS:LogoutStep::DeleteRecordsB4ExtDaysFromTicketDB()::Result of DT11=%d",Result);
}
//Transactions
if(Result == 0)
{
	deleteRecordsB4ExtDaysQueryForTicketDB = "DELETE FROM tblTicketTransactions WHERE DATE(dteTicketDateTime)<='"+strDateBeforeExtensionDays+"' and bUploaded = 1 and bMoved=1 and strWayBillNo!='"+strWaybillNo+"';";
	Result += DeleteUploadedRecordsBeforeExtensionDaysForTicketDB(deleteRecordsB4ExtDaysQueryForTicketDB,_DeletetblTicketTransactionsDB2);
	DBGF_TRACE("AFCS:LogoutStep::DeleteRecordsB4ExtDaysFromTicketDB()::Result of DT1=%d",Result);
}
//Trips
if(Result == 0)
{
	deleteRecordsB4ExtDaysQueryForTicketDB = "DELETE FROM tblTripInfo WHERE DATE(dteTripStartTime) <='"+strDateBeforeExtensionDays+"' and bUploaded = 1 and bUploadedTripEnd = 1 and BMOVEDTRIPSTART=1 and BMOVEDTRIPEND=1 and strWayBillNo!='"+strWaybillNo+"';";
	Result += DeleteUploadedRecordsBeforeExtensionDaysForTicketDB(deleteRecordsB4ExtDaysQueryForTicketDB,_DeletetblTripInfoDB2);
	DBGF_TRACE("AFCS:LogoutStep::DeleteRecordsB4ExtDaysFromTicketDB()::Result of DT2=%d",Result);
}
//Device Login History
if(Result == 0)
{
	deleteRecordsB4ExtDaysQueryForTicketDB = "DELETE FROM tblDeviceLoginHistory WHERE DATE(dteLoginDate) <='"+strDateBeforeExtensionDays+"' and bUploaded = 1 and bUploadedLogout=1 and bLoginMoved=1 and bLogoutMoved=1;";
	Result += DeleteUploadedRecordsBeforeExtensionDaysForTicketDB(deleteRecordsB4ExtDaysQueryForTicketDB,_DeletetblDeviceLoginHistoryDB2);
	DBGF_TRACE("AFCS:LogoutStep::DeleteRecordsB4ExtDaysFromTicketDB()::Result of DT3=%d",Result);
}
//Device Login History Delete Records :-buploadedLogout=0 and Logout Date is Null or NA and intDeviceLoginId is Not Max Id
if(Result == 0)
{
	deleteRecordsB4ExtDaysQueryForTicketDB = "DELETE FROM tblDeviceLoginHistory WHERE DATE(dteLoginDate) <='"+strDateBeforeExtensionDays+"' and bUploaded = 1 and bUploadedLogout=0 and bLoginMoved=1 and bLogoutMoved=0 and (dteLogoutDate IS  NULL OR dteLogoutDate == 'NA' OR dteLogoutDate == '') and INTDEVICELOGINID!=(select max(INTDEVICELOGINID) from TBLDEVICELOGINHISTORY order by 1 desc);";
	Result += DeleteUploadedRecordsBeforeExtensionDaysForTicketDB(deleteRecordsB4ExtDaysQueryForTicketDB,_DeletetblDeviceLoginHistoryEmptyLogoutDB2);
	DBGF_TRACE("AFCS:LogoutStep::DeleteRecordsB4ExtDaysFromTicketDB()::Result of DT4=%d",Result);
}
//Error Logs
if(Result == 0)
{
	deleteRecordsB4ExtDaysQueryForTicketDB = "DELETE FROM tblErrorLogs WHERE DATE(dteErrorDateTime) <='"+strDateBeforeExtensionDays+"' and bUploaded = 1 and strWayBillNo!='"+strWaybillNo+"' and intErrorLogID!=(select MAX(intErrorLogID) from tblErrorLogs where intTicketTypeID=57 ) ;";
	Result += DeleteUploadedRecordsBeforeExtensionDaysForTicketDB(deleteRecordsB4ExtDaysQueryForTicketDB,_DeletetblErrorLogsDB2);
	DBGF_TRACE("AFCS:LogoutStep::DeleteRecordsB4ExtDaysFromTicketDB()::Result of DT5=%d",Result);
}
if(Result == 0)
{
	deleteRecordsB4ExtDaysQueryForTicketDB = "DELETE FROM TBLINSPECTEDTICKETDETAIL WHERE DATE(DTEINSPECTEDTICKETDATETIME) <='"+strDateBeforeExtensionDays+"' and BUPLOADED = 1 and BMOVED = 1 and STRWAYBILLNO !='"+strWaybillNo+"' LIMIT 500;";
	Result += DeleteUploadedRecordsBeforeExtensionDaysForTicketDB(deleteRecordsB4ExtDaysQueryForTicketDB,_DeleteInspectedTicketTransactionsDB2);
	DBGF_TRACE("AFCS:LogoutStep::DeleteRecordsB4ExtDaysFromTicketDB()::Result of DT12=%d",Result);
}

//Mobile Ticket & Pass
if(Result == 0)
{
	deleteRecordsB4ExtDaysQueryForTicketDB = "DELETE FROM TBLMOBILETRANSACTIONS WHERE DATE(DTEUSEDON) <='"+strDateBeforeExtensionDays+"' and BUPLOADED = 1 and BMOVED=1 and STRWAYBILLNO!='"+ strWaybillNo +"';";
	Result += DeleteUploadedRecordsBeforeExtensionDaysForTicketDB(deleteRecordsB4ExtDaysQueryForTicketDB,_DeleteTBLMOBILETRANSACTIONSDB2);
	DBGF_TRACE("AFCS:LogoutStep::DeleteRecordsB4ExtDaysFromTicketDB()::Result of DT7=%d",Result);
}
//PreFareMatrix
if(Result == 0)
{
	deleteRecordsB4ExtDaysQueryForTicketDB = "DELETE FROM TBLPREFAREMATRIXRANGEWISE WHERE DATE(DTEEFFECTIVEON) <='"+strDateBeforeExtensionDays+"' AND BAPPLIED = 0;";
	Result += DeleteUploadedRecordsBeforeExtensionDaysForTicketDB(deleteRecordsB4ExtDaysQueryForTicketDB,_DeleteTBLPREFAREMATRIXRANGEWISEDB2);
	DBGF_TRACE("AFCS:LogoutStep::DeleteRecordsB4ExtDaysFromTicketDB()::Result of DT8=%d",Result);
}
//Event Logs with Ticket No E1
if(Result == 0)
{
	deleteRecordsB4ExtDaysQueryForTicketDB = "DELETE FROM tblEventsLogging WHERE DATE(dteModifiedOn) <='"+strDateBeforeExtensionDays+"' and BUPLOADED = 1 and BMOVED=1 and substr(strTicketNo,11,2) = 'E1' and STRWAYBILLNO!='"+ strWaybillNo +"';";
	Result += DeleteUploadedRecordsBeforeExtensionDaysForTicketDB(deleteRecordsB4ExtDaysQueryForTicketDB,_DeletetblEventsLoggingE1DB2);
	DBGF_TRACE("AFCS:LogoutStep::DeleteRecordsB4ExtDaysFromTicketDB()::Result of DT9=%d",Result);
}
//Event Logs with Ticket No E2
if(Result == 0)
{
	deleteRecordsB4ExtDaysQueryForTicketDB = "DELETE FROM tblEventsLogging WHERE DATE(dteModifiedOn) <='"+strDateBeforeExtensionDays+"' and BUPLOADED = 1 and substr(strTicketNo,11,2) = 'E2' and STRWAYBILLNO!='"+ strWaybillNo +"';";
	Result += DeleteUploadedRecordsBeforeExtensionDaysForTicketDB(deleteRecordsB4ExtDaysQueryForTicketDB,_DeletetblEventsLoggingE2DB2);
	DBGF_TRACE("AFCS:LogoutStep::DeleteRecordsB4ExtDaysFromTicketDB()::Result of DT10=%d",Result);
}
//PENALTYINSPECTIONDETAILS Transaction
if(Result == 0)
{
	deleteRecordsB4ExtDaysQueryForTicketDB = "DELETE FROM TBLPENALTYINSPECTIONDETAILS WHERE DATE(DTEPENALTYINSPECTIONDATE) <='"+strDateBeforeExtensionDays+"' and BUPLOADED = 1 and BMOVED = 1 and STRINSPECTORWAYBILLNO !='"+strWaybillNo+"';";
	Result += DeleteUploadedRecordsBeforeExtensionDaysForTicketDB(deleteRecordsB4ExtDaysQueryForTicketDB,_DeletePenaltyInspectionDetailsTransactionsDB2);
	DBGF_TRACE("AFCS:LogoutStep::DeleteRecordsB4ExtDaysFromTicketDB()::Result of DT11=%d",Result);
}

//HH Problems
if(Result == 0)
{
	deleteRecordsB4ExtDaysQueryForTicketDB = "DELETE FROM TBLHHPROBLEM WHERE DATE(DTEREPORTDATE) <='"+strDateBeforeExtensionDays+"' and BUPLOADED = 1 and bMoved=1 and STRWAYBILLNO!='"+strWaybillNo+"';";
	Result += DeleteUploadedRecordsBeforeExtensionDaysForTicketDB(deleteRecordsB4ExtDaysQueryForTicketDB,_DeleteTBLHHPROBLEMDB2);
	DBGF_TRACE("AFCS:LogoutStep::DeleteRecordsB4ExtDaysFromTicketDB()::Result of DT6=%d",Result);
}

if(Result == 0)
{
	deleteRecordsB4ExtDaysQueryForTicketDB = "DELETE FROM TBLINSPECTORACTIVITYTRANSACTION WHERE DATE(DTEACTIVITYSTARTTIME) <='"+strDateBeforeExtensionDays+"' and BUPLOADED = 1 and BUPLOADEDEND = 1 and BMOVEDTOBACKSYNCDB = 1 and BMOVEDTOBACKSYNCDBEND = 1 and STRWAYBILLNO !='"+strWaybillNo+"';";
	Result += DeleteUploadedRecordsBeforeExtensionDaysForTicketDB(deleteRecordsB4ExtDaysQueryForTicketDB,_DeleteInspectorActivityTransactionsDB2);
	DBGF_TRACE("AFCS:LogoutStep::DeleteRecordsB4ExtDaysFromTicketDB()::Result of DT13=%d",Result);
}
DBGF_TRACE("AFCS:LogoutStep::DeleteRecordsB4ExtDaysFromTicketDB()::Final Return Result=%d",Result);
return Result;

}

04:12 Reply: Multi-connection access to database on USB drive (artifact: b3552111d7 user: kmedcalf)

What Operating System and drive settings?

01:14 Post: Multi-connection access to database on USB drive (artifact: e6abcca34d user: deonb)

Currently we support storing our database files on USB drives, but over the years we've learned that in order not to corrupt it if the user surprise-removes the drive, we have to do:

PRAGMA MMAP_SIZE=0 PRAGMA locking_mode=EXCLUSIVE PRAGMA JOURNAL_MODE = WAL PRAGMA synchronous = NORMAL

This gives us a relatively reliable database, even if the user unplugs the USB drive while it's busy writing to SQLITE. It sometimes still corrupts on surprise-remove but orders of magnitude less.

However, I would really love to be able to have a second thread read from that USB drive (ACID semantics, not just mutexes). We generally would do this on a non-USB drive by opening a second connection, but the locking_mode EXCLUSIVE prevents that on USB.

I don't quite understand what the combination of EXCLUSIVE + WAL does and what causes it to be able to survive a surprise-removal. Is part of that mechanism maybe available outside of exclusive?

00:35 Reply: Docs: julianday() returns a real number, not a string (artifact: 2015d00b71 user: larrybr)

I was mindful of a certain Aesop's fable ("The Man, The Boy, and The Donkey") while making this last1 change. However, in light of your comments, Keith's comments, and what I understand to be an accurate disclosure policy for API behavior, I decided that the additional changes about return type and its effects is worth the extra verbiage2. Please let me know if this falls short of your expectations for what the docs should accomplish. (This invitation goes to Keith also.) Please note that when converted to HTML, this renders prettily, so critiques should be about content and clarity rather than aesthetics.


  1. At least, I hope it's the last for the present issue.

  2. It can be a narrow path between glossing over occasionally relevant detail and verbosity that hinders comprehension of more commonly important points. The fact that the added verbiage was already in a footnote-like paragraph made this change easier to justify.

2022-01-26
23:19 Edit reply: Docs: julianday() returns a real number, not a string (artifact: be59bc0b83 user: kmedcalf)

I can see your point. While it is indeed true that CURRENT_TIME, CURRENT_DATE, CURRENT_TIMESTAMP, date(...), time(...) and datetime(...) are merely aliases for the strftime function specifying a pre-bound "format string" and returning text, the julianday(...) function is not an overload of strftime(...) -- although there is a format code that can be used in a strftime format string to return a textual representation of the julian epoch day offset.

The julianday(...) function takes the same parameters (without a format string) as the strftime function -- however it returns a dirrectly calculated floating point value.

Conceptually, the '%J' format string to strftime returns the same number, just in ASCII TEXT (since that is what strftime does). The difference is, of course, that conversion between base-10 ASCII representation and base-2 binary floating point representation introduces "conversion error" and should be expected.

In other words, the Julian Epoch Offset is retrieved by the julianday(...) function. This same value can be "converted to text" by formatting the floating point result using whatever method the heart desires, including providing the appropriate formating codes to the strftime function, however, as everyone should be aware, conversion to and from ASCII base-10 print format and double precision base-2 floating point are not exact.

It would be a rational expectation that each conversion would introduce additional non-stochastic error.

It other words, I agree that the first paragraph in the documentation is misstated.

julianday(...) does not return the same value as strftime('%J', ...); nor is juliandate an overload of strftime (as are the other functions). It instead returns the accurately computed (within the bounds of hardware capability) Julian Epoch offset.

The same caveats apply to the unixepoch(...) function, which also returns a directly computed integer value and is not an overload of strftime -- although there is a format code for strftime that will produce similar text output.

23:13 Reply: Docs: julianday() returns a real number, not a string (artifact: a2f3e0b139 user: kmedcalf)

I can see your point. While it is indeed true that CURRENT_TIME, CURRENT_DATE, CURRENT_TIMESTAMP, date(...), time(...) and datetime(...) are merely aliases for the strftime function specifying a pre-bound "format string" and returning text, the julianday(...) function is not an overload of strftime(...) -- although there is a format code that can be used in a strftime format string to return a textual representation of the julian epoch day offset.

The julianday(...) function takes the same parameters (without a format string) as the strftime function -- however it returns a dirrectly calculated floating point value.

Conceptually, the '%J' format string to strftime returns the same number, just in ASCII TEXT (since that is what strftime does). The difference is, of course, that conversion between base-10 ASCII representation and base-2 binary floating point representation introduces "conversion error" and should be expected.

In other words, the Julian Epoch Offset is retrieved by the julianday(...) function. This same value can be "converted to text" by formatting the floating point result using whatever method the heart desires, including providing the appropriate formating codes to the strftime function, however, as everyone should be aware, conversion to and from ASCII base-10 print format and double precision base-2 floating point are not exact.

It would be a rational expectation that each conversion would introduce additional non-stochastic error.

It other words, I agree that the first paragraph in the documentation is misstated.

julianday(...) does not return the same value as strftime('%J', ...); nor is juliandate an overload of strftime (as are the other functions). It instead returns the accurately computed (within the bounds of hardware capability) Julian Epoch offset.

The same caveats apply to the unixepoch(...) function, which also returns a directly computed floating point value and is not an overload of strftime.

22:36 Reply: Docs: julianday() returns a real number, not a string (artifact: 5b21c1f8f8 user: hanche)

I apologize for the final paragraph of my initial post. It has led the discussion astray, or at least not where I wanted it to go. To me, the important point is the type of the result, not whether or not it is some microseconds off.

21:52 Edit reply: Docs: julianday() returns a real number, not a string (artifact: 49c8da2be8 user: kmedcalf)

Those three results represent -1, 0 and +1 ULP of precision and represents the conversion discrepancy between the actual value (as returned by the julianday function as a double precision floating point) and the ASCII representation of the same value after conversion to and fro double precision floating point.

The current epsilon of juliandate('now') expressed as a double precision floating point number is 4.65661287307739e-10 days (0.0402331352233887 milliseconds).

The maximum difference is therefore about 40 microseconds and is really due to the conversion to and from ASCII TEXT base 10 representation.

It should be pointed out that iJD (the internal timestamp used by SQLite3) is only precise to the millisecond, the difference in value is entirely an aftifact of conversion and does not represent any actual difference in the source value.

21:50 Edit reply: Docs: julianday() returns a real number, not a string (artifact: 8d8ad3c805 user: kmedcalf)

Those three results represent -1, 0 and +1 ULP of precision and represents the conversion discrepancy beteen the actual value (as returned by the julianday function as a double precision floating point) and the ASCII representation of the same value after conversion to and fro double precision floating point.

The current epsilon of juliandate('now') expressed as a double precision floating point number is 4.65661287307739e-10 days (0.0402331352233887 milliseconds).

The maximum difference is therefore about 40 microseconds and is really due to the conversion to and from ASCII TEXT base 10 representation.

It should be pointed out that iJD (the internal timestamp used by SQLite3) is only precise to the millisecond, the difference in value is entirely an aftifact of conversion and does not represent any actual difference in the source value.

21:47 Edit reply: Docs: julianday() returns a real number, not a string (artifact: 2f31fee9f6 user: kmedcalf)

Those three results represent -1, 0 and +1 ULP of precision and represents the conversion discrepancy beteen the actual value (as returned by the julianday function as a double precision floating point) and the ASCII representation of the same value after conversion to and fro double precision floating point.

The current epsion of juliandate('now') expressed as a double precision floating point number is 4.65661287307739e-10 days (0.0402331352233887 milliseconds).

THe maximum difference is therefore about 40 microseconds and is really due to the conversion to and from ASCII TEXT base 10 representation.

It should be pointed out that iJD (the internal timebase) is only precise to the millisecond, the difference in value is entirely an aftifact of conversion and does not represent any actual difference in the source value.

21:43 Reply: Docs: julianday() returns a real number, not a string (artifact: 2f07d4ae35 user: kmedcalf)

Those three results represent -1, 0 and +1 ULP of precision and represents the conversion discrepancy beteen the actual value (as returned by the julianday function as a double precision floating point) and the ASCII representation of the same value after conversion to and fro double precision floating point.

The current epsion of juliandate('now') expressed as a double precision floating point number is 4.65661287307739e-10 days (0.0402331352233887 milliseconds).

THe maximum difference is therefore about 40 microseconds and is really due to the conversion to and from ASCII TEXT base 10 representation.

21:41 Reply: Docs: julianday() returns a real number, not a string (artifact: 0c674f2417 user: hanche)

but I read too fast when busy sometimes

Me too, all the time.

I don't think the tiny difference in values is important enough to mention. But a user with an application processing loads of daytime values might want to avoid the conversion from real to string and back for performance reasons.

And here is one example where the differnt results could be important, if the result of the query is exported:

▶▶▶ select json_array(julianday('now'),strftime('%J','now')) daytimes;
daytimes
-------------------------------------
[2459606.4024086,"2459606.402408599"]

(Note the quotes around the second array element.)

21:14 Reply: Docs: julianday() returns a real number, not a string (artifact: 58e6cec859 user: larrybr)

Ok, I see your point now, although I could not easily grasp that you might expect anything but text from something named "str*". (I know you said it, but I read too fast when busy sometimes.)

I don't agree that the type of the return breaks the claimed equivalency. And I am not convinced that such difference as there is in return type is worth the doc dilution effect of explaining it, in part because words like 'number' and names like 'str%' nearly shout that they are not precisely equivalent.

If the results were in fact equivalent when converted to REAL or TEXT (one way or the other), I would agree that the difference probably does not matter.

However, I ran a little experiment, running the following repeatedly:

 select x, y, x-y from (select julianday('now') as x, strftime('%J', 'now') as y);

 select x, y, x-y from (select strftime('%J', 'now') as x, julianday('now') as y);

This yields a result that makes me think some words on the difference could matter, to those naive enough to be comparing floating point numbers. The difference term always has one of 3 tiny values, set of which is centered on the origin. To get it to be 0.0 consistently, I have to say:

select x, y, x-y from (select strftime('%J', 'now') as x, printf('%17.9f',julianday('now')) as y);

I'm still mulling over whether mentioning these minutia is a net benefit. While doing so might be more technically correct, they will likely be a distraction for the vast majority of users who either just want to know what to use to get what they want and care little about numerical accuracy limitations of binary floating point values rendered into decimal form.

16:38 Reply: empty DB created if DB file missing - is this inherent to SQLite (artifact: 77d67cc07a user: larrybr)

For the System.Data.SQLite wrapper, this forum is the right place to come for help. There is no "support" in the sense of anybody obliged to try to get users through their difficulties. That is why you are encouraged to read the documentation for System.Data.SQLite as your primary source of help.

Last time I looked, that documentation was in the form of a .htm file (which is some sort of Microsoft-devised HTML archive), and is impossible to link from here. You should have gotten that archive along with the library. Somewhere in the connection constructor or methods doc, your options with respect to auto-creation of the DB file can be found.

16:36 Reply: empty DB created if DB file missing - is this inherent to SQLite (artifact: fd18c3296b user: cuz)

Ok, we are not saying you should sod off, just that frequency of helpers are probably higher on C# or whatever platform you are using.

Where are you directed for documentation on your wrapper? Have you found anything there on opening of SQlite DBs? Did it fail to answer the question?

If so, feel free to post the section from the documentation that is relevant and say how it is unclear or what remains to be answered, that way someone here can fill in the gaps or perhaps even non-wrapper users (like myself) could offer advice or figure out what would make it work for you - or you could just wait until a System.Data.SQLite user rolls by (in case RandomCoder's reply isn't already enough).

16:33 Reply: Docs: julianday() returns a real number, not a string (artifact: 306b982f97 user: hanche)

That is a good change, but it is somewhat orthogonal to my concern, which is that julianday(…) and strftime('%J',…) are not exactly equivalent, as they return different types:

▶▶▶ create table tbl(date, type generated as (typeof(date)));
▶▶▶ insert into tbl(date) values (julianday('now')), (strftime('%J','now'));
▶▶▶ select * from tbl;
date               type
-----------------  ----
2459606.18498622   real
2459606.184986215  text

The difference probably doesn't matter in the vast majority of uses, but it is a difference.

But I agree that the doc is indeed very clear, with this itty-bitty reservation.

16:29 Reply: empty DB created if DB file missing - is this inherent to SQLite (artifact: e8b239cfbf user: ahansen)

RandomCoder - Thank you very much! That's where I was stuck.

16:21 Reply: empty DB created if DB file missing - is this inherent to SQLite (artifact: ba914ede29 user: ahansen)

My "wrapper" is - as stated - System.Data.SQLite with the .Net Framework Stub, the official package provided by the SQLite Development Team and obtained via NuGet.

Per the System.Data.SQLite page:

"This page contains all supported downloadable packages (e.g. sources, binaries, etc) for the current release version of the official "System.Data.SQLite" project. This page and the associated NuGet packages are the only official distribution points for these downloadable packages."

System.Data.SQLite Support:

"SQLite has an active forum and support community and users of the System.Data.SQLite project available on this web site are encouraged to use these for support questions.

The SQLite Forum has been established to help support SQLite and System.Data.SQLite"

This forum is where users of System.Data.SQLite are directed.

16:03 Reply: empty DB created if DB file missing - is this inherent to SQLite (artifact: ac00d0c84a user: RandomCoder)

Since Open() wraps all calls to sqlite3_open, you need to pass the options to it to the SQLite object you create:

var csb = new SqliteConnectionStringBuilder
{
    DataSource = BURS_Connections.SQLiteDB(),
    Mode = SqliteOpenMode.ReadWrite
};
var sqlite = new SqliteConnection(csb.ConnectionString);
try
{
    sqlite.Open();
}
catch (SqliteException e)
{
    if (e.SqliteErrorCode == 14) // SQLITE_CANTOPEN
    {
        Console.WriteLine("Unable to open DB");
    }
}
16:00 Reply: Just some i386 build warnings on OpenBSD (artifact: 2bd01cd8da user: ThanksRyan)

Hi,

Sorry, I should haven't mentioned these were generated when building SQLite from fossil trunk, commit a8db69411b.

Thanks!

More ↓