ePages 6.10 - DE_EPAGES/Object/API/Object/SQLStructure.pm

Package DE_EPAGES::Object::API::Object::SQLStructure

Represents an SQL schema with tables, indexes, and constraints. Used to compare schemas and generate patch scripts for schema transformation.

Example
my $SQLStruct = DE_EPAGES::Object::API::Object::SQLStructure->new;
$SQLStruct->addTable('object');
$SQLStruct->addTableColumn('object', 'objectid', 'Not Null');
$SQLStruct->addTableColumn('object', 'guid', 'Not Null');
$SQLStruct->addPrimaryKey('object', 'pk_object', ['objectid'] );
$SQLStruct->addIndex('object', 'u_object_guid', { 'guid' => undef }, 'UNIQUE');
$SQLStruct->addForeignKey('product', 'object', 'fk_product_object', ['productid'], ['objectid']);
foreach my $Table ( @{ $SQLStruct->getTables } ) {
    foreach my $Columns ( @{ $SQLStruct->getTableColumns( $Table ) } ) {
        ...
    }
}

Functions

addForeignKey
addIndex
addPrimaryKey
addTable
addTableColumn
existsPrimaryKey
existsTable
existsTableColumn
existsTableForeignKey
existsTableIndex
existsTableIndexColumn
getPrimaryKeyColumns
getPrimaryKeyInfo
getPrimaryKeyName
getSQLType
getTableColumnDataType
getTableColumnDefault
getTableColumnInfo
getTableColumnNotNull
getTableColumnPosition
getTableColumns
getTableForeignKeyColumnPairs
getTableForeignKeyRefTable
getTableForeignKeys
getTableIndexClustered
getTableIndexColumnSortOrder
getTableIndexColumnSortOrderPairs
getTableIndexColumns
getTableIndexInfo
getTableIndexUnique
getTableIndices
getTables
new

addForeignKey

add foreign key of table to structure

Syntax
$SQLStruct->addForeignKey($TableName, $ForeignKeyName, $ReferencedTableName, $aColumnNames, $aReferencedColumnNames);
Input
$TableName (string)
name of table
$ForeignKeyName (string)
identifier of foreign key
$ReferencedTableName (string)
name of table where foreign key is referenced to
$hColumnPairs (ref.hash)
keys are column names and values are the referenced column names

addIndex

add index of table to structure

Syntax
$SQLStruct->addIndex($TableName, $IndexName, $hColumnSortOrderPairs, $Unique, $Clustered);
Example
$SQLStruct->addIndex('product', 'i_productname', {'productname' => 'DESC'}, {'Unique' => '0', 'Clustered' => 'CLUSTERED'});
Input
$TableName (string)
name of table
$IndexName (string)
identifier of index
$hColumnSortOrderPairs (string)
pairs of column that shows to his sort of order if exists (DESC, ASC, undef, _SQLStruct-Default)
Return
$hParams (ref.hash)

  • Unique - flag that shows if index is unique or not - bool
  • Clustered - flag that shows if index is clustered or not - string

addPrimaryKey

add primary key of table to structure

Syntax
$SQLStruct->addPrimaryKey($TableName, $PrimaryKeyName, $aColumnNames, $SortOrder, $Clustered);
Input
$TableName (string)
name of table
$PrimaryKeyName (string)
identifier of primary key
$aColumnNames (ref.array)
names of columns belong to primary key
Return
$hParams (ref.hash)

  • SortOrder - flag that shows if sort order is ascendencing (ASC), descandencing (DESC) or default '_SQLStruct-Default' - string
  • Clustered - flag that shows if primary is clustered, columns in db where sorted physicallly [CLUSTERED,NONCLUSTERED,_SQLStruct-Default] - string

addTable

added table with table name to structure

Syntax
$SQLStruct->addTable($TableName);
Input
$TableName (string)
name of table

addTableColumn

added table column to structure

Syntax
$SQLStruct->addTableColumn($TableName, $ColumnName, $DataType, $NotNull, $Position, $Default);
Input
$TableName (string)
name of table
$ColumnName (string)
name of column
$DataType (string)
name of datatype, e.g. varchar(20), int nvarchar, etc. ...
$NotNull (bool)
flag that shows if null values are allowed for column
$Position (string)
postion of column in table
$Default (string)
value of default of columen

existsPrimaryKey

return true if primary key of table exists

Syntax
$PrimaryKeyExists = $SQLStruct->existsPrimaryKey($TableName, $PrimaryKey);
Input
$TableName (string)
name of table
$PrimaryKey (string)
name of table
Return
$TableColumnExists (bool)
true if table exists in structure

existsTable

return true if table exists in structure

Syntax
$TableExists = $SQLStruct->existsTable($TableName);
Input
$TableName (string)
name of table
Return
$TableExists (bool)
true if table exists in structure

existsTableColumn

return true if table and column exists in structure

Syntax
$TableColumnExists = $SQLStruct->existsTableColumn($TableName, $ColumnName);
Input
$TableName (string)
name of table
$ColumnName (string)
name of table column
Return
$TableColumnExists (bool)
true if table exists in structure

existsTableForeignKey

return true if foreign key of table exists in structure

Syntax
$ForeignKeyExists = $SQLStruct->existsTableForeignKey($TableName, $ForeignKeyName);
Input
$TableName (string)
name of table
$ForeignKeyName (string)
identifier of foreign key
Return
$ForeignKeyExists (bool)
true if foreign key of table exists in structure

existsTableIndex

return true if foreign key of table exists in structure

Syntax
$IndexExists = $SQLStruct->existsTableIndex($TableName, $IndexName);
Input
$TableName (string)
name of table
$IndexName (string)
identifier of index
Return
$IndexExists (bool)
true if index of table exists in structure

existsTableIndexColumn

return true if index is on column, check if column is registered in table index

Syntax
$TableColumnHasIndex = $SQLStruct->existsTableIndexColumn($TableName, $IndexName, $TableColumnName);
Input
$TableName (string)
name of table
$IndexName (string)
identifier of index
$TableColumnName (string)
name of table column
Return
$TableColumnHasIndex (bool)
true if column has an index with given name

getPrimaryKeyColumns

return columns of primary key of table in structure

Syntax
$aPrimaryKeyColumns = $SQLStruct->getPrimaryKeyColumns($TableName);
Input
$TableName (string)
name of table
Return
$aPrimaryKeyColumns (ref.array.string)
columns of primary key

getPrimaryKeyInfo

return infos of primary key

Syntax
$hPrimaryKeyInfo = $SQLStruct->getPrimaryKeyInfo($TableName);
Input
$TableName (string)
name of table
Return
$hPrimaryKeyInfo (ref.hash)

  • Name - key name - int
  • SortOrder - sort order (1/0, where 1 means ascending and 0 means descending) - bool
  • Clustered - clustered index (CLUSTERED/...) - string

getPrimaryKeyName

return primary of table in structure

Syntax
$PrimaryKeyName = $SQLStruct->getPrimaryKeyName($TableName);
Input
$TableName (string)
name of table
Return
$PrimaryKeyName (string)
name of primary key

getSQLType

returns the type of sql structure [sybase|mysql|SQL]

Syntax
my $SQLType = $SQLStruct->getSQLType();
Return
$SQLType (string)
name of SQL type

getTableColumnDataType

return data type of table column in structure

Syntax
$DataType = $SQLStruct->getTableColumnDataType($TableName, $ColumnName);
Input
$TableName (string)
name of table
$ColumnName (string)
name of table column
Return
$DataType (string)
data type of column

getTableColumnDefault

return default of table column in structure

Syntax
$Default = $SQLStruct->getTableColumnDefault($TableName, $ColumnName);
Input
$TableName (string)
name of table
$ColumnName (string)
name of table column
Return
$Default (string)
default type of column

getTableColumnInfo

return infos of table column

Syntax
$hTableColumnInfo = $SQLStruct->getTableColumnInfo($TableName, $ColumnName);
Input
$TableName (string)
name of table
$ColumnName (string)
name of table column
Return
$hTableColumnInfo (ref.hash)

  • Position - position - int
  • Default - default value - string
  • DataType - data type value - string
  • NotNull - value that shows if column allows null value
    (0 - null values allowed, 1 - no null values allowed) - bool

getTableColumnNotNull

return true if table column in structure allow null values

Syntax
$NotNull = $SQLStruct->getTableColumnNotNull($TableName, $ColumnName);
Input
$TableName (string)
name of table
$ColumnName (string)
name of table column
Return
$NotNull (int)
default type of column

getTableColumnPosition

return position of table column in structure

Syntax
$Position = $SQLStruct->getTableColumnPosition($TableName, $ColumnName);
Input
$TableName (string)
name of table
$ColumnName (string)
name of table column
Return
$Position (int)
position of column

getTableColumns

return column names of table sorted by position

Syntax
$aTableColumnNames = $SQLStruct->existsTable($TableName);
Input
$TableName (string)
name of table
Return
$aTableColumnNames (ref.array)
name of table columns sorted by position

getTableForeignKeyColumnPairs

return hash with pairs of column names, <column,referenced column>

Syntax
$hForeignKeyColumnPairs = $SQLStruct->getTableForeignKeyColumnPairs($TableName, $ForeignKeyName);
Input
$TableName (string)
name of table
$ForeignKeyName (string)
identifier of foreign key
Return
$hForeignKeyColumnPairs (ref.hash)

  • $ColumnName => $ReferencedColumnName
  • ...

getTableForeignKeyRefTable

return table name that is referenced by foreign key

Syntax
$ForeignKeyRefTable = $SQLStruct->getTableForeignKeyRefTable($TableName, $ForeignKeyName);
Input
$TableName (string)
name of table
$ForeignKeyName (string)
identifier of foreign key
Return
$ForeignKeyRefTable (string)
name of table referenced by foreign key

getTableForeignKeys

return names of all foreign keys from table in SQL structure

Syntax
$aForeignKeys = $SQLStruct->getTableForeignKeys($TableName);
Input
$TableName (string)
name of table
Return
$aForeignKeys (ref.array)
names of all foreign keys from tables in SQL structure

getTableIndexClustered

return value that shows if index is clustered, return undef if table or index doesnt exists

Syntax
$Clustered = $SQLStruct->getTableIndexClustered($TableName, $IndexName);
Input
$TableName (string)
name of table
$IndexName (string)
identifier of index
Return
$Clustered (string)
show if index is clustered [CLUSTERED,UNCLUSTERED,_SQLStruct-Default]

getTableIndexColumnSortOrder

return sort order from index of column

Syntax
$SortOrder = $SQLStruct->getTableIndexColumnSortOrder($TableName, $IndexName, $TableColumnName);
Input
$TableName (string)
name of table
$IndexName (string)
identifier of index
$TableColumnName (string)
name of table column
Return
$SortOrder (bool)
sort order from index of column [ASC, DESC, _SQLStruct-Default, undef]

getTableIndexColumnSortOrderPairs

return column sort order pairs of index

Syntax
$TableIndexColumnSortOrderPairs = $SQLStruct->getTableIndexColumnSortOrderPairs($TableName, $IndexName);
Input
$TableName (string)
name of table
$IndexName (string)
identifier of index
Return
$hTableIndexColumnSortOrderPairs (ref.hash)

  • $ColumnName => $SortOrder
  • ...

getTableIndexColumns

return all columns of index in structure

Syntax
$aTableIndexColumns = $SQLStruct->getTableIndexColumns($TableName, $IndexName);
Input
$TableName (string)
name of table
$IndexName (string)
identifier of index
Return
$aTableIndexColumns (ref.array)
columns from index

getTableIndexInfo

return the whole information abaout an table index

Syntax
$hTableIndexInfo = $SQLStruct->getTableIndexInfo($TableName, $IndexName);
Input
$TableName (string)
name of table
$IndexName (string)
identifier of index
Return
$hTableIndexInfo (ref.hash)

  • $Clustered - show if index is clustered [CLUSTERED,UNCLUSTERED,_SQLStruct-Default] - string
  • Unique - show if index is unique - bool
  • ColumnSortOrderPairs
    • 'ColumnName' => 'SortOrder'
    • ...

getTableIndexUnique

return true if index is unique, return undef if table or index doesnt exists

Syntax
$IsUnique = $SQLStruct->getTableIndexUnique($TableName, $IndexName);
Input
$TableName (string)
name of table
$IndexName (string)
identifier of index
Return
$IsUnique (bool)
true if index is unique

getTableIndices

return names of all indices from table in SQL structure

Syntax
$aTableIndices = $SQLStruct->getTableIndices($TableName);
Input
$TableName (string)
name of table
Return
$aTableIndices (ref.array)
names of all indices from tables in SQL structure

getTables

return names of all tables in SQL structure

Syntax
$aTables = $SQLStruct->getTables();
Return
$aTables (ref.array)
names of tables in SQL structure

new

constructor for semantic structure object.

Syntax
$self->new($XMLType);
Input
$XMLType (str)
type of xml file, normally 'epages_standard' or 'epages_translation'