@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

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 = []

@prologic@twtxt.net Spring cleanup! That’s one way to encourage people to self-host their feeds. :-D

Since I’m only interested in the url metadata field for hashing, I do not keep any comments or metadata for that matter, just the messages themselves. The last time I fetched was probably some time yesterday evening (UTC+2). I cannot tell exactly, because the recorded last fetch timestamp has been overridden with today’s by now.

I dumped my new SQLite cache into: https://lyse.isobeef.org/tmp/backup.tar.gz This time maybe even correctly, if you’re lucky. I’m not entirely sure. It took me a few attempts (date and time were separated by space instead of T at first, I normalized offsets +00:00 to Z as yarnd does and converted newlines back to U+2028). At least now the simple cross check with the Twtxt Feed Validator does not yield any problems.

@prologic@twtxt.net In all my two Go projects I use modernc.org/sqlite and can’t complain. Works great for me.

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

Thanks, @movq@www.uninformativ.de!

My backing SQLite database with indices is 8.7 MiB in size right now.

The twtxt cache is 7.6 MiB, it uses Python’s pickle module. And next to it there is a 16.0 MiB second database with all the read statuses for the old tt. Wow, super inefficient, it shouldn’t contain anything else, it’s a giant, pickled {"$hash": {"read": True/False}, …}. What the heck, why is it so big?! O_o

@movq@www.uninformativ.de Yeah, most of the graphical applications are actually KDE programs:

• KMail – e-mail client
  
• Okular – PDF viewer
  
• Gwenview – image viewer
  
• Dolphin – file browser
  
• KWallet – password manager (I want to check out pass one day. The most annoying thing is that when I copy a password, it says that the password has been modified and asks me whether I want to save the changes. I never do, because the password is still the same. I don’t get it.)
  
• KPatience – card game
  
• Kdenlive – video editor
  
• Kleopatra – certificate manager
  

Qt:

• VLC – video player
  
• Psi – Jabber client (I happily used Kopete in the past, but that is not supported anymore or so. I don’t remember.)
  
• sqlitebrowser – SQLite browser
  

Gtk:

• Firefox – web browser
  
• Quod Libet – music player (I should look for a better alternative. Can’t remember why I had to move away from Amarok, was it dead? There was a fork Clementine or so, but I had to drop that for some unknown reason, too.)
  
• Audacity – audio editor
  
• GIMP – image editor
  

These are the things that are open right now or that I could think of. Most other stuff I actually do in the terminal.

In the past™, I used the Python KDE4 bindings. That was really nice. I could pass most stuff directly in the constructor and didn’t have to call gazillions of setters improving the experience significantly. If I ever wanted to do GUI programming again, I’d definitely go that route. There are also great Qt bindings for Python if one wanted to avoid the KDE stuff on top. The vast majority I do for myself, though, is either CLI or maybe TUI. A few web shit things, but no GUIs anymore. :-)

The other day, after a discussion online, we came to the conclusion that using awk+sed+tr could replace much of the development that requires a database. However, using SQLite to have a SQL syntax isn’t a bad idea either. What do you think?

Thanks, @xuu@txt.sour.is, great explanation. In another project I’ve structured it exactly like you wrote. The mock storage over there extends the SQLite storage and provides mechanism to return errors and such for testing purposes:

• storage/ defines the interface
  
  • sqlite/ implements the storage interface
    
  • mock/ extends the SQLite implementation by some mocking capabilities and assertions
    

Here, however, there are no storage subpackages. It’s just storage, that’s it. Everything is in there. The only implementation so far is an SQLite backend that resides in storage. My RAM storage is exactly that SQLite storage, but with :memory: instead a backing file on disk. I do not have a mock storage (yet).

I have to think about it a bit more, but I probably have to do exactly that in my tt rewrite, too. Sigh. I just have the feeling that in storage/sqlite/sqlite_test.go I cannot import storage/mock for the helper because storage/mock/mock.go imports and embeds the type from storage/sqlite. But I’m too tired right now to think clearly.

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

@xuu@txt.sour.is My layout looks like this:

• storage/
  
  • storage.go: defines a Storage interface
    
  • sqlite.go: implements the Storage interface
    
  • sqlite_test.go: originally had a function to set up a test storage to test the SQLite storage implementation itself: newRAMStorage(testing.T, $initialData) *Storage
    
• controller/
  
  • feeds.go: uses a Storage
    
  • feeds_test.go: here I wanted to reuse the newRAMStorage(…) function
    

I then tried to relocate the newRAMStorage(…) into a

• teststorage/
  
  • storage.go: moved here as NewRAMStorage(…)
    

so that I could just reuse it from both

• storage/
  
  • sqlite_test.go: uses testutils.NewRAMStorage(…)
    
• controller/
  
  • feeds_test.go: uses testutils.NewRamStorage(…)
    

But that results into an import cycle, because the teststorage package imports storage for storage.Storage and the storage package imports testutils for testutils.NewRAMStorage(…) in its test. I’m just screwed. For now, I duplicated it as newRAMStorage(…) in controller/feeds_test.go.

I could put NewRAMStorage(…) in storage/testutils.go, which could be guarded with //go:build testutils. With go test -tags testutils …, in storage/sqlite_test.go could just use NewRAMStorage(…) directly and similarly in controller/feeds_test.go I could call storage.NewRamStorage(…). But I don’t know if I would consider this really elegant.

The more I think about it, the more appealing it sounds. Because I could then also use other test-related stuff across packages without introducing other dedicated test packages. Build some assertions, converters, types etc. directly into the same package, maybe even make them methods of types.

If I went that route, I might do the opposite with the build tag and make it something like !prod instead of testing. Only when building the final binary, I would have to specify the tag to exclude all the non-prod stuff. Hmmm.

@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)

@prologic@twtxt.net that “little database that could” is simply amazing, isn’t it? I run Conduwuit (nevermind, this one is RocksDB), and GoToSocial using it as a backend, no issues. And, of course, sqlite is the database of choice for a lot of things under iOS.

@prologic@twtxt.net, are you running Gitea with an SQL backend, or using sqlite? Any reason have haven’t moved to Forgejo?

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.

a decentralized community !zet. individual zet feeds could be managed using something like git/git submodules, then built locally into self-contained SQLite files. zet items would be referenced by their zet nickname and UUID. #halfbakedideas

user-defined order in SQL [[https://begriffs.com/posts/2018-03-20-user-defined-order.html]] #sql #links maybe something that can be adapted to !sqlite?

huh. it seems that dumping + gzipping a SQLite database can sometimes have better compression than gzipping the SQLite database directly. cool. #sqlite

A fragment of my !monolith program has been woven to a !weewiki from !worgle using !sqlite. Find it for now at [[/proj/monolith/wiki/][the monolith project page]].

!worgle -> !sqlite -> !worgmap -> !weewiki is kinda working?

It turns out that fts5 is enabled by default on SQLite! My twtxt2sqlite generator has been updated to use fts5. Now I can do full text search on all my twtxt tweets. I have implemented a related-tweets box in the !twtxt_playground as a proof-of-concept. More info on fts5 can be found at [[https://www.sqlite.org/fts5.html]].

here is the script I use to convert my twtxt feed into a SQLite database: !twtxt_sqlite

a unique thing I do with my twtxt feed is convert it to a SQLite database. This, combined with the Janet + SQLite scripting abilities available in SQLite, could provide interesting metrics and insights over time.

While certainly not a solution to everything, I find I’m using temporary SQLITE database a bunch to solve problems with a few lines of sql and less then 50 lines of code (to insert data into the SQLITE DB) instead of several hundred of lines of code and a bunch of arrays.