public static Map Select(IDbConnection dbConn, String name)
{
IDataReader reader = null;
try
{
IDbCommand command = dbConn.CreateCommand();
command.CommandText = SELECT_BY_NAME;
DbUtil.AddParameter(command, "@name", name);
reader = command.ExecuteReader();
if (reader == null)
{
return(null);
}
if (!reader.Read())
{
return(null);
}
return(new Map(reader));
// Note: this assumes that we only every want the one with the
// max version. If that stops being true, we will need an
// overload that specifies the version.
}
finally
{
DbUtil.ReallyClose(reader);
}
}
public static WorkItemData Select(IDbConnection dbConn, int dataId)
{
IDataReader reader = null;
try
{
IDbCommand command = dbConn.CreateCommand();
command.CommandText = SELECT_BY_DATA_ID;
DbUtil.AddParameter(command, "@data_id", dataId);
reader = command.ExecuteReader();
if (reader == null)
{
return(null);
}
if (!reader.Read())
{
return(null);
}
return(new WorkItemData(reader));
}
finally
{
DbUtil.ReallyClose(reader);
}
}
public static Rule Select(IDbConnection dbConn, int ruleId)
{
IDataReader reader = null;
try
{
IDbCommand command = dbConn.CreateCommand();
command.CommandText = SELECT_BY_ID;
DbUtil.AddParameter(command, "@id", ruleId);
reader = command.ExecuteReader();
if (reader == null)
{
return(null);
}
if (!reader.Read())
{
return(null);
}
return(new Rule(reader));
}
finally
{
DbUtil.ReallyClose(reader);
}
}
/// <summary>
/// Find all the rules hanging of a specified step, in order
/// </summary>
/// <returns>The select.</returns>
/// <param name="dbconn">connection to database</param>
/// <param name="step">The step</param>
public static IList <Rule> Select(IDbConnection dbConn, Step step)
{
var tmp = new List <Rule>();
IDataReader reader = null;
try
{
IDbCommand command = dbConn.CreateCommand();
command.CommandText = SELECT_BY_STEP_ID;
DbUtil.AddParameter(command, "@step_id", step.Id);
reader = command.ExecuteReader();
if (reader == null)
{
return(null);
}
while (reader.Read())
{
tmp.Add(new Rule(reader));
}
return(tmp);
}
finally
{
DbUtil.ReallyClose(reader);
}
}
public void Delete(IDbConnection dbConn)
{
IDbCommand command = dbConn.CreateCommand();
command.CommandText = DELETE_BY_ID;
DbUtil.AddParameter(command, "@item_id", this.Id);
int rows = command.ExecuteNonQuery();
// This assumes that the work_item_data rows are deleted in a cascading way
// by the DB. It's enforced by unit tests.
switch (rows)
{
case 0:
// strange; not even there. Maybe warn?
break;
case 1:
// working correctly.
break;
default:
var msg = new StringBuilder();
msg.Append("internal error: delete by ID returned multiple rows? ");
msg.Append(this);
throw new Exception(msg.ToString());
}
}
public static WorkItemAccess Select(IDbConnection dbConn, int workItemId, int userId)
{
IDataReader reader = null;
try
{
IDbCommand command = dbConn.CreateCommand();
command.CommandText = SELECT_BY_ITEM_USER_ID;
DbUtil.AddParameter(command, "@item_id", workItemId);
DbUtil.AddParameter(command, "@user_id", userId);
reader = command.ExecuteReader();
if (reader == null)
{
return(null);
}
if (!reader.Read())
{
return(null);
}
return(new WorkItemAccess(reader));
}
finally
{
DbUtil.ReallyClose(reader);
}
}
// TODO: spec overloads this method with ranges.
// which is a good idea as opposed to the quick-
// and-dirty "slurp" method written below.
public static IList <WorkItem> SelectByPriority(IDbConnection dbConn, Queue queue)
{
IDataReader reader = null;
List <WorkItem> tmp = new List <WorkItem>();
try
{
IDbCommand command = dbConn.CreateCommand();
command.CommandText = SELECT_BY_QUEUE_PRIORITY;
DbUtil.AddParameter(command, "@queue_id", queue.Id);
DbUtil.AddParameter(command, "@available", (int)WorkItemState.Available);
reader = command.ExecuteReader();
if (reader == null)
{
return(null);
}
while (reader.Read())
{
tmp.Add(new WorkItem(reader));
}
return(tmp);
}
finally
{
DbUtil.ReallyClose(reader);
}
}
public static Step Select(IDbConnection dbConn, String name, int mapId)
{
IDataReader reader = null;
try
{
IDbCommand command = dbConn.CreateCommand();
command.CommandText = SELECT_BY_NAME_MAP;
DbUtil.AddParameter(command, "@name", name);
DbUtil.AddParameter(command, "@map_id", mapId);
reader = command.ExecuteReader();
if (reader == null)
{
return(null);
}
if (!reader.Read())
{
return(null);
}
return(new Step(reader));
}
finally
{
DbUtil.ReallyClose(reader);
}
}
public static WorkItem Select(IDbConnection dbConn, String name)
{
IDataReader reader = null;
try
{
IDbCommand command = dbConn.CreateCommand();
command.CommandText = SELECT_BY_NAME;
DbUtil.AddParameter(command, "@name", name);
reader = command.ExecuteReader();
if (reader == null)
{
return(null);
}
if (!reader.Read())
{
return(null);
}
return(new WorkItem(reader));
}
finally
{
DbUtil.ReallyClose(reader);
}
}
public static WorkItem Insert(IDbConnection dbConn
, Step step
, String name
, int priority
, Session session
)
{
DateTime when = DateTime.UtcNow;
IDbCommand command = dbConn.CreateCommand();
command.CommandText = INSERT + " ; " + DbUtil.GET_KEY;
DbUtil.AddParameter(command, "@step_id", step.Id);
DbUtil.AddParameter(command, "@name", name);
DbUtil.AddParameter(command, "@state", (int)WorkItemState.Available);
DbUtil.AddParameter(command, "@priority", priority);
DbUtil.AddParameter(command, "@created", when);
DbUtil.AddParameter(command, "@entered", when);
DbUtil.AddParameter(command, "@session_id", session.Id);
int id = Convert.ToInt32(command.ExecuteScalar());
return(new WorkItem(id
, step.Id
, name
, WorkItemState.Available
, priority
, when
, when
, session.Id
));
}
public static IList <Session> SelectAll(IDbConnection dbConn
, User user
)
{
IDataReader reader = null;
IList <Session> tmp = new List <Session>();
try
{
IDbCommand command = dbConn.CreateCommand();
command.CommandText = SELECT_BY_USER_ID;
DbUtil.AddParameter(command, "@user_id", user.Id);
reader = command.ExecuteReader();
if (reader == null)
{
return(null);
}
while (reader.Read())
{
tmp.Add(new Session(reader));
}
}
finally
{
DbUtil.ReallyClose(reader);
}
return(tmp);
}
public static Step Insert(IDbConnection dbConn
, String name
, Map map
, Queue queue
, Step nextStep
, StepType type
)
{
IDbCommand command = dbConn.CreateCommand();
command.CommandText = INSERT + " ; " + DbUtil.GET_KEY;
DbUtil.AddParameter(command, "@name", name);
DbUtil.AddParameter(command, "@map_id", map.Id);
DbUtil.AddParameter(command, "@queue_id", queue.Id);
if (nextStep == null)
{
DbUtil.AddNullParameter(command, "@next_step_id");
}
else
{
DbUtil.AddParameter(command, "@next_step_id", nextStep.Id);
}
DbUtil.AddParameter(command, "@type", (int)type);
int id = Convert.ToInt32(command.ExecuteScalar());
Step tmp = new Step(id
, name
, map.Id
, queue.Id
, nextStep == null ? NO_NEXT_STEP : nextStep.Id
, type
);
return(tmp);
}
public static Step Select(IDbConnection dbConn, int stepId)
{
IDataReader reader = null;
try
{
IDbCommand command = dbConn.CreateCommand();
command.CommandText = SELECT_BY_ID;
DbUtil.AddParameter(command, "@step_id", stepId);
reader = command.ExecuteReader();
if (reader == null)
{
return(null);
}
if (!reader.Read())
{
return(null);
}
return(new Step(reader));
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
throw;
}
finally
{
DbUtil.ReallyClose(reader);
}
}
public static void DeleteAll(IDbConnection dbConn, int workItemId)
{
IDbCommand command = dbConn.CreateCommand();
command.CommandText = DELETE_BY_ITEM_ID;
DbUtil.AddParameter(command, "@item_id", workItemId);
command.ExecuteScalar();
}
public void Delete(IDbConnection dbConn)
{
IDbCommand command = dbConn.CreateCommand();
command.CommandText = DELETE_BY_ID;
DbUtil.AddParameter(command, "@session_id", this.Id);
command.ExecuteNonQuery();
}
public void Update(IDbConnection dbConn)
{
IDbCommand command = dbConn.CreateCommand();
command.CommandText = UPDATE;
DbUtil.AddParameter(command, "@name", this.Name);
DbUtil.AddParameter(command, "@map_id", this.MapId);
DbUtil.AddParameter(command, "@queue_id", this.QueueId);
DbUtil.AddParameter(command, "@type", (int)this.Type);
DbUtil.AddParameter(command, "@step_id", this.Id);
DbUtil.AddParameter(command, "@next_step_id", this.NextStepId);
command.ExecuteNonQuery();
}
public void Update(IDbConnection dbConn)
{
IDbCommand command = dbConn.CreateCommand();
command.CommandText = UPDATE;
DbUtil.AddParameter(command, "@step_id", this.StepId);
DbUtil.AddParameter(command, "@name", this.Name);
DbUtil.AddParameter(command, "@state", (int)this.ItemState);
DbUtil.AddParameter(command, "@priority", this.Priority);
DbUtil.AddParameter(command, "@created", this.Created);
DbUtil.AddParameter(command, "@entered", this.Entered);
DbUtil.AddParameter(command, "@session_id", this.SessionId);
DbUtil.AddParameter(command, "@item_id", this.Id);
command.ExecuteNonQuery();
}
public static AllowedQueue Insert(IDbConnection dbConn
, User user
, Queue queue
)
{
IDbCommand command = dbConn.CreateCommand();
command.CommandText = INSERT + " ; " + DbUtil.GET_KEY;
DbUtil.AddParameter(command, "@queue_id", queue.Id);
DbUtil.AddParameter(command, "@user_id", user.Id);
int id = Convert.ToInt32(command.ExecuteScalar());
AllowedQueue tmp = new AllowedQueue(id, user.Id, queue.Id);
return(tmp);
}
/// <summary>
/// Insert the specified dbConn, name and version.
/// Version doesn't default. If you want to insert
/// while creating a new version if it already exists,
/// ise InsertWithMaxVersion()
/// </summary>
/// <returns>The insert.</returns>
/// <param name="dbConn">Db conn.</param>
/// <param name="name">Name.</param>
/// <param name="version">Version.</param>
public static Map Insert(IDbConnection dbConn
, String name
, int version
)
{
IDbCommand command = dbConn.CreateCommand();
command.CommandText = INSERT + " ; " + DbUtil.GET_KEY;
DbUtil.AddParameter(command, "@name", name);
DbUtil.AddParameter(command, "@version", version);
int id = Convert.ToInt32(command.ExecuteScalar());
Map tmp = new Map(id, name, version);
return(tmp);
}
public static Session Insert(IDbConnection dbConn
, User user
)
{
DateTime when = DateTime.UtcNow;
String hostname = System.Environment.MachineName.ToLower(); // and maybe strip domains etc?
IDbCommand command = dbConn.CreateCommand();
command.CommandText = INSERT + " ; " + DbUtil.GET_KEY;
DbUtil.AddParameter(command, "@user_id", user.Id);
DbUtil.AddParameter(command, "@host_name", hostname);
DbUtil.AddParameter(command, "@start_time", when);
int id = Convert.ToInt32(command.ExecuteScalar());
return(new Session(id, user.Id, hostname, when));
}
public static WorkItemData Insert(IDbConnection dbConn
, WorkItem workItem
, String key
, String value
)
{
IDbCommand command = dbConn.CreateCommand();
command.CommandText = INSERT + " ; " + DbUtil.GET_KEY;
DbUtil.AddParameter(command, "@item_id", workItem.Id);
DbUtil.AddParameter(command, "@variable_name", key);
DbUtil.AddParameter(command, "@variable_value", value);
int id = Convert.ToInt32(command.ExecuteScalar());
return(new WorkItemData(id
, workItem.Id
, key
, value
));
}
public static User Insert(IDbConnection dbConn
, String login
, int login_limit
)
{
try
{
IDbCommand command = dbConn.CreateCommand();
command.CommandText = INSERT + ";" + DbUtil.GET_KEY;
DbUtil.AddParameter(command, "@login", login);
DbUtil.AddParameter(command, "@limit", login_limit);
int id = Convert.ToInt32(command.ExecuteScalar());
return(new User(id, login, login_limit));
}
catch (Exception ex)
{
var msg = new StringBuilder();
msg.Append("Cannot insert user [");
msg.Append(login);
msg.Append("]: ");
msg.Append(ex.Message);
throw new Exception(msg.ToString(), ex);
}
}
public static WorkItemAccess Insert(IDbConnection dbConn
, WorkItem item
, User user
, bool isAllowed
)
{
try
{
IDbCommand command = dbConn.CreateCommand();
command.CommandText = INSERT + " ; " + DbUtil.GET_KEY;
DbUtil.AddParameter(command, "@item_id", item.Id);
DbUtil.AddParameter(command, "@user_id", user.Id);
DbUtil.AddParameter(command, "@is_allowed", isAllowed);
int id = Convert.ToInt32(command.ExecuteScalar());
return(new WorkItemAccess(id
, item.Id
, user.Id
, isAllowed
));
}
catch (Exception ex)
{
var msg = new StringBuilder();
msg.Append("cannot ");
msg.Append(isAllowed ? "allow" : "disallow");
msg.Append(" access for [");
msg.Append(user.Login);
msg.Append("] on WorkItem [");
msg.Append(item);
msg.Append(']');
throw new Exception(msg.ToString(), ex);
}
}
public static Rule Insert(IDbConnection dbConn
, String variableName
, Rule.Compare operation
, String variableValue
, int ruleOrder
, Step step
, Step nextStep
)
{
try
{
// arg checks here in try block, so the building of message can only be
// done 1x:
if (ruleOrder < 0)
{
throw new ArgumentException("ruleOrder is unsigned. Use >= 0");
}
if (step == null)
{
throw new ArgumentNullException("step");
}
if (nextStep == null)
{
throw new ArgumentNullException("nextStep");
}
IDbCommand command = dbConn.CreateCommand();
command.CommandText = INSERT + " ; " + DbUtil.GET_KEY;
DbUtil.AddParameter(command, "@rule_order", ruleOrder);
DbUtil.AddParameter(command, "@variable_name", variableName);
DbUtil.AddParameter(command, "@variable_value", variableValue);
DbUtil.AddParameter(command, "@comparison", (int)(operation));
DbUtil.AddParameter(command, "@step_id", step.Id);
DbUtil.AddParameter(command, "@next_step_id", nextStep.Id);
int id = Convert.ToInt32(command.ExecuteScalar());
return(new Rule(id
, step.Id
, variableName
, operation
, variableValue
, ruleOrder
, nextStep.Id
));
}
catch (Exception ex)
{
var msg = new StringBuilder();
msg.Append("error adding rule [");
msg.Append(variableName);
msg.Append(Pretty(operation));
msg.Append(variableValue);
msg.Append("], order ");
msg.Append(ruleOrder);
msg.Append(" from step [");
msg.Append(step);
msg.Append("] to [");
msg.Append(nextStep);
msg.Append("]: ");
msg.Append(ex.Message);
//Console.WriteLine(msg);
//Console.WriteLine(INSERT);
throw new Exception(msg.ToString(), ex);
}
}
