DATA.txt

|------------------------------------------------------------------------|
|                              Introduction                              |
|------------------------------------------------------------------------|
For our database we will be hosting on Microsoft Azure using the standard
tier with 10 DTU's (Data transfer units) and 40GB of total storage.

Our database is the single point of storage or all PlantIQ data.

Database Type:
Azure SQL Server Standard S0: 10 DTUs

Database Connection Address:
db-plantiq.database.windows.net

Azure Portal:
https://portal.azure.com/#@rmiteduau.onmicrosoft.com/resource/subscriptions/a14562c6-514a-4b3d-b6fb-fb71a15d05dd/resourceGroups/PlantIQ/providers/Microsoft.Sql/servers/db-plantiq/databases/plantiq-backend/overview

Note: Please contact a team member if you are unable to access the Azure Portal resource.

|------------------------------------------------------------------------|
|                  How To Connect As An Administrator?                   |
|------------------------------------------------------------------------|

To connect to the database as an "Admin", We can use the Azure web portal or
the "Azure Data Studio" application, the web component of Azure only provides
limit access to create tables and manage data, as such using the Azure Data
Studio is key.

|------------------------------------------------------------------------|
|                       How To Connect The Application                   |
|------------------------------------------------------------------------|

To connect our Java application we can retrieve the JDBC string by going
to the Azure web portal into our database and then from the main menu on
the left and side we can select the "Connection Strings" option, this will
provide us connection strings and examples for Java via JDBC, .Net, ODBC
PHP and GO.

The only other minor item of note is that the Microsoft JDBC SQL package
must also be installed, this can be done via Maven.

https://learn.microsoft.com/en-us/sql/connect/jdbc/download-microsoft-jdbc-driver-for-sql-server?view=sql-server-ver16

|------------------------------------------------------------------------|
|                             TABLE CREATION CODE                        |
|------------------------------------------------------------------------|

Below are the provide SQL table generation code, this can be run via Azure
Data Studio to generate the tables prior to connecting a new instance of
the application to it.

This code is auto generated by Azure Data Studio.

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AwaitingRegistration](
	[id] [varchar](50) NOT NULL,
	[date] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
ALTER TABLE [dbo].[AwaitingRegistration] ADD  CONSTRAINT [PK_AwaitingRegistration] PRIMARY KEY CLUSTERED
(
	[id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Notification](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[user_id] [varchar](50) NULL,
	[timestamp] [int] NULL,
	[message] [varchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[Notification] ADD  CONSTRAINT [PK_Notifications] PRIMARY KEY CLUSTERED
(
	[id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[plantData](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[smarthomehub_Id] [varchar](50) NULL,
	[sensor_Id] [varchar](50) NULL,
	[temperature] [float] NULL,
	[humidity] [float] NULL,
	[light] [float] NULL,
	[moisture] [float] NULL,
	[timestamp] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[plantData] ADD  CONSTRAINT [PK_plantData] PRIMARY KEY CLUSTERED
(
	[id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PlantName](
	[sensor_id] [varchar](50) NOT NULL,
	[user_id] [varchar](50) NULL,
	[name] [varchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
ALTER TABLE [dbo].[PlantName] ADD  CONSTRAINT [PK_PlantName] PRIMARY KEY CLUSTERED
(
	[sensor_id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Range](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[smarthub_id] [varchar](50) NULL,
	[humidity] [varchar](50) NULL,
	[light] [varchar](50) NULL,
	[temperature] [varchar](50) NULL,
	[moisture] [varchar](50) NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Range] ADD  CONSTRAINT [PK_Range] PRIMARY KEY CLUSTERED
(
	[id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[session](
	[token] [varchar](50) NOT NULL,
	[user_id] [varchar](50) NULL,
	[expiry] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
ALTER TABLE [dbo].[session] ADD  CONSTRAINT [PK_session] PRIMARY KEY CLUSTERED
(
	[token] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[smarthomehub](
	[id] [varchar](50) NOT NULL,
	[deviceSpecificPassword] [varchar](50) NULL,
	[name] [varchar](50) NULL,
	[user_id] [varchar](50) NULL,
	[lastPosted] [int] NULL,
	[postFrequency] [int] NULL,
	[virtual] [varchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
ALTER TABLE [dbo].[smarthomehub] ADD  CONSTRAINT [PK_smartHomeHubs] PRIMARY KEY CLUSTERED
(
	[id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[user](
	[id] [varchar](50) NOT NULL,
	[firstname] [varchar](50) NULL,
	[surname] [varchar](50) NULL,
	[email] [varchar](50) NULL,
	[password] [varchar](50) NULL,
	[registrationDate] [int] NULL,
	[isAdministrator] [int] NULL,
	[isActivated] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
ALTER TABLE [dbo].[user] ADD  CONSTRAINT [PK_user] PRIMARY KEY CLUSTERED
(
	[id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO