Skip to content

Commit 5b8317c

Browse files
author
Marc Cousin
committed
Added a remapping functionnality
1 parent 6700ef7 commit 5b8317c

3 files changed

Lines changed: 74 additions & 36 deletions

File tree

‎README.md‎

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -83,6 +83,9 @@ You can also use the -i, -num and/or -nr options:
8383
-nr : Don't convert the dbo schema to public. By default, this conversion is done, as it converts MSSQL's default
8484
schema to PostgreSQL's default schema
8585

86+
-relabel_schemas is a list of schemas to remap. The syntax is : 'source1=>dest1;source2=>dest2'. Don't forget to quote this option or the shell might alter it
87+
there is a default dbo=>public remapping, that can be cancelled with -nr
88+
8689
-num : Converts numeric (xxx,0) to the appropriate smallint, integer or bigint. It won't keep the constraint on
8790
the size of the scale of the numeric
8891

‎example_conf_file‎

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -21,5 +21,7 @@ postgresql password=bar_password
2121

2222
# Optional behaviour
2323
case insensitive=1 # set it to 0 to generate a dump with citext and check constraints all over the place
24-
no relabel dbo=0 # set it to 0 to convert the dbo schema to public
24+
no relabel dbo=1 # set it to 0 to convert the dbo schema to public
2525
convert numeric to int=1 # set it to 0 to keep numeric(xx,0) as numeric(xx,0). Will be converted to smallint, int or bigint by default
26+
relabel schemas=dbo=>foo;schema1=>bar
27+

‎sqlserver2pgsql.pl‎

Lines changed: 68 additions & 35 deletions
Original file line numberDiff line numberDiff line change
@@ -31,12 +31,11 @@
3131
our ($sd, $sh, $sp, $su, $sw, $pd, $ph, $pp, $pu, $pw); # Connection args
3232
our $conf_file;
3333
our $filename; # Filename passed as arg
34-
our $case_insensitive = 0
35-
; # Passed as arg: was SQL Server installation case insensitive ? PostgreSQL can't ignore accents anyway
34+
our $case_insensitive; # Passed as arg: was SQL Server installation case insensitive ? PostgreSQL can't ignore accents anyway
3635
# If yes, we will generate citext with CHECK constraints, that's the best we can do
37-
our $norelabel_dbo = 0; # Passed as arg: should we convert DBO to public ?
38-
our $convert_numeric_to_int = 0
39-
; # Should we convert numerics to int when possible ? (numeric (4,0) could be converted an int, for instance)
36+
our $norelabel_dbo; # Passed as arg: should we convert DBO to public ?
37+
our $relabel_schemas;
38+
our $convert_numeric_to_int; # Should we convert numerics to int when possible ? (numeric (4,0) could be converted an int, for instance)
4039
our $kettle;
4140
our $before_file;
4241
our $after_file;
@@ -76,6 +75,7 @@ sub parse_conf_file
7675
'case insensitive' => 'case_insensitive',
7776
'no relabel dbo' => 'norelabel_dbo',
7877
'convert numeric to int' => 'convert_numeric_to_int',
78+
'relabel schemas' => 'relabel_schemas',
7979
);
8080

8181
# Open the conf file or die
@@ -87,7 +87,7 @@ sub parse_conf_file
8787
$line =~ s/\s+$//; # Remove trailing whitespaces
8888
next
8989
if ($line =~ /^$/); # Empty line after comments have been removed
90-
$line =~ /^(.*)=(.*)$/ or die "Cannot parse $line from $conf_file";
90+
$line =~ /^(.*?)=(.*)$/ or die "Cannot parse $line from $conf_file";
9191
my ($param, $value) = ($1, $2);
9292
no strict 'refs'; # Using references by name, temporarily
9393
unless (defined $parameters{$param})
@@ -102,6 +102,10 @@ sub parse_conf_file
102102
$$param_name = $value;
103103
use strict 'refs';
104104
}
105+
# Hard coded default values
106+
$case_insensitive=0 unless (defined ($case_insensitive));
107+
$norelabel_dbo=0 unless (defined ($norelabel_dbo));
108+
$convert_numeric_to_int=0 unless (defined ($convert_numeric_to_int));
105109
close CONF;
106110
}
107111

@@ -267,15 +271,36 @@ sub next_col_pos
267271
}
268272
}
269273

270-
# This relabels the string if it is dbo and we want to relabel it to public
271-
sub dboreplace
272274
{
273-
my ($schema) = @_;
274-
return $schema if ($schema ne 'dbo');
275-
return 'public' unless ($norelabel_dbo);
276-
return 'dbo';
277-
}
275+
# This builds %relabel_schemas for use in the next function. Both are scoped so that %relabel_schemas is not visible from outside
276+
my %relabel_schemas;
277+
sub build_relabel_schemas
278+
{
279+
foreach my $pair (split (';',$relabel_schemas))
280+
{
281+
my @pair=split('=>',$pair);
282+
unless (scalar(@pair)==2)
283+
{
284+
die "Cannot parse the schema list given as argument: <$relabel_schemas>\n";
285+
}
286+
$relabel_schemas{$pair[0]}=$pair[1];
287+
}
288+
# Don't forget dbo -> public if it was asked
289+
unless ($norelabel_dbo)
290+
{
291+
$relabel_schemas{'dbo'}='public';
292+
}
293+
}
294+
278295

296+
# This relabels the schemas
297+
sub relabel_schemas
298+
{
299+
my ($schema) = @_;
300+
return $schema unless (defined $relabel_schemas{$schema});
301+
return $relabel_schemas{$schema};
302+
}
303+
}
279304
# Test if we are on windows. We will have to convert / to \ in the XML files
280305
sub is_windows
281306
{
@@ -335,6 +360,9 @@ sub usage
335360
"-nr tells $0 not to convert the dbo schema to public. dbo will stay dbo\n";
336361
print
337362
"-num tells $0 to convert numeric xxx,0 to int, bigint, etc. Will not keep numeric scale and precision for the converted\n";
363+
print
364+
"-relabel_schemas gives a list of schemas to rename. For instance -relabel_schemas 'source1=>dest1;source2=>dest2'\n";
365+
print " -nr simply cancels the default dbo=>public remapping. Don't forget to put the remapping between quotes\n";
338366
print "before_file contains the structure\n";
339367
print "after_file contains index, constraints\n";
340368
print
@@ -372,7 +400,7 @@ sub generate_kettle
372400
foreach my $schema (sort keys %{$objects})
373401
{
374402
my $refschema = $objects->{$schema};
375-
my $targetschema = dboreplace($schema);
403+
my $targetschema = relabel_schemas($schema);
376404

377405
foreach my $table (sort keys %{$refschema->{TABLES}})
378406
{
@@ -675,7 +703,7 @@ sub parse_dump
675703
$objects->{$schemaname}->{TABLES}->{$tablename}->{COLS}
676704
->{$colname}->{DEFAULT}->{VALUE} =
677705
"nextval('"
678-
. dboreplace(${schemaname}) . '.'
706+
. relabel_schemas(${schemaname}) . '.'
679707
. ${seqname} . "')";
680708
$objects->{$schemaname}->{TABLES}->{$tablename}->{COLS}
681709
->{$colname}->{DEFAULT}->{UNSURE} = 0;
@@ -908,7 +936,7 @@ sub parse_dump
908936
{
909937
$schemaname = 'dbo';
910938
}
911-
$schemaname = dboreplace($schemaname);
939+
$schemaname = relabel_schemas($schemaname);
912940

913941
my $sql = $1 . ' ' . $schemaname . '.' . $3 . ' ' . $4 . "\n";
914942
while (my $line_cont = read_and_clean($file))
@@ -918,7 +946,7 @@ sub parse_dump
918946
{
919947
# The view definition is complete.
920948
# We get rid of dbo. schemas
921-
$sql =~ s/(dbo)\./dboreplace($1) . '.'/eg
949+
$sql =~ s/(dbo)\./relabel_schemas($1) . '.'/eg
922950
; # We put this in the replacement schema
923951

924952
# Views will be stored without the full schema in them. We will
@@ -945,7 +973,7 @@ sub parse_dump
945973

946974
# We add them to known data types, as they probably will be used in table definitions
947975
# but they point to themselves, with the schema corrected: we want them substituted by themselves
948-
$types{$schema . '.' . $type} = dboreplace($schema) . '.'
976+
$types{$schema . '.' . $type} = relabel_schemas($schema) . '.'
949977
. $type; # We store the schema with it
950978
}
951979
elsif ($line =~
@@ -1352,7 +1380,7 @@ sub generate_schema
13521380
# The schemas. don't create empty schema, sql server creates a schema per user, even if it ends empty
13531381
foreach my $schema (sort keys %{$objects})
13541382
{
1355-
unless (dboreplace($schema) eq 'public'
1383+
unless (relabel_schemas($schema) eq 'public'
13561384
or not defined $objects->{$schema})
13571385
{
13581386
print BEFORE "CREATE SCHEMA $schema;\n";
@@ -1365,7 +1393,7 @@ sub generate_schema
13651393
# We have to do all domains before all tables
13661394
while (my ($schema, $refschema) = each %{$objects})
13671395
{
1368-
$schema = dboreplace($schema)
1396+
$schema = relabel_schemas($schema)
13691397
; # If dbo, put into public, unless asked otherwise
13701398
# The user-defined types (domains, etc)
13711399
foreach my $domain (sort keys %{$refschema->{DOMAINS}})
@@ -1379,7 +1407,7 @@ sub generate_schema
13791407
# Tables and columns
13801408
while (my ($schema, $refschema) = each %{$objects})
13811409
{
1382-
$schema = dboreplace($schema)
1410+
$schema = relabel_schemas($schema)
13831411
; # If dbo, put into public, unless asked otherwise
13841412

13851413
# The tables
@@ -1410,7 +1438,7 @@ sub generate_schema
14101438
# Sequences, PKs, Indexes
14111439
while (my ($schema, $refschema) = each %{$objects})
14121440
{
1413-
$schema = dboreplace($schema)
1441+
$schema = relabel_schemas($schema)
14141442
; # If dbo, put into public, unless asked otherwise
14151443
# We now add all "AFTER" objects
14161444
# We start with SEQUENCES, PKs and INDEXES (will be needed for FK)
@@ -1423,7 +1451,7 @@ sub generate_schema
14231451
. " START WITH "
14241452
. $seqref->{START}
14251453
. " OWNED BY "
1426-
. dboreplace($seqref->{OWNERSCHEMA}) . '.'
1454+
. relabel_schemas($seqref->{OWNERSCHEMA}) . '.'
14271455
. $seqref->{OWNERTABLE} . ";\n";
14281456
}
14291457

@@ -1453,7 +1481,7 @@ sub generate_schema
14531481
# Unique
14541482
while (my ($schema, $refschema) = each %{$objects})
14551483
{
1456-
$schema = dboreplace($schema)
1484+
$schema = relabel_schemas($schema)
14571485
; # If dbo, put into public, unless asked otherwise
14581486

14591487
# Now The UNIQUE constraints. They may be used for FK (if columns are not null)
@@ -1477,7 +1505,7 @@ sub generate_schema
14771505
# Other constraints
14781506
while (my ($schema, $refschema) = each %{$objects})
14791507
{
1480-
$schema = dboreplace($schema)
1508+
$schema = relabel_schemas($schema)
14811509
; # If dbo, put into public, unless asked otherwise
14821510

14831511
# We have all we need for FKs now. We can put all other constraints (except PK of course)
@@ -1499,7 +1527,7 @@ sub generate_schema
14991527
" FOREIGN KEY ("
15001528
. $constraint->{LOCAL_COLS} . ")"
15011529
. " REFERENCES "
1502-
. dboreplace($constraint->{REMOTE_SCHEMA}) . '.'
1530+
. relabel_schemas($constraint->{REMOTE_SCHEMA}) . '.'
15031531
. $constraint->{REMOTE_TABLE} . " ( "
15041532
. $constraint->{REMOTE_COLS} . ")";
15051533
if (defined $constraint->{ON_DEL_CASC}
@@ -1540,7 +1568,7 @@ sub generate_schema
15401568
# Indexes.
15411569
while (my ($schema, $refschema) = each %{$objects})
15421570
{
1543-
$schema = dboreplace($schema)
1571+
$schema = relabel_schemas($schema)
15441572
; # If dbo, put into public, unless asked otherwise
15451573
# Indexes
15461574
# They don't have a schema qualifier. But their table has, and they are in the same schema as their table
@@ -1565,7 +1593,7 @@ sub generate_schema
15651593
# Default values
15661594
while (my ($schema, $refschema) = each %{$objects})
15671595
{
1568-
$schema = dboreplace($schema)
1596+
$schema = relabel_schemas($schema)
15691597
; # If dbo, put into public, unless asked otherwise
15701598
# Default values
15711599
foreach my $table (sort keys %{$refschema->{TABLES}})
@@ -1592,7 +1620,7 @@ sub generate_schema
15921620
# Comments on tables and columns
15931621
while (my ($schema, $refschema) = each %{$objects})
15941622
{
1595-
$schema = dboreplace($schema)
1623+
$schema = relabel_schemas($schema)
15961624
; # If dbo, put into public, unless asked otherwise
15971625
# Comments on tables
15981626
foreach my $table (sort keys %{$refschema->{TABLES}})
@@ -1617,7 +1645,7 @@ sub generate_schema
16171645
# Views, and their comments
16181646
while (my ($schema, $refschema) = each %{$objects})
16191647
{
1620-
$schema = dboreplace($schema)
1648+
$schema = relabel_schemas($schema)
16211649
; # If dbo, put into public, unless asked otherwise
16221650
# The views, and comments
16231651
foreach my $view (sort keys %{$refschema->{VIEWS}})
@@ -1633,7 +1661,7 @@ sub generate_schema
16331661
# Trigger functions
16341662
while (my ($schema, $refschema) = each %{$objects})
16351663
{
1636-
$schema = dboreplace($schema)
1664+
$schema = relabel_schemas($schema)
16371665
; # If dbo, put into public, unless asked otherwise
16381666
# The trigger functions
16391667
foreach my $triggerfunc (sort keys %{$refschema->{TRIG_FUNCTIONS}})
@@ -1649,7 +1677,7 @@ sub generate_schema
16491677
# Triggers
16501678
while (my ($schema, $refschema) = each %{$objects})
16511679
{
1652-
$schema = dboreplace($schema)
1680+
$schema = relabel_schemas($schema)
16531681
; # If dbo, put into public, unless asked otherwise
16541682
# triggers on tables, as these functions are declared now
16551683
foreach my $table (sort keys %{$refschema->{TABLES}})
@@ -1729,10 +1757,10 @@ sub resolve_name_conflicts
17291757
# If a column has a custom type, it will be prefixed by schema
17301758
# The schema will be the destination schema: dbo may have been replaced by public
17311759
if ($col->{TYPE} eq
1732-
(dboreplace($schema) . '.' . $domain))
1760+
(relabel_schemas($schema) . '.' . $domain))
17331761
{
17341762
$col->{TYPE} =
1735-
dboreplace($schema) . '.' . $domain . "2pgd";
1763+
relabel_schemas($schema) . '.' . $domain . "2pgd";
17361764
}
17371765
}
17381766
}
@@ -1794,7 +1822,8 @@ sub resolve_name_conflicts
17941822
"f=s" => \$filename,
17951823
"i" => \$case_insensitive,
17961824
"nr" => \$norelabel_dbo,
1797-
"num" => \$convert_numeric_to_int,);
1825+
"num" => \$convert_numeric_to_int,
1826+
"relabel_schemas=s" => \$relabel_schemas,);
17981827

17991828
# We don't understand command line or have been asked for usage
18001829
if (not $options or $help)
@@ -1839,6 +1868,10 @@ sub resolve_name_conflicts
18391868
exit 1;
18401869
}
18411870

1871+
# We need to build %relabel_schemas from $relabel_schemas
1872+
build_relabel_schemas();
1873+
1874+
18421875
# Read SQL Server's dump file
18431876
parse_dump();
18441877

0 commit comments

Comments
 (0)