Connection String OptionsBack To Top

AuthScheme
URL
User
Password
AccessToken
StoreCode
SSLServerCert
FirewallType
FirewallServer
FirewallPort
FirewallUser
FirewallPassword
ProxyAutoDetect
ProxyServer
ProxyPort
ProxyAuthScheme
ProxyUser
ProxyPassword
ProxySSLType
ProxyExceptions
Logfile
Verbosity
LogModules
MaxLogFileSize
MaxLogFileCount
Location
BrowsableSchemas
Tables
Views
AutoCache
CacheDriver
CacheConnection
CacheLocation
CacheTolerance
Offline
CacheMetadata
BatchSize
ConnectionLifeTime
ConnectOnOpen
GenerateSchemaFiles
IncludeCustomAttributes
MaxRows
Other
Pagesize
PoolIdleTimeout
PoolMaxSize
PoolMinSize
PoolWaitTime
PseudoColumns
Readonly
RowScanDepth
RTK
SupportEnhancedSQL
Timeout
TypeDetectionScheme
UseConnectionPooling
UseSimpleNames

AuthScheme

Data Type

string

Default Value

"Auto"

Remarks

Auto: Lets the driver decide automatically based on the other connection properties you have set.
Basic: Set this to use BASIC user / password authentication.
Token: Set this to perform Authentication using a Magento Access Token.

URL

Data Type

string

Default Value

Remarks

Set the URL to the HTTP or HTTPS endpoint of your Magento system. For example, https://magentohost/. See the Getting Started section of the help documentation for connection guides to Magento 2.

Username of the currently authenticated user. Used to connect to Magento 2.x servers only. See the Establishing a Connection section under Getting Started for more information on authenticating to different versions of Magento.

If you are accessing Magento as a customer, these credentials correspond to your customer account. To create an account, select Account > Register from the Magento home page.

To allow administrator users to connect:

Assign an admin role to a user by selecting System > Web Services > REST-Roles and click Admin Role.
Select Permissions > Users and select the user.

Password

Data Type

string

Default Value

Remarks

Password of the currently authenticated User. Used only to connect to Magento 2.x servers.

AccessToken

Data Type

string

Default Value

Remarks

The Magento Access Token used to authenticate the requests. The Access Token can be either Integration, Admin or Customer type.

StoreCode

Data Type

string

Default Value

"default"

Remarks

The store code identifies a store in the Magento system. Set the value "all" to execute the API requests on every store in the system. This value only applies to the CMS and Product related tables.

SSLServerCert

Data Type

string

Default Value

Remarks

If using a TLS/SSL connection, this property can be used to specify the TLS/SSL certificate to be accepted from the server. Any other certificate that is not trusted by the machine is rejected.

This property can take the following forms:


Description	Example
A full PEM Certificate (example shortened for brevity)	-----BEGIN CERTIFICATE----- MIIChTCCAe4CAQAwDQYJKoZIhv......Qw== -----END CERTIFICATE-----
A path to a local file containing the certificate	C:\cert.cer
The public key (example shortened for brevity)	-----BEGIN RSA PUBLIC KEY----- MIGfMA0GCSq......AQAB -----END RSA PUBLIC KEY-----
The MD5 Thumbprint (hex values can also be either space or colon separated)	ecadbdda5a1529c58a1e9e09828d70e4
The SHA1 Thumbprint (hex values can also be either space or colon separated)	34a929226ae0819f2ec14b4a3d904f801cbb150d

If not specified, any certificate trusted by the machine is accepted.

Certificates are validated as trusted by the machine based on the System's trust store. The trust store used is the 'javax.net.ssl.trustStore' value specified for the system. If no value is specified for this property, Java's default trust store is used (for example, JAVA_HOME\lib\security\cacerts).

Use '*' to signify to accept all certificates. Note that this is not recommended due to security concerns.

FirewallType

Data Type

string

Default Value

"NONE"

Remarks

This property specifies the protocol that the driver will use to tunnel traffic through the FirewallServer proxy. Note that by default, the driver connects to the system proxy; to disable this behavior and connect to one of the following proxy types, set ProxyAutoDetect to false.


Type	Default Port	Description
TUNNEL	80	When this is set, the driver opens a connection to Magento and traffic flows back and forth through the proxy.
SOCKS4	1080	When this is set, the driver sends data through the SOCKS 4 proxy specified by FirewallServer and FirewallPort and passes the FirewallUser value to the proxy, which determines if the connection request should be granted.
SOCKS5	1080	When this is set, the driver sends data through the SOCKS 5 proxy specified by FirewallServer and FirewallPort. If your proxy requires authentication, set FirewallUser and FirewallPassword to credentials the proxy recognizes.

To connect to HTTP proxies, use ProxyServer and ProxyPort. To authenticate to HTTP proxies, use ProxyAuthScheme, ProxyUser, and ProxyPassword.

FirewallServer

Data Type

string

Default Value

Remarks

This property specifies the IP address, DNS name, or host name of a proxy allowing traversal of a firewall. The protocol is specified by FirewallType: Use FirewallServer with this property to connect through SOCKS or do tunneling. Use ProxyServer to connect to an HTTP proxy.

Note that the driver uses the system proxy by default. To use a different proxy, set ProxyAutoDetect to false.

FirewallPort

Data Type

int

Default Value

Remarks

This specifies the TCP port for a proxy allowing traversal of a firewall. Use FirewallServer to specify the name or IP address. Specify the protocol with FirewallType.

FirewallUser

Data Type

string

Default Value

Remarks

The FirewallUser and FirewallPassword properties are used to authenticate against the proxy specified in FirewallServer and FirewallPort, following the authentication method specified in FirewallType.

FirewallPassword

Data Type

string

Default Value

Remarks

This property is passed to the proxy specified by FirewallServer and FirewallPort, following the authentication method specified by FirewallType.

ProxyAutoDetect

Data Type

bool

Default Value

false

Remarks

This takes precedence over other proxy settings, so you'll need to set ProxyAutoDetect to FALSE in order use custom proxy settings.

NOTE: When this property is set to True, the proxy used is determined as follows:

A search from the JVM properties (http.proxy, https.proxy, socksProxy, etc.) is performed.
In the case that the JVM properties don't exist, a search from java.home/lib/net.properties is performed.
In the case that java.net.useSystemProxies is set to True, a search from the SystemProxy is performed.
In Windows only, an attempt is made to retrieve these properties from the Internet Options in the registry.

To connect to an HTTP proxy, see ProxyServer. For other proxies, such as SOCKS or tunneling, see FirewallType.

ProxyServer

Data Type

string

Default Value

Remarks

The hostname or IP address of a proxy to route HTTP traffic through. The driver can use the HTTP, Windows (NTLM), or Kerberos authentication types to authenticate to an HTTP proxy.

If you need to connect through a SOCKS proxy or tunnel the connection, see FirewallType.

By default, the driver uses the system proxy. If you need to use another proxy, set ProxyAutoDetect to false.

ProxyPort

Data Type

int

Default Value

Remarks

The port the HTTP proxy is running on that you want to redirect HTTP traffic through. Specify the HTTP proxy in ProxyServer. For other proxy types, see FirewallType.

ProxyAuthScheme

Data Type

string

Default Value

"BASIC"

Remarks

This value specifies the authentication type to use to authenticate to the HTTP proxy specified by ProxyServer and ProxyPort.

Note that the driver will use the system proxy settings by default, without further configuration needed; if you want to connect to another proxy, you will need to set ProxyAutoDetect to false, in addition to ProxyServer and ProxyPort. To authenticate, set ProxyAuthScheme and set ProxyUser and ProxyPassword, if needed.

The authentication type can be one of the following:

BASIC: The driver performs HTTP BASIC authentication.
DIGEST: The driver performs HTTP DIGEST authentication.
NEGOTIATE: The driver retrieves an NTLM or Kerberos token based on the applicable protocol for authentication.
PROPRIETARY: The driver does not generate an NTLM or Kerberos token. You must supply this token in the Authorization header of the HTTP request.

If you need to use another authentication type, such as SOCKS 5 authentication, see FirewallType.

ProxyUser

Data Type

string

Default Value

Remarks

The ProxyUser and ProxyPassword options are used to connect and authenticate against the HTTP proxy specified in ProxyServer.

You can select one of the available authentication types in ProxyAuthScheme. If you are using HTTP authentication, set this to the user name of a user recognized by the HTTP proxy. If you are using Windows or Kerberos authentication, set this property to a user name in one of the following formats:

user@domain

domain\user

ProxyPassword

Data Type

string

Default Value

Remarks

This property is used to authenticate to an HTTP proxy server that supports NTLM (Windows), Kerberos, or HTTP authentication. To specify the HTTP proxy, you can set ProxyServer and ProxyPort. To specify the authentication type, set ProxyAuthScheme.

If you are using HTTP authentication, additionally set ProxyUser and ProxyPassword to HTTP proxy.

If you are using NTLM authentication, set ProxyUser and ProxyPassword to your Windows password. You may also need these to complete Kerberos authentication.

For SOCKS 5 authentication or tunneling, see FirewallType.

By default, the driver uses the system proxy. If you want to connect to another proxy, set ProxyAutoDetect to false.

ProxySSLType

Data Type

string

Default Value

"AUTO"

Remarks

This property determines when to use SSL for the connection to an HTTP proxy specified by ProxyServer. This value can be AUTO, ALWAYS, NEVER, or TUNNEL. The applicable values are the following:


AUTO	Default setting. If the URL is an HTTPS URL, the driver will use the TUNNEL option. If the URL is an HTTP URL, the component will use the NEVER option.
ALWAYS	The connection is always SSL enabled.
NEVER	The connection is not SSL enabled.
TUNNEL	The connection is through a tunneling proxy. The proxy server opens a connection to the remote host and traffic flows back and forth through the proxy.

ProxyExceptions

Data Type

string

Default Value

Remarks

The ProxyServer is used for all addresses, except for addresses defined in this property. Use semicolons to separate entries.

Note that the driver uses the system proxy settings by default, without further configuration needed; if you want to explicitly configure proxy exceptions for this connection, you need to set ProxyAutoDetect = false, and configure ProxyServer and ProxyPort. To authenticate, set ProxyAuthScheme and set ProxyUser and ProxyPassword, if needed.

Logfile

Data Type

string

Default Value

Remarks

Once this property is set, the driver will populate the log file as it carries out various tasks, such as when authentication is performed or queries are executed. If the specified file doesn't already exist, it will be created.

Connection strings and version information are also logged, though connection properties containing sensitive information are masked automatically.

If a relative filepath is supplied, the location of the log file will be resolved based on the path found in the Location connection property.

For more control over what is written to the log file, you can adjust the Verbosity property.

Log contents are categorized into several modules. You can show/hide individual modules using the LogModules property.

To edit the maximum size of a single logfile before a new one is created, see MaxLogFileSize.

If you would like to place a cap on the number of logfiles generated, use MaxLogFileCount.

Java Logging

Java logging is also supported. To enable Java logging, set Logfile to:

Logfile=JAVALOG://myloggername

As in the above sample, JAVALOG:// is a required prefix to use Java logging, and you will substitute your own Logger.

The supplied Logger's getLogger method is then called, using the supplied value to create the Logger instance. If a logging instance already exists, it will reference the existing instance.

When Java logging is enabled, the Verbosity will now correspond to specific logging levels.

Verbosity

Data Type

string

Default Value

"1"

Remarks

The verbosity level determines the amount of detail that the driver reports to the Logfile. Verbosity levels from 1 to 5 are supported. These are detailed in the Logging page.

LogModules

Data Type

string

Default Value

Remarks

Only the modules specified (separated by ';') will be included in the log file. By default all modules are included.

See the Logging page for an overview.

MaxLogFileSize

Data Type

string

Default Value

"100MB"

Remarks

When the limit is hit, a new log is created in the same folder with the date and time appended to the end. The default limit is 100 MB. Values lower than 100 kB will use 100 kB as the value instead.

Adjust the maximum number of logfiles generated with MaxLogFileCount.

MaxLogFileCount

Data Type

int

Default Value

-1

Remarks

When the limit is hit, a new log is created in the same folder with the date and time appended to the end and the oldest log file will be deleted.

The minimum supported value is 2. A value of 0 or a negative value indicates no limit on the count.

Adjust the maximum size of the logfiles generated with MaxLogFileSize.

Location

Data Type

string

Default Value

"%APPDATA%\\CData\\Magento Data Provider\\Schema"

Remarks

The path to a directory which contains the schema files for the driver (.rsd files for tables and views, .rsb files for stored procedures). The folder location can be a relative path from the location of the executable. The Location property is only needed if you want to customize definitions (for example, change a column name, ignore a column, and so on) or extend the data model with new tables, views, or stored procedures.

If left unspecified, the default location is "%APPDATA%\\CData\\Magento Data Provider\\Schema" with %APPDATA% being set to the user's configuration directory:


Platform	%APPDATA%
Windows	The value of the APPDATA environment variable
Mac	~/Library/Application Support
Linux	~/.config

BrowsableSchemas

Data Type

string

Default Value

Remarks

Listing the schemas from databases can be expensive. Providing a list of schemas in the connection string improves the performance.

Tables

Data Type

string

Default Value

Remarks

Listing the tables from some databases can be expensive. Providing a list of tables in the connection string improves the performance of the driver.

This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.

Specify the tables you want in a comma-separated list. Each table should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Tables=TableA,[TableB/WithSlash],WithCatalog.WithSchema.`TableC With Space`.

Note that when connecting to a data source with multiple schemas or catalogs, you will need to provide the fully qualified name of the table in this property, as in the last example here, to avoid ambiguity between tables that exist in multiple catalogs or schemas.

Views

Data Type

string

Default Value

Remarks

Listing the views from some databases can be expensive. Providing a list of views in the connection string improves the performance of the driver.

This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.

Specify the views you want in a comma-separated list. Each view should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Views=ViewA,[ViewB/WithSlash],WithCatalog.WithSchema.`ViewC With Space`.

AutoCache

Data Type

bool

Default Value

false

Remarks

When AutoCache = true, the driver automatically maintains a cache of your table's data in the database of your choice.

Setting the Caching Database

When AutoCache = true, the driver caches to a simple, file-based cache. You can configure its location or cache to a different database with the following properties:

CacheLocation: Specifies the path to the file store.
CacheDriver and CacheConnection: Specifies a driver to a database and the connection string.

CacheDriver

Data Type

string

Default Value

Remarks

You can cache to any database for which you have a JDBC driver, including CData JDBC drivers.

The cache database is determined based on the CacheDriver and CacheConnection properties. The CacheDriver is the name of the JDBC driver class that you want to use to cache data.

Note that you must also add the CacheDriver JAR file to the classpath.

The following examples show how to cache to several major databases. Refer to CacheConnection for more information on the JDBC URL syntax and typical connection properties.

Derby and Java DB

The driver simplifies Derby configuration. Java DB is the Oracle distribution of Derby. The JAR file is shipped in the JDK. You can find the JAR file, derby.jar, in the db subfolder of the JDK installation. In most caching scenarios, you need to specify only the following, after adding derby.jar to the classpath:

jdbc:magento:CacheLocation='c:/Temp/cachedir';User=admin;Password=admin;Url=https://mymagentohost/magento2

To customize the Derby JDBC URL, use CacheDriver and CacheConnection. For example, to cache to an in-memory database, use a JDBC URL like the following:

jdbc:magento:CacheDriver=org.apache.derby.jdbc.EmbeddedDriver;CacheConnection='jdbc:derby:memory';User=admin;Password=admin;Url=https://mymagentohost/magento2

SQLite

The following is a JDBC URL for the SQLite JDBC driver:

jdbc:magento:CacheDriver=org.sqlite.JDBC;CacheConnection='jdbc:sqlite:C:/Temp/sqlite.db';User=admin;Password=admin;Url=https://mymagentohost/magento2

MySQL

The following is a JDBC URL for the included CData JDBC Driver for MySQL:

  jdbc:magento:Cache Driver=cdata.jdbc.mysql.MySQLDriver;Cache Connection='jdbc:mysql:Server=localhost;Port=3306;Database=cache;User=root;Password=123456';User=admin;Password=admin;Url=https://mymagentohost/magento2

SQL Server

The following JDBC URL uses the Microsoft JDBC Driver for SQL Server:

jdbc:magento:Cache Driver=com.microsoft.sqlserver.jdbc.SQLServerDriver;Cache Connection='jdbc:sqlserver://localhost\sqlexpress:7437;user=sa;password=123456;databaseName=Cache';User=admin;Password=admin;Url=https://mymagentohost/magento2

Oracle

The following is a JDBC URL for the Oracle Thin Client:

jdbc:magento:Cache Driver=oracle.jdbc.OracleDriver;CacheConnection='jdbc:oracle:thin:scott/tiger@localhost:1521:orcldb';User=admin;Password=admin;Url=https://mymagentohost/magento2

NOTE: If using a version of Oracle older than 9i, the cache driver will instead be oracle.jdbc.driver.OracleDriver .

PostgreSQL

The following JDBC URL uses the official PostgreSQL JDBC driver:

jdbc:magento:CacheDriver=cdata.jdbc.postgresql.PostgreSQLDriver;CacheConnection='jdbc:postgresql:User=postgres;Password=admin;Database=postgres;Server=localhost;Port=5432;';User=admin;Password=admin;Url=https://mymagentohost/magento2

CacheConnection

Data Type

string

Default Value

Remarks

The cache database is determined based on the CacheDriver and CacheConnection properties. Both properties are required to use the cache database. Examples of common cache database settings can be found below. For more information on setting the caching database's driver, refer to CacheDriver.

The connection string specified in the CacheConnection property is passed directly to the underlying CacheDriver. Consult the documentation for the specific JDBC driver for more information on the available properties. Make sure to include the JDBC driver in your application's classpath.

Derby and Java DB

The driver simplifies caching to Derby, only requiring you to set the CacheLocation property to make a basic connection.

Alternatively, you can configure the connection to Derby manually using CacheDriver and CacheConnection. The following is the Derby JDBC URL syntax:

jdbc:derby:[subsubprotocol:][databaseName][;attribute=value[;attribute=value] ... ]

For example, to cache to an in-memory database, use the following:

jdbc:derby:memory

SQLite

To cache to SQLite, you can use the SQLite JDBC driver. The following is the syntax of the JDBC URL:

jdbc:sqlite:dataSource

Data Source: The path to an SQLite database file. Or, use a value of :memory to cache in memory.

MySQL

The installation includes the CData JDBC Driver for MySQL. The following is an example JDBC URL:

jdbc:mysql:User=root;Password=root;Server=localhost;Port=3306;Database=cache

The following are typical connection properties:

Server: The IP address or domain name of the server you want to connect to.
Port: The port that the server is running on.
User: The user name provided for authentication to the database.
Password: The password provided for authentication to the database.
Database: The name of the database.

SQL Server

The JDBC URL for the Microsoft JDBC Driver for SQL Server has the following syntax:

jdbc:sqlserver://[serverName[\instance][:port]][;database=databaseName][;property=value[;property=value] ... ]

For example:

jdbc:sqlserver://localhost\sqlexpress:1433;integratedSecurity=true

The following are typical SQL Server connection properties:

Server: The name or network address of the computer running SQL Server. To connect to a named instance instead of the default instance, this property can be used to specify the host name and the instance, separated by a backslash.
Port: The port SQL Server is running on.
Database: The name of the SQL Server database.
Integrated Security: Set this option to true to use the current Windows account for authentication. Set this option to false if you are setting the User and Password in the connection.
To use integrated security, you will also need to add sqljdbc_auth.dll to a folder on the Windows system path. This file is located in the auth subfolder of the Microsoft JDBC Driver for SQL Server installation. The bitness of the assembly must match the bitness of your JVM.
User Id: The user name provided for authentication with SQL Server. This property is only needed if you are not using integrated security.
Password: The password provided for authentication with SQL Server. This property is only needed if you are not using integrated security.

Oracle

The following is the conventional JDBC URL syntax for the Oracle JDBC Thin driver:

jdbc:oracle:thin:[userId/password]@[//]host[[:port][:sid]]

For example:

jdbc:oracle:thin:scott/tiger@myhost:1521:orcl

The following are typical connection properties:

Data Source: The connect descriptor that identifies the Oracle database. This can be a TNS connect descriptor, an Oracle Net Services name that resolves to a connect descriptor, or, after version 11g, an Easy Connect naming (the host name of the Oracle server with an optional port and service name).
Password: The password provided for authentication with the Oracle database.
User Id: The user Id provided for authentication with the Oracle database.

PostgreSQL

The following is the JDBC URL syntax for the official PostgreSQL JDBC driver:

jdbc:postgresql:[//[host[:port]]/]database[[?option=value][[&option=value][&option=value] ... ]]

For example, the following connection string connects to a database on the default host (localhost) and port (5432):

jdbc:postgresql:postgres

The following are typical connection properties:

Host: The address of the server hosting the PostgreSQL database.
Port: The port used to connect to the server hosting the PostgreSQL database.
Database: The name of the database.
User name: The user Id provided for authentication with the PostgreSQL database. You can specify this in the JDBC URL with the "user" parameter.
Password: The password provided for authentication with the PostgreSQL database.

CacheLocation

Data Type

string

Default Value

"%APPDATA%\\CData\\Magento Data Provider"

Remarks

The CacheLocation is a simple, file-based cache. The driver uses Java DB, Oracle's distribution of the Derby database. To cache to Java DB, you will need to add the Java DB JAR file to the classpath. The JAR file, derby.jar, is shipped in the JDK and located in the db subfolder of the JDK installation.

If left unspecified, the default location is "%APPDATA%\\CData\\Magento Data Provider" with %APPDATA% being set to the user's configuration directory:


Platform	%APPDATA%
Windows	The value of the APPDATA environment variable
Mac	~/Library/Application Support
Linux	~/.config

CacheTolerance

Data Type

int

Default Value

600

Remarks

The tolerance for stale data in the cache specified in seconds. This only applies when AutoCache is used. The driver checks with the data source for newer records after the tolerance interval has expired. Otherwise, it returns the data directly from the cache.

Offline

Data Type

bool

Default Value

false

Remarks

When Offline = true, all queries execute against the cache as opposed to the live data source. In this mode, certain queries like INSERT, UPDATE, DELETE, and CACHE are not allowed.

CacheMetadata

Data Type

bool

Default Value

false

Remarks

As you execute queries with this property set, table metadata in the Magento catalog are cached to the file store specified by CacheLocation if set or the user's home directory otherwise. A table's metadata will be retrieved only once, when the table is queried for the first time.

When to Use CacheMetadata

The driver automatically persists metadata in memory for up to two hours when you first discover the metadata for a table or view and therefore, CacheMetadata is generally not required. CacheMetadata becomes useful when metadata operations are expensive such as when you are working with large amounts of metadata or when you have many short-lived connections.

When Not to Use CacheMetadata

When you are working with volatile metadata: Metadata for a table is only retrieved the first time the connection to the table is made. To pick up new, changed, or deleted columns, you would need to delete and rebuild the metadata cache. Therefore, it is best to rely on the in-memory caching for cases where metadata changes often.
When you are caching to a database: CacheMetadata can only be used with CacheLocation. If you are caching to another database with the CacheDriver and CacheConnection properties, use AutoCache to cache implicitly. Or, use CACHE Statements to cache explicitly.

BatchSize

Data Type

int

Default Value

Remarks

When BatchSize is set to a value greater than 0, the batch operation will split the entire batch into separate batches of size BatchSize. The split batches will then be submitted to the server individually. This is useful when the server has limitations on the size of the request that can be submitted.

Setting BatchSize to 0 will submit the entire batch as specified.

ConnectionLifeTime

Data Type

int

Default Value

Remarks

The maximum lifetime of a connection in seconds. Once the time has elapsed, the connection object is disposed. The default is 0 which indicates there is no limit to the connection lifetime.

ConnectOnOpen

Data Type

bool

Default Value

false

Remarks

When set to true, a connection will be made to Magento when the connection is opened. This property enables the Test Connection feature available in various database tools.

This feature acts as a NOOP command as it is used to verify a connection can be made to Magento and nothing from this initial connection is maintained.

Setting this property to false may provide performance improvements (depending upon the number of times a connection is opened).

GenerateSchemaFiles

Data Type

string

Default Value

"Never"

Remarks

This property outputs schemas to .rsd files in the path specified by Location.

Available settings are the following:

Never: A schema file will never be generated.
OnUse: A schema file will be generated the first time a table is referenced, provided the schema file for the table does not already exist.
OnStart: A schema file will be generated at connection time for any tables that do not currently have a schema file.
OnCreate: A schema file will be generated by when running a CREATE TABLE SQL query.

Note that if you want to regenerate a file, you will first need to delete it.

Generate Schemas with SQL

When you set GenerateSchemaFiles to OnUse, the driver generates schemas as you execute SELECT queries. Schemas are generated for each table referenced in the query.

When you set GenerateSchemaFiles to OnCreate, schemas are only generated when a CREATE TABLE query is executed.

Generate Schemas on Connection

Another way to use this property is to obtain schemas for every table in your database when you connect. To do so, set GenerateSchemaFiles to OnStart and connect.

IncludeCustomAttributes

Data Type

bool

Default Value

true

Remarks

Setting this to true will cause custom attributes to be included in the column listing.

MaxRows

Data Type

int

Default Value

-1

Remarks

Limits the number of rows returned rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time.

Other

Data Type

string

Default Value

Remarks

The properties listed below are available for specific use cases. Normal driver use cases and functionality should not require these properties.

Specify multiple properties in a semicolon-separated list.

Caching Configuration


CachePartial=True	Caches only a subset of columns, which you can specify in your query.
QueryPassthrough=True	Passes the specified query to the cache database instead of using the SQL parser of the driver.

Integration and Formatting


DefaultColumnSize	Sets the default length of string fields when the data source does not provide column length in the metadata. The default value is 2000.
ConvertDateTimeToGMT	Determines whether to convert date-time values to GMT, instead of the local time of the machine.
RecordToFile=filename	Records the underlying socket data transfer to the specified file.

Pagesize

Data Type

string

Default Value

"100"

Remarks

The Pagesize property affects the maximum number of results to return per page from Magento2 schema.

PoolIdleTimeout

Data Type

int

Default Value

Remarks

The allowed idle time a connection can remain in the pool until the connection is closed. The default is 60 seconds.

PoolMaxSize

Data Type

int

Default Value

100

Remarks

The maximum connections in the pool. The default is 100. To disable this property, set the property value to 0 or less.

PoolMinSize

Data Type

int

Default Value

Remarks

The minimum number of connections in the pool. The default is 1.

PoolWaitTime

Data Type

int

Default Value

Remarks

The max seconds to wait for a connection to become available. If a new connection request is waiting for an available connection and exceeds this time, an error is thrown. By default, new requests wait forever for an available connection.

PseudoColumns

Data Type

string

Default Value

Remarks

This setting is particularly helpful in Entity Framework, which does not allow you to set a value for a pseudo column unless it is a table column. The value of this connection setting is of the format "Table1=Column1, Table1=Column2, Table2=Column3". You can use the "*" character to include all tables and all columns; for example, "*=*".

Readonly

Data Type

bool

Default Value

false

Remarks

If this property is set to true, the driver will allow only SELECT queries. INSERT, UPDATE, DELETE, and stored procedure queries will cause an error to be thrown.

RowScanDepth

Data Type

string

Default Value

"100"

Remarks

In the Magento APIs, Product entities can have different sets of associated attributes. For example, clothing Products will also have Gender, Size, and Sleeve_Length attributes in addition to the standard columns.

When TypeDetectionScheme is set to "RowScan", the RowScanDepth property determines the number of objects to scan to determine the unique columns.

Setting a high value may decrease performance. Setting a low value may prevent the driver from retrieving the relevant columns for the table.

RTK

Data Type

string

Default Value

Remarks

The RTK property may be used to license a build. See the included licensing file to see how to set this property. The runtime key is only available if you purchased an OEM license.

SupportEnhancedSQL

Data Type

bool

Default Value

true

Remarks

When SupportEnhancedSQL = true, the driver offloads as much of the SELECT statement processing as possible to Magento and then processes the rest of the query in memory. In this way, the driver can execute unsupported predicates, joins, and aggregation.

When SupportEnhancedSQL = false, the driver limits SQL execution to what is supported by the Magento API.

Execution of Predicates

The driver determines which of the clauses are supported by the data source and then pushes them to the source to get the smallest superset of rows that would satisfy the query. It then filters the rest of the rows locally. The filter operation is streamed, which enables the driver to filter effectively for even very large datasets.

Execution of Joins

The driver uses various techniques to join in memory. The driver trades off memory utilization against the requirement of reading the same table more than once.

Execution of Aggregates

The driver retrieves all rows necessary to process the aggregation in memory.

Timeout

Data Type

int

Default Value

Remarks

If Timeout = 0, operations do not time out. The operations run until they complete successfully or until they encounter an error condition.

If Timeout expires and the operation is not yet complete, the driver throws an exception.

TypeDetectionScheme

Data Type

string

Default Value

"RowScan"

Remarks

To access these unique columns, set the following connection properties:


RowScan	Setting TypeDetectionScheme to RowScan will scan objects (rows) to heuristically determine additional columns when you connect. The RowScanDepth determines the number of objects to be scanned.
None	Setting TypeDetectionScheme to None will return only the standard columns.

UseConnectionPooling

Data Type

bool

Default Value

false

Remarks

This property enables connection pooling. The default is false. See Connection Pooling for information on using connection pools.

UseSimpleNames

Data Type

bool

Default Value

false

Remarks

Magento tables and columns can use special characters in names that are normally not allowed in standard databases. UseSimpleNames makes the driver easier to use with traditional database tools.

Setting UseSimpleNames to true will simplify the names of tables and columns returned. It will enforce a naming scheme such that only alphanumeric characters and the underscore are valid for the displayed table and column names. Any nonalphanumeric characters will be converted to an underscore.

TablesBack To Top

BundleProductLinkChildren
BundleProductOption
CatalogAttributeSet
CatalogCategoryAttribute
CatalogCategoryAttributeOptionOptions
CatalogCategoryLinkProducts
CatalogProductAttribute
CatalogProductAttributeTypesList
CatalogProductLinkTypeList
CatalogProductTierPriceTiers
CatalogProductTypeList
CheckoutAgreementsCheckoutAgreements
CmsBlock
CmsPage
ConfigurableProductLinkChildren
ConfigurableProductOption
CustomerAccountBillingAddress
CustomerAccountShippingAddress
CustomerAddress
CustomerGroup
CustomerGroupDefault
Customers
DirectoryCountryInformationAcquirer
DirectoryCurrencyInformationAcquirer
DownloadableLink
DownloadableSample
EavAttributeSet
GiftMessageCart
GiftMessageGuestCart
GiftMessageGuestItem
GiftMessageItem
GuestBillingAddresses
GuestPaymentInformation
GuestPaymentMethods
GuestPaymentSelectedMethods
GuestShippingMethods
Inventory
OrderComments
OrderItems
Orders
PaymentMethods
PaymentSelectedMethods
ProductAttributeGroup
ProductAttributeOptions
ProductAttributes
ProductCategories
ProductCustomOption
ProductMedia
Products
QuoteCart
QuoteCartItem
QuoteCartTotal
QuoteGuestCart
QuoteGuestCartItem
QuoteGuestCartTotal
SalesCreditmemo
SalesCreditmemoComments
SalesInvoice
SalesInvoiceComments
SalesRule
SalesRuleCoupon
SalesShipment
SalesShipmentComments
SalesTransaction
ShippingMethods
StoreGroup
Stores
StoreWebsite
TaxClass
TaxRate
TaxRule

BundleProductLinkChildren

BundleProductLinkChildren is an auto generated table

Columns


Name	Type	ReadOnly	Description
Id [KEY]	String	False	The identifier
CanChangeQuantity	Int	False	Whether quantity could be changed
ExtensionAttributes	String	False	This is an auto-generated column
IsDefault	Bool	False	Is default
OptionId	Int	False	Option id
Position	Int	False	Position
Price	String	False	Price
PriceType	Int	False	Price type
Qty	String	False	Qty
Sku	String	False	Linked product sku

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.


Name	Type	Description
ProductSku_p	String	This is an auto-generated column

BundleProductOption

BundleProductOption is an auto generated table

Columns


Name	Type	ReadOnly	Description
ExtensionAttributes	String	False	This is an auto-generated column
OptionId [KEY]	Int	False	Option id
Position	Int	False	Option position
ProductLinks	String	False	Product links
Required	Bool	False	Is required option
Sku [KEY]	String	False	Product sku
Title	String	False	Option title
Type	String	False	Input type

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.


Name	Type	Description
Sku_p	String	This is an auto-generated column
OptionId_p	Int	This is an auto-generated column

CatalogAttributeSet

CatalogAttributeSet is an auto generated table

Columns


Name	Type	ReadOnly	Description
AttributeSetId [KEY]	Int	False	Attribute set ID
AttributeSetName	String	False	Attribute set name
EntityTypeId	Int	False	Attribute set entity type id
ExtensionAttributes	String	False	This is an auto-generated column
SortOrder	Int	False	Attribute set sort order index

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.


Name	Type	Description
AttributeSetId_p	Int	This is an auto-generated column

CatalogCategoryAttribute

CatalogCategoryAttribute is an auto generated table

Columns


Name	Type	ReadOnly	Description
ApplyTo	String	False	Apply to value for the element
AttributeCode	String	False	Code of the attribute.
AttributeId [KEY]	Int	False	Id of the attribute.
BackendModel	String	False	Backend model
BackendType	String	False	Backend type.
DefaultFrontendLabel	String	False	Frontend label for default store
DefaultValue	String	False	Default value for the element.
EntityTypeId	String	False	Entity type id
ExtensionAttributes	String	False	This is an auto-generated column
FrontendClass	String	False	Frontend class of attribute
FrontendInput	String	False	HTML for input element.
FrontendLabels	String	False	Frontend label for each store
IsComparable	String	False	The attribute can be compared on the frontend
IsFilterable	Bool	False	It used in layered navigation
IsFilterableInGrid	Bool	False	It is filterable in catalog product grid
IsFilterableInSearch	Bool	False	It is used in search results layered navigation
IsHtmlAllowedOnFront	Bool	False	The HTML tags are allowed on the frontend
IsRequired	Bool	False	Attribute is required.
IsSearchable	String	False	The attribute can be used in Quick Search
IsUnique	String	False	This is a unique attribute
IsUsedForPromoRules	String	False	The attribute can be used for promo rules
IsUsedInGrid	Bool	False	It is used in catalog product grid
IsUserDefined	Bool	False	Current attribute has been defined by a user.
IsVisible	Bool	False	Attribute is visible on frontend.
IsVisibleInAdvancedSearch	String	False	The attribute can be used in Advanced Search
IsVisibleInGrid	Bool	False	It is visible in catalog product grid
IsVisibleOnFront	String	False	The attribute is visible on the frontend
IsWysiwygEnabled	Bool	False	WYSIWYG flag
Note	String	False	The note attribute for the element.
Options	String	False	Options of the attribute (key => value pairs for select)
Position	Int	False	Position
Scope	String	False	Attribute scope
SourceModel	String	False	Source model
UsedForSortBy	Bool	False	It is used for sorting in product listing
UsedInProductListing	String	False	The attribute can be used in product listing
ValidationRules	String	False	Validation rules.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.


Name	Type	Description
AttributeCode_p	String	This is an auto-generated column

CatalogCategoryAttributeOptionOptions

CatalogCategoryAttributeOptionOptions is an auto generated table

Columns


Name	Type	ReadOnly	Description
IsDefault	Bool	False	Default
Label [KEY]	String	False	Option label
SortOrder	Int	False	Option order
StoreLabels	String	False	Option label for store scopes
Value	String	False	Option value

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.


Name	Type	Description
AttributeCode_p	String	This is an auto-generated column

CatalogCategoryLinkProducts

CatalogCategoryLinkProducts is an auto generated table

Columns


Name	Type	ReadOnly	Description
CategoryId	String	False	Category id
ExtensionAttributes	String	False	This is an auto-generated column
Position	Int	False	This is an auto-generated column
Sku [KEY]	String	False	This is an auto-generated column

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.


Name	Type	Description
CategoryId_p	Int	This is an auto-generated column

CatalogProductAttribute

CatalogProductAttribute is an auto generated table

Columns


Name	Type	ReadOnly	Description
ApplyTo	String	False	Apply to value for the element
AttributeCode	String	False	Code of the attribute.
AttributeId [KEY]	Int	False	Id of the attribute.
BackendModel	String	False	Backend model
BackendType	String	False	Backend type.
DefaultFrontendLabel	String	False	Frontend label for default store
DefaultValue	String	False	Default value for the element.
EntityTypeId	String	False	Entity type id
ExtensionAttributes	String	False	This is an auto-generated column
FrontendClass	String	False	Frontend class of attribute
FrontendInput	String	False	HTML for input element.
FrontendLabels	String	False	Frontend label for each store
IsComparable	String	False	The attribute can be compared on the frontend
IsFilterable	Bool	False	It used in layered navigation
IsFilterableInGrid	Bool	False	It is filterable in catalog product grid
IsFilterableInSearch	Bool	False	It is used in search results layered navigation
IsHtmlAllowedOnFront	Bool	False	The HTML tags are allowed on the frontend
IsRequired	Bool	False	Attribute is required.
IsSearchable	String	False	The attribute can be used in Quick Search
IsUnique	String	False	This is a unique attribute
IsUsedForPromoRules	String	False	The attribute can be used for promo rules
IsUsedInGrid	Bool	False	It is used in catalog product grid
IsUserDefined	Bool	False	Current attribute has been defined by a user.
IsVisible	Bool	False	Attribute is visible on frontend.
IsVisibleInAdvancedSearch	String	False	The attribute can be used in Advanced Search
IsVisibleInGrid	Bool	False	It is visible in catalog product grid
IsVisibleOnFront	String	False	The attribute is visible on the frontend
IsWysiwygEnabled	Bool	False	WYSIWYG flag
Note	String	False	The note attribute for the element.
Options	String	False	Options of the attribute (key => value pairs for select)
Position	Int	False	Position
Scope	String	False	Attribute scope
SourceModel	String	False	Source model
UsedForSortBy	Bool	False	It is used for sorting in product listing
UsedInProductListing	String	False	The attribute can be used in product listing
ValidationRules	String	False	Validation rules.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.


Name	Type	Description
AttributeCode_p	String	This is an auto-generated column

CatalogProductAttributeTypesList

CatalogProductAttributeTypesList is an auto generated table

Columns


Name	Type	ReadOnly	Description
ExtensionAttributes	String	False	This is an auto-generated column
Label [KEY]	String	False	Type label
Value	String	False	Value

CatalogProductLinkTypeList

CatalogProductLinkTypeList is an auto generated table

Columns


Name	Type	ReadOnly	Description
Code [KEY]	Int	False	Link type code
ExtensionAttributes	String	False	This is an auto-generated column
Name	String	False	Link type name
Type	String	False	Attribute type

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.


Name	Type	Description
Type_p	String	This is an auto-generated column

CatalogProductTierPriceTiers

CatalogProductTierPriceTiers is an auto generated table

Columns


Name	Type	ReadOnly	Description
CustomerGroupId [KEY]	Int	False	Customer group id
ExtensionAttributes	String	False	This is an auto-generated column
Qty	String	False	Tier qty
Value	String	False	Price value

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.


Name	Type	Description
CustomerGroupId_p	String	'all' can be used to specify 'ALL GROUPS'
Sku_p	String	This is an auto-generated column

CatalogProductTypeList

CatalogProductTypeList is an auto generated table

Columns


Name	Type	ReadOnly	Description
ExtensionAttributes	String	False	This is an auto-generated column
Label [KEY]	String	False	Product type label
Name	String	False	Product type code

CheckoutAgreementsCheckoutAgreements

CheckoutAgreementsCheckoutAgreements is an auto generated table

Columns


Name	Type	ReadOnly	Description
AgreementId [KEY]	Int	False	Agreement ID.
CheckboxText	String	False	Agreement checkbox text.
Content	String	False	Agreement content.
ContentHeight	String	False	Agreement content height. Otherwise, null.
ExtensionAttributes	String	False	This is an auto-generated column
IsActive	Bool	False	Agreement status.
IsHtml	Bool	False	* true - HTML. * false - plain text.
Mode	Int	False	The agreement applied mode.
Name	String	False	Agreement name.

CmsBlock

CmsBlock is an auto generated table

Columns


Name	Type	ReadOnly	Description
Id [KEY]	Int	False	ID
Active	Bool	False	Active
Content	String	False	Content
CreationTime	Datetime	False	Creation time
Identifier	String	False	Identifier
Title	String	False	Title
UpdateTime	Datetime	False	Update time

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.


Name	Type	Description
BlockId_p	Int	This is an auto-generated column
Id_p	String	This is an auto-generated column

CmsPage

CmsPage is an auto generated table

Columns


Name	Type	ReadOnly	Description
Id [KEY]	Int	False	ID
Active	Bool	False	Active
Content	String	False	Content
ContentHeading	String	False	Content heading
CreationTime	Datetime	False	Creation time
CustomLayoutUpdateXml	String	False	Custom layout update xml
CustomRootTemplate	String	False	Custom root template
CustomTheme	String	False	Custom theme
CustomThemeFrom	String	False	Custom theme from
CustomThemeTo	String	False	Custom theme to
Identifier	String	False	Identifier
LayoutUpdateXml	String	False	Layout update xml
MetaDescription	String	False	Meta description
MetaKeywords	String	False	Meta keywords
MetaTitle	String	False	Meta title
PageLayout	String	False	Page layout
SortOrder	String	False	Sort order
Title	String	False	Title
UpdateTime	Datetime	False	Update time

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.


Name	Type	Description
Id_p	String	This is an auto-generated column
PageId_p	Int	This is an auto-generated column

ConfigurableProductLinkChildren

ConfigurableProductLinkChildren is an auto generated table

Columns


Name	Type	ReadOnly	Description
Id [KEY]	Int	False	Id
AttributeSetId	Int	False	Attribute set id
CreatedAt	Datetime	False	Created date
ExtensionAttributes	String	False	This is an auto-generated column
MediaGalleryEntries	String	False	Media gallery entries
Name	String	False	Name
Options	String	False	List of product options
Price	String	False	Price
ProductLinks	String	False	Product links info
Sku	String	False	Sku
Status	Int	False	Status
TierPrices	String	False	List of product tier prices
TypeId	String	False	Type id
UpdatedAt	Datetime	False	Updated date
Visibility	Int	False	Visibility
Weight	String	False	Weight

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.


Name	Type	Description
Sku_p	String	This is an auto-generated column

ConfigurableProductOption

ConfigurableProductOption is an auto generated table

Columns


Name	Type	ReadOnly	Description
Id [KEY]	Int	False	This is an auto-generated column
AttributeId	String	False	This is an auto-generated column
ExtensionAttributes	String	False	This is an auto-generated column
IsUseDefault	Bool	False	This is an auto-generated column
Label	String	False	This is an auto-generated column
Position	Int	False	This is an auto-generated column
ProductId	Int	False	This is an auto-generated column
Values	String	False	This is an auto-generated column

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.


Name	Type	Description
Sku_p	String	This is an auto-generated column
Id_p	Int	This is an auto-generated column

CustomerAccountBillingAddress

CustomerAccountBillingAddress is an auto generated table

Columns


Name	Type	ReadOnly	Description
Id [KEY]	Int	False	ID
City	String	False	City name
Company	String	False	Company
CountryId	String	False	Country code in ISO_3166-2 format
CustomerId	Int	False	Customer ID
DefaultBilling	Bool	False	If this address is default billing address
DefaultShipping	Bool	False	If this address is default shipping address.
ExtensionAttributes	String	False	This is an auto-generated column
Fax	String	False	Fax number
Firstname	String	False	First name
Lastname	String	False	Last name
Middlename	String	False	Middle name
Postcode	String	False	Postcode
Prefix	String	False	Prefix
Region	String	False	This is an auto-generated column
RegionId	Int	False	Region ID
Street	String	False	Street
Suffix	String	False	Suffix
Telephone	String	False	Telephone number
VatId	String	False	Vat id

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.


Name	Type	Description
CustomerId_p	Int	This is an auto-generated column

CustomerAccountShippingAddress

CustomerAccountShippingAddress is an auto generated table

Columns


Name	Type	ReadOnly	Description
Id [KEY]	Int	False	ID
City	String	False	City name
Company	String	False	Company
CountryId	String	False	Country code in ISO_3166-2 format
CustomerId	Int	False	Customer ID
DefaultBilling	Bool	False	If this address is default billing address
DefaultShipping	Bool	False	If this address is default shipping address.
ExtensionAttributes	String	False	This is an auto-generated column
Fax	String	False	Fax number
Firstname	String	False	First name
Lastname	String	False	Last name
Middlename	String	False	Middle name
Postcode	String	False	Postcode
Prefix	String	False	Prefix
Region	String	False	This is an auto-generated column
RegionId	Int	False	Region ID
Street	String	False	Street
Suffix	String	False	Suffix
Telephone	String	False	Telephone number
VatId	String	False	Vat id

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.


Name	Type	Description
CustomerId_p	Int	This is an auto-generated column

CustomerAddress

CustomerAddress is an auto generated table

Columns


Name	Type	ReadOnly	Description
Id [KEY]	Int	False	ID
City	String	False	City name
Company	String	False	Company
CountryId	String	False	Country code in ISO_3166-2 format
CustomerId	Int	False	Customer ID
DefaultBilling	Bool	False	If this address is default billing address
DefaultShipping	Bool	False	If this address is default shipping address.
ExtensionAttributes	String	False	This is an auto-generated column
Fax	String	False	Fax number
Firstname	String	False	First name
Lastname	String	False	Last name
Middlename	String	False	Middle name
Postcode	String	False	Postcode
Prefix	String	False	Prefix
Region	String	False	This is an auto-generated column
RegionId	Int	False	Region ID
Street	String	False	Street
Suffix	String	False	Suffix
Telephone	String	False	Telephone number
VatId	String	False	Vat id

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.


Name	Type	Description
AddressId_p	Int	This is an auto-generated column

CustomerGroup

CustomerGroup is an auto generated table

Columns


Name	Type	ReadOnly	Description
Id [KEY]	Int	False	Id
Code	String	False	Code
ExtensionAttributes	String	False	This is an auto-generated column
TaxClassId	Int	False	Tax class id
TaxClassName	String	False	Tax class name

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.


Name	Type	Description
Id_p	Int	This is an auto-generated column

CustomerGroupDefault

CustomerGroupDefault is an auto generated table

Columns


Name	Type	ReadOnly	Description
Id [KEY]	Int	False	Id
Code	String	False	Code
ExtensionAttributes	String	False	This is an auto-generated column
TaxClassId	Int	False	Tax class id
TaxClassName	String	False	Tax class name

Customers

Customers is an auto generated table

Select

The Customers table supports server-side processing for all columns in the WHERE clause. Most of these columns support server-side processing for the >, >=, <, <=, =, IN, NOT IN and LIKE operators.

The following examples show the types of queries processed server side.

Retrieve all customers from your Magento server.
```
SELECT * FROM Customers
```
Retrieve a single customer:
```
SELECT * FROM Customers WHERE Id = '1'
```

Filter by status:

SELECT * FROM Customers WHERE GroupId = 1

Filter by created date:

SELECT * FROM Customers WHERE CreatedAt > '2017-08-01'

Example query with multiple filters:

SELECT * FROM Customers WHERE DefaultBilling IN (1,2) AND Firstname LIKE '%i%' AND WebsiteId=1 LIMIT 50

You can turn off client-side execution by setting SupportEnhancedSQL to false, in which case any search criteria that refers to any other columns will cause an error.

Insert

To add a Customer, the FirstName, LastName, and Email fields are required.

INSERT INTO Customers(FirstName,LastName,Email,WebsiteId,GroupId) VALUES ('John','Doe','john@example.com',1,1)

Update

Magento allows updates for the FirstName, LastName, MiddleName, Suffix, Email, GroupId, Prefix, DisableAutoGroupChange, and Taxvat columns. WebsiteId is a required value.

UPDATE Customers SET FirstName='Jane', Email='jane@example.com', GroupId=2, DisableAutoGroupChange='0', Prefix='x', Middlename='J', LastName='Doe', Suffix='y',Taxvat=1, WebsiteId=1 WHERE Id = 4

Delete

Customers can be deleted by providing the Id of the Customer and issuing a DELETE statement.

DELETE FROM Customers WHERE Id = 4

Columns


Name	Type	ReadOnly	Description
Id [KEY]	Int	False	Customer id
Addresses	String	False	Customer addresses.
Confirmation	String	False	Confirmation
CreatedAt	Datetime	False	Created at time
CreatedIn	String	False	Created in area
DefaultBilling	String	False	Default billing address id
DefaultShipping	String	False	Default shipping address id
DisableAutoGroupChange	Int	False	Disable auto group change flag.
Dob	String	False	Date of birth
Email	String	False	Email address
ExtensionAttributes	String	False	This is an auto-generated column
Firstname	String	False	First name
Gender	Int	False	Gender
GroupId	Int	False	Group id
Lastname	String	False	Last name
Middlename	String	False	Middle name
Prefix	String	False	Prefix
StoreId	Int	False	Store id
Suffix	String	False	Suffix
Taxvat	String	False	Tax Vat
UpdatedAt	Datetime	False	Updated at time
WebsiteId	Int	False	Website id

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.


Name	Type	Description
Id_p	String	This is an auto-generated column

DirectoryCountryInformationAcquirer

DirectoryCountryInformationAcquirer is an auto generated table

Columns


Name	Type	ReadOnly	Description
Id [KEY]	String	False	The country id for the store.
AvailableRegions	String	False	The available regions for the store.
ExtensionAttributes	String	False	This is an auto-generated column
FullNameEnglish	String	False	The country full name (in English) for the store.
FullNameLocale	String	False	The country full name (in store locale) for the store.
ThreeLetterAbbreviation	String	False	The country 3 letter abbreviation for the store.
TwoLetterAbbreviation	String	False	The country 2 letter abbreviation for the store.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.


Name	Type	Description
CountryId_p	String	This is an auto-generated column

DirectoryCurrencyInformationAcquirer

DirectoryCurrencyInformationAcquirer is an auto generated table

Columns


Name	Type	ReadOnly	Description
AvailableCurrencyCodes	String	False	The list of allowed currency codes for the store.
BaseCurrencyCode [KEY]	String	False	The base currency code for the store.
BaseCurrencySymbol	String	False	The currency symbol of the base currency for the store.
DefaultDisplayCurrencyCode	String	False	The default display currency code for the store.
DefaultDisplayCurrencySymbol	String	False	The currency symbol of the default display currency for the store.
ExchangeRates	String	False	The list of exchange rate information for the store.
ExtensionAttributes	String	False	This is an auto-generated column

DownloadableLink

DownloadableLink is an auto generated table

Columns


Name	Type	ReadOnly	Description
Id [KEY]	Int	False	Sample(or link) id
ExtensionAttributes	String	False	This is an auto-generated column
IsShareable	Int	False	Shareable status
LinkFile	String	False	relative file path
LinkFileContent	String	False	This is an auto-generated column
LinkType	String	False	This is an auto-generated column
LinkUrl	String	False	Link url or null when type is 'file'
NumberOfDownloads	Int	False	Of downloads per user
Price	String	False	Price
SampleFile	String	False	relative file path
SampleFileContent	String	False	This is an auto-generated column
SampleType	String	False	This is an auto-generated column
SampleUrl	String	False	file URL
SortOrder	Int	False	This is an auto-generated column
Title	String	False	This is an auto-generated column

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.


Name	Type	Description
Id_p	Int	This is an auto-generated column
Sku_p	String	This is an auto-generated column

DownloadableSample

DownloadableSample is an auto generated table

Columns


Name	Type	ReadOnly	Description
Id [KEY]	Int	False	Sample(or link) id
ExtensionAttributes	String	False	This is an auto-generated column
SampleFile	String	False	relative file path
SampleFileContent	String	False	This is an auto-generated column
SampleType	String	False	This is an auto-generated column
SampleUrl	String	False	file URL
SortOrder	Int	False	Order index for sample
Title	String	False	Title

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.


Name	Type	Description
Id_p	Int	This is an auto-generated column
Sku_p	String	This is an auto-generated column

EavAttributeSet

EavAttributeSet is an auto generated table

Columns


Name	Type	ReadOnly	Description
AttributeSetId [KEY]	Int	False	Attribute set ID
AttributeSetName	String	False	Attribute set name
EntityTypeId	Int	False	Attribute set entity type id
ExtensionAttributes	String	False	This is an auto-generated column
SortOrder	Int	False	Attribute set sort order index

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.


Name	Type	Description
AttributeSetId_p	Int	This is an auto-generated column

GiftMessageCart

GiftMessageCart is an auto generated table

Columns


Name	Type	ReadOnly	Description
CustomerId	Int	False	Customer ID. Otherwise, null.
ExtensionAttributes	String	False	This is an auto-generated column
GiftMessageId [KEY]	Int	False	Gift message ID. Otherwise, null.
Message	String	False	Message text.
Recipient	String	False	Recipient name.
Sender	String	False	Sender name.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.


Name	Type	Description
CartId_p	Int	The cart ID.

GiftMessageGuestCart

GiftMessageGuestCart is an auto generated table

Columns


Name	Type	ReadOnly	Description
CustomerId	Int	False	Customer ID. Otherwise, null.
ExtensionAttributes	String	False	This is an auto-generated column
GiftMessageId [KEY]	Int	False	Gift message ID. Otherwise, null.
Message	String	False	Message text.
Recipient	String	False	Recipient name.
Sender	String	False	Sender name.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.


Name	Type	Description
CartId_p	String	The cart ID.

GiftMessageGuestItem

GiftMessageGuestItem is an auto generated table

Columns


Name	Type	ReadOnly	Description
CustomerId	Int	False	Customer ID. Otherwise, null.
ExtensionAttributes	String	False	This is an auto-generated column
GiftMessageId [KEY]	Int	False	Gift message ID. Otherwise, null.
Message	String	False	Message text.
Recipient	String	False	Recipient name.
Sender	String	False	Sender name.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.


Name	Type	Description
CartId_p	String	The cart ID.
ItemId_p	Int	The item ID.

GiftMessageItem

GiftMessageItem is an auto generated table

Columns


Name	Type	ReadOnly	Description
CustomerId	Int	False	Customer ID. Otherwise, null.
ExtensionAttributes	String	False	This is an auto-generated column
GiftMessageId [KEY]	Int	False	Gift message ID. Otherwise, null.
Message	String	False	Message text.
Recipient	String	False	Recipient name.
Sender	String	False	Sender name.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.


Name	Type	Description
ItemId_p	Int	The item ID.
CartId_p	Int	The cart ID.

GuestBillingAddresses

GuestBillingAddresses is an auto generated table

Columns


Name	Type	ReadOnly	Description
Id [KEY]	Int	False	Id
City	String	False	City name
Company	String	False	Company
CountryId	String	False	Country id
CustomerAddressId	Int	False	Customer address id
CustomerId	Int	False	Customer id
Email	String	False	Billing/shipping email
ExtensionAttributes	String	False	This is an auto-generated column
Fax	String	False	Fax number
Firstname	String	False	First name
Lastname	String	False	Last name
Middlename	String	False	Middle name
Postcode	String	False	Postcode
Prefix	String	False	Prefix
Region	String	False	Region name
RegionCode	String	False	Region code
RegionId	Int	False	Region id
SameAsBilling	Int	False	Same as billing flag
SaveInAddressBook	Int	False	Save in address book flag
Street	String	False	Street
Suffix	String	False	Suffix
Telephone	String	False	Telephone number
VatId	String	False	Vat id

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.


Name	Type	Description
CartId_p	String	The cart ID.

GuestPaymentInformation

GuestPaymentInformation is an auto generated table

Columns


Name	Type	ReadOnly	Description
ExtensionAttributes	String	False	This is an auto-generated column
PaymentMethods	String	False	This is an auto-generated column
Totals	String	False	This is an auto-generated column

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.


Name	Type	Description
CartId_p	String	This is an auto-generated column

GuestPaymentMethods

GuestPaymentMethods is an auto generated table

Columns


Name	Type	ReadOnly	Description
Code [KEY]	String	False	Payment method code
Title	String	False	Payment method title

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.


Name	Type	Description
CartId_p	String	The cart ID.

GuestPaymentSelectedMethods

GuestPaymentSelectedMethods is an auto generated table

Columns


Name	Type	ReadOnly	Description
AdditionalData	String	False	Payment additional details
ExtensionAttributes	String	False	This is an auto-generated column
Method [KEY]	String	False	Payment method code
PoNumber	String	False	Purchase order number

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.


Name	Type	Description
CartId_p	String	The cart ID.

GuestShippingMethods

GuestShippingMethods is an auto generated table

Columns


Name	Type	ReadOnly	Description
Amount	String	False	Shipping amount in store currency.
Available	Bool	False	The value of the availability flag for the current shipping method.
BaseAmount	String	False	Shipping amount in base currency.
CarrierCode	String	False	Shipping carrier code.
CarrierTitle	String	False	Shipping carrier title. Otherwise, null.
ErrorMessage	String	False	Shipping Error message.
ExtensionAttributes	String	False	This is an auto-generated column
MethodCode [KEY]	String	False	Shipping method code.
MethodTitle	String	False	Shipping method title. Otherwise, null.
PriceExclTax	String	False	Shipping price excl tax.
PriceInclTax	String	False	Shipping price incl tax.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.


Name	Type	Description
CartId_p	String	The shopping cart ID.

Inventory

Inventory is an auto generated table

Select

You must specify the product SKU to query this table. The driver will use the Magento API to filter the results by ProductSku_p while the rest of the filter is executed client side within the driver.

For example, the following query is the only one supported server side:

SELECT * FROM Inventory WHERE ProductSKU = 'sjl-8082'

You can turn off client-side execution by setting SupportEnhancedSQL to false, in which case any search criteria that refers to any other columns will cause an error.

Update

To update a product inventory, you must set the ItemId of an existing item along with the ProductSKU.

UPDATE [Inventory] SET Qty = '87' WHERE ProductSKU = 'sjl-8082' AND ItemId = '1'

Columns


Name	Type	ReadOnly	Description
SearchCriteria	String	False	This is an auto-generated column
TotalCount	Int	False	Total count.
Backorders	Int	False	Backorders status
EnableQtyIncrements	Bool	False	Whether Quantity Increments is enabled
ExtensionAttributes	String	False	This is an auto-generated column
IsDecimalDivided	Bool	False	This is an auto-generated column
IsInStock	Bool	False	Stock Availability
IsQtyDecimal	Bool	False	This is an auto-generated column
ItemId	Int	False	This is an auto-generated column
LowStockDate	String	False	This is an auto-generated column
ManageStock	Bool	False	Can Manage Stock
MaxSaleQty	String	False	Maximum Qty Allowed in Shopping Cart data wrapper
MinQty	String	False	Minimal quantity available for item status in stock
MinSaleQty	String	False	Minimum Qty Allowed in Shopping Cart or NULL when there is no limitation
NotifyStockQty	String	False	Notify for Quantity Below data wrapper
ProductId	Int	False	This is an auto-generated column
Qty	String	False	This is an auto-generated column
QtyIncrements	String	False	Quantity Increments data wrapper
ShowDefaultNotificationMessage	Bool	False	This is an auto-generated column
StockId	Int	False	Stock identifier
StockStatusChangedAuto	Int	False	This is an auto-generated column
UseConfigBackorders	Bool	False	This is an auto-generated column
UseConfigEnableQtyInc	Bool	False	This is an auto-generated column
UseConfigManageStock	Bool	False	This is an auto-generated column
UseConfigMaxSaleQty	Bool	False	This is an auto-generated column
UseConfigMinQty	Bool	False	This is an auto-generated column
UseConfigMinSaleQty	Int	False	This is an auto-generated column
UseConfigNotifyStockQty	Bool	False	This is an auto-generated column
UseConfigQtyIncrements	Bool	False	This is an auto-generated column
StockItem	String	False	This is an auto-generated column
StockStatus	Int	False	This is an auto-generated column

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.


Name	Type	Description
ItemId_p	String	This is an auto-generated column
ProductSku_p	String	This is an auto-generated column

OrderComments

OrderComments is an auto generated table

Columns


Name	Type	ReadOnly	Description
Comment	String	False	Comment.
CreatedAt	Datetime	False	Created-at timestamp.
EntityId [KEY]	Int	False	Order status history ID.
EntityName	String	False	Entity name.
ExtensionAttributes	String	False	This is an auto-generated column
IsCustomerNotified	Int	False	Is-customer-notified flag value.
IsVisibleOnFront	Int	False	Is-visible-on-storefront flag value.
ParentId	Int	False	Parent ID.
Status	String	False	Status.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.


Name	Type	Description
Id_p	Int	The order ID.

OrderItems

OrderItems is an auto generated table

Select

The driver will use the Magento API to filter the results by ItemId while the rest of the filter is executed client side within the driver.

The following examples demonstrate the types of queries processed on the server.

Retrieve information about all order items:
```
SELECT * FROM OrderItems
```
Filter by ItemId to get detailed information about an order item:
```
SELECT * FROM OrderItems WHERE ItemId = '3'
```

You can turn off client-side execution by setting SupportEnhancedSQL to false, in which case any search criteria that refers to any other columns will cause an error.

Columns


Name	Type	ReadOnly	Description
AdditionalData	String	False	Additional data.
AmountRefunded	String	False	Amount refunded.
AppliedRuleIds	String	False	Applied rule IDs.
BaseAmountRefunded	String	False	Base amount refunded.
BaseCost	String	False	Base cost.
BaseDiscountAmount	String	False	Base discount amount.
BaseDiscountInvoiced	String	False	Base discount invoiced.
BaseDiscountRefunded	String	False	Base discount refunded.
BaseDiscountTaxCompensationAmount	String	False	Base discount tax compensation amount.
BaseDiscountTaxCompensationInvoiced	String	False	Base discount tax compensation invoiced.
BaseDiscountTaxCompensationRefunded	String	False	Base discount tax compensation refunded.
BaseOriginalPrice	String	False	Base original price.
BasePrice	String	False	Base price.
BasePriceInclTax	String	False	Base price including tax.
BaseRowInvoiced	String	False	Base row invoiced.
BaseRowTotal	String	False	Base row total.
BaseRowTotalInclTax	String	False	Base row total including tax.
BaseTaxAmount	String	False	Base tax amount.
BaseTaxBeforeDiscount	String	False	Base tax before discount.
BaseTaxInvoiced	String	False	Base tax invoiced.
BaseTaxRefunded	String	False	Base tax refunded.
BaseWeeeTaxAppliedAmount	String	False	Base WEEE tax applied amount.
BaseWeeeTaxAppliedRowAmnt	String	False	Base WEEE tax applied row amount.
BaseWeeeTaxDisposition	String	False	Base WEEE tax disposition.
BaseWeeeTaxRowDisposition	String	False	Base WEEE tax row disposition.
CreatedAt	Datetime	False	Created-at timestamp.
Description	String	False	Description.
DiscountAmount	String	False	Discount amount.
DiscountInvoiced	String	False	Discount invoiced.
DiscountPercent	String	False	Discount percent.
DiscountRefunded	String	False	Discount refunded.
DiscountTaxCompensationAmount	String	False	Discount tax compensation amount.
DiscountTaxCompensationCanceled	String	False	Discount tax compensation canceled.
DiscountTaxCompensationInvoiced	String	False	Discount tax compensation invoiced.
DiscountTaxCompensationRefunded	String	False	Discount tax compensation refunded.
ExtensionAttributes	String	False	This is an auto-generated column
ExtOrderItemId	String	False	External order item ID.
FreeShipping	Int	False	Free-shipping flag value.
IsQtyDecimal	Int	False	Is-quantity-decimal flag value.
IsVirtual	Int	False	Is-virtual flag value.
ItemId [KEY]	Int	False	Item ID.
LockedDoInvoice	Int	False	Locked DO invoice flag value.
LockedDoShip	Int	False	Locked DO ship flag value.
Name	String	False	Name.
NoDiscount	Int	False	No-discount flag value.
OrderId	Int	False	Order ID.
OriginalPrice	String	False	Original price.
ParentItem	String	False	This is an auto-generated column
ParentItemId	Int	False	Parent item ID.
Price	String	False	Price.
PriceInclTax	String	False	Price including tax.
ProductId	Int	False	Product ID.
ProductOption	String	False	This is an auto-generated column
ProductType	String	False	Product type.
QtyBackordered	String	False	Quantity backordered.
QtyCanceled	String	False	Quantity canceled.
QtyInvoiced	String	False	Quantity invoiced.
QtyOrdered	String	False	Quantity ordered.
QtyRefunded	String	False	Quantity refunded.
QtyShipped	String	False	Quantity shipped.
QuoteItemId	Int	False	Quote item ID.
RowInvoiced	String	False	Row invoiced.
RowTotal	String	False	Row total.
RowTotalInclTax	String	False	Row total including tax.
RowWeight	String	False	Row weight.
Sku	String	False	SKU.
StoreId	Int	False	Store ID.
TaxAmount	String	False	Tax amount.
TaxBeforeDiscount	String	False	Tax before discount.
TaxCanceled	String	False	Tax canceled.
TaxInvoiced	String	False	Tax invoiced.
TaxPercent	String	False	Tax percent.
TaxRefunded	String	False	Tax refunded.
UpdatedAt	Datetime	False	Updated-at timestamp.
WeeeTaxApplied	String	False	WEEE tax applied.
WeeeTaxAppliedAmount	String	False	WEEE tax applied amount.
WeeeTaxAppliedRowAmount	String	False	WEEE tax applied row amount.
WeeeTaxDisposition	String	False	WEEE tax disposition.
WeeeTaxRowDisposition	String	False	WEEE tax row disposition.
Weight	String	False	Weight.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.


Name	Type	Description
Id_p	Int	The order item ID.

Orders

Orders is an auto generated table

Select

The Orders table supports server-side processing for all columns in the WHERE clause. Most of these columns support server-side processing for the >, >=, <, <=, =, IN, NOT IN and LIKE operators.

The following examples show the types of queries processed server side.

Retrieve all orders from your Magento server:
```
SELECT * FROM Orders
```

Retrieve a single order:

SELECT * FROM Orders WHERE EntityId = '1'

Filter by status:
```
SELECT * FROM Orders WHERE GroupId = 1
```

Filter by created date:

SELECT * FROM Orders WHERE CreatedAt > '2017-08-01'

Example query with multiple filters:

SELECT * FROM Orders WHERE EntityId IN (1,2) AND CustomerFirstname LIKE '%i%' AND OrderCurrencyCode='USD' LIMIT 50

Insert

You can insert items into an order using an Insert statement. The 'Items' column can be specified using either the temporary table called 'OrderItems#Temp' or as a simple string aggregate. 'Payment' aggregate column is required for Insert and Update operations on this table.

Using the temporary table:

INSERT INTO OrderItems#Temp (BasePrice, Sku) VALUES (299.99, '8915A40K4')

INSERT INTO OrderItems#Temp (BasePrice, Sku) VALUES (100, '7115AP0Z8')

INSERT INTO Orders (GrandTotal, Items, Payment) VALUES (399.99, OrderItems#Temp, '{...your payment aggregate here...}')

Using a text aggregate:

INSERT INTO Orders (GrandTotal, Items, Payment) VALUES (399.99, '{...Items aggregate here...}', '{...Payment aggregate here...}')

You can turn off client-side execution by setting SupportEnhancedSQL to false, in which case any search criteria that refers to any other columns or operators will cause an error.

Columns


Name	Type	ReadOnly	Description
AdjustmentNegative	String	False	Negative adjustment value.
AdjustmentPositive	String	False	Positive adjustment value.
AppliedRuleIds	String	False	Applied rule IDs.
BaseAdjustmentNegative	String	False	Base negative adjustment value.
BaseAdjustmentPositive	String	False	Base positive adjustment value.
BaseCurrencyCode	String	False	Base currency code.
BaseDiscountAmount	String	False	Base discount amount.
BaseDiscountCanceled	String	False	Base discount canceled.
BaseDiscountInvoiced	String	False	Base discount invoiced.
BaseDiscountRefunded	String	False	Base discount refunded.
BaseDiscountTaxCompensationAmount	String	False	Base discount tax compensation amount.
BaseDiscountTaxCompensationInvoiced	String	False	Base discount tax compensation invoiced.
BaseDiscountTaxCompensationRefunded	String	False	Base discount tax compensation refunded.
BaseGrandTotal	String	False	Base grand total.
BaseShippingAmount	String	False	Base shipping amount.
BaseShippingCanceled	String	False	Base shipping canceled.
BaseShippingDiscountAmount	String	False	Base shipping discount amount.
BaseShippingDiscountTaxCompensationAmnt	String	False	Base shipping discount tax compensation amount.
BaseShippingInclTax	String	False	Base shipping including tax.
BaseShippingInvoiced	String	False	Base shipping invoiced.
BaseShippingRefunded	String	False	Base shipping refunded.
BaseShippingTaxAmount	String	False	Base shipping tax amount.
BaseShippingTaxRefunded	String	False	Base shipping tax refunded.
BaseSubtotal	String	False	Base subtotal.
BaseSubtotalCanceled	String	False	Base subtotal canceled.
BaseSubtotalInclTax	String	False	Base subtotal including tax.
BaseSubtotalInvoiced	String	False	Base subtotal invoiced.
BaseSubtotalRefunded	String	False	Base subtotal refunded.
BaseTaxAmount	String	False	Base tax amount.
BaseTaxCanceled	String	False	Base tax canceled.
BaseTaxInvoiced	String	False	Base tax invoiced.
BaseTaxRefunded	String	False	Base tax refunded.
BaseToGlobalRate	String	False	Base-to-global rate.
BaseToOrderRate	String	False	Base-to-order rate.
BaseTotalCanceled	String	False	Base total canceled.
BaseTotalDue	String	False	Base total due.
BaseTotalInvoiced	String	False	Base total invoiced.
BaseTotalInvoicedCost	String	False	Base total invoiced cost.
BaseTotalOfflineRefunded	String	False	Base total offline refunded.
BaseTotalOnlineRefunded	String	False	Base total online refunded.
BaseTotalPaid	String	False	Base total paid.
BaseTotalQtyOrdered	String	False	Base total quantity ordered.
BaseTotalRefunded	String	False	Base total refunded.
BillingAddress	String	False	This is an auto-generated column
BillingAddressId	Int	False	Billing address ID.
CanShipPartially	Int	False	Can-ship-partially flag value.
CanShipPartiallyItem	Int	False	Can-ship-partially-item flag value.
CouponCode	String	False	Coupon code.
CreatedAt	Datetime	False	Created-at timestamp.
CustomerDob	String	False	Customer date-of-birth (DOB).
CustomerEmail	String	False	Customer email address.
CustomerFirstname	String	False	Customer first name.
CustomerGender	Int	False	Customer gender.
CustomerGroupId	Int	False	Customer group ID.
CustomerId	Int	False	Customer ID.
CustomerIsGuest	Int	False	Customer-is-guest flag value.
CustomerLastname	String	False	Customer last name.
CustomerMiddlename	String	False	Customer middle name.
CustomerNote	String	False	Customer note.
CustomerNoteNotify	Int	False	Customer-note-notify flag value.
CustomerPrefix	String	False	Customer prefix.
CustomerSuffix	String	False	Customer suffix.
CustomerTaxvat	String	False	Customer value-added tax (VAT).
DiscountAmount	String	False	Discount amount.
DiscountCanceled	String	False	Discount canceled.
DiscountDescription	String	False	Discount description.
DiscountInvoiced	String	False	Discount invoiced.
DiscountRefunded	String	False	Discount refunded amount.
DiscountTaxCompensationAmount	String	False	Discount tax compensation amount.
DiscountTaxCompensationInvoiced	String	False	Discount tax compensation invoiced amount.
DiscountTaxCompensationRefunded	String	False	Discount tax compensation refunded amount.
EditIncrement	Int	False	Edit increment value.
EmailSent	Int	False	Email-sent flag value.
EntityId	Int	False	Order ID.
ExtCustomerId	String	False	External customer ID.
ExtensionAttributes	String	False	This is an auto-generated column
ExtOrderId	String	False	External order ID.
ForcedShipmentWithInvoice	Int	False	Forced-shipment-with-invoice flag value.
GlobalCurrencyCode	String	False	Global currency code.
GrandTotal	String	False	Grand total.
HoldBeforeState	String	False	Hold before state.
HoldBeforeStatus	String	False	Hold before status.
IncrementId	String	False	Increment ID.
IsVirtual	Int	False	Is-virtual flag value.
OrderCurrencyCode	String	False	Order currency code.
OriginalIncrementId	String	False	Original increment ID.
Payment	String	False	This is an auto-generated column
PaymentAuthExpiration	Int	False	Payment authorization expiration date.
PaymentAuthorizationAmount	String	False	Payment authorization amount.
ProtectCode	String	False	Protect code.
QuoteAddressId	Int	False	Quote address ID.
QuoteId	Int	False	Quote ID.
RelationChildId	String	False	Relation child ID.
RelationChildRealId	String	False	Relation child real ID.
RelationParentId	String	False	Relation parent ID.
RelationParentRealId	String	False	Relation parent real ID.
RemoteIp	String	False	Remote IP address.
ShippingAmount	String	False	Shipping amount.
ShippingCanceled	String	False	Shipping canceled amount.
ShippingDescription	String	False	Shipping description.
ShippingDiscountAmount	String	False	Shipping discount amount.
ShippingDiscountTaxCompensationAmount	String	False	Shipping discount tax compensation amount.
ShippingInclTax	String	False	Shipping including tax amount.
ShippingInvoiced	String	False	Shipping invoiced amount.
ShippingRefunded	String	False	Shipping refunded amount.
ShippingTaxAmount	String	False	Shipping tax amount.
ShippingTaxRefunded	String	False	Shipping tax refunded amount.
State	String	False	State.
Status	String	False	Status.
StatusHistories	String	False	Array of status histories.
StoreCurrencyCode	String	False	Store currency code.
StoreId	Int	False	Store ID.
StoreName	String	False	Store name.
StoreToBaseRate	String	False	Store-to-base rate.
StoreToOrderRate	String	False	Store-to-order rate.
Subtotal	String	False	Subtotal.
SubtotalCanceled	String	False	Subtotal canceled amount.
SubtotalInclTax	String	False	Subtotal including tax amount.
SubtotalInvoiced	String	False	Subtotal invoiced amount.
SubtotalRefunded	String	False	Subtotal refunded amount.
TaxAmount	String	False	Tax amount.
TaxCanceled	String	False	Tax canceled amount.
TaxInvoiced	String	False	Tax invoiced amount.
TaxRefunded	String	False	Tax refunded amount.
TotalCanceled	String	False	Total canceled.
TotalDue	String	False	Total due.
TotalInvoiced	String	False	Total invoiced amount.
TotalItemCount	Int	False	Total item count.
TotalOfflineRefunded	String	False	Total offline refunded amount.
TotalOnlineRefunded	String	False	Total online refunded amount.
TotalPaid	String	False	Total paid.
TotalQtyOrdered	String	False	Total quantity ordered.
TotalRefunded	String	False	Total amount refunded.
UpdatedAt	Datetime	False	Updated-at timestamp.
Weight	String	False	Weight.
XForwardedFor	String	False	X-Forwarded-For field value.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.


Name	Type	Description
Id_p	Int	The order ID.

PaymentMethods

PaymentMethods is an auto generated table

Columns


Name	Type	ReadOnly	Description
Code [KEY]	String	False	Payment method code
Title	String	False	Payment method title

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.


Name	Type	Description
CartId_p	Int	The cart ID.

PaymentSelectedMethods

PaymentSelectedMethods is an auto generated table

Columns


Name	Type	ReadOnly	Description
AdditionalData	String	False	Payment additional details
ExtensionAttributes	String	False	This is an auto-generated column
Method [KEY]	String	False	Payment method code
PoNumber	String	False	Purchase order number

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.


Name	Type	Description
CartId_p	Int	The cart ID.

ProductAttributeGroup

ProductAttributeGroup is an auto generated table

Columns


Name	Type	ReadOnly	Description
AttributeGroupId [KEY]	String	False	Id
AttributeGroupName	String	False	Name
AttributeSetId	Int	False	Attribute set id
ExtensionAttributes	String	False	This is an auto-generated column

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.


Name	Type	Description
GroupId_p	Int	This is an auto-generated column
AttributeSetId_p	String	This is an auto-generated column

ProductAttributeOptions

ProductAttributeOptions is an auto generated table

Columns


Name	Type	ReadOnly	Description
IsDefault	Bool	False	Default
Label [KEY]	String	False	Option label
SortOrder	Int	False	Option order
StoreLabels	String	False	Option label for store scopes
Value	String	False	Option value

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.


Name	Type	Description
AttributeCode_p	String	This is an auto-generated column

ProductAttributes

ProductAttributes is an auto generated table

Columns


Name	Type	ReadOnly	Description
ApplyTo	String	False	Apply to value for the element
AttributeCode	String	False	Code of the attribute.
AttributeId [KEY]	Int	False	Id of the attribute.
BackendModel	String	False	Backend model
BackendType	String	False	Backend type.
DefaultFrontendLabel	String	False	Frontend label for default store
DefaultValue	String	False	Default value for the element.
EntityTypeId	String	False	Entity type id
ExtensionAttributes	String	False	This is an auto-generated column
FrontendClass	String	False	Frontend class of attribute
FrontendInput	String	False	HTML for input element.
FrontendLabels	String	False	Frontend label for each store
IsComparable	String	False	The attribute can be compared on the frontend
IsFilterable	Bool	False	It used in layered navigation
IsFilterableInGrid	Bool	False	It is filterable in catalog product grid
IsFilterableInSearch	Bool	False	It is used in search results layered navigation
IsHtmlAllowedOnFront	Bool	False	The HTML tags are allowed on the frontend
IsRequired	Bool	False	Attribute is required.
IsSearchable	String	False	The attribute can be used in Quick Search
IsUnique	String	False	This is a unique attribute
IsUsedForPromoRules	String	False	The attribute can be used for promo rules
IsUsedInGrid	Bool	False	It is used in catalog product grid
IsUserDefined	Bool	False	Current attribute has been defined by a user.
IsVisible	Bool	False	Attribute is visible on frontend.
IsVisibleInAdvancedSearch	String	False	The attribute can be used in Advanced Search
IsVisibleInGrid	Bool	False	It is visible in catalog product grid
IsVisibleOnFront	String	False	The attribute is visible on the frontend
IsWysiwygEnabled	Bool	False	WYSIWYG flag
Note	String	False	The note attribute for the element.
Options	String	False	Options of the attribute (key => value pairs for select)
Position	Int	False	Position
Scope	String	False	Attribute scope
SourceModel	String	False	Source model
UsedForSortBy	Bool	False	It is used for sorting in product listing
UsedInProductListing	String	False	The attribute can be used in product listing
ValidationRules	String	False	Validation rules.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.


Name	Type	Description
AttributeSetId_p	String	This is an auto-generated column

ProductCategories

ProductCategories is an auto generated table

Select

The CategoryId_p column can be used to filter data within this table. The CategoryId_p can be used only with the equals (=) operator.

The driver will use the Magento API to filter the results by CategoryId_p while the rest of the filter is executed client side within the driver.

The following examples show the types of queries processed server side.

Retrieve all product categories:
```
SELECT * FROM ProductCategories
```
Retrieve detailed information about a single product category:
```
SELECT * FROM ProductCategories WHERE Id = 1
```

You can turn off client-side execution by setting SupportEnhancedSQL to false, in which case any search criteria that refers to any other columns will cause an error.

Insert

The Name and IsActive columns are required to create a new category.

INSERT INTO ProductCategories(Name, IsActive, Position) VALUES ('New category', '1', '0')

Update

To update a category, you must set the Id column.

UPDATE ProductCategories SET Name = 'New category', IsActive = 1 WHERE Id = 1

Delete

Product categories can be deleted by providing the Id column.

DELETE FROM ProductCategories WHERE Id = 1

Columns


Name	Type	ReadOnly	Description
Id [KEY]	Int	False	This is an auto-generated column
AvailableSortBy	String	False	This is an auto-generated column
Children	String	False	This is an auto-generated column
CreatedAt	Datetime	False	This is an auto-generated column
ExtensionAttributes	String	False	This is an auto-generated column
IncludeInMenu	Bool	False	This is an auto-generated column
IsActive	Bool	False	Whether category is active
Level	Int	False	Category level
Name	String	False	Category name
ParentId	Int	False	Parent category ID
Path	String	False	This is an auto-generated column
Position	Int	False	Category position
UpdatedAt	Datetime	False	This is an auto-generated column
ChildrenData	String	False	This is an auto-generated column
ProductCount	Int	False	Product count

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.


Name	Type	Description
CategoryId_p	Int	This is an auto-generated column
Id_p	String	This is an auto-generated column

ProductCustomOption

ProductCustomOption is an auto generated table

Columns


Name	Type	ReadOnly	Description
ExtensionAttributes	String	False	This is an auto-generated column
FileExtension	String	False	This is an auto-generated column
ImageSizeX	Int	False	This is an auto-generated column
ImageSizeY	Int	False	This is an auto-generated column
IsRequire	Bool	False	Is require
MaxCharacters	Int	False	This is an auto-generated column
OptionId [KEY]	Int	False	Option id
Price	String	False	Price
PriceType	String	False	Price type
ProductSku	String	False	Product SKU
Sku	String	False	Sku
SortOrder	Int	False	Sort order
Title	String	False	Option title
Type	String	False	Option type
Values	String	False	This is an auto-generated column

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.


Name	Type	Description
OptionId_p	String	This is an auto-generated column
Sku_p	String	This is an auto-generated column

ProductMedia

ProductMedia is an auto generated table

Select

You must specify the Sku_p column in the WHERE clause to retrieve results from ProductMedia. Sku_p can be used only with the equals (=) operator.

The driver will use the Magento API to filter the results by Sku_p while the rest of the filter is executed client side within the driver. For example, the following query is processed server side:

SELECT * FROM [ProductMedia] WHERE Sku = 'sjl-8082'

You can turn off client-side execution by setting SupportEnhancedSQL to false, in which case any search criteria that refers to any other columns will cause an error.

Update

To update a ProductMedia record, you must set Sku and EntryId (refers to the Id column).

UPDATE ProductMedia SET Label = 'lbl new' WHERE EntryId = '1' AND Sku = 'sjl-8082'

Delete

Product media can be deleted by providing Sku_p and EntryId_p (refers to the Id column).

DELETE FROM ProductMedia WHERE EntryId = 1 AND Sky = 'sjl-8082'

Columns


Name	Type	ReadOnly	Description
Id [KEY]	Int	False	Gallery entry ID
Content	String	False	This is an auto-generated column
Disabled	Bool	False	If gallery entry is hidden from product page
ExtensionAttributes	String	False	This is an auto-generated column
File	String	False	File path
Label	String	False	Gallery entry alternative text
MediaType	String	False	Media type
Position	Int	False	Gallery entry position (sort order)
Types	String	False	Gallery entry image types (thumbnail, image, small_image etc)

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.


Name	Type	Description
Sku_p	String	This is an auto-generated column
EntryId_p	Int	This is an auto-generated column

Products

Products is an auto generated table

Select

Custom Attributes will be dynamically retrieved as columns when querying this table. The Products table supports server-side processing for all columns in the WHERE clause. Most of these columns support server-side processing for the >, >=, <, <=, =, IN, NOT IN and LIKE operators.

The driver will use the Magento API to filter the results with these operators while the rest of the filter is executed client side within the provider. You can turn off client-side execution by setting SupportEnhancedSQL to false in which case any search criteria that invokes other operators will cause an error.

The following examples show the types of queries processed server side.

Retrieve all products from your Magento server:
```
SELECT * FROM Products
```

Retrieve a single product:

SELECT * FROM Products WHERE Sku = 'sjl-8082'

Filter by status:
```
SELECT * FROM Products WHERE Status = 1
```

Filter by created date:

SELECT * FROM Products WHERE CreatedAt > '2017-08-01'

Example query with multiple filters:

SELECT * FROM Products WHERE Id IN (250,260) AND Name LIKE '%d%' AND Visibility = 4 LIMIT 50

You can turn off client-side execution by setting SupportEnhancedSQL to false, in which case any search criteria that refers to any other columns will cause an error.

Insert

To create a new product, Name, AttributeSetId, and Sku are required.

INSERT INTO Products(Sku, AttributeSetId, Name) VALUES ('sjl-1999', '4', 'insert prod')

Update

To update a product, you must set the Sku and Price columns.

UPDATE Products SET Price = '88', CustomAttribute1='ValidValue' WHERE Sku = 'sjl-1999'

Delete

Products can be deleted by providing the Sku column.

DELETE FROM Products WHERE Sku = 'sjl-1999'

Columns


Name	Type	ReadOnly	Description
Id	Int	False	Id
AttributeSetId	Int	False	Attribute set id
CreatedAt	Datetime	False	Created date
ExtensionAttributes	String	False	This is an auto-generated column
MediaGalleryEntries	String	False	Media gallery entries
Name	String	False	Name
Options	String	False	List of product options
Price	String	False	Price
ProductLinks	String	False	Product links info
Sku [KEY]	String	False	Sku
Status	Int	False	Status
TierPrices	String	False	List of product tier prices
TypeId	String	False	Type id
UpdatedAt	Datetime	False	Updated date
Visibility	Int	False	Visibility
Weight	String	False	Weight
CustomAttribute1	String	False	A custom attribute created for the Products. The custom fields will be listed dynamically.
CustomAttribute2	String	False	A custom attribute created for the Products. The custom fields will be listed dynamically.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.


Name	Type	Description
Sku_p	String	This is an auto-generated column

QuoteCart

QuoteCart is an auto generated table

Columns


Name	Type	ReadOnly	Description
Id [KEY]	Int	False	Cart/quote ID.
BillingAddress	String	False	This is an auto-generated column
ConvertedAt	String	False	Cart conversion date and time. Otherwise, null.
CreatedAt	Datetime	False	Cart creation date and time. Otherwise, null.
Currency	String	False	This is an auto-generated column
Customer	String	False	This is an auto-generated column
CustomerIsGuest	Bool	False	For guest customers, false for logged in customers
CustomerNote	String	False	Notice text
CustomerNoteNotify	Bool	False	Customer notification flag
CustomerTaxClassId	Int	False	Customer tax class ID.
ExtensionAttributes	String	False	This is an auto-generated column
IsActive	Bool	False	Active status flag value. Otherwise, null.
IsVirtual	Bool	False	Virtual flag value. Otherwise, null.
ItemsCount	Int	False	Number of different items or products in the cart. Otherwise, null.
ItemsQty	String	False	Total quantity of all cart items. Otherwise, null.
OrigOrderId	Int	False	Original order ID. Otherwise, null.
ReservedOrderId	Int	False	Reserved order ID. Otherwise, null.
StoreId	Int	False	Store identifier
UpdatedAt	Datetime	False	Cart last update date and time. Otherwise, null.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.


Name	Type	Description
CartId_p	Int	This is an auto-generated column

QuoteCartItem

QuoteCartItem is an auto generated table

Columns


Name	Type	ReadOnly	Description
ExtensionAttributes	String	False	This is an auto-generated column
ItemId	Int	False	Item ID. Otherwise, null.
Name	String	False	Product name. Otherwise, null.
Price	String	False	Product price. Otherwise, null.
ProductOption	String	False	This is an auto-generated column
ProductType	String	False	Product type. Otherwise, null.
Qty	String	False	Product quantity.
QuoteId [KEY]	String	False	Quote id.
Sku	String	False	Product SKU. Otherwise, null.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.


Name	Type	Description
ItemId_p	String	This is an auto-generated column
CartId_p	Int	The cart ID.
QuoteId_p	String	This is an auto-generated column

QuoteCartTotal

QuoteCartTotal is an auto generated table

Columns


Name	Type	ReadOnly	Description
BaseCurrencyCode	String	False	Base currency code
BaseDiscountAmount	String	False	Discount amount in base currency
BaseGrandTotal	String	False	Grand total in base currency
BaseShippingAmount	String	False	Shipping amount in base currency
BaseShippingDiscountAmount	String	False	Shipping discount amount in base currency
BaseShippingInclTax	String	False	Shipping including tax in base currency
BaseShippingTaxAmount	String	False	Shipping tax amount in base currency
BaseSubtotal	String	False	Subtotal in base currency
BaseSubtotalInclTax	String	False	Subtotal including tax in base currency
BaseSubtotalWithDiscount	String	False	Subtotal in base currency with applied discount
BaseTaxAmount	String	False	Tax amount in base currency
CouponCode	String	False	Applied coupon code
DiscountAmount	String	False	Discount amount in quote currency
ExtensionAttributes	String	False	This is an auto-generated column
GrandTotal	String	False	Grand total in quote currency
ItemsQty	Int	False	Items qty
QuoteCurrencyCode	String	False	Quote currency code
ShippingAmount	String	False	Shipping amount in quote currency
ShippingDiscountAmount	String	False	Shipping discount amount in quote currency
ShippingInclTax	String	False	Shipping including tax in quote currency
ShippingTaxAmount	String	False	Shipping tax amount in quote currency
Subtotal	String	False	Subtotal in quote currency
SubtotalInclTax	String	False	Subtotal including tax in quote currency
SubtotalWithDiscount	String	False	Subtotal in quote currency with applied discount
TaxAmount	String	False	Tax amount in quote currency
TotalSegments	String	False	Dynamically calculated totals
WeeeTaxAppliedAmount	String	False	Item weee tax applied amount in quote currency.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.


Name	Type	Description
CartId_p	Int	The cart ID.

QuoteGuestCart

QuoteGuestCart is an auto generated table

Columns


Name	Type	ReadOnly	Description
Id [KEY]	Int	False	Cart/quote ID.
BillingAddress	String	False	This is an auto-generated column
ConvertedAt	String	False	Cart conversion date and time. Otherwise, null.
CreatedAt	Datetime	False	Cart creation date and time. Otherwise, null.
Currency	String	False	This is an auto-generated column
Customer	String	False	This is an auto-generated column
CustomerIsGuest	Bool	False	For guest customers, false for logged in customers
CustomerNote	String	False	Notice text
CustomerNoteNotify	Bool	False	Customer notification flag
CustomerTaxClassId	Int	False	Customer tax class ID.
ExtensionAttributes	String	False	This is an auto-generated column
IsActive	Bool	False	Active status flag value. Otherwise, null.
IsVirtual	Bool	False	Virtual flag value. Otherwise, null.
ItemsCount	Int	False	Number of different items or products in the cart. Otherwise, null.
ItemsQty	String	False	Total quantity of all cart items. Otherwise, null.
OrigOrderId	Int	False	Original order ID. Otherwise, null.
ReservedOrderId	Int	False	Reserved order ID. Otherwise, null.
StoreId	Int	False	Store identifier
UpdatedAt	Datetime	False	Cart last update date and time. Otherwise, null.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.


Name	Type	Description
CartId_p	String	This is an auto-generated column

QuoteGuestCartItem

QuoteGuestCartItem is an auto generated table

Columns


Name	Type	ReadOnly	Description
ExtensionAttributes	String	False	This is an auto-generated column
ItemId	Int	False	Item ID. Otherwise, null.
Name	String	False	Product name. Otherwise, null.
Price	String	False	Product price. Otherwise, null.
ProductOption	String	False	This is an auto-generated column
ProductType	String	False	Product type. Otherwise, null.
Qty	String	False	Product quantity.
QuoteId [KEY]	String	False	Quote id.
Sku	String	False	Product SKU. Otherwise, null.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.


Name	Type	Description
CartId_p	String	This is an auto-generated column
ItemId_p	String	This is an auto-generated column

QuoteGuestCartTotal

QuoteGuestCartTotal is an auto generated table

Columns


Name	Type	ReadOnly	Description
BaseCurrencyCode	String	False	Base currency code
BaseDiscountAmount	String	False	Discount amount in base currency
BaseGrandTotal	String	False	Grand total in base currency
BaseShippingAmount	String	False	Shipping amount in base currency
BaseShippingDiscountAmount	String	False	Shipping discount amount in base currency
BaseShippingInclTax	String	False	Shipping including tax in base currency
BaseShippingTaxAmount	String	False	Shipping tax amount in base currency
BaseSubtotal	String	False	Subtotal in base currency
BaseSubtotalInclTax	String	False	Subtotal including tax in base currency
BaseSubtotalWithDiscount	String	False	Subtotal in base currency with applied discount
BaseTaxAmount	String	False	Tax amount in base currency
CouponCode	String	False	Applied coupon code
DiscountAmount	String	False	Discount amount in quote currency
ExtensionAttributes	String	False	This is an auto-generated column
GrandTotal	String	False	Grand total in quote currency
ItemsQty	Int	False	Items qty
QuoteCurrencyCode	String	False	Quote currency code
ShippingAmount	String	False	Shipping amount in quote currency
ShippingDiscountAmount	String	False	Shipping discount amount in quote currency
ShippingInclTax	String	False	Shipping including tax in quote currency
ShippingTaxAmount	String	False	Shipping tax amount in quote currency
Subtotal	String	False	Subtotal in quote currency
SubtotalInclTax	String	False	Subtotal including tax in quote currency
SubtotalWithDiscount	String	False	Subtotal in quote currency with applied discount
TaxAmount	String	False	Tax amount in quote currency
TotalSegments	String	False	Dynamically calculated totals
WeeeTaxAppliedAmount	String	False	Item weee tax applied amount in quote currency.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.


Name	Type	Description
CartId_p	String	The cart ID.

SalesCreditmemo

SalesCreditmemo is an auto generated table

Columns


Name	Type	ReadOnly	Description
Adjustment	String	False	Credit memo adjustment.
AdjustmentNegative	String	False	Credit memo negative adjustment.
AdjustmentPositive	String	False	Credit memo positive adjustment.
BaseAdjustment	String	False	Credit memo base adjustment.
BaseAdjustmentNegative	String	False	Credit memo negative base adjustment.
BaseAdjustmentPositive	String	False	Credit memo positive base adjustment.
BaseCurrencyCode	String	False	Credit memo base currency code.
BaseDiscountAmount	String	False	Credit memo base discount amount.
BaseDiscountTaxCompensationAmount	String	False	Credit memo base discount tax compensation amount.
BaseGrandTotal	String	False	Credit memo base grand total.
BaseShippingAmount	String	False	Credit memo base shipping amount.
BaseShippingDiscountTaxCompensationAmnt	String	False	Credit memo base shipping discount tax compensation amount.
BaseShippingInclTax	String	False	Credit memo base shipping including tax.
BaseShippingTaxAmount	String	False	Credit memo base shipping tax amount.
BaseSubtotal	String	False	Credit memo base subtotal.
BaseSubtotalInclTax	String	False	Credit memo base subtotal including tax.
BaseTaxAmount	String	False	Credit memo base tax amount.
BaseToGlobalRate	String	False	Credit memo base-to-global rate.
BaseToOrderRate	String	False	Credit memo base-to-order rate.
BillingAddressId	Int	False	Credit memo billing address ID.
Comments	String	False	Array of any credit memo comments. Otherwise, null.
CreatedAt	Datetime	False	Credit memo created-at timestamp.
CreditmemoStatus	Int	False	Credit memo status.
DiscountAmount	String	False	Credit memo discount amount.
DiscountDescription	String	False	Credit memo discount description.
DiscountTaxCompensationAmount	String	False	Credit memo discount tax compensation amount.
EmailSent	Int	False	Credit memo email sent flag value.
EntityId [KEY]	Int	False	Credit memo ID.
ExtensionAttributes	String	False	This is an auto-generated column
GlobalCurrencyCode	String	False	Credit memo global currency code.
GrandTotal	String	False	Credit memo grand total.
IncrementId	String	False	Credit memo increment ID.
InvoiceId	Int	False	Credit memo invoice ID.
OrderCurrencyCode	String	False	Credit memo order currency code.
OrderId	Int	False	Credit memo order ID.
ShippingAddressId	Int	False	Credit memo shipping address ID.
ShippingAmount	String	False	Credit memo shipping amount.
ShippingDiscountTaxCompensationAmount	String	False	Credit memo shipping discount tax compensation amount.
ShippingInclTax	String	False	Credit memo shipping including tax.
ShippingTaxAmount	String	False	Credit memo shipping tax amount.
State	Int	False	Credit memo state.
StoreCurrencyCode	String	False	Credit memo store currency code.
StoreId	Int	False	Credit memo store ID.
StoreToBaseRate	String	False	Credit memo store-to-base rate.
StoreToOrderRate	String	False	Credit memo store-to-order rate.
Subtotal	String	False	Credit memo subtotal.
SubtotalInclTax	String	False	Credit memo subtotal including tax.
TaxAmount	String	False	Credit memo tax amount.
TransactionId	String	False	Credit memo transaction ID.
UpdatedAt	Datetime	False	Credit memo updated-at timestamp.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.


Name	Type	Description
Id_p	Int	The credit memo ID.

SalesCreditmemoComments

SalesCreditmemoComments is an auto generated table

Columns


Name	Type	ReadOnly	Description
Comment	String	False	Comment.
CreatedAt	Datetime	False	Created-at timestamp.
EntityId [KEY]	Int	False	Credit memo ID.
ExtensionAttributes	String	False	This is an auto-generated column
IsCustomerNotified	Int	False	Is-customer-notified flag value.
IsVisibleOnFront	Int	False	Is-visible-on-storefront flag value.
ParentId	Int	False	Parent ID.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.


Name	Type	Description
Id_p	Int	The credit memo ID.

SalesInvoice

SalesInvoice is an auto generated table

Columns


Name	Type	ReadOnly	Description
BaseCurrencyCode	String	False	Base currency code.
BaseDiscountAmount	String	False	Base discount amount.
BaseDiscountTaxCompensationAmount	String	False	Base discount tax compensation amount.
BaseGrandTotal	String	False	Base grand total.
BaseShippingAmount	String	False	Base shipping amount.
BaseShippingDiscountTaxCompensationAmnt	String	False	Base shipping discount tax compensation amount.
BaseShippingInclTax	String	False	Base shipping including tax.
BaseShippingTaxAmount	String	False	Base shipping tax amount.
BaseSubtotal	String	False	Base subtotal.
BaseSubtotalInclTax	String	False	Base subtotal including tax.
BaseTaxAmount	String	False	Base tax amount.
BaseToGlobalRate	String	False	Base-to-global rate.
BaseToOrderRate	String	False	Base-to-order rate.
BaseTotalRefunded	String	False	Base total refunded.
BillingAddressId	Int	False	Billing address ID.
CanVoidFlag	Int	False	Can void flag value.
Comments	String	False	Array of any invoice comments. Otherwise, null.
CreatedAt	Datetime	False	Created-at timestamp.
DiscountAmount	String	False	Discount amount.
DiscountDescription	String	False	Discount description.
DiscountTaxCompensationAmount	String	False	Discount tax compensation amount.
EmailSent	Int	False	Email-sent flag value.
EntityId [KEY]	Int	False	Invoice ID.
ExtensionAttributes	String	False	This is an auto-generated column
GlobalCurrencyCode	String	False	Global currency code.
GrandTotal	String	False	Grand total.
IncrementId	String	False	Increment ID.
IsUsedForRefund	Int	False	Is-used-for-refund flag value.
OrderCurrencyCode	String	False	Order currency code.
OrderId	Int	False	Order ID.
ShippingAddressId	Int	False	Shipping address ID.
ShippingAmount	String	False	Shipping amount.
ShippingDiscountTaxCompensationAmount	String	False	Shipping discount tax compensation amount.
ShippingInclTax	String	False	Shipping including tax.
ShippingTaxAmount	String	False	Shipping tax amount.
State	Int	False	State.
StoreCurrencyCode	String	False	Store currency code.
StoreId	Int	False	Store ID.
StoreToBaseRate	String	False	Store-to-base rate.
StoreToOrderRate	String	False	Store-to-order rate.
Subtotal	String	False	Subtotal.
SubtotalInclTax	String	False	Subtotal including tax.
TaxAmount	String	False	Tax amount.
TotalQty	String	False	Total quantity.
TransactionId	String	False	Transaction ID.
UpdatedAt	Datetime	False	Updated-at timestamp.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.


Name	Type	Description
Id_p	Int	The invoice ID.

SalesInvoiceComments

SalesInvoiceComments is an auto generated table

Columns


Name	Type	ReadOnly	Description
Comment	String	False	Comment.
CreatedAt	Datetime	False	Created-at timestamp.
EntityId [KEY]	Int	False	Invoice ID.
ExtensionAttributes	String	False	This is an auto-generated column
IsCustomerNotified	Int	False	Is-customer-notified flag value.
IsVisibleOnFront	Int	False	Is-visible-on-storefront flag value.
ParentId	Int	False	Parent ID.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.


Name	Type	Description
Id_p	Int	The invoice ID.

SalesRule

SalesRule is an auto generated table

Columns


Name	Type	ReadOnly	Description
ActionCondition	String	False	This is an auto-generated column
ApplyToShipping	Bool	False	The rule applies to shipping
Condition	String	False	This is an auto-generated column
CouponType	String	False	Coupon type
CustomerGroupIds	String	False	Ids of customer groups that the rule applies to
Description	String	False	Description
DiscountAmount	String	False	Discount amount
DiscountQty	String	False	Maximum qty discount is applied
DiscountStep	Int	False	Discount step
ExtensionAttributes	String	False	This is an auto-generated column
FromDate	String	False	The start date when the coupon is active
IsActive	Bool	False	The coupon is active
IsAdvanced	Bool	False	Is this field needed
IsRss	Bool	False	Whether the rule is in RSS
Name	String	False	Rule name
ProductIds	String	False	Product ids
RuleId [KEY]	Int	False	Rule id
SimpleAction	String	False	Simple action of the rule
SimpleFreeShipping	String	False	To grant free shipping
SortOrder	Int	False	Sort order
StopRulesProcessing	Bool	False	To stop rule processing
StoreLabels	String	False	Display label
TimesUsed	Int	False	How many times the rule has been used
ToDate	String	False	The end date when the coupon is active
UseAutoGeneration	Bool	False	To auto generate coupon
UsesPerCoupon	Int	False	Limit of uses per coupon
UsesPerCustomer	Int	False	Number of uses per customer
WebsiteIds	String	False	A list of websites the rule applies to

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.


Name	Type	Description
RuleId_p	Int	This is an auto-generated column

SalesRuleCoupon

SalesRuleCoupon is an auto generated table

Columns


Name	Type	ReadOnly	Description
Code	String	False	Coupon code
CouponId [KEY]	Int	False	Coupon id
CreatedAt	Datetime	False	When the coupon is created
ExpirationDate	String	False	Expiration date
ExtensionAttributes	String	False	This is an auto-generated column
IsPrimary	Bool	False	The coupon is primary coupon for the rule that it's associated with
RuleId	Int	False	The id of the rule associated with the coupon
TimesUsed	Int	False	The number of times the coupon has been used
Type	Int	False	Of coupon
UsageLimit	Int	False	Usage limit
UsagePerCustomer	Int	False	Usage limit per customer

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.


Name	Type	Description
CouponId_p	Int	This is an auto-generated column

SalesShipment

SalesShipment is an auto generated table

Columns


Name	Type	ReadOnly	Description
BillingAddressId	Int	False	Billing address ID.
Comments	String	False	Array of comments.
CreatedAt	Datetime	False	Created-at timestamp.
CustomerId	Int	False	Customer ID.
EmailSent	Int	False	Email-sent flag value.
EntityId [KEY]	Int	False	Shipment ID.
ExtensionAttributes	String	False	This is an auto-generated column
IncrementId	String	False	Increment ID.
OrderId	Int	False	Order ID.
Packages	String	False	Array of packages, if any. Otherwise, null.
ShipmentStatus	Int	False	Shipment status.
ShippingAddressId	Int	False	Shipping address ID.
ShippingLabel	String	False	Shipping label.
StoreId	Int	False	Store ID.
TotalQty	String	False	Total quantity.
TotalWeight	String	False	Total weight.
Tracks	String	False	Array of tracks.
UpdatedAt	Datetime	False	Updated-at timestamp.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.


Name	Type	Description
Id_p	Int	The shipment ID.

SalesShipmentComments

SalesShipmentComments is an auto generated table

Columns


Name	Type	ReadOnly	Description
Comment	String	False	Comment.
CreatedAt	Datetime	False	Created-at timestamp.
EntityId [KEY]	Int	False	Shipment comment ID.
ExtensionAttributes	String	False	This is an auto-generated column
IsCustomerNotified	Int	False	Is-customer-notified flag value.
IsVisibleOnFront	Int	False	Is-visible-on-storefront flag value.
ParentId	Int	False	Parent ID.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.


Name	Type	Description
Id_p	Int	The shipment ID.

SalesTransaction

SalesTransaction is an auto generated table

Columns


Name	Type	ReadOnly	Description
AdditionalInformation	String	False	Array of additional information. Otherwise, null.
ChildTransactions	String	False	Array of child transactions.
CreatedAt	Datetime	False	Created-at timestamp.
ExtensionAttributes	String	False	This is an auto-generated column
IsClosed	Int	False	Is-closed flag value.
OrderId	Int	False	Order ID.
ParentId	Int	False	The parent ID for the transaction. Otherwise, null.
ParentTxnId	String	False	Parent transaction business ID.
PaymentId	Int	False	Payment ID.
TransactionId [KEY]	Int	False	Transaction ID.
TxnId	String	False	Transaction business ID.
TxnType	String	False	Transaction type.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.


Name	Type	Description
Id_p	Int	The transaction ID.

ShippingMethods

ShippingMethods is an auto generated table

Columns


Name	Type	ReadOnly	Description
Amount	String	False	Shipping amount in store currency.
Available	Bool	False	The value of the availability flag for the current shipping method.
BaseAmount	String	False	Shipping amount in base currency.
CarrierCode	String	False	Shipping carrier code.
CarrierTitle	String	False	Shipping carrier title. Otherwise, null.
ErrorMessage	String	False	Shipping Error message.
ExtensionAttributes	String	False	This is an auto-generated column
MethodCode [KEY]	String	False	Shipping method code.
MethodTitle	String	False	Shipping method title. Otherwise, null.
PriceExclTax	String	False	Shipping price excl tax.
PriceInclTax	String	False	Shipping price incl tax.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.


Name	Type	Description
CartId_p	Int	The shopping cart ID.

StoreGroup

StoreGroup is an auto generated table

Columns


Name	Type	ReadOnly	Description
Id [KEY]	Int	False	This is an auto-generated column
DefaultStoreId	Int	False	This is an auto-generated column
ExtensionAttributes	String	False	This is an auto-generated column
Name	String	False	This is an auto-generated column
RootCategoryId	Int	False	This is an auto-generated column
WebsiteId	Int	False	This is an auto-generated column

Stores

Stores is an auto generated table

Columns


Name	Type	ReadOnly	Description
Id [KEY]	Int	False	This is an auto-generated column
Code	String	False	This is an auto-generated column
ExtensionAttributes	String	False	This is an auto-generated column
Name	String	False	Store name
StoreGroupId	Int	False	This is an auto-generated column
WebsiteId	Int	False	This is an auto-generated column

StoreWebsite

StoreWebsite is an auto generated table

Columns


Name	Type	ReadOnly	Description
Id [KEY]	Int	False	This is an auto-generated column
Code	String	False	This is an auto-generated column
DefaultGroupId	Int	False	This is an auto-generated column
ExtensionAttributes	String	False	This is an auto-generated column
Name	String	False	Website name

TaxClass

TaxClass is an auto generated table

Columns


Name	Type	ReadOnly	Description
ClassId [KEY]	Int	False	Tax class ID.
ClassName	String	False	Tax class name.
ClassType	String	False	Tax class type.
ExtensionAttributes	String	False	This is an auto-generated column

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.


Name	Type	Description
ClassId_p	String	This is an auto-generated column
TaxClassId_p	Int	This is an auto-generated column

TaxRate

TaxRate is an auto generated table

Columns


Name	Type	ReadOnly	Description
Id [KEY]	Int	False	Id
Code	String	False	Tax rate code
ExtensionAttributes	String	False	This is an auto-generated column
Rate	String	False	Tax rate in percentage
RegionName	String	False	Region name
TaxCountryId	String	False	Country id
TaxPostcode	String	False	Postcode
TaxRegionId	Int	False	Region id
Titles	String	False	Tax rate titles
ZipFrom	Int	False	Zip range from
ZipIsRange	Int	False	Zip is range
ZipTo	Int	False	Zip range to

TaxRule

TaxRule is an auto generated table

Columns


Name	Type	ReadOnly	Description
Id [KEY]	Int	False	Id
CalculateSubtotal	Bool	False	Calculate subtotal.
Code	String	False	Tax rule code
CustomerTaxClassIds	String	False	Customer tax class id
ExtensionAttributes	String	False	This is an auto-generated column
Position	Int	False	Sort order.
Priority	Int	False	Priority
ProductTaxClassIds	String	False	Product tax class id
TaxRateIds	String	False	Tax rate ids

Matillion ETL Data Model for MagentoVersion - 21.0.8137.0

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

See Also

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Java Logging

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Matillion ETL Data Model for Magento
Version - 21.0.8137.0