create table boolean (
id integer primary key,
name text not null unique
)
insert into boolean (name) values ('true');
insert into boolean (name) values ('false');
create table document (
id integer primary key,
name text not null unique,
body text not null,
is_archived not null integer,
foreign key (is_archived) references boolean (id)
on delete cascade
on update no action
);
Solved.
Bonus: DBAs hate this one weird trick that can free up incredible amounts of disk space by deleting just two rows.
That’s what I like about Ruby ORMs. They did all the conversion for you, and you could have SQLite on your dev box, Postgres on the test server and MySQL on the annoying production host that wouldn’t run anything else.
WAL mode makes writes a lot faster, which is sufficient for a bunch of use cases. Writers do still need to wait, but they have to wait for a shorter duration. It's still not the right choice for write-heavy use cases, of course.
Use a CHAR(1) you can then use it as an enumeration.
Don't use T/F for true/false use it for the actual sematic meaning for the thing that the Boolean is toggling. E g. S for subscribed, U for unsubscribed, or whatever.
It also means when you inevitably grow to needing a tri-state it makes sense.
Unless SQLite actually supports enumerations, then just use them
34 Comments
TootSweet@lemmy.world · 60 pts · 98d
Solved.
Bonus: DBAs hate this one weird trick that can free up incredible amounts of disk space by deleting just two rows.
folekaule@lemmy.world · 46 pts · 97d
That
on delete cascadeis evil. I love it.Baizey@feddit.dk · 23 pts · 97d
Would this make 0 = true and 1 = false?
TootSweet@lemmy.world · 34 pts · 97d
You're right, that's way too simple. Definitely need to rotate the booleans daily. For... security. Yeah, security.
Baizey@feddit.dk · 15 pts · 97d
RustyNova@lemmy.world · 49 pts · 98d
I think you got the wrong caption. It's the world if SQLite supported multiple concurent writes.
Stupid transaction deadlocks...
irelephant@lemmy.dbzer0.com · 10 pts · 98d
In my case, I want to use sqlite locally, for development, but I don't want to add a load of jank to handle booleans for sqlite.
RustyNova@lemmy.world · 14 pts · 97d
I use rust's SQLx which map bools to numbers so it must be a problem with your connector maybe
irelephant@lemmy.dbzer0.com · 4 pts · 97d
Yeah I should probably open an issue.
WhyJiffie@sh.itjust.works · 0 pts · 97d
username checks out
or with their programming language
RustyNova@lemmy.world · 2 pts · 97d
I actually started using rust well after picking this username :P
nilloc@discuss.tchncs.de · 7 pts · 97d
That’s what I like about Ruby ORMs. They did all the conversion for you, and you could have SQLite on your dev box, Postgres on the test server and MySQL on the annoying production host that wouldn’t run anything else.
This was 18 years ago though.
HK65@sopuli.xyz · 1 pts · 95d
Are not all ORMs like that? I only used ActiveRecord before fucking off from backend 10 years ago
nilloc@discuss.tchncs.de · 1 pts · 56d
Probably. I stopped serious dev a decade ago now and just manage some old sites as a side gig.
qevlarr@lemmy.world · 2 pts · 97d
This is sqlite's intended use case. To replace configure files and local data
dan@upvote.au · 3 pts · 97d
WAL mode makes writes a lot faster, which is sufficient for a bunch of use cases. Writers do still need to wait, but they have to wait for a shorter duration. It's still not the right choice for write-heavy use cases, of course.
RustyNova@lemmy.world · 2 pts · 97d
I'm not actually looking for the speed most of the time, but more about preventing partial writes, so I'm still using it
Hadriscus@jlai.lu · 11 pts · 98d
What do you use instead of booleans ? floats ?
MultipleAnimals@sopuli.xyz · 42 pts · 98d
strings "true" and "false" ofc like any sane developer
Valmond@lemmy.dbzer0.com · 29 pts · 98d
I got a better one: O for true and N for false.
Seen in production for quite important stuff (payment requests).
O is from Oui, N from Non, of course!
😐🫤
felbane@lemmy.world · 5 pts · 96d
This is awful and aweful at the same time.
Valmond@lemmy.dbzer0.com · 1 pts · 95d
Non affective, non effective.
psud@aussie.zone · 3 pts · 92d
The system I work on uses "Y" and "N".
Hadriscus@jlai.lu · 26 pts · 98d
good fucking god
kubica@fedia.io · 32 pts · 98d
it allows for mood changes, some parts of the code can check
charAt(0) == 't'others can doval != 'false'just let it flow.Hadriscus@jlai.lu · 22 pts · 98d
lord mary joseph make it stop
sznowicki@lemmy.world · 23 pts · 98d
And for double fun if the output doesn’t matter you can make if endsWith(“e”).
deadbeef79000@lemmy.nz · 11 pts · 98d
Use a
CHAR(1)you can then use it as an enumeration.Don't use
T/Ffor true/false use it for the actual sematic meaning for the thing that the Boolean is toggling. E g. S for subscribed, U for unsubscribed, or whatever.It also means when you inevitably grow to needing a tri-state it makes sense.
Unless SQLite actually supports enumerations, then just use them
SpaceNoodle@lemmy.world · 5 pts · 98d
I think you could use a CHECK constraint to effectively create en enum
irelephant@lemmy.dbzer0.com · 10 pts · 98d
Sometimes it's 0 and 1
lord_ryvan@ttrpg.network · 4 pts · 97d
Smallest INT it can support and only ever use 0 and 1.
dalakkin@lemmy.world · 4 pts · 97d
If it just supported sorting by random with a seed..
altphoto@lemmy.today · 3 pts · 97d
But that's IFF.
lambisio@feddit.cl · 3 pts · 97d
I can live without Booleans I think... what saddens me more than nothing else is the lack of more proper treatment for Decimal-like types.