Remerciez-le!

Remerciez @Admin pour avoir partagé cet document gratuitement, de la manière la plus simple, en partageant sur les réseaux sociaux.

HyperSQL User Guide HyperSQL Database Engine (HSQLDB) Edited by The HSQL Develo

HyperSQL User Guide HyperSQL Database Engine (HSQLDB) Edited by The HSQL Development Group, Blaine Simpson, and Fred Toussi HyperSQL User Guide: HyperSQL Database Engine (HSQLDB) by The HSQL Development Group, Blaine Simpson, and Fred Toussi $Revision: 3817 $ Published $Date: 2010-10-17 10:06:12 -0400 (Sun, 17 Oct 2010) $ Copyright 2002-2010 The HSQL Development Group. Permission is granted to distribute this document without any alteration under the terms of the HSQLDB license. You are not allowed to distribute or display this document on the web in an altered form. iv Table of Contents Preface ......................................................................................................................................... xii Available formats for this document .......................................................................................... xii 1. Running and Using HyperSQL ....................................................................................................... 1 The HSQLDB Jar .................................................................................................................... 1 Running Database Access Tools ................................................................................................. 1 A HyperSQL Database .............................................................................................................. 2 In-Process Access to Database Catalogs ....................................................................................... 2 Server Modes .......................................................................................................................... 3 HyperSQL HSQL Server ................................................................................................... 4 HyperSQL HTTP Server ................................................................................................... 4 HyperSQL HTTP Servlet ................................................................................................... 4 Connecting to a Database Server ......................................................................................... 4 Security Considerations ..................................................................................................... 5 Using Multiple Databases .................................................................................................. 5 Accessing the Data ................................................................................................................... 5 Closing the Database ................................................................................................................ 6 Creating a New Database .......................................................................................................... 7 2. SQL Language ............................................................................................................................. 8 Standards Support .................................................................................................................... 8 Short Guide to Types ................................................................................................................ 8 SQL Data and Tables ............................................................................................................... 9 Temporary Tables ............................................................................................................ 9 Persistent Tables ............................................................................................................ 10 Lob Data ...................................................................................................................... 10 Basic Types and Operations ..................................................................................................... 11 Numeric Types .............................................................................................................. 11 Boolean Type ................................................................................................................ 13 Character String Types .................................................................................................... 13 Binary String Types ........................................................................................................ 14 Bit String Types ............................................................................................................. 15 Storage and Handling of Java Objects ................................................................................ 15 Type Length, Precision and Scale ...................................................................................... 16 Datetime types ....................................................................................................................... 16 Interval Types ........................................................................................................................ 19 Arrays .................................................................................................................................. 22 Array Definition ............................................................................................................. 23 Array Reference ............................................................................................................. 23 Array Operations ............................................................................................................ 24 Indexes and Query Speed ......................................................................................................... 25 Query Processing and Optimisation ........................................................................................... 26 Indexes and Conditions ................................................................................................... 26 Indexes and Operations ................................................................................................... 26 Indexes and ORDER BY, OFFSET and LIMIT .................................................................... 27 3. Sessions and Transactions ............................................................................................................ 28 Overview .............................................................................................................................. 28 Session Attributes and Variables ............................................................................................... 28 Session Attributes ........................................................................................................... 29 Session Variables ........................................................................................................... 29 Session Tables ............................................................................................................... 29 Transactions and Concurrency Control ....................................................................................... 30 Two Phase Locking ........................................................................................................ 30 HyperSQL User Guide v Two Phase Locking with Snapshot Isolation ........................................................................ 30 Lock Contention in 2PL .................................................................................................. 31 MVCC ......................................................................................................................... 31 Choosing the Transaction Model ....................................................................................... 32 Schema and Database Change ........................................................................................... 32 Simultaneous Access to Tables ......................................................................................... 33 Session and Transaction Control Statements ................................................................................ 33 4. Schemas and Database Objects ...................................................................................................... 40 Overview .............................................................................................................................. 40 Schemas and Schema Objects ................................................................................................... 40 Names and References .................................................................................................... 41 Character Sets ................................................................................................................ 41 Collations ...................................................................................................................... 42 Distinct Types ................................................................................................................ 42 Domains ....................................................................................................................... 42 Number Sequences ......................................................................................................... 42 Tables .......................................................................................................................... 44 Views ........................................................................................................................... 44 Constraints .................................................................................................................... 45 Assertions ..................................................................................................................... 46 Triggers ........................................................................................................................ 46 Routines ....................................................................................................................... 46 Indexes ......................................................................................................................... 46 Statements for Schema Definition and Manipulation ..................................................................... 47 Common Elements and Statements .................................................................................... 47 Renaming Objects .......................................................................................................... 48 Commenting Objects ....................................................................................................... 48 Schema Creation ............................................................................................................ 49 Table Creation ............................................................................................................... 50 Table Manipulation ......................................................................................................... 55 View Creation and Manipulation ....................................................................................... 59 Domain Creation and Manipulation .................................................................................... 60 Trigger Creation ............................................................................................................. 61 Routine Creation ............................................................................................................ 63 Sequence Creation .......................................................................................................... 65 SQL Procedure Statement ................................................................................................ 66 Other Schema Object Creation .......................................................................................... 67 The Information Schema .......................................................................................................... 70 Predefined Character Sets, Collations and Domains ............................................................... 70 Views in INFORMATION SCHEMA ................................................................................ 70 5. Text Tables ................................................................................................................................ 77 Overview .............................................................................................................................. 77 The Implementation ................................................................................................................ 77 Definition of Tables ........................................................................................................ 77 Scope and Reassignment .................................................................................................. 77 Null Values in Columns of Text Tables .............................................................................. 78 Configuration ................................................................................................................. 78 Disconnecting Text Tables ............................................................................................... 79 Text File Usage ..................................................................................................................... 80 Text File Global Properties ...................................................................................................... 80 Transactions .......................................................................................................................... 80 6. Access Control ........................................................................................................................... 82 Overview .............................................................................................................................. 82 Authorizations and Access Control ............................................................................................ 83 HyperSQL User Guide vi Built-In Roles and Users .................................................................................................. 83 Access Rights ................................................................................................................ 84 Statements for Authorization and Access Control ......................................................................... 85 7. Data Access and Change .............................................................................................................. 90 Overview .............................................................................................................................. 90 Cursors And Result Sets .......................................................................................................... 90 Columns and Rows ......................................................................................................... 90 Navigation ..................................................................................................................... 90 Updatability ................................................................................................................... 91 Sensitivity ..................................................................................................................... 92 Holdability .................................................................................................................... 92 Autocommit ................................................................................................................... 92 JDBC Overview ............................................................................................................. 92 JDBC Parameters ........................................................................................................... 93 JDBC and Data Change Statements ................................................................................... 93 JDBC Callable Statement ................................................................................................. 93 JDBC Returned Values .................................................................................................... 94 Cursor Declaration .......................................................................................................... 94 Syntax Elements ..................................................................................................................... 94 Literals ......................................................................................................................... 94 References, etc. .............................................................................................................. 98 Value Expression ............................................................................................................ 99 Predicates .................................................................................................................... 105 Other Syntax Elements .................................................................................................. 110 Data Access Statements ......................................................................................................... 113 Table .......................................................................................................................... 114 Subquery ..................................................................................................................... 115 Query Specification ....................................................................................................... 115 Table Expression .......................................................................................................... 116 Table Primary .............................................................................................................. 117 Joined Table ................................................................................................................ 119 Selection ..................................................................................................................... 120 Projection .................................................................................................................... 120 Computed Columns ....................................................................................................... 120 Naming ....................................................................................................................... 120 Grouping Operations ..................................................................................................... 121 Aggregation ................................................................................................................. 121 Set Operations .............................................................................................................. 122 Query Expression .......................................................................................................... 122 Ordering ...................................................................................................................... 123 Slicing ........................................................................................................................ 123 Data Change Statements ......................................................................................................... 124 Delete Statement ........................................................................................................... 124 Truncate Statement ........................................................................................................ 124 Insert Statement ............................................................................................................ 124 Update Statement .......................................................................................................... 126 Merge Statement ........................................................................................................... 127 8. SQL-Invoked Routines ............................................................................................................... 129 Routine Definition ................................................................................................................ 130 Routine Characteristics .................................................................................................. 132 SQL Language Routines (PSM) ............................................................................................... 134 Routine Statements ........................................................................................................ 134 Compound Statement ..................................................................................................... 135 Table Variables ............................................................................................................ 136 HyperSQL User Guide vii Variables ..................................................................................................................... 136 Cursors ....................................................................................................................... 137 Handlers ...................................................................................................................... 137 Assignment Statement .................................................................................................... 138 Select Statement : Single Row ......................................................................................... 139 Formal Parameters ........................................................................................................ 139 Iterated Statements ........................................................................................................ 140 Iterated FOR Statement .................................................................................................. 140 Conditional Statements .................................................................................................. 141 Return Statement .......................................................................................................... 142 Control Statements ........................................................................................................ 143 Raising Exceptions ........................................................................................................ 143 Routine Polymorphism ................................................................................................... 144 Returning Data From Procedures ..................................................................................... 144 Recursive Routines ........................................................................................................ 145 Java Language Routines (SQL/JRT) ......................................................................................... 146 Polymorphism .............................................................................................................. 147 Java Language Procedures .............................................................................................. 148 Java Static Methods ...................................................................................................... 149 Legacy Support ............................................................................................................ 150 Securing Access to Classes ............................................................................................. 150 User Defined Aggregate Functions ........................................................................................... 150 Definition of Aggregate Functions ................................................................................... 151 SQL PSM Aggregate Functions ....................................................................................... 151 Java Aggregate Functions ............................................................................................... 153 9. Triggers ................................................................................................................................... 155 Overview ............................................................................................................................. 155 BEFORE Triggers ......................................................................................................... 155 AFTER Triggers ........................................................................................................... 156 INSTEAD OF Triggers .................................................................................................. 156 Trigger Properties ................................................................................................................. 156 Trigger Event ............................................................................................................... 156 Granularity .................................................................................................................. 156 Trigger Action Time ..................................................................................................... 156 References to Rows ....................................................................................................... 157 Trigger Condition ......................................................................................................... 157 Trigger Action in SQL ................................................................................................... 157 Trigger Action in Java ................................................................................................... 158 Trigger Creation ................................................................................................................... 159 10. Built In Functions .................................................................................................................... 162 Overview ............................................................................................................................. 162 String and Binary String Functions .......................................................................................... 163 Numeric Functions ................................................................................................................ 167 Date Time and Interval Functions ............................................................................................ 171 Array Functions .................................................................................................................... 177 General Functions ................................................................................................................. 178 System Functions .................................................................................................................. 180 11. System Management and Deployment Issues ................................................................................ 183 Mode of Operation and Tables ................................................................................................ 183 Mode of Operation ........................................................................................................ 183 Tables ......................................................................................................................... 183 Large Objects ............................................................................................................... 184 Deployment context ...................................................................................................... 184 Readonly Databases ...................................................................................................... 184 HyperSQL User Guide viii Memory and Disk Use ........................................................................................................... 185 Table Memory Allocation ............................................................................................... 185 Result Set Memory Allocation ........................................................................................ 185 Temporary Memory Use During Operations ....................................................................... 185 Data Cache Memory Allocation ....................................................................................... 186 Object Pool Memory Allocation ...................................................................................... 186 Lob Memory Usage ...................................................................................................... 186 Disk Space .................................................................................................................. 186 Managing Database Connections ............................................................................................. 186 Tweaking the Mode of Operation ............................................................................................ 187 Application Development and Testing .............................................................................. 187 Embedded Databases in Desktop Applications .................................................................... 188 Embedded Databases in Server Applications ...................................................................... 188 Embedding a HyperSQL Server (Listener) ......................................................................... 188 Using HyperSQL Without Logging .................................................................................. 189 Server Databases .......................................................................................................... 189 Persistence and Reliability .............................................................................................. 189 Upgrading Databases ............................................................................................................. 189 Upgrading From Older Versions ...................................................................................... 190 Manual Changes to the *.script File ................................................................................. 190 Backing Up Database Catalogs ................................................................................................ 191 Making Online Backups ................................................................................................. 191 Making Offline Backups ................................................................................................ 192 Examining Backups ....................................................................................................... 192 Restoring a Backup ....................................................................................................... 192 Encrypted Databases ............................................................................................................. 192 Creating and Accessing an Encrypted Database .................................................................. 193 Speed Considerations ..................................................................................................... 193 Security Considerations .................................................................................................. 193 Monitoring Database Operations .............................................................................................. 193 Statement Level Monitoring ............................................................................................ 193 Internal Event Monitoring .............................................................................................. 194 Server Operation Monitoring ........................................................................................... 194 Backward Compatibility Issues ................................................................................................ 194 Compatibility With Other RDBMS .......................................................................................... 195 PostgreSQL Compatibility .............................................................................................. 196 MySQL Compatibility ................................................................................................... 196 Firebird Compatibility .................................................................................................... 196 Apache Derby Compatibility ........................................................................................... 196 Oracle Compatibility ..................................................................................................... 197 DB2 Compatibility ........................................................................................................ 197 MS SQLServer Compatibility ......................................................................................... 197 Database Security ................................................................................................................. 197 Authentication Control ................................................................................................... 198 Statements ........................................................................................................................... 198 System Operations ........................................................................................................ 198 Database Settings .......................................................................................................... 200 SQL Conformance Settings ............................................................................................. 202 Cache, Persistence and Files Settings ................................................................................ 206 Authentication Settings .................................................................................................. 209 12. Properties ............................................................................................................................... 211 Connection URL ................................................................................................................... 211 Variables In Connection URL ......................................................................................... 212 Connection properties .................................................................................................... 212 HyperSQL User Guide ix Database Properties in Connection URL and Properties ................................................................ 213 13. HyperSQL Network Listeners (Servers) ....................................................................................... 222 Listeners ............................................................................................................................. 222 HyperSQL Server ......................................................................................................... 222 HyperSQL HTTP Server ................................................................................................ 222 HyperSQL HTTP Servlet ............................................................................................... 222 Server and Web Server Properties ............................................................................................ 223 Starting a Server from your Application .................................................................................... 224 Allowing a Connection to Open or Create a Database .................................................................. 225 TLS Encryption .................................................................................................................... 225 Requirements ............................................................................................................... 225 Encrypting your JDBC connection ................................................................................... 226 JSSE ........................................................................................................................... 227 Making a Private-key Keystore ........................................................................................ 228 Automatic Server or WebServer startup on UNIX ............................................................... 228 Network Access Control ........................................................................................................ 228 14. HyperSQL on UNIX ................................................................................................................ 231 Purpose ............................................................................................................................... 231 Installation ........................................................................................................................... 231 Setting up Database Catalog and Listener .................................................................................. 233 Accessing your Database ........................................................................................................ 234 Create additional uploads/Finance/ hsqldb-guide 1 .pdf