tables/PROPERTY.pg_sql
Tables
PROPERTY |
---|
Contains property value pairs used for the logging facility.
- Columns:
Column | Data Type | Description |
---|
ID | UUID | | PROPERTY_NAME | TEXT | | PROPERTY_VALUE_BOOLEAN | BOOLEAN | | PROPERTY_VALUE_STRING | TEXT | | PROPERTY_VALUE_NUMBER | NUMERIC | | PROPERTY_VALUE_INTEGER | BIGINT | | PROPERTY_VALUE_DATE | DATE | | PROPERTY_VALUE_TIME | TIME(6) | | PROPERTY_VALUE_TIMESTAMP | TIMESTAMPTZ(6) | | PROPERTY_VALUE_INTERVAL | INTERVAL | | PROPERTY_DESCRIPTION | TEXT | |
- Copyright:
- Thiemo Kellner, 2018 -
- Webpage:
- https://www.sourceforge.net/projects/pglogger
- Version Info:
- $Id: PROPERTY.pg_sql 22 2018-06-15 20:25:59Z thiemo $
- Additional Info:
- License LGPLv3
- TODO:
- Once HyperSQL supports a license tag, convert the corresponding info tag into one.
|
Source
0001: \echo Start tables/PROPERTY.pg_sql
0002:
0003: drop table if exists PROPERTY;
0004:
0005: 0006: 0007: 0008: 0009: 0010: 0011: 0012: 0013: 0014: 0015: 0016: 0017: 0018: 0019: 0020: 0021: 0022: 0023: 0024: 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: 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: 0165: insert into PROPERTY (
0166: PROPERTY_NAME,
0167: PROPERTY_VALUE_STRING,
0168: PROPERTY_DESCRIPTION
0169: ) values (
0170: 'DBLINK_CONNECT_STRING',
0171: 0172: 'port=5432 dbname=act user=logger password=SlgoomzO61OjrqstjbQdl6fyRvE9NKDp',
0173: 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: 0201: 0202: 0203: 0204: 0205: 0206: 0207: 0208: 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; 0236:
0237: \echo End tables/PROPERTY.pg_sql
Generated by
HyperSQL v3.9.8 at Wed Jul 4 07:48:54 2018