paperhater - classification issues (minus database)

We want to organize electronic media as much as possible, without a database, for at least two reasons: 1) if we can reach file organization and "findability" goals without the database, then we've saved the expense, 2) if our situation ultimately requires a database, the need for one becomes more cleary defined. For this reason, the first steps are the same, database or no ultimate database.

overview

We begin with an entirely non-homogenous mess of files, a deck of scattered cards but without names or suits. Our pile includes everything from receipts, code, diplomas, research articles, manuals, correspondence, old emails, and on. Some of these files are active, some are reference, some are family history, and on.

first cut

Our first cleavage is between all the articles, texts, and manuals which might be used as a reference, or cited in a thesis, etc. Call this our library. The second pile (receipts, forms, photos) is... everything else. We'll overlay a universal file and folder naming convention to both categories. However libraries require an additional handling step due to standardized citation requirements.

There's help for both categories. 1) for library items, ISBN/ISSNs, Dewey Decimal(since 1876), and/or a Library of Congress classifications, already exist. We can organize these using BibTeX. 2) for everything else, think back to the paper era. Records management had proven ways to organize these. This process has become electronic records management (ISO 15489), and is just as helpful. See the first video below.

records basics (25:31) US National Archives Records Management, 2009 Doubling info every two years. 11:00 paper's been lost -- we used to know how to do it. sample basic structure. 17:00 She recommends a file plan.
file management (1:23:57) Nicholas Andre 2013. Windows-based lecture, but clear thinking fellow gives good context for what we're after. Backgrounder. Corning (NY) Community College course.

naming - files

3 part naming convention. Subject, date, code. These vary in order depending on what's most important to the user of that file.

file naming conventions (10:00) Simpletivity, 2018. Ad first 1:36. Uses 3 part naming, succinctly described. Probably best at .75 speed. Comments excellent.

naming - folders(directories)

Mostly the naming is the same as the files, but arranged vertically. 3 layer naming convention. Function, subfunction, action. Thinking of the deck of cards, we can arrange by suit, by number, by color. What's the fastest way to find a card if they're in folders? Might depend on my style of play. NARA notes that granularity of folders depends on number of docs for that folder.

folders - website (8:19) John Morris, 2018. Standard website folder organization.

plan - file & folder

This step is the combination of the decisions on naming of files and folders. Government suggested in General records schedule from the NARM website.

records basics (25:31) US National Archives Records Management, 2014. Donna Read. informatioin doubling every two years. 11:00 paper's been lost -- we used to know how to do it. sample basic structure. 17:00 She recommends a file plan.
file plan basics (47:44) US National Archives Records Management, 2013. Jeff Benson. staff consensus, record retention,
holding to center (10:46) Luke Smith, 2020. Part of sticking to a file plan is understanding that what works for a person can be useful.
federal social media (43:37) US National Archives Records Management, 2013. Bethany Cron. Federal records, con

A. non-research example

This is the thing for say, billings or other saved items.

B. research/library example

We rarely seem to get New Yorker articles directly related to Bay Area unless it's a controversial topic: presumably editors don't want to research on their home NYC turf and alienate locals. Let's say I want to keep one such article to cite later. The information panel.

Step 1 - gather info

Very difficult without a physical copy. There's no online index for magazine volume and numbers cross-referenced with date, at least that I've found. With a physical copy of the New Yorker, I can get the info -- ISSN 0028792X, Volume 98, No 4, Mar 14, 2022. NYC, NY. The article of interest in this example: "The Access Trap", is from Nathan Heller, pgs 34-45. I've scanned these pages into a PDF, as yet unnamed. If online, we may also find other control numbers we want to include. Eg, if we had a dissertation to cite, we know that, "There is no single source for a comprehensive dissertation search." We might encounter a different control number at different sites and want to include them.

Step 2 - PDF name - first cut

Revisit the PDF name and evaluate the 3 part name. It should include a date, name, and subject code, in a way that at least hints at the file contents. In this case, we might, eg

20220314_NY_LowellEquity.pdf

Step 3 - BibTeX it

Information for storage, retrieval, and citation (chicago, mla, apa) could come from a bibtex BIB file. Bibtex files can be massaged in the document for any citation format. The question we'll want to ask later is how many BIB files ? since we can't have just one immense BIB file for ever article we have -- and of course we need to create a custom one for any document we create -- we're going to run into meta-problems. In this case we'd "@article" template, minimally...

@article{uniquecitekey,
author = "Heller, Nathan",
title = "The Access Trap",
journal = "The New Yorker",
year = 2022,
volume = "98",
number = "4",
month = "Mar",
pages = "34--45",
issn = "0028-792X",
doi = "20220314_NY_LowellEquity.pdf",
note = "Lowell HS equity clause"
}

We probably don't need 11 fields in a database, but BibTeX has these readymade. Review the DOI and see that it corresponds with the filename -- make any adjustments.

apa style bibtex (2:53) Charles Clayton, 2016. Also does IEEE. Important about making certain filenames match. Probably best at .75 speed. Comments excellent.
latex review (59:42) Derek Banas, 2019. Typical Banas killer review. He uses "TexShop", which appears to have autocomplete
bibtex citation (7:38) Center of Math, 2015 Have to run it twice, like a table of contents.

Step 4 - filename and folder again

Review the filename, folder, and BibTex information. Or, if a random receipt, review retreival issues again, such as the folder depth (no more than 3) and the filename (gives some info).

C. database or not?

ISSN number searchable here, topic, Somtimes we have subtopics

csv, sms, postgres, dbeaver, python project?

I began this post 10 years ago only thinking about a LAPP or LAMP. Even that was too wide a scope. This post only covers importing a CSV into a stand-alone PostgreSQL instance. We can then take the data for additional processing or practice or whatever. Or for Business Intelligence (BI), since every database is an opportunity for BI meta-analysis. There are several pre-made tools for this, but they may not address our focus. They're still worth a look.

dash and plotly introduction  (29:20) Charming Data, 2020. A bit outdated, but the fundamental concepts still helpful.
PBX - on-site or cloud  (35:26) Lois Rossman, 2016. Cited mostly for source 17:45 breaks down schematically.
PBX - true overhead costs  (11:49) Rich Technology Center, 2020. Average vid, but tells hard facts. Asteriks server ($180) discussed.

file/record plan issues

We can save psql and sql scripts as *.psql and *.sql files respectively. If we use, eg DBeaver, it will generate SQL scripts, which we can subsequently transform, with some work, into PSQL scripts. We may wish to learn more Math also, though this can be difficult on one's own.

Back to our project. At the lowest levels, we need a system which

• provides document storage. folders receive a number relevant to their path, and we save documents in them without changing the file names. Of course this has to account for updates of new file addition or any which are retired/deleted.
• queries either on meta information (dates), or upon document content (word search)
• reports on the database itself; its size, category structures, number of files pointed to, and so on. This is good BI app territory.
• back-up configurable or cloud hosting
• if office access, possible GUI to delimit employees queries. browser friendly desktop development required for a GUI (PyGObject - GTK, or QT), maybe even Colab.

Python is now flexible enough, especially through become advanced enough to establish client side connections with a database which used to require PHP.

	Grafana apparently works well in a time-series but also could allow us perhaps display counts minutes or numbers of texts tied to various numbers. Pandas and even R have some visualization options.
	Dash is another API similar to Grafana. We can use one or the other, or GraphQL.
	Fluentd can help us if we need to manage several relevant logs around this database project.
	Logs are somewhat specific, but prometheus monitors selected system traits - memory use, HDD access. It can also do this over HTTP in a disributed system, as described here. These kinds of time series integrate well with grafana.
	No particular logo, but SMS related, being able to run some kind of programs against databases to find mean, mode, and standard deviation, even in a table. For example a report that showed average number of texts, word length, top 3 recipients and their tallies, etc. Easily run queries against a number and report to screen. Use browser to access DB.

SMS

A possible project for document managment may be to establish SMS message storage, query and retrieval. The data are row entries from a CSV, rather than disparate files with file names. A reasonable test case.

• pacman postgresql dbeaver python. By using dbeaver, we don't have to install PHP and Pgadmin. Saves an extra server.
• activate postgreql server
• import CSV into Gnumeric and note column names and data types. This can even be codified with UML
• populate database with correct column names and formats. Also check data file with text editor to verify comma numbers are accurrate, etc
• Python script or manual dBeaver to import CSV file(s).
• rclone backup of data files to Cloud

PostgreSQL - install and import CSV  (11:19) Railsware Product Academy, 2020. Also PgAdmin install and walkthrough.
PBX - on-site or cloud  (35:26) Lois Rossman, 2016. Cited mostly for source 17:45 breaks down schematically.
PBX - true overhead costs  (11:49) Rich Technology Center, 2020. Average vid, but tells hard facts. Asteriks server ($180) discussed.

1 & 2. postgresql install/activate (1.5 hrs)

Link: Derek Banas' 3.75 hr PostgreSQL leviathan

See my earlier post on the steps for this. I rated the process at 2 hrs at the time. However, with the steps open in a separate page, 1.5 hrs seems reasonable.

At this point, we should have a PostgreSQL server (cluster) operating on a stand-alone system out of our home directory, with our own username, eg "foo". We can CREATE or DROP databases as needed as we attempt to hit upon a workable configuration.

$ postgres -D /home/foo/postgres/data
$ psql -U foo postgres
postgres=#: create database example;
postgres=#: drop database example;
OR
postgres=#: drop if exists database example;
postgres=#: exit

It would be nice to have a separate cluster for each import attempt, so I don't have to carefully name databases, but I don't want to run "initdb" a bunch of times and create a different data directory for each cluster, which is required. So I'm leaving everything under "postgres" cluster and when I get the final set of databases I like, I'll do a script (eg. *.psql) or some UML so I can re-install the solution after deleting the test project.

3. csv import

Most things on a computer can be done 100 different ways so I started with the simplest -- psql and moved to other ways. This is all covered below. But my first step was to pick a CSV of backed-up SMS's for the process, and clean it.

3a. csv cleaning

I first selected "backup.csv" with 7 columns and 456 rows. I simply opened it with Gnumeric and had a look. I noted advertising in the first and last rows and stripped these rows from Geany. This left me a 7x454, with the first row the column titles. What's interesting here is some of the texts had hard returns in their message contents, so that there were 581 lines. I therefore made a second version, 7x10, with no returns in the message contents; "backup02.csv", for simplest processing.

3b. data types

The efforts below have taught me that we need to understand several basic data types. There were a lot of failures until then. I found this video and this post helpful. Matt was only making a table for a two column price list, but it gives us a start.

And here the more complex scenario Raghav skillfully addresses with multiple tables.

3c. monolithic import attempt

Without considering keys or any normalization, let's try to just bring in an entire CSV, along the lines of Matt's import above. Of note, the times were in the format, "2021-03-07 21:40:25", apparently in the timezone where the call occurred.

$ psql -U foo postgres
postgres=#: create database test01;
postgres=#: \c test01
test01=#: create table try01 (between VARCHAR, name VARCHAR(30), phone VARCHAR(20), content VARCHAR, date TIMESTAMP, mms_subject VARCHAR(10), mms_link VARCHAR(20));
test01=#: select * from try01;

test01=#: COPY try01 FROM '/home/foo/backup01.csv' DELIMITER ',' CSV HEADER;

This accurately brought in and formatted the data, however it also brought a first row that was all dashes, and I don't really need the 1st, 6th, and 7th columns. I looked at the PostgreSQL documentation for COPY. The columns look easiest to fix so i created a smaller table without them.

test01=#: create table try01 (name VARCHAR(30), phone VARCHAR(20), content VARCHAR, date TIMESTAMP);

test01=#: COPY try01 FROM '/home/foo/backup01.csv' DELIMITER ',' CSV HEADER;

4. dbeaver tweaks (1 hr)

The most important thing in DBeaver, after we've connected to our server, is to remember to R-Click on the server, go into settings and select "Show all Databases". If you forget this step, you will go insane. I didn't know about that step and... just do it. The other thing is a helpful "Test Connection" button down in the L corner.

Finding that some columns have dates, times, numbers, and do we want to use the telephone number as the primary key? Once we have a working concept, we'll want to UML it.

dbeaver - some basics  (16:23) QAFox, 2020. Begins about 10:00. Windoze install but still useful.
PBX - on-site or cloud  (35:26) Lois Rossman, 2016. Cited mostly for source 17:45 breaks down schematically.
PBX - true overhead costs  (11:49) Rich Technology Center, 2020. Average vid, but tells hard facts. Asteriks server ($180) discussed.

python

As noted above, however, we are now interested in letting Python do some of the scripting, so that we don't need two languages. To do this, we install PL/Python on the PostgreSQL side. Other options are available for other languages too -- for example if we want to run statistical "R" programs against a PostgreSQLdatabase, we'd install PL/R. Or we can write-out PL/pgSQL commands and put them into Python or R scripts if we wish.

On the Python side, we obtain modules from PyPi, such as Psycopg2 (also available in pacman repos in Arch). With PL/Python in the database, and Psycopg2 modules in our Python programs, our connections and commands to PostgreSQL become much simpler. And of course, one can still incorporate as much or as little PHP as they wish, and we'd still use judicious amounts of JavaScript in our Apache-served HTML pages. To summarize, in a LAMP we might want:

server-side

1. PostgreSQL - open source database.
2. Python - general purpose language, but with modules such as Psycopg2, we can talk to the database and also serve dynamic webpages in HTML, replacing PHP, or even create some GUI application which does this from our laptop.
3. Apache - still necessary, since this is what interacts with the client. Pytahon or PHP feeds the dynamic content into the HTML, but Apache feeds these pages to the client (browser).
4. PHP - still available, if we want to use it.

system - server - hosting

We want a system for learning management (LMS), and another for general usage. I like the Moodle LMS and Nextcloud. The problem is that, for years, both of these should be done locally (VPN), you can't really webface them. New solutions are making it possible to do both. I've previously had webhosting, and I think that's been part of the problem. This time around I want to do a VPS. I would still put Nextcloud on a VPN, but I think Moodle can reasonably be done on a VPS at this point with TOTP. So we can host Moodle on Google, but the question is which Tech Stack (see below). The idea is there re 3 layers: the hosting (Google), the http server (Apache), and the system (Moodle, NextCloud).

• VPS - Virtual Private Server. Cloud server. Google, UpCloud
• VPN - Virtual Private Network. Home server. Unlimited storage, only limited by HDD space. I am uninterested in the typical web usage of VPN's for anonymity and so on. These are mostly useless (see vid from Wolfgang's Channel below). Thinking here of the much more prudent usage of a home network for a VPN. It's possible to make it web-facing also, but this should not be done without 2FA and SSL.
• Backup Critical files need this. Probably anything paper that's irreplaceable, eg, DD214, grades, etc. This shouldn't need to be more than about 1-5 GB anyway, but critical. Chris Titus uses BackBlaze. BackBlaze however relies on Duplicity, which in turn relies upon the dreaded gvfs, one of the top 5 no-no items (pulse audio, gvfs, microsoft, oracle, adobe). Use some other with rclone, rsync, remmina, cron.

plan

Current A-Plus costs: $5 month x 2 sites ($120) + annual 2 x domain w/privacy ($30), one site only MySQL.

1. DNS - Google ($12 yr x 2 incl.privacy)
2. rclone some criticals to Drive
3. Moodle VPS on Google LXC
   • $ yay -S google-cloud-sdk 282MB
   • go to Google Cloud and provide credit card
   • follow Chris Titus' instructions in video below

   Host on Google (30:32) Chris Titus Tech, 2019. Do an inexpensive, shared kernel setup. Uses Ubuntu server and Wordpress in this case.
   Moodle 3.5 Install (22:47) A. Hasbiyatmoko, 2018. Soundless. Steps through every basic setup feature. Ubuntu 18.04 server.

4. Nextcloud VPS on Skysilk ($60)

1. transfer DNS to Google

Chatted with old provider and obtained the EPP's for both domains, began registration in the new domain. Once these are established, we'll have to change the A-records, and pehaps "@" and "C" records to point to current hosting. Each possible VPS provider handles their DNS in different ways. Some providers manage the entire process under the hood, at others a person must manually make any changes to their A-records.

Rsync Backup on Linux (9:19) Chris Titus Tech, 2019. Great rundown plus excellent comments below.
New DNS Update (7:18) Without Code, 2018. Proprietary, but a transparent example of what is involved in the process.

server blend

Nextcloud is not an actual server itself, the underlying server should be something like Apache or Nginx. Nextcloud then overlays these and serves files via the server underlying it. The logins and so forth are accomplished in Nextcloud in the same way we used to do so with, eg. Joomla or Wordpress (optimized for blogs).

Nextcloud: Setting Up Your Server (17:43) Chris Titus Tech, 2019. Uses Ubuntu as underlying server on (sponsored) Xen or Upcloud. Rule of thumb $0.10 month per GB, eg $5 for 50G.
What are Snaps for Linux (4:47) quidsup, 2018. These are the apps that are installable across distros.

2. existing storage for backup

We can use free storage such as Drive or Dropbox to backup data. They key is it should be encrypted on these data mining, big tech servers.

RClone encryption (10:21) Tyler, 2017. Methods to encypt with rclone. Also good idea to download rclone-browser, for an easy GUI.
Rsync Backup on Linux (9:19) Chris Titus Tech, 2019. Great rundown plus excellent comments below.
Using Cloud Storage (22:55) Chris Titus Tech, 2019. Easy ways to encrypt before dropping into Google Drive, etc. (sponsor:Skysilk)

choosing a VPS

One can of course select Google, but what virtualization do they typically employ? Skysilk uses LXC containers via ProxMox.

Rsync Backup on Linux (9:19) Chris Titus Tech, 2019. Great rundown plus excellent comments below.
Using Cloud Storage (7:31) Wolfgang's Channel, 2019. Be sure to pick a provider that uses Xen or KVM, rather than OpenVz-based virtual machines.

tech stack

I used to use a LAMP stack, but I am trying to avoid MySQL (proprietary RDBMS), and use PostgreSQL (OODBMS), as a minimum update (LAPP), and have looked at some other stuff (see below). I may try a PERN stack if I can get it going with Moodle. Post

Various Tech Stacks (48:25) RealToughCandy, 2020. Decent rundown plus large number of comments below. Narrator skews "random with passion" over "methodical presentation", but useful. PostgreSQL around 38:00.
Using Arch as Server (33:11) LearnLinuxTV, 2019. He's running on Linode (sponsor), but the basics the same anywhere. Arch is rolling, but just keep it as the OS for one app.

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

C++ - Eclipse

Links: Short tutorial   C++ examples   Connecting C++ to MySQL   makefile tutorial   make manual  

---

Test-driving the Eclipse Java/C++ interface (IDE) as part of my continuing work on the Paperhater database solution. Previously, I thought a LAMP was the best idea, but the four layers of software make it challenging to focus on function. I intend to do a LAMP version as I gain PHP ability, but a C++ application needs to be accomplished to get basic functionality. One hitch...I don't know any C++.

Make (gcc)
Although this post is mostly about the Eclipse IDE, I will likely eventually rely on hand-made Makefiles for more fine-grained control. As an example, Paperhater needs to interface with MySQL. There are special compilation commands to allow code to interface with MySQL. These are unlikely to be managed by a generic IDE, unless it allows for customization.

Eclipse
This interface is a great thing. It's oriented around Java, but I don't want to write in Java until they solve the problems they seem to have with JRE in Linux environments. The C++ IDE (Eclipse calls it "CDT" C++ Development Tool) can be downloaded directly into Eclipse. This is just a plug-in for Eclipse, since Eclipse was designed for Java programming. But it's nearly as robust as the Java tool.

The CDT is Eclipse version specific. When I went to the CDT download page, it noted I would need the link specifically for my Eclipse release. I have Eclipse "Ganymeade" (aka version 3.4.1). I copied the link they provided for version 3.4.1, http://download.eclipse.org/tools/cdt/releases/ganymede, to my clipboard.

With the link in my clipboard, I went to Eclipse and: Help->>Software Updates->>Available Updates. Select "New Sites" and put the link in there. Eclipse downloaded and configured the necessary CDT files once I pointed it to that link.

more data fun - PaperHater

Links: Common MySQL Commands  WizzyWeb   VistA database

---

I've worked on going paperless since about 2002. It's a maddeningly dull, time consuming affair if one is not a computer programmer capable of designing a database. I am not. Just a person, a computer user. I call this project "PaperHater".

schema

critical, tax, code, projects, auto,

categories

Can it be done without a database? We have the Dewey Decimal (since 1876) and Library of Congress system for books and mags (ISBN, ISSN), so we can use their system to an extent, possibly for our library and citations grouping. But at home I have receipts from vehicles, medical lab reports, HS diploma, and on. This is also not exactly business style records management, since the categories include personal areas. Probably benefits from electronic records management (ISO 15489). File naming conventions, folder conventions and metadata conventions. Yet still might end up requiring a database.

file naming conventions (10:00) Simpletivity, 2018. Ad first 1:36. Uses 3 part naming, succinctly described. Probably best at .75 speed. Comments excellent.
Ffmpeg advanced techniques pt 2 (1:23:57) Nicholas Andre 2013. Windows-based lecture, but clear thinking fellow gives good context for what we're after. Backgrounder. Corning (NY) Community College course.

clutter issues

By mid-2002, I was regularly scanning. Everything from bank statements to letters from my deceased grandmother. I learned that the number and variety of the generated files grew rapidly and could not easily be organized; I was spending significant time categorizing and renaming the files using customary file-naming and folder-naming conventions. In spite of this effort, and with only a couple thousand files (albeit growing in number), I had to admit to myself that I was wasting more time arranging and not finding files than I previously had spent hassling with the inconvenience of paper documents. What good was the computer accomplishing for me?

A proper database sitting between thousands of stored electronic files, and the user attempting to retrieve one of those files, appeared to make all the difference. However, since an immediate solution looked neither likely nor affordable at that time, I reverted to storing documents in bankers boxes. I did this for several years, but with began to slowly acquaint myself with database programs.

By 2009, I had become routinely frustrated with paper accumulation again. Stuck between a computer and a pile of papers, there appeared to be no easy way out. Since early 2009, alternating Saturdays have been spent reading and learning installation processes, webserver configurations, and the like -- LAMP stuff. It seems to have paid off. I'm roughly at the 2/3 mark on this project. Over the next several months, I intend to add an entry here and there about PaperHater's progress to summarize for myself and to possibly help other home users who might be attempting to design and implement something similar on their own system(s). Good luck to all of us. It would be helpful if expert organizations with CMS-type experience could release reasonably priced PC/Mac database solutions instead of gouging us.

step 1

Design the database. I mostly use localhost for this, since it's fast and secure, but sometimes run things on the cloud server. In other words, I spent the many weekends required to learn how to configure reliable LAMPs on both my localhost and server site, and to have them running with the permissions and tweaks (think, eg. php.ini, config.inc.php, etc) I wanted. Following that portion came additional reading, head-scratching, and back-of-envelope sketches; then downloading and installing working applications. Finally, using phpMyAdmin and MySQL commands, I reviewed and modified table structures until I could determine a desirable schema. I also worked on php scripts. Eventually, I had a set of working databases, some copied to .sql scripts, and some partially completed php scripts. That's mostly where it stands now, but I'll move on to describe what's going to come next.

step 2

Copy the structure of a localhost or online database I've designed and tested and want to use for interaction. We put this into an .sql script.
(localhost)$ mysqldump --no-data database >/home/foo/database_template.sql

(online)$ mysqldump -h whateversite.com -uuser -ppassword --no-data -D database >database_template.sql

Then go to whatever web address I keep databases on, create a new database there (thanks to this site), and give it the structure we want from the .sql script.

$ mysql -h whateversite.com -uuser -ppassword
mysql> CREATE DATABASE newdatabase;
mysql> USE newdatabase;
mysql> SOURCE /home/foo/database_template.sql;
mysql> quit;

The new database is now ready to accept data-entry and to run queries.

step 3

Upload some php scripts. I've written a large percentage of the interfacing php scripts but, today, I learned about WizzyWeb, a $99 product which could help tune my scripts or create similar ones quickly. This seems like a very reasonable price if it does what's advertised. That is, if it saves me two hours, I've made-back my money.

step 4

Enjoy. But remember: the PHP, Postgresql/Mysql,Apache, etc. are all on the server side. Users still must create some Javascript additions for their served pages to influence the browser client. However, it is possible to write such skillful PHP code on the server side, that a client needs very little browser-side code to have a nice experience on the site. And remember that server-side is usually more secure since it can't be hacked without hacking the site, not just a webpage. Here's a list of some pre-built server-side PHP "UI Frameworks" written in PHP (as opposed to Java or .Net). Edit: Also, Sitepoint's poll results from 2015 for PHP frameworks.

PHP vs Python vs Java vs C#

Java no way, since the 2011 Oracle purchase. C# no way, since it's more or less locked to Microsoft's .NET framework. But Python now has modules which allow it to work on a server (of course it can still be used to code stand-alone programs too). PHP is designed to work on a webserver inherently, and so is more naturally integrated into HTML, but if one wants to be consistent, they could just do everything with (open-source) Python, using its server side add-on modules (scroll down to "Compared as Web Development Frameworks"). Even low-end ISP's like APlus, which has a terrible lag implementing anything, has PHP4/5 and Python capacity.

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.