General thoughts about the project and code readability

[pchemguy]

I kind of like the whole concept of this project simply because I need a quick prototype application serving as GUI front end to my database of components/vendors/orders/assemblies and so on. At present, I already write SQL anyway (even though I also use general-purpose DB managers). Modern DB managers are pretty powerful, but are not always optimal for day-to-day routine use. For now, I develop/use this database myself, but I also want to be able to share it later, if necessary, with people who are not used to either DB managers or SQL, so a low-code form-based GUI is a good thing.

I tried to look for other options, and decided to try this project for a few reasons. On the one hand, the application I need is centered around presenting/editing structured and semi-structured data stored in an SQL database, On the other hand, I want a lightweight but flexible information manager and could not find anything satisfying among the ready-to-use options. Being able to easily run it on Windows is also an important advantage for me. SQLite is optimal for my needs (as opposed to server/client databases), so full support of SQLite is a must for me as well.

At the same time, SQL is a domain-specific language, so this unorthodox application of SQL does require some stretching of the language, so to speak, and introduction of unusual interpretation of the code to stay within [mostly] plain SQL. It might be tricky at first grasp this interpretation. Still, even recursive CTEs is a fairly convoluted feature, which extends SQL in somewhat similar fashion, making it possible to have a limited analog of for/while loops while again staying mostly within the conventional SQL realm.

In addition to required special interpretation of the code, it appears to be very difficult to navigate any sizeable code. A well-formatted html is much more readable, as it has inherent structure. An SQLpage code is just a wall of SQL. I understand that the project is very young, but I think it is important to figure out and suggest approaches to writing manageable code. The first thing I thought of is that it is particularly important to have a lot of comments here and using comments to structure the code. Splitting larger files and using run_sql might also help (and this is where the thing about having "pre-authorized" hashed files might come in handy in terms of added security).

[lovasoa]

I understand that the project is very young, but I think it is important to figure out and suggest approaches to writing manageable code.

The project is 2 years old which you may not consider young by tech software standards.
There are multiple large sqlpage codebases running in production.

You are right that we should do more in tutorials and examples to instill good practices of code organisation.
There is no silver bullet, but the gist of it is:

• structure your application into multiple small pages rather than a few large ones
• use run_sql for the website header
• use database views whenever possible to prevent your requests to get too large
• for code organization, here is what I prefer:
  • leave a blank line between each component
  • add a comment whenever necessary

[pchemguy]

I accidentally learned about the standalone SET statement. I anticipated that SQLpage would not like SQL code inside its custom functions, but decided to verify it. And the error message referred to the SET statement. Such a statement is not available in SQLite, which also does not have stored procedures, and I did not see it in the docs. But this statement is a really big deal. Basically, even in the code targeting SQLite, one can define, assign, and use variables. And I understand that the variable can be assigned the result of an arbitrary SELECT, so either a scalar for a single value or a JSON containing string for multiple values. I mean, CTEs make a huge difference for developing and reusing SQL code. But with SET/variables, I can start implementing separation of concerns. You should definitely make sure this feature is readily available in the docs.

And it would be nice to see references to big installation of the project.