> And, IMO, making dynamic queries harder is preferable. Dynamic queries are inherently unsafe. Sometimes necessary, however you have to start considering things like sql injection attacks with dynamic queries.
Depends on what you mean by "dynamic query". You are dealing with injection attacks as soon as you start taking user input. Most useful user facing applications take user input.
In a simple case it might be "SELECT * FROM posts WHERE title LIKE '%hello world%', where "hello world" is a user specified string. This is easy with sqlx. Where things get more difficult is if you want to optionally add filters for things like date posted, score of the post, author, etc... That makes the query dynamic in a way that can't be solved by simply including a bind.
That's where sea-orm shines over sqlx IMO. sqlx will force you to do something like
```
let mut my_query = "SELECT * FROM posts WHERE title LIKE '%' + $1 + '%'";
let mut my_binds = vec![args.keyword];
if let Some(date) = args.date {
my_query = format("{my_query} AND date = $2");
my_binds.push(date);
}
...
```
Your building a string and tracking binds. It gets messy. A good query builder like seaorm has lets you do something this:
```
let mut query = Posts::find().filter(Column::title::like(args.keyword));
if let Some(date) = args.date {
query = query.filter(column::Date::eq(date));
}
```
This pays off as your queries get more complicated. It pushes the string manipulation and bookkeeping into a library, which can be more thoroughly tested.
It also lets you pass around typed partial queries, eg in the example above query might be returned from a function, which helps you build more modular code.
> Similar thing can be said about writing SQL. I was really happy with using sqlx, which is a crate for compile-time checked SQL queries. By relying on macros in Rust, sqlx would execute the query against a real database instance in order to make sure that your query is valid, and the mappings are correct. However, writing dynamic queries with sqlx is a PITA, as you can’t build a dynamic string and make sure it’s checked during compilation, so you have to resort to using non-checked SQL queries. And honestly, with kysely in Node.js, I can get a similar result, without the need to have a connection to the DB, while having ergonomic query builder to build dynamic queries, without the overhead of compilation time.
I've used sqlx, and its alright, but I've found things much easier after switching to sea-orm. Sea-orm has a wonderful query builder that makes it feel like you are writing SQL. Whereas with sqlx you end up writing Rust that generates SQL strings, ie re-inventing query builders.
You also get type checking; define your table schema as a struct, and sea-orm knows what types your columns are. No active connection required. This approach lets you use Rust types for fields, eg Email from the email crate or Url from the url crate, which lets you constrain fields even further than what is easy to do at the DB layer.
ORMs tend to get a bad reputation for how some ORMs implement the active record pattern. For example, you might forget something is an active record and write something like "len(posts)" in sqlalchemy and suddenly you are counting records by pulling them from the DB in one by one. I haven't had this issue with sea-orm, because it is very clear about what is an active record and what is not, and it is very clear when you are making a request out to the DB. For me, it turns out 90% of the value of an ORM is the query builder.
I haven't tested that, so I'm not sure if it would work. The import only inserts rows, it doesn't delete, so I don't think that is the cause of fragmentation. I suspect this line in the vacuum docs:
> The VACUUM command may change the ROWIDs of entries in any tables that do not have an explicit INTEGER PRIMARY KEY.
means SQLite does something to organize by rowid and that this is doing most of the work.
Reddit post/comment IDs are 1:1 with integers, though expressed in a different base that is more friendly to URLs. I map decoded post/comment IDs to INTEGER PRIMARY KEYs on their respective tables. I suspect the vacuum operation sorts the tables by their reddit post ID and something about this sorting improves tables scans, which in turn helps building indices quickly after standing up the DB.
I did something similar. I build a tool[1] to import the Project Arctic Shift dumps[2] of reddit into sqlite. It was mostly an exercise to experiment with Rust and SQLite (HN's two favorite topics). If you don't build a FTS5 index and import without WAL (--unsafe-mode), import of every reddit comment and submission takes a bit over 24 hours and produces a ~10TB DB.
SQLite offers a lot of cool json features that would let you store the raw json and operate on that, but I eschewed them in favor of parsing only once at load time. THat also lets me normalize the data a bit.
I find that building the DB is pretty "fast", but queries run much faster if I immediately vacuum the DB after building it. The vacuum operation is actually slower than the original import, taking a few days to finish.
I'm curious if you have tried SeaORM? I've used it a little bit (not too extensively) and really like it. It's like sqlalchemy in that you can declare your tables and have a type checked query builder, which is a big win IMO. It's nice to add/change a field and have the compiler tell you everywhere you need to fix things.
I've definitely had issues when using sqlalchemy where some REST API type returns an ORM object that ends up performing many queries to pull in a bunch of unnecessary data. I think this is harder to do accidentally with SeaORM because the Rust type system makes hiding queries and connections harder.
Most of my usage of SeaORM has been as a type query builder, which is really what I want from an ORM. I don't want to have to deal with lining my "?" or "$1" binds or manually manipulate strings to build a query. IMO a good query builder moves the experience closer to writing actual SQL, without a query builder I find myself writing "scripts" to write SQL.
This is what a number of startups, such as Yurts.ai and Vannevar Labs, are racing to build for organizations. I wouldn't be surprised if, in 5-10 years, most large corps and government agencies had these sort of LLM/RAGs over their internal documents.
Here is straw man proposal, similar to cert chains and webs of trust: Say I'm a "curator". I say on HN/Reddit/Discord "here is my key hash 'p2pcuration:185da2bc59167692f596404fd83235f9bcb4e107b041f2e6e8d972da6dba00b7'". Any user that clicks the link or copies it into the search app adds the key to the trusted user list. With my private key I can sign torrents after I download them myself, which would mark the torrent as "good". When anyone who has added my key searches, the system searches for a corresponding signature from me as well. If a signature is found, the UI can chose to elevate that result.
The system could be extended so that signers could also sign other keys, expanding the trust network.
This system doesn't need to be run or maintained by each user. It could be served through a webui that can be run locally or shared with a small community. Migrating the interface to a new host would just require moving the config and keys.
I've met Ross during my time at Princeton and he is a really genuine person, he is not trying to ruin anyone's life. This incident is the result of an uncharacteristic blind spot in empathy: a mistake.
I also have experience with the Princeton IRB on similar topics. The reality is that Princeton's IRB, and IRBs in general, are not equipped to deal with this sort of online research. IRBs were created as a reaction to unethical medical research, in particular the Tuskegee Syphilis Study [1]. My experience has been that the IRB has a greater expertise on medical and sociological studies. This leads the IRB to having a very narrow view of its remit in other domains. Unless humans are in a very literal way "subjects" of the study, then the IRB doesn't see it as human subjects research. In this case the IRB likely saw "Free Radical" and other websites as the subject. In both my studies and those done by my peers, the responses on what is and isn't human subjects research is uneven and you will often get a generic "this study does not constitute human subjects research" response from the IRB. This can be the case even if there possible negative repercussions to the "not subjects" in your research.
For example, say your study involves testing the vulnerability disclosure policies. How well do websites respond to vuln reports? In your study you send out 100 vulnerability disclosures. After you report these vulnerabilities, a human may read your vulnerability report and make a decision based on it. This presents a risk that the individual security team employees involved in your study will be scapegoated and fired when you publish your (potentially damning) results. How do you balance the value this study provides the public against the risk to the individual employees' livelihoods? The IRB isn't going to help you do this balancing, they will just say "this isn't human subjects research".
IRBs quite simply aren't equipped to evaluate this sort of research at the moment. This can be frustrating for a young twenty-something researcher just out of college trying to do the right thing while generating impactful research. You come in thinking that the IRB will be a guiding hand of wisdom and prudence, but you are quickly disabused of that notion after most of your interactions feel like conversations with lawyers in a compliance department. Many researchers in "CS" don't even involve the IRB, because they don't always see the ethical dimension of their work, but the fact that Ross did shows that he was trying to do the right thing here.
This paper was published when there were big fights going on about whether the block size should be increased. Om camp argued for the status quo, which was causing high transaction fees and long backlogs. The other camp argued for a larger block size, which would reduce or eliminate the transaction backlog. Eliminating the backlog was generally considered a good thing, and the arguments were mostly around the block size, but this paper essentially came in and said "wait a minute, we might actually need the backlog once the block reward is 0."
One of the assumptions the authors make in this paper is that miners can turn their hardware on and off quickly, and that they will benefit financially for doing so. Mainly by paying lower electricity bills. It turns out the really big miners don't pay for electricity the same way you or I do. Big miners sign long term contracts for continuous consumption of energy, and don't save any money for turning mining hardware off for a short duration.
I know one of the authors personally and can try to forward on questions for anyone interested, though they have moved on from academic work so may have limited interest or context.
The paper that relies on turns out to have some inaccurate assumptions. The paper assumes that miners can flip their hardware on/off at essentially instantaneous intervals. The assume this to argue that miners will strategically turn their hardware off when the expected value of mining a block drops below the cost of power.
It turns out that many of the big miners have long term contracts with power companies to consumer power; they wouldn't save money by turning their hardware off for short periods of time. Power companies like this arrangement because it lets them predict demand better, and miners like it because they get "bulk rates" on electricity for being predictable in their consumption. The arrangement falls apart when miners turn their hardware on and off.