public QueryEvent(SqlQuery query)
{
if (query == null)
throw new ArgumentNullException("query");
Query = query;
}
internal QueryResult(SqlQuery query, ITable result)
{
Query = query;
Result = result;
FormColumns(Result);
locked = 0;
}
public QueryExecuteRequest(long commitId, SqlQuery query)
{
if (query == null)
throw new ArgumentNullException("query");
CommitId = commitId;
Query = query;
}
public QueryEvent(IEventSource source, SqlQuery sourceQuery, string statementText)
: base(source, NotificationLevel.Verbose, EventClasses.Runtime, 1000, null)
{
if (sourceQuery == null)
throw new ArgumentNullException("sourceQuery");
SourceQuery = sourceQuery;
StatementText = statementText;
}
public QueryEvent(SqlQuery query, QueryEventType eventType, StatementResult[] result)
{
if (query == null)
throw new ArgumentNullException("query");
Query = query;
EventType = eventType;
Result = result;
}
internal QueryResult(SqlQuery query, StatementResult result, bool readAll)
{
Query = query;
Result = result;
FormColumns(Result);
if (readAll && Result.Type == StatementResultType.CursorRef)
ReadAll();
}
public void InnerJoinSelect()
{
var connection = CreateConnection();
var query = new SqlQuery("SELECT a.*, b.city, b.country FROM person a INNER JOIN lives b ON a.id = b.person_id WHERE a.first_name = 'Antonello';");
var result = SqlExecutor.Execute(connection, query);
Assert.IsNotNull(result);
Assert.AreEqual(1, result.Length);
Assert.AreEqual(1, result[0].RowCount);
}
internal Query(ISession session, SqlQuery sourceQuery)
{
Session = session;
SourceQuery = sourceQuery;
Context = session.Context.CreateQueryContext();
Context.RegisterInstance<IQuery>(this);
StartedOn = DateTimeOffset.UtcNow;
metadata = GetMetadata();
}
internal Query(ISession session, SqlQuery sourceQuery)
: base(session as IEventSource)
{
Session = session;
SourceQuery = sourceQuery;
Context = session.Context.CreateQueryContext();
Context.RegisterInstance<IQuery>(this);
StartedOn = DateTimeOffset.UtcNow;
Access = new RequestAccess(this);
}
public void GroupBySelect()
{
var connection = CreateConnection();
var query = new SqlQuery("SELECT a.*, COUNT(b.id) AS device_count FROM person a LEFT JOIN devices b ON a.id = b.person_id GROUP BY a.first_name HAVING a.first_name = 'Antonello';");
var result = SqlExecutor.Execute(connection, query);
Assert.IsNotNull(result);
Assert.AreEqual(1, result.Length);
Assert.AreEqual(1, result[0].RowCount);
Assert.IsNotNull(result[0].GetValue(result[0].TableInfo.ColumnCount - 1, 0));
Assert.AreEqual(4, result[0].GetValue(result[0].TableInfo.ColumnCount - 1, 0).ToNumber().ToInt32());
}
/// <summary>
/// This method transforms the input SQL query into a set of statements,
/// prepares and executes them against the provided context.
/// </summary>
/// <param name="context">The context used to prepare and execute the statements
/// resolved from the compilation of the input query.</param>
/// <param name="query">The input SQL query with optional parameters, that is
/// compiled into a set of statements to be executed.</param>
/// <remarks>
/// The method first tries to resolve the compiled statements from the specialized
/// cache (<see cref="StatementCache"/>) from the system, to reduce the compilation time.
/// </remarks>
/// <returns>
/// Returns an array of <see cref="ITable"/> objects that represent the results
/// of the execution of the input query.
/// </returns>
public static ITable[] Execute(IQueryContext context, SqlQuery query)
{
if (query == null)
throw new ArgumentNullException("query");
var sqlSouce = query.Text;
// TODO: find it from the cache...
var statements = SqlStatement.Parse(sqlSouce);
// TODO: set it in cache ...
var preparer = new QueryPreparer(query);
bool statementSeen = false;
var results = new List<ITable>();
foreach (var statement in statements) {
context.RegisterQuery(statement);
// TODO: Invoke diagnostics for the preparation...
var prepared = statement.Prepare(preparer, context);
ITable result;
try {
result = prepared.Execute(context);
} catch(StatementException ex) {
context.RegisterError(ex);
throw;
} catch (Exception ex) {
var sex = new StatementException("An unhanded error occurred while executing the statement.", ex);
context.RegisterError(sex);
throw sex;
} finally {
statementSeen = true;
}
results.Add(result);
}
if (!statementSeen)
throw new SqlParseException("The input query was not parsed in any statements that could be executed.");
return results.ToArray();
}
public static ITable[] Execute(IDatabaseConnection connection, SqlQuery query)
{
// StatementTree caching
// Substitute all parameter substitutions in the statement tree.
// TODO: IExpressionPreparer preparer = new QueryPreparer(query);
// Create a new parser and set the parameters...
IEnumerable<Statement> statements;
string commandText = query.Text;
try {
lock (SqlParser) {
SqlParser.ReInit(new StreamReader(new MemoryStream(Encoding.Unicode.GetBytes(commandText)), Encoding.Unicode));
SqlParser.Reset();
// Parse the statement.
statements = SqlParser.SequenceOfStatements();
}
} catch (ParseException e) {
var tokens = SqlParser.token_source.tokenHistory;
throw new SqlParseException(e, commandText, tokens);
}
List<ITable> results = new List<ITable>();
foreach (Statement parsedStatement in statements) {
var statement = parsedStatement;
// TODO: statement = statement.PrepareExpressions(preparer);
// Convert the StatementTree to a statement object
statement.Query = query;
DatabaseQueryContext context = new DatabaseQueryContext(connection);
// Prepare the statement
statement = statement.PrepareStatement(context);
// Evaluate the SQL statement.
results.Add(statement.Evaluate(context));
}
return results.ToArray();
}
internal IQueryResponse[] ExecuteQuery(int commitId, SqlQuery query)
{
try {
return Client.ExecuteQuery(commitId, query);
} catch (Exception ex) {
throw new DeveelDbException("An error occurred while executing a query.", ex);
}
}
/// <inheritdoc/>
public virtual IQueryResponse[] ExecuteQuery(SqlQuery query)
{
CheckNotDisposed();
// Record the Query start time
DateTime startTime = DateTime.Now;
// Where Query result eventually resides.
ResultSetInfo resultSetInfo;
int resultId = -1;
// For each StreamableObject in the SQLQuery object, translate it to a
// IRef object that presumably has been pre-pushed onto the server from
// the client.
bool blobsWereFlushed = false;
object[] vars = query.Variables;
if (vars != null) {
for (int i = 0; i < vars.Length; ++i) {
object ob = vars[i];
// This is a streamable object, so convert it to a *IRef
if (ob != null && ob is StreamableObject) {
StreamableObject sObject = (StreamableObject) ob;
// Flush the streamable object from the cache
// Note that this also marks the blob as complete in the blob store.
IRef reference = FlushLargeObjectRefFromCache(sObject.Identifier);
// Set the IRef object in the Query.
vars[i] = reference;
// There are blobs in this Query that were written to the blob store.
blobsWereFlushed = true;
}
}
}
// Evaluate the sql Query.
Table[] results = SqlQueryExecutor.Execute(dbConnection, query);
IQueryResponse[] responses = new IQueryResponse[results.Length];
int j = 0;
foreach (Table result in results) {
try {
// Put the result in the result cache... This will Lock this object
// until it is removed from the result set cache. Returns an id that
// uniquely identifies this result set in future communication.
// NOTE: This locks the roots of the table so that its contents
// may not be altered.
resultSetInfo = new ResultSetInfo(query, result);
resultId = AddResultSet(resultSetInfo);
} catch (Exception e) {
// If resultId set, then dispose the result set.
if (resultId != -1)
DisposeResultSet(resultId);
// Handle the throwable during Query execution
throw HandleExecuteThrowable(e, query);
}
// The time it took the Query to execute.
TimeSpan taken = DateTime.Now - startTime;
// Return the Query response
responses[j] = new QueryResponse(resultId, resultSetInfo, (int) taken.TotalMilliseconds, "");
j++;
}
return responses;
}
/// <inheritdoc/>
public object Clone()
{
SqlQuery q = new SqlQuery();
q.text = text;
q.parameters = (Object[])parameters.Clone();
q.parameters_index = parameters_index;
q.parameter_count = parameter_count;
q.prepared = prepared;
return q;
}
/// <summary>
/// Wraps an <see cref="Exception"/> thrown by the execution of a Query in
/// <see cref="DatabaseConnection"/> with an <see cref="DataException"/> and
/// puts the appropriate error messages to the debug log.
/// </summary>
/// <param name="e"></param>
/// <param name="query"></param>
/// <returns></returns>
protected DataException HandleExecuteThrowable(Exception e, SqlQuery query)
{
if (e is ParseException) {
Logger.Warning(this, e);
// Parse exception when parsing the SQL.
String msg = e.Message;
msg = msg.Replace("\r", "");
return new DbDataException(msg, msg, 35, e);
}
if (e is TransactionException) {
TransactionException te = (TransactionException) e;
// Output Query that was in error to debug log.
Logger.Info(this, "Transaction error on: " + query);
Logger.Info(this, e);
// Denotes a transaction exception.
return new DbDataException(e.Message, e.Message, 200 + te.Type, e);
}
// Output Query that was in error to debug log.
Logger.Warning(this, "Exception thrown during Query processing on: " + query);
Logger.Warning(this, e);
// Error, we need to return exception to client.
return new DbDataException(e.Message, e.Message, 1, e);
}
protected IQueryResponse[] CoreExecuteQuery(IQueryContext context, string text, IEnumerable<QueryParameter> parameters)
{
// Where Query result eventually resides.
int resultId = -1;
// For each StreamableObject in the query object, translate it to a
// IRef object that presumably has been pre-pushed onto the server from
// the client.
// Evaluate the sql Query.
var query = new SqlQuery(text);
if (parameters != null) {
// TODO: Download the Large-Objects and replace with a reference
}
var stopwatch = new Stopwatch();
stopwatch.Start();
var results = context.ExecuteQuery(query);
var responses = new IQueryResponse[results.Length];
int j = 0;
foreach (var result in results) {
QueryResult queryResult;
try {
queryResult = new QueryResult(query, result);
resultId = AddResult(queryResult);
} catch (Exception e) {
if (resultId != -1)
DisposeResult(resultId);
throw;
}
var taken = stopwatch.Elapsed;
// Return the Query response
responses[j] = new QueryResponse(resultId, queryResult, (int)taken.TotalMilliseconds, "");
j++;
}
stopwatch.Stop();
return responses;
}
/// <summary>
/// Sends a command to the server to process a command.
/// </summary>
/// <param name="sql"></param>
/// <remarks>
/// The response from the server will contain a 'result_id' that is a unique
/// number for refering to the result. It also contains information about the
/// columns input the table, and the total number of rows input the result.
/// </remarks>
/// <returns>
/// Returns the dispatch id key for the response from the server.
/// </returns>
public int ExecuteQuery(SqlQuery sql)
{
lock (this) {
int dispatchId = NextDispatchId();
commandWriter.Write(ProtocolConstants.Query);
commandWriter.Write(dispatchId);
sql.WriteTo(commandWriter);
FlushCommand();
return dispatchId;
}
}
/// <inheritdoc/>
public IQueryResponse[] ExecuteQuery(SqlQuery sql)
{
try {
// Execute the command
int dispatchId = connectionThread.ExecuteQuery(sql);
// Get the response
ServerCommand command = connectionThread.ReceiveCommand(DeveelDbConnection.QueryTimeout, dispatchId);
// If command == null then we timed output
if (command == null)
throw new DataException("Query timed output after " + DeveelDbConnection.QueryTimeout + " seconds.");
BinaryReader input = new BinaryReader(command.GetInputStream());
// Query response protocol...
int status = input.ReadInt32();
if (status == ProtocolConstants.Success) {
int resultId = input.ReadInt32();
int queryTime = input.ReadInt32();
int rowCount = input.ReadInt32();
int colCount = input.ReadInt32();
ColumnDescription[] col_list = new ColumnDescription[colCount];
for (int i = 0; i < colCount; ++i) {
col_list[i] = ColumnDescription.ReadFrom(input);
}
return new IQueryResponse[] {new QueryResponseImpl(resultId, queryTime, colCount, rowCount, col_list)};
}
if (status == ProtocolConstants.Exception) {
int dbCode = input.ReadInt32();
string message = input.ReadString();
string stack_trace = input.ReadString();
throw new DbDataException(message, null, dbCode, stack_trace);
}
if (status == ProtocolConstants.AuthenticationError) {
// Means we could perform the command because user doesn't have enough
// rights.
string accessType = input.ReadString();
string tableName = input.ReadString();
throw new DataException("User doesn't have enough privs to " + accessType + " table " + tableName);
}
throw new DataException("Illegal response code from server.");
} catch (IOException e) {
LogException(e);
throw new DataException("IO Error: " + e.Message);
}
}
public static void RegisterQuery(this IQueryContext context, SqlQuery query, string statementText)
{
context.RegisterEvent(new QueryEvent(context.Session, query, statementText));
}
protected IQueryResponse[] ExecuteQuery(long commitId, SqlQuery query)
{
AssertAuthenticated();
IQuery queryContext;
if (commitId > 0) {
queryContext = OpenQueryContext(commitId);
} else {
queryContext = CreateQueryContext();
}
return ExecuteQuery(queryContext, query);
}
public IQueryResponse[] ExecuteQuery(int commitId, SqlQuery query)
{
var response = SendMessage(new QueryExecuteRequest(commitId, query))
as QueryExecuteResponse;
if (response == null)
throw new InvalidOperationException("No response from the server");
return response.QueryResponse;
}
/// <summary>
/// Constructs the result set.
/// </summary>
/// <param name="query"></param>
/// <param name="result"></param>
public ResultSetInfo(SqlQuery query, Table result)
{
this.query = query;
this.result = result;
streamableBlobMap = new Dictionary<long, IRef>();
resultRowCount = result.RowCount;
// HACK: Read the contents of the first row so that we can pick up
// any errors with reading, and also to fix the 'uniquekey' bug
// that causes a new transaction to be started if 'uniquekey' is
// a column and the value is resolved later.
IRowEnumerator rowEnum = result.GetRowEnumerator();
if (rowEnum.MoveNext()) {
int row_index = rowEnum.RowIndex;
for (int c = 0; c < result.ColumnCount; ++c) {
result.GetCell(c, row_index);
}
}
// If simple enum, note it here
resultIsSimpleEnum = (rowEnum is SimpleRowEnumerator);
rowEnum = null;
// Build 'row_index_map' if not a simple enum
if (!resultIsSimpleEnum) {
rowIndexMap = new List<int>(result.RowCount);
IRowEnumerator en = result.GetRowEnumerator();
while (en.MoveNext()) {
rowIndexMap.Add(en.RowIndex);
}
}
// This is a safe operation provides we are shared.
// Copy all the TableField columns from the table to our own
// ColumnDescription array, naming each column by what is returned from
// the 'GetResolvedVariable' method.
int colCount = result.ColumnCount;
colDesc = new ColumnDescription[colCount];
for (int i = 0; i < colCount; ++i) {
VariableName v = result.GetResolvedVariable(i);
string fieldName;
if (v.TableName == null) {
// This means the column is an alias
fieldName = "@a" + v.Name;
} else {
// This means the column is an schema/table/column reference
fieldName = "@f" + v;
}
colDesc[i] = new ColumnDescription(fieldName, result.GetColumnInfo(i));
}
locked = 0;
}
private void OnQueryCommand(QueryEvent e)
{
lastCommandTime = e.TimeStamp;
lastCommand = e.Query;
lastCommandResult = e.Result;
}
protected IQueryResponse[] CoreExecuteQuery(string text, IEnumerable<SqlQueryParameter> parameters)
{
// Record the Query start time
DateTime startTime = DateTime.Now;
// Where Query result eventually resides.
int resultId = -1;
// For each StreamableObject in the query object, translate it to a
// IRef object that presumably has been pre-pushed onto the server from
// the client.
// Evaluate the sql Query.
var query = new SqlQuery(text);
if (parameters != null) {
foreach (var parameter in parameters) {
var preparedParam = parameter.Value;
if (preparedParam is StreamableObject) {
var obj = (StreamableObject) preparedParam;
IRef objRef = CompleteStream(obj.Identifier);
preparedParam = objRef;
}
query.Parameters.Add(new SqlQueryParameter(parameter.Name, preparedParam));
}
}
Table[] results = SqlQueryExecutor.Execute(Session.Connection, query);
var responses = new IQueryResponse[results.Length];
int j = 0;
foreach (Table result in results) {
QueryResult queryResult;
try {
// Put the result in the result cache... This will Lock this object
// until it is removed from the result set cache. Returns an id that
// uniquely identifies this result set in future communication.
// NOTE: This locks the roots of the table so that its contents
// may not be altered.
queryResult = new QueryResult(query, result);
resultId = AddResult(queryResult);
} catch (Exception e) {
// If resultId set, then dispose the result set.
if (resultId != -1)
DisposeResult(resultId);
throw;
}
// The time it took the Query to execute.
TimeSpan taken = DateTime.Now - startTime;
// Return the Query response
responses[j] = new QueryResponse(resultId, queryResult, (int) taken.TotalMilliseconds, "");
j++;
}
return responses;
}
internal QueryResult(SqlQuery query, ITable result)
{
Query = query;
Result = result;
}
private SqlQuery CreateQuery()
{
QueryParameter[] queryParameters;
if (prepared && preparedParameters != null) {
queryParameters = preparedParameters.ToArray();
} else {
queryParameters = new QueryParameter[parameters.Count];
for (int i = 0; i < parameters.Count; i++) {
var parameter = parameters[i];
var queryParam = PrepareParameter(parameter);
queryParameters[i] = queryParam;
}
}
var query = new SqlQuery(CommandText, Connection.Settings.ParameterStyle);
// now verify all parameter names are consistent
foreach (var parameter in queryParameters) {
if (connection.Settings.ParameterStyle == QueryParameterStyle.Marker) {
if (!String.IsNullOrEmpty(parameter.Name) &&
parameter.Name != QueryParameter.Marker)
throw new InvalidOperationException();
} else if (connection.Settings.ParameterStyle == QueryParameterStyle.Named) {
if (String.IsNullOrEmpty(parameter.Name))
throw new InvalidOperationException("Named parameters must have a name defined.");
if (parameter.Name == QueryParameter.Marker)
throw new InvalidOperationException("Cannot set the parameter marker in a named parameter query.");
var paramName = parameter.Name;
if (paramName[0] == QueryParameter.NamePrefix)
paramName = paramName.Substring(1);
if (paramName.Length < 1)
throw new InvalidOperationException("Invalid parameter name: cannot specify only the variable bind prefix.");
}
query.Parameters.Add(parameter);
}
return query;
}
private SqlQuery CreateQuery()
{
QueryParameter[] queryParameters;
if (prepared && preparedParameters != null) {
queryParameters = preparedParameters.ToArray();
} else {
queryParameters = new QueryParameter[parameters.Count];
for (int i = 0; i < parameters.Count; i++) {
var parameter = parameters[i];
var queryParam = PrepareParameter(parameter);
queryParameters[i] = queryParam;
}
}
var query = new SqlQuery(CommandText);
// now verify all parameter names are consistent
foreach (var parameter in queryParameters) {
if (connection.Settings.ParameterStyle == QueryParameterStyle.Marker) {
if (!String.IsNullOrEmpty(parameter.Name) &&
parameter.Name != QueryParameter.Marker)
throw new InvalidOperationException();
} else if (connection.Settings.ParameterStyle == QueryParameterStyle.Named) {
if (String.IsNullOrEmpty(parameter.Name))
throw new InvalidOperationException("Named parameters must have a name defined.");
if (parameter.Name == QueryParameter.Marker)
throw new InvalidOperationException();
if (parameter.Name.Length <= 1)
throw new InvalidOperationException();
if (!Char.IsLetter(parameter.Name[0]) &&
parameter.Name[0] != QueryParameter.NamePrefix)
throw new InvalidOperationException();
}
query.Parameters.Add(parameter);
}
return query;
}
public QueryPreparer(SqlQuery query)
{
this.query = query;
}
protected virtual IQueryResponse[] ExecuteQuery(IQuery context, SqlQuery query)
{
// TODO: Log a debug message..
IQueryResponse[] response = null;
try {
// Execute the Query (behaviour for this comes from super).
response = CoreExecuteQuery(context, query.Text, query.Parameters, query.ParameterStyle);
// Return the result.
return response;
} finally {
// This always happens after tables are unlocked.
// Also guarenteed to happen even if something fails.
// If we are in auto-commit mode then commit the Query here.
// Do we auto-commit?
if (context.AutoCommit()) {
// If an error occured then roll-back
if (response == null) {
// Rollback.
context.Session.Rollback();
} else {
try {
// Otherwise commit.
context.Session.Commit();
} catch (Exception) {
foreach (IQueryResponse queryResponse in response) {
// Dispose this response if the commit failed.
DisposeResult(queryResponse.ResultId);
}
// And throw the SQL Exception
throw;
}
}
}
}
}
