Getting Started With My First SQLPage PostgreSQL Connection (Windows OS)

[dbailey7]

Howdy. So, I've read through the SQLPage info on the website, trying to pay attention to the How It Works piece.

I downloaded the Zip file (Windows 11) containing the single sqlpage.exe server file. I placed the downloaded server in the SQLPage root folder I created, as instructed. I created the folder structure suggested: ...\migrations, ...\templates, et cetera. I launched the server and got the "[2023-08-27T09:22:09Z INFO sqlpage] Starting server on 0.0.0.0:8080" message expected. I opened the website locally by entering into my browser: http://localhost:8080. I got the page displayed that said: "It works ! If you can see this, then SQLPage is running correctly on your server. Congratulations!"

So, now, my question: How do I get SQLPage to run SQL queries against my PostgreSQL database? And how do I get the examples listed in (https://github.com/lovasoa/SQLpage/tree/main/examples) to run against my PostgreSQL database as well?

I currently use the PGAdmin 4 tool to run queries against my PostgreSQL database. I'm failing to see how SQLPage connects to my PostgreSQL database. I can't find the examples that show how to use the SQLPage connection parameters for PostgreSQL. They're probably right under my nose, but I'm nor grokking the process.

Any advice/assistance will be welcomed. (:>)

Cheers,
David

[lovasoa]

How to Use SQLPage with your PostgreSQL Database

Hi David, and welcome to SQLPage !

You've already successfully set up SQLPage and verified that it's running on your local machine, congratulations !

To connect SQLPage to your PostgreSQL database and run SQL queries, you can follow the instructions from the official get started guide, in the section Connect to a custom database.

Connecting SQLPage to PostgreSQL

1. Database Setup: Ensure that you have a PostgreSQL database ready. You can continue using PGAdmin 4 for this. SQLPage doesn't create databases; it connects to an existing one.

2. Configuration File: Create a sqlpage.json file in a folder named sqlpage in your website's root folder. This file will hold your PostgreSQL database connection details. You can use a text editor to create and edit this file.

   {
      "database_url": "postgres://user:password@host/database" 
   }

   Replace the placeholders:

   • user: Your PostgreSQL username.
   • password: Your PostgreSQL password.
   • host: The address of your PostgreSQL server (usually localhost for a local server).
   • database: The name of your PostgreSQL database.
     For example, if your PostgreSQL server is running locally with the default username and password yourpassword, and your database is named "mydb," your sqlpage.json file would look like this:

   {
     "database_url": "postgres://postgres:yourpassword@localhost/mydb"
   }

3. Restart SQLPage: Restart your SQLPage server to apply the new configuration. Stop the server (Ctrl+C in the terminal where SQLPage is running) and then restart it with sqlpage.exe.

Running SQL Queries

Now, let's see how to run SQL queries using SQLPage.

1. SQL Files: In your SQLPage root folder, create SQL files that contain your queries. For example, create a file named query.sql in the root folder.

     .
     ├── query.sql
     └── sqlpage
         └── sqlpage.json
   
     1 directory, 2 files
   

2. Write SQL Queries: In your SQL file, write your SQL queries. Here's an example:

     -- query.sql 
     select 'table' as component;
     SELECT * FROM mytable;`

   This SQL query retrieves data from a table named mytable.

3. Access Queries: You can access these queries by navigating to them through your browser. For example, if your SQLPage is running locally at http://localhost:8080, you can access the query you just created by going to http://localhost:8080/query.sql.

   SQLPage will execute the query against your PostgreSQL database and render the results as a web page.

Additional Examples

Most examples in the examples directory use the SQLite SQL dialect. They may need some minor syntax adaptations to run on PostgreSQL.

One exception is the user authentication example, which is written using PostgreSQL syntax and database features.

[lovasoa]

Hey David ! My real name is Ophir Lojkine.

About _sqlx_migrations: it's a normal table, it lives alongside all the other tables you create, in the same database. Here is the code that handles migrations: https://github.com/lovasoa/sqlx-oldapi/blob/ec347d59224f692105fd3282d22fc168622b6fa3/sqlx-core/src/migrate/migrator.rs

Anywhoo, how do I clear all migrations and start over from scratch (i.e., re-run them)? I thought all I had to do was 'drop' the 'users' table and that would allow the migration to be run again, but obviously that is not the case. Huh?

You can delete the _sqlx_migrations table.

[dbailey7]

Hey Ophir,

It's me, again! (:>)

I could not find the _sqlx_migrations table. I looked all through my cluster/database/schema's tables via PGAdmin 4.

The error (screenshot attached) says: "[2023-09-03T17:27:28Z ERROR sqlpage] An error occurred while running the migration.", while attempting to run it.

This is puzzling. Sorry to bug you, but what the heck do you think is going on? (:>)

Ta,
David

P.S. Cool name! What does Ophir mean? (:>) I know it might not seem so, but I really am doing a bunch to reading to get myself up to speed on SQLPage. Before this, I only used PGAdmin4 to do all of my database heavy lifting., but now I have to know more about what is going on "behind the scenes"/"under the hood", so to speak, with the database, in order to "develop"! (:>)

[lovasoa]

The message says migration 1 was previously applied but has been modified.

You changed an existing sql file in the migration folder. You should not do that. When you want to change your database schema, you should create a new migration file that describes the migration from the old schema to the new one (with ALTER TABLE for instance).

Just executing a CREATE TABLE again with a different table schema wouldn't do what you want.

Now, you can either restore the old version of your sql migration file if you still have it, or delete it completely.

[dbailey7]

"You changed an existing sql file in the migration folder."

Yes, I did! But only because I thought it was perfectly okay to do so.

"You should not do that.

Okay. I won't do this again.

"When you want to change your database schema, you should create a new migration file that describes the migration from the old schema to the new one (with ALTER TABLE for instance)."

Okay, it is insightful to know this, and it tells me better how things are working "under the hood", so to speak.

"Just executing a CREATE TABLE again with a different table schema wouldn't do what you want."

Hmmm, I was wondering why the migration wasn't working. I guess I now know why it didn't.

"Now, you can either restore the old version of your sql migration file if you still have it, or delete it completely."

That sounds like you're saying that I can drop the users table, perhaps 'archive' off the old migration file to a storage area somewhere, and then create a new users table using a new migration file. Yes?

Presuming that is right, I'll try that now. If you don't hear from me again regarding this, then that'll be a case of "No news is good news!" (:>)

Thanks again,
David

P.S. Yay! I found the _sqlx_migrations table! It was in the 'public' schema.

postgres=# \c mypgdb
You are now connected to database "mypgdb" as user "postgres".
mypgdb=# \dt
              List of relations
 Schema |       Name       | Type  |  Owner
--------+------------------+-------+----------
 public | _sqlx_migrations | table | postgres
(1 row)

I was doing everything in the 'myschema' schema so I thought it had to be there. I can now cross that question off the list!

[lovasoa]

"Now, you can either restore the old version of your sql migration file if you still have it, or delete it completely."

That sounds like you're saying that I can drop the users table, perhaps 'archive' off the old migration file to a storage area somewhere, and then create a new users table using a new migration file. Yes?

No, I was talking about the migration file. But now that you found the sqlx_migrations table, the best thing to do to start from a clean state is to remove the sqlx_migrations so that sqlpage does not remembder it has applied the migration, and write whatever migration you want.

[dbailey7]

Oh, okay. Anyway, I think I'll be all set now. -:- David Bailey ***@***.*** 207 691 4123 Cell 509 267 7789 Fax "Any sufficiently advanced technology is indistinguishable from magic." Arthur C. Clarke

…

On Mon, Sep 4, 2023 at 5:50 AM Ophir LOJKINE ***@***.***> wrote: "Now, you can either restore the old version of your sql migration file if you still have it, or delete it completely." That sounds like you're saying that I can drop the users table, perhaps 'archive' off the old migration file to a storage area somewhere, and then create a new users table using a new migration file. Yes? No, I was talking about the migration *file*. But now that you found the sqlx_migrations table, the best thing to do to start from a clean state is to remove the sqlx_migrations so that sqlpage does not remembder it has applied the migration, and write whatever migration you want. — Reply to this email directly, view it on GitHub <#73 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ADMYVMGKFE767F3W6KV4YGTXYWP4RANCNFSM6AAAAAA4AGKXKM> . You are receiving this because you authored the thread.Message ID: ***@***.***>

[dbailey7]

Hey Ophir,

Thanks for all of your help. I really, Really, REALLY appreciate it! (:>)

David

[dbailey7]

Hey Ophir, Howdy, it's me, David, again! I don't have any silly questions this time. Or even any pressing issues, really. Just looking for some "direction", is all. I've spent the time since we last communicated, building up my database application. It's now done and tested on my client-side. So, I now have a database server and database (in PostgreSQL 15) and of course my app written in PL/pgSQL, that I want to launch on smart phone platforms, targeting the IoT. What I am looking for (need) is ... Where do I find (read up on) in the documentation, on how to set these up on SQLPage in order to get started? Please point me - in the docs - to where I get this process underway. Make sense? Thank you, Buddy, David P.S. I'll be poking around on my own, but it's just that I often find myself going around in circles when I don't have a specific roadmap of where and "how" to proceed. (:>) -:- David Bailey ***@***.*** 207 691 4123 Cell 509 267 7789 Fax "Any sufficiently advanced technology is indistinguishable from magic." Arthur C. Clarke On Mon, Sep 4, 2023 at 6:04 AM David Bailey ***@***.***> wrote:

…

Oh, okay. Anyway, I think I'll be all set now. -:- David Bailey ***@***.*** 207 691 4123 Cell 509 267 7789 Fax "Any sufficiently advanced technology is indistinguishable from magic." Arthur C. Clarke On Mon, Sep 4, 2023 at 5:50 AM Ophir LOJKINE ***@***.***> wrote: > "Now, you can either restore the old version of your sql migration file > if you still have it, or delete it completely." > > That sounds like you're saying that I can drop the users table, perhaps > 'archive' off the old migration file to a storage area somewhere, and then > create a new users table using a new migration file. Yes? > > No, I was talking about the migration *file*. But now that you found the > sqlx_migrations table, the best thing to do to start from a clean state > is to remove the sqlx_migrations so that sqlpage does not remembder it > has applied the migration, and write whatever migration you want. > > — > Reply to this email directly, view it on GitHub > <#73 (reply in thread)>, > or unsubscribe > <https://github.com/notifications/unsubscribe-auth/ADMYVMGKFE767F3W6KV4YGTXYWP4RANCNFSM6AAAAAA4AGKXKM> > . > You are receiving this because you authored the thread.Message ID: > ***@***.***> >

[lovasoa]

Hi David!
I'm not entirely sure what you are looking for. The "getting started" guide is linked from SQLPage's homepage : https://sql.ophir.dev/your-first-sql-website/
If you need guidance on a specific topic that isn't covered on the official website yet, or hit something that isn't clear, ask here and I'll update the website.

[dbailey7]

Oh, okay. I'll start with the "Getting Started" guide link ( https://sql.ophir.dev/your-first-sql-website/ ) and go from there. And, yes, I'll circle back if I need more specific information. Thanks Ophir, David -:- David Bailey ***@***.*** 207 691 4123 Cell 509 267 7789 Fax "Any sufficiently advanced technology is indistinguishable from magic." Arthur C. Clarke

…

On Sun, Sep 24, 2023 at 2:02 PM Ophir LOJKINE ***@***.***> wrote: Hi David! I'm not entirely sure what you are looking for. The "getting started" guide is linked from SQLPage's homepage : https://sql.ophir.dev/your-first-sql-website/ If you need guidance on a specific topic that isn't covered on the official website yet, or hit something that isn't clear, ask here and I'll update the website. — Reply to this email directly, view it on GitHub <#73 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ADMYVMHSBBQFF7CXLUIMXFLX4BYTNANCNFSM6AAAAAA4AGKXKM> . You are receiving this because you authored the thread.Message ID: ***@***.***>

[dbailey7]

Hey Ophir, I encountered a bunch of issues on my way to getting migrations fired up. Fortunately I saved all of my notes from our earlier conversations, and got them all resolved without having to bug you again. (:>) Here's a new error that I couldn't find anywhere, though: "2023-09-25T12:34:38Z ERROR sqlpage] Unable to listen to the specified port Caused by: An attempt was made to access a socket in a way forbidden by its access permissions. (os error 10013)" Why is this error showing up? The configuration seems to be okay. Anyway, can you direct me to where I can find information on how to resolve it? Thanks, Buddy, David -:- David Bailey ***@***.*** 207 691 4123 Cell 509 267 7789 Fax "Any sufficiently advanced technology is indistinguishable from magic." Arthur C. Clarke On Sun, Sep 24, 2023 at 2:49 PM David Bailey ***@***.***> wrote:

…

Oh, okay. I'll start with the "Getting Started" guide link ( https://sql.ophir.dev/your-first-sql-website/ ) and go from there. And, yes, I'll circle back if I need more specific information. Thanks Ophir, David -:- David Bailey ***@***.*** 207 691 4123 Cell 509 267 7789 Fax "Any sufficiently advanced technology is indistinguishable from magic." Arthur C. Clarke On Sun, Sep 24, 2023 at 2:02 PM Ophir LOJKINE ***@***.***> wrote: > Hi David! > I'm not entirely sure what you are looking for. The "getting started" > guide is linked from SQLPage's homepage : > https://sql.ophir.dev/your-first-sql-website/ > If you need guidance on a specific topic that isn't covered on the > official website yet, or hit something that isn't clear, ask here and I'll > update the website. > > — > Reply to this email directly, view it on GitHub > <#73 (reply in thread)>, > or unsubscribe > <https://github.com/notifications/unsubscribe-auth/ADMYVMHSBBQFF7CXLUIMXFLX4BYTNANCNFSM6AAAAAA4AGKXKM> > . > You are receiving this because you authored the thread.Message ID: > ***@***.***> >

[dbailey7]

Uh, never mind, I restarted my desktop and reloaded the server via sqlpage.exe and the port listening error went away. I'm now able to see a pag when I fire up http:\localhost:8080 ... "Popular Websites Hello world! <https://wilipedia.org/> " Um, why this page? Where is the SQL that is generating it? Cheers, -:- David Bailey ***@***.*** 207 691 4123 Cell 509 267 7789 Fax "Any sufficiently advanced technology is indistinguishable from magic." Arthur C. Clarke On Mon, Sep 25, 2023 at 8:57 AM David Bailey ***@***.***> wrote:

…

Hey Ophir, I encountered a bunch of issues on my way to getting migrations fired up. Fortunately I saved all of my notes from our earlier conversations, and got them all resolved without having to bug you again. (:>) Here's a new error that I couldn't find anywhere, though: "2023-09-25T12:34:38Z ERROR sqlpage] Unable to listen to the specified port Caused by: An attempt was made to access a socket in a way forbidden by its access permissions. (os error 10013)" Why is this error showing up? The configuration seems to be okay. Anyway, can you direct me to where I can find information on how to resolve it? Thanks, Buddy, David -:- David Bailey ***@***.*** 207 691 4123 Cell 509 267 7789 Fax "Any sufficiently advanced technology is indistinguishable from magic." Arthur C. Clarke On Sun, Sep 24, 2023 at 2:49 PM David Bailey < ***@***.***> wrote: > Oh, okay. I'll start with the "Getting Started" guide link ( > https://sql.ophir.dev/your-first-sql-website/ ) and go from there. And, > yes, I'll circle back if I need more specific information. Thanks Ophir, > > David > > -:- > > David Bailey > ***@***.*** > > 207 691 4123 Cell > 509 267 7789 Fax > > "Any sufficiently advanced technology is indistinguishable from magic." > Arthur C. Clarke > > > On Sun, Sep 24, 2023 at 2:02 PM Ophir LOJKINE ***@***.***> > wrote: > >> Hi David! >> I'm not entirely sure what you are looking for. The "getting started" >> guide is linked from SQLPage's homepage : >> https://sql.ophir.dev/your-first-sql-website/ >> If you need guidance on a specific topic that isn't covered on the >> official website yet, or hit something that isn't clear, ask here and I'll >> update the website. >> >> — >> Reply to this email directly, view it on GitHub >> <#73 (reply in thread)>, >> or unsubscribe >> <https://github.com/notifications/unsubscribe-auth/ADMYVMHSBBQFF7CXLUIMXFLX4BYTNANCNFSM6AAAAAA4AGKXKM> >> . >> You are receiving this because you authored the thread.Message ID: >> ***@***.***> >> >

[dbailey7]

Popular Websites Hello world! <https://wilipedia.org/> Hey, upon browsing to http:\\localhost:8080, I get this "Popular Websites" page. I thought I was going to get the Success page that I got originally, not this one. So I guess I am still not understanding where the example code goes in order for it to show up on the web page. I don't have any index.sql active. There is only one migration file for create user. And there is nothing in the sqlpage\templates folder either. So, my question is where does the code live that generated this Popular Websites page come from? I thought I wrote this down before, but I guess I didn't. Rats! David -:- David Bailey ***@***.*** 207 691 4123 Cell 509 267 7789 Fax "Any sufficiently advanced technology is indistinguishable from magic." Arthur C. Clarke On Mon, Sep 25, 2023 at 9:22 AM David Bailey ***@***.***> wrote:

…

Uh, never mind, I restarted my desktop and reloaded the server via sqlpage.exe and the port listening error went away. I'm now able to see a pag when I fire up http:\localhost:8080 ... "Popular Websites Hello world! <https://wilipedia.org/> " Um, why this page? Where is the SQL that is generating it? Cheers, -:- David Bailey ***@***.*** 207 691 4123 Cell 509 267 7789 Fax "Any sufficiently advanced technology is indistinguishable from magic." Arthur C. Clarke On Mon, Sep 25, 2023 at 8:57 AM David Bailey < ***@***.***> wrote: > Hey Ophir, > > I encountered a bunch of issues on my way to getting migrations fired > up. Fortunately I saved all of my notes from our earlier conversations, > and got them all resolved without having to bug you again. (:>) > > Here's a new error that I couldn't find anywhere, though: > > "2023-09-25T12:34:38Z ERROR sqlpage] Unable to listen to the specified > port > > Caused by: > An attempt was made to access a socket in a way forbidden by its > access permissions. (os error 10013)" > > Why is this error showing up? The configuration seems to be okay. > Anyway, can you direct me to where I can find information on how to resolve > it? Thanks, Buddy, > > David > > -:- > > David Bailey > ***@***.*** > > 207 691 4123 Cell > 509 267 7789 Fax > > "Any sufficiently advanced technology is indistinguishable from magic." > Arthur C. Clarke > > > On Sun, Sep 24, 2023 at 2:49 PM David Bailey < > ***@***.***> wrote: > >> Oh, okay. I'll start with the "Getting Started" guide link ( >> https://sql.ophir.dev/your-first-sql-website/ ) and go from there. >> And, yes, I'll circle back if I need more specific information. Thanks >> Ophir, >> >> David >> >> -:- >> >> David Bailey >> ***@***.*** >> >> 207 691 4123 Cell >> 509 267 7789 Fax >> >> "Any sufficiently advanced technology is indistinguishable from magic." >> Arthur C. Clarke >> >> >> On Sun, Sep 24, 2023 at 2:02 PM Ophir LOJKINE ***@***.***> >> wrote: >> >>> Hi David! >>> I'm not entirely sure what you are looking for. The "getting started" >>> guide is linked from SQLPage's homepage : >>> https://sql.ophir.dev/your-first-sql-website/ >>> If you need guidance on a specific topic that isn't covered on the >>> official website yet, or hit something that isn't clear, ask here and I'll >>> update the website. >>> >>> — >>> Reply to this email directly, view it on GitHub >>> <#73 (reply in thread)>, >>> or unsubscribe >>> <https://github.com/notifications/unsubscribe-auth/ADMYVMHSBBQFF7CXLUIMXFLX4BYTNANCNFSM6AAAAAA4AGKXKM> >>> . >>> You are receiving this because you authored the thread.Message ID: >>> ***@***.***> >>> >>

[lovasoa]

I encountered a bunch of issues on my way to getting migrations fired up.

If you find migrations difficult, you don't have to use them. They are very useful for larger projects, but if it's just you messing around, you'll be just as fine changing your database schema directly on your postgres instance through pgAdmin.

An attempt was made to access a socket in a way forbidden by its access permissions. (os error 10013)"

This is windows telling you not to launch two instances of sqlpage with the same configuration at the same time. If you have an instance running, it is already waiting for client connections on port 8080 (by default), so if you try to launch a second instance, it will fail.

[lovasoa]

So I guess I am still not understanding where the example code goes in order for it to show upon the web page. I don't have any index.sql active.

Yes you have 🙂 The example code is not included with sqlpage, so if you see it, it comes from a file you wrote yourself. If you are using the latest version of sqlpage, look at the console, it will tell you where it is serving files from.

[dbailey7]

Well, I restarted everything and the "It works!" page displayed. I'm not sure how the Popular Websites code was "served" before, instead. Anyway, is the code for the "It works !" page in one of the Examples folders? If so, what is it called? You said "If you are using the latest version of sqlpage, look at the console, it will tell you where it is serving files from" 1. How can I tell what version of SQLPage I am running? The sqlpage.exe file I first downloaded is dated 8/25/2023. 2. Does the console output of "2023-09-25T17:30:45Z INFO sqlpage::webserver::database] Connecting to database: ***@***.***:5432/mypgdb" mean that SQLPage.exe is serving from my PostgreSQL 15 mypgdb database? 3. If I want to run a different "example", does that just mean that I need to put .sql files, one of which will be an index.sql file, from the GitHub Examples in the root folder, yes? Geesh! I thought sure I was over asking you silly questions, but ... there I go again ... THINKING! (:>) -:- David Bailey ***@***.*** 207 691 4123 Cell 509 267 7789 Fax "Any sufficiently advanced technology is indistinguishable from magic." Arthur C. Clarke

…

On Mon, Sep 25, 2023 at 1:40 PM Ophir LOJKINE ***@***.***> wrote: So I guess I am still not understanding where the example code goes in order for it to show upon the web page. I don't have any index.sql active. Yes you have 🙂 The example code is not included with sqlpage, so if you see it, it comes from a file you wrote yourself. If you are using the latest version of sqlpage, look at the console, it will tell you where it is serving files from. — Reply to this email directly, view it on GitHub <#73 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ADMYVMHBZDGTZGRPUGUDPL3X4G6ZRANCNFSM6AAAAAA4AGKXKM> . You are receiving this because you authored the thread.Message ID: ***@***.***>

[lovasoa]

Anyway, is the code for the "It works !" page in one of the Examples folders? If so, what is it called?

The code is embedded in the sqlpage binary so that everything is self-contained. The source is here: https://github.com/lovasoa/SQLpage/blob/main/index.sql

How can I tell what version of SQLPage I am running?

In recent versions this information is displayed on the default welcome page. It's also shown on hover over the default footer text.
It's also available programmatically through the sqlpage.version() function.

The release history is available here: https://github.com/lovasoa/SQLpage/releases

Does [...] Connecting to database: ***@***.***:5432/mypgdb mean that SQLPage.exe is serving from my PostgreSQL 15 mypgdb database?

No, your old version of SQLPage doesn't display the current folder in the terminal. You should upgrade.

If I want to run a different "example", does that just mean that I need to put .sql files, one of which will be an index.sql file, from the GitHub Examples in the root folder, yes?

Most of the time, yes. But be careful, each example is written in the sql dialect of a specific database. Some of them are for SQLite, others for Postgres. Read the README of each example before re-using it.

[dbailey7]

Where is(are) the "It works !" page's file(s)? -:- David Bailey ***@***.*** 207 691 4123 Cell 509 267 7789 Fax "Any sufficiently advanced technology is indistinguishable from magic." Arthur C. Clarke On Mon, Sep 25, 2023 at 4:11 PM David Bailey ***@***.***> wrote:

…

Well, I restarted everything and the "It works!" page displayed. I'm not sure how the Popular Websites code was "served" before, instead. Anyway, is the code for the "It works !" page in one of the Examples folders? If so, what is it called? You said "If you are using the latest version of sqlpage, look at the console, it will tell you where it is serving files from" 1. How can I tell what version of SQLPage I am running? The sqlpage.exe file I first downloaded is dated 8/25/2023. 2. Does the console output of "2023-09-25T17:30:45Z INFO sqlpage::webserver::database] Connecting to database: ***@***.***:5432/mypgdb" mean that SQLPage.exe is serving from my PostgreSQL 15 mypgdb database? 3. If I want to run a different "example", does that just mean that I need to put .sql files, one of which will be an index.sql file, from the GitHub Examples in the root folder, yes? Geesh! I thought sure I was over asking you silly questions, but ... there I go again ... THINKING! (:>) -:- David Bailey ***@***.*** 207 691 4123 Cell 509 267 7789 Fax "Any sufficiently advanced technology is indistinguishable from magic." Arthur C. Clarke On Mon, Sep 25, 2023 at 1:40 PM Ophir LOJKINE ***@***.***> wrote: > So I guess I am still not understanding where the example code goes in > order for it to show upon the web page. I don't have any index.sql > active. > > Yes you have 🙂 The example code is not included with sqlpage, so if you > see it, it comes from a file you wrote yourself. If you are using the > latest version of sqlpage, look at the console, it will tell you where it > is serving files from. > > — > Reply to this email directly, view it on GitHub > <#73 (comment)>, > or unsubscribe > <https://github.com/notifications/unsubscribe-auth/ADMYVMHBZDGTZGRPUGUDPL3X4G6ZRANCNFSM6AAAAAA4AGKXKM> > . > You are receiving this because you authored the thread.Message ID: > ***@***.***> >

[lovasoa]

The code is embedded in the sqlpage binary so that everything is self-contained. The source is here: https://github.com/lovasoa/SQLpage/blob/main/index.sql

[dbailey7]

Hey Ophir, I was feeling really dumb, not being able to find the "It works !" page's code! But now I know what I couldn't. Anyway, my questions seem less silly and more 'legitimate', now. Oh, the footer hover shows that I have "SQLPage v0.10.0"(By the way, can I run the sqlpage.version function from inside of PGAdmin4, or do I have to do it in an index.sql file from the root folder?). What is the current/latest version, anyway? Any hooo, thanks for the thoughtful answers/replies! David -:- David Bailey ***@***.*** 207 691 4123 Cell 509 267 7789 Fax "Any sufficiently advanced technology is indistinguishable from magic." Arthur C. Clarke

…

On Mon, Sep 25, 2023 at 5:25 PM Ophir LOJKINE ***@***.***> wrote: The code is embedded in the sqlpage binary so that everything is self-contained. The source is here: https://github.com/lovasoa/SQLpage/blob/main/index.sql — Reply to this email directly, view it on GitHub <#73 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ADMYVMA4ZN4GHWE5YPJUU7DX4HZEXANCNFSM6AAAAAA4AGKXKM> . You are receiving this because you authored the thread.Message ID: ***@***.***>

[lovasoa]

You can follow releases here: https://github.com/lovasoa/SQLpage/releases . This will always give you the latest version. You should upgrade now.
You can watch this repository to be notified of updates, too.

By the way, can I run the sqlpage.version function from inside of PGAdmin4, or do I have to do it in an index.sql file from the root folder?

PgAdmin lets you interact with the database directly, there is no way it could tell you anything about sqlpage. Functions available in sqlpage are not run on the database, but inside sqlpage, and their result is sent to the database before executing the queries. They are not visible to the database.

[dbailey7]

Dear Ophir, Kewl. Your answers/replies are always so helpful and insightful! The more I get of them, the more I understand all things "SQLPage-age!" (:>) Thank you again, my man! Cheers, David -:- David Bailey ***@***.*** 207 691 4123 Cell 509 267 7789 Fax "Any sufficiently advanced technology is indistinguishable from magic." Arthur C. Clarke

…

On Mon, Sep 25, 2023 at 6:25 PM Ophir LOJKINE ***@***.***> wrote: You can follow releases here: https://github.com/lovasoa/SQLpage/releases . This will always give you the latest version. You should upgrade now. You can watch this repository to be notified of updates, too. By the way, can I run the sqlpage.version function from inside of PGAdmin4, or do I have to do it in an index.sql file from the root folder? PgAdmin lets you interact with the database directly, there is no way it could tell you anything about sqlpage. Functions available in sqlpage are not run on the database, but inside sqlpage, and their result is sent to the database before executing the queries. They are not visible to the database. — Reply to this email directly, view it on GitHub <#73 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ADMYVMC2PZLNCDNSXXPF3LTX4IAEHANCNFSM6AAAAAA4AGKXKM> . You are receiving this because you authored the thread.Message ID: ***@***.***>

[dbailey7]

Hmmm, should I update to the Version 0.11.1 Security update release, or the Version 0.12.0-Beta release? -:- David Bailey ***@***.*** 207 691 4123 Cell 509 267 7789 Fax "Any sufficiently advanced technology is indistinguishable from magic." Arthur C. Clarke On Mon, Sep 25, 2023 at 11:40 PM David Bailey ***@***.***> wrote:

…

Dear Ophir, Kewl. Your answers/replies are always so helpful and insightful! The more I get of them, the more I understand all things "SQLPage-age!" (:>) Thank you again, my man! Cheers, David -:- David Bailey ***@***.*** 207 691 4123 Cell 509 267 7789 Fax "Any sufficiently advanced technology is indistinguishable from magic." Arthur C. Clarke On Mon, Sep 25, 2023 at 6:25 PM Ophir LOJKINE ***@***.***> wrote: > You can follow releases here: https://github.com/lovasoa/SQLpage/releases > . This will always give you the latest version. You should upgrade now. > You can watch this repository to be notified of updates, too. > > By the way, can I run the sqlpage.version function from inside of > PGAdmin4, or do I have to do it in an index.sql file from the root folder? > > PgAdmin lets you interact with the database directly, there is no way it > could tell you anything about sqlpage. Functions available in sqlpage are > not run on the database, but inside sqlpage, and their result is sent to > the database before executing the queries. They are not visible to the > database. > > — > Reply to this email directly, view it on GitHub > <#73 (reply in thread)>, > or unsubscribe > <https://github.com/notifications/unsubscribe-auth/ADMYVMC2PZLNCDNSXXPF3LTX4IAEHANCNFSM6AAAAAA4AGKXKM> > . > You are receiving this because you authored the thread.Message ID: > ***@***.***> >

[dbailey7]

Um, I decided to go with v0.11.1. (:>) Is there a SQL CREATE script for the sqlpage_files table, so I get the column's data types right? It works ! If you can see this, then SQLPage v0.11.1 is running correctly on your server. Congratulations! -:- David Bailey ***@***.*** 207 691 4123 Cell 509 267 7789 Fax "Any sufficiently advanced technology is indistinguishable from magic." Arthur C. Clarke On Mon, Sep 25, 2023 at 11:45 PM David Bailey ***@***.***> wrote:

…

Hmmm, should I update to the Version 0.11.1 Security update release, or the Version 0.12.0-Beta release? -:- David Bailey ***@***.*** 207 691 4123 Cell 509 267 7789 Fax "Any sufficiently advanced technology is indistinguishable from magic." Arthur C. Clarke On Mon, Sep 25, 2023 at 11:40 PM David Bailey < ***@***.***> wrote: > Dear Ophir, > > Kewl. Your answers/replies are always so helpful and insightful! The > more I get of them, the more I understand all things "SQLPage-age!" (:>) > > Thank you again, my man! Cheers, > > David > -:- > > David Bailey > ***@***.*** > > 207 691 4123 Cell > 509 267 7789 Fax > > "Any sufficiently advanced technology is indistinguishable from magic." > Arthur C. Clarke > > > On Mon, Sep 25, 2023 at 6:25 PM Ophir LOJKINE ***@***.***> > wrote: > >> You can follow releases here: >> https://github.com/lovasoa/SQLpage/releases . This will always give you >> the latest version. You should upgrade now. >> You can watch this repository to be notified of updates, too. >> >> By the way, can I run the sqlpage.version function from inside of >> PGAdmin4, or do I have to do it in an index.sql file from the root folder? >> >> PgAdmin lets you interact with the database directly, there is no way it >> could tell you anything about sqlpage. Functions available in sqlpage are >> not run on the database, but inside sqlpage, and their result is sent to >> the database before executing the queries. They are not visible to the >> database. >> >> — >> Reply to this email directly, view it on GitHub >> <#73 (reply in thread)>, >> or unsubscribe >> <https://github.com/notifications/unsubscribe-auth/ADMYVMC2PZLNCDNSXXPF3LTX4IAEHANCNFSM6AAAAAA4AGKXKM> >> . >> You are receiving this because you authored the thread.Message ID: >> ***@***.***> >> >

[dbailey7]

Got it ... CREATE TABLE sqlpage_files( INSERT INTO sqlpage_files(path, contents) VALUES ('unit test file.txt', 'Héllö world! 😀'); -:- David Bailey ***@***.*** 207 691 4123 Cell 509 267 7789 Fax "Any sufficiently advanced technology is indistinguishable from magic." Arthur C. Clarke On Tue, Sep 26, 2023 at 12:18 AM David Bailey ***@***.***> wrote:

…

Um, I decided to go with v0.11.1. (:>) Is there a SQL CREATE script for the sqlpage_files table, so I get the column's data types right? It works ! If you can see this, then SQLPage v0.11.1 is running correctly on your server. Congratulations! -:- David Bailey ***@***.*** 207 691 4123 Cell 509 267 7789 Fax "Any sufficiently advanced technology is indistinguishable from magic." Arthur C. Clarke On Mon, Sep 25, 2023 at 11:45 PM David Bailey < ***@***.***> wrote: > Hmmm, should I update to the Version 0.11.1 Security update release, or > the Version 0.12.0-Beta release? > > -:- > > David Bailey > ***@***.*** > > 207 691 4123 Cell > 509 267 7789 Fax > > "Any sufficiently advanced technology is indistinguishable from magic." > Arthur C. Clarke > > > On Mon, Sep 25, 2023 at 11:40 PM David Bailey < > ***@***.***> wrote: > >> Dear Ophir, >> >> Kewl. Your answers/replies are always so helpful and insightful! The >> more I get of them, the more I understand all things "SQLPage-age!" (:>) >> >> Thank you again, my man! Cheers, >> >> David >> -:- >> >> David Bailey >> ***@***.*** >> >> 207 691 4123 Cell >> 509 267 7789 Fax >> >> "Any sufficiently advanced technology is indistinguishable from magic." >> Arthur C. Clarke >> >> >> On Mon, Sep 25, 2023 at 6:25 PM Ophir LOJKINE ***@***.***> >> wrote: >> >>> You can follow releases here: >>> https://github.com/lovasoa/SQLpage/releases . This will always give >>> you the latest version. You should upgrade now. >>> You can watch this repository to be notified of updates, too. >>> >>> By the way, can I run the sqlpage.version function from inside of >>> PGAdmin4, or do I have to do it in an index.sql file from the root folder? >>> >>> PgAdmin lets you interact with the database directly, there is no way >>> it could tell you anything about sqlpage. Functions available in sqlpage >>> are not run on the database, but inside sqlpage, and their result is sent >>> to the database before executing the queries. They are not visible to the >>> database. >>> >>> — >>> Reply to this email directly, view it on GitHub >>> <#73 (reply in thread)>, >>> or unsubscribe >>> <https://github.com/notifications/unsubscribe-auth/ADMYVMC2PZLNCDNSXXPF3LTX4IAEHANCNFSM6AAAAAA4AGKXKM> >>> . >>> You are receiving this because you authored the thread.Message ID: >>> ***@***.***> >>> >>

[dbailey7]

I'm off to the races!

…

--SQLPage Test Query (sqlpagetestguery.sql) ... SELECT 'text' AS component; SELECT 'Current SQLPage Version is ''v' AS contents; SELECT sqlpage.version() AS contents, true AS code; SELECT '''' AS contents; --INSERT INTO public.test ( tst_id, tst_text) VALUES (1, 'Current SQLPage Version is ''v'); --UPDATE public.test SET tst_text = tst_text || sqlpage.version() WHERE tst_id = 1; --UPDATE public.test SET tst_text = tst_text || '''' WHERE tst_id = 1); SELECT 'text' AS component; SELECT 'Currently running from directory ''' AS contents; SELECT sqlpage.current_working_directory() AS contents, true AS code; SELECT '''' AS contents;

--INSERT INTO test (tst_id, tst_text) VALUES (2, sqlpage.version()) RETURNING 'integer-value' AS value, 'string-value' AS value; SELECT 'table' AS component; SELECT * FROM test; [image: image.png] Thanks again! Cheers, David -:- David Bailey ***@***.*** 207 691 4123 Cell 509 267 7789 Fax "Any sufficiently advanced technology is indistinguishable from magic." Arthur C. Clarke On Tue, Sep 26, 2023 at 12:22 AM David Bailey ***@***.***> wrote:

Got it ... CREATE TABLE sqlpage_files( INSERT INTO sqlpage_files(path, contents) VALUES ('unit test file.txt', 'Héllö world! 😀'); -:- David Bailey ***@***.*** 207 691 4123 Cell 509 267 7789 Fax "Any sufficiently advanced technology is indistinguishable from magic." Arthur C. Clarke On Tue, Sep 26, 2023 at 12:18 AM David Bailey < ***@***.***> wrote: > Um, I decided to go with v0.11.1. (:>) Is there a SQL CREATE script > for the sqlpage_files table, so I get the column's data types right? > It works ! > If you can see this, then SQLPage v0.11.1 is running correctly on your > server. Congratulations! > > > -:- > > David Bailey > ***@***.*** > > 207 691 4123 Cell > 509 267 7789 Fax > > "Any sufficiently advanced technology is indistinguishable from magic." > Arthur C. Clarke > > > On Mon, Sep 25, 2023 at 11:45 PM David Bailey < > ***@***.***> wrote: > >> Hmmm, should I update to the Version 0.11.1 Security update release, or >> the Version 0.12.0-Beta release? >> >> -:- >> >> David Bailey >> ***@***.*** >> >> 207 691 4123 Cell >> 509 267 7789 Fax >> >> "Any sufficiently advanced technology is indistinguishable from magic." >> Arthur C. Clarke >> >> >> On Mon, Sep 25, 2023 at 11:40 PM David Bailey < >> ***@***.***> wrote: >> >>> Dear Ophir, >>> >>> Kewl. Your answers/replies are always so helpful and insightful! The >>> more I get of them, the more I understand all things "SQLPage-age!" (:>) >>> >>> Thank you again, my man! Cheers, >>> >>> David >>> -:- >>> >>> David Bailey >>> ***@***.*** >>> >>> 207 691 4123 Cell >>> 509 267 7789 Fax >>> >>> "Any sufficiently advanced technology is indistinguishable from magic." >>> Arthur C. Clarke >>> >>> >>> On Mon, Sep 25, 2023 at 6:25 PM Ophir LOJKINE ***@***.***> >>> wrote: >>> >>>> You can follow releases here: >>>> https://github.com/lovasoa/SQLpage/releases . This will always give >>>> you the latest version. You should upgrade now. >>>> You can watch this repository to be notified of updates, too. >>>> >>>> By the way, can I run the sqlpage.version function from inside of >>>> PGAdmin4, or do I have to do it in an index.sql file from the root folder? >>>> >>>> PgAdmin lets you interact with the database directly, there is no way >>>> it could tell you anything about sqlpage. Functions available in sqlpage >>>> are not run on the database, but inside sqlpage, and their result is sent >>>> to the database before executing the queries. They are not visible to the >>>> database. >>>> >>>> — >>>> Reply to this email directly, view it on GitHub >>>> <#73 (reply in thread)>, >>>> or unsubscribe >>>> <https://github.com/notifications/unsubscribe-auth/ADMYVMC2PZLNCDNSXXPF3LTX4IAEHANCNFSM6AAAAAA4AGKXKM> >>>> . >>>> You are receiving this because you authored the thread.Message ID: >>>> ***@***.***> >>>> >>>

[dbailey7]

Hey, I'm trying to use the new set variable feature in the v0.12.1-beta version. I attempted the example code: ```sql -- Set a variableSET person = 'Alice';-- Use it in a querySELECT 'text' AS component, 'Hello ' || $person AS contents; ``` But I got an error: [image: image.png] Also, when I ran the sqlpage.version() function against what I THOUGHT was the beta version, it reported ... [image: image.png] 'Sup wit dis?

[lovasoa]

Can you please write directly on github, and without signing your messages each time ? #73

This conversation is public, and it would be nice if it were more readable to others.
Your email formatting appears quite messed up here, and images do not appear.

[dbailey7]

Mea culpa! I'm all set ... [image: image.png] -:- David Bailey ***@***.*** 207 691 4123 Cell 509 267 7789 Fax "Any sufficiently advanced technology is indistinguishable from magic." Arthur C. Clarke On Wed, Sep 27, 2023 at 1:36 PM David Bailey ***@***.***> wrote:

…

Hey, I'm trying to use the new set variable feature in the v0.12.1-beta version. I attempted the example code: -- Set a variableSET person = 'Alice';-- Use it in a querySELECT 'text' AS component, 'Hello ' || $person AS contents; But I got an error: [image: image.png] Also, when I ran the sqlpage.version() function against what I THOUGHT was the beta version, it reported ... [image: image.png] 'Sup wit dis? -:- David Bailey ***@***.*** 207 691 4123 Cell 509 267 7789 Fax "Any sufficiently advanced technology is indistinguishable from magic." Arthur C. Clarke On Tue, Sep 26, 2023 at 7:02 AM David Bailey < ***@***.***> wrote: > I'm off to the races! > > --SQLPage Test Query (sqlpagetestguery.sql) ... > > SELECT 'text' AS component; > SELECT 'Current SQLPage Version is ''v' AS contents; > SELECT sqlpage.version() AS contents, true AS code; > SELECT '''' AS contents; > --INSERT INTO public.test ( tst_id, tst_text) VALUES (1, 'Current SQLPage > Version is ''v'); > --UPDATE public.test SET tst_text = tst_text || sqlpage.version() WHERE > tst_id = 1; > --UPDATE public.test SET tst_text = tst_text || '''' WHERE tst_id = 1); > > > SELECT 'text' AS component; > SELECT 'Currently running from directory ''' AS contents; > SELECT sqlpage.current_working_directory() AS contents, true AS code; > SELECT '''' AS contents; > > --INSERT INTO test (tst_id, tst_text) VALUES (2, sqlpage.version()) > RETURNING 'integer-value' AS value, 'string-value' AS value; > > SELECT 'table' AS component; > SELECT * FROM test; > > [image: image.png] > Thanks again! Cheers, > > David > > -:- > > David Bailey > ***@***.*** > > 207 691 4123 Cell > 509 267 7789 Fax > > "Any sufficiently advanced technology is indistinguishable from magic." > Arthur C. Clarke > > > On Tue, Sep 26, 2023 at 12:22 AM David Bailey < > ***@***.***> wrote: > >> Got it ... >> >> CREATE TABLE sqlpage_files( >> INSERT INTO sqlpage_files(path, contents) VALUES ('unit test file.txt', >> 'Héllö world! 😀'); >> >> >> -:- >> >> David Bailey >> ***@***.*** >> >> 207 691 4123 Cell >> 509 267 7789 Fax >> >> "Any sufficiently advanced technology is indistinguishable from magic." >> Arthur C. Clarke >> >> >> On Tue, Sep 26, 2023 at 12:18 AM David Bailey < >> ***@***.***> wrote: >> >>> Um, I decided to go with v0.11.1. (:>) Is there a SQL CREATE script >>> for the sqlpage_files table, so I get the column's data types right? >>> It works ! >>> If you can see this, then SQLPage v0.11.1 is running correctly on your >>> server. Congratulations! >>> >>> >>> -:- >>> >>> David Bailey >>> ***@***.*** >>> >>> 207 691 4123 Cell >>> 509 267 7789 Fax >>> >>> "Any sufficiently advanced technology is indistinguishable from magic." >>> Arthur C. Clarke >>> >>> >>> On Mon, Sep 25, 2023 at 11:45 PM David Bailey < >>> ***@***.***> wrote: >>> >>>> Hmmm, should I update to the Version 0.11.1 Security update release, >>>> or the Version 0.12.0-Beta release? >>>> >>>> -:- >>>> >>>> David Bailey >>>> ***@***.*** >>>> >>>> 207 691 4123 Cell >>>> 509 267 7789 Fax >>>> >>>> "Any sufficiently advanced technology is indistinguishable from magic." >>>> Arthur C. Clarke >>>> >>>> >>>> On Mon, Sep 25, 2023 at 11:40 PM David Bailey < >>>> ***@***.***> wrote: >>>> >>>>> Dear Ophir, >>>>> >>>>> Kewl. Your answers/replies are always so helpful and insightful! >>>>> The more I get of them, the more I understand all things "SQLPage-age!" >>>>> (:>) >>>>> >>>>> Thank you again, my man! Cheers, >>>>> >>>>> David >>>>> -:- >>>>> >>>>> David Bailey >>>>> ***@***.*** >>>>> >>>>> 207 691 4123 Cell >>>>> 509 267 7789 Fax >>>>> >>>>> "Any sufficiently advanced technology is indistinguishable from >>>>> magic." >>>>> Arthur C. Clarke >>>>> >>>>> >>>>> On Mon, Sep 25, 2023 at 6:25 PM Ophir LOJKINE < >>>>> ***@***.***> wrote: >>>>> >>>>>> You can follow releases here: >>>>>> https://github.com/lovasoa/SQLpage/releases . This will always give >>>>>> you the latest version. You should upgrade now. >>>>>> You can watch this repository to be notified of updates, too. >>>>>> >>>>>> By the way, can I run the sqlpage.version function from inside of >>>>>> PGAdmin4, or do I have to do it in an index.sql file from the root folder? >>>>>> >>>>>> PgAdmin lets you interact with the database directly, there is no >>>>>> way it could tell you anything about sqlpage. Functions available in >>>>>> sqlpage are not run on the database, but inside sqlpage, and their result >>>>>> is sent to the database before executing the queries. They are not visible >>>>>> to the database. >>>>>> >>>>>> — >>>>>> Reply to this email directly, view it on GitHub >>>>>> <#73 (reply in thread)>, >>>>>> or unsubscribe >>>>>> <https://github.com/notifications/unsubscribe-auth/ADMYVMC2PZLNCDNSXXPF3LTX4IAEHANCNFSM6AAAAAA4AGKXKM> >>>>>> . >>>>>> You are receiving this because you authored the thread.Message ID: >>>>>> ***@***.***> >>>>>> >>>>>

[lovasoa]

We still don't see the screenshots here

[dbailey7]

Going forward, I shall do so. I thought I was using to the correct venue. And I'll also adhere to the appropriate formatting standards so that any code examples are formatted appropriatly and my screen shots actually show up. I didn't know that they weren't. Oh, what link shoudl I use for my communications to the general community, instead of my hooking everything onto that originil email I sent to you? Anyway, my apologies. -:- David Bailey ***@***.*** 207 691 4123 Cell 509 267 7789 Fax "Any sufficiently advanced technology is indistinguishable from magic." Arthur C. Clarke

…

On Wed, Sep 27, 2023 at 2:36 PM Ophir LOJKINE ***@***.***> wrote: Can you please write directly on github, and without signing your messages each time ? #73 <#73> This conversation is public, and it would be nice if it were more readable to others. Your email formatting appears quite messed up here. — Reply to this email directly, view it on GitHub <#73 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ADMYVMCDUFNHUUTD6JPG7JTX4RW2ZANCNFSM6AAAAAA4AGKXKM> . You are receiving this because you authored the thread.Message ID: ***@***.***>

[dbailey7]

Great! Will do. Thank you.

[dbailey7]

Does SQLPage allow/support
DO ... END;
blocks?

[dbailey7]

Oh, and does SQLPage allow user-defined functions?

[lovasoa]

Hmmm, I need to construct a game board that looks like a rectangle of squares, where each square can be highlighted and then read/inputted from and/or written into.

For custom user interface components like that, you can create your own SQLPage components just by writing a .handlebars file in sqlpage/templates. Have a look at the example. https://github.com/lovasoa/SQLpage/tree/main/examples/using%20react%20and%20other%20custom%20scripts%20and%20styles

Does SQLPage support [...]

SQLPage just forwards your sql to your database. It still has to be able to parse the sql. If something doesn't work, open an issue.

[dbailey7]

Ahh, "handlebars"! Okay, I shall proceed to some 'delve-age' into same. (:>)

Well, if I look at my 'learning curve' for SQLPage, I'm probably about here ...

(:>)

Anyway, thanks.

[dbailey7]

Oh, and does SQLPage allow user-defined functions?

Yep, I 'discovered; that yesterday when I got a complex query to run!

[lovasoa]

What is it you are building exactly?

[dbailey7]

Oh, one thing: I wasn't trying to be a "jerk" with my response. That is, if using a simpler, or non-React, approach can fulfill all of my UI needs, then I would certainly be amenable to going that simpler route. For example, if the graphical components I need can be constructed with just SQLPage and some simple JS, CSS and HTML, without the need for the React JS library, then I would not insist on using React just to be using it. Especially since I don't know a heck of a lot about it, anyway. (:>)

Anyway, now that I have a Proof of Concept (POC) done by making a grid that will form the basis of a rudimentary game board, my next step will be a modification to make a realistic-looking version of it, and then to get it to display via SQLPage. Make sense? Cheers,

David

[lovasoa]

No problem, you never were a jerk 😉 I was simply under the impression that you are learning a lot of different things at the same time, and it may be more productive to process with a more "top-down" approach: first model your sql data schema, then make the queries you need, then display the results with whatever existing SQLPage component is the least awkward for it, then make a simple custom template to display it visually precisely the way you want, then add client-side interactivity in JavaScript if you need it, and only then, if you need complex ui manipulation in JavaScript, introduce react.

That would allow you to get familiar with each piece of the puzzle before getting to the next, and you would have a working game you can iterate on faster.

But of course, this is just a suggestion, if you feel more comfortable with something else, no problem!

[dbailey7]

Bingo! This is EXACTLY the kind of suggestion/advice I am looking for! And, yes, I DO have a bunch of 'irons in the fire" learning-wise. Hopefully managing this process won't get too ugly. So, it's onward and upward, my Brother! (:>)

[dbailey7]

Hey Ophir,

Howdy. It's me again! I took a little time off to get 'other work' done, but I'm back to pick up where I left off.

I'm happy to report that I ran all of the Examples/Demos (except for the "make a geographic data application using sqlite extensions" one, which specifically targeted SQLite's SpatiaLite application), and got most of them to work.

Happily, I remembered most of what I learned about the SQLPage Web Application Framework earlier, so I had not too much difficulty handling the few issues that cam up for the nine (9) examples that I got working (screenshots).

There were four (4) that I didn't get to work, however (screenshots).

09-modeling-a-many-to-many-relationship-with-a-form_Errors.txt


10-master-detail-forms_Errors.txt


11-charts-computations-and-custom-components_Errors.txt


13-splitwise_Errors.txt

I'm running PostgreSQL 15 and so I'm looking to get them running on that database. I'm grappling with the "modeling-a-many-to-many-relationship-with-a-form" example now. At first (after firing up SQLPage.exe), I got no errors:

D:\SQLPage_Root_Folder_One\Ho Dis\Examples Folder\09-modeling-a-many-to-many-relationship-with-a-form>sqlpage
[2023-10-22T00:43:33Z INFO sqlpage::webserver::database] Connecting to database: postgres://postgres:fojkolmipqvixyz@localhost:5432/mypgdb
[2023-10-22T00:43:34Z INFO sqlpage::webserver::database] Applying migrations from 'D:\SQLPage_Root_Folder_One\Ho Dis\Examples Folder\09-modeling-a-many-to-many-relationship-with-a-form\sqlpage/migrations'
[2023-10-22T00:43:34Z INFO sqlpage::webserver::database] Found 3 migrations:
[2023-10-22T00:43:34Z INFO sqlpage::webserver::database] [0000] Simple schema
[2023-10-22T00:43:34Z INFO sqlpage::webserver::database] [0001] Simple sample data
[2023-10-22T00:43:34Z INFO sqlpage::webserver::database] [0002] Simple sqlpage shell
[2023-10-22T00:43:34Z WARN sqlx::query] -- A simple blog …; rows affected: 0, rows returned: 0, elapsed: 286.884ms

-- A simple blog with topics and posts
-- Users can create topics and posts. A topic can have many posts and a post can have many topics.
-- The first step is to create the tables in the database. We will use the following SQL queries:
CREATE TABLE topic (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  icon TEXT NOT NULL,
  UNIQUE (name)
);
CREATE TABLE post (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  content TEXT NOT NULL,
  -- The contents will be stored in markdown
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  main_topic_id INTEGER REFERENCES topic(id)
);
CREATE TABLE topic_post (
  topic_id INTEGER NOT NULL REFERENCES topic(id),
  post_id INTEGER NOT NULL REFERENCES post(id),
  PRIMARY KEY (topic_id, post_id),
  FOREIGN KEY (topic_id) REFERENCES topic(id),
  FOREIGN KEY (post_id) REFERENCES post(id)
);
-- A view of the topics with the number of posts and most recent post
CREATE VIEW topic_with_stats AS
SELECT
  topic.id,
  topic.name,
  topic.icon,
  COALESCE(count(topic_post.post_id), 0) as nb_posts,
  max(post.created_at) as last_post
FROM
  topic
  LEFT JOIN topic_post ON topic_post.topic_id = topic.id
  LEFT JOIN post ON post.id = topic_post.post_id
GROUP BY
  topic.id
ORDER BY
  nb_posts DESC;

[2023-10-22T00:43:34Z INFO sqlpage] Starting server on 0.0.0.0:8080
[2023-10-22T00:43:34Z INFO actix_server::builder] starting 16 workers
[2023-10-22T00:43:34Z INFO actix_server::server] Actix runtime found; starting in Actix runtime
[2023-10-22T00:43:34Z INFO sqlpage] Server started successfully.
SQLPage is now running on http://127.0.0.1:8080/
You can write your website's code in .sql files in D:\SQLPage_Root_Folder_One\Ho Dis\Examples Folder\09-modeling-a-many-to-many-relationship-with-a-form.

But when I launched the Home Page, I got errors: Error in query number 2:

Failed to prepare SQL statement: SELECT post.title AS title, post.sql || post.id AS link, ;Published on || created_at AS description, CASE WHEN created_at ...

Yada, yada, yada!

How do I fix this? Thanks,

[lovasoa]

Hello and welcome back to SQLPage!

Some of the examples use SQLite-specific SQL syntax, so you have to run them on SQLite.

However, you can reuse the principles and components they demonstrate in other databases.

[dbailey7]

"Some of the examples use SQLite-specific SQL syntax, so you have to run them on SQLite"

Uh, yeah. but what do you think about converting "those" to PostgreSQL? Not the "make a geographic data application using sqlite extensions" one, because it specifically targets "SpacialLite", so that might not have the same functionality in the PostgreSQL domain. But what about these four (4):

• modeling-a-many-to-many-relationship-with-a-form
• master-detail-forms
• charts-computations-and-custom-components
• splitwise

The many-to-many paradigm is common to ALL databases, for instance. Anyway, you could "steer" me and I'd be happy to "drive" the work! (:>)

What say you?

David

P.S. I kinda started to do this for "modeling-a-many-to-many-relationship-with-a-form" already:

For example, for its "index.sql" file ...

Error before conversion"

"ERROR:  syntax error at or near "$"
LINE 12: WHERE $topic IS NULL
               ^ 

SQL state: 42601
Character: 366"

Then

SELECT 'list' as component, COALESCE((SELECT name FROM topic WHERE id = $topic

becomes

SELECT 'list' as component,
    COALESCE(
        (SELECT name FROM topic WHERE id = (SELECT COALESCE(MAX(id), 0) + 1 FROM post)),
        'Recent blog posts'
    ) as title;

and

CASE
        WHEN created_at > date('now', '-2 days') THEN 'red'
        ELSE NULL
    END as color,

becomes

    CASE
        WHEN created_at > current_date - interval '2 days' THEN 'red'
        ELSE NULL
    END as color,

Then the code runs without error:

[dbailey7]

Oh. Okay! Your rationale makes a lot of sense. I'll stick to SQLite for the examples and master the underlying SQLPage concepts therein. And, yeah, you're right: I had a taste of converting the SQLite code over to PostgreSQL and it wasn't pretty! (:>)

I don't need to spin my wheels in PostgreSQL just for the heck of it when there is no real need to do so, because I can glean what is important using the bundled SQLlite, and have something that "just works" without a lot of "kicking and screaming". (:>)

As usual, thanks for your thoughtful response, Buddy! Cheers,

David

[dbailey7]

OL,

Um, how do I fix the following error? I'm using the SQLite json file and (I guess) the in-memory version of SQLite3. By the way, SQLite3 is installed and in the Windows 11 PATH. I had issues installing the Spacial Lite tools (for the SQLite extension: mod_spatialite?) though. Any ideas? Please and thank you,

DB

D:\SQLPage_Root_Folder_One\Ho Dis\Examples Folder\14-make-a-geographic-data-application-using-sqlite-extensions>sqlpage
[2023-10-23T21:25:26Z INFO  sqlpage::app_config] No DATABASE_URL, using the default sqlite database './sqlpage.db'
[2023-10-23T21:25:26Z INFO  sqlpage::webserver::database] Loading SQLite extension: mod_spatialite
[2023-10-23T21:25:26Z INFO  sqlpage::webserver::database] Connecting to database: sqlite://sqlpage.db
[2023-10-23T21:25:26Z WARN  sqlpage::webserver::database] Failed to connect to the database: error returned from database: (code: 0) **The specified module could not be found**.
    . Retrying in 5 seconds.
[2023-10-23T21:25:56Z ERROR sqlpage] Unable to open connection to sqlite://sqlpage.db

Caused by:
    0: error returned from database: (code: 0) The specified module could not be found.
    1: (code: 0) The specified module could not be found.

D:\SQLPage_Root_Folder_One\Ho Dis\Examples Folder\14-make-a-geographic-data-application-using-sqlite-extensions>

[lovasoa]

I'm sorry, I don't use windows, I won't be able to help with that. But since the application you are building had nothing to do with manipulating geographical data, you probably shouldn't spend too much time setting up this example; it won't help you for what you are doing.

[dbailey7]

Okey dokey. Guess I'm on my own, on this one, then. (:>)

I "think" what the error is telling me that I didn't install Spacial Lite correctly. There isn't too much advice on doing that for Windows 11. I'll spend "a little" time trying to get the installation done properly, though - what with me being so stubborn, and all! (:>)

I'll let you know if I'm successful.

Thanks, anyway,

DB

[dbailey7]

OL,

All done! (:>)

The remaining four (4) examples run fine ...

0-master-detail-forms_terminal](https://github.com/lovasoa/SQLpage/assets/14256816/b2f78a0f-c4ac-4d6a-83da-5f9979155ca6)e remaining four (4)

The SpaciaLite one, as well ...

The steps to get the SpaciaLite extension loaded for this last one on Windows are:

1. Download (https://www.sqlite.org/download.html) and install SQLite3 and stash its files in a folder (D:\Installs\sqlite3)
2. Add the SQLite3 folder (D:\Installs\sqlite3) to the Windows PATH system environment variable
3. Download the SpaciaLite binaries (mod_spatialite-5.1.0-win-amd64.7z) from the SINS download page (http://www.gaia-gis.it/gaia-sins/windows-bin-amd64)
4. Stash those, including the extension (mod_spatialite.dll) in the folder (D:\Installs\sqlite3)), as well
5. Create/set a Windows System Envirohment variable (SPACIALITE_PATH) for SpaciaLite (SPACIALITE_PATH = 'D:\mod-spacialite'
6. Test it with a Python script:
   D:>python
   Python 3.11.5 (tags/v3.11.5:cce6ba9, Aug 24 2023, 14:38:34) [MSC v.1936 64 bit (AMD64)] on win32
   Type "help", "copyright", "credits" or "license" for more information.

import sqlite3
try:
... # Connect to DB
... db = sqlite3.connect(':memory:')
... db.enable_load_extension(True)
... db.execute("select load_extension('mod_spatialite')")
... print('mod_spatialite Extension loaded')
... #Handle errors
... except sqlite3.Error as err:
... print('Error occurred loading extension: ', err)
... # Close DB Connection regardless of success or failure
... finally:
... if db:
... db.close()
... print('SQLite Connection closed')
...
<sqlite3.Cursor object at 0x000002B3A7233440>
mod_spatialite Extension loaded
SQLite Connection closed

7. Voila!

Um, just in case anyone asks you how to do this for Windows 11. (:>)

Cheers,

DB

[dbailey7]

OL,

Small broken link typo:

In the Official Website page (https://sql.ophir.dev/),

the link for the "Tutorial" button: "http://localhost:8080/get%20started.sql"

doesn't work. 'I believe' it should be underscore '_' between the words
'get' and 'started', instead of space ' '.

Let me know if I am wrong. (:>)

Ta,
DB

[lovasoa]

It works for me: https://sql.ophir.dev/get%20started.sql

[dbailey7]

Oh, I went in from the GitHub "Examples" link: https://github.com/lovasoa/SQLpage/tree/main/examples/official-site. Then I must have somehow selected the wrong "Get Started" code; there are two of them ...

Launching the page directly via "http://localhost:8080/your-first-sql-website/" does work. Who knows what the heck I did to fail to bring up that page earlier? (:>)

[lovasoa]

Both simply redirect to /your-first-sql-website/