java.lang.Object
org.postgresql.pljava.management.Commands

@SQLAction(provides="sqlj.tables",install={"\tCREATE TABLE sqlj.jar_repository(\t\tjarId       SERIAL PRIMARY KEY,\t\tjarName     CHARACTER VARYING(100) UNIQUE NOT NULL,\t\tjarOrigin   CHARACTER VARYING(500) NOT NULL,\t\tjarOwner    pg_catalog.NAME NOT NULL,\t\tjarManifest pg_catalog.TEXT\t)","\tCOMMENT ON TABLE sqlj.jar_repository IS\t\'Information on jars loaded by PL/Java, one row per jar.\'","\tGRANT SELECT ON sqlj.jar_repository TO public","\tCREATE TABLE sqlj.jar_entry(\t\tentryId     SERIAL PRIMARY KEY,\t\tentryName   CHARACTER VARYING(200) NOT NULL,\t\tjarId       INT NOT NULL\t\t\t\t\tREFERENCES sqlj.jar_repository ON DELETE CASCADE,\t\tentryImage  pg_catalog.BYTEA NOT NULL,\t\tUNIQUE(jarId, entryName)\t)","\tCOMMENT ON TABLE sqlj.jar_entry IS\t\'Name and content of each entry in every jar loaded by PL/Java.\'","\tGRANT SELECT ON sqlj.jar_entry TO public","\tCREATE TABLE sqlj.jar_descriptor(\t\tjarId       INT REFERENCES sqlj.jar_repository ON DELETE CASCADE,\t\tordinal     pg_catalog.INT2,\t\tPRIMARY KEY (jarId, ordinal),\t\tentryId     INT NOT NULL REFERENCES sqlj.jar_entry ON DELETE CASCADE\t)","\tCOMMENT ON TABLE sqlj.jar_descriptor IS\t\'Associates each jar with zero-or-more deployment descriptors (a row for each), with ordinal indicating their order of mention in the manifest.\'","\tGRANT SELECT ON sqlj.jar_descriptor TO public","\tCREATE TABLE sqlj.classpath_entry(\t\tschemaName  CHARACTER VARYING(30) NOT NULL,\t\tordinal     pg_catalog.INT2 NOT NULL,\t\tjarId       INT NOT NULL\t\t\t\t\tREFERENCES sqlj.jar_repository ON DELETE CASCADE,\t\tPRIMARY KEY(schemaName, ordinal)\t)","\tCOMMENT ON TABLE sqlj.classpath_entry IS\t\'Associates each schema with zero-or-more jars (a row for each), with ordinal indicating their order of precedence in the classpath.\'","\tGRANT SELECT ON sqlj.classpath_entry TO public","\tCREATE TABLE sqlj.typemap_entry(\t\tmapId       SERIAL PRIMARY KEY,\t\tjavaName    CHARACTER VARYING(200) NOT NULL,\t\tsqlName     pg_catalog.NAME NOT NULL\t)","\tCOMMENT ON TABLE sqlj.typemap_entry IS\t\'A row for each SQL type <-> Java type custom mapping.\'","\tGRANT SELECT ON sqlj.typemap_entry TO public"},remove={"\tDROP TABLE sqlj.typemap_entry","\tDROP TABLE sqlj.jar_repository CASCADE"}) @SQLAction(provides="alias_java_language",install="\tSELECT \t\tpg_catalog.set_config(\'pljava.implementors\', \'alias_java_language,\' \t\t|| pg_catalog.current_setting(\'pljava.implementors\'), true)")
public class Commands
extends Object
This methods of this class are implementations of SQLJ procedures (and some related ones beyond what ISO 9075-13 specifies).

SQLJ procedures

install_jar

The install_jar procedure loads a jar file from a location appointed by an URL or a binary image that constitutes the contents of a jar file into the SQLJ jar repository. It is an error if a jar with the given name already exists in the repository.

Usage 1

SELECT sqlj.install_jar(<jar_url>, <jar_name>, <deploy>);

Parameters

Parameters for sqlj.install_jar(url...)
jar_url The URL that denotes the location of the jar that should be loaded
jar_name This is the name by which this jar can be referenced once it has been loaded
deploy True if the jar should be deployed according to a deployment descriptor, false otherwise

Usage 2

SELECT sqlj.install_jar(<jar_image>, <jar_name>, <deploy>);

Parameters

Parameters for sqlj.install_jar(bytea...)
jar_image The byte array that constitutes the contents of the jar that should be loaded
jar_name This is the name by which this jar can be referenced once it has been loaded
deploy True if the jar should be deployed according to a deployment descriptor, false otherwise

replace_jar

The replace_jar procedure will replace a loaded jar with another jar. Use this command to update already loaded files. It's an error if the jar is not found.

Usage 1

SELECT sqlj.replace_jar(<jar_url>, <jar_name>, <redeploy>);

Parameters

Parameters for sqlj.replace_jar(url...)
jar_url The URL that denotes the location of the jar that should be loaded
jar_name The name of the jar to be replaced
redeploy True if the old and new jar should be undeployed and deployed according to their respective deployment descriptors, false otherwise

Usage 2

SELECT sqlj.replace_jar(<jar_image>, <jar_name>, <redeploy>);

Parameters

Parameters for sqlj.replace_jar(bytea...)
jar_image The byte array that constitutes the contents of the jar that should be loaded
jar_name The name of the jar to be replaced
redeploy True if the old and new jar should be undeployed and deployed according to their respective deployment descriptors, false otherwise

remove_jar

The remove_jar procedure will drop the jar from the jar repository. Any classpath that references this jar will be updated accordingly. It's an error if no such jar is installed.

Usage

SELECT sqlj.remove_jar(<jar_name>, <undeploy>);

Parameters

Parameters for sqlj.remove_jar
jar_name The name of the jar to be removed
undeploy True if the jar should be undeployed according to its deployment descriptor, false otherwise

get_classpath

The get_classpath procedure will return the classpath that has been defined for the given schema or NULL if the schema has no classpath. It's an error if the given schema does not exist.

Usage

SELECT sqlj.get_classpath(<schema>);

Parameters

Parameters for sqlj.get_classpath
schema The name of the schema

set_classpath

The set_classpath procedure will define a classpath for the given schema. A classpath consists of a colon separated list of jar names. It's an error if the given schema does not exist or if one or more jar names references non-existent jars.

Usage

SELECT sqlj.set_classpath(<schema>, <classpath>);

Parameters

Parameters for sqlj.set_classpath
schema The name of the schema
classpath The colon separated list of jar names

add_type_mapping

The add_type_mapping procedure defines the mapping between an SQL type and a Java class.

Usage

SELECT sqlj.add_type_mapping(<sqlTypeName>, <className>);

Parameters

Parameters for sqlj.add_type_mapping
sqlTypeName The name of the SQL type. The name can be qualified with a schema (namespace). If the schema is omitted, it will be resolved according to the current setting of the search_path.
className The name of the class. The class must be found in the classpath in effect for the current schema

drop_type_mapping

The drop_type_mapping procedure removes the mapping between an SQL type and a Java class.

Usage

SELECT sqlj.drop_type_mapping(<sqlTypeName>);

Parameters

Parameters for sqlj.drop_type_mapping
sqlTypeName The name of the SQL type. The name can be qualified with a schema (namespace). If the schema is omitted, it will be resolved according to the current setting of the search_path.

alias_java_language

The alias_java_language procedure issues a PostgreSQL CREATE LANGUAGE command to define a named "language" that is an alias for PL/Java. The name can appear in the Java security policy to grant specific permissions to functions created in this "language".

Usage

SELECT sqlj.alias_java_language(<alias>, sandboxed => <boolean>);

Parameters

Parameters for sqlj.alias_java_language
alias The name desired for the language alias. Language names are not schema-qualified.
sandboxed Whether to create a sandboxed "TRUSTED" language, in which functions can be created by any role granted USAGE permission (true), or an unsandboxed one in which only superusers may create functions (false).
orReplace Optional parameter, default false. See the method documentation for details.
comment Optional parameter. If empty string (the default), a comment is supplied. See the method documentation for details.
Author:
Thomas Hallgren, Chapman Flack
  • Constructor Summary

    Constructors
    Constructor Description
    Commands()  
  • Method Summary

    Modifier and Type Method Description
    static void addTypeMapping​(String sqlTypeName, String javaClassName)
    Defines the mapping between an SQL type and a Java class.
    static void aliasJavaLanguage​(String alias, Boolean sandboxed, Boolean orReplace, String comment)
    Creates a named PostgreSQL LANGUAGE that refers to PL/Java; its name may be referred to in the Java security policy to grant selected permissions to functions created in this "language".
    static void dropTypeMapping​(String sqlTypeName)
    Drops the mapping between an SQL type and a Java class.
    static String getClassPath​(String schemaName)
    Return the classpath that has been defined for the schema named schemaName.
    static String getClassPath​(Lexicals.Identifier.Simple schema)  
    static void installJar​(byte[] image, String jarName, boolean deploy)
    Installs a new Jar in the database jar repository under name jarName.
    static void installJar​(String urlString, String jarName, boolean deploy)
    Installs a new Jar in the database jar repository under name jarName.
    static void removeJar​(String jarName, boolean undeploy)
    Removes the jar named jarName from the database jar repository.
    static void replaceJar​(byte[] jarImage, String jarName, boolean redeploy)
    Replaces the image of jar named jarName in the database jar repository.
    static void replaceJar​(String urlString, String jarName, boolean redeploy)
    Replaces the image of jar named jarName in the database jar repository.
    static void setClassPath​(String schemaName, String path)
    Define the class path to use for Java functions, triggers, and procedures that are created in the schema named schemaName.
    static void setClassPath​(Lexicals.Identifier.Simple schema, String path)  

    Methods inherited from class java.lang.Object

    clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait
  • Constructor Details

    • Commands

      public Commands()
  • Method Details

    • addTypeMapping

      @Function(schema="sqlj", name="add_type_mapping", security=DEFINER, requires="sqlj.tables") public static void addTypeMapping​(String sqlTypeName, String javaClassName) throws SQLException
      Defines the mapping between an SQL type and a Java class.
      Parameters:
      sqlTypeName - The name of the SQL type. The name can be qualified with a schema (namespace). If the schema is omitted, it will be resolved according to the current setting of the search_path.
      javaClassName - The name of the class. The class must be found in the classpath in effect for the current schema
      Throws:
      SQLException - if the type or class cannot be found, or if the invoking user does not own the type.
    • dropTypeMapping

      @Function(schema="sqlj", name="drop_type_mapping", security=DEFINER, requires="sqlj.tables") public static void dropTypeMapping​(String sqlTypeName) throws SQLException
      Drops the mapping between an SQL type and a Java class.
      Parameters:
      sqlTypeName - The name of the SQL type. The name can be qualified with a schema (namespace). If the schema is omitted, it will be resolved according to the current setting of the search_path.
      Throws:
      SQLException - if the type cannot be found, or if the invoking user does not own the type.
    • getClassPath

      @Function(schema="sqlj", name="get_classpath", security=DEFINER, requires="sqlj.tables") public static String getClassPath​(String schemaName) throws SQLException
      Return the classpath that has been defined for the schema named schemaName. This method is exposed in SQL as sqlj.get_classpath(VARCHAR).
      Parameters:
      schemaName - Name of the schema for which this path is valid.
      Returns:
      The defined classpath or null if this schema has no classpath.
      Throws:
      SQLException
    • getClassPath

      public static String getClassPath​(Lexicals.Identifier.Simple schema) throws SQLException
      Throws:
      SQLException
    • installJar

      @Function(schema="sqlj", name="install_jar", security=DEFINER, requires="sqlj.tables") public static void installJar​(byte[] image, String jarName, boolean deploy) throws SQLException
      Installs a new Jar in the database jar repository under name jarName. Once installed classpaths can be defined that refrences this jar. This method is exposed in SQL as sqlj.install_jar(BYTEA, VARCHAR, BOOLEAN).
      Parameters:
      image - The byte array that constitutes the jar content.
      jarName - The name by which the system will refer to this jar.
      deploy - If set, execute install commands found in the deployment descriptor.
      Throws:
      SQLException - if the jarName contains characters that are invalid or if the named jar already exists in the system.
      See Also:
      setClassPath(java.lang.String, java.lang.String)
    • installJar

      @Function(schema="sqlj", name="install_jar", security=DEFINER, requires="sqlj.tables") public static void installJar​(String urlString, String jarName, boolean deploy) throws SQLException
      Installs a new Jar in the database jar repository under name jarName. Once installed classpaths can be defined that refrences this jar. This method is exposed in SQL as sqlj.install_jar(VARCHAR, VARCHAR, BOOLEAN).
      Parameters:
      urlString - The location of the jar that will be installed.
      jarName - The name by which the system will refer to this jar.
      deploy - If set, execute install commands found in the deployment descriptor.
      Throws:
      SQLException - if the jarName contains characters that are invalid or if the named jar already exists in the system.
      See Also:
      setClassPath(java.lang.String, java.lang.String)
    • removeJar

      @Function(schema="sqlj", name="remove_jar", security=DEFINER, requires="sqlj.tables") public static void removeJar​(String jarName, boolean undeploy) throws SQLException
      Removes the jar named jarName from the database jar repository. Class path entries that references this jar will also be removed (just the entry, not the whole path). This method is exposed in SQL as sqlj.remove_jar(VARCHAR, BOOLEAN).
      Parameters:
      jarName - The name by which the system referes this jar.
      undeploy - If set, execute remove commands found in the deployment descriptor of the jar.
      Throws:
      SQLException - if the named jar cannot be found in the repository.
    • replaceJar

      @Function(schema="sqlj", name="replace_jar", security=DEFINER, requires="sqlj.tables") public static void replaceJar​(byte[] jarImage, String jarName, boolean redeploy) throws SQLException
      Replaces the image of jar named jarName in the database jar repository. This method is exposed in SQL as sqlj.replace_jar(BYTEA, VARCHAR, BOOLEAN).
      Parameters:
      jarImage - The byte array that constitutes the jar content.
      jarName - The name by which the system referes this jar.
      redeploy - If set, execute remove commands found in the deployment descriptor of the old jar and install commands found in the deployment descriptor of the new jar.
      Throws:
      SQLException - if the named jar cannot be found in the repository.
    • replaceJar

      @Function(schema="sqlj", name="replace_jar", security=DEFINER, requires="sqlj.tables") public static void replaceJar​(String urlString, String jarName, boolean redeploy) throws SQLException
      Replaces the image of jar named jarName in the database jar repository. This method is exposed in SQL as sqlj.replace_jar(VARCHAR, VARCHAR, BOOLEAN).
      Parameters:
      urlString - The location of the jar that will be installed.
      jarName - The name by which the system referes this jar.
      redeploy - If set, execute remove commands found in the deployment descriptor of the old jar and install commands found in the deployment descriptor of the new jar.
      Throws:
      SQLException - if the named jar cannot be found in the repository.
    • setClassPath

      @Function(schema="sqlj", name="set_classpath", security=DEFINER, requires="sqlj.tables") public static void setClassPath​(String schemaName, String path) throws SQLException
      Define the class path to use for Java functions, triggers, and procedures that are created in the schema named schemaName. This method is exposed in SQL as sqlj.set_classpath(VARCHAR, VARCHAR).
      Parameters:
      schemaName - Name of the schema for which this path is valid.
      path - Colon separated list of names. Each name must denote the name of a jar that is present in the jar repository.
      Throws:
      SQLException - If no schema can be found with the givene name, or if one or several names of the path denotes a nonexistant jar file.
    • setClassPath

      public static void setClassPath​(Lexicals.Identifier.Simple schema, String path) throws SQLException
      Throws:
      SQLException
    • aliasJavaLanguage

      @Function(schema="sqlj", name="alias_java_language", onNullInput=CALLED, requires="sqlj.tables", implementor="alias_java_language") public static void aliasJavaLanguage​(String alias, Boolean sandboxed, @SQLType(defaultValue="false") Boolean orReplace, @SQLType(defaultValue="") String comment) throws SQLException
      Creates a named PostgreSQL LANGUAGE that refers to PL/Java; its name may be referred to in the Java security policy to grant selected permissions to functions created in this "language".

      More on configuring Java permissions specific to this alias can be found in the policy documentation.

      PostgreSQL normally grants USAGE to PUBLIC if a sandboxed language is created. This routine does not, so that USAGE on the new alias can then be GRANTed to specific roles or to PUBLIC as desired.

      Parameters:
      alias - Name for this "language".
      sandboxed - Whether this alias should be a sandboxed/"TRUSTED" language that USAGE can be granted on, or an unsandboxed one that only superusers can create functions in. Must be specified.
      orReplace - Whether to succeed even if a language by the same name already exists; if so, the sandboxed bit, handler entry points, and comment may all be changed. Default is false.
      comment - A comment to associate with the alias "language". If an empty string (the default), a default comment will be constructed. Pass null explicitly to avoid setting any comment (or changing any existing comment, in the orReplace case).
      Throws:
      SQLException