Voeg aangepaste query herschrijvingsregels toe aan Invantive UniversalSQL Server

Wiki (en)
,
1

Invantive UniversalSQL Server biedt realtime connectiviteit via het TDS-protocol van Microsoft SQL Server naar meer dan 80 (cloud)platforms zoals Freshdesk, Freshservice, ActiveCampaign, Salesforce, Twinfield en andere.

Alle SQL-statements worden uitgevoerd als Invantive UniversalSQL. Veel SQL Server-clientprogramma’s draaien zonder enige wijziging. SQL Server-programma’s kunnen echter ook SQL-statements gebruiken die niet compatibel zijn met Invantive UniversalSQL. Een ingebouwde preprocessor dekt veel toepassingen, maar niet allemaal.

Dit onderwerp legt uit hoe je de preprocessor van Invantive UniversalSQL Server kunt uitbreiden met je eigen logica. Het onderwerp is gebaseerd op de eerste stappen om Sumatra 2022 werkend te krijgen op een Invantive-database.

Database toevoegen

Na installatie van Sumatra 2022 wordt de webmanager gestart en een database toegevoegd:

image
image479×302 5.33 KB

Het opgeven van nieuwe inloggegevens mislukt met itgensql005: Unsupported function 'HAS_PERMS_BY_NAME' found... zoals hieronder getoond:

image
image759×378 16.7 KB

Het betreffende SQL-statement kan worden opgehaald uit Elastic, Invantive Trace of de monitor. Met behulp van Monitor werd het SQL-statement snel gevonden:

image
image1058×126 5.44 KB

namelijk:

select name from sys.databases where HAS_PERMS_BY_NAME('[' + replace(name,']',']]') + ']', 'DATABASE', 'SELECT') = 1

In de appsettings.json van Invantive UniversalSQL Server wordt een sectie toegevoegd om de preprocessor als volgt uit te breiden:

  , "QueryRewriteRules":
    [ { "Code": "rle001"
      , "SortingOrder": "A001"
      , "RegexPattern": "select name from sys\\.databases where HAS_PERMS_BY_NAME\\('\\[' \\+ replace\\(name,'\\]','\\]\\]'\\) \\+ '\\]', 'DATABASE', 'SELECT'\\) = 1"
      , "Replacement": "select 'master'"
      , "NextCodeOnMatch": "EXIT"
      , "NextCodeOnNoMatch": null
      }
    , { "Code": "EXIT"
      , "SortingOrder": "EXIT"
      , "RegexPattern": "xyzxyzxyz"
      , "Replacement": "xyzxyzxyz"
      , "NextCodeOnMatch": null
      , "NextCodeOnNoMatch": null
      , "ExitSuccess": true
      }
    ]

Als het SQL-statement overeenkomt met regel rle001, wordt de query herschreven om alleen master terug te geven met behulp van een Invantive UniversalSQL-statement. Daarna gaat de uitvoering verder met de EXIT-herschrijfregel (die in deze specifieke situatie ook weggelaten had kunnen worden).

Na het herstarten slaagt de verbinding nu, waarbij Sumatra ervan uitgaat dat er alleen een master-database is:

image
image524×347 12.2 KB

Volgende uitdaging

Bij het verder gaan met het aanmaken van de database treedt vervolgens de volgende fout op:

image
image915×423 10.6 KB

die wordt veroorzaakt door een query met een afsluitende carriage return:

select count(*) from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'ExactLicense' and TABLE_SCHEMA = 'dbo' union all select case when count(*) = 3 then 1 else 0 end from INFORMATION_SCHEMA.TABLES where TABLE_NAME in ('Uren', 'WeekStaat', 'weburen_config') and TABLE_SCHEMA = 'sumatra' union all select case when count(*) = 4 then 1 else 0 end from INFORMATION_SCHEMA.TABLES where TABLE_NAME in ('bedryf', 'gbkmut', 'requesttasks', 'GLMaps') and TABLE_SCHEMA = 'dbo'\n

en een verwachte uitkomst op een niet-Exact Globe-omgeving van drie rijen, elk met 0.

Eindresultaat

Na verschillende iteraties is het resultaat:

  , "QueryRewriteRules":
    [ { "Code": "rle001"
      , "SortingOrder": "A001"
      , "RegexPattern": "select name from sys\\.databases where HAS_PERMS_BY_NAME\\('\\[' \\+ replace\\(name,'\\]','\\]\\]'\\) \\+ '\\]', 'DATABASE', 'SELECT'\\) = 1"
      , "Replacement": "select 'master'"
      , "NextCodeOnMatch": "EXIT"
      , "NextCodeOnNoMatch": null
      }
    , { "Code": "rle002"
      , "SortingOrder": "A002"
      , "RegexPattern": "select count\\(\\*\\) from INFORMATION_SCHEMA\\.TABLES where TABLE_NAME = 'ExactLicense' and TABLE_SCHEMA = 'dbo' union all select case when count\\(\\*\\) = 3 then 1 else 0 end from INFORMATION_SCHEMA\\.TABLES where TABLE_NAME in \\('Uren', 'WeekStaat', 'weburen_config'\\) and TABLE_SCHEMA = 'sumatra' union all select case when count\\(\\*\\) = 4 then 1 else 0 end from INFORMATION_SCHEMA\\.TABLES where TABLE_NAME in \\('bedryf', 'gbkmut', 'requesttasks', 'GLMaps'\\) and TABLE_SCHEMA = 'dbo'\n"
      , "Replacement": "select 0 union all select 0 union all select 0"
      , "NextCodeOnMatch": "EXIT"
      , "NextCodeOnNoMatch": null
      }
    , { "Code": "rle003"
      , "SortingOrder": "A003"
      , "RegexPattern": "select count\\(\\*\\) from fn_my_permissions\\(null,'DATABASE'\\) where permission_name = 'SELECT'"
      , "Replacement": "select 1"
      , "NextCodeOnMatch": "EXIT"
      , "NextCodeOnNoMatch": null
      , "ExitError": false
      , "ExitErrorMessage": null
      }
    , { "Code": "EXIT"
      , "SortingOrder": "EXIT"
      , "RegexPattern": "xyzxyzxyz"
      , "Replacement": "xyzxyzxyz"
      , "NextCodeOnMatch": null
      , "NextCodeOnNoMatch": null
      , "ExitSuccess": true
      }
    ]

De volledige documentatie van Invantive UniversalSQL Server is beschikbaar op:

Documentation start page