Working with Database Objects

Databases are composed of various smaller units usually called database objects. In the relational databases, tables are the basic objects. But other types of objects can exist depending on the database type. For example:

  • dBase database consist of data files with .dbf extension, index files, and files with BLOB data;
  • text file databases consist of data files (text and CSV) and schema files, which describe the field structure of data files and store some data manipulation settings;
  • Microsoft Access databases consist of tables, views, indexes and some other objects crucial for the application functioning;
  • PostgreSQL databases consist of tables, views, indexes, stored procedures, triggers, and many other types of objects.

and so on.

Currently, Exportizer has interface (more or less completed) to work with database tables and schemata for text tables. For other database objects, which are potentially available in the database, you can use SQL (Pro and Enterprise only). Working with SQL may require some special knowledge, but in many cases using it is pretty simple; anyway, if necessary, read the documentation for your database. Below, there are some descriptions and examples.

Tables

After opening a database in Exportizer, you see the list of the database tables in the left part of the main window. Depending on the type of the connected database, you can find the following object there:

  • Regular tables;
  • Views;
  • External tables for Oracle databases;
  • Foreign tables for PostgreSQL databases;
  • Sequences. Some databases (e.g. PostgreSQL) allow to query database sequences like tables, that's why they can be present in the table list. For PostgreSQL, configure this using environment options.

For most database types, these different kinds of tables are shown in the table list with different icons, so you can easily distinguish between them.

When you select a table in the list, you see the table data in a separate tab in the right part of the main window. Exportizer provides rich functionality to work with table data like:

  • browsing
  • editing
  • sorting
  • filtering
  • copying to clipboard
  • exporting
  • printing.

Creating Tables

In Exportizer, you can create tables by three ways:

  • Use a special interface.
  • Export data to a new table in that database utilizing Database or other appropriate export formats. So, you are using an existing table as a base. For example:
    • If you export the source dataset to dBase format, the result will be a dBase file/table in a target folder.
    • If you export the source dataset to CSV format, the result will be a CSV file/table in a target folder.
    • If you export the source dataset to a SQLite database, the result will be a SQLite table in that database.
  • Create a table from scratch using SQL (Pro and Enterprise only). For example: 
    CREATE TABLE ORDERS (
  ID INTEGER NOT NULL,
  ORDER_DATE DATE NOT NULL,
  ORDER_SUM MONEY NOT NULL,
  ORDER_VAT MONEY,
  PRIMARY KEY (ID)
);

    Note: Databases of different types have different CREATE TABLE syntax. Read your database documentation to learn more.

Deleting Tables

To delete (drop) a table in Exportizer, select it the list of tables and click Delete button above the list.

To delete (drop) a group of tables, click Select Tables button or the corresponding item from the context menu of the table list. Then select needed tables in the list and click Delete button.

It is also possible to use SQL to delete tables (Pro and Enterprise only). For example:

DROP TABLE ORDERS;

Editing Table Structure

To modify structure of a table in Exportizer, you need to use SQL (Pro and Enterprise only). Examples:

ALTER TABLE ORDERS
  DROP COLUMN ORDER_VAT;
ALTER TABLE ORDERS
  ADD COLUMN TS DATE NOT NULL;

Note: Databases of different types have different ALTER TABLE syntax with a lot of options. Read documentation for your database to learn more.

Other Table Functions

You can find useful table functions in Tools menu or in the context menu of the table list:

Table list context menu
Function Description
Generate SQL Generate SQL commands for selected tables, one command per each selected table (Pro and Enterprise):
  • Query Data. Generate SELECT queries. Every query will list all columns from the table.
  • SELECT *. Generate short SELECT queries (with an asterisk instead of the list of columns).
  • DDL. Generate CREATE TABLE commands.
  • Row Count. Generate a command to show count of records in each selected table.
  • GRANT SELECT. Generate GRANT SELECT commands. Only for databases which support this. You can then specify a grantee by one text replace operation using the Replace button Replace button.
  • DROP. Generate DROP TABLE commands.

Below are command templates:

  • INSERT Target. Generate targets of INSERT commands. The target here is a first clause of the INSERT command, which provides a table name and the list of columns.
  • INSERT Template. INSERT command templates.
  • UPDATE Template. UPDATE command templates.
  • DELETE Template. DELETE command templates.
Connect / Register Choose and connect another data source. Note: This will close the current connection.
Close All Close all open tabs and disconnect from the data source.
New SQL window Create a new SQL tab (Pro and Enterprise).
Explore Database Folder If open databases is a folder with dBase or CSV files, open the folder in Windows File Explorer.
Registered Databases See and manage your registered databases.
Delete Delete / drop selected tables.
Copy Copy table list to clipboard.
Refresh Refresh table list.
Select Tables Turn on/off table list multi-selection mode.
Sort by Sort table list by table names or by table types.

Schemas for Text Tables

For structured text files and CSV files, you have a possibility to create schemata describing the data structures. To edit schema files, you can you any text editor.

Table Indexes

Currently, you need to use SQL to create or delete table indexes in Exportizer. Examples:

CREATE INDEX IDX_ORDERS_DATE
  ON ORDERS (ORDER_DATE);
DROP INDEX IDX_ORDERS_DATE;

Note: Databases of different types have different CREATE INDEX syntax. Read your database documentation to learn more.

Schemas

In some relational databases of higher level like Oracle, PostgreSQL, SQL Server and so on, schema is a collection of database objects, including tables, views, indexes etc. which are combined logically. Schemas are also considered database objects.

Do not confuse schemas as collections of database objects with schemas in primitive text databases (see above).

Currently, you need to use SQL to create or delete this kind of schemas in Exportizer. Examples:

CREATE SCHEMA showcase;
DROP SCHEMA showcase;

Attention! Deleting (dropping) a schema deletes all objects inside it.

Stored Procedures

Stored procedures are pre-compiled sets of commands written in SQL or SQL-like languages and stored in a database. In different types of databases, there can be different kinds of stored procedures like functions, triggers etc.

Currently, you need to use SQL to create, modify or delete stored procedures in Exportizer. Example of creating or modifying a PostgreSQL function:

CREATE OR REPLACE FUNCTION public.is_valid_json(p_json TEXT)
 RETURNS BOOLEAN
 LANGUAGE plpgsql
 IMMUTABLE
AS $func$
BEGIN
  RETURN (p_json::JSON IS NOT NULL);
EXCEPTION
  WHEN others THEN
    RETURN FALSE;
END;
$func$;

Note: Databases of different types have different syntax for stored procedures. Read your database documentation to learn more.

Other Objects

You need to use SQL to work with other database objects in Exportizer. Whenever necessary, read the documentation for your database to learn the correct syntax and possible options.

See also