Esempio n. 1
0
        /// <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);
            }
        }
Esempio n. 2
0
        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);
            }
        }