SQLite Forum


8 forum posts by user jchd18

12:56 Reply: Proposed slightly-incompatible change to date-time functions (artifact: 2e71dd548b user: jchd18)

As others have said, many applications store JDNs (Julian Day Numbers) as INT.

In many use cases dates are best expressed in whole days, e.g. accounting. For instance I've almost never seen an invoice with h:m:s timestamps. In history only whole days are significant and you expect sign of friendship on your "integral" birthday no matter the clock hour.

While I fully understand the reason for the proposed change, I suspect this would break a significant number of real-world applications.

An SQLite JDN is currently Julian date compatible but would no more yield a valid result if the change was silently applied. By "silently" I mean that its highly probable that latest SQLite releases are often used 'as is' (as amalgamation or directly as a downloaded .DLL) without reading the release notes in full. This thanks to the high confidence people have built wrt SQLite code. People know by experience that subsequent SQLite releases are never worse than previous, fixing obscure bugs, improving speed or adding new features.

Application code (whatever language they are built with) or libraries often offer routines to deal with JDNs or JDN differences.

The perspective of a number of sqlite3.dll hanging around on the average Windows storage for instance, some speaking JDN, some speaking epoch, is quite embarrasing. From my point of view, this would be a breach of contract.

00:42 Post: Minor typo in doc chapter title (artifact: 1b348bee06 user: jchd18)

https://www.sqlite.org/matrix/lang_expr.html SQL Langauge Expressions ^^

09:27 Reply: HEX - What is the reverse function? (artifact: 3edd1cf4f7 user: jchd18)

Yeah, as usual: know your data!

Can as well be done with extension eval(), but application code is another way.

08:28 Reply: HEX - What is the reverse function? (artifact: 79dc039e21 user: jchd18)

select hex('Price is 3€'), cast(x'50726963652069732033E282AC' as text)

19:50 Reply: SQLITE_HAS_CODE is gone? (artifact: bdf1defeb8 user: jchd18)

I don't have the resource to backup every bit of system/programs/dlls everytime. I focus backups on user data.

I need system.data.sqlite for X86. Even X64 would help getting out of the trap.

18:56 Reply: SQLITE_HAS_CODE is gone? (artifact: 9bcbf50147 user: jchd18)


While I agree with everybody here that you and your staff have made an amazing work for years, I also agree with affected people that this is a significant inconvenience for some users, but a disaster for some others.

I've blindly upgraded by overwriting the previous DLL (I'm running a Windows-only scripting language which can only use a DLL) and now the fact that the encryption support has disappeared and that older versions are no more downloadable, I'm left with an number of unreadable DBs which represent more than 15 years of continuous hard work.

I can understand that you prefer to sell SEE to corps for megabucks (which you deserve without question) but self-employed individuals like me are just plain ruined. I'd prefer having died from COVID now.

Removing the support without any prior nor post notice wasn't fair nor consistent with your praised profile, since you knew what kind of havoc would result and even if that was a world-known undocumented feature.

19:01 Reply: Removing not null constraint doesn't behave correctly (artifact: 941006e391 user: jchd18)

I don't refer to the thread per se.

My remark is simply pointing out that in the 12-step procedure proposed to make profond changes to a table X (beyond what alter table currently does), the step #3 is not sufficient in all cases and must also include triggers of other tables which issue SQL statement(s) against the changed table X.

For instance, in the "4. Some example triggers" paragraph, the first trigger updates table orders "after UPDATE OF address ON customers".

So if one only looks at what is in sqlite_master with name 'customers', the trigger update_customer_address won't be selected/examined/modified and might cause havoc after an alter table changing sqlite_master directly. This won't be noticed until the unchanged trigger is invoked, as AFAIK triggers are only parsed when launched.

13:54 Reply: Removing not null constraint doesn't behave correctly (artifact: 7c44f99222 user: jchd18)

I'm afraid the 12-step procedure isn't even enough.

Step#3: SELECT type, sql FROM sqlite_master WHERE tbl_name='X'

That doesn't include triggers associated with other tables, where SQL statements involving table X can be present. I have several such use cases and I bet I'm far from alone.