SQLPage
Nick, regarding fastht.ml - you are moving too fast :). I decided to open a new topic about SQLPage. I'm playing with an invoicing application and I'm trying to display a message to confirm the deletion of a record. In index.sql I have the piece of code below :
select
'alert' as component,
'Delete document!' as title,
'analyze' as icon,
'teal' as color,
TRUE as dismissible,
'Do you want to delete the document?' as description where $delete is not null;
select
'delete.sql?delete='||$delete||'&confirm=OK' as link,
'Delete document' as title where $delete is not null;
select
'index.sql' as link,
'Cancel' as title,
'secondary' as color where $delete is not null;
and in delete.sql I have
delete doc,doc_d from doc left join doc_d on doc.id=doc_d.id_doc where $confirm="OK" and doc.id=$delete
select 'redirect' AS component, 'index.sql' AS link where $confirm="OK" and $delete is not null;
This was the only functional option I found.
I wonder if it's ok with that redirect. Practically, every time I delete a record, it reloads my entire index.sql page (where I have a table with all the invoices issued - if there are a few dozen or hundreds it wouldn't be a problem, but if there were a few hundred thousand?). Would there be a way to reload only one component from the index.sql page (as ajax used to do)?
I also wonder how I could generate links that send the parameters via POST, not via GET (now I have a table component and a markdown field in which I generate link '[🗑️](?delete=' || doc.id | |')' AS delete )
posted by: iontab 3-Sep-2024/1:50:12-7:00
SQLPage is a simple web server architecture - not meant to be used with AJAX by default, although you could certainly write custom components to send AJAX requests. But using the default simple configuration in SQLPage, it's probably better to paginate, as in the example at:
http://server.py-thon.com:8008/contacts.sql
http://server.py-thon.com:8008/contacts.txt
That example also demonstrates sending POST requests as hidden form values, but I haven't tried anything but GET requests for those sorts of interactions that are expected to be GET requests.
If you're trying to keep GET request URLs from showing up in the URL bar, you could also use cookies as described in the section about authorization and session variables...
posted by: Nick 3-Sep-2024/11:32:29-7:00
BTW, the table component is really snappy (instant on a basic new netbook with Windows 11), displaying up to around 2000 rows, and still very usable up to 10,000ish rows.
posted by: Nick 3-Sep-2024/11:34:10-7:00
Also, there is a delete confirmation routine in that contacts app example.
For anything else that isn't accomplished easily by building a custom SQLPage component (AJAX features included), I'd build whatever little piece of an app is needed, using any other frontend or backend tool that's appropriate (for most simple needs, I'd choose Flask, Bootstrap, SQLAlchemy), and pop it into a SQLPage iframe - using SQLPage to deliver an API that the external app connects to.
This example includes a pile of different architectures on the same page:
http://server.py-thon.com:8008/iframe.sql
For example, the Contacts app on the bottom uses AJAX to update the database values in the grid, in real time, without having to click any save buttons, but there's also a Streamlit app, and NiceGUI app, and an Anvil app all embedded, along with some straightforward Flask/Bootstrap/SQLAlchemy apps.
posted by: Nick 3-Sep-2024/17:59:20-7:00
Hmmm, making a custom SQLPage component from Tabulator, Aggrid, etc. might be a good project to take on...
posted by: Nick 3-Sep-2024/19:00:30-7:00
I'm really curious how far I can push SQLPage. It's basically meant to provide a server based presentation layer to SQL queries - not meant to do AJAX, but it's performant enough that AJAX shouldn't be necessary for most of the sorts of things it excels at. I expect that pairing it with calls to Python (either with sqlpage.exec() or with APIs), and building some more custom components will push its usefulness for production data management apps pretty darn far, with extremely quick and productive work patterns. I'm really looking forward to getting more development-adjacent professionals working on projects with it, tying in APIs and executable code where needed.
Perhaps I'll look at integrating HTMX into some SQLPage custom components next - that could be a cool way to add simple dynamic front end capabilities (of course, that's the whole basis of FastHTML... :)
posted by: Nick 3-Sep-2024/20:42:20-7:00
I think it's interesting that a new framework is getting back to the basics of old-school server rendering, with full page refresh upon each submission. It's definitely a simpler development process, and I think if an underlying architecture goal is to make the server perform very fast, it can work really effectively. Fast performance is certainly one of SQLPage's core goals:
https://sql.datapage.app/performance.sql
In fact, this might be similar to how Kaj is currently approaching web UI with Meta (I haven't looked at his forum or any other Meta web app code, but he mentioned that Meta web UI currently relies on CGI).
The idea of performing really fast full page refreshes - if it's actually handled blazingly fast - should eliminate the need for a huge portion of the AJAX, JS, etc. (even HTMX) mess, with maybe just a little bit of rethinking about how interfaces need to be designed...
posted by: Nick 4-Sep-2024/10:47:56-7:00
It's so easy to add new components to SQLPage, that once the routine is worked out, it might be practical to simply create a customized grid component (i.e., based on Tabulator, Aggrid, etc.) for each project ... and/or any other custom component that simplifies the need for refreshing full pages.
posted by: Nick 4-Sep-2024/10:53:51-7:00
Of course the real benefit of customized SQLPage components is to simplify the population of customized 'repeating panel' widgets with rows of data returned from a SQL query, you can use them to deliver just about anything, including JS based components that have configuration values populated by a query result...
posted by: Nick 4-Sep-2024/10:58:49-7:00
Also, keep in mind, I still tend to think about using an ORM in Python (SQLAlchemy, pyDAL, Pony, etc.), along with API endpoints delivered by Flask, FastAPI, etc., but SQLPage eliminates the need for all those pieces. We should be able to connect a datagrid delivered as a custom component in a SQLPage layout, directly to an API endpoint made available by the same SQLPage server :)
posted by: Nick 4-Sep-2024/11:03:45-7:00
And of course, if you run into any problems delivering a third party front end framework piece as a custom component, you can always simply build whatever you want to build in a standalone HMTL file, connect that to an API written in SQLPage, serve the HTML page with SQLPage, and then include that in an iframe in a SQLPage app. Those words sound complicated, but it's a really simple and elegant way to include unlimited functionality, with just about *any front-end or back-end technology you prefer:
http://server.py-thon.com:8008/hello_world.html
http://server.py-thon.com:8008/hello_world_iframe.sql
posted by: Nick 4-Sep-2024/11:16:30-7:00
Include SQLPage in an Anvil app (of course this could be formatted to fit the Anvil layout better):
https://sqlpage.anvil.app
posted by: Nick 4-Sep-2024/11:39:58-7:00
This prompted me to add a section to the tutorial, which covers this:
http://server.py-thon.com:8008/message_api.sql
http://server.py-thon.com:8008/bootstrap_message_table.html
http://server.py-thon.com:8008/bootstrap_message_table_iframe.sql
The JS fetch API code gets JSON returned by the SQLPage API. It uses AJAX, so the values could easily be refreshed without refreshing the rest of the SQLPage layout. The Bootstrap code could of course be replaced by *any other front-end framework technology.
Those 3 pieces form a fully self-contained app, served entirely by SQLPage, with data returned solely by a SQLPage API. No other programming languages (Python, PHP, Java, etc.), ORMs (SQLAlchemy, pyDAL, Pony, etc.), or any other API server framework (Flask, FastAPI, etc.) are required on the back end. In this way, SQLPage can take the place of all those other complex architecture pieces, and integrate beautifully with any front-end tooling of your choice.
posted by: Nick 4-Sep-2024/13:00:55-7:00
https://learnsqlpage.com/sqlpage_quickstart.html#section-11
posted by: Nick 4-Sep-2024/13:30:30-7:00
BTW, that Bootstrap code was generated by GPT in a few seconds, just by providing the URL of the API and some example output - this stuff is all becoming child's play :)
posted by: Nick 4-Sep-2024/13:32:34-7:00
This is what's really attracting me to SQLPage - the database work stays the database work, in SQL. There are all sorts of great hi-level interfaces to that - initially looking at FastHTML, for example, there's a nice little integrated tool set for building up a SQLite database if one doesn't exist - but... that's just another level of tooling. SQLPage starts with the SQL, which has decades of maturity, performance, usefulness, entrenched popularity, etc., baked in. The SQL users I work with can build their schema, built their queries, and *easily build a UI to deliver some CRUD and API functionality, etc. Then when they need to improve something about the UI which isn't so easy with the built-ins, I can build them a custom component or some other UI that they can integrate immediately, without ever having to get out of their SQL comfort zone (no other programming languages, frameworks, ORMs, or other tooling mess). And the same is true for the back end. They can call an API or some local code with sqlpage.exec(), and be on their merry way. Or they can go so far as just embedding an entire app, written in a totally different tool set, which connects with their database, and integrate just by sharing a database.
The built-in components in SQLPage are super usable and effective for a wide range of cases, and extending it in any way is about as easy as could be hoped for. I hope the Fetch/Bootstrap example above clarifies a bit about how that's working so far for me.
posted by: Nick 4-Sep-2024/16:19:55-7:00
Here are links to the code for each piece of the Bootstrap table example:
http://server.py-thon.com:8008/message_api.sql
http://server.py-thon.com:8008/message_api.txt
http://server.py-thon.com:8008/bootstrap_message_table.html
http://server.py-thon.com:8008/bootstrap_message_table.txt
http://server.py-thon.com:8008/bootstrap_message_table_iframe.sql
http://server.py-thon.com:8008/bootstrap_message_table_iframe.txt
Notice how simple the SQLPage code is :)
posted by: Nick 4-Sep-2024/16:52:35-7:00
BTW, here's the original forum app, so you can see changes update in the Bootstrap example:
http://server.py-thon.com:8008/forum.sql
posted by: Nick 4-Sep-2024/16:57:29-7:00
I made a super compact version of a super simple full CRUD example (10 lines):
http://server.py-thon.com:8008/tightest.txt
http://server.py-thon.com:8008/tightest.sql
posted by: Nick 7-Sep-2024/23:54:08-7:00
It's so short, here's the code:
CREATE TABLE IF NOT EXISTS p (i INTEGER PRIMARY KEY, x TEXT, y TEXT);
INSERT INTO p (x, y) SELECT :X, :Y WHERE $e IS NULL AND :X IS NOT NULL;
UPDATE p SET x = :X, y = :Y WHERE i = $e AND :X IS NOT NULL;
DELETE FROM p WHERE i = $d;
SELECT 'form' AS component;
SELECT (SELECT x FROM p WHERE i = $e) AS value, 'X' AS name;
SELECT (SELECT y FROM p WHERE i = $e) AS value, 'Y' AS name;
SELECT 'button' as component; SELECT '?' as link, 'New' as title;
SELECT 'table' AS component, 'X' AS markdown, '' AS markdown, TRUE AS sort;
SELECT i AS I, '['||x||'](?e='||i||')' AS X, y AS Y, '[X](?d='||i||')' AS '' FROM p;
-- db-table p, db-fields i x y, form-values :X :Y, UI-columns I X Y E '', ?query-params $e, $d
posted by: Nick 7-Sep-2024/23:55:54-7:00
Even simpler 8 line version:
CREATE TABLE IF NOT EXISTS t (i INTEGER PRIMARY KEY, a TEXT);
INSERT INTO t (a) SELECT :A WHERE $e IS NULL AND :A IS NOT NULL;
UPDATE t SET a = :A WHERE i = $e AND :A IS NOT NULL;
DELETE FROM t WHERE i = $d;
SELECT 'button' as component; SELECT '?' as link, 'New' as title;
SELECT 'form' AS component; SELECT (SELECT a FROM t WHERE i = $e) AS value, 'A' AS name;
SELECT 'table' AS component, 'A' AS markdown, 'X' AS markdown, TRUE AS sort;
SELECT '['||a||'](?e='||i||')' AS A, '['||i||'](?d='||i||')' AS X FROM t;
http://server.py-thon.com:8008/tightest.txt
http://server.py-thon.com:8008/tightest.sql
The example above is now:
http://server.py-thon.com:8008/tightest.txt
http://server.py-thon.com:8008/tightest.sql
posted by: Nick 8-Sep-2024/9:34:36-7:00
Oops, the example above is now:
http://server.py-thon.com:8008/tight.txt
http://server.py-thon.com:8008/tight.sql
posted by: Nick 8-Sep-2024/9:35:21-7:00
That competes in minimalism with this Streamlit/Dataset example:
import streamlit as st, dataset
db=dataset.connect('sqlite:///db'); t=db['user']
if st.button('Add Row'): t.insert(dict(x='',y=''))
for r in t:
with st.expander(r['x']):
with st.form(str(r['id'])):
x=st.text_input('Name', r['x'])
y=st.text_area('Note', r['y'])
if st.form_submit_button('Save'):
t.update(dict(x=x,y=y,id=r['id']),['id'])
if st.form_submit_button('Delete'):
t.delete(id=r['id']); st.experimental_rerun()
http://server.py-thon.com:8501
posted by: Nick 8-Sep-2024/9:39:22-7:00
Both the SQLPage and the Dataset example create and/or connect to sqlite database files, but Dataset only enables a small number of basic interactions with the database. SQLPage can implement *any SQL code that a database system enables, and it requires only a single file server setup, which can run unlimited code files, server other files is far more extensible than Streamlit, performs much faster, and includes API capabilities & lots of extra content such as 5000+ built-in icons, etc. Streamlit and Dataset both require Python and a number of libraries to be installed, each Streamlit code file needs a separate server instance to run (on a separate port), it's got a bit of an odd way of dealing with UI refreshes, it's not as easily extensible, doesn't have an API layer built in, and it performs much more slowly. Streamlit has its place for data visualization, but it's not as powerful, performant, or enjoyable to use as SQLPage (for a general purpose web development framework in Python, FastHTML is the thing to watch).
posted by: Nick 8-Sep-2024/9:53:10-7:00
I made a quick little video demonstrating how to install and run SQLPage apps on inexpensive Linux VPS hosting:
https://www.youtube.com/watch?v=9h2Qg2mgVIY
posted by: Nick 8-Sep-2024/20:42:18-7:00
I made another more basic introductory video about SQLPage:
https://youtu.be/ZOCmY_PrC1Q?feature=shared
posted by: Nick 10-Sep-2024/10:44:58-7:00
The little intro document that goes along with that is at:
http://216.137.179.125:8008/sqlpage_intro.txt
posted by: Nick 10-Sep-2024/11:12:55-7:00
Quick Intro, displayed as aSQLPage app:
http://216.137.179.125:8008/sqlpage_intro.sql
posted by: Nick 12-Sep-2024/11:00:20-7:00
I made all the 7 apps below with 27 total lines of SQLPage code:
CRUD
http://216.137.179.125:8008/1line.sql
http://216.137.179.125:8008/items.sql
http://216.137.179.125:8008/table.sql
Call APIs
http://216.137.179.125:8008/api.sql
http://216.137.179.125:8008/map_search.sql
Serve an API
http://216.137.179.125:8008/messages_api.sql
Call 1 line of Python code (could be any language/complexity)
http://216.137.179.125:8008/word_count_python.sql
SQLPage intro & code explanation: https://tinyurl.com/mr3wc7a9
posted by: Nick 15-Sep-2024/8:44:32-7:00
https://tinyurl.com/yckwcdmt
posted by: Nick 15-Sep-2024/8:46:39-7:00
I updated it to 9 apps in 29 lines:
https://learnsqlpage.com/29lines.html
https://learnsqlpage.com/29lines.txt
Those 1-liners really remind me of the early Rebol days!
posted by: Nick 15-Sep-2024/14:11:25-7:00
There's honestly a massive amount of useful capability demonstrated in those 29 lines: all the most typical UIs (forms, sortable tables/datagrids, charts, markdown layouts, etc.) and CRUD database interactions, integration of JSON to/from web APIs, integration of data with 3rd party back-end language code, etc.
posted by: Nick 15-Sep-2024/14:30:04-7:00
The idea with those 9 examples is that 29 lines should not take anyone too much time to understand completely.
The Quick Intro is also updated:
https://tinyurl.com/mr3wc7a9
posted by: Nick 15-Sep-2024/14:32:43-7:00
I was very happy to see today that the default SQLPage UI components render usably even in Dillo and OffbyOne browsers. Even those tiny browsers, without any JS, can be used to deliver powerful database app UIs with SQLPage, and of course the back end can connect and integrate with code written in any programming language, process and integrate web API responses, deliver data at web API endpoints, and for data management, you get build-in all the power of SQL in whatever common RDBMS you choose. Third party programming languages could be anything: Python, Rebol, Meta, Julia, etc. I love this tool :)
posted by: Nick 18-Sep-2024/18:27:23-7:00
Just went down a little rabbit hole looking at Dillo for DOS :P
posted by: Nick 19-Sep-2024/18:03:16-7:00
Is there a web browser for the Atari? If so, I wonder how well default SQLPage component output would do...
posted by: Nick 19-Sep-2024/20:56:50-7:00
Here are the current SQLPage demo examples I'm handing out at http://learnsqlpage.com/intro.html:
The 10 apps below total 29 lines of SQLPage code:
CRUD
(source) | http://216.137.179.125:8008/1line.sql
(source) | http://216.137.179.125:8008/chart.sql
(source) | http://216.137.179.125:8008/blog.sql
(source) | http://216.137.179.125:8008/items.sql
(source) | http://216.137.179.125:8008/table.sql
(source) | http://216.137.179.125:8008/layout.sql
INTEGRATE 3RD PARTY WEB APIs
(source) | http://216.137.179.125:8008/api.sql
(source) | http://216.137.179.125:8008/map_search.sql
SERVE A WEB API
(source) | http://216.137.179.125:8008/messages_api.sql
INTEGRATE PYTHON CODE (could be any language)
(source) |
http://216.137.179.125:8008/word_count_python.sql | (count.py)
Anyone can quickly learn how to use the 29 lines of code above, and SQLPage takes less than a minute to install. SQLPage integrates directly with SQL code, in any common RDBMS, to generate web based UIs and APIs populated by SQL query results. No other web frameworks, language ecosystem tools, or software development layers are required for SQL users, so the complexity of building web apps is dramatically reduced. In addition to serving data through UI and web API interfaces, SQLPage can request data from 3rd party APIs, integrate directly with back-end code written in any other programming language, and incorporate new custom UI components built with any web framework. Bootstrap and Tabler (with all its components & 5000+ icons) are built in. Every SQLPage task is accomplished entirely with SQL code.
INSTALL INSTRUCTIONS AND RESOURCES
http://216.137.179.125:8008/sqlpage_intro.sql
posted by: Nick 19-Sep-2024/20:59:50-7:00
The QuickStart is now the default recommended tutorial:
https://learnsqlpage.com/sqlpage_quickstart.html
posted by: Nick 19-Sep-2024/21:19:24-7:00
I haven't even tried actually using dillo for maybe 2 decades. This message is posted with Dillo. Boy, an Internet without JS can be pretty useful ;) The layout is nice, and it's crazy fast. Prior to trying to use SQLPage, I'd thought the earliest potentially useful browser was QtWeb, but only the oldest libraries (Bootstrap 3.x, jslinb 1.4, etc.) would work with it, XUI for AJAX, plain very old school JS, etc., and it even QtWeb 3.8.2 was 7.5Mb. It's great to get away from JS completely and get back to server rendered content. A little 1Mb browser (uncompressed) that runs even in DOS suddenly is all you need.
posted by: Nick 20-Sep-2024/1:15:31-7:00
To be clear, I'm using the version of Dillo called D+, which might be the most recently released binary:
https://sourceforge.net/projects/dplus-browser/files/Releases/dplus-0.5b/
posted by: Nick 20-Sep-2024/1:25:46-7:00
I experimented with running SQLPage apps in pywebview:
import webview
webview.create_window("Example", "http://216.137.179.125:8008/sqlpage_intro.sql")
webview.start()
That just needs:
pip install webview
And this code, for example, injects a back button into any page navigated to in the webview (in case any navigated pages don't include navigation (such as third party links that might take a use away from the SQLPage app)):
import webview
# Function to inject a back button into the current page
def inject_back_button(window):
js_code = """
if (!document.getElementById('backButton')) {
let backButton = document.createElement('button');
backButton.innerText = 'Go Back';
backButton.id = 'backButton';
backButton.style.position = 'fixed';
backButton.style.top = '10px';
backButton.style.left = '10px';
backButton.style.padding = '10px';
backButton.style.backgroundColor = '#4CAF50';
backButton.style.color = 'white';
backButton.style.border = 'none';
backButton.style.cursor = 'pointer';
backButton.onclick = function() {
window.history.back();
};
document.body.appendChild(backButton);
}
"""
window.evaluate_js(js_code)
if __name__ == '__main__':
# Create the pywebview window with the target website or local HTML file
window = webview.create_window('My WebView', 'http://216.137.179.125:8008/sqlpage_intro.sql')
# Set the loaded event to re-inject the back button after every page load
window.events.loaded += inject_back_button
# Start the pywebview event loop
webview.start()
I also ran Anvil apps in pywebview, and they do well :)
You can also use KivyMD's WebView for Android and iOS apps, and there are a wide variety of other options, but SQLPage is so small and simple to package right along with embeddable Python, for example, that that forms an a fully extensible UI, database, and API framework with full Python support. Pretty cool stuff!
posted by: Nick 27-Sep-2024/12:15:13-7:00
|