Source

BsonSql / BsonSql.Driver / Builders / Query.cs

Full commit
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using BsonSql.Driver.Builders.Enums;
using System.Data.SqlClient;
using MongoDB.Bson;
using BsonSql.Common;
using System.Collections.Concurrent;

namespace BsonSql.Driver.Builders
{
	public class Query
	{
		internal bool _compiled = false;
		internal SqlCommand _command = null;
		internal ConcurrentDictionary<string, BsonSqlFieldWrapper> _wrappedValuesForWhereClause = null;
		internal ConcurrentDictionary<string, string> _valuesNeedingRetrieval = null;
		internal string _whereClause = null;
		internal string _orderByClause = null;
		internal List<Join> _joins = new List<Join>();

		public WhereClauseBuilder Where { get; set; }
		public List<OrderByClause> OrderBy { get; set; }
		public Dictionary<string, string> StringValues { get; set; }

        //used by others to determine if they have processed a query before for storing where clauses
        public string Id = Guid.NewGuid().ToString();

		private object _sync = new object();

		public string SqlWhereClause { get { return _whereClause; } }
		public string SqlOrderByClause { get { return _orderByClause; } }

		public Query()
		{
			Where = new WhereClauseBuilder();
			OrderBy = new List<OrderByClause>();
		}

		public Query AddOrderBy(Enum field)
		{
			return AddOrderBy(field.ToString());
		}

		public Query AddOrderBy(string fieldName)
		{
			return AddOrderBy(fieldName, Sorting.Ascending);
		}

		public Query AddOrderBy(Enum field, Sorting order)
		{
			return AddOrderBy(field.ToString(), order);
		}

		public Query AddOrderBy(string fieldName, Sorting order)
		{
			return AddOrderBy(new OrderByClause(fieldName, order));
		}

		public Query AddOrderBy(OrderByClause clause)
		{
			OrderBy.Add(clause);
			return this;
		}

		public Query LeftJoin(string collectionName, Query queryToJoin, WhereClauseBuilder joinClause, Type returnType)
		{
			return Join(JoinType.LeftJoin, collectionName, queryToJoin, joinClause, returnType);
		}

		public Query LeftJoin(string collectionName, string myFieldName, string joinFieldName, Type returnType)
		{
			return Join(JoinType.LeftJoin, collectionName, null, WhereClauseBuilder.CreateJoin(joinFieldName, Comparison.Equals, myFieldName), returnType);
		}

		public Query LeftJoin(string collectionName, string myFieldName, string joinFieldName)
		{
			return Join(JoinType.LeftJoin, collectionName, null, WhereClauseBuilder.CreateJoin(joinFieldName, Comparison.Equals, myFieldName), null);
		}

		public Query InnerJoin(string collectionName, Query queryToJoin, WhereClauseBuilder joinClause, Type returnType)
		{
			return Join(JoinType.InnerJoin, collectionName, queryToJoin, joinClause, returnType);
		}

		public Query InnerJoin(string collectionName, string myFieldName, string joinFieldName)
		{
			return Join(JoinType.InnerJoin, collectionName, null, WhereClauseBuilder.CreateJoin(joinFieldName, Comparison.Equals, myFieldName), null);
		}

		public Query InnerJoin(string collectionName, string myFieldName, string joinFieldName, Type returnType)
		{
			return Join(JoinType.InnerJoin, collectionName, null, WhereClauseBuilder.CreateJoin(joinFieldName, Comparison.Equals, myFieldName), returnType);
		}

		public Query Join(JoinType joinType, string collectionName, Query queryToJoin, WhereClauseBuilder joinClause, Type returnType)
		{
			if (queryToJoin != null && queryToJoin._joins.Count > 0)
				throw new InvalidOperationException("you can not join to a query that already contains a join to another query, you must chain all joins from one main query");

			Join join = new Join()
			{
				JoinType = joinType,
				CollectionName = collectionName,
				Query = queryToJoin,
				JoinClause = joinClause,
				ReturnType = returnType
			};

			_joins.Add(join);

			return this;
		}

		public Query Compile()
		{
			if (_compiled)
				return this;

			lock (_sync)
			{
				_command = new SqlCommand();
				StringValues = new Dictionary<string, string>();
				_valuesNeedingRetrieval = new ConcurrentDictionary<string, string>();

				//get the string values for shard lookup later
				List<WhereClause> whereClauses = new List<WhereClause>();

				foreach (List<WhereClause> clauseList in Where)
				{
					whereClauses.AddRange(clauseList);
				}

				foreach (WhereClause w in whereClauses)
				{
					if (w.ComparisonOperator == Comparison.Equals && w.FieldValue != null && !(w.FieldValue is BsonSqlJoinFieldWrapper) 
						&& !(w.FieldValue is SqlLiteral))
					{
						if (w.FieldValue is BsonSqlFieldWrapper)
						{
							BsonSqlFieldWrapper fieldWrapper = w.FieldValue as BsonSqlFieldWrapper;
							_valuesNeedingRetrieval[w.FieldName] = fieldWrapper.FieldName;
						}
						else
						{
							if(w.FieldName == "BsonDocumentID")
								StringValues["_id"] = w.FieldValue.ToString();
							else
								StringValues[w.FieldName] = w.FieldValue.ToString();
						}
					}
				}

				if (_joins.Count == 0)
				{
					StringBuilder whereSb = new StringBuilder();
					whereSb.Append("WHERE ");
					Where.CompileWhereClause(_command, whereSb);
					_whereClause = whereSb.ToString();

					//go through the parameters to see which ones we need
					_wrappedValuesForWhereClause = new ConcurrentDictionary<string, BsonSqlFieldWrapper>();
					foreach (SqlParameter parameter in _command.Parameters)
					{
						BsonSqlFieldWrapper wrapper = parameter.Value as BsonSqlFieldWrapper;
						if (wrapper != null)
							_wrappedValuesForWhereClause[parameter.ParameterName] = wrapper;
					}
				}
				else
					CompileJoinClauses(_command);

				if (OrderBy.Count > 0)
				{
					StringBuilder orderBy = new StringBuilder(250);
					orderBy.Append("ORDER BY ");
					bool first = true;
					foreach (OrderByClause ob in OrderBy)
					{
						if (!first)
							orderBy.Append(",");
						else
							first = false;

						if (ob.FieldName.IndexOf(".") == -1)
							orderBy.Append("[BI].");

						orderBy.Append(ob.FieldName);

						if (ob.SortOrder == Sorting.Descending)
							orderBy.Append(" DESC");
					}
					_orderByClause = orderBy.ToString();
				}
				else
					_orderByClause = string.Empty;

                _compiled = true;

				return this;
			}
		}

		private void CompileJoinClauses(SqlCommand command)
		{
			StringBuilder joinClauses = new StringBuilder(1000);
			StringBuilder whereClauses = new StringBuilder(1000);

			//1st - need to compile our own where clause
			whereClauses.Append("(");
			Where.CompileWhereClause(command, whereClauses);
			whereClauses.Append(")");

			int joinLevel = 1;
			foreach (Join join in _joins)
			{
				joinClauses.AppendLine();

				switch(join.JoinType)
				{
					case JoinType.InnerJoin:
                        joinClauses.Append("INNER JOIN dbo.[PREFIX]");
						break;
					case JoinType.LeftJoin:
                        joinClauses.Append("LEFT JOIN dbo.[PREFIX]");
						break;
				}

				joinClauses.Append(join.CollectionName);
				joinClauses.Append("_I [BI");
				joinClauses.Append(joinLevel.ToString());
				joinClauses.Append("] ON (");

				join.JoinClause.CompileWhereClause(true, command, joinClauses, joinLevel);

				joinClauses.AppendLine(")");

				switch (join.JoinType)
				{
					case JoinType.InnerJoin:
                        joinClauses.Append("INNER JOIN dbo.[PREFIX]");
						break;
					case JoinType.LeftJoin:
                        joinClauses.Append("LEFT JOIN dbo.[PREFIX]");
						break;
				}

				joinClauses.Append(join.CollectionName);
				joinClauses.Append(" [BD");
				joinClauses.Append(joinLevel.ToString());
				joinClauses.Append("] ON (BD");
				joinClauses.Append(joinLevel.ToString());
				joinClauses.Append(".BsonDocumentID = BI");
				joinClauses.Append(joinLevel.ToString());
				joinClauses.AppendLine(".BsonDocumentID)");

				if (join.Query != null)
				{
					whereClauses.Append(" AND (");
					Where.CompileWhereClause(true, command, whereClauses, joinLevel);
					whereClauses.Append(")");
				}

				joinLevel += 1;
			}

			joinClauses.AppendLine();
			joinClauses.Append("WHERE ");
			joinClauses.Append(whereClauses.ToString());

			_whereClause = joinClauses.ToString();

			//one master command with all the variables
			//which still allows you to compile and bind joined queries
			_wrappedValuesForWhereClause = new ConcurrentDictionary<string, BsonSqlFieldWrapper>();
			foreach (SqlParameter parameter in _command.Parameters)
			{
				BsonSqlFieldWrapper wrapper = parameter.Value as BsonSqlFieldWrapper;
				if (wrapper != null)
					_wrappedValuesForWhereClause[parameter.ParameterName] = wrapper;
			}
		}

		public Query Reset()
		{
			_wrappedValuesForWhereClause = null;
			_compiled = false;
			_command = null;
			_whereClause = null;
			_orderByClause = null;
			return this;
		}

		public Query Bind(object source)
		{
			//return a new Query object
			Compile();

			Query cloned = new Query() { OrderBy = this.OrderBy, Where = this.Where };
			cloned._compiled = true;
			cloned._whereClause = _whereClause;
			cloned._orderByClause = _orderByClause;
			cloned._joins = _joins;


			//copy the existing string values
			cloned.StringValues = new Dictionary<string, string>(StringValues);

			SqlCommand command = _command.Clone();

			BsonDocument document = BsonSqlSerializer.SerializeToDocument(source);
			Dictionary<string, object> retrievedValues = new Dictionary<string, object>();

			//bind the parameter values from this baby
			foreach (KeyValuePair<string, BsonSqlFieldWrapper> kvp in _wrappedValuesForWhereClause)
			{
				SqlParameter parameter = command.Parameters[kvp.Key];
				if (document == null)
					parameter.Value = DBNull.Value;
				else
				{
					BsonSqlFieldWrapper wrapper = kvp.Value;
					string[] fieldNames = wrapper.FieldName.Split('.');
					BsonElement currentElement = null;

					if (document.Contains(fieldNames[0]))
					{
						BsonValue finalValue = null;
						currentElement = document.GetElement(fieldNames[0]);
						for (int i = 1; i < fieldNames.Length; i++)
						{
							if (currentElement == null)
								break;

							if (currentElement.Value.IsBsonArray)
							{
								//get the first element
								BsonArray elementValues = currentElement.Value.AsBsonArray;
								if (elementValues.Count == 0)
								{
									currentElement = null;
									break;
								}

								currentElement = new BsonElement(fieldNames[i], elementValues[0]);
								//now see if this element has the propety requested
							}


							if (currentElement.Value.IsBsonDocument)
							{
								if (((BsonDocument)currentElement.Value).Contains(fieldNames[i]))
									currentElement = ((BsonDocument)currentElement.Value).GetElement(fieldNames[i]);
								else
								{
									currentElement = null;
									break;
								}
							}
							else
								currentElement = null; //wipe it out
						}

						if (currentElement != null)
							finalValue = currentElement.Value;

                        if (finalValue == null)
                            parameter.Value = DBNull.Value;
                        else
                        {
                            if (finalValue is BsonDateTime)
                                parameter.Value = finalValue.AsBsonDateTime.ToUniversalTime();
                            else
                                parameter.Value = finalValue.RawValue;
                        }
					}
					else
						parameter.Value = DBNull.Value;

					retrievedValues[wrapper.FieldName] = parameter.Value;
				}
			}

			//go through the values needing retrieval to add them to the list
			foreach (KeyValuePair<string, string> rvKvp in _valuesNeedingRetrieval)
			{
				object rv = null;
				if (retrievedValues.TryGetValue(rvKvp.Value, out rv))
				{
					if (rv != null && rv != DBNull.Value)
					{
						if (rvKvp.Key == "BsonDocumentID")
							cloned.StringValues["_id"] = rv.ToString();
						else
							cloned.StringValues[rvKvp.Key] = rv.ToString();
					}
				}
			}

			cloned._command = command;

			return cloned;
		}

		public SqlCommand Command 
		{
			get
			{
				//compile the query and return the command
				Compile();
				return _command;
			}
		}
	}
}