Exemplo n.º 1
0
        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;
        }
Exemplo n.º 2
0
        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));
        }
Exemplo n.º 3
0
        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));
        }
Exemplo n.º 4
0
        // 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);
            }
        }
Exemplo n.º 5
0
        // 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 "";
        }
Exemplo n.º 6
0
        // 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;
        }
Exemplo n.º 7
0
        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);
        }