DB_DIFFERENCES
07/28/2000
Freemed-YiRC Beta0.13
---------------------

DIFFERENCES BETWEEN POSTGRESQL AND MYSQL

There are quite a few major differences between PostgreSQL and MySQL that
have to be accounted for when programming/modifying Freemed-YiRC.  Here is
a list:

1.  The way you create/delete the database structure.

	For both databases, you still have to create the database on your
	own.  This is usually really simple.
	For PostgreSQL the same script can be used to both wipe the DB 
	clean AND create it.
	For MySQL there are two different scripts, one to delete, one to 
	create.  This is due to the fact that if the database doesn't exist
	and you try to delete in MySQL, the entire thing errors out.  Hence,
	two different scripts.

2.  The functions are different in PHP!

	Due to the fact that different DBs are accessed in different ways in
	PHP, a set of SQL abstraction functions had to be created.  These,
	along with all the other Freemed-YiRC functions, can be located in
	'freemed_yirc-funcs.inc'.   Little to no data is accessed directly
	from a PHP script.  Usually all data is accessed through one or more
	of the SQL abstraction functions.  Please read PROGRAMMING_STYLE to
	get an idea of how these functions work!

3.  The date formats are different.

	PostgreSQL uses a native date format of MM-DD-YYYY.
	MySQL uses a native date format of YYYY-MM-DD.

	Freemed-YiRC currently uses a native date format of MM-DD-YYYY.
	Hence, some abstraction functions had to be added to 
	'freemed_yirc-funcs.inc' to make up for these differences.  So,
	whenever a date is entered, or read from, either database, it should
	be used through either fy_putdate() (for PUTTING a date INTO the DB)
	and fy_getdate() (for READING a date FROM the DB).  If these functions
	aren't used across the board, then problems will occur when using
	different databases!

4.  The time formats are different.

	PostgreSQL understands the concepts for AM and PM.
	MySQL does not.

	Hence, Freemed-YiRC does a lot of conversion from a 12-hour
	clock cycle to the 24-hour cycle to coexist with MySQL.

5.  IDs and OIDs (Object IDs)

	PostgreSQL has a feature called an OID.  This is a unique Object ID
	assigned to all rows.  
	MySQL doesn't have OIDs.
	
	In any case, Freemed-YiRC has an ID field in nearly every single table
	which is unique.

	In PostgreSQL the ID field is a 'serial' field.
	In MySQL the ID field is an 'integer auto-increment' field.

	PHP has a function to get the last OID from the last inserted row
	in a PostgreSQL database.
	PHP has a function to get the last ID from the last inserted row
	in a MySQL database.

	Obviously, these two are not interchangeable.  So, in the case where
	the generated ID needs to be known the the script, the script has to
	first decide whether the DB is PostgreSQL or MySQL.  If it's MySQL,
	then it can just use the PHP/MySQL function to get the ID.  If it's
	PostgreSQL, then the script first needs to get the last OID, then
	get the ID attached to that OID.
	So, for MySQL this is a one step process, for PostgreSQL, this is a
	two step process.

	An example of this is in the ir_add2.php3 file.  This is the one
	rare exception where DB-specific functions have to be used in a
	PHP script (as opposed to the SQL abstraction functions).
