pgworksheet_1.9/pgw/Syntax.py

   1 #!/usr/bin/env python
   2 # -*- coding: latin-1; -*-
   3 #
   4 # PgWorksheet - PostgreSQL Front End
   5 # http://pgworksheet.projects.postgresql.org/
   6 #
   7 # Copyright © 2004-2008 Henri Michelon & CML http://www.e-cml.org/
   8 #
   9 # This program is free software; you can redistribute it and/or
  10 # modify it under the terms of the GNU General Public License
  11 # as published by the Free Software Foundation; either version 2
  12 # of the License, or (at your option) any later version.
  13 #
  14 # This program is distributed in the hope that it will be useful,
  15 # but WITHOUT ANY WARRANTY; without even the implied warranty of
  16 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  17 # GNU General Public License for more details (read LICENSE.txt).
  18 #
  19 # You should have received a copy of the GNU General Public License
  20 # along with this program; if not, write to the Free Software
  21 # Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.
  22 #
  23 # $Id: Syntax.py,v 1.30 2008/03/12 20:26:23 hmichelon Exp $
  24 #
  25 import gtk.gdk
  26 import pango
  27 import pgw
  28 import pgw.Lexical
  29
  30 # built-in data types and extensions
  31 TYPES = [ 'BIGINT', 'INT8', 'BIGSERIAL', 'SERIAL8', 'BIT', 'VARYING',
  32           'VARBIT', 'BOOL', 'BOOLEAN', 'BOX', 'BYTEA', 'CHARACTER', 'VARCHAR',
  33           'CHAR', 'CIDR', 'CIRCLE', 'DATE', 'DOUBLE', 'PRECISION',
  34           'INET', 'INTEGER', 'FLOAT8', 'INT', 'INT4', 'INTERVAL',
  35           'LINE', 'LSEG', 'MACADDR', 'MONEY', 'NUMERIC', 'DECIMAL',
  36           'PATH', 'POINT', 'POLYGON', 'REAL', 'FLOAT4', 'SMALLINT',
  37           'INT2', 'SERIAL', 'SERIAL4', 'TEXT','TIME', 'TIMESTAMP',
  38           'TIMETZ', 'TIMESTAMPTZ', 'WITHOUT', 'TIME', 'ZONE'
  39         ]
  40
  41 # special constants
  42 SPECIALS = [ 'FALSE', 'NULL', 'TRUE', 'UNKNOWN', 'ALL', 'ANY', 'SOME' ]
  43
  44
  45 # named operators, constructs, conditionals and subqueries
  46 OPERATORS2 = [ 'OVERLAPS', 'AND', 'OR', 'NOT', 'BETWEEN', 'IS', 'ISNULL',
  47                'NOTNULL', 'CAST', 'LIKE', 'ILIKE', 'SIMILAR',
  48                'EXISTS', 'IN',
  49                'CASE', 'WHEN', 'THEN', 'ELSE', 'END',
  50                'UNION', 'ARRAY',
  51              ]
  52
  53 # SQL and PostgreSQL statements
  54 STATEMENTS = [ 'ABORT', 'ALTER', 'ANALYSE', 'BEGIN', 'CHECKPOINT', 'CLOSE',
  55                'CLUSTER', 'COMMENT', 'COMMIT', 'COPY', 'CREATE', 'DEALLOCATE',
  56                'DECLARE', 'DELETE', 'DROP', 'END', 'EXECUTE', 'EXPLAIN',
  57                'FETCH', 'GRANT', 'INSERT', 'LISTEN', 'LOAD', 'LOCK', 'MOVE',
  58                'NOTIFY', 'PREPARE', 'REINDEX', 'RELEASE', 'RESET', 'REVOKE',
  59                'ROLLBACK', 'SAVEPOINT', 'SELECT', 'SET', 'SHOW', 'START',
  60                'TRUNCATE', 'UNLISTEN', 'UPDATE', 'VACUUM' ]
  61
  62 # built-in functions
  63 BUILTINS  = [
  64               # MATH
  65               'ABS', 'CBTR', 'CEIL', 'CEILING', 'DEGREES', 'EXP', 'FLOOR', 'LN',
  66               'LOG', 'MOD', 'PI', 'POWER', 'RADIANS', 'RANDOM', 'ROUND',
  67               'SETSEED', 'SIGN', 'SQRT', 'TRUNC', 'WIDTH_BUCKET', 'ACOS',
  68               'ASIN', 'ATAN', 'ATAN2', 'COS', 'COT', 'SIN', 'TAN',
  69               # STRING
  70               'BIT_LENGTH', 'CHAR_LENGTH', 'CONVERT', 'LOWER', 'OCTET_LENGTH',
  71               'OVERLAY', 'POSITION', 'SUBSTRING', 'TRIM', 'UPPER', 'ASCII',
  72               'BTRIM', 'CHR', 'DECODE', 'ENCODE', 'INITCAP', 'LENGTH',
  73               'LPAD', 'LTRIM', 'MD5', 'PG_CLIENT_ENCODING', 'QUOTE_IDENT',
  74               'QUOTE_LITERAL', 'REPEAT', 'RPAD', 'RTRIM', 'SPLIT_PART',
  75               'STRPOS', 'TO_ASCII', 'TO_HEX', 'TRANSLATE',
  76               # BSTRING
  77               'GET_BYTE', 'SET_BYTE', 'GET_BIT', 'SET_BIT',
  78               # DATE
  79               'TO_CHAR', 'TO_DATE', 'TO_TIMESTAMP', 'TO_NUMBER',
  80               'AGE', 'DATE_PART', 'DATE_TRUNC', 'EXTRACT', 'ISFINITE',
  81               'NOW', 'TIMEOFDAY',
  82               # GEOMETRIC
  83               'AREA', 'BOX_INTERSECT', 'CENTER', 'DIAMETER', 'HEIGHT',
  84               'ISCLOSED', 'ISOPEN', 'LENGTH', 'NPOINTS', 'PCLOSE',
  85               'POPEN', 'RADIUS', 'WIDTH', 'BOX', 'CIRCLE', 'LSEG',
  86               'PATH', 'POINT', 'POLYGON',
  87               # NETWORK
  88               'BROADCAST', 'HOST', 'MASKLEN', 'SET_MASKLEN',
  89               'NETMASK', 'HOSTMASK', 'NETWORK', 'TEXT', 'ABBREV',
  90               'FAMILY', 'TRUNC',
  91               # SEQUENCES
  92               'NEXTVAL', 'CURRVAL',
  93               # CONDITIONAL
  94               'COALESCE', 'NULLIF',
  95               # ARRAY
  96               'ARRAY_CAT', 'ARRAY_APPEND', 'ARRAY_PREPEND', 'ARRAY_DIMS',
  97               'ARRAY_LOWER', 'ARRAY_UPPER', 'ARRAY_TO_STRING',
  98               'STRING_TO_ARRAY'
  99               # AGGREGATE
 100               'AVG', 'BIT_AND', 'BIT_OR', 'BOOL_AND', 'BOOL_OR',
 101               'COUNT', 'EVERY', 'MAX', 'MIN', 'STDDEV', 'SUM',
 102               'VARIANCE'
 103               # SETS
 104               'GENERATE_SERIES',
 105               # SYSTEM
 106               'CURRENT_DATABASE', 'CURRENT_SCHEMA',
 107               'INET_CLIENT_ADDR', 'INET_CLIENT_PORT',
 108               'INET_SERVER_ADDR', 'INET_SERVER_PORT', 'VERSION',
 109               'HAS_TABLE_PRIVILEGE', 'HAS_DATABASE_PRIVILEGE',
 110               'HAS_FUNCTION_PRIVILEGE', 'HAS_LANGUAGE_PRIVILEGE',
 111               'HAS_SCHEMA_PRIVILEGE', 'HAS_TABLESPACE_PRIVILEGE'
 112               'PG_TABLE_IS_VISIBLE', 'PG_TYPE_IS_VISIBLE',
 113               'PG_FUNCTION_IS_VISIBLE', 'PG_OPERATOR_IS_VISIBLE',
 114               'PG_OPCLASS_IS_VISIBLE', 'PG_CONVERSION_IS_VISIBLE',
 115               'FORMAT_TYPE', 'PG_GET_VIEWDEF', 'PG_GET_RULEGET',
 116               'PG_GET_INDEXDEF', 'PG_GET_TRIGGERDEF',
 117               'PG_GET_CONSTRAINTDEF', 'PG_GET_EXPR', 'PG_GET_USERBYID',
 118               'PG_GET_SERIAL_SEQUENCE', 'PG_TABLESPACE_DATABASES',
 119               'OBJ_DESCRIPITION',
 120               # SYSTEM ADMIN
 121               'CURRENT_SETTINGS', 'SET_CONFIG', 'PG_CANCEL_BACKEND',
 122               'PG_START_BACKUP', 'PG_STOP_BACKUP',
 123
 124             ]
 125
 126 # built-in functions that can be used without ()
 127 # or conditional expressions
 128 BUILTINS2 = [ # DATE
 129               'CURRENT_DATE', 'CURRENT_TIME', 'CURRENT_TIMESTAMP',
 130               'LOCALTIME', 'LOCALTIMESTAMP',
 131               # SYSTEM
 132               'CURRENT_USER', 'SESSION_USER', 'USER',
 133             ]
 134
 135
 136 """
 137 # PL/PgSQL keywords
 138 PLPGSQL = [ # structure
 139             'DECLARE', 'BEGIN', 'END',
 140             # declarations
 141             'RECORD', 'DEFAULT', 'CONSTANT', 'ALIAS', 'FOR', 'TYPE', 'ROWTYPE',
 142             'RENAME', 'TO',
 143             # Statements
 144             'FOUND', 'PERFORM', 'GET', 'DIAGNOSTICS',
 145             # Control Structures
 146             'RETURN', 'NEXT', 'IF', 'THEN', 'ELSE', 'END', 'ELSIF', 'LOOP',
 147             'EXIT', 'WHILE', 'REVERSE', 'IN', 'EXCEPTION', 'WHEN',
 148             # Cursors
 149             'CURSOR', 'OPEN', 'EXECUTE', 'FETCH', 'INTO', 'CLOSE',
 150             # Errors
 151             'RAISE', 'DEBUG', 'LOG', 'INFO', 'NOTICE', 'WARNING', 'EXCEPTION',
 152             # Trigger Procedures
 153             'NEW', 'OLD', 'TG_NAME', 'TG_WHEN', 'TG_LEVEL', 'TG_OP',
 154             'TG_RELID', 'TG_RELNAME', 'TG_NARGS', 'TG_ARGV',
 155           ]
 156 """
 157
 158 # keywords for each statement
 159 KEYWORDS = { 'ABORT'    : [ 'WORK', 'TRANSACTION' ],
 160              'ALTER'    : { 'AGGREGATE'   : [ [ 'RENAME', 'TO' ],
 161                                               [ 'OWNER', 'TO' ]
 162                                             ],
 163                             'CONVERSION'  : [ [ 'RENAME', 'TO' ],
 164                                               [ 'OWNER', 'TO' ]
 165                                             ],
 166                             'DATABASE'    : [ 'SET', 'TO', 'DEFAULT',
 167                                               'RESET', [ 'RENAME', 'TO' ],
 168                                               [ 'OWNER', 'TO' ]
 169                                             ],
 170                             'DOMAIN'      : [ [ 'SET', 'DEFAULT' ],
 171                                               [ 'DROP', 'DEFAULT' ],
 172                                               'ADD',
 173                                               [ 'DROP', 'CONSTRAINT' ],
 174                                               'RESTRICT', 'CASCADE',
 175                                               [ 'OWNER', 'TO' ]
 176                                             ],
 177                             'FUNCTION'    : [ [ 'RENAME', 'TO' ],
 178                                               [ 'OWNER', 'TO' ]
 179                                             ],
 180                             'GROUP'       : [ [ 'ADD', 'USER' ],
 181                                               [ 'DROP', 'USER' ],
 182                                               [ 'RENAME', 'TO' ]
 183                                             ],
 184                             'INDEX'       : [ [ 'RENAME', 'TO' ],
 185                                               [ 'OWNER', 'TO' ],
 186                                               [ 'SET', 'TABLESPACE' ]
 187                                             ],
 188                             'LANGUAGE'    : [ [ 'RENAME', 'TO' ] ],
 189                             'OPERATOR'    : [ 'NONE', [ 'OWNER', 'TO' ],
 190                                               'CLASS', 'USING'
 191                                             ],
 192                             'SCHEMA'      : [ [ 'RENAME', 'TO' ],
 193                                               [ 'OWNER', 'TO' ]
 194                                             ],
 195                             'SEQUENCE'    : [ [ 'INCREMENT', 'BY' ],
 196                                               'MINVALUE', 'MAXVALUE',
 197                                               [ 'RESTART', 'WITH' ], 'CACHE',
 198                                               'NO', 'CYCLE'
 199                                             ],
 200                             'TABLE'       : [ 'ONLY', [ 'RENAME', 'COLUMN' ],
 201                                                [ 'RENAME', 'TO' ], 'TO',
 202                                                [ 'ADD', 'COLUMN' ],
 203                                                [ 'DROP', 'COLUMN' ], 'RESTRICT',
 204                                                'CASCADE', 'TYPE', 'USING',
 205                                                [ 'ALTER', 'COLUMN' ],
 206                                                [ 'SET', 'DEFAULT' ],
 207                                                [ 'DROP', 'DEFAULT' ],
 208                                                'SET', 'DROP',
 209                                                [ 'SET', 'STATISTICS' ],
 210                                                [ 'SET', 'STORAGE' ], 'PLAIN',
 211                                                'EXTERNAL', 'EXTERNAL', 'MAIN',
 212                                                [ 'DROP', 'CONSTRAINT' ],
 213                                                [ 'CLUSTER', 'ON' ],
 214                                                [ 'SET', 'WITHOUT', 'CLUSTER' ],
 215                                                [ 'SET', 'WITHOUT', 'OIDS' ],
 216                                                [ 'OWNER', 'TO' ],
 217                                                [ 'SET', 'TABLESPACE' ],
 218                                                'CHECK',
 219                                                [ 'FOREIGN', 'KEY' ], 'CASCADE',
 220                                                [ 'PRIMARY', 'KEY' ],
 221                                                [ 'ON', 'DELETE' ],
 222                                                [ 'ON', 'UPDATE' ], 'REFERENCES',
 223                                              ],
 224                             'TABLESPACE'  : [ [ 'RENAME', 'TO' ],
 225                                               [ 'OWNER', 'TO' ]
 226                                             ],
 227                             'TRIGGER'     : [ 'ON', [ 'RENAME', 'TO' ] ],
 228                             'TYPE'        : [ [ 'OWNER', 'TO' ] ],
 229                             'USER'        : [ 'WITH', 'CREATEDB', 'NOCREATEDB',
 230                                               'CREATEUSER', 'NOCREATEUSER',
 231                                               'ENCRYPTED', 'UNENCRYPTED',
 232                                               'PASSWORD', [ 'VALID', 'UNTIL' ],
 233                                               [ 'RENAME', 'TO' ], 'SET',
 234                                               'TO', 'DEFAULT', 'RESET'
 235                                             ],
 236                           },
 237              'ANALYZE'  : [ 'VERBOSE' ],
 238              'BEGIN'    : [ 'WORK', 'TRANSACTION', [ 'ISOLATION', 'LEVEL' ],
 239                             'SERIALIZABLE', [ 'REPEATABLE', 'READ' ],
 240                             [ 'READ', 'COMMITTED' ], [ 'READ', 'UNCOMMITTED' ],
 241                             [ 'READ', 'WRITE' ], [ 'READ', 'ONLY' ]
 242                           ],
 243              'CHECKPOINT' : [],
 244              'CLOSE'    : [],
 245              'CLUSTER'  : [ 'ON' ],
 246              'COMMENT'  : { 'ON' : [ 'TABLE', 'COLUMN', 'AGGREGATE', 'CAST',
 247                                      'AS', 'CONSTRAINT', 'ON', 'CONVERSION',
 248                                      'DATABASE', 'DOMAIN', 'FUNCTION', 'INDEX',
 249                                      [ 'LARGE', 'OBJECT' ], 'OPERATOR',
 250                                      [ 'OPERATOR', 'CLASS' ], 'USING',
 251                                      'PROCEDURAL', 'LANGUAGE', 'RULE',
 252                                      'SCHEMA', 'SEQUENCE', 'TRIGGER',
 253                                      'TYPE', 'VIEW', 'IS'
 254                                    ]
 255                           },
 256              'COMMIT'   : [ 'WORK', 'TRANSACTION' ],
 257              'COPY'     : [ 'FROM', 'STDIN', 'WITH', 'BINARY', 'OIDS',
 258                             'DELIMITER', 'CSV', 'AS', 'ESCAPE'
 259                             'FORCE', 'TO', 'STDOUT', 'QUOTE'
 260                           ],
 261              'CREATE'   : { 'AGGREGATE'   : [ 'BASETYPE', 'SFUNC', 'STYPE',
 262                                               'FINALFUNC', 'INITCOND' ],
 263                             'CAST'        : [ [ 'WITH', 'FUNCTION' ],
 264                                               [ 'AS', 'ASSIGNMENT' ],
 265                                               [ 'AS', 'IMPLICIT' ],
 266                                               [ 'WITHOUT', 'FUNCTION']
 267                                             ],
 268                             'CONSTRAINT'  : [ 'TRIGGER', 'AFTER', 'ON',
 269                                               [ 'FOR', 'EACH', 'ROW', 'EXECUTE'
 270                                                 'PROCEDURE' ]
 271                                             ],
 272                             'CONVERSION'  : [ 'FOR', 'TO', 'FROM' ],
 273                             'DEFAULT'     : [ 'CONVERSION', 'FOR',
 274                                               'TO', 'FROM' ],
 275                             'DATABASE'    : [ 'WITH', 'OWNER', 'TEMPLATE',
 276                                               'ENCODING', 'TABLESPACE',
 277                                               'DEFAULT' ],
 278                             'DOMAIN'      : [ 'AS', 'DEFAULT', 'CONSTRAINT',
 279                                               'CHECK'
 280                                             ],
 281                             'OR'          : [ 'REPLACE', 'FUNCTION',
 282                                               'RETURNS', 'LANGUAGE', 'AS',
 283                                               'IMMUTABLE', 'STABLE',
 284                                               'VOLATILE', [ 'CALLED', 'ON',
 285                                               'NULL', 'INPUT' ], [ 'RETURNS',
 286                                               'NULL', 'ON', 'NULL', 'INPUT' ],
 287                                               'STRICT', 'EXTERNAL',
 288                                               [ 'SECURITY', 'INVOKER' ],
 289                                               [ 'SECURITY', 'DEFINER' ], 'WITH',
 290                                               # XXX: for RULE
 291                                               'RULE',
 292                                               'AS', 'ON', 'TO', 'WHERE',
 293                                               'DO', 'ALSO', 'INSTEAD',
 294                                               'NOTHING',
 295                                               'VIEW'
 296                                             ],
 297                             'FUNCTION'    : [ 'RETURNS', 'LANGUAGE', 'AS',
 298                                               'IMMUTABLE', 'STABLE',
 299                                               'VOLATILE', [ 'CALLED', 'ON',
 300                                               'NULL', 'INPUT' ], [ 'RETURNS',
 301                                               'NULL', 'ON', 'NULL', 'INPUT' ],
 302                                               'STRICT', 'EXTERNAL',
 303                                               [ 'SECURITY', 'INVOKER' ],
 304                                               [ 'SECURITY', 'DEFINER' ], 'WITH'
 305                                             ],
 306                             'GROUP'       : [ 'WITH', 'SYSID', 'USER' ],
 307                             'UNIQUE'      : [ 'ON', 'USING', 'TABLESPACE',
 308                                               'WHERE', 'INDEX' ],
 309                             'INDEX'       : [ 'ON', 'USING', 'TABLESPACE',
 310                                               'WHERE', 'RTREE', 'HASH' ],
 311                             'LANGUAGE'    : [ 'HANDLER', 'VALIDATOR' ],
 312                             'PROCEDURAL'  : [ 'HANDLER', 'VALIDATOR',
 313                                               'LANGUAGE' ],
 314                             'TRUSTED'     : [ 'HANDLER', 'VALIDATOR',
 315                                               'LANGUAGE' ],
 316                             'OPERATOR'    : [ 'PROCEDURE', 'LEFTARG',
 317                                               'RIGHTARG', 'COMMUTATOR',
 318                                               'NEGATOR', 'RESTRICT',
 319                                               'JOIN', 'HASHES', 'MERGES',
 320                                               'SORT1', 'SORT2', 'LTCMP',
 321                                               'GTCMP',
 322                                               'CLASS', 'DEFAULT',
 323                                               [ 'FOR', 'TYPE' ], 'USING',
 324                                               'AS', 'OPERATOR', 'RECHECK',
 325                                               'FUNCTION', 'STORAGE' ],
 326                             'RULE'        : [ 'AS', 'ON', 'TO', 'WHERE',
 327                                               'DO', 'ALSO', 'INSTEAD',
 328                                               'NOTHING' ],
 329                             'SCHEMA'      : [ 'AUTHORIZATION' ],
 330                             'SEQUENCE'    : [ 'INCREMENT', 'BY', 'MINVALUE',
 331                                               'NO', 'MAXVALUE', [ 'START',
 332                                               'WITH' ], 'CACHE', 'CYCLE' ],
 333                             'TEMPORARY'    : [ 'SEQUENCE',
 334                                                'INCREMENT', 'BY', 'MINVALUE',
 335                                               'NO', 'MAXVALUE', [ 'START',
 336                                               'WITH' ], 'CACHE', 'CYCLE',
 337                                               # CREATE TABLE
 338                                               'TABLE',
 339                                               'DEFAULT', 'INCLUDING',
 340                                               'EXCLUDING', 'DEFAULTS',
 341                                               'INHERITS', 'WITH',
 342                                               'WITHOUT', 'OIDS',
 343                                               [ 'ON', 'COMMIT' ],
 344                                               [ 'PRESERVE', 'ROWS' ],
 345                                               [ 'DELETE', 'ROWS' ], 'DROP',
 346                                               'TABLESPACE', 'CONSTRAINT',
 347                                               'UNIQUE',
 348                                               [ 'USING', 'INDEX', 'TABLESPACE'],
 349                                               [ 'PRIMARY', 'KEY' ], 'CHECK',
 350                                               'REFERENCES', [ 'MATCH', 'FULL' ],
 351                                               [ 'MATCH', 'PARTIAL' ],
 352                                               [ 'MATCH', 'SIMPLE' ],
 353                                               [ 'ON', 'DELETE' ],
 354                                               [ 'ON', 'UPDATE' ], 'DEFERRABLE',
 355                                               [ 'INITIALLY', 'DEFERRED' ],
 356                                               [ 'INITIALLY', 'IMMEDIATE' ],
 357                                               [ 'FOREIGN', 'KEY' ],
 358                                                'CHECK',
 359                                             ],
 360                             'TEMP'        : [ 'SEQUENCE',
 361                                                'INCREMENT', 'BY', 'MINVALUE',
 362                                               'NO', 'MAXVALUE', [ 'START',
 363                                               'WITH' ], 'CACHE', 'CYCLE',
 364                                               # CREATE TABLE
 365                                               'TABLE',
 366                                               'DEFAULT', 'INCLUDING',
 367                                               'EXCLUDING', 'DEFAULTS',
 368                                               'INHERITS', 'WITH', 'OIDS',
 369                                               'WITHOUT',
 370                                               [ 'ON', 'COMMIT' ],
 371                                               [ 'PRESERVE', 'ROWS' ],
 372                                               [ 'DELETE', 'ROWS' ], 'DROP',
 373                                               'TABLESPACE', 'CONSTRAINT',
 374                                               'UNIQUE',
 375                                               [ 'USING', 'INDEX', 'TABLESPACE'],
 376                                               [ 'PRIMARY', 'KEY' ], 'CHECK',
 377                                               'REFERENCES', [ 'MATCH', 'FULL' ],
 378                                               [ 'MATCH', 'PARTIAL' ],
 379                                               [ 'MATCH', 'SIMPLE' ],
 380                                               [ 'ON', 'DELETE' ],
 381                                               [ 'ON', 'UPDATE' ], 'DEFERRABLE',
 382                                               [ 'INITIALLY', 'DEFERRED' ],
 383                                               [ 'INITIALLY', 'IMMEDIATE' ],
 384                                               [ 'FOREIGN', 'KEY' ],
 385                                                'CHECK',
 386                                             ],
 387                             'LOCAL'       : [ 'TABLE',
 388                                               'DEFAULT', 'INCLUDING',
 389                                               'EXCLUDING', 'DEFAULTS',
 390                                               'INHERITS', 'WITH',
 391                                               'WITHOUT', 'OIDS',
 392                                               [ 'ON', 'COMMIT' ],
 393                                               [ 'PRESERVE', 'ROWS' ],
 394                                               [ 'DELETE', 'ROWS' ], 'DROP',
 395                                               'TABLESPACE', 'CONSTRAINT',
 396                                               'UNIQUE',
 397                                               [ 'USING', 'INDEX', 'TABLESPACE'],
 398                                               [ 'PRIMARY', 'KEY' ], 'CHECK',
 399                                               'REFERENCES', [ 'MATCH', 'FULL' ],
 400                                               [ 'MATCH', 'PARTIAL' ],
 401                                               [ 'MATCH', 'SIMPLE' ],
 402                                               [ 'ON', 'DELETE' ],
 403                                               [ 'ON', 'UPDATE' ], 'DEFERRABLE',
 404                                               [ 'INITIALLY', 'DEFERRED' ],
 405                                               [ 'INITIALLY', 'IMMEDIATE' ],
 406                                               [ 'FOREIGN', 'KEY' ],
 407                                                'CHECK',
 408                                             ],
 409                             'GLOBAL'      : [ 'TABLE',
 410                                               'DEFAULT', 'INCLUDING',
 411                                               'EXCLUDING', 'DEFAULTS',
 412                                               'INHERITS', 'WITH', 'OIDS',
 413                                               'WITHOUT',
 414                                               [ 'ON', 'COMMIT' ],
 415                                               [ 'PRESERVE', 'ROWS' ],
 416                                               [ 'DELETE', 'ROWS' ], 'DROP',
 417                                               'TABLESPACE', 'CONSTRAINT',
 418                                               'UNIQUE',
 419                                               [ 'USING', 'INDEX', 'TABLESPACE'],
 420                                               [ 'PRIMARY', 'KEY' ], 'CHECK',
 421                                               'REFERENCES', [ 'MATCH', 'FULL' ],
 422                                               [ 'MATCH', 'PARTIAL' ],
 423                                               [ 'MATCH', 'SIMPLE' ],
 424                                               [ 'ON', 'DELETE' ],
 425                                               [ 'ON', 'UPDATE' ], 'DEFERRABLE',
 426                                               [ 'INITIALLY', 'DEFERRED' ],
 427                                               [ 'INITIALLY', 'IMMEDIATE' ],
 428                                               [ 'FOREIGN', 'KEY' ],
 429                                                'CHECK',
 430                                             ],
 431                             'TABLE'       : [ 'DEFAULT', 'INCLUDING',
 432                                               'EXCLUDING', 'DEFAULTS',
 433                                               'INHERITS', 'WITH', 'OIDS',
 434                                               'WITHOUT',
 435                                               [ 'ON', 'COMMIT' ],
 436                                               [ 'PRESERVE', 'ROWS' ],
 437                                               [ 'DELETE', 'ROWS' ], 'DROP',
 438                                               'TABLESPACE', 'CONSTRAINT',
 439                                               'UNIQUE',
 440                                               [ 'USING', 'INDEX', 'TABLESPACE'],
 441                                               [ 'PRIMARY', 'KEY' ], 'CHECK',
 442                                               'REFERENCES', [ 'MATCH', 'FULL' ],
 443                                               [ 'MATCH', 'PARTIAL' ],
 444                                               [ 'MATCH', 'SIMPLE' ],
 445                                               [ 'ON', 'DELETE' ],
 446                                               [ 'ON', 'UPDATE' ], 'DEFERRABLE',
 447                                               'INITIALLY', 'DEFERRED',
 448                                               'IMMEDIATE', 'RESTRICT',
 449                                               [ 'FOREIGN', 'KEY' ], 'CASCADE'
 450                                                'CHECK',
 451                                             ],
 452                             'TABLESPACE'  : [ 'OWNER', 'LOCATION' ],
 453                             'TRIGGER'     : [ 'BEFORE', 'AFTER', 'OR', 'ON',
 454                                               'FOR', 'EACH', 'ROW', 'STATEMENT',
 455                                               [ 'EXECUTE', 'PROCEDURE' ]
 456                                             ],
 457                             'TYPE'        : [ 'AS', 'INPUT', 'OUTPUT',
 458                                               'RECEIVE', 'SEND', 'ANALYZE',
 459                                               'INTERNALLENGTH', 'VARIABLE',
 460                                               'PASSEDBYVALUE', 'ALIGNMENT',
 461                                               'STORAGE', 'DEFAULT',
 462                                               'ELEMENT', 'DELIMITER' ],
 463                             'USER'        : [ 'WITH', 'SYSID', 'CREATEDB',
 464                                               'NOCREATEDB', 'CREATEUSER',
 465                                               'NOCREATEUSER', [ 'IN', 'GROUP' ],
 466                                               'ENCRYPTED', 'UNENCRYPTED',
 467                                               'PASSWORD', [ 'VALID', 'UNTIL' ]
 468                                             ],
 469                             'VIEW'        : [ 'AS' ,
 470                                               # select
 471                                               'SELECT', 'DISTINCT', 'ON',
 472                                               'AS', 'FROM', 'WHERE',
 473                                               [ 'GROUP', 'BY' ], 'HAVING',
 474                                               'UNION', 'INTERSECT', 'EXCEPT',
 475                                               [ 'ORDER', 'BY' ], 'ASC',
 476                                               'DESC', 'USING', 'LIMIT',
 477                                               'OFFSET', [ 'FOR', 'UPDATE',
 478                                               'OF' ], 'ONLY', 'NATURAL',
 479                                               'USING', 'INNER', 'LEFT', 'OUTER',
 480                                               'RIGHT', 'FULL', 'JOIN', 'CROSS',
 481                                               'INTO' ],
 482                           },
 483              'DEALLOCATE': [ 'PREPARE' ],
 484              'DECLARE'  : [ 'BINARY', 'INSENSITIVE', 'NO', 'SCROLL',
 485                             'CURSOR', 'WITH', 'WITHOUT', 'HOLD', 'FOR',
 486                             [ 'READ', 'ONLY' ], 'UPDATE', 'OF' ],
 487              'DELETE'   : { 'FROM'        : [ 'ONLY', 'WHERE' ] },
 488              'DROP'     : { 'AGGREGATE'   : [ 'CASCADE', 'RESTRICT' ],
 489                             'CAST'        : [ 'AS', 'CASCADE', 'RESTRICT' ],
 490                             'CONVERSION'  : [ 'CASCADE', 'RESTRICT' ],
 491                             'DATABASE'    : [],
 492                             'DOMAIN'      : [ 'CASCADE', 'RESTRICT' ],
 493                             'FUNCTION'    : [ 'CASCADE', 'RESTRICT' ],
 494                             'GROUP'       : [],
 495                             'INDEX'       : [ 'CASCADE', 'RESTRICT' ],
 496                             'LANGUAGE'    : [ 'CASCADE', 'RESTRICT' ],
 497                             'PROCEDURAL'  : ['LANGUAGE', 'CASCADE', 'RESTRICT'],
 498                             'OPERATOR'    : [ 'NONE', 'CASCADE', 'RESTRICT',
 499                                               'CLASS', 'USING' ],
 500                             'RULE'        : [ 'ON', 'CASCADE', 'RESTRICT' ],
 501                             'SCHEMA'      : [ 'CASCADE', 'RESTRICT' ],
 502                             'SEQUENCE'    : [ 'CASCADE', 'RESTRICT' ],
 503                             'TABLE'       : [ 'CASCADE', 'RESTRICT' ],
 504                             'TABLESPACE'  : [],
 505                             'TRIGGER'     : [ 'ON', 'CASCADE', 'RESTRICT' ],
 506                             'TYPE'        : [ 'CASCADE', 'RESTRICT' ],
 507                             'USER'        : [],
 508                             'VIEW'        : [ 'CASCADE', 'RESTRICT' ],
 509                           },
 510              'END'      : [ 'WORK', 'TRANSACTION' ],
 511              'EXECUTE'  : [],
 512              'EXPLAIN'  : [ 'ANALYZE', 'VERBOSE' ],
 513              'FETCH'    : [ 'FROM', 'IN', 'NEXT', 'PRIOR', 'FIRST', 'LAST',
 514                             'ABSOLUTE', 'RELATIVE', 'FORWARD',
 515                             'BACKWARD' ],
 516              'GRANT'    : [ 'SELECT', 'INSERT', 'UPDATE', 'DELETE', 'RULE',
 517                             'REFERENCES', 'TRIGGER', 'PRIVILEGES',
 518                             'ON', 'TABLE', 'TO', 'GROUP', 'PUBLIC',
 519                             [ 'WITH', 'GRANT', 'OPTION' ],
 520                             'CREATE', 'TEMPORARY', 'TEMP', 'PRIVILEGES',
 521                             'DATABASE', 'EXECUTE', 'FUNCTION', 'USAGE',
 522                             'LANGUAGE', 'SCHEMA', 'TABLESPACE' ],
 523              'INSERT'   : { 'INTO' : [ 'DEFAULT', 'VALUES',
 524                                        'SELECT', 'DISTINCT', 'ON', 'AS',
 525                                        'FROM', 'WHERE', [ 'GROUP', 'BY' ],
 526                                        'HAVING', 'UNION', 'INTERSECT', 'EXCEPT',
 527                                        [ 'ORDER', 'BY' ],
 528                                        'ASC', 'DESC', 'USING', 'LIMIT',
 529                                        'OFFSET', [ 'FOR', 'UPDATE', 'OF' ],
 530                                        'ONLY', 'NATURAL', 'USING', 'INNER',
 531                                        'LEFT', 'OUTER', 'RIGHT', 'FULL',
 532                                        'JOIN', 'CROSS', 'INTO',
 533                                      ]
 534                           },
 535              'LISTEN'   : [],
 536              'LOAD'     : [],
 537              'LOCK'     : [ 'TABLE', 'IN', 'MODE', 'NOWAIT',
 538                             [ 'ACCESS', 'SHARE' ], [ 'ROW', 'SHARE' ],
 539                             [ 'ROW', 'EXCLUSIVE' ],
 540                             [ 'SHARE', 'UPDATE', 'EXCLUSIVE' ],
 541                             'SHARE', [ 'SHARE', 'ROW', 'EXCLUSIVE' ],
 542                             'EXCLUSIVE', [ 'ACCESS', 'EXCLUSIVE' ] ],
 543              'MOVE'     : [ 'FROM', 'IN' ],
 544              'NOTIFY'   : [],
 545              'PREPARE'  : [ 'AS' ],
 546              'REINDEX'  : [ 'DATABASE', 'TABLE', 'TABLE', 'FORCE' ],
 547              'RELEASE'  : [ 'SAVEPOINT' ],
 548              'RESET'    : [ ],
 549              'REVOKE'   : [ [ 'GRANT', 'OPTION', 'FOR' ], 'SELECT', 'INSERT',
 550                             'UPDATE', 'DELETE', 'RULE', 'REFERENCES',
 551                             'TRIGGER', 'PRIVILEGES', 'ON', 'TABLE',
 552                             'FROM', 'GROUP', 'PUBLIC', 'CASCADE', 'RESTRICT',
 553                             'CREATE', 'TEMPORARY', 'TEMP', 'EXECUTE',
 554                             'USAGE', 'SCHEMA', 'LANGUAGE', 'TABLESPACE' ],
 555              'ROLLBACK' : [ 'WORK', 'TRANSACTION', 'TO', 'SAVEPOINT' ],
 556              'SAVEPOINT': [],
 557
 558              'SELECT'   : [ 'SELECT', 'INTO', 'DISTINCT', 'ON', 'AS', 'FROM',
 559                             'WHERE', [ 'GROUP', 'BY' ], 'HAVING', 'UNION',
 560                             'INTERSECT', 'EXCEPT', [ 'ORDER', 'BY' ],
 561                             'ASC', 'DESC', 'USING', 'LIMIT', 'OFFSET',
 562                             [ 'FOR', 'UPDATE', 'OF' ], 'ONLY', 'NATURAL',
 563                             'USING', 'INNER', 'LEFT', 'OUTER', 'RIGHT', 'FULL',
 564                             'JOIN', 'CROSS', 'INTO' ],
 565              'SET'      : [ 'SESSION', 'LOCAL', 'TO', 'DEFAULT', 'LOCAL',
 566                             [ 'TIME', 'ZONE' ], 'CONSTRAINTS',
 567                             'DEFERRED', 'IMMEDIATE', 'AUTHORIZATION',
 568                             'TRANSACTION', 'CHARACTERISTICS',
 569                             [ 'ISOLATION', 'LEVEL' ], 'SERIALIZABLE',
 570                             [ 'REPEATABLE', 'READ' ], [ 'READ', 'COMMITTED' ],
 571                             [ 'READ', 'UNCOMMITTED' ], [ 'READ', 'WRITE' ],
 572                             [ 'READ', 'ONLY' ]
 573                           ],
 574              'SHOW'     : [ ],
 575              'START'    : { 'TRANSACTION' : [ [ 'ISOLATION', 'LEVEL' ],
 576                                               'SERIALIZABLE',
 577                                               [ 'REPEATABLE', 'READ' ],
 578                                               [ 'READ', 'COMMITTED' ],
 579                                               [ 'READ', 'UNCOMMITTED' ],
 580                                               [ 'READ', 'WRITE' ],
 581                                               [ 'READ', 'ONLY' ]
 582                                             ],
 583                           },
 584              'TRUNCATE' : [ 'TABLE' ],
 585              'UNLISTEN' : [],
 586              'UPDATE'   : [ 'ONLY', 'SET', 'DEFAULT', 'FROM', 'WHERE' ],
 587              'VACUUM'   : [ 'FULL', 'FREEZE', 'VERBOSE', 'ANALYSE' ]
 588            }
 589
 590
 591 class Syntax:
 592   """Syntax highlight"""
 593
 594   def __init__(self, buffer):
 595     self.lexical = pgw.Lexical.Lexical()
 596     self.buffer = buffer
 597     self.last = None
 598     # default colors
 599     self.tag = {}
 600     self.tag['font'] = self.buffer.create_tag("font")
 601     if (pgw.mswindows()):
 602       self.tag['font'].set_property('font', 'Courier New 10')
 603     else:
 604       self.tag['font'].set_property('family', 'monospace')
 605     self.tag['function'] = self.buffer.create_tag("function")
 606     self.tag['function'].set_property('foreground', '#009999')
 607     self.tag['dollarquote'] = self.buffer.create_tag("dollarquote")
 608     self.tag['dollarquote'].set_property('foreground', '#000000')
 609     self.tag['identifier'] = self.buffer.create_tag("identifier")
 610     self.tag['identifier'].set_property('foreground', '#000000')
 611     self.tag['keyword'] = self.buffer.create_tag("keyword")
 612     self.tag['keyword'].set_property('foreground', '#0000FF')
 613     self.tag['type'] = self.buffer.create_tag("type")
 614     self.tag['type'].set_property('foreground', '#009900')
 615     self.tag['string'] = self.buffer.create_tag("string")
 616     self.tag['string'].set_property('foreground', '#F700BF')
 617     self.tag['numeric_constant'] = self.buffer.create_tag("numeric_constant")
 618     self.tag['numeric_constant'].set_property('foreground', '#c53838')
 619     self.tag['special'] = self.buffer.create_tag("special")
 620     self.tag['special'].set_property('foreground', '#c53838')
 621     self.tag['comment'] = self.buffer.create_tag("comment")
 622     self.tag['comment'].set_property('foreground', '#999999')
 623     self.tag['comment2'] = self.buffer.create_tag("comment2")
 624     self.tag['comment2'].set_property('style', pango.STYLE_ITALIC)
 625     self.tag['operator'] = self.buffer.create_tag("operator")
 626     self.tag['operator'].set_property('foreground', '#555555')
 627     self.tag['psql'] = self.buffer.create_tag("psql")
 628     self.tag['psql'].set_property('background', '#d0d4df')
 629
 630
 631   def start_of_prev_statement(self, iter):
 632     """Find the first character of a statement"""
 633     if (self.last is not None):
 634       self.found = None
 635       for token in self.last:
 636         if (token.start_iter.compare(iter) >= 0):
 637           if (self.found is None):
 638             break
 639           return self.found.start_iter
 640         if (token.value == ';'):
 641           self.found = token
 642       if (self.found):
 643         return self.found.start_iter
 644     return iter.get_buffer().get_start_iter()
 645
 646
 647   def start_of_next_statement(self, iter):
 648     """Find the last character of a statement"""
 649     if (self.last is not None):
 650       self.found = None
 651       for token in reversed(self.last):
 652         if (token.end_iter.compare(iter) <= 0):
 653           if (self.found is None):
 654             break
 655           return self.found.end_iter
 656         if (token.value == ';'):
 657           self.found = token
 658       if (self.found):
 659         return self.found.end_iter
 660     return iter.get_buffer().get_end_iter()
 661
 662
 663   def text_inserted(self, buffer, iter, text, length):
 664     """Called by Gtk when text is inserted in the buffer:
 665       prepare 'start' and 'end' for text_changed()"""
 666     self.start = self.start_of_prev_statement(iter.copy()).get_offset()
 667     self.end = self.start_of_next_statement(iter.copy()).get_offset() + length
 668
 669
 670   def text_deleted(self, buffer, start, end):
 671     """Called by Gtk when text is deleted from the buffer:
 672       prepare 'start' and 'end' for text_changed()"""
 673     self.start = self.start_of_prev_statement(start.copy()).get_offset()
 674     self.end = self.start_of_next_statement(end.copy()).get_offset()
 675
 676
 677   def text_changed(self, buffer):
 678     """Called by Gtk when (after) text is deleted or inserted
 679     in the buffer. Uses 'start' and 'end' prepared in text_inserted
 680     and text_deleted then run the analyse"""
 681     start = self.buffer.get_iter_at_offset(self.start)
 682     end = self.buffer.get_iter_at_offset(self.end)
 683     self.analyse(start, end)
 684
 685
 686   def refresh(self):
 687     """Used when you want to manually refresh the syntax highlight
 688     of the whole buffer"""
 689     start = self.buffer.get_start_iter()
 690     end = self.buffer.get_end_iter()
 691     self.analyse(start, end)
 692
 693
 694   def analyse(self, start, end):
 695     """Run the lexical and syntaxical analysers then
 696     apply the syntax highlight to the buffer"""
 697     self.tokens = self.lexical.analyse(self.buffer, start, end)
 698     self.syntaxical_analyser()
 699     self.buffer.remove_all_tags(start, end)
 700     self.buffer.apply_tag(self.tag['font'], start, end)
 701     self.last = self.tokens
 702     for token in self.tokens:
 703       self.buffer.apply_tag(self.tag[token.token], \
 704                             token.start_iter, \
 705                             token.end_iter)
 706       if (token.token == 'comment'):
 707         self.buffer.apply_tag(self.tag['comment2'], \
 708                               token.start_iter, \
 709                               token.end_iter)
 710
 711
 712   def syntaxical_analyser(self):
 713     """Find keywords"""
 714     tokens = self.tokens
 715     self.tokens = []
 716     try:
 717       while (len(tokens) > 0):
 718         token = tokens.pop(0)
 719         # only statements, other tokens
 720         # are analysed below or already founds in
 721         # the lexical analyser
 722         if (token.value in STATEMENTS):
 723           token.token = 'keyword'
 724           self.tokens.append(token)
 725           statement = token.value
 726           token = tokens.pop(0)
 727           # get the list containing the
 728           # keywords for this statement
 729           try:
 730             keywords = KEYWORDS[statement]
 731           except KeyError:
 732             continue
 733           if (type(keywords) is dict):
 734             try:
 735               keywords = keywords[token.value]
 736               token.token = 'keyword'
 737               self.tokens.append(token)
 738               token = tokens.pop(0)
 739             except KeyError:
 740               pass
 741             self.tokens.append(token)
 742           # identify each token inside the statement
 743           while (token.value != ';'):
 744             # only identifiers, other tokens are
 745             # analysed in the lexical analyser
 746             if (token.token == 'identifier'):
 747               # special constants
 748               if (token.value in SPECIALS):
 749                 token.token = 'special'
 750               # buit-in data types
 751               elif (token.value in TYPES):
 752                 # TODO : manage composed types names
 753                 token.token = 'type'
 754               # names operators
 755               elif (token.value in OPERATORS2):
 756                 token.token = 'operator'
 757               # built-in functions
 758               elif (token.value in BUILTINS):
 759                 next = tokens.pop(0)
 760                 if (next.value == '('):
 761                   token.token = 'function'
 762                   self.tokens.append(next)
 763                 else:
 764                   tokens.insert(0, next)
 765               # built-in function that can be used without ()
 766               if (token.value in BUILTINS2):
 767                 token.token = 'function'
 768               # everything else : keywords or indentifiers
 769               else:
 770                 for keyword in keywords:
 771                   if (type(keyword) is list):
 772                     # TODO : write the right code here
 773                     # to manage composed keywords
 774                     if (token.value in keyword):
 775                       token.token = 'keyword'
 776                   elif (token.value == keyword):
 777                     token.token = 'keyword'
 778             self.tokens.append(token)
 779             token = tokens.pop(0)
 780         self.tokens.append(token)
 781     except IndexError:
 782       pass