public void convertSQLQueryToDocDBQuery(SQLtoNoSQLViewModel model)
{
//SqlTokenizer sqlTokenizer = new SqlTokenizer();
//SqlParsedInfo sqlParsedInfo = new SqlParsedInfo();// sqlTokenizer.BradFunction(model.SqlQuery);
SqlTokenizer2 tokenizer = new SqlTokenizer2();
string docDBQuery = tokenizer.convertSQLToDocDB(model);
//StringBuilder sb = new StringBuilder();
//// "SELECT "
//sb.Append(sqlParsedInfo.OperationName).Append(" ");
//if (sqlParsedInfo.ColumnsInSelectClause.Count == 0)
//{
// sb.Append("*");
//}
//else for (int i = 0; i < sqlParsedInfo.ColumnsInSelectClause.Count; ++i)
//{
// // These come back as TableName.Identifier,
// sb.Append(sqlParsedInfo.ColumnsInSelectClause[i]);
// if (i + 1 != sqlParsedInfo.ColumnsInSelectClause.Count)
// sb.Append(", ");
//}
//sb.Append(" FROM ").Append(sqlParsedInfo.TableName).Append(" ");
//// WHERE ... AND ... OR
//if(!sqlParsedInfo.WhereClause.Equals("WHERE "))
// sb.Append(sqlParsedInfo.WhereClause);
// Update the docDB query
//model.DocDBQuery = sb.ToString();
model.DocDBQuery = docDBQuery;
}
public async Task <ActionResult> SQLtoNoSQL(SQLtoNoSQLViewModel model, bool paging = true)
{
// Gather the data from the SQL query!
bool exceptionHappened = false;
try
{
SQLtoNoSQLHelper sqlHelper = new SQLtoNoSQLHelper();
// TODO: (1)
sqlHelper.convertSQLQueryToDocDBQuery(model);
// (2)
await ExecuteDocDBQuery(model, paging);
// (3)
// TODO: Temporary for now.. just pass the queryResult to the mega function
sqlHelper.executeSQLQuery(model);
}
catch (DocumentClientException de)
{
Exception baseException = de.GetBaseException();
model.QueryResult = String.Format("{0} error occurred: {1}, Message: {2}", de.StatusCode, de.Message, baseException.Message);
exceptionHappened = true;
}
catch (Exception e)
{
Exception baseException = e.GetBaseException();
model.QueryResult = String.Format("Error: {0}, Message: {1}", e.Message, baseException.Message);
exceptionHappened = true;
}
finally // TODO: Maybe do something here?
{
}
// Update the POST response
ViewBag.Message = "This is the SQLtoNoSQL page.";
// Tell user no results or update formatting if results are found
// implicit else: Exception happened
if (model.QueryResult.Equals(""))
{
model.QueryResult = "No Results were found for your query!";
}
else if (!exceptionHappened)
{
model.QueryResult = model.QueryResult.Replace("}", "}\n");
// Allows user to page the results, and load them on demand after query
ViewBag.LoadMoreResults = true;
}
return(View(model));
}
public ActionResult SQLtoNoSQL()
{
/* TODO TEST */
//SQLtoNoSQLHelper sqlHelper = new SQLtoNoSQLHelper();
//sqlHelper.ConvertDBtoSQL("Hello World!");
/* TODO TEST */
ViewBag.Message = "This is the SQLtoNoSQL page.";
SQLtoNoSQLViewModel model = new SQLtoNoSQLViewModel("SELECT * FROM c", "");
return(View(model));
}
// Step (6)
// Actually runs the original SQL query against the temp table we have created
private void QuerySQLTable(SQLtoNoSQLViewModel model, List<SQLSchemaData> schemaList)
{
try
{
// Query the table for values!
// TODO: Modify the real query to use our TempTable
// Modify the SQL command to use the TempTable
for (int i = 0; i < schemaList.Count; ++i)
{
string columnName = schemaList[i].ColumnName;
string original = model.TableName + "." + columnName;
string replacement = TempTableName + "." + columnName;
model.SqlQuery = model.SqlQuery.Replace(model.SqlQuery.Contains(original) ? original : columnName, replacement);
}
model.SqlQuery = model.SqlQuery.Replace(model.TableName + ".", TempTableName + ".");
model.SqlQuery = model.SqlQuery.Replace("FROM " + model.TableName, "FROM " + TempTableName);
SqlCommand sqlCommand = new SqlCommand(model.SqlQuery, SqlConnectionObject);//"SELECT * FROM " + TempTableName, SqlConnectionObject);
SqlDataReader sqlReader = sqlCommand.ExecuteReader();
StringBuilder sb = new StringBuilder();
while (sqlReader.Read())
{
sb.Append("{\n");
for (int i = 0; i < sqlReader.FieldCount; ++i)
{
sb.Append("\t").Append(sqlReader.GetName(i)).Append(":").Append(sqlReader.GetValue(i).ToString());
// Don't need comma for last element
if (i + 1 != sqlReader.FieldCount)
{
sb.Append(",\n");
}
}
sb.Append("\n}");
}
model.QueryResult = sb.ToString();
}
catch (Exception e)
{
Exception baseException = e.GetBaseException();
string exceptionString = String.Format("Error: {0}, Message: {1}", e.Message, baseException.Message);
}
}
// For now this function will do steps (3-6)
// model.Query = Original SQL query
// model.QueryResult = DocumentDB results running slimmer version of model.Query
public string executeSQLQuery(SQLtoNoSQLViewModel model)
{
// DocumentDB had no results, no need to convert anything
if (model.QueryResult.Equals(""))
{
goto exit;
}
// (3)
List<SQLSchemaData> schemaList = BuildSchemaList(model.QueryResult);
// (4-5)
string result = CreateTempSQLTableAndInsertJSON(schemaList, model.QueryResult);
// (6)
QuerySQLTable(model, schemaList);
SqlConnectionObject.Close();
exit:
return "";
}
// We can now execute specific "sql" query across a Database.Collection which is partitioned!
//
// 429: "Request rate is large" indicates that the application has exceeded the
// provisioned RU quota and should retry the request after some time interval
// 503: Service unavailable
//
// Clients may not catch 429 since .NET SDK will handle it implicitly and retry the request when exceeding RU's
// unless you have multiple concurrent clients: https://azure.microsoft.com/en-us/blog/performance-tips-for-azure-documentdb-part-2/
//
// What to do if 429 or 503 happen?: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/8edaa6bf-a141-4363-b280-0964d0525129/request-rate-is-too-large-azure-document-db?forum=AzureDocumentDB
// HTTP status codes: https://msdn.microsoft.com/en-us/library/azure/dn783364.aspx
private async Task ExecuteDocDBQuery(SQLtoNoSQLViewModel model, bool paging)
{
this.client = new DocumentClient(new Uri(EndpointURIString), PrimaryKey);
FeedOptions feedOptions = new FeedOptions {
MaxItemCount = (paging ? 3 : -1), EnableCrossPartitionQuery = true, RequestContinuation = (string)Session["ContinuationString"]
};
var collectionUri = UriFactory.CreateDocumentCollectionUri(DatabaseName, CollectionName);
IDocumentQuery <dynamic> docQuery = this.client.CreateDocumentQuery(collectionUri, model.DocDBQuery, feedOptions).AsDocumentQuery();
UInt64 documentCount = 0;
StringBuilder sb = new StringBuilder();
FeedResponse <dynamic> results;
// Make sure we can continue querying the partitions
do
{
// Print out all the documents in the Collection
try
{
// TODO: To disable paging, add while loop on docQuery.HasMoreResults, need to add UI option too...
do
{
// TODO: Return document count to the View?
results = await docQuery.ExecuteNextAsync();
documentCount += Convert.ToUInt64(results.Count);
foreach (dynamic result in results)
{
sb.Append(result);
}
} while (docQuery.HasMoreResults && !paging);
model.QueryResult = sb.ToString();
// This is the loading of results on demand to the user
Session["ContinuationString"] = results.ResponseContinuation;
}
catch (DocumentClientException exception)
{
if (exception != null &&
exception.StatusCode != null &&
((int)exception.StatusCode == 429 || (int)exception.StatusCode == 503))
{
Console.WriteLine("Query failed with status code {0} retrying after {1} seconds.", (int)exception.StatusCode, exception.RetryAfter.Seconds);
Thread.Sleep(exception.RetryAfter);
}
else
{
throw;
}
}
catch (AggregateException exception)
{
if (exception.InnerException.GetType() == typeof(DocumentClientException))
{
var docException = exception.InnerException as DocumentClientException;
if ((int)docException.StatusCode == 429 ||
(int)docException.StatusCode == 503)
{
Console.WriteLine("Query failed with status code {0} retrying after {1} seconds.", (int)docException.StatusCode, docException.RetryAfter.Seconds);
Thread.Sleep(docException.RetryAfter);
}
else
{
throw;
}
}
}
catch (Exception exception)
{
Console.WriteLine("Query failed with status code {0}", exception.StackTrace);
}
} while (false);//(docQuery != null && docQuery.HasMoreResults);
//return null;
}
public string convertSQLToDocDB(SQLtoNoSQLViewModel model)//string sqlQuery)
{
StringBuilder sb = new StringBuilder("");
model.SqlQuery = FormatSqlQueryString(model.SqlQuery);
// Good reference for what I was trying to do:
// http://stackoverflow.com/questions/4780728/regex-split-string-preserving-quotes/4780801#4780801
// Split on spaces/newline/carriage return, but don't split internal strings that have spaces inside
const string regexString = "(?<=^[^']*(?:'[^']*'[^']*)*)[\n\r ](?=(?:[^']*'[^']*')*[^']*$)";
string[] sqlTokenArray = Regex.Split(model.SqlQuery, regexString).Where(x => !String.IsNullOrEmpty(x)).ToArray();
// Get the table name
TableName = FindTableName(sqlTokenArray);
model.TableName = TableName;
FindColumns(sqlTokenArray);
// Build the SELECT statement with Columns
sb.Append("SELECT ");
for (int i = 0; i < Columns.Count; ++i)
{
sb.Append(Columns[i]).Append(i + 1 != Columns.Count ? ", " : "");
}
sb.Append(" FROM ").Append(TableName);
// No WHERE clause immediatly exit!
if (IndexOfWhere == -1)
{
goto exit;
}
sb.Append(" WHERE ");
// Build up the rest of the query
BuildQueryAfterWhereClause(sqlTokenArray, sb);
// Modify the SQL query to remove the contraints we will have applied against documentDB
var myList = sqlTokenArray.ToList();
int IndexOfGroup = Array.FindIndex(sqlTokenArray, x => x.Equals("GROUP", StringComparison.InvariantCultureIgnoreCase));
int IndexOfBy = Array.FindIndex(sqlTokenArray, x => x.Equals("BY", StringComparison.InvariantCultureIgnoreCase));
if (IndexOfBy != -1 && IndexOfGroup != -1 && (IndexOfGroup + 1 == IndexOfBy))
{
myList.RemoveRange(IndexOfWhere, IndexOfGroup - IndexOfWhere);
}
else
{
myList.RemoveRange(IndexOfWhere, myList.Count - IndexOfWhere);
}
StringBuilder sb1 = new StringBuilder();// newSqlQuery = "";
foreach (string s in myList)
{
sb1.Append(s).Append(" ");
}
model.SqlQuery = sb1.ToString();
exit:
string docDBQuery = sb.ToString();
docDBQuery = ReplaceColumnsWithAlias(docDBQuery);
return(docDBQuery);
}