Show threadMichal  Jun 16

Aaand upgrade done, overall took ~30min to migrate my family #Nextcloud from #Sqlite3 to #Postgres

It does feel much snappier now, especially when loading many thumbnails.

Unfortunately the process does take a software engineering degree to finish (--clean-schema does not work due to missing `drop table oc_news_* ... *cascade*`), it's a lot of babysitting and manual corrections.

Show threadMichal  Jun 16

Turns out #sqlite3 does not have a built-in function to validate #utf8 - it just does GIGO (garbage in, garbage out): https://sqlite.org/invalidutf.html

So one quick hack of a utf8 validation loadable extension, I ran `select * from oc_filecache where isutf8(name) is null` and there was one (!!!) among the 3.5M entries (it was an external file).

`delete from oc_filecache where fileid=287791` and I'm ready to go again. Setting `cpupower frequency-set -g performance` and now it's just 20min left.

Invalid UTF Policy

N-gated Hacker NewsApr 27
☠️ Oh joy, yet another tale of how #SQLite3 becomes a sardine can when you try to cram in #multitenancy with Rails! 🐟 Apparently, the author's groundbreaking revelation is that ignoring #multithreading makes your app collapse faster than my interest in reading this. 🚀
https://blog.julik.nl/2025/04/a-can-of-shardines #Rails #Issues #SardineCan #HackerNews #ngated
A Can of Shardines: SQLite Multitenancy With Rails

There is a pattern I am very fond of - “one database per tenant” in web applications with multiple, isolated users. Recently, I needed to fix an application I had for a long time where this database-per-tenant multitenancy utterly broke down, because I was doing connection management wrong. Which begat the question: how do you even approach doing it right? And it turns out I was not alone in this. The most popular gem for multitenancy - Apartment - which I have even used in my failed startup back in the day - has the issue too. The culprit of does not handle multithreading very well is actually deeper. Way deeper. Doing runtime-defined multiple databases with Rails has only recently become less haphazard, and there are no tools either via gems or built-in that facilitate these flows. It has also accrued a ton of complexity, and also changes with every major Rails revision. TL;DR If you need to do database-per-tenant multitenancy with Rails or ActiveRecord right now - grab the middleware from this gist and move on. If you are curious about the genesis of this solution, strap in - we are going on a tour of a sizeable problem, and of an API of stature - the ActiveRecord connection management. Read on and join me on the ride! Many thanks to Kir Shatrov and Stephen Margheim for their help in this.

Julik Tarkhanov
Hacker NewsApr 27

Shardines: SQLite3 Database-per-Tenant with ActiveRecord

https://blog.julik.nl/2025/04/a-can-of-shardines

#HackerNews #Shardines #SQLite3 #ActiveRecord #DatabasePerTenant #HackerNews

A Can of Shardines: SQLite Multitenancy With Rails

There is a pattern I am very fond of - “one database per tenant” in web applications with multiple, isolated users. Recently, I needed to fix an application I had for a long time where this database-per-tenant multitenancy utterly broke down, because I was doing connection management wrong. Which begat the question: how do you even approach doing it right? And it turns out I was not alone in this. The most popular gem for multitenancy - Apartment - which I have even used in my failed startup back in the day - has the issue too. The culprit of does not handle multithreading very well is actually deeper. Way deeper. Doing runtime-defined multiple databases with Rails has only recently become less haphazard, and there are no tools either via gems or built-in that facilitate these flows. It has also accrued a ton of complexity, and also changes with every major Rails revision. TL;DR If you need to do database-per-tenant multitenancy with Rails or ActiveRecord right now - grab the middleware from this gist and move on. If you are curious about the genesis of this solution, strap in - we are going on a tour of a sizeable problem, and of an API of stature - the ActiveRecord connection management. Read on and join me on the ride! Many thanks to Kir Shatrov and Stephen Margheim for their help in this.

Julik Tarkhanov
Show threadDipoleApr 22

well

I ran ANALYZE. indexed by no longer affects performance, but it's slower than indexed by was before. 120ms

I might scream

#sqlite3 #database

Show threadDipoleApr 22

#sqlite3 #database #sqlite I have a strong preference for solutions I can make work with pypika, since there are parts of the codebase that are not conducive to prepared statements and that's what I use for query building. I haven't looked into how to make the indexed by and likely/unlikely work with pypika yet

Side note: I swear I did something earlier today that made the query complete in 40ms (still with the indexed by)and I'm losing my marbles trying to recreate that

DipoleApr 22

The #sqlite3 docs say that indexed by should not be used for query design -- only to prevent regressions. But what is the alternative? I have a #database where most queries are similar to the following:

select count(distinct tags.file) from tags indexed by tags_tag_nocase join meta on tags.file = meta.file where meta.trash = 0 and (tags.tag = 'foo' or tags.tag like 'foo.%')

Without any indexed by clause, the best I can get with this query is about 170ms. With the clause shown, it takes about 80ms. I've tried peppering in "likely" and "unlikely", but this is not an improvement

Some key facts about the data:

  • about 64K rows in tags, and 10K in meta
  • At most 10% of rows in tags can be expected to fulfill either of the tags.tag clauses
  • 90% of rows in meta have meta.trash=0

#sqlite

Michal  Apr 14

My first experience with #sqlite3 (well, #Sqlite then)

~2005 I had an mp3 player with a mini HDD (yes, hard drive [1]) that only allowed uploading music via a tool. I managed to find the files on the drive, but their names were "mangled" (e.g. 18274521.mp3)

I found a weird looking file that looked like it might contain some interesting data with "sqlite" at start of hexdump. I found sqlite online and managed to find the way to map the file names back to metadata.

[1] https://en.wikipedia.org/wiki/Portable_media_player#Hard_drive

Portable media player - Wikipedia

HabrApr 1

Codeby.Games. CTF TASK «ТЕТРИС»/«TETRIS»

Приветствую всех любителей CTF и этичного хакинга на стороне Red Team! В этой статье мы рассмотрим прохождение легкого таска "ТЕТРИС", разработанного пентестерами из команды Codeby.Games . Справка: codeby.games - отечественный условно бесплатный веб-проект, где каждый может попрактиковаться в оттачивании навыков наступательной кибербезопасности. Таски (задания) представлены в широком спектре: начиная от использования методов OSINT и заканчивая компрометацией учебного домена Active Directory. CTF разделяются на три группы - "Легкий", "Средний", "Сложный" в различных категориях. Но подробнее об этом - на официальном сайте проекта. Задание "Тетрис" находится в категории "Веб". Категория посвящена оттачиванию практических навыков в рамках OWASP Top 10. Цель этого задания - получить доступ к панели администратора веб-приложения и захватить флаг. Общий план решения CTF выглядит так:

https://habr.com/ru/articles/896470/

#burpsuite #red_team #sqlite3 #python

Codeby.Games. CTF TASK «ТЕТРИС»/«TETRIS»

Приветствую всех любителей CTF и этичного хакинга на стороне Red Team! В этой статье мы рассмотрим прохождение таска средней сложности "ТЕТРИС", разработанного пентестерами из команды Codeby.Games ....

Хабр
LoaferMar 13

Yay, I was right!

#python3 #sqlite3 #database #db #connections

https://github.com/Zaloog/kanban-tui/issues/14#issuecomment-2718495556

Implement Database Connection Pooling · Issue #14 · Zaloog/kanban-tui

I am not sure about this, but I remember in Java that creating a database connection is expensive, and so database connections should be created once and stored in a database connection pool. Of co...

GitHub