/// <summary>
/// Creates a node table in the graph database.
/// </summary>
/// <param name="sqlStr">A CREATE TABLE statement with annotations.</param>
/// <param name="externalTransaction">An existing SqlTransaction instance under which the create node table will occur.</param>
/// <returns>True, if the statement is successfully executed.</returns>
public bool CreateNodeTable(string sqlStr, SqlTransaction externalTransaction = null)
{
// get syntax tree of CREATE TABLE command
var parser = new GraphViewParser();
List<WNodeTableColumn> columns;
IList<ParseError> errors;
var script = parser.ParseCreateNodeTableStatement(sqlStr, out columns, out errors) as WSqlScript;
if (errors.Count > 0)
throw new SyntaxErrorException(errors);
if (script == null || script.Batches.Count == 0)
{
throw new SyntaxErrorException("Invalid CREATE TABLE statement.");
}
var statement = script.Batches[0].Statements[0] as WCreateTableStatement;
var tableSchema = statement.SchemaObjectName.SchemaIdentifier != null
? statement.SchemaObjectName.SchemaIdentifier.Value
: "dbo";
var tableName = statement.SchemaObjectName.BaseIdentifier.Value;
SqlTransaction tx;
if (externalTransaction == null)
{
tx = Conn.BeginTransaction();
}
else
{
tx = externalTransaction;
}
// Persists the node table's meta-data
try
{
Int64 tableId;
using (var command = new SqlCommand(null, Conn))
{
command.Transaction = tx;
command.CommandText = string.Format(@"
INSERT INTO [{0}] ([TableSchema], [TableName])
OUTPUT [Inserted].[TableId]
VALUES (@tableSchema, @tableName)", MetadataTables[0]);
command.Parameters.AddWithValue("@tableSchema", tableSchema);
command.Parameters.AddWithValue("@tableName", tableName);
// get generated TableId
WValueExpression tableIdentitySeed;
using (var reader = command.ExecuteReader())
{
if (!reader.Read())
{
return false;
}
tableId = Convert.ToInt64(reader["TableId"], CultureInfo.CurrentCulture);
var tableIdSeek = tableId << 48;
tableIdentitySeed = new WValueExpression(tableIdSeek.ToString(CultureInfo.InvariantCulture), false);
}
// create graph table
var wColumnDefinition = statement.Definition.ColumnDefinitions
.FirstOrDefault(x => x.ColumnIdentifier.Value == "GlobalNodeId");
if (wColumnDefinition != null)
wColumnDefinition.IdentityOptions.IdentitySeed = tableIdentitySeed;
command.CommandText = statement.ToString();
command.ExecuteNonQuery();
}
var edgeColumnNameToColumnId = new Dictionary<string, long>(StringComparer.OrdinalIgnoreCase);
var hasReversedEdge = new Dictionary<string, bool>(StringComparer.OrdinalIgnoreCase);
using (var command = new SqlCommand(null, Conn))
{
command.Transaction = tx;
// insert graph column
command.CommandText = string.Format(@"
INSERT INTO [{0}]
([TableSchema], [TableName], [TableId], [ColumnName], [ColumnRole], [Reference], [HasReversedEdge], [EdgeUdfPrefix])
OUTPUT [Inserted].[ColumnId]
VALUES (@tableSchema, @tableName, @tableid, @columnName, @columnRole, @ref, @hasRevEdge, @udfPrefix)", MetadataTables[1]);
command.Parameters.AddWithValue("@tableSchema", tableSchema);
command.Parameters.AddWithValue("@tableName", tableName);
command.Parameters.AddWithValue("@tableid", tableId);
command.Parameters.Add("@columnName", SqlDbType.NVarChar, 128);
command.Parameters.Add("@columnRole", SqlDbType.Int);
command.Parameters.Add("@ref", SqlDbType.NVarChar, 128);
command.Parameters.Add("@hasRevEdge", SqlDbType.Bit);
command.Parameters.Add("@udfPrefix", SqlDbType.NVarChar, 512);
foreach (var column in columns)
{
command.Parameters["@columnName"].Value = column.ColumnName.Value;
command.Parameters["@columnRole"].Value = (int) column.ColumnRole;
command.Parameters["@hasRevEdge"].Value = column.ColumnRole == WNodeTableColumnRole.Edge ? 1 : 0;
var edgeColumn = column as WGraphTableEdgeColumn;
if (edgeColumn != null)
{
command.Parameters["@ref"].Value =
(edgeColumn.TableReference as WNamedTableReference).ExposedName.Value;
command.Parameters["@udfPrefix"].Value =
tableSchema + "_" + tableName + "_" + column.ColumnName.Value;
}
else
{
command.Parameters["@ref"].Value = command.Parameters["@udfPrefix"].Value = SqlChars.Null;
}
using (var reader = command.ExecuteReader())
{
if (!reader.Read())
{
return false;
}
if (column.ColumnRole == WNodeTableColumnRole.Edge)
{
edgeColumnNameToColumnId[column.ColumnName.Value] = Convert.ToInt64(reader["ColumnId"].ToString(), CultureInfo.CurrentCulture);
hasReversedEdge[column.ColumnName.Value] = true;
}
}
}
command.CommandText = string.Format(@"
INSERT INTO [{0}]
([TableSchema], [TableName], [ColumnName], [ColumnId], [AverageDegree])
VALUES (@tableSchema, @tableName, @columnName, @columnid, @AverageDegree)", MetadataTables[3]);
command.Parameters.Add("@AverageDegree", SqlDbType.Int);
command.Parameters["@AverageDegree"].Value = 5;
command.Parameters.Add("@columnid", SqlDbType.Int);
foreach (var column in columns.OfType<WGraphTableEdgeColumn>())
{
command.Parameters["@columnid"].Value = edgeColumnNameToColumnId[column.ColumnName.Value];
command.Parameters["@columnName"].Value = column.ColumnName.Value;
command.ExecuteNonQuery();
}
}
// insert graph edge attributes
using (var command = new SqlCommand(null, Conn))
{
command.Transaction = tx;
command.CommandText = string.Format(@"
INSERT INTO [{0}]
([TableSchema], [TableName], [ColumnName], [ColumnId], [AttributeName], [AttributeType], [AttributeEdgeId])
VALUES (@tableSchema, @tableName, @columnName, @columnid, @attrName, @attrType, @attrId)", MetadataTables[2]);
command.Parameters.AddWithValue("@tableSchema", tableSchema);
command.Parameters.AddWithValue("@tableName", tableName);
command.Parameters.Add("@columnName", SqlDbType.NVarChar, 128);
command.Parameters.Add("@attrName", SqlDbType.NVarChar, 128);
command.Parameters.Add("@attrType", SqlDbType.NVarChar, 128);
command.Parameters.Add("@attrId", SqlDbType.Int);
command.Parameters.Add("@columnid", SqlDbType.Int);
var createOrder = 1;
foreach (var column in columns.OfType<WGraphTableEdgeColumn>())
{
command.Parameters["@columnName"].Value = column.ColumnName.Value;
foreach (var attr in column.Attributes)
{
command.Parameters["@attrName"].Value = attr.Item1.Value;
command.Parameters["@attrType"].Value = attr.Item2.ToString();
command.Parameters["@attrId"].Value = (createOrder++).ToString();
command.Parameters["@columnid"].Value = edgeColumnNameToColumnId[column.ColumnName.Value];
command.ExecuteNonQuery();
}
}
}
// create column edge sampling table
// using (var command = new SqlCommand(null, Conn))
// {
// command.Transaction = tx;
// foreach (var column in columns.OfType<WGraphTableEdgeColumn>())
// {
// command.CommandText = String.Format(CultureInfo.CurrentCulture, @"
// CREATE TABLE [{0}_{1}_{2}_Sampling] (
// [src] [bigint],
// [dst] [bigint]
// )", tableSchema, tableName, column.ColumnName.Value);
// command.ExecuteNonQuery();
// }
// }
// process edge's Decoder function
//var edgeDict = columns.OfType<WGraphTableEdgeColumn>()
// .ToDictionary(col => col.ColumnName.Value,
// col =>
// col.Attributes.Select(
// x =>
// new Tuple<string, string>(x.Item1.Value,
// x.Item2.ToString().ToLower(CultureInfo.CurrentCulture)))
// .ToList());
var edgeDict =
columns.OfType<WGraphTableEdgeColumn>()
.Select(
col =>
new Tuple<string,int, List<Tuple<string, string>>>(col.ColumnName.Value,
(int)edgeColumnNameToColumnId[col.ColumnName.Value],
col.Attributes.Select(
x =>
new Tuple<string, string>(x.Item1.Value,
x.Item2.ToString().ToLower(CultureInfo.CurrentCulture)))
.ToList())).ToList();
var userIdColumn = columns.Where(e => e.ColumnRole == WNodeTableColumnRole.NodeId).ToList();
string userId = (userIdColumn.Count == 0) ? "" : userIdColumn[0].ColumnName.Value;
if (edgeDict.Count > 0)
{
var assemblyName = tableSchema + '_' + tableName;
GraphViewDefinedFunctionRegister register = new NodeTableRegister(assemblyName, tableName, edgeDict, userId);
register.Register(Conn, tx);
}
using (var command = new SqlCommand(null, Conn))
{
command.Transaction = tx;
foreach (var column in columns.OfType<WGraphTableEdgeColumn>())
{
command.CommandText = String.Format(CultureInfo.CurrentCulture, @"
SELECT * INTO [{0}_{1}_{2}_Sampling] FROM (
SELECT ([GlobalNodeID]+0) as [Src], [Edge].*
FROM [{0}].[{1}] WITH (NOLOCK)
CROSS APPLY {0}_{1}_{2}_Decoder([{2}],[{2}DeleteCol],0) AS Edge
WHERE 1=0) as EdgeSample",
tableSchema, tableName, column.ColumnName.Value);
command.ExecuteNonQuery();
}
}
//updateGlobalNodeView(tableSchema, tx);
// check whether adding reversed edges into the current node table column collection are required
var reversedEdgeCommandText = String.Empty;
var reversedEdgeSamplingCommandText = String.Empty;
var reversedMetaCommandTextList = new List<string>();
using (var command = new SqlCommand(null, Conn))
{
command.Transaction = tx;
command.CommandText = String.Format(@"
SELECT [ColumnId], [TableSchema], [TableName], [ColumnName], [ColumnRole], [Reference], [HasReversedEdge], [EdgeUdfPrefix]
FROM [{0}]
WHERE [ColumnRole] = 1 AND [Reference] = '{1}'",
MetadataTables[1], tableName);
var originalColumnIdList = new List<long>();
using (var reader = command.ExecuteReader())
{
var index = 0;
while (reader.Read())
{
if (!bool.Parse(reader["HasReversedEdge"].ToString())) continue;
var sourceTableSchema = reader["TableSchema"].ToString();
var sourceTableName = reader["TableName"].ToString();
var sourceEdgeName = reader["ColumnName"].ToString();
var reversedEdgeName = sourceTableName + "_" + sourceEdgeName + "Reversed";
var parameters = new string[]
{
"@tableSchema" + index,
"@tableName" + index,
"@tableid" + index,
"@columnName" + index,
"@columnRole" + index,
"@ref" + index,
"@refTableSchema" + index,
"@isRevEdge" + index,
"@udfPrefix" + index
};
// add reversed edge varbinary columns
reversedEdgeCommandText += String.Format(@"
ALTER TABLE [{0}].[{1}]
ADD [{2}] VARBINARY(MAX) NOT NULL DEFAULT 0x,
[{3}] VARBINARY(MAX) NOT NULL DEFAULT 0x,
[{4}] INT NOT NULL DEFAULT 0;
",
tableSchema, tableName,
reversedEdgeName,
reversedEdgeName + "DeleteCol",
reversedEdgeName + "OutDegree");
// create reversed edge sampling table
reversedEdgeSamplingCommandText += String.Format(@"
SELECT * INTO [{0}_{1}_{2}_Sampling] FROM (
SELECT ([GlobalNodeID]+0) as [Src], [Edge].*
FROM [{0}].[{1}] WITH (NOLOCK)
CROSS APPLY {3}_{4}_{5}_Decoder([{2}],[{2}DeleteCol],0) AS Edge
WHERE 1=0) as EdgeSample
",
tableSchema, tableName, reversedEdgeName,
sourceTableSchema, sourceTableName, sourceEdgeName);
// update meta info about reversed edges
reversedMetaCommandTextList.Add(string.Format(@"
INSERT INTO [{0}]
([TableSchema], [TableName], [TableId], [ColumnName], [ColumnRole], [Reference], [RefTableSchema], [IsReversedEdge], [EdgeUdfPrefix])
OUTPUT [Inserted].[ColumnId]
VALUES ({1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9})
",
MetadataTables[1],
parameters[0], parameters[1], parameters[2],
parameters[3], parameters[4], parameters[5],
parameters[6], parameters[7], parameters[8]));
command.Parameters.AddWithValue(parameters[0], tableSchema);
command.Parameters.AddWithValue(parameters[1], tableName);
command.Parameters.AddWithValue(parameters[2], tableId);
command.Parameters.AddWithValue(parameters[3], reversedEdgeName);
command.Parameters.AddWithValue(parameters[4], Convert.ToInt32(reader["ColumnRole"].ToString(), CultureInfo.CurrentCulture));
command.Parameters.AddWithValue(parameters[5], sourceTableName);
command.Parameters.AddWithValue(parameters[6], sourceTableSchema);
command.Parameters.AddWithValue(parameters[7], 1);
command.Parameters.AddWithValue(parameters[8], reader["EdgeUdfPrefix"].ToString());
originalColumnIdList.Add(Convert.ToInt64(reader["ColumnId"].ToString(), CultureInfo.CurrentCulture));
++index;
}
}
if (String.IsNullOrEmpty(reversedEdgeCommandText) == false)
{
command.CommandText = reversedEdgeCommandText;
command.ExecuteNonQuery();
command.CommandText = reversedEdgeSamplingCommandText;
command.ExecuteNonQuery();
command.CommandText = String.Format(@"
SELECT MAX([AttributeEdgeId]) AS maxAttrEdgeId
FROM [{0}]
WHERE [TableSchema] = '{1}' AND [TableName] = '{2}'",
MetadataTables[2], tableSchema, tableName);
var createOrder = 1;
using (var reader = command.ExecuteReader())
{
if (reader.Read())
{
var startOrder = reader["maxAttrEdgeId"].ToString();
createOrder = String.IsNullOrEmpty(startOrder) ? 1 : Convert.ToInt32(startOrder, CultureInfo.CurrentCulture) + 1;
}
}
// NodeTableColumnCollection update
for (var i = 0; i < reversedMetaCommandTextList.Count; ++i)
{
command.CommandText = reversedMetaCommandTextList[i];
if (command.Parameters.Contains("@tableSchema"))
{
command.Parameters.RemoveAt("@tableSchema");
command.Parameters.RemoveAt("@tableName");
command.Parameters.RemoveAt("@columnName");
command.Parameters.RemoveAt("@columnid");
command.Parameters.RemoveAt("@AverageDegree");
if (command.Parameters.Contains("@attrName"))
{
command.Parameters.RemoveAt("@attrName");
command.Parameters.RemoveAt("@attrType");
command.Parameters.RemoveAt("@attrId");
}
}
long reversedColumnId = 0;
using (var reader = command.ExecuteReader())
{
if (!reader.Read())
{
return false;
}
if (Convert.ToInt32(command.Parameters["@columnRole"+i].Value, CultureInfo.CurrentCulture) == 1)
{
reversedColumnId = Convert.ToInt64(reader["ColumnId"].ToString(), CultureInfo.CurrentCulture);
}
}
// EdgeAverageDegreeCollection update
command.Parameters.AddWithValue("@tableSchema", tableSchema);
command.Parameters.AddWithValue("@tableName", tableName);
command.Parameters.AddWithValue("@columnName", command.Parameters["@columnName" + i].Value.ToString());
command.Parameters.Add("@columnid", SqlDbType.Int);
command.Parameters["@columnid"].Value = reversedColumnId;
command.Parameters.Add("@AverageDegree", SqlDbType.Int);
command.Parameters["@AverageDegree"].Value = 5;
command.CommandText = string.Format(@"
INSERT INTO [{0}]
([TableSchema], [TableName], [ColumnName], [ColumnId], [AverageDegree])
VALUES (@tableSchema, @tableName, @columnName, @columnid, @AverageDegree)", MetadataTables[3]);
command.ExecuteNonQuery();
// get all original attributes
var attributes = new List<Tuple<string, string>>();
command.CommandText = String.Format(@"
SELECT [AttributeName], [AttributeType]
FROM [{0}]
WHERE [ColumnId] = {1}",
MetadataTables[2], originalColumnIdList[i]);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
attributes.Add(new Tuple<string, string>(
reader["AttributeName"].ToString(), reader["AttributeType"].ToString()));
}
}
if (attributes.Count == 0) continue;
// EdgeAttributeCollection update
command.Parameters.Add("@attrName", SqlDbType.NVarChar, 128);
command.Parameters.Add("@attrType", SqlDbType.NVarChar, 128);
command.Parameters.Add("@attrId", SqlDbType.Int);
foreach (var attr in attributes)
{
command.Parameters["@attrName"].Value = attr.Item1;
command.Parameters["@attrType"].Value = attr.Item2;
command.Parameters["@attrId"].Value = (createOrder++).ToString();
command.CommandText = String.Format(@"
INSERT INTO [{0}]
([TableSchema], [TableName], [ColumnName], [ColumnId], [AttributeName], [AttributeType], [AttributeEdgeId])
VALUES (@tableSchema, @tableName, @columnName, @columnid, @attrName, @attrType, @attrId)", MetadataTables[2]);
command.ExecuteNonQuery();
}
}
}
}
// check whether adding reverse edges to pointed node tables are required
using (var command = new SqlCommand(null, Conn))
{
command.Transaction = tx;
foreach (var column in columns.Where(x=>hasReversedEdge.ContainsKey(x.ColumnName.Value)))
{
command.Parameters.Clear();
reversedEdgeCommandText = reversedEdgeSamplingCommandText = String.Empty;
var edgeColumn = column as WGraphTableEdgeColumn;
if (edgeColumn == null) continue;
var refTableName =
(edgeColumn.TableReference as WNamedTableReference).ExposedName.Value;
string refTableSchema = String.Empty, reversedEdgeName = String.Empty;
Int64 refTableId = 0;
// skip the edge which connects to the node table itself
if (refTableName.Equals(tableName)) continue;
command.CommandText = String.Format(@"
SELECT [TableId], [TableSchema], [TableName]
FROM [{0}]
WHERE [TableRole] = 0 AND [TableName] = '{1}'",
MetadataTables[0], refTableName);
using (var reader = command.ExecuteReader())
{
// if the target node table exists
if (reader.Read())
{
refTableSchema = reader["TableSchema"].ToString();
reversedEdgeName = tableName + "_" + column.ColumnName.Value + "Reversed";
// add reversed edge varbinary column
reversedEdgeCommandText += String.Format(@"
ALTER TABLE [{0}].[{1}]
ADD [{2}] VARBINARY(MAX) NOT NULL DEFAULT 0x,
[{3}] VARBINARY(MAX) NOT NULL DEFAULT 0x,
[{4}] INT NOT NULL DEFAULT 0
",
refTableSchema, refTableName,
reversedEdgeName,
reversedEdgeName + "DeleteCol",
reversedEdgeName + "OutDegree");
// create reversed edge sampling table
reversedEdgeSamplingCommandText += String.Format(@"
SELECT * INTO [{0}_{1}_{2}_Sampling] FROM (
SELECT ([GlobalNodeID]+0) as [Src], [Edge].*
FROM [{0}].[{1}] WITH (NOLOCK)
CROSS APPLY {3}_{4}_{5}_Decoder([{2}],[{2}DeleteCol],0) AS Edge
WHERE 1=0) as EdgeSample
",
refTableSchema, refTableName, reversedEdgeName,
tableSchema, tableName, column.ColumnName.Value);
refTableId = Convert.ToInt64(reader["TableId"], CultureInfo.CurrentCulture);
}
}
// update meta info about reversed edge
if (String.IsNullOrEmpty(reversedEdgeCommandText) == false)
{
command.CommandText = reversedEdgeCommandText;
command.ExecuteNonQuery();
command.CommandText = reversedEdgeSamplingCommandText;
command.ExecuteNonQuery();
// NodeTableColumnCollection update
command.CommandText = string.Format(@"
INSERT INTO [{0}]
([TableSchema], [TableName], [TableId], [ColumnName], [ColumnRole], [Reference], [RefTableSchema], [IsReversedEdge], [EdgeUdfPrefix])
OUTPUT [Inserted].[ColumnId]
VALUES (@tableSchema, @tableName, @tableid, @columnName, @columnRole, @ref, @refTableSchema, @isRevEdge, @udfPrefix)", MetadataTables[1]);
command.Parameters.AddWithValue("@tableSchema", refTableSchema);
command.Parameters.AddWithValue("@tableName", refTableName);
command.Parameters.AddWithValue("@tableid", refTableId);
command.Parameters.AddWithValue("@columnName", reversedEdgeName);
command.Parameters.AddWithValue("@columnRole", (int)column.ColumnRole);
command.Parameters.AddWithValue("@ref", tableName);
command.Parameters.AddWithValue("@refTableSchema", tableSchema);
command.Parameters.AddWithValue("@isRevEdge", 1);
command.Parameters.AddWithValue("@udfPrefix", tableSchema + "_" + tableName + "_" + column.ColumnName.Value);
long reversedColumnId = 0;
using (var reader = command.ExecuteReader())
{
if (!reader.Read())
{
return false;
}
if (column.ColumnRole == WNodeTableColumnRole.Edge)
{
reversedColumnId = Convert.ToInt64(reader["ColumnId"].ToString(), CultureInfo.CurrentCulture);
}
}
// EdgeAverageDegreeCollection update
command.Parameters.Add("@columnid", SqlDbType.Int);
command.Parameters["@columnid"].Value = reversedColumnId;
command.Parameters.Add("@AverageDegree", SqlDbType.Int);
command.Parameters["@AverageDegree"].Value = 5;
command.CommandText = string.Format(@"
INSERT INTO [{0}]
([TableSchema], [TableName], [ColumnName], [ColumnId], [AverageDegree])
VALUES (@tableSchema, @tableName, @columnName, @columnid, @AverageDegree)", MetadataTables[3]);
command.ExecuteNonQuery();
command.CommandText = String.Format(@"
SELECT MAX([AttributeEdgeId]) AS maxAttrEdgeId
FROM [{0}]
WHERE [TableSchema] = '{1}' AND [TableName] = '{2}'",
MetadataTables[2], refTableSchema, refTableName);
var createOrder = 1;
using (var reader = command.ExecuteReader())
{
if (reader.Read())
{
var startOrder = reader["maxAttrEdgeId"].ToString();
createOrder = String.IsNullOrEmpty(startOrder) ? 1 : Convert.ToInt32(startOrder, CultureInfo.CurrentCulture) + 1;
}
}
// EdgeAttributeCollection update
command.Parameters.Add("@attrName", SqlDbType.NVarChar, 128);
command.Parameters.Add("@attrType", SqlDbType.NVarChar, 128);
command.Parameters.Add("@attrId", SqlDbType.Int);
foreach (var attr in edgeColumn.Attributes)
{
command.Parameters["@attrName"].Value = attr.Item1.Value;
command.Parameters["@attrType"].Value = attr.Item2.ToString();
command.Parameters["@attrId"].Value = (createOrder++).ToString();
command.CommandText = String.Format(@"
INSERT INTO [{0}]
([TableSchema], [TableName], [ColumnName], [ColumnId], [AttributeName], [AttributeType], [AttributeEdgeId])
VALUES (@tableSchema, @tableName, @columnName, @columnid, @attrName, @attrType, @attrId)", MetadataTables[2]);
command.ExecuteNonQuery();
}
}
}
}
UpdateGlobalNodeView(tableSchema, tx);
if (externalTransaction == null)
{
tx.Commit();
}
return true;
}
catch (SqlException e)
{
if (externalTransaction == null)
{
tx.Rollback();
}
throw new SqlExecutionException("An error occurred when creating the node table.\n" + e.Message, e);
}
}
public void UpgradeNodeTableFunctionV100(SqlTransaction externalTransaction = null)
{
SqlTransaction tx = externalTransaction ?? Conn.BeginTransaction();
try
{
using (var command = Conn.CreateCommand())
{
command.Transaction = tx;
command.CommandText = string.Format(@"
select nt.TableId, nt.TableSchema, nt.TableName, ntc.ColumnName, ntc.ColumnId, ec.AttributeName, ec.AttributeType,ntc.ColumnRole
from
{0} as nt
join
{1} as ntc
on ntc.TableId = nt.TableId
left join
{2} as ec
on ec.ColumnId = ntc.ColumnId
where ntc.ColumnRole = @role1 or ntc.ColumnRole = @role2
order by ntc.TableId", MetadataTables[0], MetadataTables[1], MetadataTables[2]);
command.Parameters.AddWithValue("@role1", WNodeTableColumnRole.Edge);
command.Parameters.AddWithValue("@role2", WNodeTableColumnRole.NodeId);
string tableSchema = null;
string tableName = null;
string columnName = null;
// [colId, [columnName, [attributeName, attributeType]]]
var edgeDict =
new Dictionary<long, Tuple<string, List<Tuple<string, string>>>>();
long tableId = -1;
// [tableId, edgeDict]
var tableColDict =
new Dictionary
<long, Dictionary<long, Tuple<string, List<Tuple<string, string>>>>>();
// [tableId, [schema, tableName, userId]]
var tableInfoDict =
new Dictionary<long, Tuple<string, string, string>>();
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var curTableId = (long)reader["TableId"];
if (tableId == -1)
{
tableId = curTableId;
tableSchema = reader["TableSchema"].ToString();
tableName = reader["TableName"].ToString();
}
else if (curTableId != tableId)
{
tableColDict[tableId] = edgeDict;
tableInfoDict[tableId] = new Tuple<string, string, string>(tableSchema, tableName,
columnName);
tableSchema = reader["TableSchema"].ToString();
tableName = reader["TableName"].ToString();
columnName = null;
edgeDict = new Dictionary<long, Tuple<string, List<Tuple<string, string>>>>();
tableId = curTableId;
}
var role = (WNodeTableColumnRole)reader["ColumnRole"];
if (role == WNodeTableColumnRole.NodeId)
{
columnName = reader["ColumnName"].ToString();
//continue;
}
else if (role == WNodeTableColumnRole.Edge)
{
long colId = (long)reader["ColumnId"];
if (!reader.IsDBNull(5) && !reader.IsDBNull(6))
{
Tuple<string, List<Tuple<string, string>>> tuple;
if (edgeDict.TryGetValue(colId, out tuple))
{
tuple.Item2.Add(new Tuple<string, string>(reader["AttributeName"].ToString(),
reader["AttributeType"].ToString().ToLower()));
}
else
{
edgeDict[colId] =
new Tuple<string, List<Tuple<string, string>>>(
reader["ColumnName"].ToString(),
new List<Tuple<string, string>>
{
new Tuple<string, string>(reader["AttributeName"].ToString(),
reader["AttributeType"].ToString().ToLower())
});
}
}
else
{
edgeDict[colId] =
new Tuple<string, List<Tuple<string, string>>>(reader["ColumnName"].ToString(),
new List<Tuple<string, string>>());
}
}
}
tableColDict[tableId] = edgeDict;
tableInfoDict[tableId] = new Tuple<string, string, string>(tableSchema, tableName,
columnName);
}
foreach (var item in tableColDict)
{
var edgeList =
item.Value.Select(
e =>
new Tuple<string, int, List<Tuple<string, string>>>(e.Value.Item1, (int)e.Key,
e.Value.Item2)).ToList();
if (edgeList.Any())
{
var tableInfo = tableInfoDict[item.Key];
var assemblyName = tableInfo.Item1 + '_' + tableInfo.Item2;
GraphViewDefinedFunctionRegister register = new NodeTableRegister(assemblyName, tableInfo.Item2, edgeList,
tableInfo.Item3);
register.Register(Conn, tx);
}
}
}
if (externalTransaction == null)
{
tx.Commit();
}
}
catch (SqlException e)
{
if (externalTransaction == null)
{
tx.Rollback();
}
throw new SqlExecutionException("An error occurred when upgrading the node table function.", e);
}
}