LIMIT Yourself to MySQL No More

So far we have not found any actual schema for the database -- that is, the structure of the database tables and columns. This is another area that databases tend to differ in rather creative ways. FRED contains all of the CREATE TABLE statements in installation/sql/FRED.sql. Let's take a look and try to spot some potential problems:

CREATE TABLE `#__bannerclient` (
  `cid` int(11) NOT NULL auto_increment,
  `name` varchar(60) NOT NULL default '',
  `extrainfo` text NOT NULL,
  `checked_out` tinyint(1) NOT NULL default '0',
  `checked_out_time` time default NULL,
  PRIMARY KEY  (`cid`)
) TYPE=MyISAM;

TYPE=MyISAM: this clause will make any database other than MySQL choke. Note also that the table type gives you speed at the expense of transactions, relational integrity, row locking, and other standard database features.

Quotation marks: most databases do not allow you to put single-quotes around table names, column names, and other SQL identifiers and will return an error message. You can use double-quotes, but these indicate to the database that the SQL identifier will be case-sensitive.

The auto_increment clause used to define an identity column may not be understood by most databases, as this is non-standard DDL. "Standard" DDL defines an auto-incrementing column using the GENERATED ALWAYS AS IDENTITY clause instead:

DB2 and Apache Derby offer the GENERATED ... IDENTITY CLAUSE

PostgreSQL offers the SERIAL data type

Microsoft SQL Server offers the IDENTITY data type attribute

Oracle uses a combination of sequences and triggers to simulate an identity column.

TEXT is a non-standard data type that best maps to a VARCHAR(65,535) or CLOB(64K) standard data type.

TINYINT (an integer accepting values from 0-255 or -128 to 128) can be mapped best to either a plain old INTEGER or a NUMERIC(3,0). In other cases in the DDL, the TINYINT column is defined with a (1) or (3) -- this affects the display of the value by left-padding the integer type with zeros, which simply reduces the portability of the schema to other databases and the flexibility of the data in the column.

The "special" table name prefix #__ is a way of avoiding table name clashes with other applications stored in the same database. Apache Derby, like most database servers, supports the use of schemas to provide a namespace for tables. For example, application dboy's tables could be created in the dboy schema and accessible as dboy.table1, dboy.table2 without conflicting with dgirl.table1, dgirl.table2 existing in the same database.