github.com
I've found my SQL knowledge to be lacking, so I made a project that uses SQLite as a backend.
As it is intended for self-hosting I aim to make it easy to set up and maintain. Getting it up & running takes no more than a few commands (bar setting up a proxy such as nginx, which is out of scope).
I've set up a "demo" site at https://forum.agreper.com/ if you want to try out the UI.
You might take a look at Fossil (fossil-scm.org) which is not a forum, but is an all-in-one source control system, bug tracker, wiki, and web backend, all implemented as an SQLite client written in C. Its blurb says a scripting language might sound more attractive than C superficially, but in Fossil, SQLite does all the heavy lifting that would otherwise have been done with scripts. On my bottomless todo list I want to study its implementation sometime, since I think it would be a good example of "advanced" uses of SQL and SQLite. That is completely independent of Fossil's purpose as a VCS application. I'm ok at basic SQL but not so clueful about sophisticated uses.
One thing to watch out for is that since SQLite is an embedded DB, when you make an SQlite query, the overhead is basically a local subroutine call rather than the network traffic that a client/server DB would incur. That in a way changes the asymptotics of an application: particularly, the "N+1 queries" antipattern of some client/server DB apps is a perfectly ok design strategy for SQLite. I believe Fossil may rely on this in some way.
If I was developing a new forum engine, I'd base it on federated activitypub protocol.
I miss the days of good ole forum software (e.g. FluxBB/PunBB) before it was superseded with massive JS bloat/clunky forum software like Discourse, Flarum, etc.
I also commend demindiro on not requiring JS. It is refreshing to see simple yet useful applications.
[1] - https://www.phpbb.com/community/viewtopic.php?t=2106245
EDITED: *conversations
For example, the Tab key jumps to the next unread message, Space/Backspace scroll up/down within a message, and the Up/Down arrow keys jump to the previous/next message (regardless if unread or not). A message is marked as read automatically when you navigate to it (it gains focus). There’s also a keyboard shortcut to mark a read message as unread again if you w.
The keyboard navigation means that only one message has focus any point in time. You have a threaded view of all messages, where the unread ones are highlighted (e.g. by bold typeface). Then you can decide which ones to navigate to. Usually you just use Tab and Space to jump/scroll through the unread messages. It’s incredibly efficient to use.
Important distinction.
New Reddit also fell prey to bad development practices and is without exaggeration unusable for me. It often "crashes" and the whole page goes dark-grey on my browser, and this has been happening for at least a year. After reloading I can't see all messages without navigating away, and middle click messes with the scrolling. At this point I will assume they either don't care or are fucking with me.
This is not new, it just isn't applied to web based forums as much as I (someone who likes threaded discussions like that) would like. For small discussions it make little difference, for large branching threads it can help greatly when you don't care to follow all the sub-threads. It was not uncommon in Usenet clients back in the day. Of course it needs to be coupled with good UI for collapsing branches and otherwise navigating the tree, and perhaps a chronological view option for issue who prefer that (or a tree-view with posts spaced by arrival order for the best of both of you do it right - I had a reader that did that but can't currently remember it's name).
But the only forum I know of that realised how to properly do it is this old Russian software forum [1] You need a mail client-like interface so that you can see your place in the discussion.
Triple quoting someone and trying to read the actual thread in a flat forum sucked then and sucks now.
[1] Example link http://rsdn.org/?forum/philosophy/8357952
Forum software of old actually gave users the power to choose the interface they want. We've really gone backwards with software interfaces.
Also, themes. These days all we get are "Dark Mode" and "Light Mode". Boring.
Until JS came along, reading online forums on a broadband internet connection was a worse user experience than using BBS forum software on a dial up modem.
Every single action, full page reload. And until cloud computing came along, even the best of website forums had servers that took forever to respond.
Somehow a BBS written for a 386 16mhz in 1992 was faster than a forum written in 1999 or even 2005. Oh wait, the 1992 forum was likely written in C, or even assembly! (And it only had 1 user at a time, that helped!)
A 'classical' web forum is threaded, contrasting with an unthreaded (bulletin) board - at least that's how the terminology was used in the German webdev scene of the late 90s/early 2000s, possibly due to the prominence of SELFHTML and its forum.
Good times. Sad that things have moved on to systems that don't suck quite so much but that's progress for you.
With modern forums, it’s mostly the UX that sucks. PWAs that never recover from errors, endless scrolling, stuff like that. Also
* Falsehoods forum developers believe about network reliability – did you know the network can be down? Then up again?
* Falsehoods forum developers believe about time – did you know that time can jump forward, by huge amounts, because the page was suspended for whatever reason?
Turns out, if you try to do the browser’s job, that’s a lot of work! Just don’t.
Performance is usually good enough for me, even with all the JS and whatnot.
Curious, what about vBulletin v4 and v5 do you not like?
And then vB 4 landed and turned out to be insanely poor launch - forum index was plugin based, but it was very easy to end with index page that did 700 mysql queries, which took forever to execute without relying on full page cache like varnish.
Today its supposedly much better, but Internet Brands uses internal version of vBulletin 3 (3.8) to run their sites instead of v4 or v5.
vBulletin 4 was also okay in the end, but I always thought vBulletin 5 was trying to be too many things at once. It’s just too modern! I understand, of course, that the online community landscape has changed since back then and vBulletin has to adapt to remain competitive.
Now on my phone it’s relatively easy to jump to the top of the page. It’s also almost done impossible to jump to the bottom.
On a desktop I can at least mash the END key or play silly reindeer games with the scrollbar thumb.
On mobile it’s very hard to Leap or Surge or Jump.
If I’m madly fanning the page with my thumb or, worse, my fingers because I’m tired with my thumb, I pretty much just give up at that point.
I also like having non-endless scrolling because I can use the scroll bar as a gauge of how much is left of the article.
Being constantly fed is just a bad experience.
But the biggest reason people hate as a matter principle is because it is in 99% of cases done without any UX research and without any care from developers, and that's in the best case. The worst case is to cause doomscrolling, which is nefarious in its own.
It is disrespectful to users. If you don't want people seeing old content just fucking delete it.
For reading, do you prefer books or ancient scrolls?
I'm not saying your complaint is invalid, just that there are ways of mitigating the issue. And that said, I can't imagine how reddit threads would work if paginated.
Presumably similarly to HackerNews threads, which are paginated.
This also means you need a predictable, deterministic algorithm, like "ORDER BY DATE DESC", but Reddit does have order by popularity, so that will not be idempotent. But now being super picky as order by popularity is pretty useful. Although I think date should be the default. And order by [what cambridge analytica-likes know about me] should be off.
HackerNews posts are paginated. And there isn't any interface for navigating the pages. Which is fine for HN's minimalist approach and tech-oriented audience. But it hardly seems optimal for a general audience.
It's not that hard. I saw good example of paginated threads at livejournal.com: it paginates root comments and collapses large subthreads. https://ammo1.livejournal.com/1348094.html?view=comments#com...
Back in the day, UBB was the most popular forum script but when vBulletin came out with its extremely comprehensive administration panel and constantly improving features, it started to replace UBB very fast. Then appeared free alternatives like phpBB, which probably became the most popular alternative for a long time and also there was a popular alternative written in ASP: Web Wiz Forums.
Sure something like phpBB is light, but the UX is also barely usable. Give me something like Reddit or Discourse over that any day. Why can't we have the best of both worlds?
Hackernews on the other hand manages to lack both (in some speculations intentionally), with neither usable design nor speed.
Improving either is an uphill battle when decision makers don't value the work, at best, or at worst are committed to resistance. And you don't even get paid.
For example, Blender pre-v2.8, many many flamewars belittling people who wanted something usable, akin to calling them shallow/superficial Untermensch who "don't understand" Blender's supposedly genius but strange design choices at that time.
I suppose that's why most aren't capable of making a product and are relegated to being an employee or unpaid open source employee.
Can't you do a lot of that with CSS?
I actually shortened it, expanded it to add bot detection and published the update also under 1KB: https://gist.github.com/Xeoncross/1503594
Such abuse PHP could handle back then.
I'm absolutely loving the fact that the HN zeitgeist has come around to embracing these no-js solutions focusing on function over form.
Every once in awhile I come across little sparks in the forum space, but they all fizzle out for one simple reason: building a forum software isn't as easy as you think it is.
For us, it took three developers a year apiece of full-time work to reach rough feature parity with our competitors, and we're still trying to innovate nearly a decade in (and add in the constant need for reducing technical debt, etc.)
For now I will say what I always say when I see new competitors in the forum space — give me a run for my money. Keep me on my toes! I appreciate the challenge, and it gives me that extra incentive to make our software the best it can be.
It definitely feels like we're going full circle with the Web and back to its earlier, decentralized, stripped-down past.
I didn't knew but I love it... I wish more developers took interest in such things.
Thanks for sharing it :)
Do you intend to extend it and keep working on it or was it just a one off project?
"Modern practices make for slow apps" is a common trope on HN, but it's important to note that it's not just modern practices that make modern web apps feel slow; modern feature sets are the larger culprit.
This app could be a React SPA and it would still be faster than most forums you've used because it intentionally has a minimal feature set.
Modern forums haven't innovated all that much, you have infinite scrolls, notifications and live updates, that's all that I can think of, off the top of my head. If you go on the main page of a modern forum and a Phpbb one they'll be functionally equivalent for the end user.
And even if they had innovated, you can put as big a feature set as you want on the backend and not send 20Mb of JavaScript along with a single request per post and your page will load instantly.
And that's not even going into all the websites which are entirely featureless, I regularly see SAAS landing pages that take over a minute to fully load, that's just to display text and images.
Maybe the stack is capable of being fast and light theoretically but it never is in practice.
I'm not comparing modern forums to older forums, I'm comparing both types of forum to OP's minimal forum.
> And even if they had innovated, you can put as big a feature set as you want on the backend and not send 20Mb of JavaScript along with a single request per post and your page will load instantly.
You have to distinguish between initial page loads and subsequent interactions. Forums are not typically one-page-and-done websites: you spend a lot of time navigating around the different views.
For this use case, phpBB forums were and are very slow.
I tested out the phpBB demo [0] and compared it with the Discourse demo [1]. The Discourse demo has a slow initial load, but it's quite snappy after you've downloaded the 6.23mb (not 20mb!) of JS and CSS. The phpBB demo seems faster on the initial page load, but it gets pretty painful to navigate as you click around into different sub-forums.
The reason why the OP's no-JS forum can outperform both phpBB and Discourse is primarily that it doesn't do very much work on each user action, not that its stack is inherently superior. The biggest bottleneck in a forum will be the speed of each request-response cycle, not whether the requests return JSON or HTML.
The pbpBB page you linked downloads 330 KB. Or you can disable JavaScript (and the page still works), and it only downloads 156 KB.
Discourse sucks.
I hate forums which indent replies and don’t support collapsing sub-threads.
The proper way of handling replies is to post them under each other and support quoting.
[-] should collapse subthreads. Caveat is that the browser doesn't remember which subthreads were collapsed due to no-JS.
In 1999 I had a cable modem connection that could pull down 2 megabytes per second and ping up and down the west cost well under 50ms.
But forum servers were slow. Like, really really slow. When Sites like Reddit finally came to, 15 or so years later, with inline replies, they were a breath of fresh air.
Back in 99 Slashdot had massive resources put into making it responsive, lots of servers thrown at it, and it was still laggy and slow compared to Reddit now days.
Everyone arguing about "time to first draw being under 100ms" forgets that just a short time ago, web servers took more than 100ms to respond to a connection.
In 1999 I would have been jealous of your internet connection — I still had a 256kbps ISDN line, and there were fast forums even then (but mostly not). I think it mainly depended on whether the sites rendered HTML on the fly (with like maybe Perl? to render database content) or... just served static HTML.
Page reloads only got expensive when we decided that they would do a bunch of other shit besides that.
100ms is noticeable, 500ms is bad.
The old dial up BBSs may have been slow enough that you could watch character appear, but they were responsive! Those characters started printing right after your keypress happened. Of course it helped that it was likely a local phone call.
I imagine HN ever had a 500ms response rate, the engineers behind it would consider it a failure!
(Even reddit is under 500ms for many operations, and it is super heavyweight)
> I think it mainly depended on whether the sites rendered HTML on the fly (with like maybe Perl? to render database content) or... just served static HTML.
Yeah Perl didn't help the 90s web at all. It made stuff possible, but wow, the performance was bad.
Then again web server software design back then was also a long ways away from what we know to do now.
And yet, once you wait that 100ms or however long it takes for the server to respond, your page is fully loaded and interactive. During the loading process, your browser remains responsive and your CPU is not loaded.
Nowadays, we still wait the same amount of time for the initial load, but now your CPU is at 100% parsing megabytes of shitty Javascript, and once the initial load completes, you're still not guaranteed the page won't slow down/overload your CPU again because some stuff can be asynchronously loaded (supposedly for performance reasons, even though doing it on the backend as part of the initial load would be faster in the vast majority of cases).
The new reddit site is fast. Yes initial load time is like 2 seconds, but after that, everything else is instant. Click comments, escape out, go back to feed, load comments of a different story, it is quick.
Yes crappy JS abounds everywhere, NYT is an offender recently (they used to be good!), lots of sites suck in this regard, but when modern web dev is done well, it works really well.
Though having read the Reddit engineering blog posts, they throw a ton of resources at ensuring Reddit is fast.
Meanwhile, Ars Technica loads things almost instantly, but for the last year or two (!!) their article pages have leaked memory and CPU, eventually taking up all my CPU and gigs of memory.
Not sure how even. Great load times, responsive site, keep that background tab open and watch the CPU start to melt down.
Disagree. I just gave it a try on a desktop (!) browser – when switching from the thread list into a particular comment thread, "new" reddit is definitively slower than old.reddit.com, both visually (after the thread page as such displays, it spends another second or two loading the actual comments, whereas on old.reddit.com everything renders instantly), and measured end-to-end (from clicking on the "comments" link to the moment the comments actually appear) as well.
On my underpowered phone, "new" reddit fares even worse, and never mind all that additional "you can only view this in the app"-crap that was thankfully never backported to old.reddit.com.
I did not recognize the [-] as it looks like some weird emoji on my mobile browser due to the too small and uncentered background.
So you get a vast amount of low quality replies above the next high-quality reply.
For low volume it works okay because you can manually collapse but for high volume once it starts paging it breaks down and you just get further into the top level comment without properly being able to escape.
Reddit's model of auto-collapsing so you get a mix of threads and top-level replies is much better for picking out the better responses.
Also for long list of replies, you also only show first few.
This design may helps "smaller comments" to surface when the conversation is relatively "mature".
However, the main issue is that when you can quote multiple comments (when you are in a firehose view) you tend to send a single post for multiple replies; I have no idea how that could be shown in the tree view.
Essentially a firehose is a superset of a tree structure, if your unit is a post.
The potential for problems would arise when everyone Replied using a root-level post to the firehose. This could be mitigated with prominent Reply links on every post in the firehose, with the root-level contribution form deemphasized. So you can quote multiple people but you still chose a parent by choosing which Reply link to click.
One problem is that when people are manually quoting a post, they can edit that quote down to only include the relevant portion of the post they're replying to, and depending on the forum culture unnecessary full-quoting is actively being discouraged.
Automatically added quotes on the other hand don't have such a luxury, so you'd get a big bunch of full quotes, which wouldn't really be ideal, either.
I ask because I believe this is the hardest part of a forum software; The one thing that make or break a community is whether there is timely and fair moderation so contents are fresh and relevant.
I will definitely focus on having good moderation features as I also believe it is essential for a healthy forum.
And specifically, looks great - clearly outlined, threaded, indented, flow of conversation.
I'm pretty okay with that, my only complaint is that when you vote a comment or story it creates an invisible image with a voting URL, which may be loaded much later so the click may not register from time to time.
You are using flask. How is the performance? I moved away from flask to FastAPI but another one of my goal has been to create a program that can run on cheapest VMs availalble in the market, which means very low ram. I am currently trying to rewrite the server into Go and use something that can consume jinja2 templates in it.
I'm renting a VPS in New York which costs ~€20 a month (€15 for VPS with 1 dedi 3900X core, 4GB RAM and 80GB NVMe, €3 for DDoS protection and €1.5 for up to 7 daily backups), which in hindsight is overkill.
I do intend to add pagination but I'm not sure what a clean implementation would look like.
Here's my shameless plug: `pip install sqlitedao`: https://github.com/Aperocky/sqlitedao
my experience optimizing SQL has been removing the ORM layer via hand rolling SQL queries.
Most of my peers also seem to agree that ORM is great for knocking a feature out, but you’ll inevitably run into performance concerns at scale and need to write your own optimized queries yourself.
I feel like with SQLite having limited resources, you’d likely want to optimize your queries as best you can without an ORM? Is that not the case?
The fundamental issue with both handrolled SQL and ORM is inefficient queries leading to linearly increasing time consumption. for instance usage of `LIMIT` and accidentally querying on non-indexed fields. But I don't see how this differentiate the 2 as I have seen it happen in both situations professionally.
A good ORM enforces certain behaviors, specifically querying by index and constant time pagination. This guards against accidental querying behaviors.
ORM's never scale and you end up with hand written SQL long term. Unless it is really just a CRUD app then ORM's really work fairly well.
- A weak CAPTCHA that will be replaced with something stronger (probably image-based)
- Disabling registrations
const input = document.querySelector('body > main > form > table > tbody > tr:nth-child(3) > td:nth-child(2) > input');
const challenge = document.querySelector('body > main > form > table > tbody > tr:nth-child(3) > td:nth-child(1)').innerText.split('=')[0];
input.value = eval(challenge);