Using HSQLDB

These notes are for dealing with the HSQL database used in a legacy web application.

HSQL database commands

Note: These commands are required to by run where the hsqldb jar file is stored

• Starting the server:

  
  java -cp /var/lib/hsqldb/lib/hsqldb.jar org.hsqldb.Server -database.0 file:mydb -dbname.0 phonebook

  
  
• Start the GUI application for the HSQL database:
  
  

  java -cp /var/lib/hsqldb/lib/hsqldb.jar org.hsqldb.util.DatabaseManagerSwing

SQLTOOL

An Example ~/sqltool.rc file:

urlid localhost-sa
url jdbc:hsqldb:hsql://localhost/
username SA
password

To use SQL Tool:

1. Setup a ~/sqltool.rc to store the connection settings for the database (and run the server).
2. Once you have started the database (using the commands in the previous section), run the SQLTool command line utility:
   
   

   java -cp /var/lib/hsqldb/lib/hsqldb.jar:sqltool.jar org.hsqldb.cmdline.SqlTool

   
   
3. Connect to the database as indication by the urlid in your sqltool.rc file:
   
   

   \j localhost-sa

   
   
4. You can stop sqltool at any using
   
   

   \xq (table OR SELECT QUERY)

DATABASE INFO

• Grab list of all tables in the database:
  
  

  SELECT TABLE_SCHEM,TABLE_NAME FROM INFORMATION_SCHEMA.SYSTEM_TABLES WHERE TABLE_TYPE = 'TABLE';

  
  
• Grabbing a single employee record:
  
  

  SELECT LIMIT 0 1 - FROM EMPLOYEES;

  
  
• Outputting user info as a CSV file:
  
  

  SELECT A.USERNAME, A.PASSWORD, A.FIRSTNAME, A.LASTNAME, A.MIDDLENAME as OTHERNAME, B.EMAIL INTO TEXT "users_file" FROM USERS AS A INNER JOIN USER_DETAILS AS B ON A.USERNAME = B.USERNAME;

  
  
• Removing the temporary users table used to create the CSV file:
  
  

  DROP TABLE "users";

  
  
• Date based search query where date is a string (DD-MM-YYYY):
  
  

  SELECT A.USERNAME, B.FIRSTNAME FROM USERS AS A INNER JOIN EMPLOYEES AS B ON A.USERNAME = B.USERNAME WHERE SUBSTRING(A.EXPIRY,7,4) > 2016;

  
  
• Searching for string partials:
  
  

  SELECT TIMES, DATES, USERS FROM LOGS WHERE USERS LIKE 'admin%' AND DATES LIKE '%/03/2015';

  
  
• Grabbing the exact number of matches that match parameters:
  
  

  code>SELECT BUILDING, INCTYPE, COUNT(*) AS NUM FROM REPORTS WHERE BUILDING LIKE 'PLACE %' AND DATE LIKE '%/2015' GROUP BY BUILDING, INCTYPE;

PostgreSQL Full Text Search

PostgreSQL has a decent Full Text Search capability. And it is quite simple to implement too; just a few SQL statements and you are done.

You want to implement your search function using Full Text Search because this method is comparatively faster than pattern matching with '%'. The 'tsvector' and 'gin' processes indexes your values and makes it easier for your DBMS to retrieve them.

To the SQL code (assuming you have a system schema with a clients table):

 1 - Add a search field to the table:

ALTER TABLE system.clients ADD COLUMN search TSVECTOR;

 2 - Convert the existing 'name' and 'email' fields into a TSVECTOR stored in the new search field:

UPDATE system.clients SET search = to_tsvector(name || email);

 3 - Index the search field:

CREATE INDEX search_client_index ON system.clients USING gin(search);

 4 - Create a trigger so that whenever the fields are updated then the search field is updated too:

CREATE TRIGGER update_ticket_search BEFORE INSERT OR UPDATE ON system.clients FOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger(search,"pg_catalog.english",title,email);

 5 - To search for entries you run the following code:

SELECT * FROM system.tickets WHERE search @@ to_tsquery('scrubber');

Resources:

•  Linux Gazette article on full text search features in PostgreSQL

PostgreSQL: Check if Procedural Language is installed

To check if a procedural language is installed on PostgreSQL, just run the following:

SELECT COUNT(*) FROM pg_language WHERE lanname = 'plpgsql';

Values for lanname can be one of the following:

• plsql
• pltcl
• plperl
• plpython

References:

• PostgreSQL documentation on procedural languages
• This SQL code snippet from polak.ro

PostgreSQL: Advanced Concepts

This a continuation of my PostgreSQL series (an installation guide for Ubuntu and an overview of some basic concepts). Here I will briefly outline some more concepts about the database.

You can also check out my notes on designing relational databases

• The purpose of an index is to allow the RDBMS to locate the row efficiently. However maintaining an index requires some overhead, so it is not worthwhile to index all fields in a table. In addition, since the index is synchronized when the table is updated it is recommended to remove indexes that are seldom or never used.


• Although a multi-column index can be specified (up to 32 columns), it is generally not recommended to do so unless in certain circumstances. For instance, a multi-column index is useful if you are constantly making queries that references the two or more fields. This is because multi-column indexes are larger and slower than querying a single column index.


• Unique indexes with NULL values are not considered equal in PostgreSQL (you will need to specify the NOT NULL clause to restrict NULL values for this field)


• Partial indexes allow you to index only the field values that you search frequently, and not the whole table. It offers some benefits as your index will not be as big as if you indexed the whole table, but lost out if you every need to query something outside of the index parameters.


•  Data consistency is maintained by following the MVCC approach (MultiVersion Concurrency Control). Each transaction will see a snapshot of the data as it was, regardless of the current state of the underlying data. This isolates the transaction, preventing it from seeing inconsistent data that is produced from concurrent transactions.
  
  MVCC's major advantage is that locks for read transactions do not conflict with locks for write transactions, and vice versa.


• You can explicitly lock tables or rows when the MVCC approach does not produce desirable behavior. However by explicitly locking resources you increase the risk of producing deadlocks in the system.


• Messages generated by the server are assigned a five-character error code that follows the SQL standard for SQLSTATE codes. This standard states that the first two characters denote the class of the message, while the last three characters denote the specific condition.


• The followin class codes have been defined:
  
  

  CODE	DESCRIPTOR
  00	Successful completion
  01	Warning
  02	No Data (also serves as a warning)
  03	SQL Statement not yet complete
  08	Connection exception
  09	Triggered action exception
  0A	Feature not supported
  0B	Invalid transaction initiation
  0F	Locator Exception
  0L	Invalid Grantor
  0P	Invalid Role specification
  20	Case not found
  21	Cardinality Exception
  22	Data Exception
  23	Integrity Constraint Violation
  24	Invalid Cursor state
  25	Invalid Transaction State
  26	Invalid SQL Statement Name
  27	Triggered Data Change Violation
  28	Invalid Authorization Specification
  2B	Dependant Privileges Descriptors still exist
  2D	Invalid Transaction Termination
  2F	SQL Routine Exception
  34	Invalid Cursor Name
  38	External Routine Exception
  39	External Routine Invocation Exception
  3B	Savepoint Exception
  3D	Invalid Catalog name
  40	Transaction rollback
  42	Syntax Error or Access Role Violation
  44	WITH CHECK OPTION Violation
  53	Insufficient Resources
  54	Program Limit Exceeded
  55	Object not in prerequisite state
  57	Operator Intervention
  58	System Error (external to PostgreSQL)
  F0	Configuration File Error
  HV	Foreign Data Wrapper Error
  P0	PL/pgSQL Error
  XX	Internal Error

  
  


• Unfortunately you cannot raise exceptions directly within the PostgreSQL server. You could use a procedural language within the server (such as PL/pgSQL), but this will limit the portability and compatibility of your code with other RDBMS systems.


• In PL/pgSQL, you would use the RAISE statement to report exceptions and messages.
  

  RAISE [level] 'message' [, value] USING ERRCODE = 'SQLSTATE';

  where:
  level = DEBUG, LOG, INFO, NOTICE, WARNING, and EXCEPTION
  message = String (% denotes a placeholder for an optional value
  value = field value
  SQLSTATE = Error code conforming to the SQLSTATE convention


• A SQL query will undergo the following process in order to generate a result:
  1. A connection from the application to the server is established. The application then sends a query to the server and waits for a result
  2. The parser checks the query for correct syntax and creates a query tree.
  3. The rewrite system takes the query tree and looks for any rules (stored in the system catalog) to apply to the tree. It performs the transformations given in the rule bodies.
  4. The planner/optimizer takes the rewritten query tree and creates a query plan. It does this by creating all possible paths leading to the same result, and then calculates the estimated cost of each plan. It will then select the cheapest path and prepare a query plan tree for the executor.
  5. The executor recursively steps through the plan tree and retrieves rows in the way represented by the plan. When it is complete it hands the result back to the application.

References:

• The PGAdmin3 main web-page
• The PostgreSQL 9.1 documentation
• An extensive (but still a work in progress) comparison of the SQL implementations of various RDBMS' from troels.arvin.dk
• ISO/IEC 9075-1:2011, or the SQL standard (it costs money, though) 
• PL/pgSQL exception handling by stackoverflow.com

PostgreSQL 9.1 and Ubuntu 12.04

This is just a quick guide on how to get started using PostgreSQL on Ubuntu.

Installing

1. Install the PostgreSQL server using apt:
   

   sudo apt-get install postgresql

2. Install the contrib add-on package. This provides additional tools and features, such as improved logging and administration functions.
   

   sudo apt-get install postgresql-contrib

3. Install the GUI admin interface pgadmin3:

   sudo apt-get install pgadmin3

4.  Edit the file /etc/postgresql/9.1/main/postgresql.conf to allow TCP/IP connections to the server. Just uncomment the following line in the file:
   

   listen_addresses = 'localhost'

5. Start the database
   

   sudo service postgresql start

6.  To automatically start the server run the following command:
   

   sudo update-rc.d postgresql defaults

7. Put a password on the default postgresql user by opening up a connection to the database:
   

   sudo -u postgres psql template1

   And then running the following SQL command:

   ALTER USER postgres WITH PASSWORD 'password';

8. Set up the same password for the system postgres user:
   

   # Delete the existing postgres user password
   sudo passwd -d postgres
   
   # Set the user password
   sudo su postgres -c passwd

    

Interfacing with PostgreSQL

• You can create a database directly from the command line (this assumes that your linux user has a corresponding account to use PostgreSQL):
  

  createdb newdb

• To create a new database with a user that has full rights on that database:

  # Create the user
  sudo -u postgres createuser -D -A -P newuser# Create the database
  sudo -u postgres createdb -O newuser newdb

• The corresponding command to delete a database is:
  

  dropdb olddb

• Accessing the database through the commandline:
  

  psql newdb
  #OR you could use
  sudo -u postgres psql newdb#OR you could use
  psql -h localhost -U postgres -W newdb

  
  NOTE: If the command prompt shows "=#", then you are using an admin account which bypasses normal access controls. This is potentially dangerous. Your average account should be displaying "=>" as part of the prompt.
• In-built psql commands are prefaced with a '\':
  
  • \h displays the help for SQL commands
  • \? displays the help for psql-specific commands
  • \q exits the terminal interface for psql
• Some useful in-built SQL statements:
• SELECT version(); will display the current PostgreSQL version
• SELECT current_date; will display the current date of the system
• SELECT now(); will display the date and time
• SELECT now()::date; will only display the date portion
• SELECT now()::time; will only display the time portion
• count(), sum(), avg(), max(), and min() are special aggregate functions you can perform on fields in your SQL statement.

References:

• How to set up PostgreSQL in Ubuntu by ubuntugeek.com
• The PGAdmin3 main web-page
• The PostgreSQL 9.1 documentation
• Automatically starting services in Debian

Email validation through MySQL triggers and signals

Did some research and found out that MySQL provides triggers and signals. Triggers allow you to run some code when an certain event occurs, and Signals allow you to raise exceptions in your code.

Here is a SQL script to show you how it is done:

CREATE SCHEMA IF NOT EXISTS `test` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;

USE `test`;

CREATE TABLE IF NOT EXISTS `test`.`entity_email` (

    `emailID` INT NOT NULL,

    `email` VARCHAR(64) NOT NULL,

    PRIMARY KEY (`emailID`) )

ENGINE = InnoDB

COMMENT = "Generic Email table";

DELIMITER $$

USE `test`$$

CREATE TRIGGER `trg_entity_email_insert` BEFORE INSERT ON `test`.`entity_email` FOR EACH ROW

BEGIN

    IF NOT (SELECT NEW.email REGEXP '$[A-Z0-9._%-]+@[A-Z0-9.-]+\.[A-Z]{2,4}$') THEN

        -- bad data

        SIGNAL SQLSTATE '40000';

    END IF;

END$$

CREATE TRIGGER `trg_entity_email_update` BEFORE UPDATE ON `test`.`entity_email` FOR EACH ROW

BEGIN

    IF NOT (SELECT NEW.email REGEXP '$[A-Z0-9._%-]+@[A-Z0-9.-]+\.[A-Z]{2,4}$') THEN

        -- bad data

        SIGNAL SQLSTATE '40000';

    END IF;

END$$

DELIMITER ;

Now MySQL will perform e-mail validation EVERY time you insert or edit the record. If it doesn't match, it will fail (MySQL outputs an "Unhandled user-defined exception condition" as the error message).

Hope this helps!