23.2. An Example of a Simple Type Mapping to a Database Schema
Listing 23.1 shows the simple type partNameType, first seen in Listing 10.1.
Listing 23.1. A Simple Type Derived from a Token (catalog.xsd)
<xsd:simpleType name="partNameType"
final="list,union"
id="catalog.partName.sType">
<xsd:annotation>
<xsd:documentation xml:lang="en">
A part name can be almost anything.
The name is a short description.
</xsd:documentation>
</xsd:annotation>
<xsd:restriction base="xsd:token"
id="pnt-rst">
<xsd:minLength value="1"/>
<xsd:maxLength value="40"/>
</xsd:restriction>
</xsd:simpleType> |
Mapping this to a database schema involves creating a database column of type token, entering a description of the column, and enforcing the minLength and maxLength constraining facets. Listing 23.2 creates the PartNameTypeExample table, using a check constraint.
Listing 23.2. Mapping a Simple Type Derived from a Token to a Database Representation (catalog.xsd)
CREATE TABLE PartNameTypeExample (
partNameType VARCHAR2(40) NOT NULL
CHECK (INSTR(partNameType, UNISTR(' 00D')) = 0 AND
INSTR(partNameType, UNISTR(' 00A')) = 0 AND
INSTR(partNameType, UNISTR(' 009')) = 0 AND
LENGTH(partNameType) =
LENGTH(TRIM(partNameType)) AND
INSTR(partNameType, ' ') = 0 AND
LENGTH(partNameType) >= 1)
);
--Add a column comment to match the annotation.
COMMENT ON COLUMN PartNameTypeExample.partNameType IS
' A part name can be almost anything.
The name is a short description.'; |
Given the preceding table declaration, the following is a valid insert statement:
INSERT INTO PartNameTypeExample
VALUES
('Short Description of Unit 1'), Listing 23.3 iterates Listings 10.2 and 10.3 and shows the XML schema document representation of partNumberType and an assemblyPartNumberType.
Listing 23.3. Restricting a Custom Simple Type (catalog.xsd)
<xsd:simpleType name="partNumberType"
final="union"
id="catalog.partNumber.sType">
<xsd:annotation>
<xsd:documentation xml:lang="en">
Declaration of a part number.
Each part number consists of one to
three alphabetic characters followed by
one to eight digits. The following part
numbers, for example, are valid:
J1
ABC32897
ZZ22233344
</xsd:documentation>
</xsd:annotation>
<xsd:restriction base="xsd:token">
<xsd:pattern value="[A-Z]{1,3}d{1,8}"/>
</xsd:restriction>
</xsd:simpleType>
<xsd:simpleType name="assemblyPartNumberType"
final="#all"
id="catalog.assemblypartNumber.sType">
<xsd:annotation>
<xsd:documentation xml:lang="en">
An "assembly" represents a pre-built
collection of unit items. The
part number for an assembly
always starts with "ASM."
</xsd:documentation>
</xsd:annotation>
<xsd:restriction base="partNumberType">
<xsd:pattern value="ASMd{1,8}"/>
</xsd:restriction>
</xsd:simpleType> |
Mapping assemblyPartNumberType to a database schema involves creating a database column of type token, entering a description of the column, enforcing the minLength and maxLength constraining facets, and enforcing the pattern facet. Listing 23.4 creates an AssemblyPartNumberTypeExample table, using a check constraint.
Listing 23.4. Mapping a Pattern Simple Type Derived from a Token to a Database Representation (catalog.xsd)
CREATE TABLE AssemblyPartNumberTypeExample (
assemblyPartNumberType VARCHAR2(40) NOT NULL
CHECK (INSTR(assemblyPartNumberType, UNISTR(' 00D')) = 0 AND
INSTR(assemblyPartNumberType, UNISTR(' 00A')) = 0 AND
INSTR(assemblyPartNumberType, UNISTR(' 009')) = 0 AND
LENGTH(assemblyPartNumberType) =
LENGTH(TRIM(assemblyPartNumberType)) AND
INSTR(assemblyPartNumberType, ' ') = 0 AND
LENGTH(assemblyPartNumberType) BETWEEN 4 AND 11 AND
SUBSTR(assemblyPartNumberType, 1, 3) = 'ASM' AND
-- If the value is not a number, then an
-- error will be thrown. IS NOT NULL makes
-- this valid SQL, but is meaningless.
CAST(SUBSTR(assemblyPartNumberType, 4, 8) AS NUMBER)
IS NOT NULL)
);
COMMENT ON COLUMN
AssemblyPartNumberTypeExample.assemblyPartNumberType IS
'An "assembly" represents a pre-built collection of unit items.[ccc]
The part number for an assembly always starts with "ASM."'; |
Given the preceding table declaration, the following is a valid insert statement:
INSERT INTO AssemblyPartNumberTypeExample
VALUES
('ASM4534'),