RE: [Phpgroupware-developers] proposal branch - reserved names

[Kai Hofmann]

Just for information (0.9.16 release):

Column name '_oid' is a reserved keyword in table 'phpgw_eldaptir_schema'
(not
really reserved, but _ is not allowed as first character.
Column name 'access' is a reserved keyword in table 'phpgw_addressbook'
Column name 'access' is a reserved keyword in table 'phpgw_contact'
Column name 'action' is a reserved keyword in table 'phpgw_fud_ses'
Column name 'alias' is a reserved keyword in table 'phpgw_fud_users'
Column name 'class' is a reserved keyword in table 'phpgw_contact_comm_type'
Column name 'count' is a reserved keyword in table 'phpgw_fud_poll_opt'
Column name 'count' is a reserved keyword in table 'phpgw_wiki_links'
Column name 'data' is a reserved keyword in table 'phpgw_async'
Column name 'data' is a reserved keyword in table 'phpgw_fud_ses'
Column name 'depth' is a reserved keyword in table 'phpgw_forum_threads'
Column name 'host' is a reserved keyword in table 'phpgw_javassh_servers'
Column name 'length' is a reserved keyword in table 'phpgw_fud_msg'
Column name 'length' is a reserved keyword in table 'phpgw_fud_pmsg'
Column name 'link' is a reserved keyword in table 'phpgw_headlines_cached'
Column name 'link' is a reserved keyword in table 'phpgw_wiki_links'
Column name 'next' is a reserved keyword in table 'phpgw_async'
Column name 'path' is a reserved keyword in table 'news_msg'
Column name 'prefix' is a reserved keyword in table 'phpgw_contact_person'
Column name 'prefix' is a reserved keyword in table 'phpgw_wiki_interwiki'
Column name 'prefix' is a reserved keyword in table 'phpgw_wiki_sisterwiki'
Column name 'size' is a reserved keyword in table 'phpgw_vfs'
Column name 'stamp' is a reserved keyword in table 'phpgw_fud_msg_report'
Column name 'stamp' is a reserved keyword in table
'phpgw_fud_thread_rate_track'
Column name 'state' is a reserved keyword in table 'phpgw_contact_addr'
Column name 'state' is a reserved keyword in table
'phpgw_sitemgr_categories_state'
Column name 'state' is a reserved keyword in table 'phpgw_sitemgr_content'
Column name 'state' is a reserved keyword in table 'phpgw_sitemgr_pages'
Column name 'time' is a reserved keyword in table 'phpgw_wiki_pages'
Column name 'time' is a reserved keyword in table 'phpgw_wiki_rate'
Column name 'uid' is a reserved keyword in table 'news_msg'
Column name 'uid' is a reserved keyword in table 'phpgw_cal'


to find these things I added the following code to my local
class.schema_proc.inc.php:

within the _GetTableSQL  while loop as first statement:

if ($this->isReservedKeyword($sFieldName))
{
        echo "<strong>Column name '$sFieldName' is a reserved keyword in
table
'$sTableName'</strong><br />\n";
}


and an additional function:

                function isReservedKeyword($keyword)
                {
                                        static $reserved_keywords = array(
'ABS', 'ABSOLUTE', 'ACCESS', 'ACOS', 'ACTION', 'ADD', 'ADDDATE', 'ADDTIME',
'ADMIN', 'AFTER', 'AGGREGATE', 
'ALIAS', 'ALL', 'ALLOCATE', 'ALPHA', 'ALTER', 'ANALYSE', 'ANALYZE', 'AND',
'ANY', 'ARE', 'ARRAY', 'AS', 'ASC',
'ASCII', 'ASENSITIVE', 'ASIN', 'ASSERTION', 'AT', 'ATAN', 'ATAN2',
'AUTHORIZATION', 'AUTO_INCREMENT', 'AVG',
'BDB', 'BEFORE', 'BEGIN', 'BERKELEYDB', 'BETWEEN', 'BIGINT', 'BINARY',
'BIT',
'BIT_LENGTH', 'BLOB', 'BOOLEAN',
'BOTH', 'BREADTH', 'BY', 'BYTE',
'CALL', 'CASCADE', 'CASCADED', 'CASE', 'CAST', 'CATALOG', 'CEIL', 'CEILING',
'CHANGE', 'CHAR', 'CHARACTER',
'CHARACTER_LENGTH', 'CHAR_LENGTH', 'CHECK', 'CHR', 'CLASS', 'CLOB', 'CLOSE',
'COALESCE', 'COLLATE', 'COLLATION',
'COLUMN', 'COLUMNS', 'COMMIT', 'COMPLETION', 'COMPRESS', 'CONCAT',
'CONDITION',
'CONNECT', 'CONNECTION', 'CONSTRAINT',
'CONSTRAINTS', 'CONSTRUCTOR', 'CONTINUE', 'CONVERT', 'CORRESPONDING', 'COS',
'COSH', 'COT', 'COUNT', 'CREATE', 'CROSS',
'CUBE', 'CURDATE', 'CURRENT', 'CURRENT_DATE', 'CURRENT_PATH',
'CURRENT_ROLE',
'CURRENT_TIME', 'CURRENT_TIMESTAMP',
'CURRENT_USER', 'CURSOR', 'CURTIME', 'CYCLE',
'DATA', 'DATABASE', 'DATABASES', 'DATE', 'DATEDIFF', 'DAY', 'DAYNAME',
'DAYOFMONTH', 'DAYOFWEEK', 'DAYOFYEAR',
'DAY_HOUR', 'DAY_MICROSECOND', 'DAY_MINUTE', 'DAY_SECOND', 'DBYTE',
'DEALLOCATE', 'DEC', 'DECIMAL', 'DECLARE',
'DECODE', 'DEFAULT', 'DEFERRABLE', 'DEFERRED', 'DEGREES', 'DELAYED',
'DELETE',
'DEPTH', 'DEREF', 'DESC', 'DESCRIBE',
'DESCRIPTOR', 'DESTROY', 'DESTRUCTOR', 'DETERMINISTIC', 'DIAGNOSTICS',
'DICTIONARY', 'DIGITS', 'DISCONNECT', 'DISTINCT',
'DISTINCTROW', 'DIV', 'DO', 'DOMAIN', 'DOUBLE', 'DROP', 'DYNAMIC',
'EACH', 'EBCDIC', 'ELSE', 'ELSEIF', 'ENCLOSED', 'END', 'END-EXEC', 'EQUALS',
'ESCAPE', 'ESCAPED', 'EVERY', 'EXCEPT',
'EXCEPTION', 'EXEC', 'EXECUTE', 'EXISTS', 'EXIT', 'EXP', 'EXPAND',
'EXPLAIN',
'EXTERNAL', 'EXTRACT',
'FALSE', 'FETCH', 'FIELDS', 'FILE', 'FIRST', 'FIXED', 'FLOAT', 'FLOOR',
'FOR',
'FORCE', 'FOREIGN', 'FOUND', 'FRAC_SECOND',
'FREE', 'FREEZE', 'FROM', 'FULL', 'FULLTEXT', 'FUNCTION',
'GENERAL', 'GET', 'GET_OBJECTNAME', 'GET_OWNER', 'GLOBAL', 'GO', 'GOTO',
'GRANT', 'GRAPHIC', 'GREATEST', 'GROUP', 'GROUPING',
'HAVING', 'HEX', 'HEXTORAW', 'HIGH_PRIORITY', 'HOST', 'HOUR',
'HOUR_MICROSECOND', 'HOUR_MINUTE', 'HOUR_SECOND',
'IDENTITY', 'IF', 'IFNULL', 'IGNORE', 'ILIKE', 'IMMEDIATE', 'IN',
'INCREMENT',
'INDEX', 'INDICATOR', 'INFILE', 'INITCAP',
'INITIAL', 'INITIALIZE', 'INITIALLY', 'INNER', 'INNODB', 'INOUT', 'INPUT',
'INSENSITIVE', 'INSERT', 'INT', 'INTEGER',
'INTERNAL', 'INTERSECT', 'INTERVAL', 'INTO', 'IO_THREAD', 'IS', 'ISNULL',
'ISOLATION', 'ITERATE', 
'JOIN', 
'KEY', 'KEYS', 'KILL',
'LANGUAGE', 'LARGE', 'LAST', 'LATERAL', 'LCASE', 'LEADING', 'LEAST',
'LEAVE',
'LEFT', 'LENGTH', 'LESS', 'LEVEL',
'LFILL', 'LIKE', 'LIMIT', 'LINES', 'LINK', 'LIST', 'LN', 'LOAD', 'LOCAL',
'LOCALTIME', 'LOCALTIMESTAMP', 'LOCATE',
'LOCATOR', 'LOCK', 'LOG', 'LOG10', 'LONG', 'LONGBLOB', 'LONGFILE',
'LONGTEXT',
'LOOP', 'LOWER', 'LOW_PRIORITY', 'LPAD', 'LTRIM',
'MAKEDATE', 'MAKETIME', 'MAP', 'MAPCHAR', 'MASTER_SERVER_ID', 'MATCH',
'MAX',
'MAXEXTENTS', 'MBCS', 'MEDIUMBLOB', 'MEDIUMINT',
'MEDIUMTEXT', 'MICROSECOND', 'MIDDLEINT', 'MIN', 'MINUTE',
'MINUTE_MICROSECOND',
'MINUTE_SECOND', 'MOD', 'MODIFIES', 'MODIFY',
'MODULE', 'MONTH', 'MONTHNAME', 
'NAMES', 'NATIONAL', 'NATURAL', 'NCHAR', 'NCLOB', 'NEW', 'NEXT', 'NO',
'NOAUDIT', 'NOCOMPRESS', 'NONE', 'NOROUND', 'NOT',
'NOTNULL', 'NOW', 'NO_WRITE_TO_BINLOG', 'NULL', 'NULLIF', 'NUM', 'NUMERIC',
'OBJECT', 'OCTET_LENGTH', 'OF', 'OFF', 'OFFLINE', 'OFFSET', 'OLD', 'ON',
'ONLINE', 'ONLY', 'OPEN', 'OPERATION', 'OPTIMIZE',
'OPTION', 'OPTIONALLY', 'OR', 'ORDER', 'ORDINALITY', 'OUT', 'OUTER',
'OUTFILE',
'OUTPUT', 'OVERLAPS',
'PACKED', 'PAD', 'PARAMETER', 'PARAMETERS', 'PARTIAL', 'PATH', 'PI',
'PLACING',
'POSITION', 'POSTFIX', 'POWER', 'PRECISION',
'PREFIX', 'PREORDER', 'PREPARE', 'PRESERVE', 'PREV', 'PRIMARY', 'PRIOR',
'PRIVILEGES', 'PROCEDURE', 'PUBLIC', 'PURGE',
'RADIANS', 'READ', 'READS', 'REAL', 'RECURSIVE', 'REF', 'REFERENCES',
'REFERENCING', 'REGEXP', 'REJECT', 'RELATIVE', 'RENAME', 
'REPEAT', 'REPLACE', 'REQUIRE', 'RESTRICT', 'RESULT', 'RETURN', 'RETURNS',
'REVOKE', 'RFILL', 'RIGHT', 'RLIKE', 'ROLE', 'ROLLBACK',
'ROLLUP', 'ROUND', 'ROUTINE', 'ROW', 'ROWID', 'ROWNO', 'ROWS', 'RPAD',
'RTRIM',
'SAVEPOINT', 'SCHEMA', 'SCOPE', 'SCROLL', 'SEARCH', 'SECOND',
'SECOND_MICROSECOND', 'SECTION', 'SELECT', 'SELUPD', 'SENSITIVE',
'SEPARATOR', 'SEQUENCE', 'SERIAL', 'SESSION', 'SESSION_USER', 'SET', 'SETS',
'SHOW', 'SIGN', 'SIMILAR', 'SIN', 'SINH', 'SIZE',
'SMALLINT', 'SOME', 'SONAME', 'SOUNDEX', 'SPACE', 'SPATIAL', 'SPECIFIC',
'SPECIFICTYPE', 'SQL', 'SQLCODE', 'SQLERROR', 'SQLEXCEPTION',
'SQLSTATE', 'SQLWARNING', 'SQL_BIG_RESULT', 'SQL_CALC_FOUND_ROWS',
'SQL_SMALL_RESULT', 'SQL_TSI_DAY', 'SQL_TSI_FRAC_SECOND', 
'SQL_TSI_HOUR', 'SQL_TSI_MINUTE', 'SQL_TSI_MONTH', 'SQL_TSI_QUARTER',
'SQL_TSI_SECOND', 'SQL_TSI_WEEK', 'SQL_TSI_YEAR', 'SQRT',
'SSL', 'STAMP', 'START', 'STARTING', 'STATE', 'STATEMENT', 'STATIC',
'STATISTICS', 'STDDEV', 'STRAIGHT_JOIN', 'STRIPED', 'STRUCTURE',
'SUBDATE', 'SUBSTR', 'SUBSTRING', 'SUBTIME', 'SUCCESSFUL', 'SUM', 'SYSDBA',
'SYSTEM_USER',
'TABLE', 'TABLES', 'TAN', 'TANH', 'TEMPORARY', 'TERMINATE', 'TERMINATED',
'THAN', 'THEN', 'TIME', 'TIMEDIFF', 'TIMESTAMP', 'TIMESTAMPADD',
'TIMESTAMPDIFF', 'TIMEZONE', 'TIMEZONE_HOUR', 'TIMEZONE_MINUTE', 'TINYBLOB',
'TINYINT', 'TINYTEXT', 'TO', 'TOIDENTIFIER', 'TRAILING',
'TRANSACTION', 'TRANSLATE', 'TRANSLATION', 'TREAT', 'TRIGGER', 'TRIM',
'TRUE',
'TRUNC', 'TRUNCATE',
'UCASE', 'UID', 'UNDER', 'UNDO', 'UNICODE', 'UNION', 'UNIQUE', 'UNKNOWN',
'UNLOCK', 'UNNEST', 'UNSIGNED', 'UPDATE', 'UPPER',
'USAGE', 'USE', 'USER', 'USERGROUP', 'USER_RESOURCES', 'USING', 'UTCDATE',
'UTCDIFF', 'UTC_DATE', 'UTC_TIME', 'UTC_TIMESTAMP',
'VALIDATE', 'VALUE', 'VALUES', 'VARBINARY', 'VARCHAR', 'VARCHARACTER',
'VARGRAPHIC', 'VARIABLE', 'VARIANCE', 'VARYING', 'VERBOSE', 'VIEW',
'WEEK', 'WEEKOFYEAR', 'WHEN', 'WHENEVER', 'WHERE', 'WHILE', 'WITH',
'WITHOUT',
'WORK', 'WRITE',
'XOR',
'YEAR', 'YEAR_MONTH',
'ZEROFILL', 'ZONE', 'ZONED'
                );
                        
        
return(in_array(strtoupper($keyword),$reserved_keywords,true) ||
($keyword{0}
== '_'));
                }


this should be added for debugging in head and the new framework.

Greetings

   Kai

-- 
*****    Open Source und Linux im professionellen Einsatz    *****
**  komplexe Mailserver, Groupware, Office: sprechen Sie uns an **
Dipl.-Inform. Kai Hofmann                    Team Softwarelösungen
pro|business AG, EXPO Plaza 1 (Deutscher Pavillon), 30539 Hannover
E-Mail: address@hidden,   Tel.: 0511/60066-332, Fax: -355
WWW: http://www.probusiness.de/