Skip to content

#112 negative argument as IDENTITY #149

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Merged
madtibo merged 3 commits into from
Apr 29, 2021
Merged

#112 negative argument as IDENTITY #149

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
3 commits
Select commit Hold shift + click to select a range
28be92b
support IDENTITY with negative arguments; detect end of table more ge…
potrusil-osi Sep 6, 2019
33ce6af
Add tests
potrusil-osi Oct 15, 2019
621b25f
rebase patch by potrusil-osi
madtibo Apr 29, 2021
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
1 change: 1 addition & 0 deletions contributors
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -16,6 +16,7 @@ bsacks99
keyjote
mark-jay
mikes-gh
postrusil-osi
sebpcspkr
stuey1978

Expand Down
46 changes: 46 additions & 0 deletions regression/issue_112.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1,46 @@
CREATE TABLE [dbo].[AFElementAttributeCategory](
[rid] [bigint] IDENTITY(-1,-1) NOT NULL,
[id] [uniqueidentifier] NOT NULL,
[rowversion] [timestamp] NOT NULL,
[fkelementversionid] [bigint] NOT NULL,
[fkparentattributeid] [uniqueidentifier] NULL,
[fkcategoryid] [uniqueidentifier] NOT NULL,
[changedby] [int] NOT NULL,
CONSTRAINT [PK_AFElementAttributeCategory] PRIMARY KEY CLUSTERED
(
[rid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [ASSETS]
) ON [ASSETS]
GO

CREATE TABLE [dbo].[AFCaseAdjustment](
[rid] [bigint] IDENTITY(-1,-1) NOT NULL,
[id] [uniqueidentifier] NOT NULL,
[rowversion] [timestamp] NOT NULL,
[fkcaseid] [bigint] NOT NULL,
[attributeid] [uniqueidentifier] NOT NULL,
[adjustedvalue] [varbinary](max) NULL,
[comment] [nvarchar](1000) NULL,
[previousvalue] [varbinary](max) NULL,
[creator] [nvarchar](50) NULL,
[creationdate] [datetime2](7) NULL,
[changedby] [int] NOT NULL,
CONSTRAINT [PK_AFCaseAdjustment] PRIMARY KEY NONCLUSTERED
(
[rid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [ANALYSIS]
) ON [ANALYSIS] TEXTIMAGE_ON [ANALYSIS]
GO

CREATE TABLE [dbo].[sd](
[rid] [int] IDENTITY(1000,1) NOT NULL,
[rowversion] [timestamp] NOT NULL,
[sd] [nvarchar](max) NOT NULL,
[ownerRights] [int] NOT NULL,
[lupd] [datetime2](7) NULL,
CONSTRAINT [pk_sd] PRIMARY KEY CLUSTERED
(
[rid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [ASSETS]
) ON [ASSETS] TEXTIMAGE_ON [ASSETS]
GO
10 changes: 4 additions & 6 deletions sqlserver2pgsql.pl
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -1348,7 +1348,7 @@ sub add_column_to_table

# We have an identity field. We remember the default value and
# initialize the sequence correctly in the after script
$isidentity =~ /IDENTITY\s*\((\d+),\s*(\d+)\)/
$isidentity =~ /IDENTITY\s*\((-?\d+),\s*(-?\d+)\)/
or die "Cannot understand <$isidentity>";
my $startseq = $1;
my $stepseq = $2;
Expand All @@ -1365,8 +1365,6 @@ sub add_column_to_table

$objects->{SCHEMAS}->{$schemaname}->{SEQUENCES}->{$seqname}->{START}
= $startseq;
$objects->{SCHEMAS}->{$schemaname}->{SEQUENCES}->{$seqname}->{MIN}
= $startseq;
$objects->{SCHEMAS}->{$schemaname}->{SEQUENCES}->{$seqname}->{STEP}
= $stepseq;
$objects->{SCHEMAS}->{$schemaname}->{SEQUENCES}->{$seqname}
Expand Down Expand Up @@ -1449,7 +1447,7 @@ sub parse_dump
# column name, typical microsoft stuff :( )
# To make matters even worse, they seem to systematically add a space after it :)
if ($line =~
/^\s+\[(.*)\]\s*(?:\[(.*)\]\.)?\[(.*)\]\s*(\(.+?\))?(?: COLLATE (\S+))?( IDENTITY\s*\(\d+,\s*\d+\))?(?: ROWGUIDCOL ?)? (?:NOT FOR REPLICATION )?(?:SPARSE +)?(NOT NULL|NULL)(?:\s+CONSTRAINT \[.*\])?(?:\s+DEFAULT \((.*)\))?(?:,|$)?/
/^\s+\[(.*)\]\s*(?:\[(.*)\]\.)?\[(.*)\]\s*(\(.+?\))?(?: COLLATE (\S+))?( IDENTITY\s*\(-?\d+,\s*-?\d+\))?(?: ROWGUIDCOL ?)? (?:NOT FOR REPLICATION )?(?:SPARSE +)?(NOT NULL|NULL)(?:\s+CONSTRAINT \[.*\])?(?:\s+DEFAULT \((.*)\))?(?:,|$)?/
)
{
# Deported into a function because we can also meet alter table add columns on their own
Expand Down Expand Up @@ -1983,7 +1981,7 @@ sub parse_dump
# Added table columns… this seems to appear in SQL Server when some columns have ANSI padding, and some not.
# PG follows ANSI, that is not an option. The end of the regexp is pasted from the create table
elsif ($line =~
/^ALTER TABLE \[(.*)\]\.\[(.*)\] ADD \[(.*)\] (?:\[(.*)\]\.)?\[(.*)\](\(.+?\))?( IDENTITY\(\d+,\s*\d+\))? (NOT NULL|NULL)(?: CONSTRAINT \[.*\] )?(?: DEFAULT \(.*\))?$/
/^ALTER TABLE \[(.*)\]\.\[(.*)\] ADD \[(.*)\] (?:\[(.*)\]\.)?\[(.*)\](\(.+?\))?( IDENTITY\(-?\d+,\s*-?\d+\))? (NOT NULL|NULL)(?: CONSTRAINT \[.*\] )?(?: DEFAULT \(.*\))?$/
)
{
my $schemaname=relabel_schemas($1);
Expand Down Expand Up @@ -2883,7 +2881,7 @@ sub generate_schema
# This may not be an identity. Skip it then
next unless defined ($seqref->{OWNERCOL});
print AFTER "select setval('" . format_identifier($schema) . '.'
. format_identifier($sequence) . "',(select max("
. format_identifier($sequence) . "',(select " . ($seqref->{STEP} > 0 ? "max" : "min") . "("
. format_identifier($seqref->{OWNERCOL}) .") from "
. format_identifier($seqref->{OWNERSCHEMA}) . '.'
. format_identifier($seqref->{OWNERTABLE}) . ")::bigint);\n";
Expand Down