All data are displayed here. Displayed data can be:
Results of queries launched by the graphical query builder .
Results of statements launched in the SQL Editor.
Content of tables and views listed in the Schema Browser. Click on a table name to fetch its data.
Edited rows are displayed with a blue-green background.
Changes in Data Viewer are cached locally and have to be explicitly
promoted into the database. So you
have to
click the
button to promote them into the database file or the
button to discard them.
Important
Data changes are available only for tables (invoked in Schema Browser). There is no way to edit the data returned by an SQL statement yet, but for simple queries the Find Dialog may help you.
-
Find...
-
This button is enabled only when viewing a table. It opens a dialog enabling you to search in the table. for details see the Find Dialog.
-
New Row
-
This button is enabled only when viewing a table. It creates a new row and displays it at the end of the table. If preference Prefill new rows is not set, all fields are set to NULL. If this preference is set, default values are prefilled, and fields with no default are still set to NULL.
An INTEGER PRIMARY KEY AUTOINCREMENT field is prefilled with a value one higher than the highest value which has ever been used in the table including new rows which have not yet been committed. The prefilled value, or any modified value that you insert, is used when the new row is committed. If row 9223372036854775807 has ever existed in the table, this does not produce the same result as directly inserting a row with an sqlite statement, which would fail with error SQLITE_FULL.
An INTEGER PRIMARY KEY DESC field is not prefilled by sqliteman.
An ordinary INTEGER PRIMARY KEY (not AUTOINCREMENT or DESC) field is prefilled with a value one higher than the highest value currently used in the table again including new rows which have not yet been committed. The prefilled value, or any modified value that you insert, is used when the new row is committed. If row 9223372036854775807 currently exists in the table, this does not produce the same result as directly inserting a row with an sqlite statement, which would either insert a random previously unused value or fail with error SQLITE_FULL.
If a field has a default of CURRENT_TIMESTAMP, CURRENT_DATE, or CURRENT_TIME, it will be prefilled with the date and/or time when the record is created, but if the field is not modified it will be overwritten when changes are committed with the date and/or time at that moment.
If the default for a field is an sqlite expression, it will be prefilled with the result of evaluating the expression: since it is possible (see appendix B) to constuct an sqlite constant expression with a non-constant value using sqlite built-in functions, the actual value when the changes are committed may be different.
If the whole table has not been read yet, inserting a row causes the remainder of the table to be read into memory.
Commit action is required to make this permanent.
-
Duplicate Row
-
This button is enabled only when viewing a table and a current row is selected. It creates a copy of the current row and displays it at the end of the table (this can be useful when creating rows which differ only in a few fields).
If the whole table has not been read yet, copying a row causes the remainder of the table to be read into memory.
If the table contains a field which is an INTEGER PRIMARY KEY (not DECR) or an INTEGER PRIMARY KEY AUTOINCREMENT, in order to avoid a constraint violation that field is prefilled as for a new row instead of being copied.
Commit action is required to make this permanent.
Important
The Qt SQL library gets confused if the database contains multiple rows with the same data fields: if there is no primary key make sure to modify the copy before committing. -
Remove Row
-
This button is enabled only when viewing a table and a current row is selected. It deletes the selected row from the table. Commit action is required to make this permanent.
-
Delete Multiple
-
This button replaces the Remove Row button when you have just executed a query created by the Query Builder. It deletes all the records found by the query from the table against which the query was run. It will ask you if you really want to do this first as this can delete a lot of records. This is an immediate action and does not require Commit action to update the database. Note this button is not available after executing a query from the SQL Editor widget, since this can contain a more complex SELECT which cannot be simply modified into a DELETE statement.
-
Commit
-
This button is enabled only when changes have been made in the DataViewer. It promotes the changes into the database.
Sqlite does not define where a new row is inserted into the table, or the order in which table rows are displayed. Current versions display rows in the order of the Primary Key, which will be the rowid except in the case of a WITHOUT ROWID table. This means that when committing a new or copied row its apparent position in the table may change.
-
Rollback
-
This button is enabled only when changes have been made in the DataViewer. It rejects the changes and reloads the Data Viewer from the database.
-
Blob Preview
-
Enable or disable BLOB preview widget. The blob preview widget can only be enabled when a field containing a blob is selected, but it can be disabled whenever it is enabled. If the blob preview is enabled, it will appear whenever a field containing a blob is selected and it will attempt to display the blob as an image.
-
Data Export
-
Open the Data Export dialog. You can export currently selected data in various formats. See Data Export documentation.
-
Table Snapshot
-
Duplicate current result table into a new read only window. You can compare data versions etc. with it.
-
Go to line number
-
This button pops up a dialog to select a row number. It will select the first field of that row and scroll the Data Viewer to make it visible. It ignores any attempt to go to a row which has been deleted.
- Full View
-
Display data as a table. This is the most common view on SQL results.
Clicking on the Item View tab will display the Item View for the currently selected row (the tab is disabled when there is no current row). Double-clicking on a row number in the Full View will open the Item View for that row.
The following functions are available from the context menu invoked by right-clicking in a field, or with the keyboard shortcuts shown:-
- Copy Whole Ctrl+W
- Copies the whole field to the Clipboard.
- Paste Over Ctrl+Alt+V
- If there is text in the Clipboard, replace the whole field with that text: otherwise do nothing.
-
Insert NULL
Ctrl+Alt+N
- Replace the contents of the field with a Null: SQL does not treat a Null field the same as an empty one.
- Open In-line Editor Ctrl+Space
- Open the In-line Editor in the field. This can also be invoked by double-clicking on the field.
-
Open Multiline Editor
Ctrl+Alt+E
- Bring up the Multiline Editor to edit the contents of the field in a pop-up window.
- Item View
-
The Item View displays only one row at a time, but with more space to edit each field. The navigation buttons at the right move to the first, previous, next, and last row respectively, and are disabled if you are already at that row. Below the navigation buttons there are two small buttons to overwrite the selected field with a NULL and to open the Multiline Editor: these are only visible if the table is editable. You can also use the go to line number button.
This view is now fully editable and synchronised with the Full View. If you are using NULL higlighting, the NULL alias will disappear if you click on a NULL field in an editable table, so that you can type something in there.
The following functions are available from the context menu invoked by right-clicking in a field, or with the keyboard shortcuts shown:-
- Copy Ctrl+C
- Copies the selected text to the Clipboard.
- Copy Whole Ctrl+W
- Copies the whole field to the Clipboard.
- Cut Ctrl+X
- Copies the selected text to the Clipboard and deletes it from the field.
- Paste Ctrl+V
- Pastes text from the Clipboard in place of the selection or at the cursor position if nothing is selected.
- Paste Over Ctrl+Alt+V
- If there is text in the Clipboard, replace the whole field with that text: otherwise do nothing.
-
Insert NULL
Ctrl+Alt+N
- Replace the contents of the field with a Null: SQL does not treat a Null field the same as an empty one.
-
Open Multiline Editor
Ctrl+Alt+E
- Bring up the Multiline Editor to edit the contents of the field in a pop-up window.
- Script Output
- Results from the menu action or from running multiple commands as a script in the SQL Editor are listed in this tab. It is automatically selected if you select a menu action or run a script which does not generate a table.
-
In-line Editor
-
Simple strings can be edited in this widget. There are also two buttons:-
-
Set NULL
Ctrl+Alt+N
- Set the cell to the real NULL value.
-
Multiline Editor
Ctrl+Alt+E
- Open multiline editor
Multiline Editor
This dialog is designed because the In-line Editor cannot handle advanced features comfortably. It allows you to edit multi line texts, BLOBs and the other special data formats.
When you accept changes in this dialog with the button, only the result of the active tab will be promoted to the Data Viewer. If the option is checked, only NULL is inserted. As usual the commit button then has to be used to promote the changes into the database.
- Text tab
-
Full plain text editor. There is no rich text support.
- Blob tab
-
The content of the chosen file will be transferred into the cell as is. No data transformations are performed. The original value of the cell can be saved into a local file with the button.
- Date to String tab
-
A helper wizard for storing date and time in string values.
Important
Consult the Sqlite manual for date and time issues.The Date item in this form can be set with a drop-down calendar by clicking on the arrow to the right of the date.
The String Format should be set with a date time transform mask. Consult the Qt4 documentation for an explanation of its codes.
Sqliteman can use progressive (step-by-step) fetching of data in the result set. This means that you don't need to wait until all data are transferred from the database file to the result table.
When you call a select statement from table with e.g. thousands of rows only the first part of rows is physically fetched. The remaining rows are available on demand - scrolling down in the result table will perform next fetch.
A message in the error log widget will inform you:
- More rows can be fetched. Scroll the result set for more rows and/or read the documentation.
Alternatively you can set a preference to read all of a table when opening it: this can be more convenient on a fast computer with plenty of memory.
If you select a column by clicking on the column name at the top, and then type characters, sqliteman will do an incremental search on the data in the selected column. The view will be scrolled so that the first matching row is visible, at the top of the window unless there are not enough rows below it to fill the window. This works best with a sorted column, but sqliteman will do the search anyway.