Jsp, Tomcat, J2Ee, Linux Web Hosting, Hibernate Programming Blog

CHAPTER 28 From Databases to Datatypes Taking time to properly design your project s data model is key to its success. Neglecting to do so can have dire consequences not only on storage requirements, but also on application performance, maintainability, and data integrity. In this chapter, you ll become better acquainted with the many facets of the hierarchy of objects within PostgreSQL. By its conclusion, you will be familiar with the following topics: The difference between the various levels of the PostgreSQL hierarchy, including clusters, databases, schemas, and tables. The purpose and range of PostgreSQL s supported datatypes. To facilitate reference, these datatypes are broken into four categories: date and time, numeric, textual, and Boolean. PostgreSQL s table attributes, which serve to further modify the behavior of tables and their columns. How to use advanced concepts, such as constraints and domains, to help further enforce data integrity. Working with Databases While most people think of a database as a single entity, the truth is that a single installation of PostgreSQL can handle many unique databases at the same time. This collection of databases is technically referred to as a cluster. In this section, we look at how to manipulate databases within a cluster. Default Databases By default, a PostgreSQL cluster comes with two template databases, template0 and template1. These databases contain all of the basic information that is needed to create new databases on the system. When you initially connect to a new installation of PostgreSQL, you ll want to connect to the template1 database and use that to create a new database. If there are schema objects or extensions that you need to load into PostgreSQL that you want all future databases to have access to, you can load them into the template1 database. The template0 database is mainly provided as a backup in case you manage to modify your template1 database in a manner that cannot be corrected.
In case you need quality webspace to host and run your web applications, try our personal web hosting services.

CHAPTER 27 THE MANY POSTGRESQL CLIENTS Figure 27-3. Viewing the contents of corporate.hr.employee Availability Navicat is a product of PremiumSoft CyberTech Ltd. and is available for download at http:// www.navicat.com/. Unlike the previously discussed solutions, Navicat is not free, and at the time of writing costs $129, $79, and $75 for the enterprise, standard, and educational versions, respectively. You can download a fully functional 30-day evaluation version. Binary packages are available for Microsoft Windows, Mac OS X, and Linux platforms. Summary You need to have a capable utility at your disposal to effectively manage your PostgreSQL server. Regardless of whether your particular situation or preference calls for a command-line or graphical interface, this chapter demonstrated that you have a wealth of options at your disposal. The next chapter discusses how PostgreSQL organizes its data hierarchies, introducing the concepts of clusters, databases, schemas, and tables. You ll also learn about the many datatypes PostgreSQL supports for representing a wide variety of data, how table attributes affect the way tables operate, and how to enforce data integrity.
Searching for affordable and reliable webhost to host and run your web applications? Go to our java web server services and you will be pleased.

622 CHAPTER 27 THE MANY POSTGRESQL CLIENTS so download the version that s most convenient to your platform and uncompress it to an appropriate location within the Web server document root. Next, open the conf/config.inc.php-dist file, located in this newly uncompressed directory (which at the time of writing is titled phpPgAdmin), and save it as config.inc.php to the same directory. Open a Web browser and proceed to the phpPgAdmin home directory for example, http://www.example.com/phpPgAdmin/index.php. You will be presented with a welcome screen, which prompts for a username, password, choice of language, and a target server (provided more than one was defined within the config.inc.php file; open this file for more details). This interface prompts you for a username and password, referring to one of the accounts created within the PostgreSQL server. For security reasons, you cannot log in without a password, nor with the usernames administrator, pgsql, postgresql, or root, as this presumes you re attempting to log in using the superuser account and therefore could be transmitting the password in an unencrypted format. Because the config.inc.php file can store information for any number of PostgreSQL servers via the $conf[’servers’]configuration array, you ll be able to choose which server to connect to using the Server drop-down list box. You can also change the interface s language. At the time of writing, phpPgAdmin supports 26 different languages, including English, Spanish, Italian, and Romanian, to name a few. If you ve already gone ahead and tried to log in, depending upon how your PostgreSQL installation is configured, you might have been surprised to learn that you are allowed in even if you entered an incorrect or blank password. This is not a flaw in phpPgAdmin, but rather is a byproduct of PostgreSQL s default configuration of using trust-based authentication! See Chapter 29 for more information about how to modify this feature. Navicat Navicat is a commercial PostgreSQL database administration client application that presents a host of user-friendly tools through a rather slick interface. Under active development for several years, Navicat offers users a feature-rich and stable solution for managing all aspects of the database server. Navicat offers a number of compelling features: An interface that provides easy access to 10 different management features, including backups, connections, data synchronization, reporting, scheduled tasks, stored procedures, structure synchronization, tables, users, and views. Comprehensive user management features, including a unique tree-based privilege administration interface that allows you to quickly add and delete database, table, and column rights. A mature, full-featured interface for creating and managing views. Most tools offer a means for managing the database by manually entering the command, as one might via the psql client, and a wizard for accomplishing the same via a pointand- click interface. Figure 27-3 depicts Navicat s data-viewing interface.
Looking for affordable and reliable webhost to host and run your business application? Then look no more and go to servlet web hosting services.

CHAPTER 27 THE MANY POSTGRESQL CLIENTS users, their concern applies solely to usage; in this case you re free to use pgAdmin III for both personal and commercial uses free of charge. If you d like to use pgAdmin III on a Unix-based platform, you first need to download it from the pgAdmin Web site (http://www.pgadmin.org/) or from the appropriate directory within the PostgreSQL FTP server (http://www.postgresql.org/ftp/). Offering binaries for Fedora Core 4, FreeBSD, Mandriva Linux, OS X, and Slackware, in addition to the source code, you re guaranteed to be able to use pgAdmin III regardless of platform. If you re using Windows, pgAdmin III is bundled and installed along with the PostgreSQL server download; therefore, no special installation steps are necessary for this platform. phpPgAdmin Managing your database using a Web-based administration interface can be very useful because it not only enables you to log in from any computer connected to the Internet, but also enables you to easily secure the connection using SSL. Additionally, not all hosting providers allow users to log in to a command-line interface, nor connect remotely through any but a select few, well-defined ports, negating the possibility that a client-side application could be easily used. For all of these reasons and more, you might consider installing a Web-based PostgreSQL manager. While there are several such products, the most prominent is phpPgAdmin, an open source, Web-based PostgreSQL administration application written completely in PHP. Modeled after the extremely popular phpMyAdmin (http://www.phpmyadmin.net/) application (used to manage the MySQL database), phpPgAdmin has been in active development since 2002, and is presently collaboratively developed by a team of seven. It supports all of the features one would expect of such an application, including the ability to manage users and databases, generate reports and view server statistics, import and export data, and much more. For instance, Figure 27-2 depicts the interface you ll encounter when viewing the schemas found within the example corporate database. Figure 27-2. Viewing the corporate database s schemas Note phpPgAdmin requires PHP 4.1 or greater, and supports all versions of PostgreSQL 7.0 and greater. Availability phpPgAdmin is freely available for download and use under the GNU GPL license. To install phpPgAdmin, proceed to the phpPgAdmin Web site (http://phppgadmin.sourceforge.net/) and download the latest stable version. It is compressed using three different formats, bz2, gz, and zip,
Note: In case you are looking for affordable and reliable webhost to host and run your j2ee application check Vision J2ee Web Hosting services.

620 CHAPTER 27 THE MANY POSTGRESQL CLIENTS GUI-based Clients Although a command-line-based client such as psql offers an amazing degree of efficiency, its practical use comes at the cost of having to memorize a great number of often-complex commands. The memorization process not only is tedious, but can also require a great deal of typing (although using the tab-completion feature can greatly reduce that). To make commonplace database administration tasks more tolerable, both the PostgreSQL developers and thirdparty vendors have long offered GUI-based solutions. This section introduces several of the most popular products. pgAdmin III pgAdmin III is a powerful, client-based administration utility that is capable of managing nearly every aspect of a PostgreSQL server, including the various PostgreSQL configuration files, data and data structures, users, and groups. Figure 27-1 shows the interface you might encounter when reviewing the corporate database s schemas. Figure 27-1. Viewing the corporate database s internal table schema Availability Licensed under the open source Artistic license, pgAdmin III is freely available for download, use, distribution, and modification in accordance with the Artistic license s terms. For most
In case you need affordable webhost to host your website, our recommendation is ecommerce web host services.

CHAPTER 27 THE MANY POSTGRESQL CLIENTS multiple database servers simultaneously. However, you can avoid any such confusion by modifying the psql prompt to automatically display various items of information. For example, if you d like your prompt to include the name of the server host, the username you re logged in as, and the name of the current database, set the PROMPT1 variable, like so: corporate=> set PROMPT1 ‘%n@%m::%/> ‘ Once set, the prompt contains the username, server hostname, and presently selected database, like this example: corporate@apress::test> Two other prompt variables exist, namely PROMPT2and PROMPT3. PROMPT2 stores the prompt for subsequent lines of a multiline statement. PROMPT3 represents the prompt used while entering data passed to the COPY command. All three variables use the same substitution sequences to determine what the rendered prompt will look like. Many of the most common sequences are presented in Table 27-3. Table 27-3. Common Prompt Substitution Sequences Sequence Description %~ The name of the presently selected database. Alternatively, the %/ sequence can be used. %# The hash mark if the present user is a superuser. Alternatively, the greater-than sign (>) is used. %> The server port number. %`command` Output of the command represented by command. For instance, you might set this (on a Unix system) to %`date +%H:%M:%S` to include the present time on each prompt. %m The server hostname. %n The presently connected user s username. Controlling the Command History Three variables control psql s command history capabilities: HISTCONTROL: This variable determines whether certain lines will be ignored. If set to ignoredups, any repeatedly entered lines occurring directly following the first line will not be logged. If set to ignorespace, any lines beginning with a space are ignored. If set to ignoreboth, both ignoredups and ignorespace are enforced. HISTFILE: By default, a user s history information is stored within ~/.psql_history. However, you re free to change this to any location you please, ~/pgsql/.psql_history for instance. On Windows, the preceding period is omitted (psql_history). HISTSIZE: By default, 500 of the most recent lines are stored within the history file. Using HISTSIZE, you can change this to any size you please.
Go visit our java server pages services for a reliable, lowcost webhost to satisfy all your needs.

618 CHAPTER 27 THE MANY POSTGRESQL CLIENTS To learn more about a particular command, execute h again, but this time pass the command as a parameter. For example, to learn more about the INSERT command, execute the following: corporate=> h INSERT This produces the following output: Command: INSERT Description: create new rows in a table Syntax: INSERT INTO table [ ( column [, …] ) ] { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, …] ) | query } Therefore, h is useful not only for determining what psql commands are at your disposal, but also for recalling what syntax is required for a particular command. Executing a Query Once connected to a PostgreSQL server, you re free to execute any supported query. For example, to retrieve a list of all company employees, execute a SELECT query, like so: corporate=>SELECT lastname, email, telephone FROM employee ORDER by lastname; Executing a DELETE query works just the same: corporate=> DELETE FROM hr.employee WHERE lastname=’Gilmore’; If you re interested in executing a single query, you can do so when invoking psql, like so: %>psql -d corporate -U hrstaff -c “SELECT lastname, email, telephone FROM employee ORDER by lastname” Once the appropriate query result has been displayed, psql exits and returns to the command line. For automation purposes, you can dump query output to a file with the -o option: %>psql -d corporate -U hrstaff -c “SELECT lastname, email, telephone FROM employee ORDER by lastname” -o “/dataimport/employeeinfo.txt” Note In the next chapter, you ll learn how to execute commonplace administration tasks such as managing users and creating and destroying databases and schemas. Modifying the psql Prompt Because of the lack of visual cues when using the command line, it s easy to forget which database you re presently using, or even which server you re logged into if you re working on
Go visit our java server pages services for a reliable, lowcost webhost to satisfy all your needs.