Leopard: using Leo as a DB and website

[Edward K. Ream]

​I called my brother Speed yesterday to find out more about how he used Leo as a database. I had kinda given up on him ever writing up his ideas, so I offered to be his scribe while he described his work to me. At first he said that he would like to describe his work some other time, but you know how it is, once he got started he was hooked :-) I peppered him with questions make sure I knew how it all worked.

I sent Speed a first draft of this post, and he responded with copious additions and corrections. He said, "Thanks for the help getting my thoughts in order on this subject. Here are my comments. BTW, all the code examples below are from memory, so there could be all kinds of typos or other details that are screwed up."

I may have made some mistakes folding Speed's comments into my first draft. All comments, questions and corrections are welcome.

There are four parts of his approach: the idea, using .leo files as db's, scripts and procedures. Each part is straightforward. It looks like a work of genius.

The idea: DAG's as Databases

Speed was familiar with the Associative model of data, pioneered by Simon Williams, the founder of lazysoft. A bit of googling reveals that the product is still called sentences. The Wayback machine contains this article, and there are other links in the wikipedia article.

In the Associative Model, databases are just DAG's (Directed Acyclic Graphs), which is what a .leo file is.

According to Speed, the sentences DB never gained traction because it didn't support SQL.

My first draft said, "Speed figure out how to support SQL in Sentences." Speed corrected me: "I didn't really figure out how to get Sentences DB to comply with the SQL syntax. I did the opposite. I took a DAG and organized it in such a way that it would be easy to use the organization as a data source for the SQL syntax. In the process I saw how beautifully an SQL database can be made to render in Leo."

Using .leo files as a DB

A single .leo file could represent one or more DB's. Any node can be the root of (a particular) DB. "Opening" a db (with a given name) means finding the node with the given name.

The direct children of the root node correspond to the tables of the db. The headline of each table node is the table's name.

The direct children of each table correspond to columns of the table. The headline of each column node is the column's name.

The direct children of each column describe the column. Order matters:

- Child 1: The contents (body?) contains metadata.
- Child 2: The contents (body?) contains the column type. This is anything that a SQL DB accepts for a type of column.
- Child 3: The contents (body?) can be a link, or actual contents. The metadata node tells whether the contents is a link or actual data.

Speed says this is a standard DB organization. He didn't invent this format!

That's it! That's the format of the DB!

Missing scripts

Important: The descriptions in the next two sections are a bit vague because Speed has not yet located the scripts. There is still hope, though. He has two other dead computers to search :-) As you will see, this is not a catastrophe. The scripts are simple enough in concept that recreating them would be relatively easy. It also means that Leonistas could make their own scripts following the patterns to be described next.

leo_database.py handles SQL queries

A single script file, leo_database.py, implements a simple subset of the SQL language. It works something like this:

leo_database.py takes command-line arguments that give the DB (.leo file and root node) and a query file (a text file) containing SQL queries, one per line.

leo_database.py opens the .leo file using the Leo bridge. The commander returned from the bridge is the data source.

leo_database.py then reads and handles the queries, one at a time. The script understands at least the following SQL commands:
 
Create database:

  CREATE DATABASE MyDatabase

Define table:

  CREATE TABLE MyTable

Set a database as active for following commands:

  USE MyDatabase

Get data from a table in a database:

  SELECT birthday, outstanding_balance FROM MyTable WHERE user_id = "Harry Potter"

Alter data in a table:

  UPDATE MyTable SET birthday = "Jan 1, 2020", outstanding_balance = $10000000000.00 WHERE WHERE user_id = "Harry Potter"

Delete table:

  DROP TABLE MyTable

Delete database:

  DROP DATABASE MyDatabase

And that's it!

SQL syntax is pretty simple, and leo_database needs only to handle a simple subset of SQL, so parsing the query file is straightforward.

Further comments from Speed

In actual practice, I created the database tables and initial data values by simply editing the Leo file. I never used the "CREATE DATABASE" OR "CREATE TABLE" commands.

Many times I would edit the leopard.leo on my local machine, then ftp it up to the web site. Let's say I added a new user and password to the comments section on the web site. I'd just open the Leo file, add the new data to the Members table, then ftp the leo file to the web site. That's it.

Likewise, if I had to alter the structure of a table, I just did it in Leo. This is way simpler and faster than using phpMyAdmin.

Leo as a better phpMyAdmin

phpMyAdmin is a web-based front end for DB's. php.admin gives the graphical view of all db's on a web site.

Well, the .leo file containing the DB is already the graphical view!

leowapp.py (leo --gui=browser) allows you to edit any .leo file (any DB) on a web site. In other words, leo --gui=browser is the same php.admin.

Even better, the .leo file supports clones, something php.admin doesn't. So leowapp.py (or the .leo file itself) is an infinitely flexible version of php.admin!

Serving web pages: Leopard (Leo Python automated response daemon)

Speed used clones in leopard.leo file to generate the web site. Before leowapp, he manually uploaded leopard.leo file using ftp. With leowapp (leo --gui=browser), there is no need to use ftp.

The leopard.py script serves web pages as follows:

When a user visits a page, the apache server called leopard.py with the name of the page. leopard.py does the following:

1. Open the db file with the leoBridge.
2. Use the commander to get data.
3. Print to stdout for apache server, which creates the web page.

More comments from Speed re running a website with Leopard

The following are from Speed's verbatim comment on my first draft. I don't understand them in detail. I'm trusting they will make sense to you. If not, please let me know, and I'll relay questions to Speed.

The websites I had (using Leopard) had one php file named index.php.

The reasons for using index.php to invoke leopard.py are too many to go into here. Suffice it to say, it is soooooo much simple to do it this way if your Apache server is already set up with php.

The Apache server .htaccess file is set up so that all calls to the web site, no matter what the URL, go to index.php. This is dirt simple to do.

The php file simply calls leopard.py. The Apache server has already set up everything so that inside of leopard.py we can get at all the data we need about what url the user has entered, any form data, cookies, etc.

This data is available as environmental variables, or as file system data, so no command line arguments are needed for leopard.py.

leopard.py figures out what page the user wants (plus form data, cookies, etc.). Leopard.py then calls leo_database.py with the command line arguments which are the SQL COMMAND to be performed.

When I used this, I hard coded the database name into leo_database.py so I never had to use the USE statement. For example:

- The user goes to: www.LeoIsWonderfull.com/GiveMeFreeMoney.html

- Apache sends this request, along with any cookies, form data, etc. to index.php.

- Index.php calls ./cgi-bin/leopard.py:

  $web_page = shell_exec("/var/www/cgi-bin/leopard.py")

- leopard.py figures out if it needs any data from the database to output the page. If so, it can executes this script:

cmd = [
 "/usr/bin/python3",
 "leo_database.py",
 "UPDATE MyTable, SET birthday = \"Jan 1, 2020\",
 outstanding_balance = $10000000000.00 WHERE user_id = \"Harry Potter\""
]
cwd = "\var\www\cgi-bin"
proc = Popen(cmd, cwd=cwd, stdout=subprocess.PIPE, stderr=subprocess.PIPE)

out, err = proc.communicate()

In this case, out is the string output from leo_database.py, which is typically the actual SQL response string that one would expect from any SQL query. Because we are using subprocess, and are capturing both standard out and standard error, nothing is output to Apache's cgi environment or yet returned to php.

- leopard.py then takes this data and processes it into a web page. When done, it simply does a "print" of the web page contents:

  print(my_web_page_data)

- php's shell_exec() then gets this data into $web_page. index.php then does any final cleanup (if any) and outputs the page to the server. Perhaps just:

  echo $web_page

In the simplest case, index.php is as follows. The first non-php line can be very important depending on your Apache setup.

<!doctype=html>
<?php
$web_page = shell_exec("/var/www/cgi-bin/leopard.py");
echo $web_page;
?>

To be lightning fast, in some cases I set up a separate web.py server on localhost that was always running. In these cases, the server on port, say 54000, would establish the leo bridge connection to leopard.leo, then leave it up and running forever in its own separate process separate from Apache, but running on the same Linux server.

In such cases, leopard.py would not use the command line or Popen to call leo_database.py. Instead, leo_database.py resides as the same python code, almost identically, but residing in web.py's code.py.

In this case, leopard.py does a direct socket connection to port localhost:54000, using an http GET request as follows:

http://localhost:5400/database?query="'UPDATE MyTable SET birthday = "Jan 1, 2020", outstanding_balance = $10000000000.00 WHERE WHERE user_id = "Harry Potter"'"

Now, the part after the ?query= would be html escaped using the standard python html escape routines.

The web.py server would get this request and access leopard.leo using its leo bridge connection.

Once web.py gets the database response from leopard.leo, it returns the response to leopard.py.

Leopard.py then processes the data as explained above.

BTW, many times the leopard.leo database would contain entire web pages, in html, in the 'Pages' table. So leopard.py had nothing to do but print the results of the SQL query.

And, these pages could make use of all of Leo's outlining and cloning thingies. So a table entry in the Pages table could be an entire subtree containing clones, @others and << my_section >> sections.

To change the copyright section on all 1000 html pages, just one clone had to be changed, then the leo file uploaded via ftp to the web site.

Summary

Many thanks to Speed for providing all these details.

This is a simple, flexible framework for using DB's and serving web pages.

1. Each .leo file is one or more databases.
2. leo_database.py fulfills SQL queries.
3. leopard.py serves web pages using .leo files.
4. A single .leo file can contain an entire website.

The leo_database.py and leopard.py scripts are conceptually simple. Even if Speed's debugged versions are lost to entropy, they should be easy enough to recreate or customize to your taste.

Edward

[jkn]

Interesting stuff, thanks for this. I shall have to read more about the 'Associative Model of Data'.

Couple of quick corrections etc. on the posting:

1) you have two definitions for 'USE MyDatabase' in the section "leo_database.py handles SQL queries'

2a) so does each 'table' node necessarily have exactly three children? Would be worth saying so is so

2b) Does the Child1 metadata consist only of information as to whether the Child3 body is data, or a link? If so, would be worth saying.

    Thanks

    Jon N

Select a database to work on:

  USE MyDatabase

[Edward K. Ream]

On Mon, Sep 21, 2020 at 3:05 AM jkn <jkn...@nicorp.f9.co.uk> wrote:

Interesting stuff, thanks for this. I shall have to read more about the 'Associative Model of Data'.

That might be fun. I took a quick look years ago.

However, there is no great need to do so. Speed's insight is that using Leo as a DB is a whole lot easier than doing a "real" DB. No need for all the "abilities" (Reliability, Availability, Serviceability, Serviceability, Scalability, etc) that a real DB must obsess over. Just back up your .leo file one in a while :-)

Couple of quick corrections etc. on the posting:

1) you have two definitions for 'USE MyDatabase' in the section "leo_database.py handles SQL queries'

Thanks. Just at present I am not able to edit the post.

2a) so does each 'table' node necessarily have exactly three children? Would be worth saying so is so

I don't know for sure. Presumably, that depends on Speed's scripts. I might have used another way, say with @meta, @column and @contents nodes.

2b) Does the Child1 metadata consist only of information as to whether the Child3 body is data, or a link? If so, would be worth saying.

Again, I don't know. Again presumably, the answer is that the data is similar to whatever the "standard" way describing DB's is.  I don't know what that standard way is, but Speed said there is such a way, and it uses 3-tuples.

Speed is still looking for the missing scripts. I hope he can find them. The scripts contain the implicit answers to your questions. If Speed can't find them, I think a project to recreate them would be in order. We can then choose for ourselves how we answer your questions.

Edward

[k-hen]

Cool!