345 lines
8.5 KiB
Go
345 lines
8.5 KiB
Go
package main
|
|
|
|
import (
|
|
"database/sql"
|
|
"fmt"
|
|
"log"
|
|
"time"
|
|
)
|
|
|
|
// Tag : holds information about a specific Tag
|
|
type Tag struct {
|
|
ID int `json:"id"`
|
|
Name string `json:"name"`
|
|
TagClassID int `json:"tagClassId"`
|
|
TagClass TagClass `json:"tagClass"`
|
|
TagName string `json:"tagName"`
|
|
DeviceID int `json:"deviceId"`
|
|
Device Device `json:"device"`
|
|
Description string `json:"description"`
|
|
DataTypeID int `json:"dataTypeId"`
|
|
DataType DataType `json:"dataType"`
|
|
ChangeThreshold float32 `json:"changeThreshold"`
|
|
GuaranteeSec int `json:"guaranteeSec"`
|
|
MapFunction string `json:"mapFunction"`
|
|
Units string `json:"units"`
|
|
MinExpected float32 `json:"minExpected"`
|
|
MaxExpected float32 `json:"maxExpected"`
|
|
CreatedAt time.Time `json:"createdAt"`
|
|
UpdatedAt time.Time `json:"updatedAt"`
|
|
}
|
|
|
|
// Tags : a list of Tag items
|
|
type Tags []Tag
|
|
|
|
const tagTableCreationQuery = `CREATE TABLE IF NOT EXISTS tags (
|
|
id int(10) unsigned NOT NULL AUTO_INCREMENT,
|
|
name varchar(255),
|
|
tagName varchar(255),
|
|
tagClassId int(10) unsigned,
|
|
deviceId int(10) unsigned,
|
|
description varchar(255),
|
|
dataTypeId int(10) unsigned,
|
|
changeThreshold float,
|
|
guaranteeSec int(10) unsigned,
|
|
mapFunction varchar(255),
|
|
units varchar(255),
|
|
minExpected float,
|
|
maxExpected float,
|
|
createdAt datetime,
|
|
updatedAt datetime,
|
|
PRIMARY KEY (id),
|
|
CONSTRAINT fk_tagclass FOREIGN KEY (tagClassId) REFERENCES tagClasses(id),
|
|
CONSTRAINT fk_device FOREIGN KEY (deviceId) REFERENCES devices(id),
|
|
CONSTRAINT fk_datatype FOREIGN KEY (dataTypeId) REFERENCES dataTypes(id)
|
|
);`
|
|
|
|
const latestTagValueCreationQuery = `CREATE TABLE IF NOT EXISTS latestValues (
|
|
id int(10) unsigned NOT NULL AUTO_INCREMENT,
|
|
tagId int(10) unsigned,
|
|
val varchar(64),
|
|
createdAt datetime,
|
|
updatedAt datetime,
|
|
PRIMARY KEY (id),
|
|
CONSTRAINT fk_tagId FOREIGN KEY (tagId) REFERENCES tags(id) ON DELETE CASCADE
|
|
);`
|
|
|
|
func ensureTagTableExists(db *sql.DB) {
|
|
if _, err := db.Exec(tagTableCreationQuery); err != nil {
|
|
log.Fatal(err)
|
|
}
|
|
}
|
|
|
|
func ensureLatestTagValueTableExists(db *sql.DB) {
|
|
if _, err := db.Exec(latestTagValueCreationQuery); err != nil {
|
|
log.Fatal(err)
|
|
}
|
|
}
|
|
|
|
// getTag : used during GET command
|
|
func (t *Tag) getTag(db *sql.DB) error {
|
|
sqlQuery := `SELECT
|
|
tags.name,
|
|
tags.tagName,
|
|
tags.tagClassId,
|
|
tagClasses.id,
|
|
tagClasses.classType,
|
|
tagClasses.description,
|
|
tagClasses.updatedAt,
|
|
tagClasses.createdAt,
|
|
tags.deviceId,
|
|
devices.id,
|
|
devices.name,
|
|
devices.deviceTypeId,
|
|
deviceTypes.id,
|
|
deviceTypes.name,
|
|
deviceTypes.createdAt,
|
|
deviceTypes.updatedAt,
|
|
devices.address,
|
|
devices.createdAt,
|
|
devices.updatedAt,
|
|
tags.description,
|
|
tags.dataTypeId,
|
|
dataTypes.id,
|
|
dataTypes.dataType,
|
|
dataTypes.plcType,
|
|
dataTypes.updatedAt,
|
|
dataTypes.createdAt,
|
|
tags.changeThreshold,
|
|
tags.guaranteeSec,
|
|
tags.mapFunction,
|
|
tags.units,
|
|
tags.maxExpected,
|
|
tags.minExpected,
|
|
tags.createdAt,
|
|
tags.updatedAt
|
|
FROM tags
|
|
JOIN tagClasses ON tags.tagClassId = tagClasses.id
|
|
JOIN devices ON tags.deviceId = devices.id
|
|
JOIN deviceTypes ON devices.deviceTypeId = deviceTypes.id
|
|
JOIN dataTypes ON tags.dataTypeId = dataTypes.id
|
|
WHERE tags.id=?`
|
|
return db.QueryRow(sqlQuery, t.ID).Scan(
|
|
&t.Name,
|
|
&t.TagName,
|
|
&t.TagClassID,
|
|
&t.TagClass.ID,
|
|
&t.TagClass.ClassType,
|
|
&t.TagClass.Description,
|
|
&t.TagClass.UpdatedAt,
|
|
&t.TagClass.UpdatedAt,
|
|
&t.DeviceID,
|
|
&t.Device.ID,
|
|
&t.Device.Name,
|
|
&t.Device.DeviceTypeID,
|
|
&t.Device.DeviceType.ID,
|
|
&t.Device.DeviceType.Name,
|
|
&t.Device.DeviceType.CreatedAt,
|
|
&t.Device.DeviceType.UpdatedAt,
|
|
&t.Device.Address,
|
|
&t.Device.CreatedAt,
|
|
&t.Device.UpdatedAt,
|
|
&t.Description,
|
|
&t.DataTypeID,
|
|
&t.DataType.ID,
|
|
&t.DataType.DataType,
|
|
&t.DataType.PlcType,
|
|
&t.DataType.UpdatedAt,
|
|
&t.DataType.CreatedAt,
|
|
&t.ChangeThreshold,
|
|
&t.GuaranteeSec,
|
|
&t.MapFunction,
|
|
&t.Units,
|
|
&t.MaxExpected,
|
|
&t.MinExpected,
|
|
&t.CreatedAt,
|
|
&t.UpdatedAt)
|
|
}
|
|
|
|
// updateTag : used during PUT command
|
|
func (t *Tag) updateTag(db *sql.DB) error {
|
|
sqlQuery := `UPDATE tags SET
|
|
name=?,
|
|
tagName=?,
|
|
tagClassId=?,
|
|
deviceId=?,
|
|
description=?,
|
|
dataTypeId=?,
|
|
changeThreshold=?,
|
|
guaranteeSec=?,
|
|
mapFunction=?,
|
|
units=?,
|
|
minExpected=?,
|
|
maxExpected=?,
|
|
updatedAt=?
|
|
WHERE id=?`
|
|
updStmt, updErr := db.Prepare(sqlQuery)
|
|
if updErr != nil {
|
|
panic(updErr.Error()) // proper error handling instead of panic in your app
|
|
}
|
|
defer updStmt.Close() // Close the statement when we leave main() / the program terminates
|
|
|
|
_, err := updStmt.Exec(t.Name, t.TagName, t.TagClassID, t.DeviceID,
|
|
t.Description, t.DataTypeID, t.ChangeThreshold, t.GuaranteeSec,
|
|
t.MapFunction, t.Units, t.MinExpected, t.MaxExpected, time.Now(), t.ID)
|
|
return err
|
|
}
|
|
|
|
// deleteTag : used during DELETE command
|
|
func (t *Tag) deleteTag(db *sql.DB) error {
|
|
_, err := db.Exec("DELETE FROM tags WHERE id=?", t.ID)
|
|
return err
|
|
}
|
|
|
|
// createTag : used during PUSH command
|
|
func (t *Tag) createTag(db *sql.DB) error {
|
|
tagSQLQuery := `INSERT INTO tags (
|
|
name,
|
|
tagName,
|
|
tagClassId,
|
|
deviceId,
|
|
description,
|
|
dataTypeId,
|
|
changeThreshold,
|
|
guaranteeSec,
|
|
mapFunction,
|
|
units,
|
|
minExpected,
|
|
maxExpected,
|
|
createdAt,
|
|
updatedAt) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`
|
|
|
|
valSQLQuery := `INSERT INTO latestValues (tagId, val, createdAt, updatedAt) VALUES (?, ?, ?, ?)`
|
|
|
|
stmtIns, insErr := db.Prepare(tagSQLQuery)
|
|
if insErr != nil {
|
|
panic(insErr.Error()) // proper error handling instead of panic in your app
|
|
}
|
|
defer stmtIns.Close() // Close the statement when we leave main() / the program terminates
|
|
|
|
stmtInsVal, insValErr := db.Prepare(valSQLQuery)
|
|
if insValErr != nil {
|
|
panic(insValErr.Error()) // proper error handling instead of panic in your app
|
|
}
|
|
defer stmtInsVal.Close() // Close the statement when we leave main() / the program terminates
|
|
|
|
res, err := stmtIns.Exec(t.Name, t.TagName, t.TagClassID, t.DeviceID,
|
|
t.Description, t.DataTypeID, t.ChangeThreshold, t.GuaranteeSec,
|
|
t.MapFunction, t.Units, t.MinExpected, t.MaxExpected, time.Now(), time.Now())
|
|
|
|
lastID, idErr := res.LastInsertId()
|
|
if idErr == nil {
|
|
_, valErr := stmtInsVal.Exec(lastID, "0.0", time.Now(), time.Now())
|
|
return valErr
|
|
}
|
|
fmt.Printf("\n%s -- %d\n", "Invalid last inserted id", lastID)
|
|
|
|
return err
|
|
}
|
|
|
|
// getTags : used during GET command for all
|
|
func getTags(db *sql.DB, start, count int) (Tags, error) {
|
|
sqlQuery := `SELECT
|
|
tags.ID,
|
|
tags.name,
|
|
tags.tagName,
|
|
tags.tagClassId,
|
|
tagClasses.id,
|
|
tagClasses.classType,
|
|
tagClasses.description,
|
|
tagClasses.updatedAt,
|
|
tagClasses.createdAt,
|
|
tags.deviceId,
|
|
devices.id,
|
|
devices.name,
|
|
devices.deviceTypeId,
|
|
deviceTypes.id,
|
|
deviceTypes.name,
|
|
deviceTypes.createdAt,
|
|
deviceTypes.updatedAt,
|
|
devices.address,
|
|
devices.createdAt,
|
|
devices.updatedAt,
|
|
tags.description,
|
|
tags.dataTypeId,
|
|
dataTypes.id,
|
|
dataTypes.dataType,
|
|
dataTypes.plcType,
|
|
dataTypes.updatedAt,
|
|
dataTypes.createdAt,
|
|
tags.changeThreshold,
|
|
tags.guaranteeSec,
|
|
tags.mapFunction,
|
|
tags.units,
|
|
tags.maxExpected,
|
|
tags.minExpected,
|
|
tags.createdAt,
|
|
tags.updatedAt
|
|
FROM tags
|
|
JOIN tagClasses ON tags.tagClassId = tagClasses.id
|
|
JOIN devices ON tags.deviceId = devices.id
|
|
JOIN deviceTypes ON devices.deviceTypeId = deviceTypes.id
|
|
JOIN dataTypes ON tags.dataTypeId = dataTypes.id
|
|
LIMIT ? OFFSET ?;`
|
|
|
|
getStmt, prepErr := db.Prepare(sqlQuery)
|
|
if prepErr != nil {
|
|
panic(prepErr.Error()) // proper error handling instead of panic in your app
|
|
}
|
|
defer getStmt.Close() // Close the statement when we leave main() / the program terminates
|
|
|
|
rows, err := getStmt.Query(count, start)
|
|
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
|
|
defer rows.Close()
|
|
|
|
tags := Tags{}
|
|
|
|
for rows.Next() {
|
|
var t Tag
|
|
if err := rows.Scan(&t.ID,
|
|
&t.Name,
|
|
&t.TagName,
|
|
&t.TagClassID,
|
|
&t.TagClass.ID,
|
|
&t.TagClass.ClassType,
|
|
&t.TagClass.Description,
|
|
&t.TagClass.UpdatedAt,
|
|
&t.TagClass.UpdatedAt,
|
|
&t.DeviceID,
|
|
&t.Device.ID,
|
|
&t.Device.Name,
|
|
&t.Device.DeviceTypeID,
|
|
&t.Device.DeviceType.ID,
|
|
&t.Device.DeviceType.Name,
|
|
&t.Device.DeviceType.CreatedAt,
|
|
&t.Device.DeviceType.UpdatedAt,
|
|
&t.Device.Address,
|
|
&t.Device.CreatedAt,
|
|
&t.Device.UpdatedAt,
|
|
&t.Description,
|
|
&t.DataTypeID,
|
|
&t.DataType.ID,
|
|
&t.DataType.DataType,
|
|
&t.DataType.PlcType,
|
|
&t.DataType.UpdatedAt,
|
|
&t.DataType.CreatedAt,
|
|
&t.ChangeThreshold,
|
|
&t.GuaranteeSec,
|
|
&t.MapFunction,
|
|
&t.Units,
|
|
&t.MaxExpected,
|
|
&t.MinExpected,
|
|
&t.CreatedAt,
|
|
&t.UpdatedAt); err != nil {
|
|
return nil, err
|
|
}
|
|
tags = append(tags, t)
|
|
}
|
|
|
|
return tags, nil
|
|
}
|