6.11.3. Data manipulation

The data manipulation tool, a long awaited feature called introduced still in pgModeler 0.8.0, is an extension to the SQL tool. As the name suggests, this feature enables the CRUD operations for tables and the Read for views. The application implements this feature in separated dialogs as the one displayed in the below, permitting the user to handle different tables or views at once.

The data manipulation dialog works as a batch of operations to be applied to a table, which means, you can insert, update and delete rows in the same transaction. The rows in the grid are colored according to the operations to be done. As seen in the image below, green rows mean new data to be added to the table. Yellow rows indicate changes to be applied and the columns with bold text indicate where the modification will occur. Red rows point data to be deleted from table.

There are additional features in this dialog that help in data filtering for instance. The first one is the Filter expression that works like the WHERE clause in a SELECT command. In that field the user must inform a conditional expression to be used as filter using SQL language without the keyword WHERE. The second auxiliary field is the Order & Limit that controls the data sorting by columns (ascending or descending) and the limit of retrieved rows.

Despite the usefulness of data manipulation dialog there are some limitations that should be informed. The first one is that binary columns or bytea can't be directly handled and they will be display the special value [binary data] in the grid. This is done to avoid overflows if the value of a column is too large for the cell to store it.

Another limitation is that handle tables without primary keys will cause delete and update operations to use all columns as primary keys. This because pgModeler need to know which rows will be deleted or modified by using their ids as configured in the primary keys. In the absence of a primary key the mentioned operations will adopt all columns as the primary key in order to minimize the changes done to the table since more data than the expected by the user can be modified in the process. Below all controls and actions of the data manipulation dialog are fully explained.

Action/Control Description
Schema This combo box lists all schemas. Changing the selected schema will automatically fill the Table combo box with all available tables in that.
Table This combo lists all tables and views for the selected schema.
Hide views Toggles the display of views in the table listing.
Refresh By clicking the button or hitting F5 the grid will be refreshed using all additional filtering settings. Any work in progress in the grid like new data or rows marked to be deleted or updated will be discarded causing no data to be changed in the table.
Save Applies all changes done in the grid to the table, in practice, DML commands are generated for each row and executed in the database. To perform this action click the button or use the shortcut Ctrl+S.
Copy Triggered by the icon this action enables the user to copy the selection in the grid to the clipboard in form of a CSV buffer by using the button (Ctrl+C) or as a plain text buffer (Ctrl+Shift+C).
Paste Pastes the contents of the clipboard into the grid using the action or the shortcut Ctrl+V.
Export Exports the entire result set in the grid to a CSV file. This can be done by activating the button or hitting Ctrl+X.
Browse Clicking the icon
Undo Reverts any changes made in the grid by clicking or hitting Ctrl+Z
Add Click or hit Ins to add a new empty row in the grid.
Delete Marks the selected rows to be deleted when the action or the shortcut Del is triggered.
Edit The action expands in a menu with two items which enables the user to browse rows on other tables which reference and are referenced by the selected row in the current data manipulation dialog. This same menu can be triggered by right clicking any item in the grid. This feature is detailed in the subsection below.
Duplicate Triggered by the action or by the shortcut Ctrl+D it duplicates the selected rows in the grid. In order to enable the row duplication make sure to select all columns of the desired row.
Truncate The action expands in a menu which contains the actions Truncate and Truncate cascade. Both menu actions wipes out all data of the current managed table but the cascade version also wipes out the data of all tables which somehow references the current one. Note that truncation is an irreversible action so use the feature with extreme caution.
Load CSV The action allows the user to feed the current managed table with external data coming from a CSV file. The import process can be fine tuned by specifying the column separator by usingSeparator field, the text delimiter character by adjusting the field Text delimiter and indicate if the first row of the imported file contains the name of the columns using the option Columns in the first row. When this last option is checked pgModeler will match the values of the columns names and fill only the ones present in the current table.
Filter The action toggles the filter section.

6.11.3.1. Browsing references & referrers

This feature helps those who have to browse data in several tables that are related somehow. In releases prior to 0.9.0-beta1 the proper way to know which row in a table A is referenced by a row in table B is by opening the data manipulation dialog and use the set of filters which can be a bit annoying and time consuming. So in order to minimize the task to find those rows this new feature does some tricks in the backstages in order to perform the automatic filtering that the user needed to do manually.

Technically, when browsing a table in the data manipulation dialog pgModeler discovers and stores information about the primary and foreign keys of the current table. From that data we are able to see all the rows in other tables that somehow reference the selected row in the currently managed table. The inverse way is possible too, discover which rows in other tables that are referenced by the browsed table from the selected row. It seems a bit confusing but let's detail it based upon the image below.

In the image we have a simple model which relates cars and engines. On the bottom left we have a set of cars which references a set of engine models as exposed in the bottom right. Suppose that when browsing the cars table you need to know the engine which the Model A references, for instance. The only information we have is that Model A references the engine which id is 1. In order to show all details about the engine 1 we would need to open a new instance of the data manipulation dialog, browse the table public.engines and setup a filter id = 1 so we can expose all details about that engine. This is the way things were done before the introduction of the row navigation feature.

Being said that, using the row navigation feature, you would simple select the row related to Model A, right click it to show the context menu and follow Browse tables > Referenced tables > public.engine (engines_fk). Note that the item named after the referenced table public.engines contains the name of the foreign key in public.cars which denotes the relationship between them, in that case engines_fk.

Once clicked the mentioned menu item a new instance of the data manipulation dialog is open and automatically filters the results to show only the engine which code is that one referenced by the Model A. See how shorter was the path to achieve the same result compared to classic way. This is a small change that makes a lot of difference when handling several tables that are linked.

You can still play around with the new instance of the dialog and try the inverse. Right click the row related to engine 1 and select Browse tables > Referrer tables > public.cars (engines_fk) to see all rows in public.cars that reference the selected row in public.engines.


Sep 5, 2018 at 09:30