1 /* vim: set expandtab sw=4 ts=4 sts=4: */
3 * @fileoverview functions used wherever an sql query form is used
6 * @requires js/functions.js
11 * decode a string URL_encoded
14 * @return string the URL-decoded string
16 function PMA_urldecode(str) {
17 return decodeURIComponent(str.replace(/\+/g, '%20'));
21 * Get the field name for the current field. Required to construct the query
24 * @param $this_field jQuery object that points to the current field's tr
25 * @param disp_mode string
27 function getFieldName($this_field, disp_mode) {
29 if(disp_mode == 'vertical') {
30 var field_name = $this_field.siblings('th').find('a').text();
31 // happens when just one row (headings contain no a)
32 if ("" == field_name) {
33 field_name = $this_field.siblings('th').text();
37 var this_field_index = $this_field.index();
38 // ltr or rtl direction does not impact how the DOM was generated
40 // 5 columns to account for the checkbox, edit, appended inline edit, copy and delete anchors but index is zero-based so substract 4
41 var field_name = $('#table_results').find('thead').find('th:nth('+ (this_field_index-4 )+') a').text();
42 // happens when just one row (headings contain no a)
43 if ("" == field_name) {
44 field_name = $('#table_results').find('thead').find('th:nth('+ (this_field_index-4 )+')').text();
48 field_name = $.trim(field_name);
54 * The function that iterates over each row in the table_results and appends a
55 * new inline edit anchor to each table row.
58 function appendInlineAnchor() {
59 var disp_mode = $("#top_direction_dropdown").val();
61 if (disp_mode == 'vertical') {
62 // there can be one or two tr containing this class, depending
63 // on the ModifyDeleteAtLeft and ModifyDeleteAtRight cfg parameters
64 $('#table_results tr')
65 .find('.edit_row_anchor')
66 .removeClass('.edit_row_anchor')
67 .parent().each(function() {
68 var $this_tr = $(this);
69 var $cloned_tr = $this_tr.clone();
71 var $img_object = $cloned_tr.find('img:first').attr('title', PMA_messages['strInlineEdit']);
72 if ($img_object.length != 0) {
73 var img_src = $img_object.attr('src').replace(/b_edit/,'b_inline_edit');
74 $img_object.attr('src', img_src);
78 .addClass('inline_edit_anchor')
79 .find('a').attr('href', '#')
81 .text(' ' + PMA_messages['strInlineEdit'])
82 .prepend($img_object);
84 $cloned_tr.insertAfter($this_tr);
87 $('#rowsDeleteForm').find('tbody').find('th').each(function() {
88 var $this_th = $(this);
89 if ($this_th.attr('rowspan') == 4) {
90 $this_th.attr('rowspan', '5');
95 $('.edit_row_anchor').each(function() {
97 var $this_td = $(this);
98 $this_td.removeClass('edit_row_anchor');
100 var $cloned_anchor = $this_td.clone();
102 var $img_object = $cloned_anchor.find('img').attr('title', PMA_messages['strInlineEdit']);
103 if ($img_object.length != 0) {
104 var img_src = $img_object.attr('src').replace(/b_edit/,'b_inline_edit');
105 $img_object.attr('src', img_src);
108 $cloned_anchor.addClass('inline_edit_anchor')
109 .find('a').attr('href', '#')
111 .text(' ' + PMA_messages['strInlineEdit'])
112 .prepend($img_object);
114 $this_td.after($cloned_anchor);
117 $('#rowsDeleteForm').find('thead, tbody').find('th').each(function() {
118 var $this_th = $(this);
119 if ($this_th.attr('colspan') == 4) {
120 $this_th.attr('colspan', '5');
131 * @description <p>Ajax scripts for sql and browse pages</p>
133 * Actions ajaxified here:
135 * <li>Retrieve results of an SQL query</li>
136 * <li>Paginate the results table</li>
137 * <li>Sort the results table</li>
138 * <li>Change table according to display options</li>
139 * <li>Inline editing of data</li>
142 * @name document.ready
145 $(document).ready(function() {
148 * Set a parameter for all Ajax queries made on this page. Don't let the
149 * web server serve cached pages
156 * current value of the direction in which the table is displayed
161 var disp_mode = $("#top_direction_dropdown").val();
164 * Update value of {@link jQuery.disp_mode} everytime the direction dropdown changes value
166 * @name direction_dropdown_change
168 $("#top_direction_dropdown, #bottom_direction_dropdown").live('change', function(event) {
169 disp_mode = $(this).val();
173 * Attach the {@link appendInlineAnchor} function to a custom event, which
174 * will be triggered manually everytime the table of results is reloaded
177 $("#sqlqueryresults").live('appendAnchor',function() {
178 appendInlineAnchor();
182 * Trigger the appendAnchor event to prepare the first table for inline edit
183 * (see $GLOBALS['cfg']['AjaxEnable'])
185 * @name sqlqueryresults_trigger
187 $("#sqlqueryresults.ajax").trigger('appendAnchor');
190 * Append the "Show/Hide query box" message to the query input form
193 * @name appendToggleSpan
195 // do not add this link more than once
196 if (! $('#sqlqueryform').find('a').is('#togglequerybox')) {
197 $('<a id="togglequerybox"></a>')
198 .html(PMA_messages['strHideQueryBox'])
199 .appendTo("#sqlqueryform")
200 // initially hidden because at this point, nothing else
201 // appears under the link
204 // Attach the toggling of the query box visibility to a click
205 $("#togglequerybox").bind('click', function() {
207 $link.siblings().slideToggle("fast");
208 if ($link.text() == PMA_messages['strHideQueryBox']) {
209 $link.text(PMA_messages['strShowQueryBox']);
210 // cheap trick to add a spacer between the menu tabs
211 // and "Show query box"; feel free to improve!
212 $('#togglequerybox_spacer').remove();
213 $link.before('<br id="togglequerybox_spacer" />');
215 $link.text(PMA_messages['strHideQueryBox']);
217 // avoid default click action
223 * Ajax Event handler for 'SQL Query Submit'
225 * @see PMA_ajaxShowMessage()
226 * @see $cfg['AjaxEnable']
228 * @name sqlqueryform_submit
230 $("#sqlqueryform.ajax").live('submit', function(event) {
231 event.preventDefault();
232 // remove any div containing a previous error message
233 $('.error').remove();
236 PMA_ajaxShowMessage();
238 if (! $form.find('input:hidden').is('#ajax_request_hidden')) {
239 $form.append('<input type="hidden" id="ajax_request_hidden" name="ajax_request" value="true" />');
242 $.post($(this).attr('action'), $(this).serialize() , function(data) {
243 if(data.success == true) {
244 // fade out previous messages, if any
245 $('.success').fadeOut();
246 $('.sqlquery_message').fadeOut();
247 // show a message that stays on screen
248 $('#sqlqueryform').before(data.message);
249 // and display the query
250 $('<div class="sqlquery_message"></div>')
251 .html(data.sql_query)
252 .insertBefore('#sqlqueryform');
253 // unnecessary div that came from data.sql_query
254 $('.notice').remove();
255 $('#sqlqueryresults').show();
256 // this happens if a USE command was typed
257 if (typeof data.reload != 'undefined') {
258 $form.find('input[name=db]').val(data.db);
259 // need to regenerate the whole upper part
260 $form.find('input[name=ajax_request]').remove();
261 $form.append('<input type="hidden" name="reload" value="true" />');
262 $.post('db_sql.php', $form.serialize(), function(data) {
263 $('body').html(data);
264 }); // end inner post
267 else if (data.success == false ) {
268 // show an error message that stays on screen
269 $('#sqlqueryform').before(data.error);
270 $('#sqlqueryresults').hide();
273 // real results are returned
274 $('#sqlqueryresults').show();
275 $("#sqlqueryresults").html(data);
276 $("#sqlqueryresults").trigger('appendAnchor');
277 $('#togglequerybox').show();
278 if($("#togglequerybox").siblings(":visible").length > 0) {
279 $("#togglequerybox").trigger('click');
284 }) // end SQL Query submit
287 * Ajax Event handlers for Paginating the results table
291 * Paginate when we click any of the navigation buttons
292 * (only if the element has the ajax class, see $cfg['AjaxEnable'])
294 * @name paginate_nav_button_click
295 * @uses PMA_ajaxShowMessage()
296 * @see $cfg['AjaxEnable']
298 $("input[name=navig].ajax").live('click', function(event) {
300 event.preventDefault();
302 PMA_ajaxShowMessage();
305 * @var $the_form Object referring to the form element that paginates the results table
307 var $the_form = $(this).parent("form");
309 $the_form.append('<input type="hidden" name="ajax_request" value="true" />');
311 $.post($the_form.attr('action'), $the_form.serialize(), function(data) {
312 $("#sqlqueryresults").html(data);
313 $("#sqlqueryresults").trigger('appendAnchor');
316 })// end Paginate results table
319 * Paginate results with Page Selector dropdown
321 * @name paginate_dropdown_change
322 * @see $cfg['AjaxEnable']
324 $("#pageselector").live('change', function(event) {
325 var $the_form = $(this).parent("form");
327 if ($(this).hasClass('ajax')) {
328 event.preventDefault();
330 PMA_ajaxShowMessage();
332 $.post($the_form.attr('action'), $the_form.serialize() + '&ajax_request=true', function(data) {
333 $("#sqlqueryresults").html(data);
334 $("#sqlqueryresults").trigger('appendAnchor');
341 })// end Paginate results with Page Selector
344 * Ajax Event handler for sorting the results table
346 * @name table_results_sort_click
347 * @see $cfg['AjaxEnable']
349 $("#table_results.ajax").find("a[title=Sort]").live('click', function(event) {
350 event.preventDefault();
352 PMA_ajaxShowMessage();
356 $.get($anchor.attr('href'), $anchor.serialize() + '&ajax_request=true', function(data) {
357 $("#sqlqueryresults")
359 .trigger('appendAnchor');
361 })//end Sort results table
364 * Ajax Event handler for the display options
366 * @name displayOptionsForm_submit
367 * @see $cfg['AjaxEnable']
369 $("#displayOptionsForm.ajax").live('submit', function(event) {
370 event.preventDefault();
374 $.post($form.attr('action'), $form.serialize() + '&ajax_request=true' , function(data) {
375 $("#sqlqueryresults")
377 .trigger('appendAnchor');
381 //end displayOptionsForm handler
384 * Ajax Event handlers for Inline Editing
388 * On click, replace the fields of current row with an input/textarea
390 * @name inline_edit_start
391 * @see PMA_ajaxShowMessage()
392 * @see getFieldName()
394 $(".inline_edit_anchor").live('click', function(event) {
396 event.preventDefault();
398 $(this).removeClass('inline_edit_anchor').addClass('inline_edit_active');
400 // Initialize some variables
401 if(disp_mode == 'vertical') {
403 * @var this_row_index Index of the current <td> in the parent <tr>
404 * Current <td> is the inline edit anchor.
406 var this_row_index = $(this).index();
408 * @var $input_siblings Object referring to all inline editable events from same row
410 var $input_siblings = $(this).parents('tbody').find('tr').find('.inline_edit:nth('+this_row_index+')');
412 * @var where_clause String containing the WHERE clause to select this row
414 var where_clause = $(this).parents('tbody').find('tr').find('.where_clause:nth('+this_row_index+')').val();
417 var this_row_index = $(this).parent().index();
418 var $input_siblings = $(this).parent('tr').find('.inline_edit');
419 var where_clause = $(this).parent('tr').find('.where_clause').val();
422 $input_siblings.each(function() {
425 * @var data_value Current value of this field
427 var data_value = $(this).html();
429 // We need to retrieve the value from the server for truncated/relation fields
430 // Find the field name
433 * @var this_field Object referring to this field (<td>)
435 var $this_field = $(this);
437 * @var field_name String containing the name of this field.
438 * @see getFieldName()
440 var field_name = getFieldName($this_field, disp_mode);
442 * @var relation_curr_value String current value of the field (for fields that are foreign keyed).
444 var relation_curr_value = $this_field.find('a').text();
446 * @var enum_curr_value String current value of the field (for fields that are of type enum).
448 var enum_curr_value = $this_field.text();
450 if($this_field.is(':not(.not_null)')){
451 // add a checkbox to mark null for all the field that are nullable.
452 $this_field.html('<div class="null_div">Null :<input type="checkbox" class="checkbox_null_'+ field_name + '_' + this_row_index +'"></div>');
453 // check the 'checkbox_null_<field_name>_<row_index>' if the corresponding value is null
454 if($this_field.is('.null')) {
455 $('.checkbox_null_' + field_name + '_' + this_row_index).attr('checked', true);
458 // if the select/editor is changed un-check the 'checkbox_null_<field_name>_<row_index>'.
459 if ($this_field.is('.enum, .set')) {
460 $this_field.find('select').live('change', function(e) {
461 $('.checkbox_null_' + field_name + '_' + this_row_index).attr('checked', false);
463 } else if ($this_field.is('.relation')) {
464 $this_field.find('select').live('change', function(e) {
465 $('.checkbox_null_' + field_name + '_' + this_row_index).attr('checked', false);
467 $this_field.find('.browse_foreign').live('click', function(e) {
468 $('.checkbox_null_' + field_name + '_' + this_row_index).attr('checked', false);
471 $this_field.find('textarea').live('keypress', function(e) {
472 $('.checkbox_null_' + field_name + '_' + this_row_index).attr('checked', false);
476 // if 'chechbox_null_<field_name>_<row_index>' is clicked empty the corresponding select/editor.
477 $('.checkbox_null_' + field_name + '_' + this_row_index).bind('click', function(e) {
478 if ($this_field.is('.enum, .set')) {
479 $this_field.find('select').attr('value', '');
480 } else if ($this_field.is('.relation')) {
481 // if the dropdown is there to select the foreign value
482 if ($this_field.find('select').length > 0) {
483 $this_field.find('select').attr('value', '');
484 // if foriegn value is selected by browsing foreing values
486 $this_field.find('span.curr_value').empty();
489 $this_field.find('textarea').val('');
494 $this_field.html('<div class="null_div"></div>');
497 // In each input sibling, wrap the current value in a textarea
498 // and store the current value in a hidden span
499 if($this_field.is(':not(.truncated, .transformed, .relation, .enum, .null)')) {
500 // handle non-truncated, non-transformed, non-relation values
501 // We don't need to get any more data, just wrap the value
502 $this_field.append('<textarea>'+data_value+'</textarea>');
503 $this_field.append('<span class="original_data">'+data_value+'</span>');
504 $(".original_data").hide();
506 else if($this_field.is('.truncated, .transformed')) {
508 //handle truncated/transformed values values
511 * @var sql_query String containing the SQL query used to retrieve value of truncated/transformed data
513 var sql_query = 'SELECT ' + field_name + ' FROM ' + window.parent.table + ' WHERE ' + where_clause;
515 // Make the Ajax call and get the data, wrap it and insert it
517 'token' : window.parent.token,
518 'db' : window.parent.db,
519 'ajax_request' : true,
520 'sql_query' : sql_query,
523 if(data.success == true) {
524 $this_field.append('<textarea>'+data.value+'</textarea>');
525 $this_field.append('<span class="original_data">'+data_value+'</span>');
526 $(".original_data").hide();
529 PMA_ajaxShowMessage(data.error);
533 else if($this_field.is('.relation')) {
538 * @var post_params Object containing parameters for the POST request
541 'ajax_request' : true,
542 'get_relational_values' : true,
543 'db' : window.parent.db,
544 'table' : window.parent.table,
545 'column' : field_name,
546 'token' : window.parent.token,
547 'curr_value' : relation_curr_value
550 $.post('sql.php', post_params, function(data) {
551 $this_field.append(data.dropdown);
552 $this_field.append('<span class="original_data">'+data_value+'</span>');
553 $(".original_data").hide();
556 else if($this_field.is('.enum')) {
561 * @var post_params Object containing parameters for the POST request
564 'ajax_request' : true,
565 'get_enum_values' : true,
566 'db' : window.parent.db,
567 'table' : window.parent.table,
568 'column' : field_name,
569 'token' : window.parent.token,
570 'curr_value' : enum_curr_value
573 $.post('sql.php', post_params, function(data) {
574 $this_field.append(data.dropdown);
575 $this_field.append('<span class="original_data">'+data_value+'</span>');
576 $(".original_data").hide();
579 else if($this_field.is('.null')) {
581 $this_field.append('<textarea></textarea>');
582 $this_field.append('<span class="original_data">NULL</span>');
583 $(".original_data").hide();
586 }) // End On click, replace the current field with an input/textarea
589 * After editing, clicking again should post data
592 * @name inline_edit_save
593 * @see PMA_ajaxShowMessage()
594 * @see getFieldName()
596 $(".inline_edit_active").live('click', function(event) {
598 event.preventDefault();
601 * @var $this_td Object referring to the td containing the
602 * "Inline Edit" link that was clicked to save the row that is
606 var $this_td = $(this);
608 var $test_element = ''; // to test the presence of a element
610 // Initialize variables
611 if(disp_mode == 'vertical') {
613 * @var this_td_index Index of the current <td> in the parent <tr>
614 * Current <td> is the inline edit anchor.
616 var this_td_index = $this_td.index();
618 * @var $input_siblings Object referring to all inline editable events from same row
620 var $input_siblings = $this_td.parents('tbody').find('tr').find('.inline_edit:nth('+this_td_index+')');
622 * @var where_clause String containing the WHERE clause to select this row
624 var where_clause = $this_td.parents('tbody').find('tr').find('.where_clause:nth('+this_td_index+')').val();
627 var $input_siblings = $this_td.parent('tr').find('.inline_edit');
628 var where_clause = $this_td.parent('tr').find('.where_clause').val();
632 * @var nonunique Boolean, whether this row is unique or not
634 if($this_td.is('.nonunique')) {
641 // Collect values of all fields to submit, we don't know which changed
643 * @var relation_fields Array containing the name/value pairs of relational fields
645 var relation_fields = {};
647 * @var transform_fields Array containing the name/value pairs for transformed fields
649 var transform_fields = {};
651 * @var transformation_fields Boolean, if there are any transformed fields in this row
653 var transformation_fields = false;
656 * @var sql_query String containing the SQL query to update this row
658 var sql_query = 'UPDATE ' + window.parent.table + ' SET ';
660 $input_siblings.each(function() {
663 * @var this_field Object referring to this field (<td>)
665 var $this_field = $(this);
667 * @var field_name String containing the name of this field.
668 * @see getFieldName()
670 var field_name = getFieldName($this_field, disp_mode);
673 * @var this_field_params Array temporary storage for the name/value of current field
675 var this_field_params = {};
677 if($this_field.is('.transformed')) {
678 transformation_fields = true;
681 * @var is_null String capturing whether 'checkbox_null_<field_name>_<row_index>' is checked.
683 var is_null = $this_field.find('input:checkbox').is(':checked');
687 sql_query += ' ' + field_name + "=NULL , ";
689 if($this_field.is(":not(.relation, .enum)")) {
690 this_field_params[field_name] = $this_field.find('textarea').val();
691 if($this_field.is('.transformed')) {
692 $.extend(transform_fields, this_field_params);
696 // results from a drop-down
697 $test_element = $this_field.find('select');
698 if ($test_element.length != 0) {
699 this_field_params[field_name] = $test_element.val();
702 // results from Browse foreign value
703 $test_element = $this_field.find('span.curr_value');
704 if ($test_element.length != 0) {
705 this_field_params[field_name] = $test_element.text();
708 if($this_field.is('.relation')) {
709 $.extend(relation_fields, this_field_params);
713 sql_query += ' ' + field_name + "='" + this_field_params[field_name].replace(/'/g, "''") + "' , ";
717 //Remove the last ',' appended in the above loop
718 sql_query = sql_query.replace(/,\s$/, '');
719 sql_query += ' WHERE ' + PMA_urldecode(where_clause);
722 * @var rel_fields_list String, url encoded representation of {@link relations_fields}
724 var rel_fields_list = $.param(relation_fields);
727 * @var transform_fields_list String, url encoded representation of {@link transform_fields}
729 var transform_fields_list = $.param(transform_fields);
731 // Make the Ajax post after setting all parameters
733 * @var post_params Object containing parameters for the POST request
735 var post_params = {'ajax_request' : true,
736 'sql_query' : sql_query,
737 'disp_direction' : disp_mode,
738 'token' : window.parent.token,
739 'db' : window.parent.db,
740 'table' : window.parent.table,
741 'clause_is_unique' : nonunique,
742 'where_clause' : where_clause,
743 'rel_fields_list' : rel_fields_list,
744 'do_transformations' : transformation_fields,
745 'transform_fields_list' : transform_fields_list,
747 'submit_type' : 'save'
750 $.post('tbl_replace.php', post_params, function(data) {
751 if(data.success == true) {
752 PMA_ajaxShowMessage(data.message);
753 $this_td.removeClass('inline_edit_active').addClass('inline_edit_anchor');
755 $input_siblings.each(function() {
756 // Inline edit post has been successful.
757 $this_sibling = $(this);
759 var is_null = $this_sibling.find('input:checkbox').is(':checked');
761 $this_sibling.html('NULL');
762 $this_sibling.addClass('null');
764 $this_sibling.removeClass('null');
765 if($this_sibling.is(':not(.relation, .enum)')) {
767 * @var new_html String containing value of the data field after edit
769 var new_html = $this_sibling.find('textarea').val();
771 if($this_sibling.is('.transformed')) {
772 var field_name = getFieldName($this_sibling, disp_mode);
773 $.each(data.transformations, function(key, value) {
774 if(key == field_name) {
775 if($this_sibling.is('.text_plain, .application_octetstream')) {
780 var new_value = $this_sibling.find('textarea').val();
781 new_html = $(value).append(new_value);
791 $test_element = $this_sibling.find('select');
792 if ($test_element.length != 0) {
793 new_value = $test_element.val();
796 $test_element = $this_sibling.find('span.curr_value');
797 if ($test_element.length != 0) {
798 new_value = $test_element.text();
801 if($this_sibling.is('.relation')) {
802 var field_name = getFieldName($this_sibling, disp_mode);
803 $.each(data.relations, function(key, value) {
804 if(key == field_name) {
805 new_html = $(value).append(new_value);
810 if($this_sibling.is('.enum')) {
811 new_html = new_value;
814 $this_sibling.html(new_html);
819 PMA_ajaxShowMessage(data.error);
822 }) // End After editing, clicking again should post data
823 }, 'top.frame_content') // end $(document).ready()
826 * Starting from some th, change the class of all td under it
828 function PMA_changeClassForColumn($this_th, newclass) {
829 // index 0 is the th containing the big T
830 var th_index = $this_th.index();
831 // .eq() is zero-based
833 var $tr_with_data = $this_th.closest('table').find('tbody tr ').has('td.data');
834 $tr_with_data.each(function() {
835 $(this).find('td.data:eq('+th_index+')').toggleClass(newclass);
839 $(document).ready(function() {
841 $('.browse_foreign').live('click', function(e) {
843 window.open(this.href, 'foreigners', 'width=640,height=240,scrollbars=yes,resizable=yes');
845 $anchor.addClass('browse_foreign_clicked');
850 * vertical column highlighting in horizontal mode when hovering over the column header
852 $('.column_heading').live('hover', function() {
853 PMA_changeClassForColumn($(this), 'hover');
857 * vertical column marking in horizontal mode when clicking the column header
859 $('.column_heading').live('click', function() {
860 PMA_changeClassForColumn($(this), 'marked');