FWD » Core Development » Database

What I have so far.

• An improved dbo.words function:
  

  IF OBJECT_ID('dbo.words', 'IF') IS NOT NULL
      DROP FUNCTION dbo.words;
  GO
  
  CREATE FUNCTION dbo.words(@input NVARCHAR(MAX), @caseSensitive BIT = 0)
  RETURNS TABLE
  AS
  RETURN
  (
      SELECT DISTINCT
          CASE
              WHEN @caseSensitive = 0 THEN UPPER(s.value)
              ELSE s.value
          END AS word
      FROM
          -- Replace all delimiters with a space character.
          (VALUES (
              TRANSLATE(
                  @input,
                  N' !"&()*+,-./:;<=>?[\]^`{|}~'
                  + NCHAR(1) + NCHAR(2) + NCHAR(3) + NCHAR(4) + NCHAR(5) + NCHAR(6) + NCHAR(7) + NCHAR(8)
                  + NCHAR(9) + NCHAR(10) + NCHAR(11) + NCHAR(12) + NCHAR(13) + NCHAR(14) + NCHAR(15) + NCHAR(16)
                  + NCHAR(17) + NCHAR(18) + NCHAR(19) + NCHAR(20) + NCHAR(21) + NCHAR(22) + NCHAR(23) + NCHAR(24)
                  + NCHAR(25) + NCHAR(26) + NCHAR(27) + NCHAR(28) + NCHAR(29) + NCHAR(30) + NCHAR(31)
                  + NCHAR(127) + NCHAR(128) + NCHAR(129) + NCHAR(130) + NCHAR(131) + NCHAR(132) + NCHAR(133) + NCHAR(134) + NCHAR(135) + NCHAR(136) + NCHAR(137)
                  + NCHAR(159) + NCHAR(161) + NCHAR(162) + NCHAR(163) + NCHAR(165) + NCHAR(169) + NCHAR(171) + NCHAR(184) + NCHAR(187)
                  + NCHAR(188) + NCHAR(189) + NCHAR(191) + NCHAR(197) + NCHAR(198) + NCHAR(215) + NCHAR(216) + NCHAR(229) + NCHAR(230) + NCHAR(248),
                  REPLICATE(' ', 88)
              )
          )) AS T(TranslatedString)
      CROSS APPLY STRING_SPLIT(T.TranslatedString, ' ') AS s
      -- Filter out empty results
      WHERE
          s.value <> ''
  );
  GO
  

• DDLs for creating the primary key and other indexes: indexes DDLs ...indexes DDLs ...

  IF OBJECT_ID('dbo.pk__table__field', 'PK') IS NOT NULL
  BEGIN
      ALTER TABLE dbo.table__field DROP CONSTRAINT pk__table__field;
  END
  GO
  
  ALTER TABLE dbo.table__field
     ADD CONSTRAINT pk__table__field PRIMARY KEY CLUSTERED (parent__id, word);
  GO
  
  DROP INDEX IF EXISTS idx__table__field__word ON dbo.table__field;
  GO
  CREATE NONCLUSTERED INDEX idx__table__field__word ON dbo.table__field (word);
  GO
  
  IF OBJECT_ID('dbo.fk__table__field__parent_table', 'F') IS NOT NULL
  BEGIN
      ALTER TABLE dbo.table__field DROP CONSTRAINT fk__table__field__parent_table;
  END
  GO
  
  DROP INDEX IF EXISTS idx__table__field__parent_id ON dbo.table__field;
  GO
  CREATE NONCLUSTERED INDEX idx__table__field__parent_id ON dbo.table__field (parent__id);
  GO
  
  ALTER TABLE dbo.table__field
     ADD CONSTRAINT fk__table__field__parent_table
     FOREIGN KEY (parent__id)
     REFERENCES dbo.parent_table(recid)
     ON DELETE CASCADE;
  GO
  

• DDLs for creating the necessary triggers: Triggers DDLs ...Triggers DDLs ...
  • Insert trigger:
    

    IF OBJECT_ID('dbo.tt_word_ins_trg', 'TR') IS NOT NULL
        DROP TRIGGER dbo.tt_word_ins_trg;
    GO
    
    CREATE TRIGGER dbo.tt__sentence_ins_trg
       ON dbo.tt -- Trigger is on the parent table
       AFTER INSERT
    AS
    BEGIN
        IF NOT EXISTS (SELECT 1 FROM inserted) RETURN;
        DELETE T FROM dbo.tt__sentence AS T JOIN inserted AS i ON T.parent__id = i.recid;
    
        ;WITH NewWords (parent__id, word) AS (
            SELECT i.recid, w.word
            FROM inserted AS i
            CROSS APPLY dbo.words(i.sentence_1, 0) AS w
            WHERE i.sentence IS NOT NULL AND i.sentence <> ''
        )
        INSERT INTO dbo.tt__sentence (parent__id, word)
        SELECT parent__id, word FROM NewWords;
    END;
    GO
    

  • update triggers:
    

    IF OBJECT_ID('dbo.tt__sentence_upd_trg', 'TR') IS NOT NULL
        DROP TRIGGER dbo.tt__sentence_upd_trg;
    GO
    
    CREATE TRIGGER dbo.tt__sentence_upd_trg
       ON dbo.tt
       AFTER UPDATE
    AS
    BEGIN
        IF UPDATE(sentence)
        BEGIN
            DELETE T
            FROM dbo.tt__sentence AS T
            JOIN inserted AS i ON T.parent__id = i.recid;
    
            INSERT INTO dbo.tt__sentence (parent__id, word)
            SELECT i.recid, w.word
            FROM inserted AS i
            CROSS APPLY dbo.words(i.sentence_1, 0) AS w
            WHERE i.sentence_1 IS NOT NULL AND i.sentence_1 <> '';
        END;
    END;
    GO
    

  Can be adapted for word index tables for extent fields (+ list__index).

Next: Review, test and work on the java code to generate all of these. (P2JSQLServer2012Dialect ?)

I have an untested prototype for P2JSQLServer2012Dialect.generateWordTablesDDLImpl method. I guess I should work over 9456a changes.

I have the function to generate the necessary triggers for word index table updates. I attached a generated DDL to see how it looks at the moment.
So, I have dbo.words_sb UDF + the necessary triggers. The SQL generated at the moment by FqlToSqlConverter for CONTAINS statement is already compatible with SQL Server so we are good here.

There is still the part for creating/dropping indexes that needs to be fixed now.

Word tables should theoretically work now. All I need to do is to test them on a real testcase. Before that, I want a review on the current changes.

Ovidiu/Eduard, Do you have any pending changes to 9456a or can I commit my changes to 9456a?

I have committed my changes for word tables support in MS SQL Server in 9456a revision 16041.
At the moment only the generated DDLs and the SQL generated by FqlToSqlConverter for CONTAINS statement were manually tested in SQL Server Management Studio.
I also added basic initial versions of udf/sqlserver/error_handler.sql, udf/sqlserver/getFWDVersion.sql, udf/sqlserver/guarded.sql to make ./gradlew all work.

Ovidiu, please review!

I fixed the above issues in revision 9456a revision 16053.

Stefanel Pezamosca wrote:

Hmm, another case of "IntelliJ knows better".

It tries to help based on your configuration. Press Ctrl+Alt+Shift+S to go to Project structure. In Project tab you will find the current SDK. It's OK to use JDK17 here, but in next box (Language level) select 8 - Lambdas, type annotations, etc.. This will cause the IDE to use Java17 for compile/run, but will limit it only to features compatible with Java8.

• when generating [...], the type of word column cannot be nvarchar(max) because it is not indexable. Please change it to nvarchar(4000) which is the maximum size for indexable character data. It should be enough since it will store a single word;

The problem is from this line in DDLGeneratorWorker: boolean isIndexed = isIndexComponent(field, table); where it does not know that word will have an index. Then it sends indexed=false to getSqlMappedType

OK, I will look at this. It seems that the word indexes are not correctly processed... I'll be back with more info on this matter.

• P2JSQLServer2012Dialect.java: lines 116, 138, 158: These lists do not require wrapping in a unmodifiableList() since they are private and processed locally not returned by any method. TBH, I really do not like the extra processing at runtime and I would prefer to have them processed at compile time, using + concatenation. Unfortunately, the joining element (eoln, which depend on the target OS) is not available at that time, so we are stuck here. OTOH, I am not sure it matters that much, but this is a discussion for another time.
• DDLGeneratorWorker.java:
  • the addLastFieldImpl() method was added because the last column in a table (see the javadoc) and some things had to be processed differently. However, its semantic seems to have changed over time in that is is only called now for word tables. Therefore, a better name would be addWordIndexComponent()? I understand that this should also fix the above word column issue.

I am updating my test environment and continue to work/test with these changes. Meanwhile I am setting the status of this task to WIP for the remaining 10% of %Done.

Ovidiu,

There was another issue in the trigger function generation for word index tables over expanded extent fields, a dangling union all.

• I had to add missing overrides for isUdfContains and containsUdfName.
• In convertBooleans, the logic was generating contains_ss(field, s) = 1. The =1 part caused issues because contains gets translated into a subselect. Therefore, we should skip the convertBooleans logic when dialect.isUdfContains is true and dialect.useUdf4Contains is false.

I fixed these errors in revision 9456a (r16061). I also set up a working environment to connect to SQL Server, create the database, and run an import. I tested a small testcase with contains and it works as expected.

Please review.

I tested 9456a with a few standalone testcases I made with CONTAINS and they were ok. 9456a was already merged to trunk, so I will switch to 9456b and search for more tests to run.