real webdev

Honestly, we're needing to look at groupings of information knowledge. It's not just masters in statistics and math and database development, it's connecting these to an interface (web, phone), and then securing them.

We can also look at our own life in terms of a database, the way various government agencies probably do. If we view ourselves and other citizens as entities, attributes, and relationships. In the case of events or incidents, each one one of these might be an entity, eg a car accident, which is a singular event.

formal

Masters in statistics, math, computer science, just to get into other classes.

Is discrete math the intersection of statistics and maths?

database

terminologies, relational, normalization,

database normalization (28:34) Decomplexify, 2022. simple examples and repairs on 1-5nf in RDBMS. columns (attributes), rows (entities). Design normalization can protect against things that cannot be logically true like two dates of birth. Eliminates data that disagrees with itself, and as a side benefit, redundant data.10:20 2nf deletion, update & insertion anomalies. 3nf 17:30 transitive dependency. 19:00 golden rule of boyce-codd.
relational terminology (8:48)Neso Academy, 2022. terminlogy for RDBMS.

sets of information

cobol, php (and whatever replaces it -- can it be done with python?)

local machine (localhost) LAPP, email

Both of these must be entered for the monitor to remain on during inactivity. Neither command by itself is enough to keep the monitor alive.

$ xset -dpms
$ xset s noblank

With these two entered, the screen will still be receiving a signal, but it's just for the backlight, not for any display conent. If we want the display content to remain during inactivity, we must do the two above AND add the following.

$ xset s off

Do we even need this project? Re the LAPP: "no, but helpful". Re the email: probably "yes, for understanding dashboard alerts". LAPP or any other monolithic CMS (XAMP, LAMP) that require learning PHP might be a waste if we can chain cloud services and so on (eg. read comments under this video).

Since LAPP elements are servers, they typically require user switching, individual configuration, or other permission issues. A separate post will deal with production configuration like that. I wrote this one aiming for a localhost light development environment (besides Docker). Additionally, I've attempted to view each LAPP element independently, in case we learn of an app that requires only one element, eg the PHP server, or just a relational database. I also subbed out the Apache "A" for another "L", lighthttp: LLPP. More commonly though, even browser based apps (eg Nextcloud - go to about 8:30) still use a CMS LAMP, LAPP, LNPP, etc. Electron, Go, Docker, won't be covered here.

LAPP

Linux (L)

For both LAPP and email, verifying /etc/hosts is properly configured for IPv4 and 6 for localhost smoothness. Otherwise we typically already login as some user, so we shouldn't have permission issues if everything else is well configured.

PostgreSQL (P)

# pacman -S postgreqsl
# pacman -Rsn postgresql

Lunatic designers really really really don't want a person running this on their own system under their own username. There's no security advantage to this, just poor design. This is almost more difficult than the design of the database itself.

After installing with pacman, add one's username to the postgres group in /etc/group, then initialize the database. Use...

$ initdb -D /home/foo/postgres/data

...instead of the default:

$ initdb -D /var/lib/postgres/data

Attempt startup.

$ postgres -D '/home/foo/postgres/data'

If starting-up gives the following error...

FATAL: could not create lock file "/run/postgresql/.s.PGSQL.5432.lock": No such file or directory

...run the obvious

# mkdir /run/postgresql
# chown foo:foo /run/postgresql

Just do the above, don't believe anything about editing service files or the postgresql.conf file in /data. None of it works. Don't even try to run it as a daemon or service. Just start it like this:

$ postgres -D '/home/foo/postgres/data'

Now that the server is up and running, you can open another terminal and run psql commands. If you run as user postgres (see below), you'll have admin authority.

$ psql postgres

If a lot of data has been written, a good exit phrase to make sure all is safely written:

$ psql -c CHECKPOINT && pg_ctl stop -m fast

PHP (P)

Link: CLI flags ::

# pacman -S php php-pgsql
# pacman -Rsn php php-pgsql

PHP server setup (8:04) Dani Crossing, 2015. Old, but not outdated.
php.ini basics (7:25) Program With Gio, 2021.

If we've got a dynamic website, we typically need PHP (server) and then some JavScript(browser/client) to help display it. The less JavaScript the better (slows browser). I consulted the Arch PHP Wiki .

standalone PHP - start/stop

Standalone PHP is easier to troubleshoot than when starting Apache simultaneously. Stopping is CTRL-C in the terminal where it was started. Startup with web-typical TCP port 80, however leads to a permission issue.

$ php -S localhost:80
[Fri Oct 23 11:20:04 2020] Failed to listen on localhost:80 (reason: Permission denied)

Port 80 works when used with an HTTP server (Apache, NGINX, etc), but not standalone PHP (not sure why). So, use any other port, eg port 8000, and it works.

$ php -S localhost:8000
[Fri Oct 23 11:20:04 2020] PHP 8.0.12 Development Server (http://127.0.0.1:8000) started

See this to determine when best to use index.html, or index.php. But there are at least 3 ways for PHP to locate the index.html or index.php file:

1. before starting the server, CD to the folder where the servable files are located
2. specify the servable directory in the startup command

   $ php -S 127.0.0.1:8000 -t /home/foo/HTML

   $ php -S localhost:8000 -t ~/HTML

3. edit /etc/php/php.ini to indicate file locations...

   # nano /etc/php/php.ini
   doc_root = "/home/foo/HTML"

   ... however this strategy can lead to mental illness and/or lost weekends: sometimes the ini file will not be parsed. Good luck.

First, take a breath. then verify which ini file is being used.

$ php -i |grep php\.ini
Configuration File (php.ini) Path => /etc/php
Loaded Configuration File => /etc/php/php.ini

If you have modified the correct ini file, hours and hours of finding the correct syntax for

standalone PHP - configuration and files

Links: PHP webserver documentation :: Arch PHP configuration subsection

PHP helpfully allows us to configure a document root, so I can keep all html files (including index.htm) inside my home directory. The open_basedir variable inside the configuration file (/etc/php/php.ini) is like a PATH command for PHP to find files. Also, when pacman installs PHP dependent programs like phpwebadmin or nextcloud it default links them to /etc/webapps, because this is a default place PHP tries to find them. Even though they are installed into /usr/share/webapps. So if I had a folder named "HTML" inside my home directory, I'd want to at least:

# nano /etc/php/php.ini
open_basedir = /srv/http/:/var/www/:/home/foo/HTML/:/tmp/

email local

Link: simple setup :: another setup :: notmuch MTA setup

$ mkdir .mail

We also need a mail server on the machine but which only sends to localhost and then only to one folder in /home/foo/.mail/ inside localhost. However, instead of setting up local email alerts, why not skip all of that (for now), and run a log analysis program like nagios?

We want to configure the lightest localhost system setup for email that we can read on a browser. Once we can reliably do alerts on, eg. systemd timers, or some consolidating log app (eg,Fluentd), or some Python/Bash script triggers, other things are possible. Our simplest model takes timer script outputs and sends email to the local system (out via SMTP port 25).

CLI suite

Linux has some default mail settings: mail to root is kept at /var/mail/root, user typically in ~/mail. It appears we'll need a webserver, no small email systems have this built-in. Appears Alot can be our MUA. It is available on the repositories, and can be configured with other light localhost services as described here.

cli configuration

browser MUA

Once the above configured, we can use a browser MUA. For Chromium, the Chrome store, has an extension for notmuch, called Open Email Client. configuration information is provided.

database options

Right tool for the right job. An easy case might be a password database, essentially a flat file database, Excel 2-D type of thing. A more difficult case is a video edit. You've got clips, which might expand in number, you've got recipes or methods for various transitions and combinations, and you've got an overall project view which pulls from these. Whatever we have we'd like it to be ACID compliant. Try to use umlet or some such to do the UML.

7 Database Paradigms (9:52) Fireship, 2020. Seven different ways of looking at data, with suggested applications.
issues univ of utah (19:49) Mark Durham, 2018. Has a great chart.

relational algebra, calculus

Relational Algebra is just a form of logical operators (most are shown below), easy to understand for those who've taken logic courses.

Linguistically, Relational Algebra imparted its first name, "relational", to databases constructed around its principles, ergo "relational databases". In other words, relational databases are databases built around Relational Algebra.

Relational Algebra itself was created by Edgar Codd. His main book on this is The Relational Model for Database Management, and his Turing Award paper on relational algebra (1981) is available online (PDF). The most important skill level to reach is the capacity for fast database normalizing. This takes practice, similar to the logic skill for taking statements, text or otherwise, and symbolizing.

Relational Algebra Lecture 1 (50:21) Database Psu, 2015. Penn State. The first in their amazing free online course. Lecture begins about 19:12.
Relational Algebra Pt 3 lecture (51:18) Database Psu, 2015. The various operators and their uses.

Relational calculus is a way of making queries using relational algebra to get what we want from our queries. It's less atomic than relational algebra.

relational keys (primary, foreign, composite, blank)

Link: postgres constraints ::

Relationals are a bit of a pain. See my later post on how to configure PostgreSQL for local access. Restrictions make searches easier. The main idea is a unique column (Cust ID), can then access a unique row in the next table. In the pic below, the primary is green, the foreign red. A column with a "references constraint" on it is a foreign key.

Primary and foreign keys (2:08) Udacity, 2015. Primary and foreign keys explained. Foreign key is column with references constraint. No lookup table is used, but it might be possible by indexing somehow.
Primary and foreign keys (5:24) Prescott Computer Guy, 2011. This has an intermediate lookup table. He wants every table to have an ID column, to be sure to have a unique value for that table.
Postgres version (5:42) Trent Jones, 2020. Unfortunately uses PgAdmin not DBeaver.
SQL from teaching perspective (10:12) Mike Dane, 2017. Exam configuration

• primary key: Postgres/SQL constraint equivalent - NOTNULL, UNIQUE
• foreign key: Postgres/SQL constraint equivalent - none. Verifies that a column from keyed table always has a value in another table (is linked).

constraints
PostgreSQL	MySQL
PRIMARY KEY	PRIMARY KEY
FOREIGN KEY	FOREIGN KEY
UNIQUE (column)	UNIQUE
NOT NULL/NULL (null allowed)	NOT NULL
CHECK	CHECK
DEFAULT	DEFAULT
CREATE INDEX	CREATE INDEX
EXCLUDE

start-up/shutdown (postgresql)

See my other post for how to configure as user. That done, it's a pretty simple....

$ postgres -D '/home/foo/postgres/data'

With the server is up and running, you can open another terminal and run psql commands. If you run as user postgres (see below), you'll have admin authority.

$ psql postgres

If a lot of data has been written, a good exit phrase to make sure all is safely written:

$ psql -c CHECKPOINT && pg_ctl stop -m fast

Apache memory, SQLite backup and restore

Running one's (simple) personal-use HTML pages sometimes requires more than a browser, and so you gotta make a little sandbox. Reasons for posts here: 1) Little sandboxes nevertheless require Apache and PHP giants -- they must be minimized or they appropriate significant RAM and CPU desired for normal ops. 2) SQLite is useful, but should be backed up. How?

SQLite backup and restore

From this stack overflow page... "The safe way to backup is to run:

$ sqlite3 my_database.sq3

and then:

$ .backup backup_file.sq3

Result is copy of the database. It's different from regularly file copying, because it takes care of any users currently working on the database. There are proper locks set on the database, so the backup is done exclusively."

That's the proper way, but with one user (no worries about locks), simpler ways are available.

initialization

Apache: Main file httpd.conf. Problem: as noted at top, Apache combined with PHP can sip RAM incrementally. Serverfault.com has pages of helpful forum question answers for a fine-grained approach. One particularly helpful overview is here.

In general for a sandbox, you only need a couple of connections. I set MaxClients (MaxRequestWorkers since Apache 2.4) to "5".

Another thing is to be sure to enable

postgresql - user level install

Want to set-up a relational database and have browser access through localhost on our local machine. If successful, we can next learn cloud install or connect clusters. DBeaver is installed as our GUI admin tool. PgAdmin is good when running but often spawns lib errors. Regardless, the install portions of administration are CLI.

installation steps (2 hrs)

1. server (1 hour)

An entirely initalized PostgreSQL cluster has two parts, a database and a server. The server acts like Apache and manages connections. We start with that portion first, the databases need to be created from within the server. The one exception is when we run "initdb", a default database "postgres is created, so it can communicate with an admin tools.

Pacman installs the PostgreSQL server into a "bin" directory. This is fine. But, we want all configuration and data writing into our home directory, and we don't want to have to become another user or admin to run PostgreSQL. The idea is simple backup, portability, and user-level permissions; nothing root or weird PAM errors.

1a. install server

# pacman -S postgresql dbeaver

Postgresql install is about 51MB, not too bad. Service runs on port 5432 (change this in postgresql.conf ), and is not a systemctl service. At the user level we can start it, and stop it without becoming root.

1b. intitialize default cluster

Like any other application, I want to access all PostgreSQL files and tasks at the user level: never want to become root or change to another user. Yet when pacman installs Postgresql, it creates a "postgres" group in /etc/group and does not append $USER (eg. 'foo') to this group.

When initdb initializes PostgreSQL, it creates a default cluster and DB named "postgres" and puts them in /var/lib/postgres/data. No! I want the files in the home "foo" directory, like any other app.

Solution: 1) Append ourselves ("foo") to the "postgres" group in /etc/group. 2) run $ initdb and specify our home directory as the data repository.

# nano /etc/group [add self to postgrep group]
$ mkdir postgres
$ initdb -D /home/foo/postgres/data

1c. lockfile hack

There's a third permissions issue. When PostgreSQL runs at user-level, it is thwarted attempting to write a session lockfile into a root controlled /run/postgresql folder. To properly solve this error probably requires manipulating groups, but I simply changed the permissions on /run/postgresql to $USER. The problem disappeared.

# mkdir /run/postgresql/
# chown -R foo:foo /run/postgresql

1d. start server

With all the above accomplished, we can start the server as a user.

$ postgres -D '/home/foo/postgres/data'

1e. make permanent

We'd prefer not to have to delineate the data directory each time we start the posgres server. So we want set the path correctly. This initdb wiki notes the "PGDATA" variable added to the /home/foo/.bashrc file will make this permanent.

$ nano .bashrc
export PGDATA="/home/foo/postgres/data"

Logout and back in and then verify using "$ listenv". Now we can start our cluster (server) with $ postgres

1e. logs, tables, configuration

Unless modified in postgresql.conf, logs will appear in our home directory. Tables are in /home/foo/postgres/data.We can also configure the server, apart from the database and cluster, in /home/foo/postrgres/data/postgresql.conf. This would arrange which port we listen on, and other features. The security related conf is pg_hba.conf. There are others. All of these control the server, not the databases themselves

2. connect to defaults (10 minutes)

With a lot of work, our server environment has been completed. default "postgres" cluster and database were created by the initdb process. Let's leave them so we don't disrupt any administrative dependencies, but let's also move on to creating our own cluster and databases.

From the CLI, there is plenty we can arrange without dbeaver. DBeaver kicks butt once our databases are filled with data. For now though, to check our connection and create databases, we want to use the psql command. Our default username is "foo" when we connect because that's the user we ran initdb. The default database "postgres" however also must be called. So here is our connection command.

$ psql -U foo postgres

Again, there are three entitites: the cluster and the default DB are named "postgres", and the username is our own home directory name.

3. create clusters and databases (whatever no. of hours)

Our server environment is complete and our plans for data begin. We might have created a UML schema or it might be trial and error against a CSV or something else.

We're connected to the database through psql, so now we want to make a cluster and a database

BTW, there are many pages about dropping clusters and databases, and we can delete them from the data folder as well, so it's not that difficult to drop. Ok, but let's add.

4. dbeaver

Now that we're at step 3, we can add dbeaver to visualize our setup. Dbeaver is java and so needs JDBC compliant databases. Postgres is Type 4 java-compliant, inherently java-compatible. Dbeaver connects to a user at startup, 'postgres' by default, so change it to your $USER, eg "foo".

concepts

Roles and users

Somewhat confusing -- an imperfect analogy is that roles are governments and users are those operating within those governments. None of these will be created until we create a database. Just turning on the server ("postgres" command) does not create a database nor its associated roles.

postgres - users and roles  (22:52) E-Multi-Skills Database Tutorials, 2020. users can login, roles cannot. Role is a function, user is a client. Dbeaver seeks to connect to a role. It will operate as an agent so it nees a role with significant privileges.
postgres - roles, schema, security  (32:31) databasetorque, 2021. role can cally.
PBX - true overhead costs  (11:49) Rich Technology Center, 2020. Average vid, but tells hard facts. Asteriks server ($180) discussed.