Wiki ⇒ v9 Developer's Manual ⇒ Coding standards ⇒ SQL handling ⇒ CPG Dragonfly™ CMS

13.2. 1: SQL handling Parent


Dragonfly's uniform
Manager functions

As Dragonfly moves closer to becoming a system that supports several SQL servers/clients, it is nice to know what impact this has since not every SQL server is the same although there are ISO documents for SQL.
One of the biggest headaches is field types, where MySQL knows tinyint but MSSQL does not. Also, difficulties arise when trying to access a server in a deeper level.

To overcome these issues Brennor and DJ Maze have developed a SQL Manager for Dragonfly 9.1.x and up, to use inside Dragonfly and the sql manager is an embedded class inside the current layer and you don't even notice it's there.
In this document we will explain exactly what this manager does.

Dragonfly's Uniform Field Type's


Since each database has its own definitions of data types, we present this illustration to show what we use compared to the SQL servers.
uniformMySQL 4MySQL 5PostgreSQL
SERIAL4 INT UNSIGNED NOT NULL AUTO_INCREMENT <- SERIAL NOT NULL
SERIAL8 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT <- BIGSERIAL NOT NULL
TEXT TEXT <- TEXT
BLOB BLOB <- BYTEA
INT1 TINYINT <- SMALLINT
INT2 SMALLINT <- SMALLINT
INT3 MEDIUMINT <- INTEGER
INT4 INT <- INTEGER
INT8 BIGINT <- BIGINT
CHAR CHAR <- CHARACTER
VARBINARY VARCHAR BINARY VARBINARY VARBINARY
VARCHAR VARCHAR <- VARCHAR
FLOAT4 FLOAT <- REAL
FLOAT8 DOUBLE <- DOUBLE PRECISION
DECIMAL DECIMAL <- NUMERIC

While the sql manager handles the conversion we would write our PHP in the following way:

global $db;
$db->create_table("my_table (
  id SERIAL4,
  title VARCHAR(40),
  content TEXT,
  date INT4
)"
);

Which results in a query for MySQL:

CREATE TABLE my_table (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  title VARCHAR(40),
  content TEXT,
  date INT(11)
) ENGINE=MyISAM

Manager Functions

create_table(query)
Creates table as given in the query.

alter_table(query)
Modifies table as given in the query.

drop_table(table)
Deletes the given table.

list_databases()
Returns an array with all accessible databases.

list_tables([database])
Returns an array with all accessible tables of the current database connection.
If the optional parameter database is given, then the tables of that database are returned.

list_columns(table[, uniform])
Returns an array with all columns of the given table.
If the optional parameter uniform is set to false, then the returned column types are as the SQL server has given. On default uniform is true and returns Dragonfly uniform field types.
Each array entry contains:

  • Field: the column/field name
  • Type: the field data type like INT4
  • Null: 1 if field may contain NULL else 0
  • Default: default column value or NULL

list_indexes(table)
Returns an array with all indexes of the given table.
Each array entry contains:

  • name: the name of the column/key
  • unique: wether if the given index is a UNIQUE index
  • type: BTREE or FULLTEXT
  • columns: array of columns with
    • name: column name

alter_field(mode, table, field[, type[, null[, default]]])
Modifies the field/column details or deletes it

alter_index(mode, table, name[, columns])
Modifies or drops a index


Created: Saturday, December 03, 2005 (04:56:14) by DJMaze
Updated: Wednesday, April 18, 2012 (06:21:19) by NanoCaiordo