pgloader/test/mysql/my.load
Dimitri Fontaine d8b0bd5145 Allow casting rules to guard on signed data types.
It used to be that our casting rules mechanism would allow for matching
unsigned data types only, and we sometimes have a need to do special
behavior on signed data types.

In particular, a signed bigint(20) in MySQL has the same values range as a
PostgreSQL bigint, so we don't need to target a numeric in that case. It's
only when the bigint is unsigned that we need to target a numeric.

In passing update some of the default casting rules documentation to match
the code.

Fix #982.
2019-06-04 15:22:25 +02:00

46 lines
1.4 KiB
Fish

load database
-- from mysql://root@localhost/pgloader
from mysql://root@unix:/tmp/mysql.sock:3306/pgloader
into postgresql:///pgloader
WITH concurrency = 2,
multiple readers per thread,
rows per range = 50000,
quote identifiers
ALTER SCHEMA 'pgloader' RENAME TO 'mysql'
ALTER TABLE NAMES MATCHING ~/./ SET TABLESPACE 'pg_default'
-- INCLUDING ONLY TABLE NAMES MATCHING 'encryption_key_canary'
CAST column utilisateurs__Yvelines2013-06-28.sexe
to text drop not null using empty-string-to-null,
column base64.id to uuid drop typemod,
column base64.data to jsonb using base64-decode,
-- This is now a default casting rule for MySQL
-- type bigint when signed to bigint drop typemod,
type decimal
when (and (= 18 precision) (= 6 scale))
to "double precision" drop typemod,
type smallint when unsigned to int drop typemod,
type timestamp
when default "CURRENT_TIMESTAMP"
with extra on update current timestamp
to "timestamp with time zone"
drop default drop not null drop extra
using zero-dates-to-null,
type timestamp with extra on update current timestamp
to "timestamp with time zone" drop extra,
column encryption_key_canary.uuid
to uuid drop typemod using byte-vector-to-hexstring
BEFORE LOAD DO
$$ create schema if not exists mysql; $$;