The northwind example database Professional

Xbase++ comes with a sample data model named northwind. The data model is available for the PostgreSQL server, for free dbf tables, Visual FoxPro dbc, ADS dictionary and SQLite databases. Two steps are required to load this sample database into a PostgreSQL server. First of all, an empty database must be created. As a second step, the tables need to be defined and the data needs to be loaded into the new database.

Hint: Alternatively, the Upsizing Tool can be used to upsize the free dbf tables from the northwind data model to the PostgreSQL server. The northwind.upsize configuration file is provided for just this purpose.

Creating the empty database

A database refers to the entire set of data belonging to a problem domain. Multiple databases can be handled by a single database server. By default, each PostgreSQL server installation comes with a default database which is named "postgres". In order to create an empty database using the pgAdmin administrative tool, double-click the server in the object browser, then open the context menu of the Database node as shown below.

Context menu of the Database node

Select New database in the context menu to open the New Database dialog. First, enter the name "northwind" into the Name entry field. Note that the PostgreSQL sever is case-sensitive when it comes to database, table and column names. It is considered good practice to specify all names in lowercase to make them easily readable within larger SQL statements.

Creating a new database

Second, it is important to ensure that the new database uses the proper encoding. This is specified on the Definition tab in the New Database dialog. The Encoding needs to be set to UTF-8. If another encoding is active, select UTF.8 instead. The default encoding is determined during installation of the PostgreSQL server. In the context of Xbase++ it is assumed that the server stores all data in UTF-8. This guarantees that all different types of character sets can be stored in a database and its tables without loss of information.

Selecting the correct database encoding

Clicking on the Ok button creates the database. Once created, the new database "northwind" is listed under the Databasenode in the object browser of the pgAdmin tool. Expanding the corresponding node reveals a number of child elements. In general, the following child nodes are relevant in the context of Xbase++.

The empty northwind database

Filling the database with data

The simplest way to define the data model and to add data to an empty database is by executing an SQL script which does the job. A proper SQL script for the northwind sample data model is provided in the sample collection which ships with Xbase++. The script contains all the CREATE TABLE and INSERT statements required to create the data model and to insert the data.

In order to execute the script, the northwind database first needs to be selected in pgAdmin's object browser. Afterwards, clicking the SQL button opens an SQL editor which is already connected to the database. Performing these steps should produce a screen similar to the one shown below.

The SQL editor

Next, the SQL script needs to be loaded into the SQL Editor. To do this, use the File->Open menu item to open the file dialog and browse to the user'sDocuments folder. Afterwards, find and select the file northwind.postgresql.sql which is located in the \xbase++\source\samples\data\northwind sub-directory. Once it is loaded, the script can be executed by clicking the Executebutton. The script may take several seconds to execute depending on workstation/server performance.

Executing an SQL script in the SQL editor

Hint: In the event you are unable to locate the specified folder in your Xbase++ installation, just start the Xbase++ Worbench and select Help->Desktop Samples in the menu bar. This will open the samples folder in Windows Explorer so you can copy the path to that folder from the Explorer's address bar. The data\northwind folder is a sub-directory of the samples folder.

Executing your first SQL query

Now that test data is available in the northwind database, queries can be run against it using the SQL Editor. First, clear the editor by clicking on the eraser icon in the tool bar of the SQL Editor.

The SQL statement shown below runs a query which returns all employees from northwind's employees table who live in the USA. To execute this query, enter its code into the editor and press F5.

SELECT firstname,lastname,city,country FROM employees WHERE country='USA' 

Browsing query results in the SQL editor

Summary:

In this chapter, a sample database was created and loaded with a data model and data using the pgAdmin tool. In addition, an SQL SELECT query was performed on the database. The next chapter shows how to connect to the database and how to perform SQL operations using Xbase++ and the PostgreSQL DatabaseEngine.