Oracle: XSQL Combining SQL, Oracle Text, XSLT, and Java to Publish Dynamic Web Content

About the Author xix Chapter 1 Introducing Oracle XSQL 1 What XSQL Solves 2 The Problems of Web Application Development 2 XSQL as a Keystone Technology 5 XSQL and Other Oracle Technologies 11 Oracle Text 12 XMLSupport 12 Oracle JDeveloper 13 Introduction to XML 13 The Role of XML 13 Well-Formed versus Valid Documents 15 Document Structure 15 Processing Instructions 17 Attributes 17 Syntax Nitty-Gritty 18 Element Name and Attribute Restrictions 18 Special Characters 19 CDATA 20 Comments 20 Namespaces 21 Schemas 21 Moving On 22 Chapter 2 Getting Started with XSQL 23 Basic Installation 24 Installation Anatomy 24 Scratch XSQL Installation 26 Unix Prerequisites 26 Using Oracle Universal Installer 27 Configuring Java 29 Contents vii Installing in Existing Environments 30 Web and Application Server Integration 30 Servlet Engine Compatibility 30 Installing on a Servlet Engine 31 Configuring the Database 32 Configuring the Database Connection 33 Moving On 33 Chapter 3 Hello, XSQL! 35 Loading the XSQL Samples 35 Creating a Demo User and Table 37 Setting Connection Definitions 38 Your First XSQL Page 38 Your First XSLT Stylesheet 39 XSQLConfig.xml 41 XSQLConfigElement 41 Servlet Element 41 Processor Element 42 HttpElement 44 ConnectiondefsElement 44 ActiondefsElement 45 SerializerdefsElement 45 Security Issues 46 Known Issues 46 The XSQLConfig.xmlFile 46 SQLPoisoning 47 Denial-of-Service Attacks 49 An Example Security Hole 50 Thinking about XSQL Security 50 Provide Few Opportunities 51 Keep Up-to-Date 51 Always Consider Security in Application Design 51 The Multilateral Approach 52 Moving On 52 Chapter 4 XSQL Architecture 53 Overview 53 Java Parts 54 Java Virtual Machine 55 Driver 56 Servlet Engine 56 JSPRuntime 57 Faces Of XSQL 58 XSQL Command Line Utility 58 XSQLRequest Class 59 XSQLServlet 60 XSQL Page Processor 60 viii Contents Oracle XML Modules 63 XMLParser 63 XML SQL 64 XSLT Processor 64 Core XSQL Components 65 XSQLPages 65 Actions 66 Action Handlers 66 Serializers 67 Moving On 68 Chapter 5 Writing XSQL Pages 69 Querying the Database 69 Multiple xsql:queryActions 73 xsql:queryDetails 75 xsql:no-rows-query 77 Canonical Schema 77 Formatting Dates 80 Other Built-in Actions 82 xsql:dml 82 xsql:ref-cursor-function 83 xsql:include-owa 83 xsql:include-request-params 84 xsql:include-param 85 xsql:include-xml 85 xsql:set-page-param 85 xsql:set-session-param 86 xsql:set-cookie 87 xsql:set-stylesheet-param 88 xsql:action 89 xsql:include-xsql 89 xsql:insert-request 90 xsql:update-request 91 xsql:delete-request 92 xsql:insert-param 93 Linking to XSLT Stylesheets 94 Processing Instruction Details 95 Choosing Stylesheets Based on Client Type 96 Moving On 96 Chapter 6 XSQL Parameters 97 Referencing Parameters 98 Parameter XML Representation 100 Parameter Types 102 Request Parameters 102 Page-Private Parameters 104 Session Parameters 104 Cookies 106 Contents ix Setting Default Parameters 108 Using Bind Variables 109 Resolving Conflicting Parameter Names 110 Stylesheets and Parameters 111 Moving On 113 Chapter 7 Database Modifications with XSQL 115 Inputting HTML Forms 115 Form Parameters and the Canonical Schema 116 Using xsql:insert-request 119 Using xsql:update-request 120 Using xsql:delete-request 121 Modifying with xsql:dml 122 Handling XML Documents 123 Handling Posted XML 124 Handling XML Parameters 128 Inserting XML as XML 129 Moving On 130 Chapter 8 Oracle SQL 131 SQLSyntax 131 Case Sensitivity 131 Lexical Conventions 132 Data Types 132 String Types 133 Number Types 133 Date Types 134 Large-Object Types 134 Raw Type 136 Other Types 136 Operators 137 SQL Expressions 137 Describing Tables 139 Select Queries 139 Target Clause 140 Types of Target Objects 140 Multiple Target Objects and Joins 140 Target Aliasing 141 Subqueries as Targets 142 Elements Clause 143 Element Expressions 143 Expression Aliasing 144 Distinct Keyword 147 WhereClause 148 How the Where Clause Works 148 Comparison Operators 151 String-Matching Operators 153 x Contents Logical Operators 153 Joins Examined 156 Order ByClause 158 Working with Sets 159 The Imaginary Dual Table 160 Managing Tables 161 Creating Tables 161 Altering Tables 164 Working with Columns 164 Dropping Tables 166 Adding and Modifying Data 167 Transactions 167 Sequences 168 InsertStatements 170 UpdateStatements 171 Delete and TruncateStatements 172 Views 172 Creating and Altering Views 172 Using Views 174 Constraints 175 Types of Constraints 175 Using Constraints 177 Date Formatting 178 XSQL Dates versus Oracle Dates 178 Date-Format Elements 180 SQLFunctions 182 Using Aggregate Functions 183 GROUP BYClauses 183 Aggregate Functions and DISTINCT Keyword 184 Avg 185 Count 186 Max 186 Min 187 Stddev 188 Sum 190 Variance 191 General-Purpose Numeric Functions 192 Abs 192 Bin_to_num 193 Ceil 193 Floor 193 Greatest 194 Least 194 Mod 195 Power 195 Round 196 Contents xi Sign 196 Sqrt 197 Trunc 197 Logarithmic and Trigonometric Functions 197 Character Functions 198 Ascii 198 Bitand 199 Chr 200 Concat 200 Greatest 200 Initcap 201 Instr 201 Instrb 202 Least 203 Length 203 Lengthb 204 Lower 204 Lpad 204 Ltrim 205 Nls_initcap 205 Nls_lower 206 Nls_upper 206 Nls_sort 206 Replace 206 Rpad 207 Rtrim 207 Soundex 208 Substr 208 Substrb 209 Translate 209 Trim 210 Upper 210 Date Functions 211 Add_months 211 Current_timestamp 211 Dbtimezone 212 Extract 212 Greatest 213 Last_day 213 Least 214 Local_timestamp 214 Months_between 214 New_time 215 Next_day 215 Numtodsinterval 216 Numtoyminterval 217 Round 217 xii Contents Sessiontimezone 218 Sys_extract_utc 218 Sysdate 218 Systimestamp 219 Trunc 219 Conversion Functions 219 Convert 221 Decompose 222 Translate 222 Unistr 222 Miscellaneous Functions 223 Moving On 224 Chapter 9 PL/SQL 225 Hello, PL/SQL! 225 Structure 228 Declaring Variables 228 Scalar Variable Declarations 229 Record Declarations 231 Cursors 232 Array Structures 232 PL/SQL Blocks and the Execution Section 234 SQL Statements in PL/SQL 235 Control Structures 237 Conditional Control Structures 237 Iterative Control Structures 239 Sequential Control Structures 240 Cursors 241 Packages 244 Procedures and Functions 245 Exceptions 247 Triggers 248 Reference Cursors 250 PL/SQL and XSQL 251 Moving On 251 Chapter 10 Using Oracle Text 253 Simple Keyword Searching 254 Stemming, Fuzzy, Wildcard, and Soundex Searching 255 Searching within XML Documents 256 Other Features 259 Moving On 259 Chapter 11 Retrieving XML 261 What’s So Hard? 261 Creating XML with xsql:include-owa 263 Generating XML from PL/SQL 265 XMLType 267 Moving On 271 Contents xiii Chapter 12 XSLT 273 Getting Started with XSLT 274 The Place of XSLT 274 Push and Pull 275 XHTML and Stylesheet Creation 277 XHTMLDefined 278 A Simple XHTML Transformation 282 Tips and Tricks of Migrating HTML 285 Moving On 286 Chapter 13 XSLT In-Depth 287 Root Element 288 xsl:stylesheetSyntax 288 Examples 290 Controlling the Output 291 xslutputSyntax 291 Examples 292 Templates 293 xsl:templateSyntax 294 xsl:apply-templatesSyntax 296 xsl:call-template 297 Examples 298 Value Selection 301 xsl:value-ofSyntax 302 Examples 303 Iteration 304 xsl:for-eachSyntax 305 Examples 306 Conditional Logic 307 boolean Expressions 308 xsl:if Syntax 308 xsl:choose, xsl:when, and xsltherwise Syntax 309 Examples 311 Working with XML Entities and Text 314 xsl:element, xsl:attribute, and xsl:attribute-setSyntax 315 xsl:attributeSyntax 316 xsl:attribute-setSyntax 318 Element Creation Examples 318 xsl:textSyntax 320 xsl:commentSyntax 321 xsl:copySyntax 322 xsl:copy-ofSyntax 324 xsl:namespace-aliasSyntax 325 xslrocessing-instructionSyntax 326 Numbering Elements 327 xsl:number 328 xsl:decimal-formatSyntax 330 xiv Contents Variables and Parameters 331 xsl:variableSyntax 331 VariableExamples 332 xslaramSyntax 335 xsl:with-paramSyntax 336 ParameterExamples 337 Reusing Stylesheets 339 xsl:include and xsl:importSyntax 340 xsl:apply-imports 340 Sorting 340 xsl:sortSyntax 340 Examples 341 WhitespaceHandling 342 Miscelleneous Elements 342 xsl:keySyntax 342 xsl:message 343 xsl:fallback 343 XPath 344 Expressions and Data Types 344 Location Paths 345 Axes 346 self (.) 347 descendant-or-self (//) 348 parent ( ) 348 attribute (@) 349 child 349 descendant 350 ancestor 350 ancestor-or-self 351 following-sibling 351 preceding-sibling 352 following 352 preceding 352 namespace 352 XPath and XSLT Functions 353 XSLT Specific Functions 353 current 353 document 353 format-number 353 generate-id 354 key 354 system-property 355 unparsed-entity-uri 356 Node-Set Functions 356 count 356 id 357 last 357 Contents xv local-name 358 Name 358 namespace-uri 358 position 358 String Functions 359 concat 359 contains 359 normalize-space 359 starts-with 359 string 360 string-length 360 Substring 361 substring-before 361 substring-after 362 translate 362 booleanFunctions 363 boolean 363 false 364 lang 364 not 364 true 364 NumberFunctions 364 ceiling 364 floor 364 number 365 round 365 sum 365 Moving On 366 Chapter 14 Building XSQL Web Applications 367 Application Architecture 368 The Simple XSQL Architecture 369 Process for Developing Your Architecture 370 Extension Options 375 Stored Procedures 375 Action Handlers 376 Programmatic Invocation 377 Stylesheet Extensions 378 Extending the Interface with JavaScript and CSS 378 Extension at the Database Level 379 A Sample Application 380 The Requirements 380 Application Interface Design 381 Database Requirements 385 Database Design 387 Database Implementation 391 Writing the SQL 392 xvi Contents Integrating XSQL with XSLT 397 Making the XSQL Pages 397 Writing the Stylesheets 401 Passing Parameters 415 Passing Using SQL 415 Setting Stylesheet Parameters 417 Using Other XSQL Parameters 418 Stateless Paging 421 Challenges of Pagination 422 Pure XSQL Stateless Paging 422 XSQL Data Editor 428 Editor Architecture 428 XSQLDevelopment 430 Developing the XSQL Interface 431 Javascript and XSQL Development 435 JavaScript and XSLT Integration 435 Javascript and XSQL Data 437 Error Handling 438 XSQL Errors 439 An XSQL Error Template 440 Ways to Handle Errors 441 Moving On 442 Chapter 15 Command Line Utility 443 Using the Command Line Utility 443 Text Example 445 Script Writing 446 Creating Static Web Pages 448 Moving On 449 Chapter 16 Web Services with XSQL 451 Architecture 452 A Simple Web Services Consumer 456 XML-to-XML Transformations 461 XML Handling Actions 467 Moving On 468 Chapter 17 XSQL Beyond Web Browsing 469 A Sample Program 470 Guide to the XSQL Classes 473 Using XSQL from within Servlets 474 XML Parsing: DOM versus SAX 475 Oracle DOM API 476 Moving On 477 Chapter 18 Custom Action Handlers 479 Getting Started 479 Hello, Action Handler! 480 Adding XML to the Datagram 482 Comparing Action Handler and Servlet Development 484 Contents xvii Action Handler APIs 485 XSQLActionHandlerInterface 485 XSQLActionHandlerImpl Base Class 487 XSQLPageRequest 491 Accessing Servlet Functionality 492 Database Interaction 493 Using Built-in Action Handlers 494 JDBC Connections 500 Using the XSU classes 502 Adding XMLType Objects 503 Parameters and Input 504 Accessing XSQL Data 505 Substituting Parameter Values 507 Setting Page Parameters 510 Inter-action Handler Communication 513 Moving On 515 Chapter 19 Serializers 517 Serializer Overview 517 The Place of Serializers 518 Using Serializers 519 Creating PDFs with Apache FOP 520 FOP Architecture 520 Using the XSQL FOP serializer 522 Creating Custom Serializers 526 Text Serializers 527 Binary Serializers 530 Serializing BLOBs 534 Creating JPEGs with Java AWT 537 Moving On 546 Appendix A Resources 547 Code Examples from This Book 547 Oracle Technet 547 Oracle Database Resources 548 SQL Manual for Oracle 9i 548 PL/SQL Reference 548 Oracle XML DB 548 Oracle XML Developer’s Kit 548 XSLT Resources 549 Java Resources 549 Appendix B Related Standards 551 XSL-FO 551 Scalable Vector Graphics (SVG) 552 SQLJ and Java-Stored Procedures 552 Index 553