Skip to main content

Rigi Documentation

SQL parser
  • Parser ID: sql.

  • Key features:

    This parser parses a specific SQL format:

    • It detects the structure by parsing the DECLARE TABLE sections.

      • It is possible to declare tables via the parser settings.

    • It parses INSERT INTO statements.

  • File format:

    Developers can export the content of a database to SQL files.

    • The parser skips lines with comments.

    • The structure is declared in DECLARE TABLE statements.

    • The translatable texts are defined in INSERT INTO or in MERGET INTO statements.

Comments

The parser skips all lines that start with --. Spaces are ignored.

Declare table

DECLARE @ResourceItem TABLE
(
[ResourceItemKey] [int] IDENTITY(1,1) NOT NULL,
[LocaleIdentifierKey] [int] NOT NULL,
[ResourceTypeKey] [smallint] NOT NULL,
[ResourceName] [nvarchar](128) NOT NULL,
[ResourceValue] [nvarchar](4000) NOT NULL,
[TranslateFlag] [bit] NOT NULL,
[ActiveFlag] [bit] NOT NULL,

PRIMARY KEY CLUSTERED ( [ResourceItemKey] ),
UNIQUE ( [LocaleIdentifierKey], [ResourceTypeKey],[ResourceName] ),
CHECK ( LocaleIdentifierKey = 1033 )
)

A row entry consists of:

  • Column name

    • Columns that start with LocaleIdentifier (e.g., LocaleIdentifier and LocaleIdentifierKey), contain the language ID (LCID).

    • Type

      • int.

      • smallint.

      • bit: 0 or 1.

      • nvarchar(maxlen): N'this is a text'.

    • Constraint.

The primary key refers to the column name that is the row identifier.

Unique indicates the columns that form the string identifier. Rigi uses the column names to form the string identifier. The locale identifier will be ignored.

Insert into

An insert into operation specifies the values that must be inserted in a table. A single operation contains one or more values elements.

Syntax:

INSERT INTO @<TABLE_NAME> (<COLUMN_NAME>*) VALUES (<COLUMN_VALUE>*) [,(<COLUMN_VALUE>*)]+

Example:

INSERT INTO @ResourceItem ([LocaleIdentifierKey],[ResourceTypeKey],[ResourceName],[ResourceValue],[TranslateFlag],[ActiveFlag])VALUES(1033,172,N'lblDeleteMessage',N'This is line one.
This is line two.',1,1),
                                   (1033,173,N'lblDeleteMessage',N'This is another text.',1,1)

A values-object contains one translatable text. This text

  1. has type NVARCHAR.

  2. is not a primary key.

  3. is not part of the string ID.

In this example, two texts are found:

172.lblDeleteMessage = This is line one.\nThis is line two.
173.lblDeleteMessage = This is another text.

Merge into

The merge into operation specifies the values that must be merged into a table. A single operation contains one or more value elements.

Syntax:

MERGE INTO <SKIP_TEXT> USING ( VALUES (<COLUMN_VALUE>*) [,(<COLUMN_VALUE>*)]+ )

Example:

MERGE INTO [x].[y] AS DestinationTable
USING ( 
VALUES ( N'multiLineSample', ',N'This is line one.
This is line two.',1,1),
                                    (N'restore', N'Restore', 1, 1)
...
)

The parser ignores the part between MERGE INTO and USING.

The table must be declared in the parser setting tableDeclarations.

In the example above, the definition could be like this (@ means that the table-name is not specified):

@
id FieldName NVARCHAR(50)
t  Text NVARCHAR(999)
- Flag1 bit
- Flag2 bit

A values-object contains one translatable text. This text

  1. has type NVARCHAR.

  2. is not a primary key.

  3. is not part of the string ID.

In this example, two texts are found:

multiLineSample'= This is line one.\nThis is line two.
restore = Restore

Parser settings

  • maxlenchars : bool

    The SQL parser checks if the maximum length is exceeded.

    The parser can check for the number of characters (default, true), or number of bytes (false). 

  • pseudo-ignore-maxlen : bool

    When the parser generates the files with Rigi tokens, strings can get too long and will then be truncated.

    This can be problematic when strings only have a few characters, whereas Rigi tokens would need more space. In such cases, Rigi would not export Rigi tokens for those strings.

    With this setting (enabled by default), the parser will ignore the maximum length restrictions.

    Developers might need to take precautions to enable them to import the generated file correctly.

  • skipemptysource: bool

    If set (default), then empty source strings are ignored.

  • tableDeclarations

    This variable contains blocks containing table definitions.

    • Blocks can be separated by a row starting with ===.

    • All texts are case-insensitive.

    • Block definition

      A block starts with the table name, followed by field definitions.

      <table-name>
      <code> <field
      ...
      field-definition
    • <table-name>

      This is the name of the table.

      The name must not contain a space.

      The parser assumes that the default type of <table-name> is NVARCHAR.

      @

      In the case of MERGE INTO commands, the parser should not check the table name. If this is the case, set the value to @@.

    • <code>

      -

      Ignore field. Field-name and type are required.

      id

      The field is part of the string identifier.

      lcid

      This field contains the language code (e.g. 1033).

      t

      This is the field that must be translated. Type must be NVARCHAR.

      len

      This field contains the length for the translatable field. Type must be INT.

    • <field-name>

      This is the name of the field (case-insensitive).

    • <type>

      Possible types are (case-insensitive):

      INT

      Integer.

      NVARCHAR

      String.

      NVARCHAR(#)

      String with maximum length.

Example 12. INSERT INTO

An insert command such as the following:

INSERT INTO DBA.TheTableName (TableName, SourceReferenceKey, FieldName, FieldMaximumLength, TranslatedText)
VALUES
 (N'TheName',N'C',N'DescriptionText',50,N'Complete')
,(N'TheName',N'E',N'DescriptionText',50,N'Error')
, ...

Can be parsed using the following setting:

DBA.TheTableName 
id    TableName NVARCHAR(50)
id    SourceReferenceKey NVARCHAR(50)
id    FieldName NVARCHAR(50)
-     FieldMaximumLength INT
t     TranslatedText NVARCHAR(50)

It results in the following keys:

TheTableName .C.DescriptionText
TheTableName .E.DescriptionText

Example 13. MERGE INTO

A merge command such as the following:

MERGE INTO
    [Locale].[ResourceItem] AS DestinationTable
USING (
    VALUES
        -- Some comment
		  (N'id1', N'Text1', 1, 1)
        -- Another comment
		, (N'id2', N'Text2', 1, 1)
		, (N'id3, N'Text3', 1, 1)
                , ...

Can be parsed using the following setting:

@
id FieldName NVARCHAR(50)
t  Text NVARCHAR(999)
- Flag1 bit
- Flag2 bit

It results in the following keys:

fileid.id1
fileid.id2
fileid.id3