dbr.editable
- Prepares a column so it is in-place editabledbr.upload
- Upload a file to the server's filesystemdbr.upload.options
- Set an upload optiondbr.selectable
- Convert an ID column to a selectable row to be used with a report linked buttondbr.selectable.selected
- Preselect selectable rows by passing the ID value as a parameter
select 'dbr.editable', ColumnReference, procedure_name, [,parameter_name=paramref] [,type=input_type] [,select=query [[ColumnReference]] ] [,options={'jeditable_options'}]
select 'dbr.upload', path_to_directory, upload_sp [, parameters]
select 'dbr.upload.options', option, value
select 'dbr.selectable', ColumnReference
select 'dbr.selectable.selected', id
Where:
Defines the selection query used when the type is 'select' or 'radiobutton.' The query should return the ID and the user-visible value. It can be a direct select query or a stored procedure call. If a stored procedure is used, it can leverage automatic parameters, and its parameters may refer to both the result set columns and report parameters.
You have the option to use the [ColumnReference] notation to reference the columns in the particular row in the query when utilizing a stored procedure.
If the type is set to 'select' or 'select_find,' you can employ opgroups by specifying the group as the third column in the select query.
'autosubmit': 1
Does not display OK and Cancel buttons'display':'inline'
Places items side-by-side'scroll': 1
Shows checkboxes in a scrollable list'find': 1
Allows textual search'collapse': 1
Allows collapsing the list'ui_height': 200
Height of the scrollable list in pxtitle
Title the be shown in uploadnoreplace
Prevent overwriting files (value = 1)accepted_files
A comma-separated list of accepted file extensionsmaxfiles
maximum number of uploaded filesmaxfilesize
maximum size of the uploaded file in megabytesmaxheight
maximum height of the uploaded image file in pixelsmaxwidth
maximum width of the uploaded image file in pixelsquality
The quality of the resized JPG/WebP images (0 to 1, default 0.8)
A report column can be marked as editable (in-place editing) with the dbr.editable
command. The command takes column number and parameter references
as parameters. When the user clicks on the editable column, an in-place editor is shown and the edited value with specified parameters
is sent to the procedure that was given as a parameter. The procedure can perform the update or reject the update and
return the original value. If the editing procedure returns the value (single row, single column), myDBR will format the cell using the defined cell formatting.
Sometimes not all rows in a column or columns in crosstab should be edited. You can prevent editing on selected cells by adding show_link-parameter to the
dbr.editable command. You can also use 'no_edit'-class to the cell by using the dbr.cellclass
command.
(If you wish to edit more than one column at a time see Creating editable reports for more info.)
With options you can:
select 'dbr.editable', 'col', 'sp_DBR_budget_edit_comment', 'inID=is', 'inQuarter=Q', 'type=textarea', "options={'rows':6,'cols':20}";
select 'dbr.editable', 'col', 'sp_DBR_budget_edit', 'inId=id', 'inQuarter=Q', "options={'height':20}";
select 'dbr.editable', 'col', 'sp_DBR_budget_edit', 'inId=id', 'inQuarter=Q', "options={'height':20, 'callback':autosum_int}";
dbr.javascript
) that has two parameters. First parameter is the value or values JSON.
If the editing report returns just single value the first parameter contains that value. If editing report returns multiple columns, the first parameter is a JSON string that you can parse in a callback with JSON.parse. An example of returning multiple columns would be that you want to change other columns based on the editing. (The second optional parameter contains the Jeditable settingsm which you probably do not need).
col_value_get( obj[, column [, decimals [, value [, prefix [, suffix]]]]] )
col_value_set( obj[, column [, decimals [, value [, prefix [, suffix]]]]] )
ct_total( obj[, NULL [, decimals [, value [, prefix [, suffix]]]]] )
col_value_text(obj [, column[, value]])
col_sum( obj [, column [, decimals[, prefix[, suffix]]]] )
autosum_int( obj, [colindex [, prefix[, suffix]]] )
autosum_float( obj, [colindex [, decimals [, prefix[ , suffix]]]] )
obj
is reference to the edited cell (javascript 'this').
In the example, we'll define the in-place editing to be available in the following cross-table report.
We'll use the dbr.editable
command to mark column 4 (Budget) to be updateable with the procedure sp_DBR_update_crosstable
.
Parameters for the command in the example are:
id
sp_DBR_update_crosstable
inCategory=cat
inWeek=Week
create procedure sp_DBR_InPlaceEditing begin select 'dbr.editable', 'Budget', 'sp_DBR_update_crosstable', 'inCategory=cat', 'inWeek=week'; select 'dbr.crosstab', 'Week'; select 'dbr.count', 'cat'; select 'dbr.sum', 'Total'; select 'dbr.hsum', 'Total'; select 'dbr.hidecolumns', 1; select category as 'Film category[cat]', payment_week 'Week', amount as 'Total', budget as 'Budget', category_id from film_budget; end
The report that is doing the actual updating can either reject the update and return the original value or simply update the new value.
The report gets all parameters specified in the dbr.editable
-call and an additional parameter for the value.
The last parameter for the editing procedure is automatically populated by the value of the edited field. You can name the parameter as you like (inValue in the example).
create procedure sp_DBR_update_crosstable( inCategory int, inWeek int, inValue float ) begin /* Check the data */ if (inValue>0) then /* Accept the update, no need to return a value */ update film_budget set budget = inValue where category_id=inCategory and payment_week=inWeek; else /* Reject the update, return the original value */ select budget from film_budget where category_id=inCategory and payment_week=inWeek; end if; end
In the example, we'll define the in-place editing to be available in the following cross-table report (Budget column under weeks).
When the editing procedure returns more than one column, the return values are sent back as a JSON string.
create procedure sp_DBR_edit_with_callback_JSON() BEGIN select 'dbr.javascript', ' function country_callback(val) { /* 'this' is the edited cell 'val' is a JSON string as the editing report returns multiple columns The JSON string can be parsed to a JSON object using JSON.parse */ var c = JSON.parse(val); this.textContent = c.Name; this.nextSibling.textContent = c.Code; this.nextSibling.nextSibling.textContent = c.Continent; } '; select 'dbr.editable', 'Country', 'sp_DBR_country_edit', 'inPerson=Person', 'type=select_find', 'select=select Code, Name from demo_country', "options={'callback':country_callback}"; select p.id as 'PersonID', p.Person, c.Name as 'Country', c.Code as 'Country Code', c.Continent from Persons p join Countries on c.Code=p.CountryCode; end
The Country-column is editable, user chooses from the selectlist the value and the selected Country's code is passed on to the editing report
drop procedure sp_DBR_country_edit $$ create procedure sp_DBR_country_edit( inPerson int, inCode varchar(3) ) begin update Persons set CountryCode=inCode where id=inPerson; /* Return two columns -> will be sent back as a JSON string */ select inCode as Code, Name, Continent from Countries where Code=inCode; end
We'll use different input types for the following data: a plain input, a textarea (autosizing), a select list, radio buttons, a datepicker, and a colorpicker. Dates are shown as-is from the database.
For the radio button and selectlist the textual representation shown to the user will be fetched. myDBR will pass the ID to the editing report.
create procedure sp_DBR_update_inputtype() begin select 'dbr.pageview'; select 'dbr.javascript', ' function color_callback(settings, cell) { /* Chosen (format #626262) value is in 'this' colorpicker.convert(this) will convert the color value into visible color */ colorpicker.convert(this); }'; select 'dbr.editable', 'Basic', 'sp_DBR_input_edit', 'inId=id'; select 'dbr.editable', 'autosize', 'sp_DBR_text_edit', 'inId=id', 'type=autosize'; select 'dbr.editable', 'select', 'sp_DBR_varchar_edit', 'inId=id', 'type=select', "select=select id, value from char_values"; select 'dbr.editable', 'radio', 'sp_DBR_integer_edit', 'inId=id', 'type=radiobutton', "select=sp_radiovalues"; select 'dbr.editable', 'Date', 'sp_DBR_date_edit', 'inId=id', 'type=datepicker'; select 'dbr.editable', 'color', 'sp_DBR_color_edit', 'inId=id', 'type=colorpicker', "options={'callback':color_callback}"; select 'dbr.hidecolumn' 'id'; select e.plain_column as 'Basic Input[Basic]', e.text_column as 'Textarea[autosize]', c.value as 'Select list[select]', i.value as 'Radio Button[radio]', e.date_column as 'Date', e.color as 'Color[color]', /* color format: #626262 */ id from mydb.editable e join int_values i on i.id = e.integer_column join char_values c on c.id = e.char_column; end
With 'autocomplete'-editable type, one can serach and autocomplete from large amount of data as the suggestion based on user's input is done via Ajax. User edit the visible value (for example country name) and the editable passes the corresponding ID (for example country code for a country) into the editing procedure. If one passes allow_new
option to the call ("options={'allow_new': true}";
), user can enter value other than fetched via Ajax.
select 'dbr.hidecolumn', 'ID'; select 'dbr.editable', '[autoc]', 'sp_DBR_edit_country_ac', 'in_id=ID', 'type=autocomplete','select=sp_country_ac'; select e.id as 'ID', c.country as 'Country[autoc]' from mydata e left join countries c on c.code=e.country_code ;
Where the editing report is:
create procedure sp_DBR_edit_country_ac( in_id int, in_value char(2) ) begin update mydata set country_code = in_value where id=in_id; -- Return data for callback handling select code, country, continent from countries where code = in_value; end
The autocomplete Ajax-query returns three columns: the visible value and the optional secondary row (leave empty if not shown) and the id for the row. Limit the query to 20 rows (maximum shown at a time).
create procedure sp_country_ac( in_search varchar(255) ) begin select country, concat('Capital: ', capital), code from countries where country like concat('%', in_search, '%') limit 20; end
Table int_values and its associated sp_radiovalues looks like this:
select * from int_values; id name ----------- 1 'Asia' 2 'Europe' 3 'North America' 4 'Africa' 5 'Oceania' 6 'South America' 7 'Antartica' create procedure sp_radiovalues() begin select id, name from int_values; end
Updating procedures get two parameters. First, the supplied 'id' parameter from the first column of the result set and the user-supplied value with the specific data type. The update procedures can include checks for the input, update the data, and optionally return a value that will replace the user input.
For the date column, we'll do a direct update
create procedure sp_DBR_date_edit ( inId int, inUservalue date ) begin update mydb.editable set date_column = inUservalue where id=inId; end
For the datetime we, for example, can check if the date belongs to the current year and accept/reject the update based on that.
create procedure sp_DBR_datetime_edit ( inId int, inUservalue datetime ) begin /* Does the user date belong to current year? */ if ( year(inUservalue) = year(now()) ) then update mydb.editable set datetime_column = inUservalue where id=inId; else /* Do not update and return the original data back to report */ select datetime_column from mydb.editable where id=inId; end if; end
myDBR uses the IDs of the user-selectable radio buttons. If this update would be rejected and ID converted, myDBR will automatically convert the ID back into user-visible value.
create procedure sp_DBR_integer_edit ( inId int, inUservalue int ) begin update mydb.editable set integer_column = inUservalue where id=inId; end
Select works just like the radio button automating the conversion between ids and user-visible values.
create procedure sp_DBR_varchar_edit ( inId int, inUservalue varchar(5) ) begin update mydb.editable set varchar_column = inUservalue where id=inId; end
When using checkbox-type, one places the IDs (numeric) and the user-visible values in separate columns. The ID column is pinpointed with 'checkbox_id'-option. myDBR will return the selected values wrapped in DIV with class editable_cb_item
and separated by DIV with class editable_cb_item_comma
. This allows the formatting of the visible values either in the same line separated with a comma or in separate lines.
Checkbox-editing has also an option which allows using find/scroll/collapse options (similar to checkbox parameters). The html-option will preserve the HTML in the cell. The
find
-option allows searching for the choices, the scroll
-option shows the list as a scrollable list, and the collapse
-option (rarely used) allows collapsing the selection.
create procedure sp_DBR_editable_checkbox() begin SET @@group_concat_max_len = 100000; /* For MySQL group_concat_max_len has a default value of 1024, we'll prepare for more */ select 'dbr.editable', '[Categories]', 'sp_DBR_edit_film_category', 'in_film_id=film_id','checkbox_id=ids', 'type=checkbox', 'select=select category_id, name from category'; select fi.film_id, fi.title, ca.cat_ids as 'ids', ca.cat_names as 'Categories' from film_samples fi join ( select f.film_id, group_concat(c.category_id) as cat_ids, group_concat(c.name) as cat_names from film_samples f left join film_category fc on fc.film_id=f.film_id left join category c on c.category_id=fc.category_id group by f.film_id ) ca on ca.film_id = fi.film_id; select 'dbr.editable', '[Countries]', 'sp_DBR_edit_selected_demo_countries', 'in_id=id','checkbox_id=codes', 'type=checkbox', 'select=select c.Code, concat( c.flag , ' ',c.Name), c.Continent from country c order by c.Continent, c.Name',"options={'html':1, 'scroll':1, 'find':1}"; select d.id, group_concat(s.country_code) as 'codes, concat('dbr.html:',group_concat(concat('<div class="editable_cb_item">',c.flag, ' ',c.name,'</div>') SEPARATOR '<div class="editable_cb_item_comma">,</div>')) as Countries from data d left join selected_demo_countries s on s.id=d.id left join country c on c.Code=s.country_code group by d.id; end
When stored procedures are used in dbr.editable select parameters, you can pass parameters to it using normal column references.
create procedure sp_DBR_update_inputtype_parameter() begin select 'dbr.pageview'; select 'dbr.editable', 'Select', 'sp_DBR_integer_edit', 'inId=id', 'type=select', "select=sp_select_with_params [id]"; select id, date_column as 'Date', datetime_column as 'Datetime', i.value as 'Radio Button[radio]', c.value as 'Select' from mydb.editable e, int_values i, char_values c where e.integer_column = i.id and e.char_column = c.id; end
File renaming editable type is meant for situations where there is a table in the database containing the info which files the user can edit. The routine changes the actual file name and the reference in the database.
The actual path to the file is now shown to the user, the routine passes the ID of the table containing the actual filename.
create procedure sp_DBR_editable_rename() begin select 'dbr.editable', 'file', 'sp_DBR_editable_rename_do', 'type=file_rename', 'in_id=id'; select id, substring_index(file, '/', -1) as 'file' from editable_rename where id=1; end;
The actual path to the file is now shown to the user, the routine passes the ID of the table containing the actual filename. The editable procedure for file rename has two automatically filled parameters (the last two). The penultimate parameter gets values of 'get' and 'set' and the last one the user-edited file name. The routine is called first with the parameter 'get' to get the full filename based on the ID. At this stage, the routine should return the full filename. After a successful rename, the routine is called a second time so that the file reference can be updated. The file will not be renamed if a) file does not exist in the filesystem b) there exists already a file with the new name or c) there are not enough filesystem permissions to the rename action.
create procedure sp_DBR_editable_rename_do( in_id int, in_action varchar(10), in_file varchar(255) ) begin if (in_action='get') then select file from editable_rename where id=in_id; else update editable_rename set file = in_file where id=in_id; end if; end
Auto-updating the summary columns with the supplied autosum_int / autosum_float requires you to just to define the predefined callback function. After the editing is done, a new summary value is calculated.
Setting the onblur option to empty makes the 'OK'- and 'Cancel'-buttons to appear below the enterable field.
select 'dbr.editable', 'value', 'sp_DBR_budget_edit', 'inAccount=account', "options={'onblur':'', 'callback':autosum_int}"; select 'dbr.sum', 2; select account, value from budget;
In the example, we have an editable column and a column that holds the calculated value of other columns. When a user updates a column, we need to calculate a new calculated value and refresh the summary columns. In the example, we'll use myDBR built-in functions col_value_get and col_value_set which will get and set table cell value for a given cell.
The example also checks if the entered value is bigger than 100 and asks for confirmation from the user. This is done by setting its own handler for the onsubmit event.
The 'col_value_get'-function has the following format: col_value_get( obj, col )
, where obj is the reference to the cell and col is the column class in the row if another column than the object's own column is referenced.
The 'col_value_set'-function has the following format: col_value_set( obj, col, decimals, val, prefix, suffix )
, where 'obj' is the reference to the cell and col is the column class
in the row if another column than the object's column is referenced. 'val' is the value to insert to set and prefix and suffix are additional text to be attached to the cell value.
The autosum-functions have the following formats:
autosum_int( obj, column, prefix, suffix )
autosum_float( obj, column, decimals, prefix, suffix )
When the first row's B column is updated following callback will calculate the cells marked in red.
select 'dbr.javascript', 'function mycallback() { // Value of A in edited row var A = col_value_get( this, "Acol" ); // Value of B in edited cell var B = col_value_get( this ); // Set the value of 4th A+B column col_value_set( this, "ABcol", 0, A+B); // Calculate sum of edited column autosum_int(this); // Calculate sum of A+B column autosum_int(this,"ABcol"); } function mysubmit(settings, cell) { var edited_value; edited_value = Cell_data.get_value_raw( $(cell).find("input").val(), "int" ); if ( (edited_value>100) && !confirm("Do you to set the value this high ("+$(cell).find("input").val()+")?")) { cell.reset(); return false; } return true; } '; select 'dbr.sum', 'A', 'B', 'AB'; select 'dbr.editable', 'B', 'sp_DBR_edit_B', 'inKey=[ID]', "options={'callback':mycallback,'onsubmit':mysubmit}"; select 'dbr.colclass', 'A', 'Acol'; select 'dbr.colclass', 'A+B', 'ABcol'; select ID, A, B, A+B as 'A+B' from mytable;
dbr.selectable converts ID columns of a result set to checkboxes, which can be used to mark the rows to be processed. The selected row IDs can be used in a subsequent linked report (usually with a combination of dbr.button). The result set for the selectable will be marked with the dbr.resultclass. The selected row IDs can later be referenced in the linked report using 'selectable.cssclass'-notation where the cssclass is the CSS class added with dbr.resultclass. Note that the selected IDs are user given values, so you should not trust the values and should validate the values in the linked report if needed.
Convert an integer ID column to checkboxes and mark the result with dbr.resultclass. The list of IDs is passed as a parameter to the report attached to a button via the selectable.resultclass syntax:
/* Convert the ID column to checkboxes */ select 'dbr.selectable', 'ID'; /* Give the result set a class which will be used later as parameter reference to the linked report */ select 'dbr.resultclass', 'films'; select f.film_id as 'ID', f.title as 'Title', c.name as 'Category' from films f join category c on c.category_id = f.category_id; select 'dbr.button', 'Set category'; select 'dbr.report', 'sp_DBR_film_category_set', 'dynamic_div[]', 'in_ids=selectable.films'; select 'dummy';
The IDs are passed in as a comma-separated string ("1,2,3,4"). At the end, we'll refresh the main report with dbr.refresh
create procedure sp_DBR_film_category_set( in_ids text, in_category_id int ) begin update films set category_id = in_category_id where find_in_set(films.film_id, in_ids); select 'dbr.refresh'; end
To refresh the list with selected values, one has multiple choices:
To use direct JavaScript to change the visible values
create procedure sp_DBR_film_category_set( in_ids text, in_category_id int ) begin declare v_category_name varchar(50); update films set category_id = in_category_id where find_in_set(films.film_id, in_ids); -- Get the name select name into v_category_name from category where category_id = in_category_id; -- Escape the v_category_name if it can contain backslashes or '-characters, if not, you can skip this set v_category_name = replace(v_category_name, "\\", "\\\\"); set v_category_name = replace(v_category_name, "'", "\\'"); select 'dbr.javascript', concat("selected.set_col_value('films', '",in_ids,"', 'category', '",v_category_name,"')"); end
The selected.set_col_value is a helper function which has a format of
selected.set_col_value(table_class, comma_separated_id_string, column_class, visible_value);
.
It changes the value for column_class-columm for all rows whose ID matches with the supplied IDs and the table is identified by the table_class.
By adding 'Rich text'-extension to the report from report parameters, one can use 'richtext' editing type which will bring up a full-fledged editor. The editor can be invoked from direct HTML code or from HTML code shown with dbr.html:.
myDBR uses TinyMCE for the Rich Text editor. TinyMCE options can be passed in as the 'richtext'-option.
To show the HTML code as rendered HTML, we'll use 'dbr.html:' in the query. To enable rich text editing, we'll use 'type=richtext' and use option "'html':1" to indicate that we'll be converting the HTML into an editable format.
select 'dbr.editable', 'rich_text', 'sp_DBR_edit_rich_text', 'in_id=id', 'type=richtext', "options={'html':1,'richtext':{'width': 600, 'height': 350}}"; select 'dbr.hidecolumn', 'id'; select 'dbr.colstyle', 'rich_text', '[white-space: pre;]'; select id, concat('dbr.html:', rich_text) as rich_text from rich_text;
One can also use direct HTML code to invoke the editor. In this case, the code is shown as is in the query and no html-option is passed.
select 'dbr.colstyle', 'rich_text', '[white-space:pre]'; select 'dbr.hidecolumn', 'id'; select 'dbr.editable', 'rich_text', 'sp_DBR_edit_rich_text', 'in_id=id', 'type=richtext', "options={'richtext':{'width': 600, 'height': 350}}"; select id, rich_text from rich_text;
By adding 'Code editor'-extension to the report from report parameters, one can use code editing with 'html', 'php', 'javascript', 'css' and 'sql' types.
select 'dbr.hidecolumn', 'id'; select 'dbr.editable', 'code', 'sp_DBR_edit_code', 'inA=id', 'type=php'; select 'dbr.colstyle', 'code', '[white-space:pre;font-family:Courier]'; select id, code from code_editing;
With dbr.upload, one can offer file upload capability to the server.
select 'dbr.upload.options', 'noreplace', 1; select 'dbr.upload.options', 'accepted_files', 'jpg,png'; select 'dbr.upload.options', 'maxheight', 300; select 'dbr.upload.options', 'maxwidth', 200; select 'dbr.upload.options', 'quality', 0.6; select 'dbr.upload', '/var/www/upload', 'sp_DBR_upload_file', v_client_id; select 'dummy';
The upload procedure takes three fixed parameters path, filename, and file size. One can also pass additional parameters to the procedure. The procedure will return the text that is shown to the user after a successful upload.
create procedure sp_DBR_upload_file( in_path varchar(255), in_file varchar(255), in_size int, in_id int, inLogin varchar(128) ) begin insert into uploads values (inLogin, in_path, in_file, in_size, in_id, now()); select concat('File ', in_file, ' uploaded (',format_bytes(in_size),')'); end