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); }