How Do You Create an Opaque Data Type?
To create a new opaque data type, follow these steps:
1. Create the internal structure (a C data structure) for the opaque data type.
2. Write the support functions as C-language functions.
3. Register the opaque data type in the database with the CREATE OPAQUE TYPE statement.
4. Register the support functions of the opaque data type with the CREATE FUNCTION statement.
5. Provide access to the opaque data type and its support functions with the GRANT statement.
6. Write any SQL-invoked functions that are needed to support the opaque data type.
7. Provide any customized secondary access methods that the opaque data type might need.
The following sections describe each of these steps.
Creating the Internal Structure
The internal structure of an opaque data type is a C data structure. For the internal structure, use the C typedefs that the DataBlade API supplies for those fields whose size might vary by platform. Use of these typedefs, such as mi_integer and mi_float, improves the portability of the opaque data type. For more information on these data types, see Chapter 1 of the DataBlade API Programmer's Manual.
The internal structure uniquely names the opaque data type. Informix recommends that you develop a unique prefix for the opaque data type. You can prepend this prefix to each member of the internal structure and to the structure itself. A convention that Informix follows for opaque data types is to append the string _t to the structure name. For example, the circle_t data structure holds the values for the circle opaque type.
When you create the internal structure, consider the following impacts of the size of this structure:
You provide this information when you create the opaque data type with the CREATE OPAQUE TYPE statement.
Final Structure Size
To save space in the database, you should lay out internal structures as compactly as possible. The database server stores values in their internal representation, so any internal structure with padding between entries consumes unnecessary space.
You supply the final size of the internal structure with the INTERNALLENGTH keyword of the CREATE OPAQUE TYPE statement. This keyword provides the following two ways to specify the size:
A Fixed-Length Opaque Data Type
When you specify the actual size for INTERNALLENGTH, you create a fixed-length opaque type. The size of a fixed-length opaque type must match the value that the C-language sizeof() directive returns for the internal structure.
On most compilers, the sizeof() directive rounds up to the nearest four-byte size to ensure that pointer match on arrays of structures works correctly. However, you do not need to round up for the size of a fixed-length opaque data type. Instead you can specify alignment for the opaque type with the ALIGNMENT modifier. For more information, see "Memory Alignment".
For an example of a fixed-length opaque type, see "A Fixed-Length Opaque Type: circle".
A Varying-Length Opaque Data Type
When you specify the VARIABLE keyword for the INTERNALLENGTH modifier, you create a varying-length opaque type. By default, the maximum size for a varying-length opaque type is 2 kilobytes.
To specify a different maximum size for a varying-length opaque type, use the MAXLEN modifier. You can specify a maximum length of up to 32 kilobytes. When you specify a MAXLEN value, Universal Server can optimize resource allocation for the opaque type. If the size of the data for an opaque type exceeds the MAXLEN value, Universal Server returns an error.
For example, the following CREATE OPAQUE TYPE statement defines a varying-length opaque type called var_type whose maximum size is 4 kilobytes:
Only the last member of the internal structure can be of varying size.
For an example of a varying-length opaque type, see "A Varying-Length Opaque Type: image".
Memory Alignment
When Universal Server passes the data type to a user-defined routine, it aligns opaque-type data on a specified byte boundary. Alignment requirements depend on the C definition of the opaque type and on the system (hardware and compiler) on which the opaque data type is compiled.
You can specify the memory-alignment requirement for your opaque type with the ALIGNMENT modifier of the CREATE OPAQUE TYPE statement. The following table summarizes valid alignment values.
Structures that begin with single-byte characters, char, can be aligned anywhere. Arrays of a data type should follow the same alignment restrictions as the data type itself.
For example, the following CREATE OPAQUE TYPE statement specifies a fixed-length opaque type, called LongLong, of 18 bytes that must be aligned on a 1-byte boundary:
If you do not include the ALIGNMENT modifier in the CREATE OPAQUE TYPE statement, the default alignment is a 4-byte boundary.
Parameter Passing
Universal Server can pass opaque-type values to a user-defined routine in either of the following ways:
By default, the database server passes all opaque types by reference. To have the database server pass an opaque type by value, specify the PASSEDBYVALUE modifier in the CREATE OPAQUE TYPE statement. Only an opaque type whose size is 4 bytes or smaller can be passed by value. However, the DataBlade API data type mi_real, although only 4 bytes in length, is always passed by reference.
The following CREATE OPAQUE TYPE statement specifies that the two_bytes opaque type be passed by value:
Writing the Support Functions
Support functions are user-defined functions that you write in the C language. These user-defined functions are called external functions. Once you code the support functions, you compile them and place the compiled versions in a shared library on the server computer. You provide the pathname for these shared libraries in the CREATE FUNCTION statement when you register the support functions (see "Registering Support Functions"). You must ensure that the database server can access the shared libraries at runtime.
For general information on how to write external functions, see Extending INFORMIX-Universal Server: User-Defined Routines. For more information on the purpose of opaque-type support functions, see Chapter 6, "Writing Support Functions."
Registering the Opaque Type with the Database
Once you have created the internal structure and support functions for the opaque data type, use the following SQL statements to register them with the database:
Registering the Opaque Data Type
The CREATE OPAQUE TYPE statement registers an opaque type with the database. It provides the following information to the database:
The opaque-type name is the name of the data type that SQL statements use. It does not have to be the name of the internal structure for the opaque type. You might find it useful to create a special prefix to identify the data type as an opaque type. The opaque-type name must be unique within the name space.
You specify this size information with the INTERNALLENGTH modifier. It indicates whether the type is a fixed-length or varying-length opaque type. For more information, see "Creating the Internal Structure".
The CREATE OPAQUE TYPE statement can specify the following modifiers for an opaque type: MAXLEN, PASSEDBYVALUE, CANNOTHASH, and ALIGNMENT. You determine this information when you create the internal structure for the opaque data type. For more information, see "Creating the Internal Structure".
The CREATE OPAQUE TYPE statement stores this information in the sysxtdtypes system catalog table. When it stores a new opaque type in sysxtdtypes, the CREATE OPAQUE TYPE statement causes a unique value, called an extended identifier, to be assigned to the opaque type. Throughout the system catalog, an opaque data type is identified by its extended identifier, not by its name. (For more information on the columns of the sysxtdtypes system catalog, see the chapter on system catalog tables in the Informix Guide to SQL: Reference.)
To register a new opaque data type in a database, you must have the Resource privilege on that database. By default, a new opaque type has Usage permission assigned to the owner. For information on how to change the permission of an opaque type, see "Granting Privileges for an Opaque Data Type".
Once you have registered the opaque type, you can use the type in SQL statements and in user-defined routines. For more information on the syntax of the CREATE OPAQUE TYPE statement, see the description in the Informix Guide to SQL: Syntax.
Registering Support Functions
Use the CREATE FUNCTION statement to register a support function with the database. Because support functions must be written in an external language (currently the C language), the CREATE FUNCTION statement that registers a support function has the syntax that Figure 5-1 shows.
This SQL statement provides the following information to the database:
When you register a support function with the CREATE FUNCTION syntax that Figure 5-1 shows, use the appropriate SQL data types for parameter_list and ret_type, as follows.
In the preceding table, opaque data type is the name of the data type that you specify in the CREATE OPAQUE TYPE statement. For more information, see "Registering the Opaque Data Type".
The CREATE FUNCTION statement stores this information in the sysprocedures system catalog table. When it stores a new support function in sysprocedures, the CREATE FUNCTION statement causes a unique value, called an routine identifier, to be assigned to the support function. Throughout the system catalog, a support function is identified by its routine identifier, not by its name.
By default, a new support function has Execute permission assigned to the owner. For information on how to change the permission of an support function, see "Privileges on the Support Functions".
You cannot use the CREATE FUNCTION directly in an INFORMIX-ESQL/C program. To register an opaque-type support function from within an ESQL/C application, you must put the CREATE FUNCTION statement in an operating-system file. Then use the CREATE FUNCTION FROM statement to identify the location of this file. The CREATE FUNCTION FROM statement sends the contents of the operating-system file to the database server for execution. 
For more information on the syntax of the CREATE FUNCTION and CREATE FUNCTION FROM statements, see their descriptions in the Informix Guide to SQL: Syntax.
Creating the Casts
For each of the support functions in the following table, Universal Server uses a companion cast definition to convert the opaque data type to a particular internal data type.
For the database server to perform these casts, you must create the casts with the CREATE CAST statement. The database server can then call the appropriate support function when it needs to cast opaque-type data to or from the LVARCHAR, SENDRECV, IMPEXP, or IMPEXPBIN data types.
The CREATE CAST statement stores information about casting functions in the syscasts system catalog table. For more information on the CREATE CAST statement, see the description in the Informix Guide to SQL: Syntax. For a description of casting, see the Informix Guide to SQL: Tutorial.
Granting Privileges for an Opaque Data Type
Once you have created the opaque type and registered it with the database, use the GRANT statement to define the following privileges on this data type:
Privileges on the Opaque Data Type
To create a new opaque type within a database, you must have the Resource privilege on the database. The CREATE OPAQUE TYPE statement creates a new opaque type with the Usage privilege granted to the owner of the opaque type and the DBA. To use the opaque data type in an SQL statement, you must have the Usage privilege. The owner can grant the Usage privilege to other users with the USAGE ON TYPE clause of the GRANT statement.
The database server checks for the Usage privilege whenever the opaque-type name appears in an SQL statement (such as a column type in CREATE TABLE or a cast type in CREATE CAST). The database server does not check for the Usage privilege when an SQL statement:
The Select, Insert, Update, and Delete table-level privileges determine access to a column.
The Execute routine-level privilege determines access to a user-defined routine.
For example, the following GRANT statement assigns the Usage privilege on the circle opaque type to the user dexter:
The sysxtdtypeauth system catalog table stores type-level privileges. This table contains privileges for each opaque and distinct data type that is defined in the database. The table contains one row for each set of privileges granted.
Privileges on the Support Functions
To register a support function within a database, you must have the Resource privilege on the database. The CREATE FUNCTION statement registers the new support function with the Execute privilege granted to the owner of the support function and the DBA. Such a function is called an owner-privileged function. To execute a support function in an SQL statement, you must have the Execute privilege. Usually, this default privilege is adequate for support functions that are implicit casts because they should not generally be called within SQL statements. Support functions that are explicit casts might have the Execute privilege granted so that users can call them explicitly. The owner grants the Execute privilege to other users with the EXECUTE ON clause of the GRANT statement.
The sysprocauth system catalog table stores routine-level privileges. This table contains privileges for each user-defined routine and therefore for all support functions that are defined in the database. The table contains one row for each set of privileges granted.
Creating SQL-Invoked Functions
An SQL-invoked function is a user-defined function that an end user can explicitly call in an SQL statement. You might write SQL-invoked functions to extend the functionality of an opaque data type in the following ways:
The versions of these functions that Universal Server defines handle the built-in data types. For your opaque data type to use any of these functions, you can write a version of the function that handles the opaque data type. (For more information on the details of writing user-defined functions, see Extending INFORMIX-Universal Server: User-Defined Routines.)
Operating on Data
Universal Server supports the following types of SQL-invoked functions that allow you to operate on data in expressions of SQL statements:
Operator Functions for Opaque Data Types
Universal Server provides the following types of operators for expressions in SQL statements:
Universal Server provides operator functions for the arithmetic operators (see Figure 2-4) and text operators (Figure 2-5). The versions of the operator functions that Universal Server provides handle the built-in data types. You can write a new version of one of these operator function to provide the associated operation on your new opaque data type.
If you write a new version of an operator function, make sure you follow these rules:
1. The name of the operator function must match the name that Figure 2-4 or Figure 2-5 lists. However, the name is not case sensitive; the plus() function is the same as the Plus() function.
2. The operator function must handle the correct number of parameters.
3. The operator function must return the correct data type, where appropriate.
Built-In Functions for Opaque Data Types
Universal Server provides special SQL-invoked functions, called built-in functions, that provide some basic mathematical operations. Figure 2-7 shows the built-in functions that Universal Server defines. The versions of the built-in functions that Universal Server provides handle the built-in data types. You can write a new version of a built-in function to provide the associated operation on your new opaque data type. If you write a new version of a built-in function, make sure you follow these rules:
1. The name of the built-in function must match the name that Figure 2-7 lists. However, the name is not case sensitive; the abs() function is the same as the Abs() function.
2. The built-in function must be one that can be overridden.
3. The built-in function must handle the correct number of parameters, and these parameters must be of the correct data type. Figure 2-7 lists the number and data types of the parameters.
4. The built-in function must return the correct data type, where appropriate.
For more information on built-in functions, see page 2-8.
Aggregrate Functions for Opaque Data Types
Universal Server supports only the following aggregate functions on an opaque data type:
For any other aggregate function to work with your opaque type, you must create your own version of the aggregate function. For more information on aggregate functions, see the Expression segment in the Informix Guide to SQL: Syntax.
Comparing Data
Universal Server supports the following types of functions that allow you to compare data in expressions of SQL statements:
Conditions for Opaque Data Types
Universal Server supports the following conditions on an opaque type in the conditional clause of SQL statements:
For more information on the conditional clause, see the Condition segment in the Informix Guide to SQL: Syntax. For more information on the equal() function, see "Relational Operators for Opaque Data Types". For more information on the compare() function, see "Opaque-Type Sorting".
Relational Operators for Opaque Data Types
Universal Server provides operator functions for the relational operators that Figure 2-6 shows. The versions of the relational-operator functions that Universal Server provides handle the built-in data types. You can write a new version of a relational-operator function to provide the associated operation on your new opaque data type. If you write a new version of a relational-operator function, make sure you follow these rules:
1. The name of the relational-operator function must match the name that Figure 2-6 lists. However, the name is not case sensitive; the equal() function is the same as the Equal() function.
2. The relational-operator function must take two parameters, both of the opaque data type.
3. The relational-operator function must be a Boolean function; that is, it must return a BOOLEAN value.
You must define an equal() function to handle your opaque data type if you want to allow columns of this type to be:
The equal() function can only compare bit-hashable data types; that is, equality can be determined with a bit-wise compare. This comparison means that two values are equal if they have the same internal representation. The database server uses a built-in hash function to perform this comparison.
If your opaque type is not bit-hashable, the database server cannot use its built-in hash function for the equality comparison. Therefore, you cannot use the opaque type in the following cases:
For opaque types that are not bit-hashable, specify the CANNOTHASH modifier in the CREATE OPAQUE TYPE statement.
Opaque-Type Sorting
The compare() function is an SQL-invoked function that sorts the target data type. Universal Server uses the compare() function to execute the following clauses and keywords of the SELECT statement:
The database server also uses the compare() function to evaluate the BETWEEN operator in the condition of an SQL statement. For more information on conditional clauses, see the Condition segment in the Informix Guide to SQL: Syntax.
Universal Server provides versions of the compare() function that handle the built-in data types. For the database server be able to sort an opaque type, you must define a compare() function that handles this opaque type. If you write a new version of a compare() function, make sure you follow these rules:
1. The name of the function must be compare(). The name, however, is not case sensitive; the compare() function is the same as the Compare() function.
2. The function must accept two arguments, each of the data type to be compared.
3. The function must return an integer value to indicate the result of the comparison, as follows:
If your opaque type is not bit-hashable, the compare() function should generate an error so that the database server does not use the default compare() function.
The compare() function is also the support function for the default operator class of the B-tree secondary access method. For more information, see "The Generic B-Tree Index".
Creating End-User Routines
Universal Server allows you to define SQL-invoked functions and procedures that the end user can use in expressions of SQL statements. These end-user routines provide additional functionality that an end user might need to work with the opaque data type. To create an end-user routine, follow these steps:
You might also write end-user functions to serve as additional casting functions for the opaque type. To create a casting function, you must use the CREATE FUNCTION statement to register the end-user function and the CREATE CAST function to register this function as a casting function.
For more information about how to write end-user routines, see "End-User Routines".
Customizing Use of Access Methods
Universal Server provides the full implementation of the generic B-tree secondary access method, and it provides definitions for the R-tree secondary access method. By default, the CREATE INDEX statement (without the USING clause) builds a generic B-tree index for the column or user-defined function.
When you create an opaque data type, you must ensure that secondary access methods exist that support the new data type. Consider the following factors about the secondary access methods and their support for the opaque data type:
To create an index of a particular secondary access method on a column of an opaque data type, the database server must find an operator class that is associated with the secondary access method. This operator class must specify operations (strategy functions) on the opaque type as well as the functions that the secondary access method uses (support functions).
For more information about an operator class and operator-class functions, see "What Is an Operator Class?".
Using the Generic B-Tree
The generic B-tree secondary access method has a default operator class, btree_ops, whose operator-class functions handle indexing for the built-in data types. These operator-class functions have the following functionality for built-in data types:
If this sequence is not logical for your opaque data type, you can define operator-class functions for the opaque data type that provide the sequence you need.
If the opaque data type holds more than one value but you can define a single value for it, you can define operator-class functions for the opaque data type that compare two of these one-dimensional values. If you cannot define a one-dimensional value for the opaque data type, you cannot use a B-tree index as its secondary access method. For more information, see "A Fixed-Length Opaque Type: circle".
To provide support for columns and user-defined functions of your opaque type, you can extend the btree_ops operator-class functions so that they handle the new opaque data type. The generic B-tree secondary access method uses the new operator-class functions to store values of your opaque data type in a B-tree index.
For more information about how to extend the default B-tree operator class, see "Extending the btree_ops Operator Class". For an example of how to extend the btree_ops operator class for a fixed-length opaque type, see "A Fixed-Length Opaque Type: circle".
Indexing Spatial Data
The way that the generic B-tree secondary access method orders data is useful for one-dimensional data. The operator-class functions (strategy and support functions) for the default B-tree operator class, btree_ops, order one-dimensional values within the B-tree index. If the data in your opaque data type is multidimensional, you might need to use a secondary access method that can order the multidimensional data.
The R-tree secondary access method is useful for spatial or multidimensional data such as maps and diagrams. If your database implements an R-tree, you can create an R-tree index on the spatial-type columns that are likely to be used in a search condition.
For information about how to create an R-tree index, see "The R-Tree Index". For information on how to extend the default R-tree operator class, see "Extending the rtree_ops Operator Class".
Other Kinds of Data
Your opaque data type might have data that is not optimally indexed by a generic B-tree or an R-tree. Often, DataBlade modules that define new opaque data types provide their own secondary access methods for these data types. For more information on the secondary access methods, check the user guide for your DataBlade module. For information, see "Other User-Defined Secondary Access Methods".
|
