// execute update command, return number of modified records
private static int executeUpdate(string sql)
{
using (SQLiteCommand cmd = new SQLiteCommand(sql))
{
return(SQLiteDBMS.execUpdate(cmd));
}
}
// 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;
}
}
}
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 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));
}
}
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));
}
}
// 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));
}
}
