public static Scaling getScaling(long tag_id)
{
try
{
using (SQLiteConnection conn = SQLiteDBMS.getConnection())
{
conn.Open();
SQLiteCommand cmd = new SQLiteCommand(
"SELECT * FROM Scaling WHERE tag_id =" + tag_id, conn);
List <Tag> list = new List <Tag>();
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
return(new Scaling(reader.GetString(0), reader.GetDouble(1),
reader.GetDouble(2), reader.GetDouble(3), reader.GetDouble(4)));
}
}
}
}
catch (SQLiteException ex)
{
// table not exist
if (ex.ErrorCode == 1)
{
createScalingTable();
}
}
return(null);
}
// check authentication from database
internal static GPLCAuthority Authenticate(string id, string pass)
{
try
{
using (SQLiteConnection conn = SQLiteDBMS.getConnection())
{
conn.Open();
SQLiteCommand cmd =
new SQLiteCommand("SELECT auth FROM User WHERE id=@id AND pass=@pass", conn);
cmd.Parameters.Add("@id", DbType.String).Value = id;
cmd.Parameters.Add("@pass", DbType.String).Value = CryptoUtil.encryptSHA1(pass);
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
String str = reader.GetString(0);
//str = CryptoUtil.Decrypt(str, id);
return((str.Equals("Administrator")) ? GPLCAuthority.Administrator : GPLCAuthority.Operator);
}
else
{
throw new WrongIdPassException();
}
}
}
}
catch (SQLiteException ex)
{
if (ex.ErrorCode == 1)
{
createSchema();
}
}
return(GPLCAuthority.Anonymous);
}
/* method for bulk insert (not tested yet)
* using transaction to achieve this demand
* a transaction is an atomic sql operation
* operations in a transaction are zero-or-none.
* */
public static void insertTag(List <Tag> list, long plc_id)
{
using (SQLiteConnection conn = SQLiteDBMS.getConnection())
{
using (SQLiteTransaction transaction = conn.BeginTransaction())
{
using (SQLiteCommand cmd = conn.CreateCommand())
{
cmd.CommandText =
"INSERT INTO Tag (alias, addr, data_type, format, unit, plc_id) "
+ "values (@alias, @addr, @data_type, @format, @unit, @plc_id)";
cmd.Parameters.Add("@alias", DbType.String);
cmd.Parameters.Add("@addr", DbType.Int32);
cmd.Parameters.Add("@data_type", DbType.String);
cmd.Parameters.Add("@format", DbType.String);
cmd.Parameters.Add("@unit", DbType.String);
foreach (Tag tag in list)
{
insertTag(cmd, tag, plc_id);
}
}
transaction.Commit();
}
}
}
// create a new user
internal static void newUser(string id, string pass, GPLCAuthority auth)
{
try
{
using (SQLiteCommand sql = new SQLiteCommand("INSERT INTO User values(@id, @pass, @auth)"))
{
sql.Parameters.Add("@id", DbType.String).Value = id;
sql.Parameters.Add("@pass", DbType.String).Value = CryptoUtil.encryptSHA1(pass);
sql.Parameters.Add("@auth", DbType.String).Value = auth.ToString();
SQLiteDBMS.execUpdate(sql);
}
}
catch (SQLiteException ex)
{
switch (ex.ErrorCode)
{
case 1:
createSchema();
newUser(id, pass, auth);
break;
case 19:
default:
break;
}
}
}
// execute update command, return number of modified records
private static int executeUpdate(string sql)
{
using (SQLiteCommand cmd = new SQLiteCommand(sql))
{
return(SQLiteDBMS.execUpdate(cmd));
}
}
public static List <Tag> getTagList(long plc_id)
{
try
{
using (SQLiteConnection conn = SQLiteDBMS.getConnection())
{
conn.Open();
SQLiteCommand cmd = new SQLiteCommand(
"SELECT * FROM Tag WHERE plc_id =" + plc_id, conn);
List <Tag> list = new List <Tag>();
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
list.Add(new Tag(reader.GetInt64(0), reader.GetString(1),
reader.GetInt32(2), reader.GetString(3), reader.GetString(4),
reader.GetString(5), reader.GetInt64(6)
));
}
}
return(list);
}
}
catch (SQLiteException ex)
{
// table not exist
if (ex.ErrorCode == 1)
{
createTagTable();
}
}
return(new List <Tag>());
}
public static int deleteScaling(long tag_id)
{
using (SQLiteCommand cmd = new SQLiteCommand(
"delete FROM Scaling WHERE tag_id=@tag_id"))
{
cmd.Parameters.Add("@tag_id", DbType.Int64).Value = tag_id;
return(SQLiteDBMS.execUpdate(cmd));
}
}
public static int deletePLC(long id)
{
// delete a plc also delete its tags
deleteTags(id);
using (SQLiteCommand cmd = new SQLiteCommand(
"delete FROM PLC WHERE id=@id"))
{
cmd.Parameters.Add("@id", DbType.Int64).Value = id;
return(SQLiteDBMS.execUpdate(cmd));
}
}
public static int deleteTag(long id)
{
// delete a tag also delete its scale info
deleteScaling(id);
using (SQLiteCommand cmd = new SQLiteCommand(
"delete FROM Tag WHERE id=@id"))
{
cmd.Parameters.Add("@id", DbType.Int64).Value = id;
return(SQLiteDBMS.execUpdate(cmd));
}
}
// get row ID of the last inserted record
private static int getLastInsertRowId()
{
using (SQLiteConnection conn = SQLiteDBMS.getConnection())
{
conn.Open();
SQLiteCommand cmd = new SQLiteCommand("select last_insert_rowid()", conn);
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
return((reader.Read()) ? reader.GetInt32(0) : -1);
}
}
}
public static int insertProject(long id, string name, string addr, double lat, double lng)
{
using (SQLiteCommand cmd = new SQLiteCommand(
"INSERT INTO Project values (@id, @name, @addr, @lat, @lng)"))
{
cmd.Parameters.Add("@id", DbType.Int64).Value = id;
cmd.Parameters.Add("@name", DbType.String).Value = name;
cmd.Parameters.Add("@addr", DbType.String).Value = addr;
cmd.Parameters.Add("@lat", DbType.Double).Value = lat;
cmd.Parameters.Add("@lng", DbType.Double).Value = lng;
return(SQLiteDBMS.execInsert(cmd));
}
}
// create user schema
private static void createSchema()
{
try
{
using (SQLiteCommand sql = new SQLiteCommand("CREATE TABLE User (id varchar(20) PRIMARY KEY, pass text, auth text)"))
{
SQLiteDBMS.execUpdate(sql);
}
}
catch (SQLiteException)
{
}
}
private static int deletePLCs(long project_id)
{
// delete PLCs also delete their tags
foreach (PLC plc in getPLCList(project_id))
{
deleteTags(plc.id);
}
using (SQLiteCommand cmd = new SQLiteCommand(
"delete FROM PLC WHERE project_id=@project_id"))
{
cmd.Parameters.Add("@project_id", DbType.Int64).Value = project_id;
return(SQLiteDBMS.execUpdate(cmd));
}
}
/* update methods for model
* using parameters instead of directly string utility
* can avoid malicious operation such as SQL injection.
* */
public static int updateProject(long id, string name, string addr, double lat, double lng, long oid)
{
using (SQLiteCommand cmd = new SQLiteCommand(
"UPDATE Project SET id=@id, name=@name, addr=@addr, lat=@lat, lng=@lng WHERE id=@oid"))
{
cmd.Parameters.Add("@id", DbType.Int64).Value = id;
cmd.Parameters.Add("@name", DbType.String).Value = name;
cmd.Parameters.Add("@addr", DbType.String).Value = addr;
cmd.Parameters.Add("@lat", DbType.Double).Value = lat;
cmd.Parameters.Add("@lng", DbType.Double).Value = lng;
cmd.Parameters.Add("@oid", DbType.Int64).Value = oid;
return(SQLiteDBMS.execUpdate(cmd));
}
}
private static int deleteTags(long plc_id)
{
// delete tags also delete their scale info
foreach (Tag tag in getTagList(plc_id))
{
deleteScaling(tag.id);
}
// delete items belonging to the plc
using (SQLiteCommand cmd = new SQLiteCommand(
"delete FROM Tag WHERE plc_id=@plcid"))
{
cmd.Parameters.Add("@plcid", DbType.Int64).Value = plc_id;
return(SQLiteDBMS.execUpdate(cmd));
}
}
public static int updateScaling(string scale_type, double raw_hi, double raw_lo, double scale_hi, double scale_lo, long tag_id)
{
using (SQLiteCommand cmd = new SQLiteCommand(
"UPDATE Scaling SET scale_type=@scale_type, raw_hi=@raw_hi, raw_lo=@raw_lo,"
+ " scale_hi=@scale_hi, scale_lo=@scale_lo WHERE tag_id=@tag_id"))
{
cmd.Parameters.Add("@scale_type", DbType.String).Value = scale_type.ToString();
cmd.Parameters.Add("@raw_hi", DbType.Double).Value = raw_hi;
cmd.Parameters.Add("@raw_lo", DbType.Double).Value = raw_lo;
cmd.Parameters.Add("@scale_hi", DbType.Double).Value = scale_hi;
cmd.Parameters.Add("@scale_lo", DbType.Double).Value = scale_lo;
cmd.Parameters.Add("@tag_id", DbType.Int64).Value = tag_id;
return(SQLiteDBMS.execUpdate(cmd));
}
}
public static int updateTag(long id, string alias, int addr, DataType data_type, string format, string unit)
{
using (SQLiteCommand cmd = new SQLiteCommand(
"UPDATE Tag SET alias=@alias, addr=@addr, data_type=@data_type, format=@format, unit=@unit"
+ " WHERE id=@id"))
{
cmd.Parameters.Add("@alias", DbType.String).Value = alias;
cmd.Parameters.Add("@addr", DbType.Int32).Value = addr;
cmd.Parameters.Add("@data_type", DbType.String).Value = data_type.ToString();
cmd.Parameters.Add("@format", DbType.String).Value = format;
cmd.Parameters.Add("@unit", DbType.String).Value = unit;
cmd.Parameters.Add("@id", DbType.Int64).Value = id;
return(SQLiteDBMS.execUpdate(cmd));
}
}
public static int updatePLC(long id, int netid, string ip, int port, string alias, int polling_rate)
{
using (SQLiteCommand cmd = new SQLiteCommand(
"UPDATE PLC SET net_id=@net_id, net_ip=@net_ip, net_port=@net_port, alias=@alias,"
+ " polling_rate=@polling_rate WHERE id=@id"))
{
cmd.Parameters.Add("@id", DbType.Int64).Value = id;
cmd.Parameters.Add("@net_id", DbType.Int32).Value = netid;
cmd.Parameters.Add("@net_ip", DbType.String).Value = ip;
cmd.Parameters.Add("@net_port", DbType.Int32).Value = port;
cmd.Parameters.Add("@alias", DbType.String).Value = alias;
cmd.Parameters.Add("@polling_rate", DbType.Int32).Value = polling_rate;
return(SQLiteDBMS.execUpdate(cmd));
}
}
public static int insertPLC(string alias, int netid, string ip, int port, int polling_rate, long project_id)
{
using (SQLiteCommand cmd = new SQLiteCommand(
"INSERT INTO PLC (net_id, net_ip, net_port, alias, polling_rate, project_id) "
+ "values (@net_id, @net_ip, @net_port, @alias, @polling_rate, @project_id)"))
{
cmd.Parameters.Add("@net_id", DbType.Int32).Value = netid;
cmd.Parameters.Add("@net_ip", DbType.String).Value = ip;
cmd.Parameters.Add("@net_port", DbType.Int32).Value = port;
cmd.Parameters.Add("@alias", DbType.String).Value = alias;
cmd.Parameters.Add("@polling_rate", DbType.Int32).Value = polling_rate;
cmd.Parameters.Add("@project_id", DbType.Int64).Value = project_id;
return(SQLiteDBMS.execInsert(cmd));
}
}
public static int insertTag(string alias, int addr, DataType type, string format, string unit, long plc_id)
{
using (SQLiteCommand cmd = new SQLiteCommand(
"INSERT INTO Tag (alias, addr, data_type, format, unit, plc_id) "
+ "values (@alias, @addr, @data_type, @format, @unit, @plc_id)"))
{
cmd.Parameters.Add("@alias", DbType.String).Value = alias;
cmd.Parameters.Add("@addr", DbType.Int32).Value = addr;
cmd.Parameters.Add("@data_type", DbType.String).Value = type.ToString();
cmd.Parameters.Add("@format", DbType.String).Value = format;
cmd.Parameters.Add("@unit", DbType.String).Value = unit;
// foreigh
cmd.Parameters.Add("@plc_id", DbType.Int64).Value = plc_id;
return(SQLiteDBMS.execInsert(cmd));
}
}
public static int insertScaling(string scale_type, double raw_hi, double raw_lo, double scale_hi, double scale_lo, long tag_id)
{
using (SQLiteCommand cmd = new SQLiteCommand(
"INSERT INTO Scaling (scale_type, raw_hi, raw_lo, scale_hi, scale_lo, tag_id) "
+ "values (@scale_type, @raw_hi, @raw_lo, @scale_hi, @scale_lo, @tag_id)"))
{
// scale
cmd.Parameters.Add("@scale_type", DbType.String).Value = scale_type.ToString();
cmd.Parameters.Add("@raw_hi", DbType.Double).Value = raw_hi;
cmd.Parameters.Add("@raw_lo", DbType.Double).Value = raw_lo;
cmd.Parameters.Add("@scale_hi", DbType.Double).Value = scale_hi;
cmd.Parameters.Add("@scale_lo", DbType.Double).Value = scale_lo;
cmd.Parameters.Add("@tag_id", DbType.Int64).Value = tag_id;
return(SQLiteDBMS.execInsert(cmd));
}
}
/* methods that get list of model
* */
public static List <ProjectData> getProjectList()
{
try
{
List <ProjectData> pList = new List <ProjectData>();
using (SQLiteConnection conn = SQLiteDBMS.getConnection())
{
conn.Open();
SQLiteCommand cmd = new SQLiteCommand("SELECT * FROM Project", conn);
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
// get column values
pList.Add(
new ProjectData(reader.GetInt64(0), reader.GetString(1),
reader.GetString(2), reader.GetDouble(3), reader.GetDouble(4), null)
);
}
}
}
return(pList);
}
catch (SQLiteException ex)
{
// table not exist
if (ex.ErrorCode == 1)
{
createProjectTable();
}
return(new List <ProjectData>());
}
catch (Exception)
{
return(new List <ProjectData>());
}
}
public static List <PLC> getPLCList(long project_id)
{
try
{
using (SQLiteConnection conn = SQLiteDBMS.getConnection())
{
conn.Open();
SQLiteCommand cmd = new SQLiteCommand(
"SELECT * FROM PLC WHERE project_id=" + project_id, conn);
List <PLC> pList = new List <PLC>();
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
long id = reader.GetInt64(0);
PLC p = new PLC(id, reader.GetString(1),
reader.GetInt32(2), reader.GetString(3),
reader.GetInt32(4), reader.GetInt32(5), null);
pList.Add(p);
}
}
return(pList);
}
}
catch (SQLiteException ex)
{
// table not exist
if (ex.ErrorCode == 1)
{
createPLCTable();
}
return(new List <PLC>());
}
catch (Exception)
{
return(new List <PLC>());
}
}
// copy current db file to specified path
public static void copyTo(string path)
{
SQLiteDBMS.copyTo(path);
}
// set db file path
public static void setPath(string path)
{
SQLiteDBMS.setDBPath(path);
}
