SQL JOINS

A brief reminder on the different types of SQL JOINS

MySQL and PostgreSQL comments

/* this is a multiple-line comment */

# this is a comment

-- this is a comment

Stored Procedures in MySQL and PostgrSQL

The following will create a storied procedure called getPercentage() with MySQL

DELIMITER $$

CREATE PROCEDURE getPercentage()
BEGIN   SELECT (COUNT(fault_resolved)/ count(id)*100)AS percent
    FROM log;
END$$
DELIMITER ;

Note:  The DELIMITER $$ exists to change the delimiter used, which is normally ;
This means we can have our statement inside and using a ; but will not be seen as the end of the statement.

This can be called by

CALL getPercentage();

Alternative methods of joining results from two tables

If we are wanting to pull information from two different tables that are linked in some way, the conventional way of doing this is as follows:

SELECT  table1.item, table2.item
FROM table1 JOIN table2 ON table1.item = table2.item;

However an alternative is to use the following;

SELECT  table1.item, table2.item
FROM table1, table2
WHERE table1.item = table2.item;

Convert PostgreSQL database to MySQL

To convert from a PostgreSQL database to MySQL;

Check out this link

Connect to Postgres database with php

This extension enables PHP to connect to and use PostgreSQL databases.

This is specifically for Slackware.

1)  install php-pgsql

Slackbuild install file ready for 14.2 here

Slack build page here

2) To enable the pgsql extension for php, go to /etc/php.d/pgsql.iniand uncomment the line:

; extension=pgsql.so

Replacing postgres database

This is applicable to the Peon application, in so far as when the PAF data is updated and a new database is created, normally to a temp test database for testing, afterwhich a renaming or replacing of working database is required.

Setup Postgres on Slackware

TODO: Add Slackware install

with user postgres run psql

CREATE DATABASE dbname;

GRANT ALL ON DATABASE dbname TO postgres;