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) 2.2 Edited by , Blaine Si

HyperSQL User Guide HyperSQL Database Engine (HSQLDB) 2.2 Edited by , Blaine Simpson, and Fred Toussi HyperSQL User Guide: HyperSQL Database Engine (HSQLDB) 2.2 by , Blaine Simpson, and Fred Toussi $Revision: 4864 $ Publication date 2012-08-06 00:12:50+0100 Copyright 2002-2011 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. iii Table of Contents Preface ........................................................................................................................................ xiii Available formats for this document ......................................................................................... xiii 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 ....................................................................................... 3 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 SQL Data and Tables ............................................................................................................... 9 Temporary Tables ............................................................................................................ 9 Persistent Tables .............................................................................................................. 9 Lob Data ...................................................................................................................... 10 Short Guide to Data Types ....................................................................................................... 10 Data Types and Operations ...................................................................................................... 11 Numeric Types .............................................................................................................. 11 Boolean Type ................................................................................................................ 13 Character String Types .................................................................................................... 14 Binary String Types ........................................................................................................ 15 Bit String Types ............................................................................................................. 15 Storage and Handling of Java Objects ................................................................................ 16 Type Length, Precision and Scale ...................................................................................... 16 Datetime types ....................................................................................................................... 17 Interval Types ........................................................................................................................ 20 Arrays .................................................................................................................................. 23 Array Definition ............................................................................................................. 23 Array Reference ............................................................................................................. 24 Array Operations ............................................................................................................ 25 Indexes and Query Speed ......................................................................................................... 26 Query Processing and Optimisation ........................................................................................... 27 Indexes and Conditions ................................................................................................... 27 Indexes and Operations ................................................................................................... 28 Indexes and ORDER BY, OFFSET and LIMIT .................................................................... 28 3. Sessions and Transactions ............................................................................................................ 30 Overview .............................................................................................................................. 30 Session Attributes and Variables ............................................................................................... 30 Session Attributes ........................................................................................................... 31 Session Variables ........................................................................................................... 31 Session Tables ............................................................................................................... 31 Transactions and Concurrency Control ....................................................................................... 32 Two Phase Locking ........................................................................................................ 32 HyperSQL User Guide iv Two Phase Locking with Snapshot Isolation ........................................................................ 33 Lock Contention in 2PL .................................................................................................. 33 Locks in SQL Routines and Triggers ................................................................................. 33 MVCC ......................................................................................................................... 33 Choosing the Transaction Model ....................................................................................... 34 Schema and Database Change ........................................................................................... 35 Simultaneous Access to Tables ......................................................................................... 35 Viewing Sessions ........................................................................................................... 35 Session and Transaction Control Statements ................................................................................ 35 4. Schemas and Database Objects ...................................................................................................... 42 Overview .............................................................................................................................. 42 Schemas and Schema Objects ................................................................................................... 42 Names and References .................................................................................................... 43 Character Sets ................................................................................................................ 43 Collations ...................................................................................................................... 44 Distinct Types ................................................................................................................ 44 Domains ....................................................................................................................... 44 Number Sequences ......................................................................................................... 44 Tables .......................................................................................................................... 46 Views ........................................................................................................................... 47 Constraints .................................................................................................................... 47 Assertions ..................................................................................................................... 48 Triggers ........................................................................................................................ 48 Routines ....................................................................................................................... 49 Indexes ......................................................................................................................... 49 Statements for Schema Definition and Manipulation ..................................................................... 49 Common Elements and Statements .................................................................................... 49 Renaming Objects .......................................................................................................... 51 Commenting Objects ....................................................................................................... 51 Schema Creation ............................................................................................................ 51 Table Creation ............................................................................................................... 52 Table Manipulation ......................................................................................................... 58 View Creation and Manipulation ....................................................................................... 62 Domain Creation and Manipulation .................................................................................... 63 Trigger Creation ............................................................................................................. 64 Routine Creation ............................................................................................................ 66 Sequence Creation .......................................................................................................... 68 SQL Procedure Statement ................................................................................................ 70 Other Schema Object Creation .......................................................................................... 70 The Information Schema .......................................................................................................... 73 Predefined Character Sets, Collations and Domains ............................................................... 74 Views in INFORMATION SCHEMA ................................................................................ 74 Visibility of Information .................................................................................................. 74 Name Information .......................................................................................................... 74 Data Type Information .................................................................................................... 75 Product Information ........................................................................................................ 75 Operations Information .................................................................................................... 75 SQL Standard Views ....................................................................................................... 75 5. Text Tables ................................................................................................................................ 82 Overview .............................................................................................................................. 82 The Implementation ................................................................................................................ 82 Definition of Tables ........................................................................................................ 82 Scope and Reassignment .................................................................................................. 82 Null Values in Columns of Text Tables .............................................................................. 83 HyperSQL User Guide v Configuration ................................................................................................................. 83 Disconnecting Text Tables ............................................................................................... 84 Text File Usage ..................................................................................................................... 85 Text File Global Properties ...................................................................................................... 85 Transactions .......................................................................................................................... 86 6. Access Control ........................................................................................................................... 87 Overview .............................................................................................................................. 87 Authorizations and Access Control ............................................................................................ 87 Built-In Roles and Users .................................................................................................. 88 Listing Users and Roles ................................................................................................... 89 Access Rights ................................................................................................................ 89 Statements for Authorization and Access Control ......................................................................... 90 7. Data Access and Change .............................................................................................................. 95 Overview .............................................................................................................................. 95 Cursors And Result Sets .......................................................................................................... 95 Columns and Rows ......................................................................................................... 95 Navigation ..................................................................................................................... 95 Updatability ................................................................................................................... 96 Sensitivity ..................................................................................................................... 97 Holdability .................................................................................................................... 97 Autocommit ................................................................................................................... 97 JDBC Overview ............................................................................................................. 97 JDBC Parameters ........................................................................................................... 98 JDBC and Data Change Statements ................................................................................... 98 JDBC Callable Statement ................................................................................................. 98 JDBC Returned Values .................................................................................................... 99 Cursor Declaration .......................................................................................................... 99 Syntax Elements ..................................................................................................................... 99 Literals ......................................................................................................................... 99 References, etc. ............................................................................................................ 103 Value Expression .......................................................................................................... 104 Predicates .................................................................................................................... 111 Aggregate Functions ...................................................................................................... 116 Other Syntax Elements .................................................................................................. 117 Data Access Statements ......................................................................................................... 119 Select Statement ........................................................................................................... 120 Table .......................................................................................................................... 120 Subquery ..................................................................................................................... 120 Query Specification ....................................................................................................... 121 Table Expression .......................................................................................................... 121 Table Primary .............................................................................................................. 122 Joined Table ................................................................................................................ 124 Selection ..................................................................................................................... 126 Projection .................................................................................................................... 126 Computed Columns ....................................................................................................... 127 Naming ....................................................................................................................... 127 Grouping Operations ..................................................................................................... 128 Aggregation ................................................................................................................. 128 Set Operations .............................................................................................................. 128 With Clause and Recursive Queries .................................................................................. 128 Query Expression .......................................................................................................... 129 Ordering ...................................................................................................................... 130 Slicing ........................................................................................................................ 131 Data Change Statements ......................................................................................................... 131 HyperSQL User Guide vi Delete Statement ........................................................................................................... 131 Truncate Statement ........................................................................................................ 132 Insert Statement ............................................................................................................ 133 Update Statement .......................................................................................................... 134 Merge Statement ........................................................................................................... 135 Diagnostics and State ............................................................................................................ 136 8. SQL-Invoked Routines ............................................................................................................... 138 Routine Definition ................................................................................................................ 139 Routine Characteristics .................................................................................................. 141 SQL Language Routines (PSM) ............................................................................................... 143 Advantages and Disadvantages ........................................................................................ 143 Routine Statements ........................................................................................................ 144 Compound Statement ..................................................................................................... 145 Table Variables ............................................................................................................ 145 Variables ..................................................................................................................... 145 Cursors ....................................................................................................................... 146 Handlers ...................................................................................................................... 147 Assignment Statement .................................................................................................... 148 Select Statement : Single Row ......................................................................................... 148 Formal Parameters ........................................................................................................ 148 Iterated Statements ........................................................................................................ 149 Iterated FOR Statement .................................................................................................. 150 Conditional Statements .................................................................................................. 150 Return Statement .......................................................................................................... 152 Control Statements ........................................................................................................ 152 Raising Exceptions ........................................................................................................ 153 Routine Polymorphism ................................................................................................... 153 Returning Data From Procedures ..................................................................................... 154 Recursive Routines ........................................................................................................ 155 Java Language Routines (SQL/JRT) ......................................................................................... 156 Polymorphism .............................................................................................................. 157 Java Language Procedures .............................................................................................. 158 Java Static Methods ...................................................................................................... 159 Legacy Support ............................................................................................................ 160 Securing Access to Classes ............................................................................................. 160 User Defined Aggregate Functions ........................................................................................... 161 Definition of Aggregate Functions ................................................................................... 161 SQL PSM Aggregate Functions ....................................................................................... 162 Java Aggregate Functions ............................................................................................... 163 9. Triggers ................................................................................................................................... 165 Overview ............................................................................................................................. 165 BEFORE Triggers ......................................................................................................... 165 AFTER Triggers ........................................................................................................... 166 INSTEAD OF Triggers .................................................................................................. 166 Trigger Properties ................................................................................................................. 166 Trigger Event ............................................................................................................... 166 Granularity .................................................................................................................. 166 Trigger Action Time ..................................................................................................... 166 References to Rows ....................................................................................................... 167 Trigger Condition ......................................................................................................... 167 Trigger Action in SQL ................................................................................................... 167 Trigger Action in Java ................................................................................................... 168 Trigger Creation ................................................................................................................... 169 10. Built In Functions .................................................................................................................... 172 HyperSQL User Guide vii Overview ............................................................................................................................. 172 String and Binary String Functions .......................................................................................... 173 Numeric Functions ................................................................................................................ 178 Date Time and Interval Functions ............................................................................................ 182 Functions to Report the Time Zone. ................................................................................. 182 Functions to Report the Current Datetime .......................................................................... 183 Functions to Extract an Element of a Datetime ................................................................... 184 Functions for Datetime Arithmetic ................................................................................... 186 Functions to Convert or Format a Datetime ....................................................................... 188 Array Functions .................................................................................................................... 190 General Functions ................................................................................................................. 191 System Functions .................................................................................................................. 193 11. System Management ................................................................................................................ 198 Mode of Operation and Tables ................................................................................................ 198 Mode of Operation ........................................................................................................ 198 Tables ......................................................................................................................... 198 Large Objects ............................................................................................................... 199 Deployment context ...................................................................................................... 199 Readonly Databases ...................................................................................................... 200 ACID, Persistence and Reliability ............................................................................................ 200 Atomicity, Consistency, Isolation, Durability ..................................................................... 200 Backing Up Database Catalogs ................................................................................................ 201 Making Online Backups ................................................................................................. 201 Making Offline Backups ................................................................................................ 201 Examining Backups ....................................................................................................... 201 Restoring a Backup ....................................................................................................... 202 Encrypted Databases ............................................................................................................. 202 Creating and Accessing an Encrypted Database .................................................................. 202 Speed Considerations ..................................................................................................... 202 Security Considerations .................................................................................................. 203 Monitoring Database Operations .............................................................................................. 203 External Statement Level Monitoring ................................................................................ 203 Internal Statement Level Monitoring ................................................................................ 203 Internal Event Monitoring .............................................................................................. 203 Log4J and JDK logging ................................................................................................. 204 Server Operation Monitoring ........................................................................................... 204 Database Security ................................................................................................................. 204 Security Defaults .......................................................................................................... 204 Authentication Control ................................................................................................... 205 Compatibility with Other RDBMS ........................................................................................... 205 PostgreSQL Compatibility .............................................................................................. 206 MySQL Compatibility ................................................................................................... 207 Firebird Compatibility .................................................................................................... 208 Apache Derby Compatibility ........................................................................................... 208 Oracle Compatibility ..................................................................................................... 208 DB2 Compatibility ........................................................................................................ 208 MS SQLServer and Sybase Compatibility ......................................................................... 209 Statements ........................................................................................................................... 209 System Operations ........................................................................................................ 209 Database Settings .......................................................................................................... 211 SQL Conformance Settings ............................................................................................. 214 Cache, Persistence and Files Settings ................................................................................ 221 Authentication Settings .................................................................................................. 224 12. Properties ............................................................................................................................... 227 HyperSQL User Guide viii Connection URL ................................................................................................................... 227 Variables In Connection URL ................................................................................................. 228 Connection properties ............................................................................................................ 228 Database Properties in Connection URL and Properties ................................................................ 230 SQL Conformance Properties .......................................................................................... 231 Database Operations Properties ....................................................................................... 235 Database File and Memory Properties ............................................................................... 236 Crypt Properties ............................................................................................................ 241 System Properties ................................................................................................................. 242 13. HyperSQL Network Listeners (Servers) ....................................................................................... 243 Listeners ............................................................................................................................. 243 HyperSQL Server ......................................................................................................... 243 HyperSQL HTTP Server ................................................................................................ 243 HyperSQL HTTP Servlet ............................................................................................... 244 Server and Web Server Properties ............................................................................................ 244 Starting a Server from your Application .................................................................................... 246 Allowing a Connection to Open or Create a Database .................................................................. 246 Specifying Database Properties at Server Start ........................................................................... 246 TLS Encryption .................................................................................................................... 247 Requirements ............................................................................................................... 247 Encrypting your JDBC connection ................................................................................... 247 JSSE ........................................................................................................................... 249 Making a Private-key Keystore ........................................................................................ 249 uploads/Finance/ guide-hsqldb.pdf