Database Objects

This module contains classes for representing various database objects.

Table

class sqlmeta.objects.table.Table(name: str, columns: List[SqlColumn] = None, schema: str | None = None, constraints: List[SqlConstraint] = None, temporary: bool = False, tablespace: str | None = None, dialect: str | None = None, comment: str | None = None, storage_engine: str | None = None, partitions: List | None = None, filegroup: str | None = None, memory_optimized: bool = False, system_versioned: bool = False, history_table: str | None = None, history_schema: str | None = None, derived_from: str | None = None)

Bases: SqlObject

Represents a database table.

__init__(name: str, columns: List[SqlColumn] = None, schema: str | None = None, constraints: List[SqlConstraint] = None, temporary: bool = False, tablespace: str | None = None, dialect: str | None = None, comment: str | None = None, storage_engine: str | None = None, partitions: List | None = None, filegroup: str | None = None, memory_optimized: bool = False, system_versioned: bool = False, history_table: str | None = None, history_schema: str | None = None, derived_from: str | None = None)

Initialize a table.

Parameters:

• name – Table name

• columns – List of columns

• schema – Schema name (optional)

• constraints – List of constraints

• temporary – Whether the table is temporary

• tablespace – Tablespace name (optional)

• dialect – SQL dialect

• comment – Table comment/description (optional)

• storage_engine – Storage engine (MySQL: InnoDB, MyISAM, etc.)

• partitions – List of Partition objects (optional)

• filegroup – Filegroup name (SQL Server T-SQL grammar-based)

• memory_optimized – Whether table is memory-optimized (SQL Server T-SQL grammar-based)

• system_versioned – Whether table is system-versioned temporal table (SQL Server T-SQL grammar-based)

• history_table – History table name for system-versioned tables (SQL Server T-SQL grammar-based)

• history_schema – History table schema for system-versioned tables (SQL Server T-SQL grammar-based)

• derived_from – Source for derived tables (e.g., “CTAS”, “LIKE source_table”, “SELECT”)

add_column(column: SqlColumn) → None

Add a column to the table.

Parameters:

column – The column to add

get_column(name: str) → SqlColumn | None

Get a column by name.

Parameters:

name – Column name

Returns:

The column or None if not found

add_constraint(constraint: SqlConstraint) → None

Add a constraint to the table.

Parameters:

constraint – The constraint to add

get_primary_key() → SqlConstraint | None

Get the primary key constraint.

Returns:

The primary key constraint or None if not found

get_foreign_keys() → List[SqlConstraint]

Get all foreign key constraints.

Returns:

List of foreign key constraints

get_unique_constraints() → List[SqlConstraint]

Get all unique constraints.

Returns:

List of unique constraints

get_check_constraints() → List[SqlConstraint]

Get all check constraints.

Returns:

List of check constraints

property create_statement: str

Generate CREATE TABLE statement.

Returns:

SQL CREATE TABLE statement for this table

property drop_statement: str

Generate DROP TABLE statement.

Returns:

SQL DROP TABLE statement for this table

__str__() → str

Return string representation of the table.

compare_with_defaults(other: SqlObject, schema_defaults: Dict[str, Any] = None) → Dict[str, Any]

Compare two tables, taking into account schema defaults.

This method extends the base class method to handle table-specific properties.

Parameters:

• other – The other table to compare with

• schema_defaults – Dictionary of schema default values

Returns:

Dictionary of differences between the tables

to_dict() → Dict[str, Any]

Convert table to dictionary representation.

Returns:

Dictionary with table attributes

classmethod from_dict(data: Dict[str, Any]) → Table

Create table from dictionary representation.

Parameters:

data – Dictionary with table attributes

Returns:

Table object

View

class sqlmeta.objects.view.View(name: str, schema: str | None = None, query: str | None = None, columns: List[str] | None = None, materialized: bool = False, dialect: str | None = None, is_populated: bool | None = None, refresh_method: str | None = None, refresh_mode: str | None = None, fast_refreshable: bool | None = None, last_refresh: str | None = None, unlogged: bool | None = None, algorithm: str | None = None, sql_security: str | None = None, definer: str | None = None, force: bool | None = None)

Bases: SqlObject

Represents a database view.

Supports both regular views and materialized views with refresh options.

__init__(name: str, schema: str | None = None, query: str | None = None, columns: List[str] | None = None, materialized: bool = False, dialect: str | None = None, is_populated: bool | None = None, refresh_method: str | None = None, refresh_mode: str | None = None, fast_refreshable: bool | None = None, last_refresh: str | None = None, unlogged: bool | None = None, algorithm: str | None = None, sql_security: str | None = None, definer: str | None = None, force: bool | None = None)

Initialize a view.

Parameters:

• name – View name

• schema – Schema name (optional)

• query – SELECT query that defines the view

• columns – List of column names (optional)

• materialized – Whether the view is materialized

• dialect – SQL dialect

• is_populated – Whether the materialized view is populated (PostgreSQL, Oracle)

• refresh_method – Refresh method - FAST, COMPLETE, FORCE, MANUAL (Oracle, DB2)

• refresh_mode – Refresh mode - ON DEMAND, ON COMMIT (Oracle)

• fast_refreshable – Whether fast refresh is available (Oracle)

• last_refresh – Timestamp of last refresh (Oracle, DB2)

• unlogged – Whether the materialized view is UNLOGGED (PostgreSQL grammar-based)

• algorithm – View algorithm - MERGE, TEMPTABLE, UNDEFINED (MySQL grammar-based)

• sql_security – SQL security - DEFINER, INVOKER (MySQL grammar-based)

• definer – Definer user - user@host (MySQL grammar-based)

• force – Whether view is created with FORCE (True) or NOFORCE (False) (Oracle grammar-based)

property create_statement: str

Generate CREATE VIEW statement.

The syntax varies by dialect.

Returns:

Dialect-specific CREATE VIEW statement

property drop_statement: str

Generate DROP VIEW statement.

Returns:

SQL DROP VIEW statement for this view

__str__() → str

Return string representation of the view.

__eq__(other: Any) → bool

Check if two views are equal.

classmethod from_dict(data: Dict[str, Any]) → View

Create view from dictionary representation.

Parameters:

data – Dictionary with view attributes

Returns:

View object

to_dict() → Dict[str, Any]

Convert view to dictionary representation.

Returns:

Dictionary with view attributes

Procedure

class sqlmeta.objects.procedure.Parameter(name: str, data_type: str, direction: str = 'IN', default_value: str | None = None, dialect: str | None = None)

Bases: object

Represents a stored procedure parameter.

__init__(name: str, data_type: str, direction: str = 'IN', default_value: str | None = None, dialect: str | None = None)

Initialize a procedure parameter.

Parameters:

• name – Parameter name

• data_type – Parameter data type

• direction – Parameter direction (IN, OUT, INOUT)

• default_value – Default value for the parameter

• dialect – SQL dialect (optional)

__str__() → str

String representation of the parameter.

to_dict() → Dict[str, Any]

Convert parameter to dictionary.

classmethod from_dict(data: Dict[str, Any]) → Parameter

Create parameter from dictionary.

class sqlmeta.objects.procedure.Procedure(name: str, schema: str | None = None, parameters: List[Parameter] | None = None, body: str | None = None, language: str = 'SQL', dialect: str | None = None, is_function: bool = False, return_type: str | None = None, comment: str | None = None, definition: str | None = None)

Bases: SqlObject

Represents a stored procedure or function.

__init__(name: str, schema: str | None = None, parameters: List[Parameter] | None = None, body: str | None = None, language: str = 'SQL', dialect: str | None = None, is_function: bool = False, return_type: str | None = None, comment: str | None = None, definition: str | None = None)

Initialize a stored procedure or function.

Parameters:

• name – Procedure/function name

• schema – Schema name

• parameters – List of procedure/function parameters

• body – Procedure/function body

• language – Procedure language (SQL, PLSQL, PLPGSQL, TSQL, etc.)

• dialect – SQL dialect

• is_function – Whether this is a function (vs procedure)

• return_type – Return type for functions

• comment – Procedure/function comment/description

• definition – Full procedure/function definition SQL

property create_statement: str

Generate CREATE PROCEDURE or CREATE FUNCTION statement.

The syntax varies by dialect.

Returns:

Dialect-specific CREATE PROCEDURE/FUNCTION statement

property drop_statement: str

Generate DROP PROCEDURE or DROP FUNCTION statement.

Returns:

SQL DROP PROCEDURE/FUNCTION statement

classmethod from_dict(data: Dict[str, Any]) → Procedure

Create procedure/function from dictionary representation.

Parameters:

data – Dictionary with procedure/function attributes

Returns:

Procedure object

to_dict() → Dict[str, Any]

Convert procedure/function to dictionary representation.

Returns:

Dictionary with procedure/function attributes

Trigger

Trigger SQL Model.

Represents a database trigger with its definition, timing, and events.

class sqlmeta.objects.trigger.Trigger(name: str, table_name: str, schema: str | None = None, timing: str | None = None, events: list[str] | None = None, orientation: str | None = None, definition: str | None = None, enabled: bool = True, dialect: str | None = None, definer: str | None = None)

Bases: SqlObject

Represents a database trigger.

__init__(name: str, table_name: str, schema: str | None = None, timing: str | None = None, events: list[str] | None = None, orientation: str | None = None, definition: str | None = None, enabled: bool = True, dialect: str | None = None, definer: str | None = None)

Initialize a trigger.

Parameters:

• name – Trigger name

• table_name – Name of the table the trigger is on

• schema – Schema name (optional)

• timing – When trigger fires (BEFORE, AFTER, INSTEAD OF)

• events – List of events that fire the trigger (INSERT, UPDATE, DELETE, TRUNCATE)

• orientation – Trigger level (ROW, STATEMENT)

• definition – Trigger body/definition

• enabled – Whether trigger is enabled

• dialect – SQL dialect

• definer – Definer user - user@host (MySQL grammar-based)

property qualified_table_name: str

Get the qualified table name (schema.table).

Returns:

Qualified table name

property event_str: str

Get events as a formatted string.

Returns:

Events joined by ‘ OR ‘ (e.g., ‘INSERT OR UPDATE’)

property create_statement: str

Generate CREATE TRIGGER statement.

The syntax varies by dialect.

Returns:

Dialect-specific CREATE TRIGGER statement

__str__() → str

Return string representation of the trigger.

__eq__(other: Any) → bool

Check if two triggers are equal.

Parameters:

other – Other object to compare

Returns:

True if triggers are equal

__repr__() → str

Return detailed representation of the trigger.

classmethod from_dict(data: Dict[str, Any]) → Trigger

Create trigger from dictionary representation.

Parameters:

data – Dictionary with trigger attributes

Returns:

Trigger object

to_dict() → Dict[str, Any]

Convert trigger to dictionary representation.

Returns:

Dictionary with trigger attributes

Sequence

class sqlmeta.objects.sequence.Sequence(name: str, schema: str | None = None, start_with: int | None = None, increment_by: int | None = None, min_value: int | None = None, max_value: int | None = None, cycle: bool = False, cache: int | None = None, dialect: str | None = None, temp: bool = False)

Bases: SqlObject

Represents a database sequence.

__init__(name: str, schema: str | None = None, start_with: int | None = None, increment_by: int | None = None, min_value: int | None = None, max_value: int | None = None, cycle: bool = False, cache: int | None = None, dialect: str | None = None, temp: bool = False)

Initialize a sequence.

Parameters:

• name – Sequence name

• schema – Schema name

• start_with – Starting value

• increment_by – Increment value

• min_value – Minimum value

• max_value – Maximum value

• cycle – Whether to cycle when reaching max_value

• cache – Cache size

• dialect – SQL dialect

• temp – Whether sequence is TEMPORARY (PostgreSQL grammar-based)

property create_statement: str

Generate CREATE SEQUENCE statement.

The syntax varies by dialect.

Returns:

Dialect-specific CREATE SEQUENCE statement

property drop_statement: str

Generate DROP SEQUENCE statement.

Returns:

SQL DROP SEQUENCE statement for this sequence

classmethod from_dict(data: Dict[str, Any]) → Sequence

Create sequence from dictionary representation.

Parameters:

data – Dictionary with sequence attributes

Returns:

Sequence object

to_dict() → Dict[str, Any]

Convert sequence to dictionary representation.

Returns:

Dictionary with sequence attributes

Index

class sqlmeta.objects.index.Index(name: str, table_name: str, columns: List[str], schema: str | None = None, table_schema: str | None = None, unique: bool = False, type: str = 'BTREE', condition: str | None = None, include_columns: List[str] | None = None, sort_directions: List[str] | None = None, dialect: str | None = None, online: bool | None = None, concurrently: bool = False, tablespace: str | None = None)

Bases: SqlObject

Represents a database index.

__init__(name: str, table_name: str, columns: List[str], schema: str | None = None, table_schema: str | None = None, unique: bool = False, type: str = 'BTREE', condition: str | None = None, include_columns: List[str] | None = None, sort_directions: List[str] | None = None, dialect: str | None = None, online: bool | None = None, concurrently: bool = False, tablespace: str | None = None)

Initialize an index.

Parameters:

• name – Index name

• table_name – Name of the table being indexed

• columns – List of indexed columns

• schema – Schema name for the index

• table_schema – Schema name for the table (if different from index schema)

• unique – Whether this is a unique index

• type – Index type (BTREE, HASH, FULLTEXT, SPATIAL, etc.)

• condition – Optional WHERE condition

• include_columns – Optional INCLUDE columns (SQL Server)

• sort_directions – Optional sort directions (ASC/DESC) for each column

• dialect – SQL dialect

• online – Whether index was created with ONLINE (True) or OFFLINE (False) (MySQL grammar-based)

• concurrently – Whether index was created CONCURRENTLY (PostgreSQL grammar-based)

• tablespace – Tablespace name for the index (Oracle grammar-based)

property create_statement: str

Generate CREATE INDEX statement.

The syntax varies by dialect.

Returns:

Dialect-specific CREATE INDEX statement

property drop_statement: str

Generate DROP INDEX statement.

Returns:

SQL DROP INDEX statement for this index

classmethod from_dict(data: Dict[str, Any]) → Index

Create index from dictionary representation.

Parameters:

data – Dictionary with index attributes

Returns:

Index object

to_dict() → Dict[str, Any]

Convert index to dictionary representation.

Returns:

Dictionary with index attributes

Other Objects

Extension

Extension SQL model class (PostgreSQL-specific).

class sqlmeta.objects.extension.Extension(name: str, version: str | None = None, schema: str | None = None, description: str | None = None, relocatable: bool = False, dialect: str | None = None)

Bases: SqlObject

Represents a database extension (PostgreSQL-specific).

Extensions are add-on modules that provide additional functionality to the database, such as PostGIS for geographic data, pgcrypto for cryptographic functions, or pg_trgm for trigram text search.

__init__(name: str, version: str | None = None, schema: str | None = None, description: str | None = None, relocatable: bool = False, dialect: str | None = None)

Initialize an extension.

Parameters:

• name – Extension name

• version – Extension version (optional)

• schema – Schema where the extension is installed (optional)

• description – Extension description (optional)

• relocatable – Whether the extension can be relocated to another schema

• dialect – SQL dialect (typically ‘postgresql’)

property create_statement: str

Generate CREATE EXTENSION statement.

Returns:

PostgreSQL CREATE EXTENSION statement

property drop_statement: str

Generate DROP EXTENSION statement.

Returns:

PostgreSQL DROP EXTENSION statement

__str__() → str

Return string representation of the extension.

__eq__(other: Any) → bool

Check if two extensions are equal.

__hash__() → int

Return hash of the extension.

Foreign Data Wrapper

Foreign Data Wrapper SQL model class (PostgreSQL-specific).

class sqlmeta.objects.foreign_data_wrapper.ForeignDataWrapper(name: str, handler: str | None = None, validator: str | None = None, options: Dict[str, str] | None = None, schema: str | None = None, dialect: str | None = None)

Bases: SqlObject

Represents a foreign data wrapper (PostgreSQL-specific).

Foreign Data Wrappers (FDW) enable PostgreSQL to access external data sources including other databases (postgres_fdw, oracle_fdw), files (file_fdw), and custom data sources.

__init__(name: str, handler: str | None = None, validator: str | None = None, options: Dict[str, str] | None = None, schema: str | None = None, dialect: str | None = None)

Initialize a foreign data wrapper.

Parameters:

• name – FDW name (e.g., ‘postgres_fdw’, ‘oracle_fdw’)

• handler – Handler function name (optional)

• validator – Validator function name (optional)

• options – FDW-specific options as key-value pairs (optional)

• schema – Schema (typically ‘public’ or system schemas)

• dialect – SQL dialect (typically ‘postgresql’)

property create_statement: str

Generate CREATE FOREIGN DATA WRAPPER statement.

Returns:

PostgreSQL CREATE FOREIGN DATA WRAPPER statement

property drop_statement: str

Generate DROP FOREIGN DATA WRAPPER statement.

Returns:

PostgreSQL DROP FOREIGN DATA WRAPPER statement

__str__() → str

Return string representation of the FDW.

__eq__(other: Any) → bool

Check if two FDWs are equal.

__hash__() → int

Return hash of the FDW.

Foreign Server

Foreign Server SQL model class (PostgreSQL-specific).

class sqlmeta.objects.foreign_server.ForeignServer(name: str, fdw_name: str, host: str | None = None, port: int | None = None, dbname: str | None = None, options: Dict[str, str] | None = None, schema: str | None = None, dialect: str | None = None)

Bases: SqlObject

Represents a foreign server (PostgreSQL-specific).

Foreign Servers define connection parameters for specific remote data sources accessed through Foreign Data Wrappers. Multiple foreign tables can reference the same foreign server.

__init__(name: str, fdw_name: str, host: str | None = None, port: int | None = None, dbname: str | None = None, options: Dict[str, str] | None = None, schema: str | None = None, dialect: str | None = None)

Initialize a foreign server.

Parameters:

• name – Foreign server name

• fdw_name – Name of the foreign data wrapper to use

• host – Remote host address (optional)

• port – Remote port number (optional)

• dbname – Remote database name (optional)

• options – Server-specific options as key-value pairs (optional)

• schema – Schema (typically ‘public’)

• dialect – SQL dialect (typically ‘postgresql’)

property create_statement: str

Generate CREATE SERVER statement.

Returns:

PostgreSQL CREATE SERVER statement

property drop_statement: str

Generate DROP SERVER statement.

Returns:

PostgreSQL DROP SERVER statement

__str__() → str

Return string representation of the foreign server.

__eq__(other: Any) → bool

Check if two foreign servers are equal.

__hash__() → int

Return hash of the foreign server.

Package

Oracle Package SQL Model.

class sqlmeta.objects.package.Package(name: str, schema: str | None = None, spec: str | None = None, body: str | None = None, dialect: str | None = 'oracle')

Bases: SqlObject

Represents an Oracle package (specification and body).

__init__(name: str, schema: str | None = None, spec: str | None = None, body: str | None = None, dialect: str | None = 'oracle')

Initialize an Oracle package.

Parameters:

• name – Package name

• schema – Schema name (optional)

• spec – Package specification (header/interface)

• body – Package body (implementation)

• dialect – SQL dialect (defaults to oracle)

property create_statement: str

Generate CREATE PACKAGE statements.

Returns both spec and body if available.

Returns:

CREATE PACKAGE and CREATE PACKAGE BODY statements

__str__() → str

Return string representation of the package.

__eq__(other: Any) → bool

Check if two packages are equal.

classmethod from_dict(data: Dict[str, Any]) → Package

Create package from dictionary representation.

Parameters:

data – Dictionary with package attributes

Returns:

Package object

to_dict() → Dict[str, Any]

Convert package to dictionary representation.

Returns:

Dictionary with package attributes

Synonym

Synonym SQL model class.

class sqlmeta.objects.synonym.Synonym(name: str, target_object: str, schema: str | None = None, target_schema: str | None = None, target_database: str | None = None, db_link: str | None = None, dialect: str | None = None)

Bases: SqlObject

Represents a database synonym (alias for another object).

__init__(name: str, target_object: str, schema: str | None = None, target_schema: str | None = None, target_database: str | None = None, db_link: str | None = None, dialect: str | None = None)

Initialize a synonym.

Parameters:

• name – Synonym name

• target_object – Name of the target object this synonym points to

• schema – Schema where the synonym is defined (optional)

• target_schema – Schema of the target object (optional)

• target_database – Database of the target object (optional, SQL Server)

• db_link – Database link for remote objects (optional, Oracle)

• dialect – SQL dialect

property target_full_name: str

Get the fully qualified name of the target object.

Returns:

Fully qualified target name including schema/database/link

property create_statement: str

Generate CREATE SYNONYM statement.

The syntax varies by dialect.

Returns:

Dialect-specific CREATE SYNONYM statement

property drop_statement: str

Generate DROP SYNONYM statement.

Returns:

Dialect-specific DROP SYNONYM statement

__str__() → str

Return string representation of the synonym.

__eq__(other: Any) → bool

Check if two synonyms are equal.

__hash__() → int

Return hash of the synonym.

Event

MySQL Event SQL Model.

class sqlmeta.objects.event.Event(name: str, schema: str | None = None, definition: str | None = None, schedule: str | None = None, enabled: bool = True, comment: str | None = None, definer: str | None = None, event_type: str = 'ONE TIME', dialect: str | None = 'mysql')

Bases: SqlObject

Represents a MySQL scheduled event.

__init__(name: str, schema: str | None = None, definition: str | None = None, schedule: str | None = None, enabled: bool = True, comment: str | None = None, definer: str | None = None, event_type: str = 'ONE TIME', dialect: str | None = 'mysql')

Initialize a MySQL event.

Parameters:

• name – Event name

• schema – Schema/database name (optional)

• definition – Event body (DO clause)

• schedule – Schedule expression (AT or EVERY clause)

• enabled – Whether the event is enabled

• comment – Event comment/description

• definer – User who defined the event

• event_type – Event type (ONE TIME or RECURRING)

• dialect – SQL dialect (defaults to mysql)

property create_statement: str

Generate CREATE EVENT statement.

Returns:

CREATE EVENT statement

__str__() → str

Return string representation of the event.

__eq__(other: Any) → bool

Check if two events are equal.

classmethod from_dict(data: Dict[str, Any]) → Event

Create event from dictionary representation.

Parameters:

data – Dictionary with event attributes

Returns:

Event object

to_dict() → Dict[str, Any]

Convert event to dictionary representation.

Returns:

Dictionary with event attributes

User Defined Type

User-Defined Type SQL model class.

class sqlmeta.objects.user_defined_type.UserDefinedType(name: str, type_category: str, schema: str | None = None, data_type: str | None = None, definition: str | None = None, attributes: List[Dict[str, Any]] | None = None, enum_values: List[str] | None = None, base_type: str | None = None, comment: str | None = None, dialect: str | None = None)

Bases: SqlObject

Represents a user-defined type (UDT).

__init__(name: str, type_category: str, schema: str | None = None, data_type: str | None = None, definition: str | None = None, attributes: List[Dict[str, Any]] | None = None, enum_values: List[str] | None = None, base_type: str | None = None, comment: str | None = None, dialect: str | None = None)

Initialize a user-defined type.

Parameters:

• name – Type name

• type_category – Category of type (COMPOSITE, ENUM, DOMAIN, OBJECT, DISTINCT, etc.)

• schema – Schema name (optional)

• data_type – SQL data type classification (optional)

• definition – Type definition/source code (optional)

• attributes – List of attributes for composite/structured types (optional)

• enum_values – List of values for enum types (optional)

• base_type – Base type for DISTINCT types (optional)

• comment – Type comment/description (optional)

• dialect – SQL dialect

property is_composite: bool

Check if this is a composite/structured type.

property is_enum: bool

Check if this is an enum type.

property is_domain: bool

Check if this is a domain type.

property is_distinct: bool

Check if this is a distinct type.

property create_statement: str

Generate CREATE TYPE statement.

The syntax varies significantly by dialect and type category.

Returns:

Dialect-specific CREATE TYPE statement

property drop_statement: str

Generate DROP TYPE statement.

Returns:

Dialect-specific DROP TYPE statement

__str__() → str

Return string representation of the type.

__eq__(other: Any) → bool

Check if two types are equal.

__hash__() → int

Return hash of the type.