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 inMERGET 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
andLocaleIdentifierKey
), 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
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>*)]+ )
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
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 : 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.
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