setup.txt

ubuntu server setup
===================

install ubuntu desktop - can download iso from https://ubuntu.com/download/desktop
update packages
insert guest additions cd image (from virtualbox toolbar) and autorun guest additions installer


debian server setup
===================

install debian with xfce4

run "su -" (not just "su") for superuser with proper path env var set

# nano /etc/apt/sources.list
----
deb http://deb.debian.org/debian/ buster main contrib non-free
deb http://security.debian.org/debian-security buster/updates main contrib non-free
deb http://deb.debian.org/debian/ buster-updates main contrib non-free
----

# apt-get update
# apt-get upgrade

# apt-get install build-essential module-assistant
# m-a prepare

insert guest additions cd image (from virtualbox toolbar)

# cd /media/cdrom
# bash VBoxLinuxAdditions.run

reboot system for guest addition kernel modules to take effect

# adduser <myuser> vboxsf

after installation of phpmyadmin:
# nano /var/lib/phpmyadmin/blowfish_secret.inc.php
use https://www.question-defense.com/tools/phpmyadmin-blowfish-secret-generator to generate string
# chown myuser:myuser /var/lib/phpmyadmin/blowfish_secret.inc.php
# chmod 600 /var/lib/phpmyadmin/blowfish_secret.inc.php


package install
===============

$ sudo apt-get install php apache2 mariadb-server git geany phpmyadmin exim4 php-gd msttcorefonts jsonlint grepcidr


apache2 and mariadb configuration
=================================

$ sudo nano /etc/apache2/envvars
----
export APACHE_RUN_USER=<myuser>
export APACHE_RUN_GROUP=<myuser>
----

$ sudo nano /etc/apache2/apache2.conf
----
ServerName <your-server-name>
----

$ cd ~
$ mkdir dev
$ cd dev
$ mkdir apache_logs
$ mkdir public
$ mkdir pwd
$ cd pwd
$ touch sql_admin
$ touch sql_user

$ sudo chmod 777 ~/dev/public

sql_admin:
----
webdb_admin
<password>
----

sql_user:
----
webdb_user
<password>
----

$ sudo nano /etc/apache2/sites-available/000-default.conf
within <VirtualHost *:80>
----
DocumentRoot /home/<myuser>/dev/public/
DirectoryIndex index.php

<Directory /home/<myuser>/dev/public>
  Options Indexes FollowSymLinks
  AllowOverride all
  Require all granted
</Directory>

ErrorLog /home/<myuser>/dev/apache_logs/error.log
CustomLog /home/<myuser>/dev/apache_logs/access.log combined
----

$ sudo apache2ctl configtest
$ sudo service apache2 reload

$ sudo mysql_secure_installation
follow prompts

$ sudo mysql -u root -p

> update mysql.user set plugin='',password=PASSWORD('<password>') where user='root';
> create user 'webdb_admin'@'localhost' identified by '<password>';
> grant all privileges on *.* to 'webdb_admin'@'localhost';
> create user 'webdb_user'@'localhost' identified by '<password>';
> grant select,insert,update,delete on *.* to 'webdb_user'@'localhost';
> flush privileges;
> set sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
> exit

# systemctl restart mysqld
# mysql -u root -p
> select @@sql_mode;
make sure it is the same as above. if not, append the following to /etc/mysql/mariadb.cnf (as root):
[mysqld]
sql_mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

download http://browscap.org/stream?q=Lite_PHP_BrowsCapINI and save somewhere on server, such as ~/dev/public/lite_php_browscap.ini

# nano /etc/php/7.0/apache2/php.ini
- change filename to suit php version, or whatever loaded configuration file is indicated by phpinfo()

post_max_size = 8M
upload_max_filesize = 8M
allow_url_fopen = Off

[mail function]
sendmail_path = sendmail -t -i

[browscap]
browscap=/home/<user>/dev/public/lite_php_browscap.ini

reboot server


webdb and application setup
===========================

$ sudo dpkg-reconfigure exim4-config
follow prompts: internet site
get external ip address (whatismyip.com) and replace 0.0.0.0 is following command with actual external ip address
$ dig -x 0.0.0.0 @8.8.8.8
result should indicate the hostname from the PTR record (if the address has a PTR record)
if there is only an authority section and no answer section, the address has no PTR record (need to speak with ISP who owns the address)

$ ssk-keygen
follow prompts with default filename and no passphrase
add content of ~/.ssh/id_rsa.pub to new ssh key in webdb repo settings
(or clone/download repo some other way)

$ cd ~/dev/public
$ git clone git@github.com:crutchy-/webdb.git

$ cp ~/dev/public/webdb/doc/webdb_common_settings.php ~/dev/public
$ nano ~/dev/public/webdb_common_settings.php
change values in file to suit

$ mkdir ~/dev/public/my_app
$ mkdir ~/dev/public/my_app/doc
$ mkdir ~/dev/public/my_app/forms
$ mkdir ~/dev/public/my_app/resources
$ mkdir ~/dev/public/my_app/sql
$ mkdir ~/dev/public/my_app/templates

you can use the files under ~/dev/public/webdb/doc/app/ to base a new application on

$ cd ~/dev/public/my_app/sql
$ nano schema.sql
create your application-specific database and table create statements here
note: use snake_case (all lowercase) naming convention for database, table and field names

$ cd ~/dev/public/my_app

$ php index.php init_webdb_schema
executes webdb/sql/schema.sql
(don't run on webdb index.php because requires database access parameters in settings.php)

$ php index.php init_app_schema
executes my_app/sql/schema.sql
(don't run on webdb index.php because requires database access parameters in settings.php)

the following command generatess form list files (including subforms) for all tables in mysql database named <database>
note 1: all existing forms in the application forms directory will be deleted, so if you have created forms that you don't
want to lose be sure to move or back them up prior to running this command
note 2: you may need to tweak the generated forms depending on your naming conventions and specific requirements (the command
will only generate basic configurations based on information from the mysql database
note 3: subforms are generated based on foreign key relationships, except any foreign key that references the same table the
key is defined in is ignored
note 4: tables with multi-field primary keys are ignored except when generating subforms for relationships with other tables
$ php index.php generate_form <database> all


mssql setup
===========

sudo apt-get install php-sybase freetds-common libsybdb5

$pdo=new PDO("dblib:host=$hostname:$port;dbname=$dbname",$dbuser,$dbpassword);
or
$pdo=new PDO("sqlsrv:server=$hostname;database=$dbname",$dbuser,$dbpassword);


install mssql server:
https://docs.microsoft.com/en-us/sql/linux/quickstart-install-connect-ubuntu?view=sql-server-linux-ver15

$ wget -qO- https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
$ sudo add-apt-repository "$(wget -qO- https://packages.microsoft.com/config/ubuntu/16.04/mssql-server-2019.list)"
$ sudo apt-get update
$ sudo apt-get install -y mssql-server
$ sudo /opt/mssql/bin/mssql-conf setup
$ systemctl status mssql-server --no-pager

$ curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list | sudo tee /etc/apt/sources.list.d/msprod.list
$ sudo apt-get install mssql-tools unixodbc-dev
$ echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile

$ sqlcmd -S localhost -U SA -P '<YourPassword>'