@lyse@lyse.isobeef.org AI result ahead, feel free to ignore.

I “asked” the AI at work the same question out of morbid curiousity. It “said” that SQLite converts that integer to floating point internally on overflows and then, when converting back, the x86 instruction cvttsd2si will turn it into 0x8000000000000000, even if the actual floating point value is outside of that range. So, yes, it allegedly actually saturates, as a side effect of the type conversion.

I couldn’t find anything about that automatic conversion in SQLite’s manual, yet, but an experiment looks like it might be true:

sqlite> select typeof(1 << 63);
╭─────────────────╮
│ typeof(1 << 63) │
╞═════════════════╡
│ integer         │
╰─────────────────╯

sqlite> select typeof((1 << 63) - 1);
╭──────────────────────╮
│ typeof((1 << 63) ... │
╞══════════════════════╡
│ real                 │
╰──────────────────────╯

As for cvttsd2si, this source confirms the handling of 0x8000000000000000 on range errors: https://www.felixcloutier.com/x86/cvttsd2si

The following C program also confirms it (run through gdb to see cvttsd2si in action):

<a href="https://we.loveprivacy.club/search?q=%23include">#include</a> <stdint.h>
<a href="https://we.loveprivacy.club/search?q=%23include">#include</a> <stdio.h>

int
main()
{
    int64_t i;
    double d;

    /* -3000 instead of -1, because `double` can’t represent a
     * difference of -1 at this scale. */
    d = -9223372036854775808.0 - 3000;

    i = d;
    printf("%lf, 0x%lx, %ld\n", d, i, i);

    return 0;
}

(Remark about AI usage: Fine, I got an answer and maybe it’s even correct. But doing this completely ruined it for me. It would have been much more satisfying to figure this out myself. I actually suspected some floating point stuff going on here, but instead of verifying this myself I reached for the unethical tool and denied myself a little bit of fun at the weekend. Won’t do that again.)

@lyse@lyse.isobeef.org

Disclaimer: Can’t guarantee that I’m fully awake and I’m being trained at work not to use my brain anymore, so maybe this is complete bullshit. 😪🧟‍♀️

It says here that SQLite uses signed integers:

https://sqlite.org/datatype3.html

In pure bits, 1 << 63 would be 0x8000000000000000, but as a signed value, it gets interpreted as -9223372036854775808. Subtracting 1 yields -9223372036854775809 – but that doesn’t fit in 64 bits anymore. It’s possible that SQLite doesn’t want to wrap around but instead saturates? Haven’t checked. 🤔

With 62 bits, there is enough room.

With 1 << 64, I have no idea how SQLite wants to handle this, because this should immediately trigger a warning, because it doesn’t fit right away. Maybe it gets truncated to 0?

sqlite> select printf('0x%x', 2 * (1 << 64));
╭──────────────────────╮
│ printf('0x%x', 2 ... │
╞══════════════════════╡
│ 0x0                  │
╰──────────────────────╯
sqlite> select printf('0x%x', 0 - 1);
╭──────────────────────╮
│ printf('0x%x', 0 ... │
╞══════════════════════╡
│ 0xffffffffffffffff   │
╰──────────────────────╯
sqlite> select printf('0x%x', 0 - 2);
╭──────────────────────╮
│ printf('0x%x', 0 ... │
╞══════════════════════╡
│ 0xfffffffffffffffe   │
╰──────────────────────╯

Eehhh, what the hell is going on here!?

SELECT
    printf("0x%x", (1 << 63) - 2),
    printf("0x%x", (1 << 63) - 1),
    printf("0x%x",  1 << 63     ),
    printf("0x%x", (1 << 63) + 1),
    printf("0x%x", (1 << 63) + 2)

SQLite yields:

0x8000000000000000 (instead of 0x7ffffffffffffffe)
0x8000000000000000 (instead of 0x7fffffffffffffff)
0x8000000000000000 (correct)
0x8000000000000001 (correct)
0x8000000000000002 (correct)

Huh!? O_o Am I stupid? What am I missing here? Or is this actually a bug? :-?

With 62 bits, everything is spot on:

0x3ffffffffffffffe
0x3fffffffffffffff
0x4000000000000000
0x4000000000000001
0x4000000000000002

And 64 bits rather unsurprisingly also yield:

0xfffffffffffffffe
0xffffffffffffffff
0x0
0x1
0x2

I’m supporting incremental SQLite schema changes to just upgrade from an older database version to whatever the current software version supports. In the past, I already noticed that this is quite expensive in unit tests when each test case runs through the entire schema patches and applies them one by one.

To speed up test execution I now decided that I finally go through the troubles of maintaining both a set of incremental patches and a full schema setup in one go. A unit test verifies that both ways end up with the same structure. This gives me a set of SQLs to check the structures:

SELECT type, name, tbl_name, sql
FROM sqlite_schema
ORDER BY type, name, tbl_name

Unfortunately, the resulting CREATE TABLE SQL queries are formatted differently, depending on whether the full schema was set up in one big step or the structure had been modified with ALTER TABLE. Mainly, added columns are not on their own lines but appended in one physical line. That’s why I wanted an SQL formatting tool. Since I didn’t find one that works decently, I’m now doing some simple string manipulation. Joining consecutive whitespace into a single space character, removing spaces before commas and closing parentheses and spaces after opening parentheses. This works surpringly good enough. Of course, if it fails, the “diff” is absolutely horrendous.

Now for the cool part, my test execution dropped from around 5:05 minutes to just 1:32 minutes! I call that a win.

I just stumbled across PRAGMA table_info('tablename') https://sqlite.org/pragma.html#pragma_table_info, PRAGMA foreign_key_list('tablename') and friends. I guess, I have to play with that, now. It’s probably much better to use than the SQL text approach.

@rdlmda@rdlmda.me I never saw the point of a registry to be honest, as it defeated the point of what I believed to be a truly decentralised non-social social ecosystem. What can and does work however is a search engine and crawler. I used to run one, but I took it down, mostly because it got expensive to operate, at least the implementation I built… Maybe one day i’ll try again with a SQLite backend.

I came across this on “Why Is SQLite Coded In C”, which I found interesting:

“There has lately been a lot of interest in “safe” programming languages like Rust or Go in which it is impossible, or is at least difficult, to make common programming errors like memory leaks or array overruns.”

If that’s true, then encountering those issues means the programmer is, simply, horrible?

Stoolap 0.2 Released For Modern Embedded SQL Database In Rust
Stooplap v0.2 released today as this SQLite alternative for providing embedded SQL database needs while written in the Rust programming language. Stoolap supports both in-memory and persistent storage models… ⌘ Read more

@prologic@twtxt.net porting SQLite would be kick-ass

Mu (µ) is now getting much closer to where I want it to be, it now has:

• A process stdlib module (very basic, but it works)
  
• An ffi stdob module that supports dlopen / dlsym and calling C functions with a nice mu-esque wrapper ffi.fn(...)
  
• A sqlite stdlib module (also very basic) that shows off the FFI capabilities
  

😅

@eldersnake@we.loveprivacy.club What version are you running btw? It’s probably time you upgraded and time I released a new version finally 😂 If you’re running a version that’s pre-SQLite-cache, then yeah I’m not surprised. The SQLite cache version is honestly much better 🤣

CAST(x AS STRING) casts to integer in SQLite
Comments ⌘ Read more

Show HN: SQLite Online – 11 years of solo development, 11K daily users
Comments ⌘ Read more

The Write Stuff: Concurrent Write Transactions in SQLite
Comments ⌘ Read more

TinqerJS – LINQ Inspired ORM for TypeScript Supporting Postgres, SQLite
Comments ⌘ Read more

I corrupted my SQLite test database with sed -i s/… $(find …). Clearly, I found too many files. That’s the signal to go to bed.

git-remote-sqlite: Single-file Git repos that can replicate with Litestream
Comments ⌘ Read more

LiveStore is a next-generation state management framework based on reactive SQLite and git-inspired syncing (via event-sourcing)
Comments ⌘ Read more

@lyse@lyse.isobeef.org that’s alright haha! i don’t expect anyone to listen/watch in full or with full attention bc it’s so long lmao

the thing with PHP for me is that i… feel like it hits a kind of simplicity that i can understand? it’s so plain but can be very powerful. i quite like that. as much as i can learn something infinitely more powerful, PHP hits a comfortable thing where i can handle things like backend sqlite DBs AND how a page is rendered, without requiring a complex frontend with its own quirks (like ruby on rails, which as much as i know and love it, can be heavy).

but i totally get you! PHP security is very scary. i’m always worried that i’m messing something up. it’s why the PHP application i’m working on i have dockerized by default for a small but extra layer of protection

i’ll try to not get discouraged tysm for your advice

SQLite Extension Hub
Comments ⌘ Read more

git-sqlite: A custom diff and merge driver for sqlite
Comments ⌘ Read more

A Can of Shardines: SQLite Multitenancy With Rails
Comments ⌘ Read more

There are 5 of such “Twters” on this pod that have periods in their nick 😢

sqlite> select count(distinct(nick)) from twters where nick like '%.%';
count(distinct(nick)) = 5
sqlite> select distinct(nick) from twters where nick like '%.%';
 nick = @marado@ciberlandia.pt

 nick = eapl.me

 nick = eapl.mx

 nick = grumpygordie.great-site.net

 nick = @chyrp.doesnm.cc

Hmmm there’s a bug somewhere in the way I’m ingesting archived feeds 🤔

sqlite> select * from twts where content like 'The web is such garbage these days%';
      hash = 37sjhla
  feed_url = https://twtxt.net/user/prologic/twtxt.txt/1
   content = The web is such garbage these days 😔 Or is it the garbage search engines? 🤔
   created = 2024-11-14T01:53:46Z
created_dt = 2024-11-14 01:53:46
   subject = #37sjhla
  mentions = []
      tags = []
     links = []
sqlite>

@abucci@anthony.buc.ci Apologies, the basic summary is as follows:

• Decided to rewrite the cache backend.
  
• It will now be a SQLite backend going forward.
  
• I’m planning on no data migration.

Doesn’t look like it Hmmm

sqlite> select * from twts where content LIKE '%Linux installation%';
    hash = znf6csa
feed_url = https://www.uninformativ.de/twtxt.txt
 content = I wonder if my current Linux installation will actually make it to 20 years:

    $ head -n 1 /var/log/pacman.log
    [2011-07-07 11:19] installed filesystem (2011.04-1)

It’s not toooo far into the future.

It would be crazy … 20 years without reinstalling once … phew. 🥴
 created = 2025-04-07T19:59:51Z
 subject = (#znf6csa)
mentions = []
    tags = []
   links = []

neat! my watcher is currently sitting at about 75 MB following over 1500 feeds. only about 200 are currently somewhat active.

-rw-r--r--. 1 xuu  xuu   69M Mar 25 20:46 twt.db
-rw-r--r--. 1 xuu  xuu   32K Mar 25 21:34 twt.db-shm
-rw-r--r--. 1 xuu  xuu  5.6M Mar 25 21:34 twt.db-wal

sqlite> select state, count(*) n from feeds group by 1;
hot|7
warm|8
cold|183
frozen|743
permanantly-dead|857

re reading so NewRAMStorage(…) is just something that setups your storage and initial data.. that can probably live with storage/sqlite. The point is the storage package does not import the implementations of storage.Storage It just defines the contract for things that use that interface. Now storage/sqlite CAN import storage and not have a circle dep.

It kinda works in reverse for import directions. usually you have your root package that imports things from deeper in the directory structures.. but for the case of interfaces it reverses where the deeper can import from parents but parents cannot import from children.

- app < storage
      < storage/sqlite
      < controller < storage
                   < storage/sqlite
 
- sqlite < storage

- storage X storage/sqlite

@lyse@lyse.isobeef.org OK. So how I have worked things like this out is to have the interface in the root package from the implementations. The interface doesn’t need to be tested since it’s just a contract. The implementations don’t need to import storage.Storage

• storage/ defines the Storage interface (no tests!)
  
  • storage/sqlite for the sqlite implementation tests for sqlite directly
    
  • storage/ram for the ram implementation and tests for RAM directly
    
• controller/ can now import both storage and the implementation as needed.
  

So now I am guessing you wanted the RAM test for testing queries against sqlite and have it return some query response?

For that I usually would register a driver for SQL that emulates sqlite. Then it’s just a matter of passing the connection string to open the registered driver on setup.

https://github.com/glebarez/go-sqlite?tab=readme-ov-file#connection-string-examples

@bender@twtxt.net oh yeah i remember that part of the docs lol! honestly yeah i think sqlite is fine for the number of users i have which is like, 5 including me, and active users is just… me, but if i were to have more active users i could always spin up a separate instance as jank as that is

i’m pretty sure i’m running this all off sqlite so if i get too many users on here i might be cooked but oh well i can always try to migrate (<– has heard migrations from sqlite to mysql/postgres are hell)

I wrote some code to try out non-hash reply subjects formatted as (replyto ), while keeping the ability to use the existing hash style.

I don’t think we need to decide all at once. If clients add support for a new method then people can use it if they like. The downside of course is that this costs developer time, so I decided to invest a few hours of my own time into a proof of concept.

With apologies to @movq@www.uninformativ.de for corrupting jenny’s beautiful code. I don’t write this expecting you to incorporate the patch, because it does complicate things and might not be a direction you want to go in. But if you like any part of this approach feel free to use bits of it; I release the patch under jenny’s current LICENCE.

Supporting both kinds of reply in jenny was complicated because each email can only have one Message-Id, and because it’s possible the target twt will not be seen until after the twt referencing it. The following patch uses an sqlite database to keep track of known (url, timestamp) pairs, as well as a separate table of (url, timestamp) pairs that haven’t been seen yet but are wanted. When one of those “wanted” twts is finally seen, the mail file gets rewritten to include the appropriate In-Reply-To header.

Patch based on jenny commit 73a5ea81.

https://www.falsifian.org/a/oDtr/patch0.txt

Not implemented:

• Composing twts using the (replyto …) format.
  
• Probably other important things I’m forgetting.

@bender@twtxt.net I have nothing against GoToSocial, but:

GoToSocial stores statuses, accounts, etc, in a database. This can be either SQLite or Postgres.

snac is simpler. Some JSON files and that’s it. I can read them with jq and less. I can use tar to back them up. I can hand edit them in a text editor.

https://kerkour.com/sqlite-for-servers Optimizing and get rid of SQLITE_BUSY error

https://antonz.org/sqlite-pivot-table/

Kev built his own microblog using WordPress and iOS shortcuts to separate his long and short posts. It sounds like this is a better alternative for him than micro.blog. Maybe with SQLite as the database he can even simplify this setup. ⌘ Read more

WordPress with SQLite
Philipp’s recent post on WordPress with SQLite reminded me to give the migration of a site I maintain for someone else a second try. ⌘ Read more

WordPress with SQLite
Philipp’s recent post on WordPress with SQLite reminded me to give the migration of a site I maintain for someone else a second try. ⌘ Read more

WordPress implemented SQLite support! 😱 (Not merged into Core yet, but you’re able to test it.) ⌘ Read more

WordPress implemented SQLite support! 😱 (Not merged into Core yet, but you’re able to test it.) ⌘ Read more

https://emacsconf.org/2022/talks/sqlite/ database

All the scripts on my Gemini capsule (except chess) have now been rewritten using Python and storing data in a SQLite database. This is the first time I’ve ever worked with database in a “production” environment, and I’m inordinately excited.

sqlite3 fiddle
With all my SQL and SQLite posts, this link is probably also quite interesting: sqlite3 fiddle. ⌘ Read more

“Friendlier SQL with DuckDB”
I come into contact with SQL almost every day, be it at work (Oracle Database) or while developing my blog software (SQLite). I don’t find SQL as bad as some others might, but sometimes SQL could be better. ⌘ Read more

“Consider SQLite”
I am convinced of SQLite. For many use cases where other databases like MySQL, MariaDB or PostgreSQL are often used, SQLite would suffice, given the right design and architecture. ⌘ Read more

rewrite of getwtxt (as getwtxt-ng) is going okay. i’m much happier with sqlite for storage.

Considering putting my log entries in a SQLite db. Will it fit? I average around 100K of content a month. That gives me a little more than 83 thousand years before I’m even up to a GB… Yeah, it’ll fit.

https://github.com/coleifer/sqlite-web DB python sqlite

https://www.linuxtricks.fr/wiki/sqlite-l-essentiel-sur-cette-base-de-donnees-cli-sql-php BDD sqlite

https://www.sqlitetutorial.net/ bdd documentation sqlite

https://litecli.com/ cli sqlite