API tools faq
paste
Guest User

Untitled

a guest
Mar 17th, 2018
91
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 10.05 KB | None | 0 0
raw download clone embed print report
  1. var sqlFromMongo;
  2.  
  3. sqlFromMongo = function(mongoQueryObject, collectionName, fields) {
  4. var JOIN_LOOKUP, containsDollars, field, fieldStringParts, fieldsString, key, keys, parseSingleKeyValuePair, parts, prefix, sql, subObject, type, value;
  5. if ((fields != null) && (collectionName == null)) {
  6. throw new Error("Must provide a collectionName if fields is provided.");
  7. }
  8. JOIN_LOOKUP = {
  9. $and: " AND ",
  10. $or: " OR ",
  11. $nor: " OR "
  12. };
  13. type = (function() {
  14. var classToType, i, len, name, ref;
  15. classToType = {};
  16. ref = "Boolean Number String Function Array Date RegExp Undefined Null".split(" ");
  17. for (i = 0, len = ref.length; i < len; i++) {
  18. name = ref[i];
  19. classToType["[object " + name + "]"] = name.toLowerCase();
  20. }
  21. return function(obj) {
  22. var strType;
  23. strType = Object.prototype.toString.call(obj);
  24. return classToType[strType] || "object";
  25. };
  26. })();
  27. containsDollars = function(obj) {
  28. return Object.keys(obj).filter(function(key) {
  29. return key[0] === '$' || typeof obj[key] === 'object' && containsDollars(obj[key]);
  30. }).length > 0;
  31. };
  32. if (type(mongoQueryObject) === 'string' && mongoQueryObject.toUpperCase().indexOf('SELECT') === 0) {
  33. return mongoQueryObject;
  34. }
  35. parseSingleKeyValuePair = function(key, value, collectionName) {
  36. var i, joinOperator, key2, keys, len, maxDistance, minDistance, o, parts, s, value2, valueKey, valueValue;
  37. switch (key) {
  38. case "$not":
  39. s = sqlFromMongo(value, collectionName);
  40. if (s.indexOf("(") === 0) {
  41. return "NOT " + s;
  42. } else {
  43. return "NOT (" + s + ")";
  44. }
  45. break;
  46. case "$and":
  47. case "$or":
  48. case "$nor":
  49. if (type(value) !== "array") {
  50. throw new Error("Use of $and, $or, or $nor operator requires an array as its parameter.");
  51. }
  52. parts = [];
  53. for (i = 0, len = value.length; i < len; i++) {
  54. o = value[i];
  55. parts.push(sqlFromMongo(o, collectionName));
  56. }
  57. joinOperator = JOIN_LOOKUP[key];
  58. s = "(" + parts.join(joinOperator) + ")";
  59. if (key === "$nor") {
  60. return "NOT " + s;
  61. } else {
  62. return s;
  63. }
  64. break;
  65. default:
  66. if (type(value) === "object") {
  67. parts = [];
  68. s = (prefix + key) + " ";
  69. for (valueKey in value) {
  70. valueValue = value[valueKey];
  71. switch (valueKey) {
  72. case "$lt":
  73. parts.push(s + ("< " + (JSON.stringify(valueValue))));
  74. break;
  75. case "$gt":
  76. parts.push(s + ("> " + (JSON.stringify(valueValue))));
  77. break;
  78. case "$lte":
  79. parts.push(s + ("<= " + (JSON.stringify(valueValue))));
  80. break;
  81. case "$gte":
  82. parts.push(s + (">= " + (JSON.stringify(valueValue))));
  83. break;
  84. case "$ne":
  85. parts.push(s + ("<> " + (JSON.stringify(valueValue))));
  86. break;
  87. case "$eq":
  88. parts.push(s + ("= " + (JSON.stringify(valueValue))));
  89. break;
  90. case "$elemMatch":
  91. if (containsDollars(valueValue)) {
  92. throw new Error("DocumentDB can only match explicit values of array objects");
  93. }
  94. return "ARRAY_CONTAINS(" + (prefix + key) + ", " + (JSON.stringify(valueValue)) + ", true)";
  95. case "$in":
  96. if (type(valueValue) === 'array') {
  97. if (valueValue.length > 100) {
  98. throw new Error("In DocumentDB the maximum number of values per IN expression is 100");
  99. }
  100. s = JSON.stringify(valueValue);
  101. s = s.substr(1, s.length - 2);
  102. return (prefix + key) + " IN (" + s + ")";
  103. } else {
  104. return "ARRAY_CONTAINS(" + (prefix + valueValue) + ", " + key + ")";
  105. }
  106. break;
  107. case "$nin":
  108. if (type(valueValue) === 'array') {
  109. if (valueValue.length > 100) {
  110. throw new Error("In DocumentDB the maximum number of values per IN expression is 100");
  111. }
  112. s = JSON.stringify(valueValue);
  113. s = s.substr(1, s.length - 2);
  114. return "NOT " + (prefix + key) + " IN (" + s + ")";
  115. } else {
  116. return "NOT ARRAY_CONTAINS(" + (prefix + valueValue) + ", " + key + ")";
  117. }
  118. break;
  119. case "$size":
  120. return "ARRAY_LENGTH(" + (prefix + key) + ") = " + valueValue;
  121. case "$exists":
  122. if (valueValue) {
  123. return "IS_DEFINED(" + (prefix + key) + ")";
  124. } else {
  125. return "NOT IS_DEFINED(" + (prefix + key) + ")";
  126. }
  127. break;
  128. case "$isArray":
  129. if (valueValue) {
  130. return "IS_ARRAY(" + (prefix + key) + ")";
  131. } else {
  132. return "NOT IS_ARRAY(" + (prefix + key) + ")";
  133. }
  134. break;
  135. case "$isBool":
  136. if (valueValue) {
  137. return "IS_BOOL(" + (prefix + key) + ")";
  138. } else {
  139. return "NOT IS_BOOL(" + (prefix + key) + ")";
  140. }
  141. break;
  142. case "$isNull":
  143. if (valueValue) {
  144. return "IS_NULL(" + (prefix + key) + ")";
  145. } else {
  146. return "NOT IS_NULL(" + (prefix + key) + ")";
  147. }
  148. break;
  149. case "$isNumber":
  150. if (valueValue) {
  151. return "IS_NUMBER(" + (prefix + key) + ")";
  152. } else {
  153. return "NOT IS_NUMBER(" + (prefix + key) + ")";
  154. }
  155. break;
  156. case "$isObject":
  157. if (valueValue) {
  158. return "IS_OBJECT(" + (prefix + key) + ")";
  159. } else {
  160. return "NOT IS_OBJECT(" + (prefix + key) + ")";
  161. }
  162. break;
  163. case "$isString":
  164. if (valueValue) {
  165. return "IS_STRING(" + (prefix + key) + ")";
  166. } else {
  167. return "NOT IS_STRING(" + (prefix + key) + ")";
  168. }
  169. break;
  170. case "$isPrimitive":
  171. if (valueValue) {
  172. return "IS_PRIMITIVE(" + (prefix + key) + ")";
  173. } else {
  174. return "NOT IS_PRIMITIVE(" + (prefix + key) + ")";
  175. }
  176. break;
  177. case "$startsWith":
  178. return "STARTSWITH(" + (prefix + key) + ", " + (JSON.stringify(valueValue)) + ")";
  179. case "$endsWith":
  180. return "ENDSWITH(" + (prefix + key) + ", " + (JSON.stringify(valueValue)) + ")";
  181. case "$contains":
  182. return "CONTAINS(" + (prefix + key) + ", " + (JSON.stringify(valueValue)) + ")";
  183. case "$geoWithin":
  184. return "ST_WITHIN(" + (prefix + key) + ", " + (JSON.stringify(valueValue)) + ")";
  185. case "$near":
  186. maxDistance = valueValue.$maxDistance;
  187. minDistance = valueValue.$minDistance;
  188. if (maxDistance != null) {
  189. if (minDistance != null) {
  190. return "(ST_DISTANCE(" + (prefix + key) + ", " + (JSON.stringify(valueValue.$geometry)) + ") <= " + maxDistance + " AND ST_DISTANCE(" + (prefix + key) + ", " + (JSON.stringify(valueValue.$geometry)) + ") >= " + minDistance + ")";
  191. } else {
  192. return "ST_DISTANCE(" + (prefix + key) + ", " + (JSON.stringify(valueValue.$geometry)) + ") <= " + maxDistance;
  193. }
  194. }
  195. if (minDistance != null) {
  196. return "ST_DISTANCE(" + (prefix + key) + ", " + (JSON.stringify(valueValue.$geometry)) + ") >= " + minDistance;
  197. } else {
  198. throw new Error("No minDistance nor maxDistance found in {" + (prefix + key) + ": " + (JSON.stringify(value)) + "}");
  199. }
  200. break;
  201. default:
  202. throw new Error("sql-from-mongo does not recognize {" + (prefix + key) + ": " + (JSON.stringify(value)) + "}");
  203. }
  204. }
  205. keys = [];
  206. for (key2 in value) {
  207. value2 = value[key2];
  208. keys.push(key2);
  209. }
  210. if (keys.length === 1) {
  211. return parts[0];
  212. } else {
  213. return "(" + parts.join(" AND ") + ")";
  214. }
  215. } else {
  216. return (prefix + key) + " = " + (JSON.stringify(value));
  217. }
  218. }
  219. };
  220. if ((collectionName != null) && collectionName.length > 0) {
  221. prefix = collectionName + ".";
  222. } else {
  223. prefix = "";
  224. }
  225. keys = [];
  226. for (key in mongoQueryObject) {
  227. value = mongoQueryObject[key];
  228. keys.push(key);
  229. }
  230. if (keys.length === 1) {
  231. parts = [parseSingleKeyValuePair(keys[0], mongoQueryObject[keys[0]], collectionName)];
  232. } else {
  233. parts = [];
  234. for (key in mongoQueryObject) {
  235. value = mongoQueryObject[key];
  236. subObject = {};
  237. subObject[key] = value;
  238. parts.push(sqlFromMongo(subObject, collectionName));
  239. }
  240. }
  241. if (parts.length === 1) {
  242. sql = parts[0];
  243. } else {
  244. sql = "(" + parts.join(" AND ") + ")";
  245. }
  246. if (fields != null) {
  247. if (fields === '*' || (fields[0] === '*') || fields === true) {
  248. fieldsString = '*';
  249. } else {
  250. fieldStringParts = (function() {
  251. var i, len, results;
  252. results = [];
  253. for (i = 0, len = fields.length; i < len; i++) {
  254. field = fields[i];
  255. results.push(prefix + field);
  256. }
  257. return results;
  258. })();
  259. fieldsString = fieldStringParts.join(", ");
  260. }
  261. sql = ("SELECT " + fieldsString + " FROM " + collectionName + " WHERE ") + sql;
  262. }
  263. return sql;
  264. };
  265.  
  266. exports.sqlFromMongo = sqlFromMongo;
  267.  
  268. // ---
  269. // generated by coffee-script 1.9.2
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!