MOD Template SQL Command Standard

[Nuttzy99]

MOD Template SQL Command Formatting
rev 1.0.0
August-11-2004

phpBB supports several databases, and as MOD'ers we should attempt to support as many as we can. The problem is that doing so is cumbersome and that very few people know the SQL for each DB in first place. The purpose of this formatting standard is to provide a simple base that can then easily be translated to the SQL of any of the DB's. Since the predominant number of MOD Authors understand the format of MySQL, it makes sense to use it as our model.

NOTE: the standards are not written in stone and are open for discussion. However, whatever is implemented MUST be able to translate to all major database types.

NOTE: I still need help with MSSQL and Postgresql


The least you need to know:

• Can't use SELECT
• Can CREATE and DROP tables. Can ADD, MODIFY, and DROP columns
• Have as many SQL declarations and as many commands inside those declarations as you desire.
• Closely resembles MySQL. See the documentation below for actual formatting.

What SQL cannot be done?
MOD Installations are concerned with things like creating tables and populating them. Until our tools get more sophisticated, SELECT statements are not allowed. It will be cool to have more interactive MOD scripts in the future though


What can be done?
Essentially what we are trying to do here is extend the existing phpBB DBAL. The Dev Team already has INSERT and UPDATE nailed, so just use these as you would inside phpBB code and it'll get passed "as is" to the phpBB DBAL. What we are now adding to the mix is the ability to CREATE and DROP tables and also ADD, MODIFY, and DROP columns. Although we're leery as to why you'd want to DROP something

The format is valid MySQL. However, this does not mean that all valid MySQL will work. So think of the format as being MySQL "Lite" So, unfortunately there are some restrictions and we'll cover them now.


Command Declaration:
Let's start with the basics. The command is denoted as you would probably expect:

#
#-----[ SQL ]---------------------------------
#

A key issue to note is dealing with comments. Often times folks will like to leave comments stating what the SQL is doing. Unfortunately this isn't highly compatible with our system. The problem is that the comments look too much like a MOD Template command and will throw off automated tools like EasyMOD. So there is a right way and a wrong way to leave comments.

#
#-----[ SQL ]---------------------------------
#
# Table structure for table `phpbb_easymod`
# (-- you can have as many comment lines here as you want
# as long as they are not separated by an empty line --)
#

...notice how all the lines above start with a # and are not interrupted by an empty line.

#
#-----[ SQL ]---------------------------------
#

#
# Table structure for table `phpbb_easymod`
# (-- since there is a break between the #'s, this is not
# a valid comment and invalidated the whole command --)
#

...notice the break between comment lines. After the break, as soon as the first # is detected, that signifies the start of the next MOD Template command.


General Layout
Sticking with the easy stuff, let's discuss how many SQL commands you can have. The answer is easy.... unlimited! You can have any number of MOD Template [ SQL ] declaration in a script, and you can have any number of valid SQL commands per [ SQL ] declaration. The only restriction is that you may only have one SQL command per line. Also each line should end with a semicolon ";"

#
#-----[ SQL ]---------------------------------
#

ALTER TABLE phpbb_categories ADD icon VARCHAR(255);

#
#-----[ SQL ]---------------------------------
#

ALTER TABLE phpbb_categories ADD icon VARCHAR(255); ALTER TABLE phpbb_forums ADD forum_link VARCHAR(255);

#
#-----[ SQL ]---------------------------------
#

ALTER TABLE phpbb_categories ADD icon VARCHAR(255);
ALTER TABLE phpbb_forums ADD forum_link VARCHAR(255);

Also note that commands are (pretty much) whitespace independent. So feel free to format commands anyway you want. The follow examples are all valid:

Code: Select all

#
#-----[ SQL ]---------------------------------
#
ALTER TABLE phpbb_categories ADD icon VARCHAR(255);

ALTER TABLE phpbb_forums ADD 
  forum_link VARCHAR(255);

ALTER TABLE phpbb_forums 
		ADD forum_link VARCHAR(255);

ALTER
TABLE `phpbb_users` ADD `user_autospell` 
	   	 TINYINT( 1 ) DEFAULT '1'

Command Syntax:
Whenever possible, the formatting is not case sensitive. We'll explain column definitions in the next section.

NOTE: for all commands, table_name could be enclosed tick marks like `table_name`. Generally, simple SQL can be cut and pasted from a phpMyAdmin export and used directly as it appears.


INSERT and UPDATE:
We'll start with the easy ones first. For UPDATE and INSERT, the normal phpBB DBAL is used. Simpy use whatever is valid for phpBB with just one exception. For INSERT, it is expected that the 3rd word is the table name, and for UPDATE it is expected that the 2nd word is the table name. For example, the following is required...

INSERT INTO table_name ...rest of command...
UPDATE table_name ...rest of command...

Create Table:
(reference) You may define as many columns as you wish when creating a table. See the column definition section for a complete explanation. Not implemented: "TEMPORARY", "IF NOT EXISTS", "LIKE", "INDEX", "CONSTRAINT", "UNIQUE", "FOREIGN KEY", "FULLTEXT", "SPATIAL", "CHECK", table_options, select_statement

CREATE TABLE table_name ( column_definition, ... ) ;

Drop Table:
(reference) Dropping only one table at a time is supported! Not implemented: "TEMPORARY", "IF EXISTS", "RESTRICT", "CASCADE"

DROP TABLE table_name ;

Add Column:
(reference) You may add multiple columns with one ALTER statement. The "COLUMN" reserved word cannot be used. Parenthesis "()" cannot be used around the column definition. You can use KEY, but this might change to INDEX instead. Not implemented: "COLUMN", "FIRST", "AFTER", "INDEX", "CONSTRAINT", "UNIQUE", "FULLTEXT", "SPATIAL", table_options

ALTER TABLE table_name ADD column_definition, ... ;

Modify Column:
(reference) You may modify only one column at a time (true of MySQL also). The "COLUMN" reserved word cannot be used. "ALTER" and "CHANGE" cannot be used in place of "MODIFY". Not implemented: "COLUMN", "ALTER", "CHANGE", "FIRST", "AFTER"

ALTER TABLE table_name MODIFY column_definition ;

Drop Column:
(reference) You may drop only one column at a time (true of MySQL also). Not implemented: "PRIMARY KEY", "INDEX", "FOREIGN KEY"

ALTER TABLE table_name DROP column_name ;

Column Definition:
See the types section for a complete explanation. The column_name can be enclosed in tick marks like `column_name` just as phpMyAdmin formats an export. "NULL/NOT NULL" and "DEFAULT" are optional, but will be set to defaults of "NULL" and either "DEFAULT ''" or "DEFAULT '0'" depending on the type. "AUTO_INCREMENT" is optional but must appear as the last parameter of the definition. Not implemented: "COMMENT", reference_definition

column_name type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT]

Keys, must appear on there own line and must not appear prior to the column definition. Important Note: keys are not yet implemented correctly yet and will need an overhaul.

[PRIMARY] KEY col_name (col_name)

Valid column definitions...

Code: Select all

SSN int(11) unsigned not null default '0' auto_increment
SSN int(11) UNSIGNED NOT NULL DEFAULT '0' AUTO_INCREMENT
SSN int(11) UNSIGNED DEFAULT '0' NOT NULL AUTO_INCREMENT
SSN int(11) UNSIGNED AUTO_INCREMENT
'SSN' int(11) UNSIGNED AUTO_INCREMENT

Invalid column definitions...

Code: Select all

SSN int(11) NOT NULL UNSIGNED DEFAULT '0' AUTO_INCREMENT
SSN int(11) NOT NULL DEFAULT '0' UNSIGNED AUTO_INCREMENT
SSN int(11) AUTO_INCREMENT UNSIGNED NOT NULL DEFAULT '0'
SSN int(11) AUTO_INCREMENT UNSIGNED

Types:
DOUBLE, FLOAT, and DECIMAL are really implemented correctly yet. Unfortunately, BLOBs are not implemented yet, but probably should be. Length and UNSIGNED are optional when appropriate. Not Implemented: "ZEROFILL", "BINARY", "ASCII", "UNICODE", spatial_type. Types not implemented: "REAL", "DATE", "TIME", "TIMESTAMP", "DATETIME", "NUMERIC", "TINYBLOB", "BLOB", "MEDIUMBLOB", "LONGBLOB", "ENUM", "SET"

TINYINT[(length)] [UNSIGNED]
SMALLINT[(length)] [UNSIGNED]
MEDIUMINT[(length)] [UNSIGNED]
INT[(length)] [UNSIGNED]
INTEGER[(length)] [UNSIGNED]
BIGINT[(length)] [UNSIGNED]
DOUBLE[(length,decimals)] [UNSIGNED]
FLOAT[(length,decimals)] [UNSIGNED]
DECIMAL(length,decimals) [UNSIGNED]
CHAR(length)
VARCHAR(length)
TINYTEXT
TEXT
MEDIUMTEXT
LONGTEXT

-Nuttzy

[Nuttzy99]

DB Translations:

Example MOD Template SQL commands:

Code: Select all

CREATE TABLE mytable ( 
  FirstName varchar(40) NOT NULL DEFAULT '', 
  `LastName` varchar(40), 
  DateOfBirth int(11) NULL, 
  `SSN` int(11) UNSIGNED DEFAULT '0' auto_increment, 
  KEY DateOfBirth (DateOfBirth), 
  PRIMARY KEY (SSN)
);

ALTER TABLE `mytable` ADD
  nut_test int(8) NULL DEFAULT '0', 
  abool tinyint(1) ;

ALTER TABLE mytable ADD nut_test2 int(8) ; 

ALTER TABLE mytable MODIFY 
  nut_test varchar(10) NULL DEFAULT '' auto_increment;

ALTER TABLE mytable DROP nut_test ;

DROP TABLE mytable;

Translations:
The following gives an idea of how the above examples will be translated into the specific databases. Note that there are no semicolons ";". Each command is separated by a line break and will be executed as it appears, therefore no need to delimit the output by semicolons. Note that the semicolons ARE present in the



Translated into MySQL:

Code: Select all

CREATE TABLE phpbb_mytable ( FirstName varchar(40) NOT NULL DEFAULT '' , LastName varchar(40) NULL DEFAULT '' , DateOfBirth int(11) NULL DEFAULT '0' , SSN int(11) UNSIGNED NULL DEFAULT '0' auto_increment, KEY DateOfBirth(DateOfBirth) , PRIMARY KEY(SSN) )

ALTER TABLE phpbb_mytable ADD nut_test int(8) NULL DEFAULT '0'

ALTER TABLE phpbb_mytable ADD abool tinyint(1) NULL DEFAULT '0'

ALTER TABLE phpbb_mytable ADD nut_test2 int(8) NULL DEFAULT '0'

ALTER TABLE phpbb_mytable MODIFY nut_test varchar(10) NULL DEFAULT '' auto_increment

ALTER TABLE phpbb_mytable DROP COLUMN nut_test

DROP TABLE phpbb_mytable

Translated into MSSQL:

NOTE: I KNOW THIS IS NOT ENTIRELY CORRECT. If you know how to fix it, reply on this post.

Code: Select all

CREATE TABLE [phpbb_mytable] ( [FirstName] [varchar] (40) NOT NULL, [LastName] [varchar] (40) NULL, [DateOfBirth] [int] NULL, [SSN] [int] UNSIGNED IDENTITY (1, 1) NULL) ON [PRIMARY] GO

ALTER TABLE [phpbb_mytable] WITH NOCHECK ADD CONSTRAINT [DF_phpbb_mytable_FirstName] DEFAULT ('') FOR [FirstName]

ALTER TABLE [phpbb_mytable] WITH NOCHECK ADD CONSTRAINT [DF_phpbb_mytable_LastName] DEFAULT ('') FOR [LastName]

ALTER TABLE [phpbb_mytable] WITH NOCHECK ADD CONSTRAINT [DF_phpbb_mytable_DateOfBirth] DEFAULT ('0') FOR [DateOfBirth]

ALTER TABLE [phpbb_mytable] WITH NOCHECK ADD CONSTRAINT [DF_phpbb_mytable_SSN] DEFAULT ('0') FOR [SSN]

CREATE INDEX [DateOfBirth_phpbb_mytable_index] ON [phpbb_mytable] ([DateOfBirth]) ON [PRIMARY] GO

ALTER TABLE [phpbb_mytable] WITH NOCHECK ADD CONSTRAINT [PK_phpbb_mytable] PRIMARY KEY CLUSTERED ( [SSN] ) ON [PRIMARY] GO

ALTER TABLE [phpbb_mytable] WITH NOCHECK ADD [nut_test] [int] NULL

ALTER TABLE [phpbb_mytable] WITH NOCHECK ADD [abool] [tinyint] NULL

ALTER TABLE [phpbb_mytable] WITH NOCHECK ADD CONSTRAINT [DF_phpbb_mytable_nut_test] DEFAULT ('0') FOR [nut_test]

ALTER TABLE [phpbb_mytable] WITH NOCHECK ADD CONSTRAINT [DF_phpbb_mytable_abool] DEFAULT ('0') FOR [abool]

ALTER TABLE [phpbb_mytable] WITH NOCHECK ADD [nut_test2] [int] NULL

ALTER TABLE [phpbb_mytable] WITH NOCHECK ADD CONSTRAINT [DF_phpbb_mytable_nut_test2] DEFAULT ('0') FOR [nut_test2]

ALTER TABLE [phpbb_mytable] ALTER COLUMN [nut_test] [varchar] (10) IDENTITY (1, 1) NULL

ALTER TABLE [phpbb_mytable] WITH NOCHECK ADD CONSTRAINT [DF_phpbb_mytable_nut_test] DEFAULT ('') FOR [nut_test]

ALTER TABLE phpbb_mytable DROP COLUMN nut_test

DROP TABLE phpbb_mytable

Translation into MS Access:
I'm pretty sure this is correct. However, AFAIK, there is no way to get Access to accept defaults via PHP. If someone figures it out, let me know!

Code: Select all

CREATE TABLE phpbb_mytable ( FirstName TEXT(40) NOT NULL , LastName TEXT(40) NULL , DateOfBirth int NULL , SSN COUNTER NULL CONSTRAINT PK_phpbb_mytable PRIMARY KEY )

CREATE INDEX DateOfBirth_phpbb_mytable_index ON phpbb_mytable (DateOfBirth)

ALTER TABLE phpbb_mytable ADD nut_test int NULL

ALTER TABLE phpbb_mytable ADD abool BIT NULL

ALTER TABLE phpbb_mytable ADD nut_test2 int NULL

ALTER TABLE phpbb_mytable ALTER COLUMN nut_test COUNTER NULL

ALTER TABLE phpbb_mytable DROP COLUMN nut_test

DROP TABLE phpbb_mytable

Translation into Postgresql:
I'm not sure if this is exactly right. Reply on this topic if you have suggestions. Also notice that dropping a column in Postgresql was not implemented. It appeared to me that I had to drop the entire table to do this. I felt this was too risky to attempt.

Code: Select all

CREATE SEQUENCE phpbb_mytable_SSN_seq start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1

CREATE TABLE phpbb_mytable ( FirstName varchar(40) NOT NULL DEFAULT '', LastName varchar(40) NULL DEFAULT '', DateOfBirth int4 NULL DEFAULT '0', SSN int4 UNSIGNED NULL DEFAULT nextval('phpbb_mytable_SSN_seq'::text), CONSTRAINT PK_phpbb_mytable PRIMARY KEY (SSN))

CREATE INDEX DateOfBirth_phpbb_mytable_index ON phpbb_mytable (DateOfBirth)

ALTER TABLE phpbb_mytable ADD nut_test int4 NULL DEFAULT '0'

ALTER TABLE phpbb_mytable ADD abool int2 NULL DEFAULT '0'

ALTER TABLE phpbb_mytable ADD nut_test2 int4 NULL DEFAULT '0'

CREATE SEQUENCE phpbb_mytable_nut_test_seq start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1

ALTER TABLE phpbb_mytable ALTER COLUMN nut_test varchar(10) NULL DEFAULT nextval('phpbb_mytable_nut_test_seq'::text)

ABORTED: [ALTER TABLE phpbb_mytable DROP COLUMN nut_test]
Dropping a field in postgresql was not implemented. Contact Nuttzy if you know how to safely do this without having to drop the whole table.

DROP TABLE phpbb_mytable

-Nuttzy

[chilling]

Are you proposing to add the RENAME TABLE command ?

For instance I am writing a MOD to bring osCommerce into phpBB and as they do not have a Table_Prefix I want to add this to the implementation. All works fine apart from that there has to manual implementation of the renaming of the tables.

[chilling]

Along the same vain is it possible to pick up the $table_prefix value from the ./config.php file and put that into the SQL commands .... just in case the end users have not used 'phpbb_' as the prefix

[chilling]

It appears that the "IF EXISTS" command is not supported, so doing something like this is not possible.

Code: Select all

DROP TABLE IF EXISTS mysql_table;
CREATE TABLE mysql_table(
  col1_id int NOT NULL auto_increment,
  col2 varchar(128) NOT NULL,
  col3 varchar(48) NOT NULL,
  PRIMARY KEY (col1_id)
);

If I just write

Code: Select all

DROP TABLE mysql_table;

and the table does not exist then the SQL statement should give an error ...?

Is the "IF EXISTS" command going to be included?

[chilling]

Just yet another suggestion ...

Instead of having the SQL statements within the [MOD name].txt instructions file it would make it much more manageable for large MODs if you save the SQL in a seperate file and then easyMOD calls the extra SQL files. (The MOD file I am writing a the moment is about 4,000 lines with the SQl statements)

Even better would be if you could have two .sql files ... one with the schema and the other with demo data, ie

• [MOD name]_schema.sql; and
• [MOD name]_data.sql

and then have an option as part of the EasyMOD routine which asks if you would like to install the demo data as well.