SET NOCOUNT, XACT_ABORT, QUOTED_IDENTIFIER ON
go
USE collationbase
go
DROP DATABASE IF EXISTS collationtest    
go
CREATE DATABASE collationtest
go
USE collationtest
go
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 1
go
USE collationbase
go
DECLARE @collation sysname, 
        @testno    tinyint,
        @runorder  int,
        @label     varchar(20),
        @isunicode bit,
        @stmt      nvarchar(MAX),
        @d         datetime2(3),
        @i         int,
        @tookms    smallint,
        @collcur   CURSOR,
        @testcur   CURSOR,
        @codepage  int,
        @dummy     int

        -- Test words for the point lookups.
DECLARE @unicode1  nvarchar(50) = N'vskočijo',
        @unicode2  nvarchar(50) = N'solmizacijskima',
        @unicode3  nvarchar(50) = N'Dobrljanom',
        @unicode4  nvarchar(50) = N'finih',
        @unicode5  nvarchar(50) = N'molznikovi',
        @unicode6  nvarchar(50) = N'Aleutčankini',
        @unicode7  nvarchar(50) = N'Dopplerjevimi',
        @unicode8  nvarchar(50) = N'nadledvična',
        @unicode9  nvarchar(50) = N'Wiesbadenčankinimi',
        @unicode10 nvarchar(50) = N'svetovali'

DECLARE @mbcs1  varchar(50) = 'Berlin',
        @mbcs2  varchar(50) = 'Sexdrega',
        @mbcs3  varchar(50) = 'Orange',
        @mbcs4  varchar(50) = 'Sundblad',
        @mbcs5  varchar(50) = 'Tandil',
        @mbcs6  varchar(50) = 'King',
        @mbcs7  varchar(50) = 'Dopplerjevimi',
        @mbcs8  varchar(50) = 'Tornquist',
        @mbcs9  varchar(50) = 'Paris',
        @mbcs10 varchar(50) = 'London'


SET @collcur = CURSOR STATIC FOR
   SELECT collation, testno, runorder, cast(collationproperty(collation, 'CodePage') AS int) 
   FROM   collations
   WHERE  done = 0
     AND  cast(collationproperty(collation, 'CodePage') AS int)  > 0
   ORDER  BY testno, runorder

OPEN @collcur

WHILE 1 = 1
BEGIN 
   FETCH @collcur INTO @collation, @testno, @runorder, @codepage
   IF @@fetch_status <> 0
      BREAK

   RAISERROR ('Collation %s, testno %d, runorder %d.', 0, 1, @collation, @testno, @runorder) WITH NOWAIT

   -- Clear anything from any interupted test.
   DELETE results 
   WHERE  collation = @collation 
     AND  testno = @testno

   -- Alter collation of test database.
   SELECT @stmt = 'ALTER DATABASE collationtest COLLATE ' + @collation
   EXEC (@stmt)

   USE collationtest

   -- Create the test tabels and load them.
   CREATE TABLE unicodewords(id     int          NOT NULL,
                             word   nvarchar(50) NOT NULL,
                             data   int          NOT NULL,
                             CONSTRAINT pk_unicodewords PRIMARY KEY(id)
   )
   INSERT unicodewords (id, word, data)
      SELECT id, word, data
      FROM   collationbase.dbo.worddata

   CREATE TABLE randomunicode(id         int            NOT NULL,
                              randomtext nvarchar(2000) NOT NULL,
                              CONSTRAINT pk_randomunicode PRIMARY KEY (id)
   )
   INSERT randomunicode (id, randomtext)
      SELECT id, randomtext
      FROM   collationbase..randomtext

   CREATE TABLE unicodekeys (word nvarchar(50) NOT NULL,
                             data int          NOT NULL,
                             CONSTRAINT pk_unicodekeys PRIMARY KEY (word) 
   )
   INSERT unicodekeys (word, data)
      SELECT word, SUM(data)
      FROM   unicodewords
      GROUP  BY word

   -- Cache warmup.
   SELECT @dummy = COUNT(DISTINCT data) FROM unicodekeys
   SELECT @dummy = COUNT(*) FROM randomunicode WHERE randomtext LIKE N'abc%'

   IF @codepage > 0
   BEGIN
CREATE TABLE mbcswords(id     int          NOT NULL,
                       word   varchar(50)  NOT NULL,
                       data   int          NOT NULL,
                       CONSTRAINT pk_mbcswords PRIMARY KEY(id)
)
INSERT mbcswords (id, word, data)
   SELECT id, word, data
   FROM   collationbase.dbo.worddata

      CREATE TABLE mbcskeys (word varchar(50) NOT NULL,
                             data int         NOT NULL,
                             CONSTRAINT pk_mbcskeys PRIMARY KEY (word) 
      )
      INSERT mbcskeys (word, data)
         SELECT word, SUM(data)
         FROM   mbcswords
         GROUP  BY word
         ORDER BY word

      CREATE TABLE randommbcs(id         int           NOT NULL,
                              randomtext varchar(2000) NOT NULL,
                              CONSTRAINT pk_randommbcs PRIMARY KEY (id)
      )
      INSERT randommbcs (id, randomtext)
         SELECT id, randomtext
         FROM   collationbase..randomtext

       SELECT @dummy = COUNT(DISTINCT data) FROM mbcskeys
       SELECT @dummy = COUNT(*) FROM randommbcs WHERE randomtext LIKE 'abc%'
    END


    -- Start cursor for tests.
    SET @testcur = CURSOR FOR
       SELECT label, isunicode, stmt
       FROM   collationbase..teststmts
       WHERE  isunicode = 1 OR @codepage > 0
       ORDER  BY id
       
    OPEN @testcur
    
    WHILE 1 = 1
    BEGIN
       FETCH @testcur INTO @label, @isunicode, @stmt
       IF @@fetch_status <> 0
          BREAK

       BEGIN TRY
          -- PRINT @stmt
          SELECT @d = sysdatetime()
          EXEC (@stmt)
          SELECT @tookms = datediff(ms, @d, sysdatetime())
       END TRY
       BEGIN CATCH
          ; THROW
       END CATCH

       INSERT collationbase..results(collation, testno, label, isunicode, tookms)
          VALUES (@collation, @testno, @label, @isunicode, @tookms)
    END

    -- The lookup tests
    SET @i = 0
    SELECT @d = sysdatetime()
    WHILE datediff(ms, @d, sysdatetime()) < 400
    BEGIN
       SELECT @dummy = data FROM unicodekeys WHERE word = @unicode1
       SELECT @dummy = data FROM unicodekeys WHERE word = @unicode2
       SELECT @dummy = data FROM unicodekeys WHERE word = @unicode3
       SELECT @dummy = data FROM unicodekeys WHERE word = @unicode4
       SELECT @dummy = data FROM unicodekeys WHERE word = @unicode5
       SELECT @dummy = data FROM unicodekeys WHERE word = @unicode6
       SELECT @dummy = data FROM unicodekeys WHERE word = @unicode7
       SELECT @dummy = data FROM unicodekeys WHERE word = @unicode8
       SELECT @dummy = data FROM unicodekeys WHERE word = @unicode9
       SELECT @dummy = data FROM unicodekeys WHERE word = @unicode10
       SELECT @i += 10
    END
    INSERT collationbase..results(collation, testno, label, isunicode, tookms)
       VALUES(@collation, @testno, 'LOOKUP', 1, @i)

    IF @codepage > 0
    BEGIN
       SET @i = 0
       SELECT @d = sysdatetime()
       WHILE datediff(ms, @d, sysdatetime()) < 400
       BEGIN
          SELECT @dummy = data FROM mbcskeys WHERE word = @mbcs1
          SELECT @dummy = data FROM mbcskeys WHERE word = @mbcs2
          SELECT @dummy = data FROM mbcskeys WHERE word = @mbcs3
          SELECT @dummy = data FROM mbcskeys WHERE word = @mbcs4
          SELECT @dummy = data FROM mbcskeys WHERE word = @mbcs5
          SELECT @dummy = data FROM mbcskeys WHERE word = @mbcs6
          SELECT @dummy = data FROM mbcskeys WHERE word = @mbcs7
          SELECT @dummy = data FROM mbcskeys WHERE word = @mbcs8
          SELECT @dummy = data FROM mbcskeys WHERE word = @mbcs9
          SELECT @dummy = data FROM mbcskeys WHERE word = @mbcs10
          SELECT @i += 10
       END
       INSERT collationbase..results(collation, testno, label, isunicode, tookms)
          VALUES(@collation, @testno, 'LOOKUP', 0, @i)


       SET @i = 0
       SELECT @d  = sysdatetime()
       WHILE datediff(ms, @d, sysdatetime()) < 400
       BEGIN
          SELECT @dummy = data FROM mbcskeys WHERE word = @unicode1
          SELECT @dummy = data FROM mbcskeys WHERE word = @unicode2
          SELECT @dummy = data FROM mbcskeys WHERE word = @unicode3
          SELECT @dummy = data FROM mbcskeys WHERE word = @unicode4
          SELECT @dummy = data FROM mbcskeys WHERE word = @unicode5
          SELECT @dummy = data FROM mbcskeys WHERE word = @unicode6
          SELECT @dummy = data FROM mbcskeys WHERE word = @unicode7
          SELECT @dummy = data FROM mbcskeys WHERE word = @unicode8
          SELECT @dummy = data FROM mbcskeys WHERE word = @unicode9
          SELECT @dummy = data FROM mbcskeys WHERE word = @unicode10
          SELECT @i += 10
       END
       INSERT collationbase..results(collation, testno, label, isunicode, tookms)
          VALUES(@collation, @testno, 'MISFIT', 0, @i)
    END

    -- Drop the test tables.
    DROP TABLE unicodewords, unicodekeys, randomunicode
    IF @codepage > 0
       DROP TABLE mbcswords, mbcskeys, randommbcs

    -- Move out of the database-
    USE collationbase

    UPDATE collations
    SET    done = 1
    WHERE  collation = @collation
      AND  testno    = @testno
END