Aug 31, 2012

HTML5 and CSS3 drop-down menu

Don't forget to visit more of CSS3 tips and tricks!

The HTML5

All you will need is a simple menu structure using <nav> and nested <ul> tags, like this:
<nav>
    <ul>
        <li><a href="/">Home</a>
            <ul>
                <li><a href="/about">About</a></li>
                <li><a href="/contact">Contact</a></li>
            </ul>
        </li>
        <li><a href="/service">Services</a>
            <ul>
                <li><a href="/service/transport">Transport</a></li>
            </ul>
        </li>
        <li><a href='/welcome/language'>Change Language</a></li>
    </ul>
</nav>

The CSS3

I am just going to comment the CSS code; hopefully that is enough explanation for you...
/********************* Nav elements ****************************/

/* Navigation menu HTML5 tag*/
nav
{
    /* No border*/
    border:none;
    border:0px;
   
    /* Ensures the text is aligned properly*/
    text-align: left;
   
    /* Margins and padding*/
    margin:0px;
    padding:0px;
}

/* Our top menu list */
nav ul
{
    /* Set's how the element will interact with adjacent elements */
    display:block;
   
    /* Sets the height of the element (needs to be larger than the text) */
    height: 35px;
   
    /* Buffer space between other containers */
    padding-top: 5px;
    padding-bottom: 5px;
    padding-left: 1%;
    margin: 0;
   
    /* Does not insert bullet points*/
    list-style:none;
}

/* Float all <li> elements */
nav li{
    float:left;
}

/* Display top menu <li> items as inline */
nav ul li
{
    /* Set's how the element will interact with adjacent elements */
    display:inline;
}

/* The main menu link */
nav ul li a
{
    /* Changes the text to bold and uppercase */
    font-weight: bold;
    text-decoration:none;
    text-transform: uppercase;
   
    /* Default text colour */
    color:#CCCCCC;
   
    /* Pads the text so that it is not right up against the parent element*/
    padding: 5px;
   
    /* Specifies the line-height of the text */
    line-height:30px;
   
    /* Background colour */
    background-color: #FFFFFF;
   
    /* Border colour */
    border: 2px solid #CCCCCC;
   
        /* CSS3 Rounded Border */
    -webkit-border-radius: 8px; /* Saf3-4, iOS 1-3.2, Android ≤1.6 */
        -moz-border-radius: 8px; /* FF1-3.6 */
        border-radius: 8px; /* Opera 10.5, IE9, Saf5, Chrome, FF4, iOS 4, Android 2.1+ */

      /* useful if you don't want a bg color from leaking outside the border: */
      -moz-background-clip: padding; -webkit-background-clip: padding-box; background-clip: padding-box;
}

/* When we hover over (or for mobile devices, click on the element) */
nav ul li a:focus, nav ul li:focus a, nav ul li a:hover, nav ul li:hover a
{
    /* Underlines the text when we hover over it*/
    text-decoration:underline;
   
    /* Change the background colour*/
    background-color: #CCCCCC;
   
    /* Default text colour */
    color:#336666;
   
    /* Border colour */
    border: 2px solid #336666;
}

/* Our sub menus */
nav li ul
{
    /* Our background colour */
    background-color: #CCCCCC;
   
    /* HIDES the element until needed! */
    display:none;
   
    /* Let the browser determine the element height */
    height:auto;
   
    /* No padding or margins required */
    padding:0px;
    margin:0px;
   
    /* Minimum width of 120px */   
    min-width: 120px;
    width: auto;
   
    /* Use the absolute positioning method*/
    position:absolute;
   
    /* Stack this element right at the front of all elements*/
    z-index:200;
   
    /* Default text colour */
    color:#336666;
   
    /* Border colour */
    border: 2px solid #FFFFFF;
   
    /* CSS3 Rounded Border */
    -webkit-border-radius: 8px; /* Saf3-4, iOS 1-3.2, Android ≤1.6 */
        -moz-border-radius: 8px; /* FF1-3.6 */
        border-radius: 8px; /* Opera 10.5, IE9, Saf5, Chrome, FF4, iOS 4, Android 2.1+ */

      /* useful if you don't want a bg color from leaking outside the border: */
      -moz-background-clip: padding; -webkit-background-clip: padding-box; background-clip: padding-box;
}
nav li:focus ul, nav li:hover ul
{
    /* Set's how the element will interact with adjacent elements */
    display:block;
}
nav li li
{
    /* Set's how the element will interact with adjacent elements */
    display:block;
   
    /* Do not allow the element to float around */
    float:none;
   
    /* Do not set margin */
    margin: 0;
   
    /* Take up all space given by parent element*/
    /*width:100%;*/
   
    /* Border colour */
    border: none;
}
nav li:focus li a, nav li:hover li a
{
    /* Turn off the background */
    background:none;
    border:none;
    text-decoration:none;
}
nav li ul a
{
    /* Set's how the element will interact with adjacent elements */
    display:block;
   
    /* Sets the height of the element (needs to be larger than the text) */
    /*height:35px;*/
   
    /* No margin */
    margin:0px;
    /*line-height: 20px;*/
   
    /* No border or underlines*/
    text-decoration:none;
    border:none;
   
    /* Default text colour */
    color:#CCCCCC;
}
nav li ul a:hover, nav ul li ul li:hover a
{
    /* Underlines the text when we hover over it*/
    text-decoration:underline;
   
    /* Change the background colour*/
    background-color: #336666;
   
    color: #CCCCCC;
}
nav p
{
    /* No floating elements are allowed to the left of this element */
    clear:left;
}

Aug 30, 2012

CodeIgniter: Dynamically setting the website's language

I have been writing up my own View subsystem for CodeIgniter that seperates the  View more clearly from the controller. I have also designed it with Language support in mind, but for that I had to do some minor edits to the default Language class.

Even though you can load a specific language file in another language, the system will load error messages in the default language. This led to some cases where system errors would be in English, but the site language was something else.

With my new system I can have my default site language can be dynamically set by a user cookie… see the following code:

$lang_temp = $this->input->cookie('language');
if ( $lang_temp )
{
     // NOTE: getDefault() is one of my core Lang.php modifications
     if (!($lang_temp === $this->lang->getDefault()))
     {
        // NOTE: setDefault() is one of my core Lang.php modifications
        $this->lang->setDefault($lang_temp);
     }
} else {
     // Set a cookie with system default
     $this->input->set_cookie('language', $this->lang->getDefault(), '7200', '.' . $_SERVER['HTTP_HOST'], '/', NULL, FALSE);
}
 
But to make the above code work I had to make some changes to the core:



<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
/**
* CodeIgniter
*
* An open source application development framework for PHP 5.1.6 or newer
*
* @package CodeIgniter
* @author ExpressionEngine Dev Team
* @copyright Copyright (c) 2008 - 2011, EllisLab, Inc.
* @license http://codeigniter.com/user_guide/license.html
* @link http://codeigniter.com
* @since Version 1.0
* @filesource
*/

// ------------------------------------------------------------------------

/**
* Language Class
*
* @package CodeIgniter
* @subpackage Libraries
* @category Language
* @author ExpressionEngine Dev Team
* @link http://codeigniter.com/user_guide/libraries/language.html
*/
class CI_Lang {

/**
* List of translations
*
* @var array
*/
var $language = array();
/**
* List of loaded language files
*
* @var array
*/
var $is_loaded = array();

/**
* Default language
*
* @var array
*/
var $default_lang;

/**
* Constructor
*
* @access public
*/
function __construct()
{
    $config =& get_config();
    $this->default_lang = ( ! isset($config['language'])) ? 'en' : $config['language'];

    log_message('debug', "Language Class Initialized");
}

// --------------------------------------------------------------------

/**
* Load a language file
*
* @access public
* @param mixed the name of the language file to be loaded. Can be an array
* @param string the language (english, etc.)
* @param bool return loaded array of translations
* @param bool add suffix to $langfile
* @param string alternative path to look for language file
* @return mixed
*/
function load($langfile = '', $idiom = '', $return = FALSE, $add_suffix = TRUE, $alt_path = '')
{
    $langfile = str_replace('.php', '', $langfile);

    if ($add_suffix == TRUE)
    {
       $langfile = str_replace('_lang.', '', $langfile).'_lang';
    }

    $langfile .= '.php';

    if (in_array($langfile, $this->is_loaded, TRUE))
    {
       return;
    }

    if ($idiom == '')
    {
       $idiom = ($this->default_lang == '') ? 'en' : $this->default_lang;
    }

    // Determine where the language file is and load it
    if ($alt_path != '' && file_exists($alt_path.'language/'.$idiom.'/'.$langfile))
    {
       include($alt_path.'language/'.$idiom.'/'.$langfile);
    }
    else
    {
       $found = FALSE;

       foreach (get_instance()->load->get_package_paths(TRUE) as $package_path)
       {
          if (file_exists($package_path.'language/'.$idiom.'/'.$langfile))
          {
              include($package_path.'language/'.$idiom.'/'.$langfile);
              $found = TRUE;
              break;
          }
      }

      if ($found !== TRUE)
      {
         show_error('Unable to load the requested language file: language/'.$idiom.'/'.$langfile);
      }
   }


    if ( ! isset($lang))
   {
      log_message('error', 'Language file contains no data: language/'.$idiom.'/'.$langfile);
      return;
   }

   if ($return == TRUE)
   {
      return $lang;
   }

   $this->is_loaded[] = $langfile;
   $this->language = array_merge($this->language, $lang);
   unset($lang);

   log_message('debug', 'Language file loaded: language/'.$idiom.'/'.$langfile);
   return TRUE;
}

// --------------------------------------------------------------------

/**
* Fetch a single line of text from the language array
*
* @access public
* @param string $line the language line
* @return string
*/
function line($line = '')
{
   $value = ($line == '' OR ! isset($this->language[$line])) ? FALSE : $this->language[$line];

   // Because killer robots like unicorns!
   if ($value === FALSE)
   {
      log_message('error', 'Could not find the language line "'.$line.'"');
   }

   return $value;
}

/**
* Fetch Default language
*
* @access public
* @param void
* @return string Default language code
*/
function getDefault()
{
   return $this->default_lang;
}

/**
* Set Default language
*
* @access public
* @param string The default language
* @return void
*/
function setDefault($language)
{
   $this->default_lang = $language;
}

}
// END Language Class

/* End of file Lang.php */
/* Location: ./system/core/Lang.php */

I am sure you can find other uses for this code.....

My other posts on CodeIgniter include:

Aug 29, 2012

PostgreSQL: Creating Users and Databases

This is a very, very quick blog post regarding how to create a user and database in PostgreSQL through the command line prompt.

To do this you just need to run two commands:
createuser -h localhost -U postgres testuser -W -S -D -R -P
createdb -h localhost -U postgres -O testuser -W  test_db

These commands assume you have password protected the local postgres user (if not, you can drop the -W trigger).

A quick overview of the createuser command:

  • -U tells the command which user to perform the command with (NOT the user to create)
  • -S tells us that the new user is NOT have superuser privileges (as opposed to the lower case -s, which do have superuser privileges)
  • -D tells us that the new user is NOT have create database privileges (as opposed to the lower case -d, which do have superuser privileges)
  • -R tells us that the new user is NOT have create roles privileges (as opposed to the lower case -r, which do have superuser privileges)
  • -P prompts the command to immediately prompt for a password for the new user.
  • -W will prompt the password for the user that will run the command (i.e. the username passed with the -U trigger)
  • -h tells the commands which host to connect to.

The createdb command only differs with the -O trigger, which tells the command which user will own the new database.

And that's it!

Other parts of PostgreSQL series include an installation guide for Ubuntu, useful functions and operators, a guide to improving Postgre performance, how to check for installed procedural languages, and an overview of some basic concepts and some more advanced ones.

You can also check out my notes on designing relational databases.

PostgreSQL: Improving performance

This a continuation of my PostgreSQL series (an installation guide for Ubuntu, useful functions and operators, an overview of some basic concepts and some more advanced ones). Here I will briefly outline some functions available on the PostgreSQL database.

You can also check out my notes on designing relational databases.

  • Although PostgreSQL does a good job of maintenance of tuning. However, it is useful to analyses the systems real-time query use of indexes. There are plenty of tools available to profile your server, but the usefulness depends on the requirements of your particular system.

    NOTE: Always performing tuning on real data. Test data will just tell you what will be optimal for the test data only.

  • EXPLAIN is a tool that shows the execution plan of a statement and the associated costs (measured in disk page fetches). You can add the options ANALYZE (actually executes the statement; no longer uses estimates), and VERBOSE (display additional information).

    A good explanation on how to use EXPLAIN is provided by the documentation.
    EXPLAIN [ANALYZE] [VERBOSE] statement

  • ANALYZE collects statistics about the contents of tables and stores the results in a system catalog. These statistics are then used by the planner in selecting an appropriate query plan.

    It is useless to conduct performance tuning without first running the ANALYZE command. Otherwise, any results gathered will be generating using default values that are sure to be wrong.
    ANALYZE [VERBOSE] [table]

  • There are a number of useful views to display statistical information about the server:
    ViewDescription
    pg_stat_activityOne row per server process
    pg_stat_databaseOne row per database
    pg_stat_database_conflictsShow standby server database conflicts due to dropped tablespaces, lock timeouts, old snapshots, pinned buffers and deadlocks.
    pg_stat_all_tablesShow all tables in the current database
    pg_stat_all_indexesShow all indexes in the current database
    pg_statio_all_tablesShow all the tables' disk io statistics
    pg_statio_all_indexesShow all the indexes' disk io statistics

  • The default configuration of PostgreSQL is designed to work on a wide variety of hardware and software installations and is most definitely not optimal for your system. You should edit the postgresql.conf file to increase the values of shared_buffers, effective_cache_size, sort_mem, max_fsm_pages and max_fsm_relations.

  • The physical memory and disks will most probably be the slowest thing in your system. You should always tweak your hardware to generate the optimal performance in your system.

References:

Aug 28, 2012

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:

References:

Aug 27, 2012

PostgreSQL: Functions & Operators

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

You can also check out my notes on designing relational databases

  • The usual logical operators of AND, OR, and NOT are available. However, SQL uses a three-valued logic system of TRUE, FALSE and NULL (which represents "unknown"). You should therefore design your system to handle a NULL result.

  • The usual comparison operators of >, <, >=, <=, = and <> (or !=, but the parser resolves this to <> anyway) are available.

  • The BETWEEN construct allows you to test a value between a range (NOT BETWEEN tests if a value is not within a range)
    a BETWEEN x AND y
    is equivalent to
    a >= x AND a <= y

  • The following extra comparison operators will always resolve to a TRUE or FALSE value:
    a IS NULL
    a IS NOT NULL
    a IS TRUE
    a IS NOT TRUE
    a IS FALSE
    a IS NOT FALSE
    a IS UNKNOWN
    a IS NOT UNKNOWN

  • Pattern matching can be achieved through LIKE, SIMILAR TO, or POSIX style regular expressions.

    LIKE is the simplest pattern matching technique. The _ represents any single character, while a % represents a sequence of characters.

    SIMILAR TO is a hybrid approach between LIKE and regular expressions. It still uses the _ and %, but additionally uses symbols borrowed from regular expressions like *, ?, {} and [] (see the documentation for an in-depth explanation).

    Regular expression support is also provided, but is an extensive topic that would be too lengthy to include here. A great reference can be found at regular-expressions.info

  • PostgreSQL provides a wide array of date & time functions. These include current_date, current_time, and current_timestamp.

    Another useful function is age(timestamp), which will subtract the timestamp from the current_date and output a result in years, months and days.

  • A CASE expression exists for conditional statements. expression is the SQL expression, value is the matching result of the expression, and result is the value to return
    CASE expression
    WHEN value THEN result
    [WHEN....]
    [ELSE result]
    END

  • The general purpose window functions will only work if invoked via the window function syntax (that is, the OVER clause is required). In addition to in-built aggregate functions, the following general purpose functions will work:
    • row_number(): number of the row within the partition
    • first_value(field): returns the value of the field from the first row of the window frame
    • last_value(field): returns the value of the field from the last row of the window frame
    • nth_value(field): returns the value of the field from the nth row of the window frame

  • There are many functions to get the current system state, but here are a few of the useful ones:
    • version(): Version info
    • current_user: Username of the current execution context
    • current_database(): Name of the database in use
    • current_schema[()]: Name of the current schema
    • inet_client_addr(): The address of the remote connection
    • inet_client_port(): The port of the remote connection
    • pg_conf_load_time(): Configuration loading time

  • There also exists many administration functions that allow to configure PostgreSQL at runtime. You can also get other running information from the system such as the physical size of databases, initiate back-ups and recoveries, and signal actions to the server.

References:


Aug 22, 2012

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:

    CODEDESCRIPTOR
    00Successful completion
    01Warning
    02No Data (also serves as a warning)
    03SQL Statement not yet complete
    08Connection exception
    09Triggered action exception
    0AFeature not supported
    0BInvalid transaction initiation
    0FLocator Exception
    0LInvalid Grantor
    0PInvalid Role specification
    20Case not found
    21Cardinality Exception
    22Data Exception
    23Integrity Constraint Violation
    24Invalid Cursor state
    25Invalid Transaction State
    26Invalid SQL Statement Name
    27Triggered Data Change Violation
    28Invalid Authorization Specification
    2BDependant Privileges Descriptors still exist
    2DInvalid Transaction Termination
    2FSQL Routine Exception
    34Invalid Cursor Name
    38External Routine Exception
    39External Routine Invocation Exception
    3BSavepoint Exception
    3DInvalid Catalog name
    40Transaction rollback
    42Syntax Error or Access Role Violation
    44WITH CHECK OPTION Violation
    53Insufficient Resources
    54Program Limit Exceeded
    55Object not in prerequisite state
    57Operator Intervention
    58System Error (external to PostgreSQL)
    F0Configuration File Error
    HVForeign Data Wrapper Error
    P0PL/pgSQL Error
    XXInternal 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:

Aug 21, 2012

PostgreSQL: Basic Concepts

This a continuation of my PostgreSQL series (an installation guide for Ubuntu can be found here). Here I will briefly outline some basic concepts about the database.

You can also check out my notes on designing relational databases.
  • Views are a concept that is common to almost all relational database (RDBMS) systems. The best way to think of a view is akin to an object-orientated interface; an abstraction that takes away all the underlying structural details and provides a generic way to access data and functions.

  • Foreign keys help maintain the referential integrity of your data. It links a record to another record stored in a different relation.You can additionally define the constraints and associations for the link.

  • Transactions are atomic operations where all the steps happen or none of the steps happen. A transaction also guarantees the the result of the operation has been logged to physical storage so that no data is lost in the case of a system crash.

  • In PostgreSQL, a transaction block starts at the BEGIN command and completes when it reaches the COMMIT command. You can place as many SQL commands as you wish between these two block commands. If you want to cancel the update and reset the database, you just issue a ROLLBACK command.

    An example of a transaction block is as follows:
    BEGIN;
    UPDATE accounts SET balance = balance - 100.00 WHERE name = 'Alice';
    UPDATE branches SET balance = balance - 100.00 WHERE name = (SELECT branch_name FROM accounts WHERE name = 'Alice');
    UPDATE accounts SET balance = balance + 100.00 WHERE name = 'Bob';
    UPDATE branches SET balance = balance + 100.00 WHERE name = (SELECT branch_name FROM accounts WHERE name = 'Bob');
    COMMIT;
  • Window functions perform a calculation across a relational table. It is similar to aggregate functions like count() and min(), however where these functions return a single output row a window function retains the identities of all rows in the relation. PostgreSQL defines some in-built aggregate functions that can be used within a window function.

  • The OVER clause in a window function specifies how the rows are split up and processed. For instance, you can specify a PARTITION BY list that groups records by a field value. The aggregate function then processes these records by the group rather than all records in the relation. This is a relatively complex topic, so it is strongly suggested that you read the PostgreSQL documentation on this topic.

  • Inheritance is a concept that has carried over from object-orientated databases. However, it is not feature-complete and has some serious caveats that severely limits the functionality of this feature. You should read the documentation to see if the inheritance feature is suitable for your needs.

  • You can alternatively use views, foreign key constraints and other database concepts to mimic inheritance in your database.

  • PostgreSQL supports the idea of constraints for table fields. This allows us to fine-tune the allowable data that can be stored beyond just defining data types. The following constraints have been defined:

    TypeDescription
    CHECKThe value for a column must satisfy a Boolean condition. You can also specify a name by using the optional CONSTRAINT clause.

    CREATE TABLE products(
    price numeric CONSTRAINT positive_price CHECK (price >0)
    );
    NOT NULLSimply states that a value cannot be NULL
    UNIQUESimply states that the value is unique across all record rows
    PRIMARY KEYSimilar to a combination of UNIQUE and NOT NULL constraints, however it specifies that the field can be used as a unique identifier for the row. There can only be one PRIMARY KEY entry for each table.
    REFERENCES (Foreign Key)The REFERENCES clause is used to specify a foreign key relationship between two tables, which will maintain referential integrity of the data.

    When this clause is used, it will become impossible to create a row when the foreign key does not exist in the referenced table.

    You can also specify what happens to the row when the referenced field is updated or deleted by using the ON UPDATE and ON DELETE clauses, respectively. The actions you can define are RESTRICT, CASCADE, NO ACTION, SET NULL and SET DEFAULT. See the documentation for more information on how this works.


  • Each table is created with several system columns. These are reserved and cannot be used as names for user-defined columns. System columns store information such as the unique id of the table, the unique id of the record in the table, and the physical location of the row.
  • The ALTER TABLE command allows you to modify an existing table. This is useful if the table already contains data or is being referenced by other tables.
  • PostgreSQL Schemas can be thought of as table namespaces. You can logically group tables in a database by schema, and you can have identically named tables existing in two different schemas.
    By Default, a new database will automatically have a 'public' schema that is accessible by all users with privileges on the database.
  • Partitioning a table is done via table inheritance (so read up on how PostgreSQL does inheritance before using this feature!). By physically partitioning a table you can increase query times (since the server only needs to search the partition and not the whole table), and you can move less-used data to cheaper disks.

References:

Aug 20, 2012

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:

Aug 5, 2012

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!