%SYSTEM.SQL.Schema
abstract class %SYSTEM.SQL.Schema extends %SYSTEM.Help
Method Inventory
- CreateLinkedProcedure()
- CreateLinkedTable()
- Default()
- DropTable()
- DropView()
- ExportDDL()
- GetAllColumns()
- GetVisibleColumns()
- ImportDDL()
- ImportDDLDir()
- ImportDir()
- LoadFDBMS()
- LoadInformix()
- LoadInterBase()
- LoadMSSQLServer()
- LoadOracle()
- LoadSybase()
- LoadTSQL()
- ProcedureExists()
- Run()
- SetDefault()
- TableExists()
- ValidateTable()
- ViewExists()
Methods
dsn - Data Source Name
externalSchema - Schema name of the linked stored procedure of external source
externalProcedure - External selected Stored Procedure name
localPackage - New Stored Procedure's Package name. Default value is "LinkedSchema".
localClass - New class name for the new Linked stored procedure. Default value is "LinkedProcedures"
localMethod - Method name for the stored procedure. Default value is externalProcedure.
localSqlName - New SQL name. Default value is externalProcedure.
description - Description. Default value is empty
dsn - Data Source Name
externalSchema - Schema name of the linked table of external source
externalTable - The linked table name of external source
primaryKeys - The fields for the primary key of new created table.
Multiple fields are separated by comma. The primary key's field(s) should be specified as the "new class property name" if you do not use the original field name of the external source
You at least have to specify one field for this primaryKey.
localClass - The new linked class name. The default value is "User.LinkedClass"
localTable - The new table name for the linked class. Default value is localClass
columnMap - The linked fields of the external table. Default is to map everything as read-only columns if you don't specify anything in the map. Otherwise, specify the fields in the below format. columnMap("external field name") = $lb("new class property name","new sql field name","read-only"(1/0)) or use external field name as default values by specifying columnMap("external field name") = "". "new class property name" and "new sql field name" could be defined empty and "external field name" would be used as their default values "read only" default is on.
Below is example for linking a table from MySQL
Use these commands to create a table in MySQL
in MySQL shell:
create database test1;
use test1;
create table Person(PID int,name varchar(255));
insert into Person(PID,name) values(1,"Cache")
in Cache Terminal:
$SYSTEM.SQL.Schema.CreateLinkedTable(dsn,"","Person","PID,name","User.LinkedClass","LinkedTable","")
would link all the fields of the table test1.Person from MySQL to the Cache class "User.LinkedTable"
and use both PID and name as primary keys
Example:
Set CurrentSchema = $SYSTEM.SQL.Schema.Default() This method can also be called as a Stored Procedure named %SYSTEM_SQL.DefaultSchema()
Parameters:
- tablename
- Name of the SQL table to delete. The name may be qualified with a schema name: Medical.Patient. If the schema name is omitted, the default schema is used.
- dropData
- TRUE(1)/FALSE(0) flag which determine if the table's data is to be deleted or not.
If dropData="" or undefined, use system flag to determine if data
should be deleted.
Examples:
- Do $SYSTEM.SQL.Schema.DropTable("MedLab.Patient",1)
- Do $SYSTEM.SQL.Schema.DropTable("IscPerson",1) ; Deletes SQLUser.IscPerson
Parameters:
- viewname
- Name of the SQL view to delete. The name may be qualified with a schema name: Medical.PatientView If the schema name is omitted, the default schema is used.
Examples:
- Do $SYSTEM.SQL.Schema.DropView("MedLab.PatientView")
- Do $SYSTEM.SQL.Schema.DropView("IscPersonView") ; Deletes SQLUser.IscPerson
Parameters:
- Schema
- Name of the schema in the current namespace to export the privileges for. Use "*" to specify the export of SQL Privileges for all schemas in the current namespace. The default is "*". Note that when "*" is specified, '%' schemas (such as %Dictionary) are not exported. To export privileges for tables in a schema that begins with '%' you must explicitly specify that schema name in Schema.
- Table
- Name of the table/view in the specified schema to export the privileges for. Use "*" to specify the export of SQL Privileges for all table/s views in the specified schema(s). The default is "*". Note that if you "*" for Schema the export will behave as if you entered "*" for Table as well. For example there is no mechanism for exporting the Person table/view in each schema in the namespace.
- File
- Name of the file to export the SQL statement to.
- qualifiers
- Specify any subset of the following arguments in in either dynamic object (e.g. {"FileOpenParam":"WNS","Roles":0}),
json string (e.g. "{""FileOpenParam"":""WNS"",""Roles"":""0""}"), or legacy qualifier format (e.g. "/FileOpenParam=WNS/Roles=0"). All qualifier names are case insensitive. Note that variables can be embedded in dynamic objects with the format {"ParamName":(varName)}.
- FileOpenParam
- Parameters to use when opening the File. The default is "WNS".
- Definitions
- 1/0 flag. Specify 1 to export the table/view definitions. The default is 1.
- Users
- 1/0 flag. Specify 1 to export the User definition. The default is 0.
- Roles
- 1/0 flag. Specify 1 to export the Role definition, and the GRANT statements to assign the Roles to the Users and Roles they have been granted to. The default is 1.
- Privileges
- 1/0 flag. Specify 1 to export the Table and View privileges for the tables and views specified in the Schema specification. The default is 1.
- SQLSystemPrivileges
- 1/0 flag. Specify 1 to export the SQL System privileges defined in the current namespace for the users and roles. The default is 1.
- FileOpenParam
Examples:
- Do $SYSTEM.SQL.Schema.ExportDDL("Sample","*","C:\PT\Sample.sql",{"FileOpenParam":"WNS","Users":0,"Roles":0,"Privileges":1,"SQLSystemPrivileges":1})
- Do $SYSTEM.SQL.Schema.ImportDDL("C:\DDT\UsersAndRoles.sql")
The method returns a Status Code.
A file created via $SYSTEM.SQL.Schema.ExportDDL() can be imported using one of:
- do $SYSTEM.SQL.Schema.ImportDDL(
, ,"IRIS") - do $SYSTEM.SQL.Schema.Run()
The ExportDDL() method will not export the following users and roles -
- Users:
- SuperUser, Admin, UnknownUser, %System, CSPSystem, _SYSTEM, _PUBLIC
- Roles:
- %All, %Developer, %Manager, %Operator, %SQL, %LegacyTerminal, %LegacyCacheDirect, %LegacySQL
Parameters:
- tablename
- Name of the table to return the columns for. Name is expected to be in the format Schema.Tablename If the name is unqualified, the default schema will be used.
- byName
- Passed By Reference. Returns columns ordered by column name with SqlColumnNumber as data. For example: byName("Id")=1,byName("Name")=2
- byNumber
- Passed By Reference. Returns columns ordered by SqlColumnNumber with column name as data. For example: byName(1)="Id",byName(2)="Name"
Parameters:
- tableName
- Name of the table to return the columns for. Name is expected to be in the format Schema.Tablename If the name is unqualified, the default schema will be used.
- byName
- Passed By Reference. Returns columns ordered by column name with SqlColumnNumber as data. For example: byName("Id")=1,byName("Name")=2
- byNumber
- Passed By Reference. Returns columns ordered by SqlColumnNumber with column name as data. For example: byName(1)="Id",byName(2)="Name" Returns: A %Status value
Parameters:
- infile
- The full path name of the script file to import. This parameter is required. A $list value may also be passed in with the filename and the TranslateTable to use for the file. (For information on translate tables, see Translation Tables.) The first element is the file name and the second element is the TranslateTable for the input stream file. This is only supported for DDLMode MSSQL, Sybase, Informix, or MySQL.
- logfile
- The full path name of the file to report errors in. This parameter is optional. Default is the same as the infile with a _Errors.log extension.
- IRIS
- CACHE
- FDBMS
- Informix
- Interbase
- MSSQL
- MSSQLServer - same as MSSQL
- MySQL
- Oracle
- Sybase
When DDLMode=IRIS, the following statements are supported:
- CREATE ...
- DROP ...
- TRUNCATE TABLE ...
- ALTER ...
- INSERT ...
- UPDATE ...
- DELETE ...
- SET OPTION ...
- GRANT ...
- REVOKE ...
- LOAD DATA ...
- USE [DATABASE] ...
- TUNE TABLE ...
- FREEZE ...
- UNFREEZE ...
- LOCK ...
- UNLOCK ...
- BUILD INDEX ...
Examples:
- Do $SYSTEM.SQL.Schema.ImportDDL("C:\PT\Patient.sql",,"Sybase")
- Do $SYSTEM.SQL.Schema.ImportDDL("C:\DDT\all_tables.sql",all.log,"Oracle")
Parameters:
- directory
- The full path name of the directory to import. This parameter is required.
- DDLMode
- Vendor from which the script file originated. This parameter is required.
Supported values are:
- Informix
- MSSQL
- MSSQLServer - same as MSSQL
- Sybase
- qualifiers
- Specify any subset of the following optional arguments in dynamic object format (e.g. {"logFile":"log.txt","eosDelimiter":";"}). Note that variables can be embedded in dynamic objects with the format {"ParamName":(varName)}, and all qualifier names are case insensitive.
- logFile
- The full path name of the file to report errors in. This parameter is Optional. Default is DDLImportDir.log in the directory loaded. If this parameter value is 1, a separate log file will be generated for each file loaded. The name of the log file will be the same as the file imported, but with the extension .log instead of .sql.
- eosDelimiter
- End of statement delimiter. Will default to an appropriate value based on the value of %DDLMode. This parameter is optional.
- logFile
Examples:
- Do $SYSTEM.SQL.Schema.ImportDDLDir("C:\Work\db1\","Informix",{"logFile":"C:\Work\db1\import.log","eosDelimiter":";"})
- Do $SYSTEM.SQL.Schema.ImportDDLDir("C:\Work\db1\","Informix",{"logFile":1,"eosDelimiter":";"})
Parameters:
- dialect: Vendor from which the script file originated. This parameter is required.
Supported values are:
- MSSQL
- MSSQLServer - same as MSSQL
- Sybase
- directory
- The full path name of the directory to import. This parameter is required.
- qualifiers
- Specify any subset of the following optional arguments in dynamic object format (e.g. {"logFile":"importdir.log","echoMode":0}). Note that variables can be embedded in dynamic objects with the format {"ParamName":(varName)}, and all qualifier names are case insensitive.
- logFile
- The full path name of the file to report errors in. Default is SQLImportDir.log in the directory loaded. If this parameter value is 1, a separate log file will be generated for each file loaded. The name of the log file will be the same as the file imported, but with the extension .log instead of .sql.
- extensions
- A comma delimited list of file extensions to import. Defaults to "sql".
- eosDelimiter
- End of statement delimiter. Will default to an appropriate value based on the value of %DDLMode.
- recurse
- 0/1. If 1, then subdirectories of directory will be recursively imported. This parameter defaults to 0.
- messageMode
- 0/1. If true then all messages reported by executing imported statements will be displayed on the current device. Default is true.
- echoMode
- 0/1. If true, all statement source is displayed on the current device. Default is true.
- errorPause
- The number of seconds to pause when an error is reported. The default is five seconds.
- logFile
Examples:
- Do $SYSTEM.SQL.Schema.ImportDir("Sybase","C:\Work\db1\",{"logFile":"C:\Work\db1\import.log","extensions":"sql","eosDelimiter":";"})
- Do $SYSTEM.SQL.Schema.ImportDir("Sybase","C:\Work\db1\",{"logFile":"C:\Work\db1\import.log","extensions":"sql,tab,sp","eosDelimiter":";","messageMode":1})
- Do $SYSTEM.SQL.Schema.ImportDir("Sybase","C:\Work\db1\",{"eosDelimiter":";"})
The Informix DDL/DML Import Utility supports the following statements:
- CREATE TABLE ...
- ALTER TABLE ...
- CREATE INDEX ...
- CREATE VIEW ...
- SET OPTION ...
- GRANT { ALTER | SELECT | INSERT | UPDATE | DELETE | REFERENCES | ALL PRIVILEGES } ...
The Interbase DDL/DML Import Utility supports the following statements:
- CREATE TABLE ...
- ALTER TABLE ...
- CREATE INDEX ...
- CREATE UNIQUE INDEX ...
- CREATE VIEW ...
- CREATE ROLE ...
The MS SQL Server DDL/DML Import Utility supports the following statements:
- CREATE [GLOBAL TEMPORARY] TABLE ...
- CREATE VIEW ...
- ALTER TABLE ...
- CREATE INDEX ...
- CREATE CLUSTERED INDEX ...
- CREATE UNIQUE INDEX ...
- INSERT ...
- UPDATE ...
- DELETE ...
- SET OPTION ...
The Oracle DDL/DML Import Utility supports the following statements:
- CREATE TABLE ...
- CREATE VIEW ...
- ALTER TABLE ...
- CREATE INDEX ...
- CREATE UNIQUE INDEX ...
- DROP TABLE ...
- DROP VIEW ...
- DROP INDEX ...
- INSERT ...
- UPDATE ...
- DELETE ...
- SET OPTION ...
- CREATE USER ...
- CREATE ROLE ...
- GRANT { SELECT | INSERT | UPDATE | DELETE | REFERENCES | ALL PRIVILEGES } ...
The Sybase DDL/DML Import Utility supports the following statements:
- CREATE [GLOBAL TEMPORARY] TABLE ...
- CREATE VIEW ...
- ALTER TABLE ...
- CREATE INDEX ...
- CREATE CLUSTERED INDEX ...
- CREATE UNIQUE INDEX ...
- INSERT ...
- UPDATE ...
- DELETE ...
- SET OPTION ...
- GRANT CONNECT ... (Same as SQL CREATE USER ...)
- GRANT { ALTER | SELECT | INSERT | UPDATE | DELETE | REFERENCES | ALL PRIVILEGES } ...
TSQL source files can contain any TSQL syntax supported by InterSystems IRIS TSQL.
This API will put the caller to the SQL Shell in the default TSQL dialect as defined in the TSQL Compatibility Settings.
The caller can then use the run [filename] command from the shell to import the script file.
Parameters:
- procname
- Name of the stored procedure to check.
procname can be qualified or unqualified. If unqualified, the default schema is applied. - metadata
- Passed by reference, optional argument.
If the stored procedure exists, returns a $LIST with the following information about the procedure: $ListBuild(SchemaName,ProcedureName,Classname that projected the stored procedure,Procedure type)
Examples:
- Write $SYSTEM.SQL.Schema.ProcedureExists("SQLUser.stpSalaryReport") // Writes a 1 if procedure SQLUser.stpSalaryReport exists
- Write $SYSTEM.SQL.Schema.ProcedureExists("stpSalaryReport",.metadata) // Writes a 1 if procedure [DefaultSchema].stpSalaryReport exists, returns metadata=$lb("SQLUser","stpSalaryReport","User.stpSalaryReport","function")
Notes:
- If the user calling the function does not hold any privileges for the procedure, 0 will be returned.
- If a class exists that would project this procedure to SQL during compilation, but the class has not been compiled, 0 will be returned.
- If a procedure is marked as hidden, 0 will be returned.
- metadata will be set to "" if 0 is returned by the function.
This configuration setting provides the ability to define a default schema name other than SQLUser, the default. When an unqualified table name is encountered in an SQL statement (and there is no #import statement specified), the default schema will be used. This setting has nothing to do with the mappings between SQL schema names and the class package name, it only specifies the default schema.
Parameter:
- schema
- String containing the default SQL schema name. If schema is "" or not defined, the default schema will be set to SQLUser.
- oldval
- Passed By Reference. Contains the previous value of the setting.
- Namespace
- Boolean 1/0 flag. If TRUE, set the default schema for the current namespace only. The default for Namespace is FALSE
- Status Code
Parameters:
- tablename
- Name of the table to check.
tablename can be qualified or unqualified. If unqualified, the default schema is applied. - metadata
- Passed by reference, optional argument.
If the table exists, returns a $LIST with the following information about the table: $ListBuild(SchemaName,TableName,Classname that projected the table,System Flag)
Examples:
- Write $SYSTEM.SQL.Schema.TableExists("Sample.Person") // Writes a 1 if table Sample.Person exists
- Write $SYSTEM.SQL.Schema.TableExists("Sample.Person",.metadata) // Writes a 1 if table Sample.Person exists, returns metadata=$lb("Sample","Person","Sample.Person","0")
Notes:
- If the user calling the function does not hold any privileges for the table, 0 will be returned.
- If a class exists that would project this table to SQL during compilation, but the class has not been compiled, 0 will be returned.
- metadata will be set to "" if 0 is returned by the function.
This utility can be called via $SYSTEM.SQL.Schema.ValidateTable(tablename) or as the %SYSTEM_SQL.Schema_ValidateTable(tablename) stored procedure. This method/procedure returns a resultset that contains a row for each issue found with the table's data. If the resultset is empty, the table has no data validation issues. The table's data is checked for the following:
Parameters:
- tablename
- Name of the table to validate data. Must be a table name, not a view name.
- index
- Internal use only
Returns:
Example:
Notes:
- After calling $SYSTEM.SQL.Schema.ValidateTable(...), the resultset will be held in the %sqlcontext object. Do %sqlcontext.%Display() will dump the results to the current device
- If the table is sharded, this should be called on the shard master table
- There is no locking performed by the utility. If run on a table in a live system, you could receive false-positive error reports
Parameters:
- viewname
- Name of the view to check.
viewname can be qualified or unqualified. If unqualified, the default schema is applied. - metadata
- Passed by reference, optional argument.
If the view exists, returns a $LIST with the following information about the view: $ListBuild(SchemaName,ViewName,Classname that projected the view,System Flag)
Examples:
- Write $SYSTEM.SQL.Schema.ViewExists("SQLUser.STestView") // Writes a 1 if view SQLUser.STestView exists
- Write $SYSTEM.SQL.Schema.ViewExists("STestView",.metadata) // Writes a 1 if view [DefaultSchema].STestView exists, returns metadata=$lb("SQLUser","STestView","User.STestView","0")
Notes:
- If the user calling the function does not hold any privileges for the view, 0 will be returned.
- If a class exists that would project this view to SQL during compilation, but the class has not been compiled, 0 will be returned.
- If a class that projects the view is marked as hidden, 0 will be returned.
- metadata will be set to "" if 0 is returned by the function.