tables/PROPERTY.pg_sql

Tables

Source

0001: \echo Start tables/PROPERTY.pg_sql
0002: 
0003: drop table if exists PROPERTY;
0004: 
0005: /** Contains property value pairs used for the logging facility.
0006:  *
0007:  *  @table PROPERTY
0008:  *  @col uuid ID
0009:  *  @col text PROPERTY_NAME
0010:  *  @col boolean PROPERTY_VALUE_BOOLEAN
0011:  *  @col text PROPERTY_VALUE_STRING
0012:  *  @col numeric PROPERTY_VALUE_NUMBER
0013:  *  @col bigint PROPERTY_VALUE_INTEGER
0014:  *  @col date PROPERTY_VALUE_DATE
0015:  *  @col time(6) PROPERTY_VALUE_TIME
0016:  *  @col timestamptz(6) PROPERTY_VALUE_TIMESTAMP
0017:  *  @col interval PROPERTY_VALUE_INTERVAL
0018:  *  @col text PROPERTY_DESCRIPTION
0019:  *  @version $Id: PROPERTY.pg_sql 22 2018-06-15 20:25:59Z thiemo $
0020:  *  @todo Once HyperSQL supports a license tag, convert the corresponding
0021:  *        info tag into one.
0022:  *  @copyright Thiemo Kellner, 2018 -
0023:  *  @info License LGPLv3
0024:  *  @webpage https://www.sourceforge.net/projects/pglogger
0025:  */
0026: create table PROPERTY(
0027:     ID uuid not null default uuid_generate_v1(),
0028:     PROPERTY_NAME text not null,
0029:     PROPERTY_VALUE_BOOLEAN boolean,
0030:     PROPERTY_VALUE_STRING text,
0031:     PROPERTY_VALUE_NUMBER numeric,
0032:     PROPERTY_VALUE_INTEGER bigint,
0033:     PROPERTY_VALUE_DATE date,
0034:     PROPERTY_VALUE_TIME time(6) with time zone,
0035:     PROPERTY_VALUE_TIMESTAMP timestamp(6) with time zone,
0036:     PROPERTY_VALUE_INTERVAL interval,
0037:     PROPERTY_DESCRIPTION text
0038: );
0039: 
0040: 
0041: alter table PROPERTY add primary key (ID);
0042: 
0043: alter table PROPERTY add unique (PROPERTY_NAME);
0044: 
0045: alter table PROPERTY
0046:   add constraint PROPERTY_CK check (
0047:       (
0048:           PROPERTY_VALUE_BOOLEAN is not null
0049:           and PROPERTY_VALUE_STRING is null
0050:           and PROPERTY_VALUE_NUMBER is null
0051:           and PROPERTY_VALUE_INTEGER is null
0052:           and PROPERTY_VALUE_DATE is null
0053:           and PROPERTY_VALUE_TIME is null
0054:           and PROPERTY_VALUE_TIMESTAMP is null
0055:           and PROPERTY_VALUE_INTERVAL is null
0056:       )
0057:       or (
0058:           PROPERTY_VALUE_BOOLEAN is null
0059:           and PROPERTY_VALUE_STRING is not null
0060:           and PROPERTY_VALUE_NUMBER is null
0061:           and PROPERTY_VALUE_INTEGER is null
0062:           and PROPERTY_VALUE_DATE is null
0063:           and PROPERTY_VALUE_TIME is null
0064:           and PROPERTY_VALUE_TIMESTAMP is null
0065:           and PROPERTY_VALUE_INTERVAL is null
0066:       )
0067:       or (
0068:           PROPERTY_VALUE_BOOLEAN is null
0069:           and PROPERTY_VALUE_STRING is null
0070:           and PROPERTY_VALUE_NUMBER is not null
0071:           and PROPERTY_VALUE_INTEGER is null
0072:           and PROPERTY_VALUE_DATE is null
0073:           and PROPERTY_VALUE_TIME is null
0074:           and PROPERTY_VALUE_TIMESTAMP is null
0075:           and PROPERTY_VALUE_INTERVAL is null
0076:       )
0077:       or (
0078:           PROPERTY_VALUE_BOOLEAN is null
0079:           and PROPERTY_VALUE_STRING is null
0080:           and PROPERTY_VALUE_NUMBER is null
0081:           and PROPERTY_VALUE_INTEGER is not null
0082:           and PROPERTY_VALUE_DATE is null
0083:           and PROPERTY_VALUE_TIME is null
0084:           and PROPERTY_VALUE_TIMESTAMP is null
0085:           and PROPERTY_VALUE_INTERVAL is null
0086:       )
0087:       or (
0088:           PROPERTY_VALUE_BOOLEAN is not null
0089:           and PROPERTY_VALUE_STRING is null
0090:           and PROPERTY_VALUE_NUMBER is null
0091:           and PROPERTY_VALUE_INTEGER is null
0092:           and PROPERTY_VALUE_DATE is not null
0093:           and PROPERTY_VALUE_TIME is null
0094:           and PROPERTY_VALUE_TIMESTAMP is null
0095:           and PROPERTY_VALUE_INTERVAL is null
0096:       )
0097:       or (
0098:           PROPERTY_VALUE_BOOLEAN is null
0099:           and PROPERTY_VALUE_STRING is null
0100:           and PROPERTY_VALUE_NUMBER is null
0101:           and PROPERTY_VALUE_INTEGER is null
0102:           and PROPERTY_VALUE_DATE is null
0103:           and PROPERTY_VALUE_TIME is not null
0104:           and PROPERTY_VALUE_TIMESTAMP is null
0105:           and PROPERTY_VALUE_INTERVAL is null
0106:       )
0107:       or (
0108:           PROPERTY_VALUE_BOOLEAN is null
0109:           and PROPERTY_VALUE_STRING is null
0110:           and PROPERTY_VALUE_NUMBER is null
0111:           and PROPERTY_VALUE_INTEGER is null
0112:           and PROPERTY_VALUE_DATE is null
0113:           and PROPERTY_VALUE_TIME is null
0114:           and PROPERTY_VALUE_TIMESTAMP is not null
0115:           and PROPERTY_VALUE_INTERVAL is null
0116:       )
0117:       or (
0118:           PROPERTY_VALUE_BOOLEAN is null
0119:           and PROPERTY_VALUE_STRING is null
0120:           and PROPERTY_VALUE_NUMBER is null
0121:           and PROPERTY_VALUE_INTEGER is null
0122:           and PROPERTY_VALUE_DATE is null
0123:           and PROPERTY_VALUE_TIME is null
0124:           and PROPERTY_VALUE_TIMESTAMP is null
0125:           and PROPERTY_VALUE_INTERVAL is not null
0126:       )
0127:   );
0128: 
0129: alter table PROPERTY
0130:   add constraint PROPERTY_CK_LOGGING_LEVEL check (
0131:       PROPERTY_NAME != 'LOGGING_LEVEL'
0132:       or PROPERTY_VALUE_STRING in (
0133:           'DEBUG',
0134:           'LOG',
0135:           'INFO',
0136:           'NOTICE',
0137:           'WARNING',
0138:           'EXCEPTION'
0139:       )
0140:   );
0141: 
0142: --- boolean properties
0143: insert into PROPERTY (
0144:     PROPERTY_NAME,
0145:     PROPERTY_VALUE_BOOLEAN,
0146:     PROPERTY_DESCRIPTION
0147: ) values (
0148:     'TO_TABLE',
0149:     true,
0150:     'Enables/disables logging to the logging table'
0151: ), (
0152:     'TO_STANDARD',
0153:     true,
0154:     'Enables/disables logging to the standard device(s) as defined in the server configuration. BEWARE that having this turned on and writing in level EXCEPTION will raise an exception.'
0155: ), (
0156:     'OUTPUT_EXCEPTION_STACK',
0157:     true,
0158:     'Enables/disables the outputting of exception context diagnositcs in case as notice.'
0159: ), (
0160:     'ASYNCH_TABLE_LOG',
0161:     false,
0162:     'Enables/disables asynchronous logging to table i. e. only takes effect if table logging is switched on. Asynchronous logging does not properly work (yet).'
0163: );
0164: -- string properties
0165: insert into PROPERTY (
0166:     PROPERTY_NAME,
0167:     PROPERTY_VALUE_STRING,
0168:     PROPERTY_DESCRIPTION
0169: ) values (
0170:     'DBLINK_CONNECT_STRING',
0171:     -- better to use dblink_connect_u with password file?
0172:     'port=5432 dbname=act user=logger password=SlgoomzO61OjrqstjbQdl6fyRvE9NKDp',
0173:     -- 'port=5432 dbname=act user=logger passfile=~/.pgpass',
0174:     'Connect string to be used in the dblink for the asynchronous writing of log via socket or localhost.'
0175: ), (
0176:     'DBLINK_CONNECTION_NAME',
0177:     'PGLOGGER_CONNECTION',
0178:     'Name of the connection for the logging facility.'
0179: ),
0180: (
0181:   'TIMESTAMP_DISPLAY_FORMAT',
0182:   'yyyy-mm-dd hh24:mi:ss.us',
0183:   'The format in which timestamps are to be displayed'
0184: ), (
0185:   'INTEGER_DISPLAY_FORMAT',
0186:   '9999999999999999999999999999999999999999999999999999999999999999999',
0187:   'The format in which integers are to be displayed, e. g. 9G999G999 for number with separator'
0188: ), (
0189:   'LOGGING_LEVEL',
0190:   'NOTICE',
0191:   'Defines the granularity of logging. There are DEBUG, LOG, INFO, ' ||
0192:     'NOTICE, WARNING and EXCEPTION in order of increasing severity. ' ||
0193:     'Beware: When using EXCEPTION this means that the error is being ' ||
0194:     're-raised. It really is meant only for fatal occurrences.'
0195: ), (
0196:   'LINEBREAK',
0197:   chr(10) || chr(13),
0198:   'Character (sequence) to use as linebreak not using carriage return & line feed might lead to unexpected text jumble.'
0199: );
0200: -- interval properties
0201: -- insert into PROPERTY (
0202:     -- PROPERTY_NAME,
0203:     -- PROPERTY_VALUE_INTERVAL,
0204:     -- PROPERTY_DESCRIPTION
0205: -- ) values (
0206:     -- 'BUSY_WAIT_INTERVAL',
0207:     -- '1000 microsecond'::interval,
0208:     -- 'Interval of time the logging facility lets pass until re-check if the connection is available again. Too small a value might lead to the system getting consumed with checking.'
0209: -- );
0210: 
0211: comment on table PROPERTY is
0212:   'Contains property value pairs used for the logging facility.
0213: $Header: svn+ssh://thiemo@svn.code.sf.net/p/pglogger/code/tables/PROPERTY.pg_sql 22 2018-06-15 20:25:59Z thiemo $';
0214: 
0215: 
0216: create unique index PROPERTY_MAP
0217:   on PROPERTY
0218:   using btree (
0219:       PROPERTY_NAME,
0220:       PROPERTY_VALUE_BOOLEAN,
0221:       PROPERTY_VALUE_STRING,
0222:       PROPERTY_VALUE_NUMBER,
0223:       PROPERTY_VALUE_INTEGER,
0224:       PROPERTY_VALUE_DATE,
0225:       PROPERTY_VALUE_TIME,
0226:       PROPERTY_VALUE_TIMESTAMP,
0227:       PROPERTY_VALUE_INTERVAL,
0228:       PROPERTY_DESCRIPTION
0229:   );
0230: 
0231: comment on index PROPERTY_MAP is
0232:   'Uniqueness actually is more restrictive than this but it makes mapping queries more effective.
0233: $Header: svn+ssh://thiemo@svn.code.sf.net/p/pglogger/code/tables/PROPERTY.pg_sql 22 2018-06-15 20:25:59Z thiemo $';
0234: 
0235: commit; -- unlike Oracle not all ddl commit implicitly
0236: 
0237: \echo End tables/PROPERTY.pg_sql