Skip to main content

SQL Server catalog

The SQL Server catalog connects Gravitino to a Microsoft SQL Server database over JDBC, exposing SQL Server schemas as schemas and SQL Server tables as tables. Schemas and tables are both read-write.

One catalog reaches exactly one SQL Server database, named by databaseName in the JDBC URL or by jdbc-database, so reaching a second database means creating a second catalog.

Quick start​

The example connects the SQL Server database {database} as the catalog sqlserver_catalog and lists its schemas. The Microsoft JDBC driver ships in the image, so there is no driver to supply.

  1. Start the catalog. Open Connect → Catalogs and click + Catalog. Enter sqlserver_catalog as the Catalog name, keep Relational as the Catalog type, and choose SQL Server under Databases and warehouses in Select provider.

  2. Enter the connection. Under Connection, enter jdbc:sqlserver://{host}:1433;databaseName={database};encrypt=true;trustServerCertificate=true as the URL, {database} as the Database, and the User and Password of the SQL Server login Gravitino connects as. The database in the URL and in Database must agree. The UI fills in jdbc-driver from the provider.

  3. Test and create it. Click Test connection, then Create catalog.

  4. List the schemas. In Explore, open sqlserver_catalog. Gravitino returns the SQL Server schemas in that database, with the system schemas filtered out.

Catalog properties​

The SQL Server catalog defines no properties of its own. It takes the jdbc-url, jdbc-driver, jdbc-user, jdbc-password, jdbc-database, and pool properties described in Shared properties.

The catalog needs a database. It takes it from jdbc-database or from databaseName in the JDBC URL, and creation fails when neither names one or when the two differ.

Connection encryption​

The bundled Microsoft driver encrypts connections by default. The encrypt and trustServerCertificate parameters in the URL control that behavior.

Use trustServerCertificate=true for development only, since it skips certificate validation entirely. In production, set trustServerCertificate=false and give the server a certificate from a CA the Gravitino server trusts. SQL Server 2008 supports only TLS 1.0, which the modern driver declines, so reaching it requires either encrypt=false or TLS configuration on the server.

Schemas​

A schema in this catalog is a SQL Server schema, such as dbo, inside the single database the catalog reaches. Create, list, load, and drop are supported.

Creation issues CREATE SCHEMA [{name}] AUTHORIZATION [dbo], so a new schema is always owned by dbo. Schema properties are rejected, and schema comments are unavailable because SQL Server has no schema comment syntax.

Drop has no cascade. SQL Server does not implement DROP SCHEMA ... CASCADE, so dropping a schema that still holds objects fails with SQL Server's own error rather than removing them.

Twelve names are reserved and cannot be used for a schema, and the same twelve are filtered out of schema listings: guest, information_schema, sys, and the nine fixed database roles from db_owner through db_denydatawriter.

Tables​

Table create, load, list, rename, alter, and drop are supported. Purge is not: SQL Server has no equivalent operation, so a purge request is rejected rather than being treated as a drop.

Listing excludes tables SQL Server ships itself, by filtering on is_ms_shipped, so internal tables such as spt_fallback_db do not appear as user metadata.

Rename goes through sp_rename rather than ALTER TABLE, for both tables and columns.

Table and column comments are stored as SQL Server extended properties under the name MS_Description, not as part of the table definition. Gravitino adds them with sp_addextendedproperty after the CREATE TABLE statement, and an update checks whether the property already exists so that it adds or updates as appropriate.

Partitioning and distribution are not supported. A create carrying partitioning is rejected, and a distribution other than none is rejected.

Properties​

Table properties are not supported. Creating a table with properties is rejected, and setting or removing a property on an existing table is rejected as well. Comment is the only reserved property entry the catalog recognizes.

Indexes​

Primary key and unique key are the only supported index types, and only at table creation. An index with a multi-part field name is rejected. A primary key created without a name is given PK_{table_name}.

Deleting an index through an alter is supported and issues DROP CONSTRAINT, since SQL Server backs both index types with constraints. Adding an index through an alter is not implemented and fails as an unsupported change, so an index that was not declared at creation cannot be added through Gravitino.

Auto increment​

An auto-increment column is generated as IDENTITY(1,1), so it starts at one and increments by one. The seed and increment are not configurable. The column type must be one that Gravitino permits for auto increment.

Supported changes​

ChangeSupported
Rename tableYes, through sp_rename
Update table commentYes
Add columnYes, at the default position only
Delete columnYes
Rename columnYes, through sp_rename
Update column typeYes
Update column nullableYes
Update column defaultYes
Update column commentYes
Delete indexYes
Add indexNo
Update column positionNo
Set or remove propertyNo

Nested column names are rejected by every column change.

Updating a column default drops the existing default constraint and adds a new one, because SQL Server attaches a default to a named constraint rather than to the column itself.

Identifier case and quoting​

Identifiers are validated against ^[a-zA-Z_\p{L}@#][\w\p{L}@#$]{0,127}$, giving a maximum of 128 characters. A name starts with a letter, an underscore, @, or #, and continues with letters, digits, underscores, @, #, or $. Letters from any script are accepted, not only ASCII.

Names are treated as case-insensitive, matching SQL Server's default collation behavior. A server configured with a case-sensitive collation is not accounted for.

Gravitino quotes identifiers with square brackets in generated SQL and doubles any ] inside a name.

Type mapping​

Gravitino reads each SQL Server type as the Gravitino type in its row, and creates each Gravitino type as the SQL Server type in its row, except where the list under the table says otherwise.

SQL Server typeGravitino type
tinyintByte
smallintShort
intInteger
bigintLong
bitBoolean
decimal(p,s)Decimal(p, s)
realFloat
floatDouble
dateDate
time(p)Time with precision p
datetime2(p)Timestamp without time zone, precision p
char(n)Char(n)
varchar(n)Varchar(n)
nvarcharString
binary(n)Fixed(n)
varbinaryBinary
uniqueidentifierUUID
varchar(max)External type varchar(max)
Any other typeExternal type carrying the SQL Server name

An external type keeps its parameters for numeric, nchar, and datetimeoffset, and is recorded by name alone for everything else.

When Gravitino creates a column:

  • Time and Timestamp without time zone without a precision become time and datetime2.
  • String becomes nvarchar(max) and Binary becomes varbinary(max).
  • An external type becomes the SQL Server type it carries.
  • Timestamp with a time zone has no mapping and is rejected, since datetime2 carries no zone. Use an external type of datetimeoffset when you need that behavior.

Column default values​

GETDATE() and CURRENT_TIMESTAMP are both recognized as the current timestamp default, and a current timestamp default is written back as GETDATE(). Any other function expression is written inside parentheses. A boolean default becomes 1 or 0 to match the bit type. An expression the converter cannot parse is preserved as an unparsed expression rather than being dropped.

Error mapping​

SQL Server error codes are translated into Gravitino exceptions so a caller sees the same failure types it would from any other catalog.

SQL Server errorGravitino exception
2714 object already existsTable already exists
15032 schema already existsSchema already exists
208 invalid object nameNo such table
3701 cannot drop, object does not existNo such schema or no such table, from the message
15151 cannot find schemaNo such schema
229 permission deniedForbidden
18456 login failedUnauthorized
4060 cannot open databaseNo such schema

SQL Server reports a duplicate table and a duplicate schema with overlapping codes, so schema creation catches the table-level result and re-raises it as a schema conflict. Any unrecognized code produces a generic runtime exception carrying the SQL Server message.

Last updated on