Adb SQLite Provider 
You can use this database (among others) in your ETL:
- Databases: SQLite v3.x (external documentation)
- Features:
- Includes all optional provider services
- AdbSQLiteTableInformationService supports tables (including temporary) and views
- Includes all optional provider services
- Adb provider: AdbSQLiteProvider
- .NET provider: "System.Data.SQLite", a free System.Data.SQLite.Core NuGet package retrieved automatically (external documentation)
- Connection strings:
Note
Due to SQLite's typeless nature, certain SELECT
queries (e.g. scalar and aggregate
functions) must be preceded by a TYPES
clause to specify the return types of the columns.
To specify return types for all but the third column, and get date text strings converted to
DateTime
:
TYPES [long], [short],, [DateTime];
SELECT 1, 2, 3, '2020-12-31';
Data Type Mappings
This section lists which database types map to or from which .NET types when querying a SQLite server and working with its database parameters. The listed .NET types below are fully supported by the dataflow.
Note
Mappings tagged with AdbColumnSchema
below depend on whether or not an
AdbColumnSchema is supplied to
SetMappedType(AdbParameter, Type, AdbColumnSchema, ParameterDirection).
Of the out of box workers, this is only supplied by AdbInsertTarget<TInputError>.
Provider-Independent .NET Types
.NET Type Display Name | DbType enum |
SQL Type Example |
---|---|---|
System.Boolean, System.Boolean? |
Boolean | boolean |
System.Byte, System.Byte? |
Byte | tinyint |
System.Byte[] |
Binary | binary(7) , blob , varbinary(10) |
System.DateTime, System.DateTime? |
DateTime | datetime . Without AdbColumnSchema also: date . |
System.DateTime, System.DateTime? |
Date | With AdbColumnSchema: date |
System.Decimal, System.Decimal? |
Decimal | decimal(38,0) , numeric(10,2) |
System.Double, System.Double? |
Double | double , float , real |
System.Guid, System.Guid? |
Guid | guid , uniqueidentifier |
System.Int16, System.Int16? |
Int16 | smallint |
System.Int32, System.Int32? |
Int32 | int |
System.Int64, System.Int64? |
Int64 | bigint , integer |
System.Single, System.Single? |
Single | float |
System.String | String | char(32) , clob , nchar(32) , ntext , nvarchar(20) , text , varchar(30) |
Identifiers
- Quoting character: double quote
"
, e.g."My Table"
- SQLite external documentation: SQLite Keywords
Note that dataflow column name matching is ordinal case insensitive, but a case sensitive match takes precedence over a case insensitive match. See Dataflow Column Mapping for details.
Parameters
Use named parameters of the format @MyParameterName
. You can use
GetPlaceholder(String) to get the placeholder
in a portable manner.
Adb Information
The Adb Information example program dumps the below information with this provider querying a SQLite server. See that example for more information.
=== AdbConnectionBuilder ===
Provider=System.Data.SQLite:SQLite:
=== AdbConnection ===
DataSourceName=2fb995b3-4626-411e-8055-a1b8308bd318
DatabaseName=main
NumberOfColumnIdentifierParts=4
NumberOfColumnRestrictions=4
ServerVersion=3.30.1
=== AdbConnection.Information ===
CompositeIdentifierSeparator=.
CompositeIdentifierSeparatorPattern=\.
DataSourceProductName=SQLite
DataSourceProductVersion=3.30.1
GetPlaceholder("MyName")=@MyName
GroupByBehavior=MustContainAll
IdentifierCase=Insensitive
IdentifierPattern=(^\[\p{Lo}\p{Lu}\p{Ll}_@#][\p{Lo}\p{Lu}\p{Ll}\p{Nd}@$#_]*$)|(^\[[^\]\0]|\]\]+\]$)|(^\"[^\"\0]|\"\"+\"$)
IdentifierQuotePrefix="
IdentifierQuotePrefixes="
IdentifierQuoteSuffix="
IdentifierQuoteSuffixes="
OrderByColumnsInSelect=False
ParameterMarkerFormat={0}
ParameterMarkerPattern=@[\p{Lo}\p{Lu}\p{Ll}\p{Lm}_@#][\p{Lo}\p{Lu}\p{Ll}\p{Lm}\p{Nd}\uff3f_@#\$]*(?=\s+|$)
ParameterNameMaxLength=255
ParameterNamePattern=^[\p{Lo}\p{Lu}\p{Ll}\p{Lm}_@#][\p{Lo}\p{Lu}\p{Ll}\p{Lm}\p{Nd}\uff3f_@#\$]*(?=\s+|$)
QuotedIdentifierCase=Insensitive
QuotedIdentifierPattern=(([^\[]|\]\])*)
QuoteIdentifierPart("My Table")="My Table"
StatementSeparatorPattern=;
StringLiteralPattern='(([^']|'')*)'
SupportedJoinOperators=Inner, LeftOuter, RightOuter, FullOuter
UsesPositionalParameters=False
=== AdbConnection.TableInformation.CreateTableIdentifier("My Database"."My Schema".MyTable) ===
OriginalCompositeName="My Database"."My Schema".MyTable
OriginalTableName=MyTable
OriginalSchemaName="My Schema"
OriginalCatalogName="My Database"
QuotedCompositeName="My Database"."My Schema"."MyTable"
QuotedTableName="MyTable"
QuotedSchemaName="My Schema"
QuotedCatalogName="My Database"
UnquotedTableName=MyTable
UnquotedSchemaName=My Schema
UnquotedCatalogName=My Database
=== AdbConnection.SchemaInformationCollection ===
Note: The metadata schemas can be retrieved with AdbConnection.GetSchema().
The returned information is provided from the .NET provider as-is. Some collections
can be inaccurate, and different values can also be needed for different purposes,
which is why database specific actionETL providers can be created that replace
individual values with the desired ones. See the vendor provider documentation for
details on the returned information.
CollectionName=MetaDataCollections
IsCommon=True
NumberOfRestrictions=0
NumberOfIdentifierParts=0
CollectionName=DataSourceInformation
IsCommon=True
NumberOfRestrictions=0
NumberOfIdentifierParts=0
CollectionName=DataTypes
IsCommon=False
NumberOfRestrictions=0
NumberOfIdentifierParts=0
CollectionName=ReservedWords
IsCommon=False
NumberOfRestrictions=0
NumberOfIdentifierParts=0
CollectionName=Catalogs
IsCommon=False
NumberOfRestrictions=1
NumberOfIdentifierParts=1
CollectionName=Columns
IsCommon=False
NumberOfRestrictions=4
NumberOfIdentifierParts=4
CollectionName=Indexes
IsCommon=False
NumberOfRestrictions=4
NumberOfIdentifierParts=3
CollectionName=IndexColumns
IsCommon=False
NumberOfRestrictions=5
NumberOfIdentifierParts=4
CollectionName=Tables
IsCommon=False
NumberOfRestrictions=4
NumberOfIdentifierParts=3
CollectionName=Views
IsCommon=False
NumberOfRestrictions=3
NumberOfIdentifierParts=3
CollectionName=ViewColumns
IsCommon=False
NumberOfRestrictions=4
NumberOfIdentifierParts=4
CollectionName=ForeignKeys
IsCommon=False
NumberOfRestrictions=4
NumberOfIdentifierParts=3
CollectionName=Triggers
IsCommon=False
NumberOfRestrictions=4
NumberOfIdentifierParts=0