SQL parser
Parser ID: sql.
Key features:
This parser parses a specific SQL format:
It detects the structure by parsing the
DECLARE TABLEsections.It is possible to declare tables via the parser settings.
It parses
INSERT INTOstatements.
File format:
Developers can export the content of a database to SQL files.
The parser skips lines containing comments.
The structure is declared in
DECLARE TABLEstatements.The translatable texts are defined in
INSERT INTOor inMERGET INTOstatements.
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.,LocaleIdentifierandLocaleIdentifierKey), 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 ID.
Unique indicates the columns that form the string ID. Rigi uses the column names to form the string ID. The locale ID 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>*)]+
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 has the following characteristics:
Has type NVARCHAR.
Is not a primary key.
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>*)]+ )
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 tableDeclarations parser setting .
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 has the following characteristics:
Has type NVARCHAR.
Is not a primary key.
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 : boolThe 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 : boolWhen the parser generates the files containing Rigi tokens, strings can get too long and will then be truncated.
This can be problematic when strings are restricted to just 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: boolIf set (default), empty source strings are ignored.
tableDeclarationsThis 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 for
<table-name>is NVARCHAR.@
In the case of
MERGE INTOcommands, the parser should not check the table name. If this is the case, set the value to@.<code>Table 11.-
Ignore field. Field name and type are required.
id
The field is part of the string ID.
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 sets the length of the translatable field. Type must be INT.
<field-name>This is the name of the field (case-insensitive).
<type>Possible types are (case-insensitive):
Table 12.INT
Integer.
NVARCHAR
String.
NVARCHAR(#)
String with maximum length.
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
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