5,838
edits
Your Own Server: Difference between revisions
From Cumulus Wiki
Jump to navigationJump to search
Updated wiki page for MX changes since 3.6.0 when page was created
mNo edit summary |
(Updated wiki page for MX changes since 3.6.0 when page was created) |
||
|
Cumulus MX includes two SQLite databases;
#The first database was added by Steve Loft, but he never documented what
#The second database is the Weather Diary [[Weather Diary|documented here]] added by Mark Crossley. Also see [https://cumulus.hosiene.co.uk/viewtopic.php?f=37&t=17919 in the support forum] for a topic comparing the differences between Cumulus 1 and Cumulus MX weather diaries.
#* The [[MX Administrative Interface|Admin Interface]] provides a page to view, or edit, a single date in the weather diary. You can install another tool that edits SQLite databases if you want (see next section).
The legacy Cumulus software used a [[Log.xml|XML file]] to store the weather diary.
===Reading content of SQLite databases===
Obviously, Cumulus MX can read and update both of these databases, but there are a number of software packages available that can be used outside MX to access these database files, and to minimise technical detail, two will be named as possible candidates, you will find others online.
On Microsoft Windows, install phpLiteAdmin (the significant part of that name is in the middle) to read (you can display the whole table very easily) and edit (via a friendly GUI) either, or both, of the SQLite databases that MX uses as mentioned above.
In Linux use <code>sudo apt install sqlitebrowser</code> to install easy-to-use software to read/edit these databases.
You may need to change file permissions.
Note that sqlite databases use sequential file structure, not a relational database design. This implies for updates, rows are physically identified by row number (order of creation), (not by the primary key that SQL accesses use with relational database designs).
= Adding a web server and a database server for your own purposes =
To get maximum benefit when using with MX, you need multiple components:
# web server – there are various options listed below; beware "apache2" is huge as it is very complicated, but secure; other options consume fewer resources, and may be easier to use, but they will also be more restrictive
# PHP script - from [https://windows.php.net/download/] - this provides a way of controlling what SQL is run depending on other factors
# Database server - MySQL Community edition from [mysql.com] or MariaSQL from [https://downloads.mariadb.org/].
# Integrating components like ‘’’phpMyAdmin’’ (there are alternatives) from [https://www.phpmyadmin.net/downloads/] which combines PHP and MySQL
There are a number of packages that combine all of these:
I will give a few examples, but there are many more, each has its supporters, but also each has people who criticise it. You can search on-line and find both all the (good and bad) comments, and clear instructions.
This Wiki page will only describe one installation (on a Raspberry Pi) in any detail, otherwise it will become far too complex.
=== Install
I will first mention 3 possible web servers than can run on a Microsoft Windows personal computer (or server box):
#You might choose to install the Abyss web server from [aprelium.com]
If you get components separately, then there is extra work to get them to work together, but you will find clear
I will next mention 3 possible web servers than can run on a Linux computer:
#You might choose '''nginx''', as it is a quite popular web server for small computers (like the RPi), install this web server with <code>sudo apt-get install nginx -y</code>.
#You might choose '''Apache 2''' as it is probably the most comprehensive, so if you have enough space, install it using <code>sudo apt install apache2 -y</code>.
#*You might want to add Fast CGI to your apache server (this is a way of interfacing interactive programmes with a web server, but if you don’t have any idea what that might mean, skip this)
#* To add this use <code>sudo apt install libapache2-mod-fcgid</code>.
#You might choose '''lighttpd''', as a web server that is designed to use as little space on your Linux computer as possible, install it with <code>sudo apt-get install lighttpd -y</code>.
As already mentioned, some web servers come in a package that includes this script language. Again, as mentioned before, such packages may be rarely upgraded so the included PHP might be an old version with bugs or security concerns.
At time of updating this Wiki page, PHP 8.1 is stable version, PHP 8.2 is available to test, and PHP 9.0 is planned. Older versions 7.x, and 5.x are largely obsolete, although some web providers may still make them available.
* PHP is not the only script language available, but it is quite comprehensive
** PHP can be used either in a fairly simple way by non-technical people
** or in an object-oriented way for those more technical to achieve success with more complex scripts.
* To check which PHP modules have been installed by any command, type <code>php -m</code>.
* To test that php is installed, type <code>php -v</code> into terminal, and you will see the exact version that has been installed, a confirmation that it offers command line interface (cli), and a copyright notice.
* If you later want to use a database (and a tool like PhpMyAdmin), then your php modules loaded must include at least '''mysqli''' and '''mbstring'''.
====Microsoft Windows - download/install PHP====
Straight forward, just down load a zip from from [https://windows.php.net/download/]. You will want a “multi-threaded” version to use with a web server, as several PHP web pages may need to run at the same time.
*PHP is not the only script language available, but it is quite comprehensive
**being able to be used either in a fairly simple way by non-technical people
**or in an object-oriented way for those more technical to achieve success with more complex scripts.
*To check which PHP modules have been installed by the above command, type <code>php -m</code>.
*If you later want to use a database (and a tool like PhpMyAdmin), then your php modules loaded must include at least '''mysqli''' and '''mbstring'''.
To test that php is installed, type <code>php -v</code> into terminal, and you will see the exact version that has been installed, a confirmation that it offers command line interface (cli), and a copyright notice.
====Linux operating systems - best repository====
The default repositories (see [[Preparing_your_Linux_computer_for_MX#The_various_components_to_commands_for_installation|here]]) may not contain up-to-date versions of all software, or may contain some software that is not compatible with other software.
You can add another repository that generally has reasonably new PHP versions (a few weeks behind the official [https://www.php.net/downloads.php] site) using
<pre>sudo wget -O /etc/apt/trusted.gpg.d/php.gpg https://packages.sury.org/php/apt.gpg
echo "deb https://packages.sury.org/php/ $(lsb_release -sc) main" | sudo tee /etc/apt/sources.list.d/php.list
sudo apt update</pre>
* On Linux, the simplest instruction to install it is <code>sudo apt install php -y</code>, although in practice this will not install all the PHP modules you actually need
** A warning, which version of PHP that command gives you depends on which php version is found first in the [[MX_on_Linux#The_various_components_to_commands_for_installation|respositories]] your computer has in its source list, so you may end up with the oldest!
* A better
<code>sudo apt install php''V.x''</code> (where V is major version, and x is minor version e.g. 7.4 or 8.1)
** This may still not give us the modules we want, but it does control which version is obtained
* Therefore the best variation is to list both the modules we want, and the version we want (here "x" denotes minor version, adjust to what is latest stable (e.g. change to php8.1-common etc.):
<code>sudo apt install -y php8.x-common php8.x-cli php8.x-json php8.x-xml php8.x-readline php8.x-mbstring php8.xmysql php8.x-curl php8.x-gd pdp8.x-psqlite php8.x-pdo -y</code>
* The Linux package manager will ensure that any components, that are required by dependencies, but not listed specifically, are also installed
** Loading from the "packages.sury.org/php" repository has one disadvantage, the dependencies there don't automatically integrate the PHP with your web server (if you are using apache 2 for web server, add "libapache2-mod-php8.x" to list);
====Linux operating systems - standard repository====
* On Linux, the simplest instruction to install PHP is <code>sudo apt install php -y</code>, although in practice this will not install all the PHP modules you actually need
** A warning, which version of PHP that command gives you depends on which php version is found first in the [[MX_on_Linux#The_various_components_to_commands_for_installation|respositories]] your computer has in its source list, so you may end up with the oldest!
* A better variation is to actually list the version we want:
<code>sudo apt install php7.4</code>
** This still does not give us the modules we want, but it does control which version is obtained
*A still better variation is to list the components we want:
<code>sudo apt install
** Add ”libapache2-mod-php7.4”
** The included “php7.4-cli” is what allows us to run scripts in batch
** “php7.4-common” and “php7.4-mbstring” are pre-requisites for various other modules, and the second is used by ‘’’phpMyAdmin’’’ and other tools
** “php7.4-json” (see [[Php_webtags#Option_3:_JavaScript_Object_Notation|here]] and [[:Category:JSON_Files#Data_Transfer_Format]] and “php7.4-xml” (see [[Php_webtags#Option_1:_eXtensible_Markup_Language_alternative|this section]]) are used to handle particular file types
** “php7.4mysql” integrates php and MariaSQL/MySQL
** "php7.4-sqlite php7.4-pdo" are needed to access the weather diary or recent history databases
** “php7.4-curl” “php7.4-gd” may be needed as these are used by some third-party routines designed by Cumulus contributors
* There are advantages in using default repositories:
** installing a web server and PHP from the default repositories will pick up joint dependencies and thus integrate the web server and php.
===Creating a Home web page on your web server===
To install this database server, we type <code>sudo apt install mariadb-server php-mysql -y</code>.
Our database files will be stored at (Linux) '''/var/lib/mysql''' by default. Our MariaDB configuration is stored at '''/etc/mysql/mariadb.conf.d/50-server.cnf''', and it is the ''datadir='' entry that controls where the database files are stored, so edit that to store files somewhere else, but make sure the storage location has right ownership permissions. This Wiki page will not give you the full technical details, so only those with some technical knowledge do such changes.
=== Making your database secure ===
===Getting web and database servers ready for use===
We need to create a user for PhpMyAdmin (or adMiner or whatever) to access our database and another for Cumulus to use to access the database tables. At the moment our database access has the single '''root@localhost''' user we created when we installed MariaDB. The initial password was set then, and we need to use it to get access to MariDB monitor where we can insert some SQL commands to create these two users.
Most tools, like PhpMyAdmin, on first start up will ask for username (here I choose "admin") and password, thereafter
The database name (here I choose "cumulus" for the data base name), user name (here I choose "weather" for the user name), and password, must all match those set in MX using the [[MX_Administrative_Interface#MySQL_settings|MySQL_settings]] in the admin interface. You could of course use PhpMyAdmin (or AdMiner) to create additional user names, and to create the database, but I assume all is done in the following bit of SQL. Remember, the Windows operating system is not case sensitive, but all Linux based operating systems are case sensitive (so whatever pattern of capitals and lowercase you choose must be used every time. Also all names must start with a letter, can contain only letters or digits (no punctuation), and must not be a reserved word ("password" is a reserved word, so you cannot use that for a password, nor for a column name).
ExportMySQL.exe and CumulusMX.exe (see [[Cumulus_MX#Executables]] for details) both create SQL for updating tables in a MySQL database, such as the one our MariaDB software package we have installed can create.
This option is about installing a package to do the tasks that go with operating a database, that MX may not be able to do in the way you require.
====PhpMyAdmin====
#The version of phpmyadmin in the default repository is not compatible with PHP7.2 and above
# Visit the phpMyAdmin [https://www.phpmyadmin.net/downloads/ official download page] and look for the .tar.gz URL and download it using wget.
# The following steps assume version 5.
# Decide whether you just want English, or need another language
#* For English only, the wget command is <code>sudo wget https://files.phpmyadmin.net/phpMyAdmin/5.
#* For other languages, the wget command is <code>sudo wget https://files.phpmyadmin.net/phpMyAdmin/5.
# Change to folder where we will extract our download, this could be <code>cd /usr/share/</code> or some other directory perhaps on an extra storage drive
#* For English only, extract your download into the current folder using <code>sudo tar xzf phpMyAdmin-5.
#* For other languages, extract your download into a sub-folder of the current folder using <code>sudo tar xzf phpMyAdmin-5.
# Create a folder to hold the download within the folder where we did our download <code>sudo mkdir
# Now we will move the contents from the sub-folder created by the "tar" command into the folder we just created
#* For English only, the move command is <code>sudo mv phpMyAdmin-5.
#* For other languages, the move command is <code>sudo mv phpMyAdmin-5.
# There is some configuration to do, for simplicity here assume the destination directory ("./phpmyadmin_5.2.0") is actually "/usr/share/phpmyadmin" and that we will use '/var/lib/' and '/etc/' to store other directories used by phpmyadmin:
#* First <code>sudo nano /usr/share/phpmyadmin/libraries/vendor_config.php</code> to edit (with nano) the vendor configuration
#** change the path shown in the existing line mentioning 'TEMP_DIR' so it becomes <code>define('TEMP_DIR', '/var/lib/
#** change the path shown in the existing line mentioning 'CONFIG_DIR' so it becomes <code>define('CONFIG_DIR', '/etc/phpmyadmin/');</code>
#The following sequence of commands will
#* add the mysqli module to our php install, <code>sudo phpenmod mysqli</code>
#*will restart apache (change if you are using a different web server), <code>sudo service apache2 restart</code>
#*will create a symbolic link for the phpadmin installation in the server web root so it can be seen (and used) in our browser, <code>sudo ln -s /usr/share/phpmyadmin /var/www/html/phpmyadmin</code>, note the first path is to directory where we stored our phpmyadmin files, and the second path/file is the default web directory and the symbolic link file name, adjust these paths to what is needed for you.
#*will give the standard user (pi) ownership of the database files and the web pages: <code>sudo chown -R pi:www-data /var/www/html/</code>
# phpMyAdmin now has the files where it expects to see them, and should work
===Populating your database tables on your Pi===
Cumulus MX has functionality to update database tables at one of
# fixed SQL at real-time
# fixed SQL at standard interval
# fixed or custom SQL at end of day
# custom number of seconds
# custom number of minutes
MX now has ability to run SQL updates when you edit a log file, provided you use the predefined column and table names. MX also has the ability to store failed SQL and to try to run it again at the next rollover. This will fail again and again until MX is stopped, if the SQL is specified wrongly, but is designed to cope with correct SQL, that fails because the database server is down, and assumes the database server may be working again by next rollover.
The database tables can use the column names in the schema pre-defined by Cumulus MX for the predefined queries, or in a custom schema (where you specify the column names) if you specify the required SQL. The settings are all in [[Cumulus.ini#Optional_Structured_Query_Language_Settings|SQL settings]], so read that cross-reference to find out more.
You might have started using MX before you set up your database. There is a option in that part of the admin interface to create database tables (as required) for each of the first 3 updating intervals. For example, the default name for the table updated at the standard interval is "monthly", but you could give it a name of "standard" or whatever you like.
# for each line in the log file the executable will try to insert a row in the database table
#*the SQL syntax used is "insert ignore", so if the row for that log file line already exists in the table, it will skip onto next line of log file.
There is no way to use this executable for insert of past rows into the real-time table.
=== Restarting Web Server ===
After all these installs, we need to restart
We will need to restart Apache (or whichever web server we installed) any time we change our php.ini files, database passwords, and anything else that is checked when the web server starts.
| |||
