SET LANGUAGE us_english;  -- avoid month day swap in datetime

GO

GO

GO

GO

GO

GO

GO

GO

GO

GO

GO

GO

GO

GO

DROP SCHEMA qgis_test;
GO

CREATE SCHEMA qgis_test;
GO

CREATE TABLE qgis_test.[someData] (
    pk integer PRIMARY KEY,
    cnt integer,
    name nvarchar(max) DEFAULT 'qgis',
    name2 nvarchar(max) DEFAULT 'qgis',
    num_char nvarchar(max),
    dt datetime,
    [date] date,
    [time] time,
    geom geometry
);
GO

CREATE TABLE qgis_test.[some_poly_data] (
    pk integer PRIMARY KEY,
    geom geometry
);
GO

CREATE TABLE qgis_test.[date_times] (
       id integer PRIMARY KEY,
       date_field date,
       time_field time,
       datetime_field datetime
);
GO

CREATE TABLE qgis_test.[float_dec] (
       id integer PRIMARY KEY,
       float_field float,
       dec_field decimal(7,3)
);
GO

CREATE TABLE qgis_test.[multiGeomColumns] (
    pk integer PRIMARY KEY,
    geom1 geometry,
    geom2 geometry
);
GO

CREATE TABLE qgis_test.[sacrificialLamb] (
    pk integer PRIMARY KEY
);
GO

INSERT INTO qgis_test.[someData] (pk, cnt, name, name2, num_char, dt, [date], [time], geom) VALUES
(5, -200, NULL, 'NuLl', '5', '2020-05-04 12:13:14', '2020-05-02', '12:13:01', geometry::STGeomFromText( 'Point(-71.123 78.23)', 4326 )),
(3,  300, 'Pear', 'PEaR', '3', NULL, NULL, NULL, NULL),
(1,  100, 'Orange', 'oranGe', '1', '2020-05-03 12:13:14', '2020-05-03', '12:13:14', geometry::STGeomFromText( 'Point(-70.332 66.33)', 4326 )),
(2,  200, 'Apple', 'Apple', '2', '2020-05-04 12:14:14', '2020-05-04', '12:14:14', geometry::STGeomFromText( 'Point(-68.2 70.8)', 4326 )),
(4,  400, 'Honey', 'Honey', '4', '2021-05-04 13:13:14', '2021-05-04', '13:13:14', geometry::STGeomFromText( 'Point(-65.32 78.3)', 4326 ))
;
GO

INSERT INTO qgis_test.[some_poly_data] (pk, geom) VALUES
(1, geometry::STGeomFromText('Polygon ((-69.0 81.4, -69.0 80.2, -73.7 80.2, -73.7 76.3, -74.9 76.3, -74.9 81.4, -69.0 81.4))', 4326 )),
(2, geometry::STGeomFromText('Polygon ((-67.6 81.2, -66.3 81.2, -66.3 76.9, -67.6 76.9, -67.6 81.2))', 4326 )),
(3, geometry::STGeomFromText('Polygon ((-68.4 75.8, -67.5 72.6, -68.6 73.7, -70.2 72.9, -68.4 75.8))', 4326 )),
(4, NULL)
;
GO

INSERT INTO qgis_test.[date_times] (id, date_field, time_field, datetime_field ) VALUES
 (1, '2004-03-04', '13:41:52', '2004-03-04 13:41:52' );

INSERT INTO qgis_test.[float_dec] (id, float_field, dec_field ) VALUES
 (1, 1.1111111111, 1.123 );
GO

INSERT INTO qgis_test.[sacrificialLamb] (pk) VALUES
 (1);
GO

INSERT INTO qgis_test.[multiGeomColumns] (pk, geom1, geom2) VALUES
(5, geometry::STGeomFromText( 'Point( 1 2 )', 4326 ), NULL),
(3, NULL, geometry::STGeomFromText( 'LineString( 1 2, 3 4 )', 4326 )),
(1, geometry::STGeomFromText( 'Point( 2 3 )', 4326 ), geometry::STGeomFromText( 'LineString( 2 3, 4 5 )', 4326 )),
(2, geometry::STGeomFromText( 'Point( 3 4 )', 4326 ), geometry::STGeomFromText( 'LineString( 3 4, 5 6 )', 4326 )),
(4, geometry::STGeomFromText( 'Point( 5 6 )', 4326 ), geometry::STGeomFromText( 'LineString( 5 6, 7 8 )', 4326 ))
;
GO

/** Contains invalid polygons **/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [qgis_test].[invalid_polys](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[ogr_geometry] [geometry] NULL,
 CONSTRAINT [PK_invalid_parcels] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET IDENTITY_INSERT [qgis_test].[invalid_polys] ON 

GO
INSERT [qgis_test].[invalid_polys] ([id], [ogr_geometry]) VALUES (258348, 0x[BLOB])
GO
INSERT [qgis_test].[invalid_polys] ([id], [ogr_geometry]) VALUES (1528836, 0x[BLOB])
GO
INSERT [qgis_test].[invalid_polys] ([id], [ogr_geometry]) VALUES (1528864, 0x[BLOB])
GO
INSERT [qgis_test].[invalid_polys] ([id], [ogr_geometry]) VALUES (3705263, 0x47100000010405000000988FB5D0DFBE6540284A2C3D9BC044C01CA53ED5E0BE654088FFC34999C044C05CEBA9DAE0BE6540C0D2596399C044C06C4C20D6DFBE6540889B9D569BC044C0988FB5D0DFBE6540284A2C3D9BC044C001000000020000000002000000FFFFFFFF0000000006000000000000000003)
GO
INSERT [qgis_test].[invalid_polys] ([id], [ogr_geometry]) VALUES (3705291, 0x[BLOB])
GO
INSERT [qgis_test].[invalid_polys] ([id], [ogr_geometry]) VALUES (3705302, 0x[BLOB])
GO
INSERT [qgis_test].[invalid_polys] ([id], [ogr_geometry]) VALUES (3705306, 0x[BLOB])
GO
INSERT [qgis_test].[invalid_polys] ([id], [ogr_geometry]) VALUES (3705315, 0x[BLOB])
GO
INSERT [qgis_test].[invalid_polys] ([id], [ogr_geometry]) VALUES (3705332, 0x[BLOB])
GO
INSERT [qgis_test].[invalid_polys] ([id], [ogr_geometry]) VALUES (3705349, 0x[BLOB])
GO
INSERT [qgis_test].[invalid_polys] ([id], [ogr_geometry]) VALUES (3705382, 0x[BLOB])
GO
INSERT [qgis_test].[invalid_polys] ([id], [ogr_geometry]) VALUES (3705449, 0x[BLOB])
GO
INSERT [qgis_test].[invalid_polys] ([id], [ogr_geometry]) VALUES (3705451, 0x[BLOB])
GO
INSERT [qgis_test].[invalid_polys] ([id], [ogr_geometry]) VALUES (3705469, 0x[BLOB])
GO
INSERT [qgis_test].[invalid_polys] ([id], [ogr_geometry]) VALUES (3705540, 0x[BLOB])
GO
INSERT [qgis_test].[invalid_polys] ([id], [ogr_geometry]) VALUES (3705563, 0x[BLOB])
GO
INSERT [qgis_test].[invalid_polys] ([id], [ogr_geometry]) VALUES (3705569, 0x[BLOB])
GO
INSERT [qgis_test].[invalid_polys] ([id], [ogr_geometry]) VALUES (3705589, 0x471000000104050000002C87AEF589BE6540D02039F769C044C0B430B0338ABE6540C02055B975C044C088C606178ABE654028CEEA1A76C044C0607722D789BE6540305ACFFC69C044C02C87AEF589BE6540D02039F769C044C001000000020000000002000000FFFFFFFF0000000006000000000000000003)
GO
INSERT [qgis_test].[invalid_polys] ([id], [ogr_geometry]) VALUES (3705609, 0x[BLOB])
GO
INSERT [qgis_test].[invalid_polys] ([id], [ogr_geometry]) VALUES (3705614, 0x[BLOB])
GO
INSERT [qgis_test].[invalid_polys] ([id], [ogr_geometry]) VALUES (3705618, 0x4710000001040500000078B1231A9CBE654038EF5A9D77C144C02897C62F9CBE6540B0B0ACD677C144C028EB74769BBE6540F896664B7AC144C06430725B9ABE654058F2CE5A77C144C078B1231A9CBE654038EF5A9D77C144C001000000020000000002000000FFFFFFFF0000000006000000000000000003)
GO
INSERT [qgis_test].[invalid_polys] ([id], [ogr_geometry]) VALUES (3705633, 0x[BLOB])
GO
INSERT [qgis_test].[invalid_polys] ([id], [ogr_geometry]) VALUES (3705638, 0x[BLOB])
GO
INSERT [qgis_test].[invalid_polys] ([id], [ogr_geometry]) VALUES (3705672, 0x[BLOB])
GO
INSERT [qgis_test].[invalid_polys] ([id], [ogr_geometry]) VALUES (3705674, 0x471000000104050000007C749C3BA7BE6540C084311F59C044C0CC683283ABBE6540787BC67457C044C0503B57EFABBE65405802846A5EC044C0E0B3380DA8BE6540E053568C60C044C07C749C3BA7BE6540C084311F59C044C001000000020000000002000000FFFFFFFF0000000006000000000000000003)
GO
INSERT [qgis_test].[invalid_polys] ([id], [ogr_geometry]) VALUES (3705686, 0x47100000010404000000605F909990BE654028A996BE21C144C0048EBA8C91BE654070C8124524C144C0D088A6A790BE65404071B66F24C144C0605F909990BE654028A996BE21C144C001000000020000000002000000FFFFFFFF0000000006000000000000000003)
GO
INSERT [qgis_test].[invalid_polys] ([id], [ogr_geometry]) VALUES (3705690, 0x[BLOB])
GO
INSERT [qgis_test].[invalid_polys] ([id], [ogr_geometry]) VALUES (3705802, 0x[BLOB])
GO
INSERT [qgis_test].[invalid_polys] ([id], [ogr_geometry]) VALUES (3705826, 0x[BLOB])
GO
INSERT [qgis_test].[invalid_polys] ([id], [ogr_geometry]) VALUES (3705832, 0x[BLOB])
GO
INSERT [qgis_test].[invalid_polys] ([id], [ogr_geometry]) VALUES (3705835, 0x[BLOB])
GO
INSERT [qgis_test].[invalid_polys] ([id], [ogr_geometry]) VALUES (3705839, 0x[BLOB])
GO
INSERT [qgis_test].[invalid_polys] ([id], [ogr_geometry]) VALUES (3705852, 0x[BLOB])
GO
INSERT [qgis_test].[invalid_polys] ([id], [ogr_geometry]) VALUES (3705886, 0x[BLOB])
GO
INSERT [qgis_test].[invalid_polys] ([id], [ogr_geometry]) VALUES (3705887, 0x[BLOB])
GO
INSERT [qgis_test].[invalid_polys] ([id], [ogr_geometry]) VALUES (3705892, 0x[BLOB])
GO
INSERT [qgis_test].[invalid_polys] ([id], [ogr_geometry]) VALUES (3705902, 0x[BLOB])
GO
INSERT [qgis_test].[invalid_polys] ([id], [ogr_geometry]) VALUES (3705915, 0x[BLOB])
GO
INSERT [qgis_test].[invalid_polys] ([id], [ogr_geometry]) VALUES (3705938, 0x47100000010405000000748C75BBA8BE654098EF25FCAEC044C010A90134AEBE6540D816E36BABC044C0E4B4B40EAFBE65405095162BB3C044C0709E8FD7A8BE654048E5924FB4C044C0748C75BBA8BE654098EF25FCAEC044C001000000020000000002000000FFFFFFFF0000000006000000000000000003)
GO
SET IDENTITY_INSERT [qgis_test].[invalid_polys] OFF
GO

GO


CREATE TABLE [qgis_test].[tb_test_compound_pk]
(
    pk1 integer,
    pk2 bigint,
    value nvarchar(16),
    geom geometry,
    PRIMARY KEY (pk1, pk2)
);

INSERT INTO [qgis_test].[tb_test_compound_pk] (pk1, pk2, value, geom) VALUES
(1, 1, 'test 1', geometry::STGeomFromText('POINT(-47.930 -15.818)', 4326)),
(1, 2, 'test 2', geometry::STGeomFromText('POINT(-47.887 -15.864)', 4326)),
(2, 1, 'test 3', geometry::STGeomFromText('POINT(-47.902 -15.763)', 4326)),
(2, 2, 'test 4', geometry::STGeomFromText('POINT(-47.952 -15.781)', 4326));
GO

CREATE TABLE [qgis_test].[tb_test_composite_float_pk]
(
    pk1 integer,
    pk2 bigint,
    pk3 real,
    value nvarchar(16),
    geom geometry,
    PRIMARY KEY (pk1, pk2, pk3)
);

INSERT INTO [qgis_test].[tb_test_composite_float_pk] (pk1, pk2, pk3, value, geom) VALUES
    (1, 1, 1.0,         'test 1', geometry::STGeomFromText('POINT(-47.930 -15.818)', 4326)),
    (1, 2, 3.141592741, 'test 2', geometry::STGeomFromText('POINT(-47.887 -15.864)', 4326)),
    (2, 2, 2.718281828, 'test 3', geometry::STGeomFromText('POINT(-47.902 -15.763)', 4326)),
    (2, 2, 1.0,         'test 4', geometry::STGeomFromText('POINT(-47.952 -15.781)', 4326));
GO

-- Table for constraint tests
CREATE TABLE [qgis_test].[constraints]
(
  gid integer PRIMARY KEY,
  val int,
  name text NOT NULL,
  description text,
  CONSTRAINT constraint_val UNIQUE (val)
);
GO

