Skip to content

Commit 9c72eac

Browse files
optimajetoptimajet
committed
Postgres Provider update
1 parent 02857aa commit 9c72eac

11 files changed

Lines changed: 255 additions & 221 deletions

‎Provider for PostgreSQL/DbObject.cs‎

Lines changed: 33 additions & 28 deletions
Original file line numberDiff line numberDiff line change
@@ -14,15 +14,23 @@ public class ColumnInfo
1414
public bool IsKey = false;
1515
public int Size = 256;
1616
}
17-
public class DbObject<T> where T : DbObject<T>, new()
17+
18+
public abstract class DbObject
1819
{
19-
public DbObject()
20-
{
20+
public static string SchemaName { get; set; }
21+
}
2122

23+
public class DbObject<T> : DbObject where T : DbObject<T>, new()
24+
{
25+
// ReSharper disable once StaticMemberInGenericType
26+
public static string DbTableName;
27+
28+
public static string ObjectName
29+
{
30+
get { return string.Format("\"{0}\".\"{1}\"", SchemaName, DbTableName); }
2231
}
2332

24-
public string db_TableName;
25-
public List<ColumnInfo> db_Columns = new List<ColumnInfo>();
33+
public List<ColumnInfo> DBColumns = new List<ColumnInfo>();
2634

2735
public virtual object GetValue(string key)
2836
{
@@ -39,12 +47,12 @@ public virtual int Insert(NpgsqlConnection connection)
3947
{
4048
using (var command = connection.CreateCommand())
4149
{
42-
command.CommandText = string.Format("INSERT INTO \"{0}\" ({1}) VALUES ({2})",
43-
db_TableName,
44-
String.Join(",", db_Columns.Select(c=> string.Format("\"{0}\"", c.Name) )),
45-
String.Join(",", db_Columns.Select(c=> "@" + c.Name)));
50+
command.CommandText = string.Format("INSERT INTO {0} ({1}) VALUES ({2})",
51+
ObjectName,
52+
String.Join(",", DBColumns.Select(c=> string.Format("\"{0}\"", c.Name) )),
53+
String.Join(",", DBColumns.Select(c=> "@" + c.Name)));
4654

47-
command.Parameters.AddRange(db_Columns.Select(c=> CreateParameter(c)).ToArray());
55+
command.Parameters.AddRange(DBColumns.Select(CreateParameter).ToArray());
4856
command.CommandType = CommandType.Text;
4957
int cnt = command.ExecuteNonQuery();
5058
return cnt;
@@ -53,13 +61,12 @@ public virtual int Insert(NpgsqlConnection connection)
5361

5462
public int Update(NpgsqlConnection connection)
5563
{
56-
string command = string.Format("UPDATE \"{0}\" SET {1} WHERE {2}",
57-
db_TableName,
58-
String.Join(",", db_Columns.Where(c => !c.IsKey).Select(c => string.Format("\"{0}\" = @{0}", c.Name))),
59-
String.Join(" AND ", db_Columns.Where(c => c.IsKey).Select(c => string.Format("\"{0}\" = @{0}", c.Name))));
64+
string command = string.Format("UPDATE {0} SET {1} WHERE {2}",
65+
ObjectName,
66+
String.Join(",", DBColumns.Where(c => !c.IsKey).Select(c => string.Format("\"{0}\" = @{0}", c.Name))),
67+
String.Join(" AND ", DBColumns.Where(c => c.IsKey).Select(c => string.Format("\"{0}\" = @{0}", c.Name))));
6068

61-
var parameters = db_Columns.Select(c =>
62-
CreateParameter(c)).ToArray();
69+
var parameters = DBColumns.Select(CreateParameter).ToArray();
6370

6471
return ExecuteCommand(connection, command, parameters);
6572

@@ -69,30 +76,29 @@ public static T SelectByKey(NpgsqlConnection connection, object id)
6976
{
7077
var t = new T();
7178

72-
var key = t.db_Columns.FirstOrDefault(c => c.IsKey);
79+
var key = t.DBColumns.FirstOrDefault(c => c.IsKey);
7380
if(key == null)
7481
{
75-
throw new Exception(string.Format("Key for table {0} isn't defined.", t.db_TableName));
82+
throw new Exception(string.Format("Key for table {0} isn't defined.", DbTableName));
7683
}
7784

78-
string selectText = string.Format("SELECT * FROM \"{0}\" WHERE \"{1}\" = @p_id", t.db_TableName, key.Name);
79-
var p_id = new NpgsqlParameter("p_id", key.Type);
80-
p_id.Value = id;
85+
string selectText = string.Format("SELECT * FROM {0} WHERE \"{1}\" = @p_id", ObjectName, key.Name);
86+
var pId = new NpgsqlParameter("p_id", key.Type) {Value = id};
8187

82-
return Select(connection, selectText, p_id).FirstOrDefault();
88+
return Select(connection, selectText, pId).FirstOrDefault();
8389
}
8490

8591
public static int Delete(NpgsqlConnection connection, object id, NpgsqlTransaction transaction = null)
8692
{
8793
var t = new T();
88-
var key = t.db_Columns.FirstOrDefault(c => c.IsKey);
94+
var key = t.DBColumns.FirstOrDefault(c => c.IsKey);
8995
if (key == null)
90-
throw new Exception(string.Format("Key for table {0} isn't defined.", t.db_TableName));
96+
throw new Exception(string.Format("Key for table {0} isn't defined.", DbTableName));
9197

9298
var pId = new NpgsqlParameter("p_id", key.Type) {Value = id};
9399

94100
return ExecuteCommand(connection,
95-
string.Format("DELETE FROM \"{0}\" WHERE \"{1}\" = @p_id", t.db_TableName, key.Name), transaction, pId);
101+
string.Format("DELETE FROM {0} WHERE \"{1}\" = @p_id", ObjectName, key.Name), transaction, pId);
96102
}
97103

98104
public static int ExecuteCommand(NpgsqlConnection connection, string commandText,
@@ -148,7 +154,7 @@ public static T[] Select(NpgsqlConnection connection, string commandText, params
148154
foreach (DataRow row in dt.Rows)
149155
{
150156
T item = new T();
151-
foreach (var c in item.db_Columns)
157+
foreach (var c in item.DBColumns)
152158
item.SetValue(c.Name, row[c.Name]);
153159
res.Add(item);
154160
}
@@ -160,8 +166,7 @@ public static T[] Select(NpgsqlConnection connection, string commandText, params
160166

161167
public virtual NpgsqlParameter CreateParameter(ColumnInfo c)
162168
{
163-
var p = new NpgsqlParameter(c.Name, c.Type);
164-
p.Value = GetValue(c.Name);
169+
var p = new NpgsqlParameter(c.Name, c.Type) {Value = GetValue(c.Name)};
165170
return p;
166171
}
167172
}

‎Provider for PostgreSQL/Models/WorkflowGlobalParameter.cs‎

Lines changed: 13 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -3,6 +3,7 @@
33
using NpgsqlTypes;
44

55

6+
// ReSharper disable once CheckNamespace
67
namespace OptimaJet.Workflow.PostgreSQL
78
{
89
public class WorkflowGlobalParameter : DbObject<WorkflowGlobalParameter>
@@ -15,18 +16,19 @@ public class WorkflowGlobalParameter : DbObject<WorkflowGlobalParameter>
1516

1617
public string Value { get; set; }
1718

18-
private const string _tableName = "WorkflowGlobalParameter";
19+
static WorkflowGlobalParameter()
20+
{
21+
DbTableName = "WorkflowGlobalParameter";
22+
}
1923

2024
public WorkflowGlobalParameter()
21-
: base()
2225
{
23-
db_TableName = _tableName;
24-
db_Columns.AddRange(new ColumnInfo[]
26+
DBColumns.AddRange(new[]
2527
{
26-
new ColumnInfo() {Name = "Id", IsKey = true, Type = NpgsqlDbType.Uuid},
27-
new ColumnInfo() {Name = "Type"},
28-
new ColumnInfo() {Name = "Name"},
29-
new ColumnInfo() {Name = "Value"}
28+
new ColumnInfo {Name = "Id", IsKey = true, Type = NpgsqlDbType.Uuid},
29+
new ColumnInfo {Name = "Type"},
30+
new ColumnInfo {Name = "Name"},
31+
new ColumnInfo {Name = "Value"}
3032
});
3133
}
3234

@@ -61,7 +63,7 @@ public override void SetValue(string key, object value)
6163
Name = value as string;
6264
break;
6365
case "Value":
64-
Value = value as string;;
66+
Value = value as string;
6567
break;
6668
default:
6769
throw new Exception(string.Format("Column {0} is not exists", key));
@@ -70,7 +72,7 @@ public override void SetValue(string key, object value)
7072

7173
public static WorkflowGlobalParameter[] SelectByTypeAndName(NpgsqlConnection connection, string type, string name = null)
7274
{
73-
string selectText = string.Format("SELECT * FROM \"{0}\" WHERE \"Type\" = @type", _tableName);
75+
string selectText = string.Format("SELECT * FROM {0} WHERE \"Type\" = @type", ObjectName);
7476

7577
if (!string.IsNullOrEmpty(name))
7678
selectText = selectText + " AND \"Name\" = @name";
@@ -87,7 +89,7 @@ public static WorkflowGlobalParameter[] SelectByTypeAndName(NpgsqlConnection con
8789

8890
public static int DeleteByTypeAndName(NpgsqlConnection connection, string type, string name = null)
8991
{
90-
string selectText = string.Format("DELETE FROM \"{0}\" WHERE \"Type\" = @type", _tableName);
92+
string selectText = string.Format("DELETE FROM {0} WHERE \"Type\" = @type", ObjectName);
9193

9294
if (!string.IsNullOrEmpty(name))
9395
selectText = selectText + " AND \"Name\" = @name";

‎Provider for PostgreSQL/Models/WorkflowProcessInstance.cs‎

Lines changed: 22 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -1,6 +1,7 @@
11
using System;
22
using NpgsqlTypes;
33

4+
// ReSharper disable once CheckNamespace
45
namespace OptimaJet.Workflow.PostgreSQL
56
{
67
public class WorkflowProcessInstance: DbObject<WorkflowProcessInstance>
@@ -18,23 +19,28 @@ public class WorkflowProcessInstance: DbObject<WorkflowProcessInstance>
1819
public string StateName { get; set; }
1920
public Guid? ParentProcessId { get; set; }
2021
public Guid RootProcessId { get; set; }
21-
public WorkflowProcessInstance(): base()
22+
23+
static WorkflowProcessInstance()
24+
{
25+
DbTableName = "WorkflowProcessInstance";
26+
}
27+
28+
public WorkflowProcessInstance()
2229
{
23-
db_TableName = "WorkflowProcessInstance";
24-
db_Columns.AddRange(new ColumnInfo[]{
25-
new ColumnInfo(){Name="Id", IsKey = true, Type = NpgsqlDbType.Uuid},
26-
new ColumnInfo(){Name="ActivityName"},
27-
new ColumnInfo(){Name="IsDeterminingParametersChanged", Type = NpgsqlDbType.Boolean},
28-
new ColumnInfo(){Name="PreviousActivity"},
29-
new ColumnInfo(){Name="PreviousActivityForDirect"},
30-
new ColumnInfo(){Name="PreviousActivityForReverse"},
31-
new ColumnInfo(){Name="PreviousState"},
32-
new ColumnInfo(){Name="PreviousStateForDirect"},
33-
new ColumnInfo(){Name="PreviousStateForReverse"},
34-
new ColumnInfo(){Name="SchemeId", Type = NpgsqlDbType.Uuid},
35-
new ColumnInfo(){Name="StateName"},
36-
new ColumnInfo() {Name = "ParentProcessId", Type = NpgsqlDbType.Uuid},
37-
new ColumnInfo() {Name = "RootProcessId", Type = NpgsqlDbType.Uuid},
30+
DBColumns.AddRange(new[]{
31+
new ColumnInfo {Name="Id", IsKey = true, Type = NpgsqlDbType.Uuid},
32+
new ColumnInfo {Name="ActivityName"},
33+
new ColumnInfo {Name="IsDeterminingParametersChanged", Type = NpgsqlDbType.Boolean},
34+
new ColumnInfo {Name="PreviousActivity"},
35+
new ColumnInfo {Name="PreviousActivityForDirect"},
36+
new ColumnInfo {Name="PreviousActivityForReverse"},
37+
new ColumnInfo {Name="PreviousState"},
38+
new ColumnInfo {Name="PreviousStateForDirect"},
39+
new ColumnInfo {Name="PreviousStateForReverse"},
40+
new ColumnInfo {Name="SchemeId", Type = NpgsqlDbType.Uuid},
41+
new ColumnInfo {Name="StateName"},
42+
new ColumnInfo {Name = "ParentProcessId", Type = NpgsqlDbType.Uuid},
43+
new ColumnInfo {Name = "RootProcessId", Type = NpgsqlDbType.Uuid},
3844
});
3945
}
4046

‎Provider for PostgreSQL/Models/WorkflowProcessInstancePersistence.cs‎

Lines changed: 12 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -2,6 +2,7 @@
22
using Npgsql;
33
using NpgsqlTypes;
44

5+
// ReSharper disable once CheckNamespace
56
namespace OptimaJet.Workflow.PostgreSQL
67
{
78
public class WorkflowProcessInstancePersistence : DbObject<WorkflowProcessInstancePersistence>
@@ -11,17 +12,18 @@ public class WorkflowProcessInstancePersistence : DbObject<WorkflowProcessInstan
1112
public string ParameterName { get; set; }
1213
public string Value { get; set; }
1314

14-
private static string _tableName = "WorkflowProcessInstancePersistence";
15+
static WorkflowProcessInstancePersistence()
16+
{
17+
DbTableName = "WorkflowProcessInstancePersistence";
18+
}
1519

1620
public WorkflowProcessInstancePersistence()
17-
: base()
1821
{
19-
db_TableName = _tableName;
20-
db_Columns.AddRange(new ColumnInfo[]{
21-
new ColumnInfo(){Name="Id", IsKey = true, Type = NpgsqlDbType.Uuid},
22-
new ColumnInfo(){Name="ProcessId", Type = NpgsqlDbType.Uuid},
23-
new ColumnInfo(){Name="ParameterName"},
24-
new ColumnInfo(){Name="Value", Type = NpgsqlDbType.Text }
22+
DBColumns.AddRange(new[]{
23+
new ColumnInfo {Name="Id", IsKey = true, Type = NpgsqlDbType.Uuid},
24+
new ColumnInfo {Name="ProcessId", Type = NpgsqlDbType.Uuid},
25+
new ColumnInfo {Name="ParameterName"},
26+
new ColumnInfo {Name="Value", Type = NpgsqlDbType.Text }
2527
});
2628
}
2729

@@ -65,7 +67,7 @@ public override void SetValue(string key, object value)
6567

6668
public static WorkflowProcessInstancePersistence[] SelectByProcessId(NpgsqlConnection connection, Guid processId)
6769
{
68-
string selectText = string.Format("SELECT * FROM \"{0}\" WHERE \"ProcessId\" = @processid", _tableName);
70+
string selectText = string.Format("SELECT * FROM {0} WHERE \"ProcessId\" = @processid", ObjectName);
6971
var p = new NpgsqlParameter("processId", NpgsqlDbType.Uuid) {Value = processId};
7072
return Select(connection, selectText, p);
7173
}
@@ -75,7 +77,7 @@ public static int DeleteByProcessId(NpgsqlConnection connection, Guid processId,
7577
var p = new NpgsqlParameter("processId", NpgsqlDbType.Uuid) {Value = processId};
7678

7779
return ExecuteCommand(connection,
78-
string.Format("DELETE FROM \"{0}\" WHERE \"ProcessId\" = @processid", _tableName), transaction, p);
80+
string.Format("DELETE FROM {0} WHERE \"ProcessId\" = @processid", ObjectName), transaction, p);
7981
}
8082
}
8183
}

‎Provider for PostgreSQL/Models/WorkflowProcessInstanceStatus.cs‎

Lines changed: 11 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -2,6 +2,7 @@
22
using Npgsql;
33
using NpgsqlTypes;
44

5+
// ReSharper disable once CheckNamespace
56
namespace OptimaJet.Workflow.PostgreSQL
67
{
78
public class WorkflowProcessInstanceStatus : DbObject<WorkflowProcessInstanceStatus>
@@ -10,16 +11,17 @@ public class WorkflowProcessInstanceStatus : DbObject<WorkflowProcessInstanceSta
1011
public Guid Lock { get; set; }
1112
public byte Status { get; set; }
1213

13-
private const string TableName = "WorkflowProcessInstanceStatus";
14+
static WorkflowProcessInstanceStatus()
15+
{
16+
DbTableName = "WorkflowProcessInstanceStatus";
17+
}
1418

1519
public WorkflowProcessInstanceStatus()
16-
: base()
1720
{
18-
db_TableName = TableName;
19-
db_Columns.AddRange(new ColumnInfo[]{
20-
new ColumnInfo(){Name="Id", IsKey = true, Type = NpgsqlDbType.Uuid},
21-
new ColumnInfo(){Name="Lock", Type = NpgsqlDbType.Uuid},
22-
new ColumnInfo(){Name="Status", Type = NpgsqlDbType.Smallint}
21+
DBColumns.AddRange(new[]{
22+
new ColumnInfo {Name="Id", IsKey = true, Type = NpgsqlDbType.Uuid},
23+
new ColumnInfo {Name="Lock", Type = NpgsqlDbType.Uuid},
24+
new ColumnInfo {Name="Status", Type = NpgsqlDbType.Smallint}
2325
});
2426
}
2527

@@ -59,7 +61,7 @@ public override void SetValue(string key, object value)
5961

6062
public static int MassChangeStatus(NpgsqlConnection connection, byte stateFrom, byte stateTo)
6163
{
62-
string command = string.Format("UPDATE \"{0}\" SET \"Status\" = @stateto WHERE \"Status\" = @statefrom", TableName);
64+
string command = string.Format("UPDATE {0} SET \"Status\" = @stateto WHERE \"Status\" = @statefrom", ObjectName);
6365
var p1 = new NpgsqlParameter("statefrom", NpgsqlDbType.Smallint) {Value = stateFrom};
6466
var p2 = new NpgsqlParameter("stateto", NpgsqlDbType.Smallint) {Value = stateTo};
6567

@@ -68,7 +70,7 @@ public static int MassChangeStatus(NpgsqlConnection connection, byte stateFrom,
6870

6971
public static int ChangeStatus(NpgsqlConnection connection, WorkflowProcessInstanceStatus status, Guid oldLock)
7072
{
71-
string command = string.Format("UPDATE \"{0}\" SET \"Status\" = @newstatus, \"Lock\" = @newlock WHERE \"Id\" = @id AND \"Lock\" = @oldlock", TableName);
73+
string command = string.Format("UPDATE {0} SET \"Status\" = @newstatus, \"Lock\" = @newlock WHERE \"Id\" = @id AND \"Lock\" = @oldlock", ObjectName);
7274
var p1 = new NpgsqlParameter("newstatus", NpgsqlDbType.Smallint) { Value = status.Status };
7375
var p2 = new NpgsqlParameter("newlock", NpgsqlDbType.Uuid) { Value = status.Lock };
7476
var p3 = new NpgsqlParameter("id", NpgsqlDbType.Uuid) { Value = status.Id };

0 commit comments

Comments
 (0)