Working with Database Objects - Exportizer Documentation

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, it is possible to use SQL (Exportizer Pro and Exportizer Enterprise only). Working with SQL may require some special knowledge, but in many cases using it is pretty simple; anyways, if necessary, read the documentation for your database. Below, there are some descriptions and examples.

Tables

After opening a database in Exportizer, it shows the list of the database tables in the left part of the main window. When you select a table there, the table data will be shown 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

There are currently two ways of creating tables in Exportizer

By exporting 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 your database documentation to learn more.

Schemas for Text Tables

For structured text files and CSV files, it is possible 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 with. 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