## 3 Shared Data Access Microservice
### S3 Sample MyMeasurementService Database Initialization  
The database structure and the initial data can be developed in multiple ways. Among these:  
1. Code-first approach--automatically generated by using the JPA annotations;
2. Database-first approach-create and populate the database by using some default SQL scripts. The default SQL script files, which are detected and interpreted by Spring Boot are schema.sql and data.sql. These two files are considered if they are present in the _src\(\backslash\)main\(\backslash\)resources_ project folder.  
In both cases, the database and the connection to it have to be specified in the application.properties project configuration file. The following steps need to be followed in order to create the database and initial data based on the scripts.  
1. Inside the _application.properties_, stop hibernate in order to generate the database schema by setting SPRING.JPA.HIBERNATE.DDL-AUTO=NONE It should be noted that this command will stop Spring Boot from generating the database based on the annotations, and the schema.sql and data.sql files will be considered.
2. Create an empty database on the database server (e.g., MyMeasurementService).
3. Edit the database connection credentials for the project into the _application.properties_ file, in order to connect it to the database server (e.g., PostgreSQL).
4. Create the data definition language script file _schema.sql_. The SQL script with commands for the database structure initialization is located into the project folder _src/main/resources_. Listing A1 presents a sample _schema.sql_ file.
5. Populate the database with the initial data. A data file _data.sql_ is created into the project folder _src/main/resources_. This file contains the SQL queries for inserting data into the database tables. Listing A2 contains sample data for initializing the database.  
Considering the provided measurements, the id = 100 represents a blood pressure meter, and its corresponding unit is mm-Hg (unit id = 20) with two readings per day. The measures can be of different types (systolic and diastolic). The measurements of the device with id = 300 can represent a smart meter gateway reading having Watt (unit id = 75) as consumption unit and as type, the tariff type (e.g., based on readings frequency, for example, monthly readings).  
1. Restart or redeploy the application, and verify the possible error messages displayed on the server console. Notes:  
* It is possible to use spring.jpa.hibernate.ddl-auto=create-drop to force JPA to delete and recreate the database and initial data for each application redeployment/restart;
* Use drop commands to remove the tables and table data if they are not properly generated when starting the project (drop views, tables, sequences, etc.), or remove the database and recreate it as an empty database with the command drop table measurements;
* The drop order is important: firstly, one must delete the tables that use foreign key related data, then the tables with the corresponding primary key data are deleted;
* When inserting the initial data, the order must also be considered in the sense that we cannot create a measurement without having its device already inserted; this rule must be considered when creating the _data.sql_ file;When restarting the application, the database schema must be (manually) deleted so that the JPA/Spring Boot component will be able to recreate it or the setting spring.jpa.hibernate.ddl-auto=update could be used.
2. Each time when needed, the database status can be inspected using the default sql client tool **PGAdmin**. Carefully check that: * All the tables, primary and secondary keys, views, and constraints, which are defined into the _schema.sql_ script file, should be available in the service database; * All the initial data records from _data.sql_ should be available.