1 /* vim: set expandtab sw=4 ts=4 sts=4: */
3 * @fileoverview function used in this file builds history tab and generates query.
10 var history_array = []; // Global array to store history objects
11 var select_field = []; // Global array to store informaation for columns which are used in select clause
15 * function for panel, hides and shows toggle_container <div>,which is for history elements uses {@link JQuery}.
17 * @param index has value 1 or 0,decides wheter to hide toggle_container on load.
23 $(".toggle_container").hide();
25 $("h2.tiger").click(function () {
26 $(this).toggleClass("active").next().slideToggle("slow");
31 * To display details of obects(where,rename,Having,aggregate,groupby,orderby,having)
33 * @param index index of history_array where change is to be made
37 function detail(index)
39 var type = history_array[index].get_type();
41 if (type == "Where") {
42 str = 'Where ' + history_array[index].get_column_name() + history_array[index].get_obj().getrelation_operator() + history_array[index].get_obj().getquery();
44 if (type == "Rename") {
45 str = 'Rename ' + history_array[index].get_column_name() + ' To ' + history_array[index].get_obj().getrename_to();
47 if (type == "Aggregate") {
48 str = 'Select ' + history_array[index].get_obj().get_operator() + '( ' + history_array[index].get_column_name() + ' )';
50 if (type == "GroupBy") {
51 str = 'GroupBy ' + history_array[index].get_column_name();
53 if (type == "OrderBy") {
54 str = 'OrderBy ' + history_array[index].get_column_name();
56 if (type == "Having") {
58 if (history_array[index].get_obj().get_operator() != 'None') {
59 str += history_array[index].get_obj().get_operator() + '( ' + history_array[index].get_column_name() + ' )';
60 str += history_array[index].get_obj().getrelation_operator() + history_array[index].get_obj().getquery();
62 str = 'Having ' + history_array[index].get_column_name() + history_array[index].get_obj().getrelation_operator() + history_array[index].get_obj().getquery();
69 * Sorts history_array[] first,using table name as the key and then generates the HTML code for history tab,
70 * clubbing all objects of same tables together
71 * This function is called whenever changes are made in history_array[]
74 * @param {int} init starting index of unsorted array
75 * @param {int} finit last index of unsorted array
79 function display(init, finit)
81 var str, i, j, k, sto, temp;
82 // this part sorts the history array based on table name,this is needed for clubbing all object of same name together.
83 for (i = init; i < finit; i++) {
84 sto = history_array[i];
85 temp = history_array[i].get_tab();//+ '.' + history_array[i].get_obj_no(); for Self JOINS
86 for (j = 0; j < i; j++) {
87 if (temp > (history_array[j].get_tab())) {//+ '.' + history_array[j].get_obj_no())) { //for Self JOINS
88 for (k = i; k > j; k--) {
89 history_array[k] = history_array[k - 1];
91 history_array[j] = sto;
96 // this part generates HTML code for history tab.adds delete,edit,and/or and detail features with objects.
97 str = ''; // string to store Html code for history tab
98 for (i = 0; i < history_array.length; i++) {
99 temp = history_array[i].get_tab(); //+ '.' + history_array[i].get_obj_no(); for Self JOIN
100 str += '<h2 class="tiger"><a href="#">' + temp + '</a></h2>';
101 str += '<div class="toggle_container">\n';
102 while ((history_array[i].get_tab()) == temp) { //+ '.' + history_array[i].get_obj_no()) == temp) {
103 str += '<div class="block"> <table width ="250">';
104 str += '<thead><tr><td>';
105 if (history_array[i].get_and_or()) {
106 str += '<img src="' + pmaThemeImage + 'pmd/or_icon.png" onclick="and_or(' + i + ')" title="OR"/></td>';
108 str += '<img src="' + pmaThemeImage + 'pmd/and_icon.png" onclick="and_or(' + i + ')" title="AND"/></td>';
110 str += '<td style="padding-left: 5px;" class="right">' + PMA_getImage('b_sbrowse.png', 'column name') + '</td><td width="175" style="padding-left: 5px">' + history_array[i].get_column_name();
111 if (history_array[i].get_type() == "GroupBy" || history_array[i].get_type() == "OrderBy") {
112 str += '</td><td class="center">' + PMA_getImage('s_info.png', detail(i)) + '<td title="' + detail(i) + '">' + history_array[i].get_type() + '</td></td><td onmouseover="this.className=\'history_table\';" onmouseout="this.className=\'history_table2\'" onclick=history_delete(' + i + ')>' + PMA_getImage('b_drop.png', 'Delete') + '</td></tr></thead>';
114 str += '</td><td class="center">' + PMA_getImage('s_info.png', detail(i)) + '</td><td title="' + detail(i) + '">' + history_array[i].get_type() + '</td><td <td onmouseover="this.className=\'history_table\';" onmouseout="this.className=\'history_table2\'" onclick=history_edit(' + i + ')>' + PMA_getImage('b_edit.png', PMA_messages.strEdit) + '</td><td onmouseover="this.className=\'history_table\';" onmouseout="this.className=\'history_table2\'" onclick=history_delete(' + i + ')><img src="themes/original/img/b_drop.png" title="Delete"></td></tr></thead>';
117 if (i >= history_array.length) {
120 str += '</table></div><br/>';
123 str += '</div><br/>';
129 * To change And/Or relation in history tab
132 * @param {int} index of history_array where change is to be made
136 function and_or(index)
138 if (history_array[index].get_and_or()) {
139 history_array[index].set_and_or(0);
141 history_array[index].set_and_or(1);
143 var existingDiv = document.getElementById('ab');
144 existingDiv.innerHTML = display(0, 0);
149 * Deletes entry in history_array
151 * @param index index of history_array[] which is to be deleted
155 function history_delete(index)
157 for (var k = 0; k < from_array.length; k++) {
158 if (from_array[k] == history_array[index].get_tab()) {
159 from_array.splice(k, 1);
163 history_array.splice(index, 1);
164 var existingDiv = document.getElementById('ab');
165 existingDiv.innerHTML = display(0, 0);
170 * To show where,rename,aggregate,having forms to edit a object
172 * @param{int} index index of history_array where change is to be made
176 function history_edit(index)
179 var type = history_array[index].get_type();
180 if (type == "Where") {
181 document.getElementById('eQuery').value = history_array[index].get_obj().getquery();
182 document.getElementById('erel_opt').value = history_array[index].get_obj().getrelation_operator();
183 document.getElementById('query_where').style.left = '530px';
184 document.getElementById('query_where').style.top = '130px';
185 document.getElementById('query_where').style.position = 'absolute';
186 document.getElementById('query_where').style.zIndex = '9';
187 document.getElementById('query_where').style.visibility = 'visible';
189 if (type == "Having") {
190 document.getElementById('hQuery').value = history_array[index].get_obj().getquery();
191 document.getElementById('hrel_opt').value = history_array[index].get_obj().getrelation_operator();
192 document.getElementById('hoperator').value = history_array[index].get_obj().get_operator();
193 document.getElementById('query_having').style.left = '530px';
194 document.getElementById('query_having').style.top = '130px';
195 document.getElementById('query_having').style.position = 'absolute';
196 document.getElementById('query_having').style.zIndex = '9';
197 document.getElementById('query_having').style.visibility = 'visible';
199 if (type == "Rename") {
200 document.getElementById('query_rename_to').style.left = '530px';
201 document.getElementById('query_rename_to').style.top = '130px';
202 document.getElementById('query_rename_to').style.position = 'absolute';
203 document.getElementById('query_rename_to').style.zIndex = '9';
204 document.getElementById('query_rename_to').style.visibility = 'visible';
206 if (type == "Aggregate") {
207 document.getElementById('query_Aggregate').style.left = '530px';
208 document.getElementById('query_Aggregate').style.top = '130px';
209 document.getElementById('query_Aggregate').style.position = 'absolute';
210 document.getElementById('query_Aggregate').style.zIndex = '9';
211 document.getElementById('query_Aggregate').style.visibility = 'visible';
216 * Make changes in history_array when Edit button is clicked
217 * checks for the type of object and then sets the new value
219 * @param index index of history_array where change is to be made
224 if (type == "Rename") {
225 if (document.getElementById('e_rename').value !== "") {
226 history_array[g_index].get_obj().setrename_to(document.getElementById('e_rename').value);
227 document.getElementById('e_rename').value = "";
229 document.getElementById('query_rename_to').style.visibility = 'hidden';
231 if (type == "Aggregate") {
232 if (document.getElementById('e_operator').value != '---') {
233 history_array[g_index].get_obj().set_operator(document.getElementById('e_operator').value);
234 document.getElementById('e_operator').value = '---';
236 document.getElementById('query_Aggregate').style.visibility = 'hidden';
238 if (type == "Where") {
239 if (document.getElementById('erel_opt').value != '--' && document.getElementById('eQuery').value !== "") {
240 history_array[g_index].get_obj().setquery(document.getElementById('eQuery').value);
241 history_array[g_index].get_obj().setrelation_operator(document.getElementById('erel_opt').value);
243 document.getElementById('query_where').style.visibility = 'hidden';
245 if (type == "Having") {
246 if (document.getElementById('hrel_opt').value != '--' && document.getElementById('hQuery').value !== "") {
247 history_array[g_index].get_obj().setquery(document.getElementById('hQuery').value);
248 history_array[g_index].get_obj().setrelation_operator(document.getElementById('hrel_opt').value);
249 history_array[g_index].get_obj().set_operator(document.getElementById('hoperator').value);
251 document.getElementById('query_having').style.visibility = 'hidden';
253 var existingDiv = document.getElementById('ab');
254 existingDiv.innerHTML = display(0, 0);
259 * history object closure
261 * @param ncolumn_name name of the column on which conditions are put
262 * @param nobj object details(where,rename,orderby,groupby,aggregate)
263 * @param ntab table name of the column on which conditions are applied
264 * @param nobj_no object no used for inner join
265 * @param ntype type of object
269 function history(ncolumn_name, nobj, ntab, nobj_no, ntype)
277 this.set_column_name = function (ncolumn_name) {
278 column_name = ncolumn_name;
280 this.get_column_name = function () {
283 this.set_and_or = function (nand_or) {
286 this.get_and_or = function () {
289 this.get_relation = function () {
292 this.set_obj = function (nobj) {
295 this.get_obj = function () {
298 this.set_tab = function (ntab) {
301 this.get_tab = function () {
304 this.set_obj_no = function (nobj_no) {
307 this.get_obj_no = function () {
310 this.set_type = function (ntype) {
313 this.get_type = function () {
316 this.set_obj_no(nobj_no);
320 this.set_column_name(ncolumn_name);
321 this.set_type(ntype);
325 * where object closure, makes an object with all information of where
327 * @param nrelation_operator type of relation operator to be applied
328 * @param nquery stores value of value/sub-query
333 var where = function (nrelation_operator, nquery) {
334 var relation_operator;
336 this.setrelation_operator = function (nrelation_operator) {
337 relation_operator = nrelation_operator;
339 this.setquery = function (nquery) {
342 this.getquery = function () {
345 this.getrelation_operator = function () {
346 return relation_operator;
348 this.setquery(nquery);
349 this.setrelation_operator(nrelation_operator);
354 * Having object closure, makes an object with all information of where
356 * @param nrelation_operator type of relation operator to be applied
357 * @param nquery stores value of value/sub-query
361 var having = function (nrelation_operator, nquery, noperator) {
362 var relation_operator;
365 this.set_operator = function (noperator) {
366 operator = noperator;
368 this.setrelation_operator = function (nrelation_operator) {
369 relation_operator = nrelation_operator;
371 this.setquery = function (nquery) {
374 this.getquery = function () {
377 this.getrelation_operator = function () {
378 return relation_operator;
380 this.get_operator = function () {
383 this.setquery(nquery);
384 this.setrelation_operator(nrelation_operator);
385 this.set_operator(noperator);
389 * rename object closure,makes an object with all information of rename
391 * @param nrename_to new name information
395 var rename = function (nrename_to) {
397 this.setrename_to = function (nrename_to) {
398 rename_to = nrename_to;
400 this.getrename_to = function () {
403 this.setrename_to(nrename_to);
407 * aggregate object closure
409 * @param noperator aggregte operator
413 var aggregate = function (noperator) {
415 this.set_operator = function (noperator) {
416 operator = noperator;
418 this.get_operator = function () {
421 this.set_operator(noperator);
425 * This function returns unique element from an array
427 * @param arraName array from which duplicate elem are to be removed.
428 * @return unique array
431 function unique(arrayName)
435 for (var i = 0; i < arrayName.length; i++) {
436 for (var j = 0; j < newArray.length; j++) {
437 if (newArray[j] == arrayName[i]) {
441 newArray[newArray.length] = arrayName[i];
447 * This function takes in array and a value as input and returns 1 if values is present in array
450 * @param arrayName array
451 * @param value value which is to be searched in the array
454 function found(arrayName, value)
456 for (var i = 0; i < arrayName.length; i++) {
457 if (arrayName[i] == value) {
465 * This function concatenates two array
467 * @params add array elements of which are pushed in
468 * @params arr array in which elemnets are added
470 function add_array(add, arr)
472 for (var i = 0; i < add.length; i++) {
478 /* This fucntion removes all elements present in one array from the other.
480 * @params rem array from which each element is removed from other array.
481 * @params arr array from which elements are removed.
484 function remove_array(rem, arr)
486 for (var i = 0; i < rem.length; i++) {
487 for (var j = 0; j < arr.length; j++) {
488 if (rem[i] == arr[j]) {
497 * This function builds the groupby clause from history object
501 function query_groupby()
505 for (i = 0; i < history_array.length;i++) {
506 if (history_array[i].get_type() == "GroupBy") {
507 str += history_array[i].get_column_name() + ", ";
510 str = str.substr(0, str.length - 1);
515 * This function builds the Having clause from the history object.
519 function query_having()
523 for (i = 0; i < history_array.length;i++) {
524 if (history_array[i].get_type() == "Having") {
525 if (history_array[i].get_obj().get_operator() != 'None') {
526 and += history_array[i].get_obj().get_operator() + "(" + history_array[i].get_column_name() + " ) " + history_array[i].get_obj().getrelation_operator();
527 and += " " + history_array[i].get_obj().getquery() + ", ";
529 and += history_array[i].get_column_name() + " " + history_array[i].get_obj().getrelation_operator() + " " + history_array[i].get_obj().getquery() + ", ";
536 and = and.substr(0, and.length - 2) + ")";
543 * This function builds the orderby clause from the history object.
547 function query_orderby()
551 for (i = 0; i < history_array.length;i++) {
552 if (history_array[i].get_type() == "OrderBy") { str += history_array[i].get_column_name() + " , "; }
554 str = str.substr(0, str.length - 1);
560 * This function builds the Where clause from the history object.
564 function query_where()
569 for (i = 0; i < history_array.length;i++) {
570 if (history_array[i].get_type() == "Where") {
571 if (history_array[i].get_and_or() === 0) {
572 and += "( " + history_array[i].get_column_name() + " " + history_array[i].get_obj().getrelation_operator() + " " + history_array[i].get_obj().getquery() + ")";
575 or += "( " + history_array[i].get_column_name() + " " + history_array[i].get_obj().getrelation_operator() + " " + history_array[i].get_obj().getquery() + ")";
581 or = or.substring(0, (or.length - 4)) + ")";
586 and = and.substring(0, (and.length - 5)) + ")";
591 and = and + " OR " + or + " )";
596 function check_aggregate(id_this)
599 for (i = 0; i < history_array.length; i++) {
600 var temp = '`' + history_array[i].get_tab() + '`.`' + history_array[i].get_column_name() + '`';
601 if (temp == id_this && history_array[i].get_type() == "Aggregate") {
602 return history_array[i].get_obj().get_operator() + '(' + id_this + ')';
608 function check_rename(id_this)
611 for (i = 0; i < history_array.length; i++) {
612 var temp = '`' + history_array[i].get_tab() + '`.`' + history_array[i].get_column_name() + '`';
613 if (temp == id_this && history_array[i].get_type() == "Rename") {
614 return " AS `" + history_array[i].get_obj().getrename_to() + "`";
620 function gradient(id, level)
622 var box = document.getElementById(id);
623 box.style.opacity = level;
624 box.style.MozOpacity = level;
625 box.style.KhtmlOpacity = level;
626 box.style.filter = "alpha(opacity=" + level * 100 + ")";
627 box.style.display = "block";
636 setTimeout("gradient('" + id + "'," + level + ")", (level * 1000) + 10);
642 * This function builds from clause of query
643 * makes automatic joins.
647 function query_from()
659 t_array = from_array;
666 for (i = 0; i < history_array.length; i++) {
667 from_array.push(history_array[i].get_tab());
669 from_array = unique(from_array);
670 tab_left = from_array;
671 temp = tab_left.shift();
674 // if master table (key2) matches with tab used get all keys and check if tab_left matches
675 // after this check if master table (key2) matches with tab left then check if any foreign matches with master .
676 for (i = 0; i < 2; i++) {
678 for (key in contr[K]) {// contr name
679 for (key2 in contr[K][key]) {// table name
680 parts = key2.split(".");
681 if (found(tab_used, parts[1]) > 0) {
682 for (key3 in contr[K][key][key2]) {
683 parts1 = contr[K][key][key2][key3][0].split(".");
684 if (found(tab_left, parts1[1]) > 0) {
685 query += "\n" + 'LEFT JOIN ';
686 query += '`' + parts1[0] + '`.`' + parts1[1] + '` ON ';
687 query += '`' + parts[1] + '`.`' + key3 + '` = ';
688 query += '`' + parts1[1] + '`.`' + contr[K][key][key2][key3][1] + '` ';
689 t_tab_left.push(parts1[1]);
697 t_tab_left = unique(t_tab_left);
698 tab_used = add_array(t_tab_left, tab_used);
699 tab_left = remove_array(t_tab_left, tab_left);
702 for (key in contr[K]) {
703 for (key2 in contr[K][key]) {// table name
704 parts = key2.split(".");
705 if (found(tab_left, parts[1]) > 0) {
706 for (key3 in contr[K][key][key2]) {
707 parts1 = contr[K][key][key2][key3][0].split(".");
708 if (found(tab_used, parts1[1]) > 0) {
709 query += "\n" + 'LEFT JOIN ';
710 query += '`' + parts[0] + '`.`' + parts[1] + '` ON ';
711 query += '`' + parts1[1] + '`.`' + contr[K][key][key2][key3][1] + '` = ';
712 query += '`' + parts[1] + '`.`' + key3 + '` ';
713 t_tab_left.push(parts[1]);
720 t_tab_left = unique(t_tab_left);
721 tab_used = add_array(t_tab_left, tab_used);
722 tab_left = remove_array(t_tab_left, tab_left);
725 for (k in tab_left) {
726 quer += " , `" + tab_left[k] + "`";
728 query = quer + query;
729 from_array = t_array;
734 * This function is the main function for query building.
735 * uses history object details for this.
737 * @ uses query_where()
738 * @ uses query_groupby()
739 * @ uses query_having()
740 * @ uses query_orderby()
742 * @param formtitle title for the form
746 function build_query(formtitle, fadin)
748 var q_select = "SELECT ";
750 for (var i = 0;i < select_field.length; i++) {
751 temp = check_aggregate(select_field[i]);
754 temp = check_rename(select_field[i]);
755 q_select += temp + ",";
757 temp = check_rename(select_field[i]);
758 q_select += select_field[i] + temp + ",";
761 q_select = q_select.substring(0, q_select.length - 1);
762 q_select += " FROM " + query_from();
763 if (query_where() !== "") {
764 q_select += "\n WHERE";
765 q_select += query_where();
767 if (query_groupby() !== "") { q_select += "\nGROUP BY " + query_groupby(); }
768 if (query_having() !== "") { q_select += "\nHAVING " + query_having(); }
769 if (query_orderby() !== "") { q_select += "\nORDER BY " + query_orderby(); }
770 var box = document.getElementById('box');
771 document.getElementById('filter').style.display = 'block';
772 var btitle = document.getElementById('boxtitle');
773 btitle.innerHTML = 'SELECT';//formtitle;
778 box.style.display = 'block';
780 document.getElementById('textSqlquery').innerHTML = q_select;
785 document.getElementById('box').style.display = 'none';
786 document.getElementById('filter').style.display = 'none';