Garden Notebook Version 3.1.2

Database guidance

GardenNotebookModel_3_Release.mwb is the database model for the GardenNotebook application and can be viewed in the MySQL Workbench tool.

To use the embedded hsqldb database:
	- GardenNotebook_HSQL_3_0_0.sql is SQL which will create the schema for the application.
	- DB_inserts_HSQL.sql is SQL to place basic information into the database for the application.
	- DB_inserts_HSQL_local.sql is SQL to add some commonly encountered plants to the database.

For MySQL and MariaDB users:
	- GardenNotebook_MySQL_3_0_0.sql is SQL which will create the schema for the application.
	- DB_inserts.sql is SQL to place basic information into the database for the application.
	- DB_inserts_local.sql is SQL to add some commonly encountered plants to the database.

For MS SQLServer users:
	- GardenNotebook_SQLServer_3_0_0.sql is SQL which will create the schema for the application.
	- DB_inserts_SQLServer.sql is SQL to place basic information into the database for the application.
	- DB_inserts_SQLServer_local.sql is SQL to add some commonly encountered plants to the database.

To use hsqldb (preferred)
-------------------------
I have implemented an in-process database manager using the hsqldb libraries (see hsqldb.org).
This runs entirely in-process on a file based database.  This does not allow multiple simultaneous users;
if you want several people to be able to use the files make sure you put them in a shared location with appropriate permissions.
Only one user at a time will be allowed to access the database.  I chose to implement it this way to keep everything as simple as possible.

After installing the GardenNotebook application you need to initialise the hsqldb database; this can be done using the files mentioned 
above but it is much easier to let the application do it for you through the normal Configuration page.
If you want to, and I strongly encourage you NOT to, you can initialise the database by hand using the sqltool library available from hsqldb.org; 
the HsqlDB_Builder.bat file can be used to do this.  You will need to provide the name of a folder (directory) to hold the files and 
the name of the database (default gardennotebnook).  Whichever method you use the username will be SA (the default user) 
with a blank password.  You can change this after installation using the sqltool (refer to the hsqldb.org website for details).

To use MS SQLServer
-------------------
Make sure the necessary services are running, namely the Server Agent and the Server Browser; from the Control Panel, Administrative Tools then Services.
If the services are NOT running Start them or set their Startup Type as Automatic; you need to run as Administrator to do this.  Be aware of Microsoft's warning about having the Browser start automatically.
You also need to ensure the database instance is listening for TCP/IP connections.  In C:\Windows\System32 find SQLServerManager14.msc and run it as Administrator.  Under SQL SERVER NetworkConfiguration check Protocols for your Server instance and ensure that TCP/IP is enabled in the right hand panel.
You need to download and install the latest MS SQLServer JDBC connector from the Microsoft website:
https://docs.microsoft.com/en-us/sql/connect/jdbc/download-microsoft-jdbc-driver-for-sql-server?view=sql-server-ver16
(or Google 'sqlserver jdbc')
Add the jar to the CLASSPATH environment variable, typically this is:
C:\Program Files\sqljdbc_12.2_enu\sqljdbc_12.2\enu\mssql-jdbc-12.2.0.jre11.jar
(see below for how to do this.)  Use the Java 19 version, as shown here.

To create the initial database, you can either use the SQLServer Management Studio to execute the files listed above (in the order given) or use the DB_Builder.bat tool included in this directory:
DB_builder /D sqlserver /u <user> /w <password> /p <port> /h <host>
The value for <port> is usually 1433; the default admin <user> is sa.
(This runs surprisingly quickly.)

New users (Windows) wishing to use MySQL or MariaDB
---------------------------------------------------
Download and install MySQL 8 from the MySQL web site (mysql.com).
Go to 'Downloads' then 'MySQL on Windows (Installer & Tools)' then 'MySQL Installer'.
Make sure you download the Connector/J connector (the application uses this to talk to the database).

A note: It is possible to install MySQL 8.0.20 and later on a Windows 10 Home PC but it is not as easy as I'd like.  The installer will probably 
fail to install some components - particularly the server (which is the most essential part!)  If this happens, you can start the installer 
from your PC's Start menu, under MySQL.  I recommend asking it to install the server separately (untick any other options and do those afterwards).
When you configure the server it's important to select the option for 'lower case file/table names' and, very important, the 
Security option for old style security - this is NOT the recommended default.  Set the server to run as a Windows service when asked.  You 
can use the Control Panel later to change whether or not the service should run on startup.  The notification tool (which you can install 
with the MySQL Installer) makes it easy to start and stop the service - if the notifier itself installs properly.

Alternatively, download and install MariaDB from the MariaDB website (mariadb.com) and follow the installation instructions.
Many NAS devices offer MariaDB as an 'app'; the database initialisation procedure below supports LAN connected database servers.

Follow the installation wizard for a 'development machine' - this installs both client and server components
on your PC.  If you want to run the server on a different machine, you already know what you're doing! 
Note that the installation will add a 'Notifier' to the System Tray on your PC (probably under the ^ extender) 
which you can use to start and stop the database server. 
You will need to set up a user on the database.  The installation process will create the master user 'root'; 
if you are the only person using the database that is sufficient for our purposes.  The application actually
uses your log-in name as the default user so you may wish to add that name - you will need at least one name
with sufficient privilege (SUPER) to delete and create schemas (which root has).

You need to add the Connector/J component to the CLASSPATH environment variable.
Open Windows File Explorer and navigate to the folder where Connector/J has been installed, typically
"C:\Program Files (x86)\MySQL\Connector J 8.0.33"
or the MariaDB equivalent, currently version 3.1.4.
In the search bar type env and select 'Edit environment variables for your account'.
If there is already a CLASSPATH variable select it and click Edit.
Click 'New' then 'Browse' and navigate to the folder where Connector/J has been installed and click 'OK'.
In the file browser, separately, navigate to this same folder and into that folder.
There will be an entry similar to 'mysql-connector-java-8.0.33.jar', use the slow double click to highlight the name
for selection (include the .jar) and copy the name.  In the environment variable editting window, append this name
to the entry you have just created.
There should now be an entry similar to:
C:\Program Files (x86)\MySQL\Connector J 8.0\mysql-connector-java-8.0.33.jar
(note that the spaces are important)

If there was NOT a CLASSPATH variable, click 'New', give CLASSPATH as the 'Variable Name' the click 'Browse File' and proceed as above.

Click 'OK'.

Please note that if you are using MySQL you need the MySQL version of Connector/J; if you are using MariaDB you need the
MariaDB version of Connector/J.  It is perfectly acceptable to have both in your CLASSPATH.
With MySQL 8, the MySQL connector will work with MariaDB 10 databases, and vice versa.  This can be very confusing - you can be in a
situation where you don't know which connector is talking to which database (this is the voice of bitter experience...).

***********************************************************************************************************************
IMPORTANT There is a bug in the MariaDB Connector/J versions 2.7.1, 2.7.2 and 2.7.3 which stops them working.
The 3.* series of Connectors throw a warning that there is a missing service but the code runs without problem.
***********************************************************************************************************************

You need to create the database for the GardenNotebook application.  The 3 .sql files in this folder will do this and 
populate the database with some useful information (all of which you can change later).

The batch file DB_Builder.bat will do this for you.  
It has a number of options which can be found by entering
DB_Builder /?
at a cmd.exe (terminal) window.  The default values are for MySQL on your current PC (localhost).  You must enter 
at least a password.
(This typically takes a couple of minutes to run.)
Please note that the user you give must have SUPER privileges, the default is 'root'.
You will also need to add the mysql command line tool to the PATH (mysql installation used to do this for you). This can typically be found at
C:\Program Files\MySQL\MySQL Shell 8.0\bin.  (It may already be there after installing MySQL.)

Existing users wishing to use MySQL or MariaDB
----------------------------------------------
This release SHOULD work with MySQL 5.7.

Make sure you have Connector/J on the CLASSPATH (see 'New users', above, for details).
MySQL recommend using Connector/J 8.

If you want to upgrade to MySQL 8 there are a couple of things to note.
Firstly, MySQL 8 is much stricter on using Security Certificates on the SSL connection between client and server.
I have not implemented this but have forced an unchecked connection.  This is OK if client and server are on the 
same PC.  More work is required here.

Secondly, MySQL 8 has changed the default values on TIMESTAMPs.  Every record in this application has a 'created' timestamp 
(which is unaffected by the change) and a 'lastUpdated' timestamp.  Under MySQL 5.7 this latter field had no DEFAULT value which
meant it was automatically updated every time the record was changed; this is no longer the case.  The database model and creation
SQL included here have changed this field to the appropriate DEFAULT values for version 2.1.0 of GardenNotebook but I have not included 
a script to update the pre-2.1.0 database.

If you have data for this application in a MySQL 5.7 database that you wish to retain, I suggest you do one of the following:
Either 
disable the explicit_defaults_for_timestamp system variable and check if you can create a new entry
(see https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_explicit_defaults_for_timestamp ) 

Or (preferable)
Back up your database using the MySQL tools.
Run the GardenNotebook application and request a JSON dump to a known folder.
run DB_Builder.bat with the /n switch -
DB_Builder /n
This will create an EMPTY database with appropriate settings.
Now run the application again and request 'Load from JSON' with the dump files previously created.

I did attempt to upgrade my own database following the MySQL documentation but failed completely; I just deleted it and started again.

