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 var img_src = $img_object.attr('src').replace(/b_edit/,'b_inline_edit');
73 $img_object.attr('src', img_src);
76 .addClass('inline_edit_anchor')
77 .find('a').attr('href', '#')
79 .text(' ' + PMA_messages['strInlineEdit'])
80 .prepend($img_object);
82 $cloned_tr.insertAfter($this_tr);
85 $('#rowsDeleteForm').find('tbody').find('th').each(function() {
86 var $this_th = $(this);
87 if ($this_th.attr('rowspan') == 4) {
88 $this_th.attr('rowspan', '5');
93 $('.edit_row_anchor').each(function() {
96 $this_td.removeClass('edit_row_anchor');
98 var $cloned_anchor = $this_td.clone();
100 var $img_object = $cloned_anchor.find('img').attr('title', PMA_messages['strInlineEdit']);
101 var img_src = $img_object.attr('src').replace(/b_edit/,'b_inline_edit');
102 $img_object.attr('src', img_src);
104 $cloned_anchor.addClass('inline_edit_anchor')
105 .find('a').attr('href', '#')
107 .text(' ' + PMA_messages['strInlineEdit'])
108 .prepend($img_object);
110 $this_td.after($cloned_anchor);
113 $('#rowsDeleteForm').find('thead, tbody').find('th').each(function() {
114 var $this_th = $(this);
115 if ($this_th.attr('colspan') == 4) {
116 $this_th.attr('colspan', '5');
127 * @description <p>Ajax scripts for sql and browse pages</p>
129 * Actions ajaxified here:
131 * <li>Retrieve results of an SQL query</li>
132 * <li>Paginate the results table</li>
133 * <li>Sort the results table</li>
134 * <li>Change table according to display options</li>
135 * <li>Inline editing of data</li>
138 * @name document.ready
141 $(document).ready(function() {
144 * Set a parameter for all Ajax queries made on this page. Don't let the
145 * web server serve cached pages
152 * current value of the direction in which the table is displayed
157 var disp_mode = $("#top_direction_dropdown").val();
160 * Update value of {@link jQuery.disp_mode} everytime the direction dropdown changes value
162 * @name direction_dropdown_change
164 $("#top_direction_dropdown, #bottom_direction_dropdown").live('change', function(event) {
165 disp_mode = $(this).val();
169 * Attach the {@link appendInlineAnchor} function to a custom event, which
170 * will be triggered manually everytime the table of results is reloaded
173 $("#sqlqueryresults").live('appendAnchor',function() {
174 appendInlineAnchor();
178 * Trigger the appendAnchor event to prepare the first table for inline edit
179 * (see $GLOBALS['cfg']['AjaxEnable'])
181 * @name sqlqueryresults_trigger
183 $("#sqlqueryresults.ajax").trigger('appendAnchor');
186 * Append the "Show/Hide query box" message to the query input form
189 * @name appendToggleSpan
191 // do not add this link more than once
192 if (! $('#sqlqueryform').find('a').is('#togglequerybox')) {
193 $('<a id="togglequerybox"></a>')
194 .html(PMA_messages['strHideQueryBox'])
195 .appendTo("#sqlqueryform")
196 // initially hidden because at this point, nothing else
197 // appears under the link
200 // Attach the toggling of the query box visibility to a click
201 $("#togglequerybox").bind('click', function() {
203 $link.siblings().slideToggle("fast");
204 if ($link.text() == PMA_messages['strHideQueryBox']) {
205 $link.text(PMA_messages['strShowQueryBox']);
206 // cheap trick to add a spacer between the menu tabs
207 // and "Show query box"; feel free to improve!
208 $('#togglequerybox_spacer').remove();
209 $link.before('<br id="togglequerybox_spacer" />');
211 $link.text(PMA_messages['strHideQueryBox']);
213 // avoid default click action
219 * Ajax Event handler for 'SQL Query Submit'
221 * @see PMA_ajaxShowMessage()
222 * @see $cfg['AjaxEnable']
224 * @name sqlqueryform_submit
226 $("#sqlqueryform.ajax").live('submit', function(event) {
227 event.preventDefault();
228 // remove any div containing a previous error message
229 $('.error').remove();
232 PMA_ajaxShowMessage();
234 if (! $form.find('input:hidden').is('#ajax_request_hidden')) {
235 $form.append('<input type="hidden" id="ajax_request_hidden" name="ajax_request" value="true" />');
238 $.post($(this).attr('action'), $(this).serialize() , function(data) {
239 if(data.success == true) {
240 // fade out previous messages, if any
241 $('.success').fadeOut();
242 $('.sqlquery_message').fadeOut();
243 // show a message that stays on screen
244 $('#sqlqueryform').before(data.message);
245 // and display the query
246 $('<div class="sqlquery_message"></div>')
247 .html(data.sql_query)
248 .insertBefore('#sqlqueryform');
249 // unnecessary div that came from data.sql_query
250 $('.notice').remove();
251 $('#sqlqueryresults').show();
252 // this happens if a USE command was typed
253 if (typeof data.reload != 'undefined') {
254 $form.find('input[name=db]').val(data.db);
255 // need to regenerate the whole upper part
256 $form.find('input[name=ajax_request]').remove();
257 $form.append('<input type="hidden" name="reload" value="true" />');
258 $.post('db_sql.php', $form.serialize(), function(data) {
259 $('body').html(data);
260 }); // end inner post
263 else if (data.success == false ) {
264 // show an error message that stays on screen
265 $('#sqlqueryform').before(data.error);
266 $('#sqlqueryresults').hide();
269 // real results are returned
270 $('#sqlqueryresults').show();
271 $("#sqlqueryresults").html(data);
272 $("#sqlqueryresults").trigger('appendAnchor');
273 $('#togglequerybox').show();
274 if($("#togglequerybox").siblings(":visible").length > 0) {
275 $("#togglequerybox").trigger('click');
280 }) // end SQL Query submit
283 * Ajax Event handlers for Paginating the results table
287 * Paginate when we click any of the navigation buttons
288 * (only if the element has the ajax class, see $cfg['AjaxEnable'])
290 * @name paginate_nav_button_click
291 * @uses PMA_ajaxShowMessage()
292 * @see $cfg['AjaxEnable']
294 $("input[name=navig].ajax").live('click', function(event) {
296 event.preventDefault();
298 PMA_ajaxShowMessage();
301 * @var $the_form Object referring to the form element that paginates the results table
303 var $the_form = $(this).parent("form");
305 $the_form.append('<input type="hidden" name="ajax_request" value="true" />');
307 $.post($the_form.attr('action'), $the_form.serialize(), function(data) {
308 $("#sqlqueryresults").html(data);
309 $("#sqlqueryresults").trigger('appendAnchor');
312 })// end Paginate results table
315 * Paginate results with Page Selector dropdown
317 * @name paginate_dropdown_change
318 * @see $cfg['AjaxEnable']
320 $("#pageselector").live('change', function(event) {
321 var $the_form = $(this).parent("form");
323 if ($(this).hasClass('ajax')) {
324 event.preventDefault();
326 PMA_ajaxShowMessage();
328 $.post($the_form.attr('action'), $the_form.serialize() + '&ajax_request=true', function(data) {
329 $("#sqlqueryresults").html(data);
330 $("#sqlqueryresults").trigger('appendAnchor');
337 })// end Paginate results with Page Selector
340 * Ajax Event handler for sorting the results table
342 * @name table_results_sort_click
343 * @see $cfg['AjaxEnable']
345 $("#table_results.ajax").find("a[title=Sort]").live('click', function(event) {
346 event.preventDefault();
348 PMA_ajaxShowMessage();
352 $.get($anchor.attr('href'), $anchor.serialize() + '&ajax_request=true', function(data) {
353 $("#sqlqueryresults")
355 .trigger('appendAnchor');
357 })//end Sort results table
360 * Ajax Event handler for the display options
362 * @name displayOptionsForm_submit
363 * @see $cfg['AjaxEnable']
365 $("#displayOptionsForm.ajax").live('submit', function(event) {
366 event.preventDefault();
370 $.post($form.attr('action'), $form.serialize() + '&ajax_request=true' , function(data) {
371 $("#sqlqueryresults")
373 .trigger('appendAnchor');
377 //end displayOptionsForm handler
380 * Ajax Event handlers for Inline Editing
384 * On click, replace the fields of current row with an input/textarea
386 * @name inline_edit_start
387 * @see PMA_ajaxShowMessage()
388 * @see getFieldName()
390 $(".inline_edit_anchor").live('click', function(event) {
392 event.preventDefault();
394 $(this).removeClass('inline_edit_anchor').addClass('inline_edit_active');
396 // Initialize some variables
397 if(disp_mode == 'vertical') {
399 * @var this_row_index Index of the current <td> in the parent <tr>
400 * Current <td> is the inline edit anchor.
402 var this_row_index = $(this).index();
404 * @var $input_siblings Object referring to all inline editable events from same row
406 var $input_siblings = $(this).parents('tbody').find('tr').find('.inline_edit:nth('+this_row_index+')');
408 * @var where_clause String containing the WHERE clause to select this row
410 var where_clause = $(this).parents('tbody').find('tr').find('.where_clause:nth('+this_row_index+')').val();
413 var $input_siblings = $(this).parent('tr').find('.inline_edit');
414 var where_clause = $(this).parent('tr').find('.where_clause').val();
417 $input_siblings.each(function() {
420 * @var data_value Current value of this field
422 var data_value = $(this).html();
424 // We need to retrieve the value from the server for truncated/relation fields
425 // Find the field name
428 * @var this_field Object referring to this field (<td>)
430 var $this_field = $(this);
432 * @var field_name String containing the name of this field.
433 * @see getFieldName()
435 var field_name = getFieldName($this_field, disp_mode);
437 * @var relation_curr_value String current value of the field (for fields that are foreign keyed).
439 var relation_curr_value = $this_field.find('a').text();
441 * @var enum_curr_value String current value of the field (for fields that are of type enum).
443 var enum_curr_value = $this_field.text();
445 if($this_field.is(':not(.not_null)')){
446 // add a checkbox to mark null for all the field that are nullable.
447 $this_field.html('<div class="null_div">Null :<input type="checkbox" class="checkbox_null_'+ field_name +'"></div>');
448 // check the 'checkbox_null_<field_name>' if the value is null
449 if($this_field.is('.null')) {
450 $('.checkbox_null_' + field_name).attr('checked', true);
453 // if the select/editor is changed un-check the 'checkbox_null_<field_name>'.
454 if ($this_field.is('.enum, .set')) {
455 var $editor = $this_field.find('select');
456 } else if ($this_field.is('.relation')) {
457 var $editor = $this_field.find('select');
459 var $editor = $this_field.find('textarea');
461 $editor.live('change', function(e) {
462 $('.checkbox_null_' + field_name).attr('checked', false);
465 // if 'chechbox_null_<field_name>' is clicked empty the select/editor.
466 $('.checkbox_null_' + field_name).bind('click', function(e) {
467 if ($this_field.is('.enum, .set')) {
468 $this_field.find('select').selectedIndex = -1;
469 } else if ($this_field.is('.relation')) {
470 $this_field.find('select').attr('value', '');
472 $this_field.find('textarea').empty();
477 $this_field.html('<div class="null_div"></div>');
480 // In each input sibling, wrap the current value in a textarea
481 // and store the current value in a hidden span
482 if($this_field.is(':not(.truncated, .transformed, .relation, .enum, .null)')) {
483 // handle non-truncated, non-transformed, non-relation values
484 // We don't need to get any more data, just wrap the value
485 $this_field.append('<textarea>'+data_value+'</textarea>');
486 $this_field.append('<span class="original_data">'+data_value+'</span>');
487 $(".original_data").hide();
489 else if($this_field.is('.truncated, .transformed')) {
491 //handle truncated/transformed values values
494 * @var sql_query String containing the SQL query used to retrieve value of truncated/transformed data
496 var sql_query = 'SELECT ' + field_name + ' FROM ' + window.parent.table + ' WHERE ' + where_clause;
498 // Make the Ajax call and get the data, wrap it and insert it
500 'token' : window.parent.token,
501 'db' : window.parent.db,
502 'ajax_request' : true,
503 'sql_query' : sql_query,
506 if(data.success == true) {
507 $this_field.append('<textarea>'+data.value+'</textarea>');
508 $this_field.append('<span class="original_data">'+data_value+'</span>');
509 $(".original_data").hide();
512 PMA_ajaxShowMessage(data.error);
516 else if($this_field.is('.relation')) {
521 * @var post_params Object containing parameters for the POST request
524 'ajax_request' : true,
525 'get_relational_values' : true,
526 'db' : window.parent.db,
527 'table' : window.parent.table,
528 'column' : field_name,
529 'token' : window.parent.token,
530 'curr_value' : relation_curr_value
533 $.post('sql.php', post_params, function(data) {
534 $this_field.append(data.dropdown);
535 $this_field.append('<span class="original_data">'+data_value+'</span>');
536 $(".original_data").hide();
539 else if($this_field.is('.enum')) {
544 * @var post_params Object containing parameters for the POST request
547 'ajax_request' : true,
548 'get_enum_values' : true,
549 'db' : window.parent.db,
550 'table' : window.parent.table,
551 'column' : field_name,
552 'token' : window.parent.token,
553 'curr_value' : enum_curr_value
556 $.post('sql.php', post_params, function(data) {
557 $this_field.append(data.dropdown);
558 $this_field.append('<span class="original_data">'+data_value+'</span>');
559 $(".original_data").hide();
562 else if($this_field.is('.null')) {
564 $this_field.append('<textarea></textarea>');
565 $this_field.append('<span class="original_data">NULL</span>');
566 $(".original_data").hide();
569 }) // End On click, replace the current field with an input/textarea
572 * After editing, clicking again should post data
575 * @name inline_edit_save
576 * @see PMA_ajaxShowMessage()
577 * @see getFieldName()
579 $(".inline_edit_active").live('click', function(event) {
581 event.preventDefault();
584 * @var $this_td Object referring to the td containing the
585 * "Inline Edit" link that was clicked to save the row that is
589 var $this_td = $(this);
591 var $test_element = ''; // to test the presence of a element
593 // Initialize variables
594 if(disp_mode == 'vertical') {
596 * @var this_td_index Index of the current <td> in the parent <tr>
597 * Current <td> is the inline edit anchor.
599 var this_td_index = $this_td.index();
601 * @var $input_siblings Object referring to all inline editable events from same row
603 var $input_siblings = $this_td.parents('tbody').find('tr').find('.inline_edit:nth('+this_td_index+')');
605 * @var where_clause String containing the WHERE clause to select this row
607 var where_clause = $this_td.parents('tbody').find('tr').find('.where_clause:nth('+this_td_index+')').val();
610 var $input_siblings = $this_td.parent('tr').find('.inline_edit');
611 var where_clause = $this_td.parent('tr').find('.where_clause').val();
615 * @var nonunique Boolean, whether this row is unique or not
617 if($this_td.is('.nonunique')) {
624 // Collect values of all fields to submit, we don't know which changed
626 * @var relation_fields Array containing the name/value pairs of relational fields
628 var relation_fields = {};
630 * @var transform_fields Array containing the name/value pairs for transformed fields
632 var transform_fields = {};
634 * @var transformation_fields Boolean, if there are any transformed fields in this row
636 var transformation_fields = false;
639 * @var sql_query String containing the SQL query to update this row
641 var sql_query = 'UPDATE ' + window.parent.table + ' SET ';
643 $input_siblings.each(function() {
646 * @var this_field Object referring to this field (<td>)
648 var $this_field = $(this);
650 * @var field_name String containing the name of this field.
651 * @see getFieldName()
653 var field_name = getFieldName($this_field, disp_mode);
656 * @var this_field_params Array temporary storage for the name/value of current field
658 var this_field_params = {};
660 if($this_field.is('.transformed')) {
661 transformation_fields = true;
664 * @var is_null String capturing whether 'checkbox_null_<field_name>' is checked.
666 var is_null = $this_field.find('input:checkbox').is(':checked');
670 sql_query += ' ' + field_name + "=NULL , ";
672 if($this_field.is(":not(.relation, .enum)")) {
673 this_field_params[field_name] = $this_field.find('textarea').val();
674 if($this_field.is('.transformed')) {
675 $.extend(transform_fields, this_field_params);
679 // results from a drop-down
680 $test_element = $this_field.find('select');
681 if ($test_element.length != 0) {
682 this_field_params[field_name] = $test_element.val();
685 // results from Browse foreign value
686 $test_element = $this_field.find('span.curr_value');
687 if ($test_element.length != 0) {
688 this_field_params[field_name] = $test_element.text();
691 if($this_field.is('.relation')) {
692 $.extend(relation_fields, this_field_params);
696 sql_query += ' ' + field_name + "='" + this_field_params[field_name].replace(/'/g, "''") + "' , ";
700 //Remove the last ',' appended in the above loop
701 sql_query = sql_query.replace(/,\s$/, '');
702 sql_query += ' WHERE ' + PMA_urldecode(where_clause);
705 * @var rel_fields_list String, url encoded representation of {@link relations_fields}
707 var rel_fields_list = $.param(relation_fields);
710 * @var transform_fields_list String, url encoded representation of {@link transform_fields}
712 var transform_fields_list = $.param(transform_fields);
714 // Make the Ajax post after setting all parameters
716 * @var post_params Object containing parameters for the POST request
718 var post_params = {'ajax_request' : true,
719 'sql_query' : sql_query,
720 'disp_direction' : disp_mode,
721 'token' : window.parent.token,
722 'db' : window.parent.db,
723 'table' : window.parent.table,
724 'clause_is_unique' : nonunique,
725 'where_clause' : where_clause,
726 'rel_fields_list' : rel_fields_list,
727 'do_transformations' : transformation_fields,
728 'transform_fields_list' : transform_fields_list,
730 'submit_type' : 'save'
733 $.post('tbl_replace.php', post_params, function(data) {
734 if(data.success == true) {
735 PMA_ajaxShowMessage(data.message);
736 $this_td.removeClass('inline_edit_active').addClass('inline_edit_anchor');
738 $input_siblings.each(function() {
739 // Inline edit post has been successful.
740 $this_sibling = $(this);
742 var is_null = $this_sibling.find('input:checkbox').is(':checked');
744 $this_sibling.html('NULL');
745 $this_sibling.addClass('null');
747 $this_sibling.removeClass('null');
748 if($this_sibling.is(':not(.relation, .enum)')) {
750 * @var new_html String containing value of the data field after edit
752 var new_html = $this_sibling.find('textarea').val();
754 if($this_sibling.is('.transformed')) {
755 var field_name = getFieldName($this_sibling, disp_mode);
756 $.each(data.transformations, function(key, value) {
757 if(key == field_name) {
758 if($this_sibling.is('.text_plain, .application_octetstream')) {
763 var new_value = $this_sibling.find('textarea').val();
764 new_html = $(value).append(new_value);
774 $test_element = $this_sibling.find('select');
775 if ($test_element.length != 0) {
776 new_value = $test_element.val();
779 $test_element = $this_sibling.find('span.curr_value');
780 if ($test_element.length != 0) {
781 new_value = $test_element.text();
784 if($this_sibling.is('.relation')) {
785 var field_name = getFieldName($this_sibling, disp_mode);
786 $.each(data.relations, function(key, value) {
787 if(key == field_name) {
788 new_html = $(value).append(new_value);
793 if($this_sibling.is('.enum')) {
794 new_html = new_value;
797 $this_sibling.html(new_html);
802 PMA_ajaxShowMessage(data.error);
805 }) // End After editing, clicking again should post data
806 }, 'top.frame_content') // end $(document).ready()
809 * Starting from some th, change the class of all td under it
811 function PMA_changeClassForColumn($this_th, newclass) {
812 // index 0 is the th containing the big T
813 var th_index = $this_th.index();
814 // .eq() is zero-based
816 var $tr_with_data = $this_th.closest('table').find('tbody tr ').has('td.data');
817 $tr_with_data.each(function() {
818 $(this).find('td.data:eq('+th_index+')').toggleClass(newclass);
822 $(document).ready(function() {
824 $('.browse_foreign').live('click', function(e) {
826 window.open(this.href, 'foreigners', 'width=640,height=240,scrollbars=yes,resizable=yes');
828 $anchor.addClass('browse_foreign_clicked');
833 * vertical column highlighting in horizontal mode when hovering over the column header
835 $('.column_heading').live('hover', function() {
836 PMA_changeClassForColumn($(this), 'hover');
840 * vertical column marking in horizontal mode when clicking the column header
842 $('.column_heading').live('click', function() {
843 PMA_changeClassForColumn($(this), 'marked');