def setUp(self):
db_utils.ensure_environment_variables_set()
db_utils.ensure_db(test_db)
self.maxDiff = None
with db_utils.get_test_connection(test_db) as conn:
conn.autocommit = True
with conn.cursor(cursor_factory=psycopg2.extras.DictCursor) as cur:
# db_utils.ensure_replication_slot(cur, test_db)
canonicalized_table_name = db_utils.canonicalized_table_name(
cur, test_schema_name, test_table_name)
create_table_sql = """
CREATE TABLE {} (id SERIAL PRIMARY KEY,
our_varchar VARCHAR,
our_varchar_10 VARCHAR(10),
our_text TEXT,
our_text_2 TEXT,
our_integer INTEGER,
our_smallint SMALLINT,
our_bigint BIGINT,
our_decimal NUMERIC(12,2),
"OUR TS" TIMESTAMP WITHOUT TIME ZONE,
"OUR TS TZ" TIMESTAMP WITH TIME ZONE,
"OUR TIME" TIME WITHOUT TIME ZONE,
"OUR TIME TZ" TIME WITH TIME ZONE,
"OUR DATE" DATE,
our_double DOUBLE PRECISION,
our_real REAL,
our_boolean BOOLEAN,
our_bit BIT(1),
our_json JSON,
our_jsonb JSONB,
our_uuid UUID,
our_store HSTORE,
our_citext CITEXT,
our_cidr cidr,
our_inet inet,
our_mac macaddr,
our_alignment_enum ALIGNMENT,
our_money money,
invalid_bigserial BIGSERIAL,
invalid_bit_varying BIT VARYING,
invalid_box BOX,
invalid_bytea BYTEA,
invalid_circle CIRCLE,
invalid_interval INTERVAL,
invalid_line LINE,
invalid_lseg LSEG,
invalid_path PATH,
invalid_pg_lsn PG_LSN,
invalid_point POINT,
invalid_polygon POLYGON,
invalid_serial SERIAL,
invalid_smallserial SMALLSERIAL,
invalid_tsquery TSQUERY,
invalid_tsvector TSVECTOR,
invalid_txid_snapshot TXID_SNAPSHOT,
invalid_xml XML)
""".format(canonicalized_table_name)
cur = db_utils.ensure_fresh_table(conn, cur, test_schema_name,
test_table_name)
cur.execute(create_table_sql)
#insert fixture data 1
our_ts = datetime.datetime(1997, 2, 2, 2, 2, 2, 722184)
nyc_tz = pytz.timezone('America/New_York')
our_ts_tz = nyc_tz.localize(our_ts)
our_time = datetime.time(12, 11, 10)
our_time_tz = our_time.isoformat() + "-04:00"
our_date = datetime.date(1998, 3, 4)
my_uuid = str(uuid.uuid1())
self.recs = []
for _ in range(500):
our_ts = datetime.datetime(1987, 3, 3, 3, 3, 3, 733184)
nyc_tz = pytz.timezone('America/New_York')
our_ts_tz = nyc_tz.localize(our_ts)
our_time = datetime.time(10, 9, 8)
our_time_tz = our_time.isoformat() + "-04:00"
our_date = datetime.date(1964, 7, 1)
my_uuid = str(uuid.uuid1())
record = {
'our_varchar': "our_varchar 4",
'our_varchar_10': "varchar_10",
'our_text': "some text 2",
'our_text_2': "NOT SELECTED",
'our_integer': 44101,
'our_smallint': 2,
'our_bigint': 1000001,
'our_decimal': decimal.Decimal('9876543210.02'),
quote_ident('OUR TS', cur): our_ts,
quote_ident('OUR TS TZ', cur): our_ts_tz,
quote_ident('OUR TIME', cur): our_time,
quote_ident('OUR TIME TZ', cur): our_time_tz,
quote_ident('OUR DATE', cur): our_date,
'our_double': 1.1,
'our_real': 1.2,
'our_boolean': True,
'our_bit': '1',
'our_json': json.dumps({'nymn': 77}),
'our_jsonb': json.dumps({'burgers': 'good++'}),
'our_uuid': my_uuid,
'our_store': 'dances=>"floor",name=>"betty"',
'our_citext': 'maGICKal 2',
'our_cidr': '192.168.101.128/25',
'our_inet': '192.168.101.128/24',
'our_mac': '08:00:2b:01:02:04',
}
db_utils.insert_record(cur, test_table_name, record)
self.recs.append(record)
cur.execute("""ANALYZE {}""".format(canonicalized_table_name))
def setUp(self):
db_utils.ensure_environment_variables_set()
db_utils.ensure_db("dev")
self.maxDiff = None
with db_utils.get_test_connection('dev') as conn:
conn.autocommit = True
with conn.cursor(cursor_factory=psycopg2.extras.DictCursor) as cur:
canon_table_name = db_utils.canonicalized_table_name(
cur, test_schema_name, test_table_name)
cur = db_utils.ensure_fresh_table(conn, cur, test_schema_name,
test_table_name)
create_table_sql = """
CREATE TABLE {} (id SERIAL PRIMARY KEY,
our_varchar VARCHAR,
our_varchar_10 VARCHAR(10),
our_text TEXT,
our_integer INTEGER,
our_smallint SMALLINT,
our_bigint BIGINT,
our_decimal NUMERIC({},{}),
"OUR TS" TIMESTAMP WITHOUT TIME ZONE,
"OUR TS TZ" TIMESTAMP WITH TIME ZONE,
"OUR TIME" TIME WITHOUT TIME ZONE,
"OUR TIME TZ" TIME WITH TIME ZONE,
"OUR DATE" DATE,
our_double DOUBLE PRECISION,
our_real REAL,
our_boolean BOOLEAN,
our_bit BIT(1),
our_json JSON,
our_jsonb JSONB,
our_uuid UUID,
our_store HSTORE,
our_citext CITEXT,
our_inet inet,
our_cidr cidr,
our_mac macaddr,
our_alignment_enum ALIGNMENT,
our_money money)
""".format(canon_table_name, NUMERIC_PRECISION, NUMERIC_SCALE)
cur.execute(create_table_sql)
# insert fixture data and track expected records
self.inserted_records = []
self.expected_records = []
# record 1
our_ts = datetime.datetime(1997, 2, 2, 2, 2, 2, 722184)
nyc_tz = pytz.timezone('America/New_York')
our_ts_tz = nyc_tz.localize(our_ts)
our_time = datetime.time(12, 11, 10)
our_time_tz = our_time.isoformat() + "-04:00"
our_date = datetime.date(1998, 3, 4)
my_uuid = str(uuid.uuid1())
self.inserted_records.append({
'our_varchar':
"our_varchar",
'our_varchar_10':
"varchar_10",
'our_text':
"some text",
'our_integer':
44100,
'our_smallint':
1,
'our_bigint':
1000000,
'our_decimal':
decimal.Decimal('.01'),
quote_ident('OUR TS', cur):
our_ts,
quote_ident('OUR TS TZ', cur):
our_ts_tz,
quote_ident('OUR TIME', cur):
our_time,
quote_ident('OUR TIME TZ', cur):
our_time_tz,
quote_ident('OUR DATE', cur):
our_date,
'our_double':
decimal.Decimal('1.1'),
'our_real':
1.2,
'our_boolean':
True,
'our_bit':
'0',
'our_json':
json.dumps({'secret': 55}),
'our_jsonb':
json.dumps({'burgers': 'good'}),
'our_uuid':
my_uuid,
'our_store':
'size=>"small",name=>"betty"',
'our_citext':
'maGICKal 4',
'our_cidr':
'192.168.100.128/25',
'our_inet':
'192.168.100.128/24',
'our_mac':
'08:00:2b:01:02:03',
'our_alignment_enum':
'good',
'our_money':
'100.1122',
})
self.expected_records.append({
'our_decimal':
decimal.Decimal('.01'),
'our_text':
'some text',
'our_bit':
False,
'our_integer':
44100,
'our_double':
decimal.Decimal('1.1'),
'id':
1,
'our_json':
'{"secret": 55}',
'our_boolean':
True,
'our_jsonb':
'{"burgers": "good"}',
'our_bigint':
1000000,
'OUR TS':
self.expected_ts(our_ts),
'OUR TS TZ':
self.expected_ts_tz(our_ts_tz),
'OUR TIME':
str(our_time),
'OUR TIME TZ':
str(our_time_tz),
'our_store': {
"name": "betty",
"size": "small"
},
'our_smallint':
1,
'OUR DATE':
'1998-03-04T00:00:00+00:00',
'our_varchar':
'our_varchar',
'our_uuid':
self.inserted_records[0]['our_uuid'],
'our_real':
decimal.Decimal('1.2'),
'our_varchar_10':
'varchar_10',
'our_citext':
self.inserted_records[0]['our_citext'],
'our_inet':
self.inserted_records[0]['our_inet'],
'our_cidr':
self.inserted_records[0]['our_cidr'],
'our_mac':
self.inserted_records[0]['our_mac'],
'our_alignment_enum':
self.inserted_records[0]['our_alignment_enum'],
'our_money':
'$100.11'
})
# record 2
our_ts = datetime.datetime(1987, 3, 3, 3, 3, 3, 733184)
nyc_tz = pytz.timezone('America/New_York')
our_ts_tz = nyc_tz.localize(our_ts)
our_time = datetime.time(10, 9, 8)
our_time_tz = our_time.isoformat() + "-04:00"
our_date = datetime.date(1964, 7, 1)
my_uuid = str(uuid.uuid1())
self.inserted_records.append({
'our_varchar':
"our_varchar 2",
'our_varchar_10':
"varchar_10",
'our_text':
"some text 2",
'our_integer':
44101,
'our_smallint':
2,
'our_bigint':
1000001,
'our_decimal':
decimal.Decimal('.02'),
quote_ident('OUR TS', cur):
our_ts,
quote_ident('OUR TS TZ', cur):
our_ts_tz,
quote_ident('OUR TIME', cur):
our_time,
quote_ident('OUR TIME TZ', cur):
our_time_tz,
quote_ident('OUR DATE', cur):
our_date,
'our_double':
decimal.Decimal('1.1'),
'our_real':
decimal.Decimal('1.2'),
'our_boolean':
True,
'our_bit':
'1',
'our_json':
json.dumps(["nymn 77"]),
'our_jsonb':
json.dumps({'burgers': 'good++'}),
'our_uuid':
my_uuid,
'our_store':
'dances=>"floor",name=>"betty"',
'our_citext':
'maGICKal 2',
'our_cidr':
'192.168.101.128/25',
'our_inet':
'192.168.101.128/24',
'our_mac':
'08:00:2b:01:02:04',
'our_money':
None
})
self.expected_records.append({
'our_decimal':
decimal.Decimal('.02'),
'OUR TIME':
str(our_time),
'our_text':
'some text 2',
'our_bit':
True,
'our_integer':
44101,
'our_double':
decimal.Decimal('1.1'),
'id':
2,
'our_json':
'["nymn 77"]',
'our_boolean':
True,
'our_jsonb':
'{"burgers": "good++"}',
'our_bigint':
1000001,
'OUR TIME TZ':
str(our_time_tz),
'our_store': {
"name": "betty",
"dances": "floor"
},
'OUR TS TZ':
self.expected_ts_tz(our_ts_tz),
'our_smallint':
2,
'OUR DATE':
'1964-07-01T00:00:00+00:00',
'our_varchar':
'our_varchar 2',
'OUR TS':
self.expected_ts(our_ts),
'our_uuid':
self.inserted_records[1]['our_uuid'],
'our_real':
decimal.Decimal('1.2'),
'our_varchar_10':
'varchar_10',
'our_citext':
self.inserted_records[1]['our_citext'],
'our_inet':
self.inserted_records[1]['our_inet'],
'our_cidr':
self.inserted_records[1]['our_cidr'],
'our_mac':
self.inserted_records[1]['our_mac'],
'our_alignment_enum':
None,
'our_money':
None
})
# record 3
self.inserted_records.append({
'our_decimal':
decimal.Decimal('NaN'),
'our_double':
float('nan'),
'our_real':
float('-inf')
})
self.expected_records.append({
'id': 3,
# We cast NaN's, +Inf, -Inf to NULL as wal2json does not support
# them and now we are at least consistent(ly wrong).
'our_decimal': None,
'our_double': None,
'our_real': None,
# any field without a set value will be set to NULL
'OUR TIME': None,
'our_text': None,
'our_bit': None,
'our_integer': None,
'our_json': None,
'our_boolean': None,
'our_jsonb': None,
'our_bigint': None,
'OUR TIME TZ': None,
'our_store': None,
'OUR TS TZ': None,
'our_smallint': None,
'OUR DATE': None,
'our_varchar': None,
'OUR TS': None,
'our_uuid': None,
'our_varchar_10': None,
'our_citext': None,
'our_inet': None,
'our_cidr': None,
'our_mac': None,
'our_alignment_enum': None,
'our_money': None
})
for record in self.inserted_records:
db_utils.insert_record(cur, test_table_name, record)
def setUp(self):
db_utils.ensure_environment_variables_set()
db_utils.ensure_db('dev')
self.maxDiff = None
with db_utils.get_test_connection('dev') as conn:
conn.autocommit = True
with conn.cursor(cursor_factory=psycopg2.extras.DictCursor) as cur:
cur = db_utils.ensure_fresh_table(conn, cur, test_schema_name,
test_table_name)
create_table_sql = """
CREATE TABLE {} (id SERIAL PRIMARY KEY,
our_varchar VARCHAR,
our_varchar_10 VARCHAR(10),
our_text TEXT,
our_integer INTEGER,
our_smallint SMALLINT,
our_bigint BIGINT,
our_decimal NUMERIC(12,2),
"OUR TS" TIMESTAMP WITHOUT TIME ZONE,
"OUR TS TZ" TIMESTAMP WITH TIME ZONE,
"OUR TIME" TIME WITHOUT TIME ZONE,
"OUR TIME TZ" TIME WITH TIME ZONE,
"OUR DATE" DATE,
our_double DOUBLE PRECISION,
our_real REAL,
our_boolean BOOLEAN,
our_bit BIT(1),
our_json JSON,
our_jsonb JSONB,
our_uuid UUID,
our_store HSTORE,
our_citext CITEXT,
our_inet inet,
our_cidr cidr,
our_mac macaddr,
our_money money)
""".format(
db_utils.canonicalized_table_name(cur, test_schema_name,
test_table_name))
cur.execute(create_table_sql)
# insert fixture data and track expected records
self.inserted_records = []
self.expected_records = []
nyc_tz = pytz.timezone('America/New_York')
our_time_offset = "-04:00"
# record 1
our_ts = datetime.datetime(1977, 3, 3, 3, 3, 3, 733184)
our_ts_tz = nyc_tz.localize(our_ts)
our_time = datetime.time(10, 9, 8)
our_time_tz = our_time.isoformat() + our_time_offset
our_date = datetime.date(1964, 7, 1)
my_uuid = str(uuid.uuid1())
self.inserted_records.append({
'our_varchar':
"our_varchar 2",
'our_varchar_10':
"varchar_10",
'our_text':
"some text 2",
'our_integer':
44101,
'our_smallint':
2,
'our_bigint':
1000001,
'our_decimal':
decimal.Decimal('9876543210.02'),
quote_ident('OUR TS', cur):
our_ts,
quote_ident('OUR TS TZ', cur):
our_ts_tz,
quote_ident('OUR TIME', cur):
our_time,
quote_ident('OUR TIME TZ', cur):
our_time_tz,
quote_ident('OUR DATE', cur):
our_date,
'our_double':
decimal.Decimal('1.1'),
'our_real':
decimal.Decimal('1.2'),
'our_boolean':
True,
'our_bit':
'1',
'our_json':
json.dumps({'nymn': 77}),
'our_jsonb':
json.dumps({'burgers': 'good++'}),
'our_uuid':
my_uuid,
'our_citext':
'cyclops 2',
'our_store':
'dances=>"floor",name=>"betty"',
'our_cidr':
'192.168.101.128/25',
'our_inet':
'192.168.101.128/24',
'our_mac':
'08:00:2b:01:02:04',
})
self.expected_records.append({
'our_decimal':
decimal.Decimal('9876543210.02'),
'OUR TIME':
str(our_time),
'our_text':
'some text 2',
'our_bit':
True,
'our_integer':
44101,
'our_double':
decimal.Decimal('1.1'),
'id':
1,
'our_json':
'{"nymn": 77}',
'our_boolean':
True,
'our_jsonb':
'{"burgers": "good++"}',
'our_bigint':
1000001,
'OUR TIME TZ':
str(our_time_tz),
'our_store': {
"name": "betty",
"dances": "floor"
},
'OUR TS TZ':
self.expected_ts_tz(our_ts_tz),
'our_smallint':
2,
'OUR DATE':
'1964-07-01T00:00:00+00:00',
'our_varchar':
'our_varchar 2',
'OUR TS':
self.expected_ts(our_ts),
'our_uuid':
self.inserted_records[0]['our_uuid'],
'our_real':
decimal.Decimal('1.2'),
'our_varchar_10':
'varchar_10',
'our_citext':
self.inserted_records[0]['our_citext'],
'our_inet':
self.inserted_records[0]['our_inet'],
'our_cidr':
self.inserted_records[0]['our_cidr'],
'our_mac':
self.inserted_records[0]['our_mac'],
'our_money':
None
})
# record 2
our_ts = datetime.datetime(1987, 2, 2, 2, 2, 2, 722184)
our_ts_tz = nyc_tz.localize(our_ts)
our_time = datetime.time(12, 11, 10)
our_time_tz = our_time.isoformat() + our_time_offset
our_date = datetime.date(1998, 3, 4)
my_uuid = str(uuid.uuid1())
self.inserted_records.append({
'our_varchar':
"our_varchar",
'our_varchar_10':
"varchar_10",
'our_text':
"some text",
'our_integer':
44100,
'our_smallint':
1,
'our_bigint':
1000000,
'our_decimal':
decimal.Decimal('1234567890.01'),
quote_ident('OUR TS', cur):
our_ts,
quote_ident('OUR TS TZ', cur):
our_ts_tz,
quote_ident('OUR TIME', cur):
our_time,
quote_ident('OUR TIME TZ', cur):
our_time_tz,
quote_ident('OUR DATE', cur):
our_date,
'our_double':
decimal.Decimal('1.1'),
'our_real':
decimal.Decimal('1.2'),
'our_boolean':
True,
'our_bit':
'0',
'our_json':
json.dumps({'secret': 55}),
'our_jsonb':
json.dumps(6777777),
'our_uuid':
my_uuid,
'our_store':
'size=>"small",name=>"betty"',
'our_citext':
'cyclops 1',
'our_cidr':
'192.168.100.128/25',
'our_inet':
'192.168.100.128/24',
'our_mac':
'08:00:2b:01:02:03',
'our_money':
'$1,445.5678'
})
self.expected_records.append({
'our_decimal':
decimal.Decimal('1234567890.01'),
'our_text':
'some text',
'our_bit':
False,
'our_integer':
44100,
'our_double':
decimal.Decimal('1.1'),
'id':
2,
'our_json':
'{"secret": 55}',
'our_boolean':
True,
'our_jsonb':
self.inserted_records[1]['our_jsonb'],
'our_bigint':
1000000,
'OUR TS':
self.expected_ts(our_ts),
'OUR TS TZ':
self.expected_ts_tz(our_ts_tz),
'OUR TIME':
str(our_time),
'OUR TIME TZ':
str(our_time_tz),
'our_store': {
"name": "betty",
"size": "small"
},
'our_smallint':
1,
'OUR DATE':
'1998-03-04T00:00:00+00:00',
'our_varchar':
'our_varchar',
'our_uuid':
self.inserted_records[1]['our_uuid'],
'our_real':
decimal.Decimal('1.2'),
'our_varchar_10':
'varchar_10',
'our_citext':
self.inserted_records[1]['our_citext'],
'our_inet':
self.inserted_records[1]['our_inet'],
'our_cidr':
self.inserted_records[1]['our_cidr'],
'our_mac':
self.inserted_records[1]['our_mac'],
'our_money':
'$1,445.57'
})
# record 3
our_ts = datetime.datetime(1997, 2, 2, 2, 2, 2, 722184)
our_ts_tz = nyc_tz.localize(our_ts)
our_time = datetime.time(12, 11, 10)
our_time_tz = our_time.isoformat() + our_time_offset
our_date = datetime.date(1998, 3, 4)
my_uuid = str(uuid.uuid1())
self.inserted_records.append({
'our_varchar':
"our_varchar",
'our_varchar_10':
"varchar_10",
'our_text':
"some text",
'our_integer':
44100,
'our_smallint':
1,
'our_bigint':
1000000,
'our_decimal':
decimal.Decimal('1234567890.01'),
quote_ident('OUR TS', cur):
our_ts,
quote_ident('OUR TS TZ', cur):
our_ts_tz,
quote_ident('OUR TIME', cur):
our_time,
quote_ident('OUR TIME TZ', cur):
our_time_tz,
quote_ident('OUR DATE', cur):
our_date,
'our_double':
'1.1',
'our_real':
decimal.Decimal('1.2'),
'our_boolean':
True,
'our_bit':
'0',
'our_json':
json.dumps({'secret': 55}),
'our_jsonb':
json.dumps(6777777),
'our_uuid':
my_uuid,
'our_store':
'size=>"small",name=>"betty"',
'our_citext':
'cyclops 1',
'our_cidr':
'192.168.100.128/25',
'our_inet':
'192.168.100.128/24',
'our_mac':
'08:00:2b:01:02:03',
'our_money':
'$1,445.5678'
})
self.expected_records.append({
'our_decimal':
decimal.Decimal('1234567890.01'),
'our_text':
'some text',
'our_bit':
False,
'our_integer':
44100,
'our_double':
decimal.Decimal('1.1'),
'id':
3,
'our_json':
'{"secret": 55}',
'our_boolean':
True,
'our_jsonb':
self.inserted_records[1]['our_jsonb'],
'our_bigint':
1000000,
'OUR TS':
self.expected_ts(our_ts),
'OUR TS TZ':
self.expected_ts_tz(our_ts_tz),
'OUR TIME':
str(our_time),
'OUR TIME TZ':
str(our_time_tz),
'our_store': {
"name": "betty",
"size": "small"
},
'our_smallint':
1,
'OUR DATE':
'1998-03-04T00:00:00+00:00',
'our_varchar':
'our_varchar',
'our_uuid':
self.inserted_records[2]['our_uuid'],
'our_real':
decimal.Decimal('1.2'),
'our_varchar_10':
'varchar_10',
'our_citext':
self.inserted_records[2]['our_citext'],
'our_inet':
self.inserted_records[2]['our_inet'],
'our_cidr':
self.inserted_records[2]['our_cidr'],
'our_mac':
self.inserted_records[2]['our_mac'],
'our_money':
'$1,445.57'
})
for rec in self.inserted_records:
db_utils.insert_record(cur, test_table_name, rec)