myDRR allows for you to edit your data directly in myDBR. myDBR offers different kind of data editing:
The editing is done by linking another report (an editing report) to the original report to which myDBR passes the editable data. Access rights to the editable report define who of the users sees the report as editable and who as a normal non-editable report. If a user has access rights to the editable report, the fields are editable, otherwise not.
An usual use case is that user is shown a list of data and user is offered an option to add new row to the list. Additionally user is able to edit the data in the list, either directly or row by row. This is an example of this.
What we will do is following:
The list is generated using a query:
select 'dbr.hidecolumns', 'film_id'; select f.title as 'Title', f.description as 'Description', f.release_year as 'Year', c.name as 'Category', d.Name as 'Director', 'Edit', 'Del', f.film_id, f.category_id, f.director_id from mydata.film f join mydata.category c on c.category_id = f.category_id join mydata.directors d on f.director_id = d.director_id order by f.film_id;
We'll add a button in bottom of the list which will bring up a dialog with all the required data fields. The linked report will be attached using dbr.report and it will insert the user data into the table and return a refresh command to refresh the original list.
select 'dbr.button', 'New film'; select 'dbr.report', 'sp_DBR_Film_new', 'newfilm'; select 'dummy result set for the button';
Report code for the sp_DBR_Film_new:
CREATE PROCEDURE sp_DBR_Film_new( inTitle varchar(69), inDescription text, inRelease_year int, inCategory tinyint, inDirector_name varchar(80), inDirector int ) begin insert into mydata.film ( title, description, release_year, category_id, director_id ) values (inTitle, inDescription, inRelease_year, inCategory, inDirector ); select 'dbr.refresh'; end
As the sp_DBR_Film_new is just a normal report we can attach functionality to the parameters: popup for the category and make the director name an autocomplete field.
The dbr.refresh command at the end of the embedded linked report is returned to the original report causing the refresh and showing the new item.
Editing a row works similarily to new row creation. Only difference being that we will pass the ID for the edited row to the linked report and instead of doing an insert, we will do an update.
First we will attach the edit report to the 'Edit' column by adding the dbr.report-command before the select-clause which shows the data to the user.
select 'dbr.report', 'sp_DBR_Film_upd', '[Edit]', 'popup', 'inFilm_id=film_id', 'inTitle<=Title', 'inDescription<=Description', 'inRelease_year<=Year', 'inCategory_id<=category_id', 'inDirector<=Director', 'inDirector_id<=director_id'; select f.title as 'Title', f.description as 'Description', f.release_year as 'Year', c.name as 'Category', d.Name as 'Director', 'Edit', 'Del', f.film_id, f.category_id, f.director_id from mydata.film f join mydata.category c on c.category_id = f.category_id join mydata.directors d on f.director_id = d.director_id order by f.film_id;
The linked report to be called is sp_DBR_Film_upd and it is attached to the 'Edit'-column. As we want the user to stay in the same page, we will put the linked report in a popup window. The edited row's ID (film_id) is passed in as a non-editable field ("=") whereas all other fields will be user editable ("<=") in the parameter popup.
When user clicks on the 'Edit' link, the values are passed into the popup and user can change them:
The code for the sp_DBR_Film_upd looks like this:
CREATE PROCEDURE sp_DBR_Film_upd( inFilm_id int, inTitle varchar(80), inDescription text, inRelease_year int, inCategory_id int, inDirector varchar(80), inDirector_id int ) BEGIN update mydata.film set title = inTitle, description = inDescription, release_year=inRelease_year, category_id=inCategory_id, director_id=inDirector_id where film_id = inFilm_id; select 'dbr.refresh'; END
Having both director name and id as parameter is due to the fact that the dialog used autocomplete value to fetch the id when user enters director's name.
As an example we'll create a basic delete functionality where the user selects the row to be deleted and myDBR will ask for confirmation about the delete, deletes the row from database and when finished removes the deleted row from the report. All will be done using Ajax without the need to refresh the page.
We will create a report which will delete a given row from the database
CREATE PROCEDURE sp_DBR_film_delete( in_film_id int ) BEGIN delete from sakila.films where film_id = in_film_id; END;
sp_DBR_film_delete-report is called. If it returns false, the report will not be called.
As a result we'll get a confirmation dialog asking user permission to delete the row:
$(mydbr_selected_cell) will make the table cell a jQuery object, the
parent().remove(), will delete it's parent row.
In-place editing allows you to mark selected data so it will be editable directly in the report. You can specify which column(s) are editable and define the actual editing report for it.
In the example, we'll define 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 updatable with the procedure
Parameters for the command in the example are:
You do not need to pass the edited column value as it is automatically added as a last parameter to editing report procedure
CREATE PROCEDURE sp_DBR_InPlaceEditing BEGIN select 'dbr.editable', 'id', '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 as '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 the parameters specified in
dbr.editable-call and an additional parameter for the value.
The last parameter for the editing procedure is automatically populated by the value of 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