1 /* vim: set expandtab sw=4 ts=4 sts=4: */
3 * @fileoverview function used in QBE for DB
4 * @name Database Operations
8 * @requires js/functions.js
12 function getFormatsText () {
20 'LIKE': ' LIKE \'%s\'',
21 'LIKE \%...\%': ' LIKE \'%%%s%%\'',
22 'NOT LIKE': ' NOT LIKE \'%s\'',
23 'BETWEEN': ' BETWEEN \'%s\'',
24 'NOT BETWEEN': ' NOT BETWEEN \'%s\'',
25 'IS NULL': ' \'%s\' IS NULL',
26 'IS NOT NULL': ' \'%s\' IS NOT NULL',
27 'REGEXP': ' REGEXP \'%s\'',
28 'REGEXP ^...$': ' REGEXP \'^%s$\'',
29 'NOT REGEXP': ' NOT REGEXP \'%s\''
33 function generateCondition (criteriaDiv, table) {
34 query = '`' + escapeBacktick(table.val()) + '`.';
35 query += '`' + escapeBacktick(table.siblings('.columnNameSelect').first().val()) + '`';
36 if (criteriaDiv.find('.criteria_rhs').first().val() === 'text') {
37 formatsText = getFormatsText();
38 query += sprintf(formatsText[criteriaDiv.find('.criteria_op').first().val()], escapeSingleQuote(criteriaDiv.find('.rhs_text_val').first().val()));
40 query += ' ' + criteriaDiv.find('.criteria_op').first().val();
41 query += ' `' + escapeBacktick(criteriaDiv.find('.tableNameSelect').first().val()) + '`.';
42 query += '`' + escapeBacktick(criteriaDiv.find('.columnNameSelect').first().val()) + '`';
47 function generateWhereBlock () {
50 $('.tableNameSelect').each(function () {
51 var criteriaDiv = $(this).siblings('.slide-wrapper').first();
52 var useCriteria = $(this).siblings('.criteria_col').first();
53 if ($(this).val() !== '' && useCriteria.prop('checked')) {
55 criteriaDiv.find('input.logical_op').each(function () {
56 if ($(this).prop('checked')) {
57 query += ' ' + $(this).val() + ' ';
61 query += generateCondition(criteriaDiv, $(this));
68 function generateJoin (newTable, tableAliases, fk) {
70 query += ' \n\tLEFT JOIN ' + '`' + escapeBacktick(newTable) + '`';
71 if (tableAliases[fk.TABLE_NAME][0] !== '') {
72 query += ' AS `' + escapeBacktick(tableAliases[newTable][0]) + '`';
73 query += ' ON `' + escapeBacktick(tableAliases[fk.TABLE_NAME][0]) + '`';
75 query += ' ON `' + escapeBacktick(fk.TABLE_NAME) + '`';
77 query += '.`' + fk.COLUMN_NAME + '`';
78 if (tableAliases[fk.REFERENCED_TABLE_NAME][0] !== '') {
79 query += ' = `' + escapeBacktick(tableAliases[fk.REFERENCED_TABLE_NAME][0]) + '`';
81 query += ' = `' + escapeBacktick(fk.REFERENCED_TABLE_NAME) + '`';
83 query += '.`' + fk.REFERENCED_COLUMN_NAME + '`';
87 function existReference (table, fk, usedTables) {
88 var isReferredBy = fk.TABLE_NAME === table && usedTables.includes(fk.REFERENCED_TABLE_NAME);
89 var isReferencedBy = fk.REFERENCED_TABLE_NAME === table && usedTables.includes(fk.TABLE_NAME);
90 return isReferredBy || isReferencedBy;
93 function tryJoinTable (table, tableAliases, usedTables, foreignKeys) {
94 for (var i = 0; i < foreignKeys.length; i++) {
95 var fk = foreignKeys[i];
96 if (existReference(table, fk, usedTables)) {
97 return generateJoin(table, tableAliases, fk);
103 function appendTable (table, tableAliases, usedTables, foreignKeys) {
104 var query = tryJoinTable (table, tableAliases, usedTables, foreignKeys);
106 if (usedTables.length > 0) {
109 query += '`' + escapeBacktick(table) + '`';
110 if (tableAliases[table][0] !== '') {
111 query += ' AS `' + escapeBacktick(tableAliases[table][0]) + '`';
114 usedTables.push(table);
118 function generateFromBlock (tableAliases, foreignKeys) {
121 for (var table in tableAliases) {
122 if (tableAliases.hasOwnProperty(table)) {
123 query += appendTable(table, tableAliases, usedTables, foreignKeys);