|
public Parse ( TextReader queryInput, IList |
||
| queryInput | TextReader | The query string |
| errors | IList |
A list of parsing errors |
| return | ||
/// <summary>
/// Returns the translated T-SQL script. For testing only.
/// </summary>
/// <returns>The translated T-SQL script</returns>
internal string GetTsqlQuery()
{
var sr = new StringReader(CommandText);
var parser = new GraphViewParser();
IList<ParseError> errors;
var script = parser.Parse(sr, out errors) as WSqlScript;
if (errors.Count > 0)
throw new SyntaxErrorException(errors);
if (errors.Count > 0)
throw new SyntaxErrorException(errors);
// Translation and Check CheckInvisibleColumn
using (SqlTransaction tx = Connection.BeginTransaction())
{
var visitor = new TranslateMatchClauseVisitor(tx);
visitor.Invoke(script);
return script.ToString();
}
}
/// <summary>
/// Drops procedure and related metadata.
/// </summary>
/// <param name="sqlStr"> Name of procedure to be dropped.</param>
/// <param name="externalTransaction">An existing SqlTransaction instance under which the drop procedure will occur.</param>
/// <returns>Returns true if the statement is successfully executed.</returns>
public bool DropProcedure(string sqlStr, SqlTransaction externalTransaction = null)
{
// get syntax tree of DROP TABLE command
var parser = new GraphViewParser();
var sr = new StringReader(sqlStr);
IList<ParseError> errors;
var script = parser.Parse(sr, out errors) as WSqlScript;
if (errors.Count > 0)
throw new SyntaxErrorException(errors);
if (script == null)
return false;
var statement = script.Batches[0].Statements[0] as WDropProcedureStatement;
if (statement == null)
return false;
SqlTransaction tran;
if (externalTransaction == null)
{
tran = Conn.BeginTransaction();
}
else
{
tran = externalTransaction;
}
try
{
using (var command = new SqlCommand(null, Conn, tran))
{
// delete metadata
foreach (var delObject in statement.Objects)
{
var proSchema = delObject.SchemaIdentifier == null ? "dbo" : delObject.SchemaIdentifier.Value;
var procName = delObject.BaseIdentifier.Value;
command.Parameters.Clear();
command.CommandText = string.Format(
@"DELETE FROM {0} WHERE ProcSchema = @procSchema AND ProcName = @procName",
MetadataTables[4]);
command.Parameters.AddWithValue("@procSchema", proSchema);
command.Parameters.AddWithValue("@procName", procName);
command.ExecuteNonQuery();
}
// drop procedure
command.CommandText = sqlStr;
command.ExecuteNonQuery();
if (externalTransaction == null)
{
tran.Commit();
}
return true;
}
}
catch (SqlException e)
{
if (externalTransaction == null)
{
tran.Rollback();
}
throw new SqlExecutionException("An error occurred when dropping the procedure.", e);
}
}
/// <summary>
/// Create procedure and related metadata.
/// </summary>
/// <param name="sqlStr"> A create procedure statement with metadata.</param>
/// <param name="externalTransaction">An existing SqlTransaction instance under which the create procedure will occur.</param>
/// <returns>Returns true if the statement is successfully executed.</returns>
public bool CreateProcedure(string sqlStr, SqlTransaction externalTransaction = null)
{
// get syntax tree of CREATE Procedure command
var parser = new GraphViewParser();
var sr = new StringReader(sqlStr);
IList<ParseError> errors;
var script = parser.Parse(sr, out errors) as WSqlScript;
if (errors.Count > 0)
throw new SyntaxErrorException(errors);
// Translation
var modVisitor = new TranslateDataModificationVisitor(Conn);
modVisitor.Invoke(script);
var matchVisitor = new TranslateMatchClauseVisitor(Conn);
matchVisitor.Invoke(script);
if (script == null)
return false;
var statement = script.Batches[0].Statements[0] as WCreateProcedureStatement;
if (statement == null)
return false;
var procName = statement.ProcedureReference.Name;
if (procName.SchemaIdentifier == null)
procName.Identifiers.Insert(0, new Identifier {Value = "dbo"});
bool exists = false;
SqlTransaction tran;
if (externalTransaction == null)
{
tran = Conn.BeginTransaction();
}
else
{
tran = externalTransaction;
}
try
{
using (var cmd = Conn.CreateCommand())
{
cmd.Transaction = tran;
cmd.CommandText = script.ToString();
cmd.ExecuteNonQuery();
cmd.CommandText = string.Format(
@"SELECT ProcID FROM {0} WHERE ProcName = @procName AND ProcSchema = @procSchema",
MetadataTables[4]);
cmd.Parameters.AddWithValue("@procName", procName.BaseIdentifier.Value);
cmd.Parameters.AddWithValue("@procSchema", procName.SchemaIdentifier.Value);
using (var reader = cmd.ExecuteReader())
{
if (reader.Read())
exists = true;
}
if (!exists)
{
cmd.CommandText = string.Format(@"
INSERT INTO [{0}]([ProcSchema], [ProcName]) VALUES (@schema, @name)", MetadataTables[4]);
cmd.Parameters.AddWithValue("@schema", procName.SchemaIdentifier.Value);
cmd.Parameters.AddWithValue("@name", procName.BaseIdentifier.Value);
cmd.ExecuteNonQuery();
}
if (externalTransaction == null)
{
tran.Commit();
}
}
}
catch (SqlException e)
{
if (externalTransaction == null)
{
tran.Rollback();
}
throw new SqlExecutionException("An error occurred when creating the procedure.", e);
}
return true;
}
/// <summary>
/// Drops node table and related metadata.
/// </summary>
/// <param name="sqlStr"> Name of table to be dropped.</param>
/// <param name="externalTransaction">An existing SqlTransaction instance under which the drop node table will occur.</param>
/// <returns>Returns true if the statement is successfully executed.</returns>
public bool DropNodeTable(string sqlStr, SqlTransaction externalTransaction = null)
{
// get syntax tree of DROP TABLE command
var parser = new GraphViewParser();
var sr = new StringReader(sqlStr);
IList<ParseError> errors;
var script = parser.Parse(sr, out errors) as WSqlScript;
if (errors.Count > 0)
throw new SyntaxErrorException(errors);
if (script == null)
return false;
var statement = script.Batches[0].Statements[0] as WDropTableStatement;
if (statement == null)
return false;
SqlTransaction tran;
if (externalTransaction == null)
{
tran = Conn.BeginTransaction();
}
else
{
tran = externalTransaction;
}
try
{
// delete metadata
using (var command = new SqlCommand(null, Conn, tran))
{
foreach (var obj in statement.Objects)
{
var tableName = obj.BaseIdentifier.Value;
var tableSchema = obj.SchemaIdentifier != null
? obj.SchemaIdentifier.Value
: "dbo";
var edgeColumns = GetGraphEdgeColumns(tableSchema, tableName, tran);
command.Parameters.AddWithValue("@tableName", tableName);
command.Parameters.AddWithValue("@tableSchema", tableSchema);
foreach (var table in MetadataTables)
{
if (table == MetadataTables[4] || table == MetadataTables[5] || table == MetadataTables[6] ||
table == MetadataTables[7])
continue;
command.CommandText = String.Format(CultureInfo.CurrentCulture, @"
DELETE FROM [{0}]
WHERE [TableName] = @tableName AND [TableSchema] = @tableSchema", table);
command.ExecuteNonQuery();
}
foreach (var edgeColumn in edgeColumns)
{
command.CommandText = String.Format(CultureInfo.CurrentCulture, @"
DROP TABLE [{0}_{1}_{2}_Sampling]",
tableSchema, tableName, edgeColumn.Item1);
command.ExecuteNonQuery();
}
var assemblyName = tableSchema + '_' + tableName;
foreach (var edgeColumn in edgeColumns)
{
if (edgeColumn.Item2)
{
command.CommandText = string.Format(
@"DROP FUNCTION [{0}_{1}_Decoder];
DROP FUNCTION [{0}_{1}_Recycle];
DROP FUNCTION [{0}_{1}_PathDecoder];
DROP FUNCTION [{0}_{1}_bfs];
DROP AGGREGATE [{0}_{1}_Encoder];",
assemblyName,
edgeColumn.Item1);
}
else
{
command.CommandText = string.Format(
@"DROP FUNCTION [{0}_{1}_Decoder];
DROP FUNCTION [{0}_{1}_Recycle];
DROP AGGREGATE [{0}_{1}_Encoder];",
assemblyName,
edgeColumn.Item1);
}
command.ExecuteNonQuery();
}
if (edgeColumns.Count == 0)
continue;
command.CommandText = @"DROP ASSEMBLY [" + assemblyName + "_Assembly]";
command.ExecuteNonQuery();
}
// drop node table
command.CommandText = sqlStr;
command.ExecuteNonQuery();
if (externalTransaction == null)
{
tran.Commit();
}
return true;
}
}
catch (SqlException e)
{
if (externalTransaction == null)
{
tran.Rollback();
}
throw new SqlExecutionException("An error occurred when dropping the node table.", e);
}
}
public int ExecuteNonQuery()
{
try
{
if (CommandType == CommandType.StoredProcedure)
{
if (Tx != null)
{
Command.Transaction = Tx;
}
Command.CommandText = CommandText;
return Command.ExecuteNonQuery();
}
var sr = new StringReader(CommandText);
var parser = new GraphViewParser();
IList<ParseError> errors;
var script = parser.Parse(sr, out errors) as WSqlScript;
if (errors.Count > 0)
throw new SyntaxErrorException(errors);
bool externalTransaction = true;
if (Tx == null)
{
externalTransaction = false;
Tx = Connection.BeginTransaction();
}
// Translation
var modVisitor = new TranslateDataModificationVisitor(Tx);
modVisitor.Invoke(script);
var matchVisitor = new TranslateMatchClauseVisitor(Tx);
matchVisitor.Invoke(script);
Command.CommandText = script.ToString();
Command.Transaction = Tx;
#if DEBUG
// For debugging
OutputResult(CommandText, Command.CommandText);
#endif
int res = Command.ExecuteNonQuery();
if (!externalTransaction)
{
Tx.Commit();
Tx.Dispose();
Tx = null;
}
return res;
}
catch (SqlException e)
{
throw new SqlExecutionException("An error occurred when executing the query", e);
}
}
public SqlDataReader ExecuteReader()
{
try
{
if (CommandType == CommandType.StoredProcedure)
{
if (Tx != null)
{
Command.Transaction = Tx;
}
Command.CommandText = CommandText;
return Command.ExecuteReader();
}
var sr = new StringReader(CommandText);
var parser = new GraphViewParser();
IList<ParseError> errors;
var script = parser.Parse(sr, out errors) as WSqlScript;
if (errors.Count > 0)
throw new SyntaxErrorException(errors);
if (Tx == null)
{
using(SqlConnection translationConnection = new SqlConnection(Connection.Conn.ConnectionString))
{
translationConnection.Open();
using (SqlTransaction translationTx = translationConnection.BeginTransaction(System.Data.IsolationLevel.RepeatableRead))
{
var visitor = new TranslateMatchClauseVisitor(translationTx);
visitor.Invoke(script);
// Executes translated SQL
Command.CommandText = script.ToString();
#if DEBUG
// For debugging
OutputResult(CommandText, Command.CommandText);
//throw new GraphViewException("No Execution");
#endif
var reader = Command.ExecuteReader();
translationTx.Commit();
return reader;
}
}
}
else
{
var visitor = new TranslateMatchClauseVisitor(Tx);
visitor.Invoke(script);
// Executes translated SQL
Command.CommandText = script.ToString();
#if DEBUG
// For debugging
OutputResult(CommandText, Command.CommandText);
//throw new GraphViewException("No Execution");
#endif
var reader = Command.ExecuteReader();
return reader;
}
}
catch (SqlException e)
{
throw new SqlExecutionException("An error occurred when executing the query", e);
}
}
/// <summary>
/// Drops node table and related metadata.
/// </summary>
/// <param name="sqlStr"> Name of table to be dropped.</param>
/// <param name="externalTransaction">An existing SqlTransaction instance under which the drop node table will occur.</param>
/// <returns>Returns true if the statement is successfully executed.</returns>
public bool DropNodeTable(string sqlStr, SqlTransaction externalTransaction = null)
{
// get syntax tree of DROP TABLE command
var parser = new GraphViewParser();
var sr = new StringReader(sqlStr);
IList<ParseError> errors;
var script = parser.Parse(sr, out errors) as WSqlScript;
if (errors.Count > 0)
throw new SyntaxErrorException(errors);
if (script == null)
return false;
var statement = script.Batches[0].Statements[0] as WDropTableStatement;
if (statement == null)
return false;
SqlTransaction tran;
if (externalTransaction == null)
{
tran = Conn.BeginTransaction();
}
else
{
tran = externalTransaction;
}
try
{
// delete metadata
using (var command = new SqlCommand(null, Conn, tran))
{
var schemaSet = new HashSet<string>();
foreach (var obj in statement.Objects)
{
var tableName = obj.BaseIdentifier.Value;
var tableSchema = obj.SchemaIdentifier != null
? obj.SchemaIdentifier.Value
: "dbo";
if (!schemaSet.Contains(tableSchema.ToLower()))
{
schemaSet.Add(tableSchema.ToLower());
}
command.Parameters.AddWithValue("@tableName", tableName);
command.Parameters.AddWithValue("@tableSchema", tableSchema);
var edgeColumns = GetGraphEdgeColumns(tableSchema, tableName, tran);
if (edgeColumns.Count > 0)
{
var assemblyName = tableSchema + '_' + tableName;
foreach (var edgeColumn in edgeColumns)
{
// isEdgeView
if (edgeColumn.Item3)
DropEdgeView(tableSchema, tableName,
edgeColumn.Item1 /*edgeView name*/, edgeColumn.Item6 /*isRevEdgeView*/, tran);
else
{
command.CommandText = String.Format(CultureInfo.CurrentCulture, @"
DROP TABLE [{0}_{1}_{2}_Sampling]",
tableSchema, tableName, edgeColumn.Item1);
command.ExecuteNonQuery();
// skip reversed edges since they have no UDF
if (!edgeColumn.Item6)
{
foreach (var it in _currentTableUdf)
{
command.CommandText = string.Format(
@"DROP {2} [{0}_{1}_{3}];",
assemblyName,
edgeColumn.Item1, it.Item1, it.Item2);
command.ExecuteNonQuery();
}
}
}
}
// skip tables which have only reversed edge columns
if (edgeColumns.Count(x => !x.Item6) > 0)
{
command.CommandText = @"DROP ASSEMBLY [" + assemblyName + "_Assembly]";
command.ExecuteNonQuery();
}
}
foreach (var table in MetadataTables)
{
if (table == MetadataTables[4] || table == MetadataTables[5] || table == MetadataTables[6] ||
table == MetadataTables[7])
continue;
command.CommandText = String.Format(CultureInfo.CurrentCulture, @"
DELETE FROM [{0}]
WHERE [TableName] = @tableName AND [TableSchema] = @tableSchema", table);
command.ExecuteNonQuery();
}
}
// drop node table
command.CommandText = sqlStr;
command.ExecuteNonQuery();
foreach (var it in schemaSet)
{
UpdateGlobalNodeView(it, tran);
}
if (externalTransaction == null)
{
tran.Commit();
}
return true;
}
}
catch (SqlException e)
{
if (externalTransaction == null)
{
tran.Rollback();
}
throw new SqlExecutionException("An error occurred when dropping the node table.", e);
}
}