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: 4582 $ Publication date 2011-11-20 14:43:59-0500 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 .......................................................................................................... 50 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 ............................................................... 73 Views in INFORMATION SCHEMA ................................................................................ 74 Visibility of Information .................................................................................................. 74 Name Information .......................................................................................................... 74 Data Type Information .................................................................................................... 74 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 ............................................................................................ 88 Built-In Roles and Users .................................................................................................. 88 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 Other Syntax Elements .................................................................................................. 116 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 ....................................................................................................... 126 Naming ....................................................................................................................... 126 Grouping Operations ..................................................................................................... 127 Aggregation ................................................................................................................. 127 Set Operations .............................................................................................................. 127 With Clause and Recursive Queries .................................................................................. 128 Query Expression .......................................................................................................... 129 Ordering ...................................................................................................................... 129 Slicing ........................................................................................................................ 130 Data Change Statements ......................................................................................................... 130 Delete Statement ........................................................................................................... 130 Truncate Statement ........................................................................................................ 131 HyperSQL User Guide vi Insert Statement ............................................................................................................ 131 Update Statement .......................................................................................................... 133 Merge Statement ........................................................................................................... 134 Diagnostics and State ............................................................................................................ 135 8. SQL-Invoked Routines ............................................................................................................... 137 Routine Definition ................................................................................................................ 138 Routine Characteristics .................................................................................................. 140 SQL Language Routines (PSM) ............................................................................................... 142 Advantages and Disadvantages ........................................................................................ 142 Routine Statements ........................................................................................................ 143 Compound Statement ..................................................................................................... 144 Table Variables ............................................................................................................ 144 Variables ..................................................................................................................... 145 Cursors ....................................................................................................................... 145 Handlers ...................................................................................................................... 146 Assignment Statement .................................................................................................... 147 Select Statement : Single Row ......................................................................................... 147 Formal Parameters ........................................................................................................ 148 Iterated Statements ........................................................................................................ 148 Iterated FOR Statement .................................................................................................. 149 Conditional Statements .................................................................................................. 150 Return Statement .......................................................................................................... 151 Control Statements ........................................................................................................ 151 Raising Exceptions ........................................................................................................ 152 Routine Polymorphism ................................................................................................... 152 Returning Data From Procedures ..................................................................................... 153 Recursive Routines ........................................................................................................ 154 Java Language Routines (SQL/JRT) ......................................................................................... 155 Polymorphism .............................................................................................................. 156 Java Language Procedures .............................................................................................. 157 Java Static Methods ...................................................................................................... 158 Legacy Support ............................................................................................................ 159 Securing Access to Classes ............................................................................................. 159 User Defined Aggregate Functions ........................................................................................... 160 Definition of Aggregate Functions ................................................................................... 160 SQL PSM Aggregate Functions ....................................................................................... 161 Java Aggregate Functions ............................................................................................... 162 9. Triggers ................................................................................................................................... 164 Overview ............................................................................................................................. 164 BEFORE Triggers ......................................................................................................... 164 AFTER Triggers ........................................................................................................... 165 INSTEAD OF Triggers .................................................................................................. 165 Trigger Properties ................................................................................................................. 165 Trigger Event ............................................................................................................... 165 Granularity .................................................................................................................. 165 Trigger Action Time ..................................................................................................... 165 References to Rows ....................................................................................................... 166 Trigger Condition ......................................................................................................... 166 Trigger Action in SQL ................................................................................................... 166 Trigger Action in Java ................................................................................................... 167 Trigger Creation ................................................................................................................... 168 10. Built In Functions .................................................................................................................... 171 Overview ............................................................................................................................. 171 String and Binary String Functions .......................................................................................... 172 HyperSQL User Guide vii Numeric Functions ................................................................................................................ 177 Date Time and Interval Functions ............................................................................................ 181 Functions to Report the Time Zone. ................................................................................. 181 Functions to Report the Current Datetime .......................................................................... 181 Functions to Extract an Element of a Datetime ................................................................... 183 Functions for Datetime Arithmatic ................................................................................... 185 Functions to Convert or Format a Datetime ....................................................................... 186 Array Functions .................................................................................................................... 188 General Functions ................................................................................................................. 190 System Functions .................................................................................................................. 192 11. System Management and Deployment Issues ................................................................................ 196 Mode of Operation and Tables ................................................................................................ 196 Mode of Operation ........................................................................................................ 196 Tables ......................................................................................................................... 196 Large Objects ............................................................................................................... 197 Deployment context ...................................................................................................... 197 Readonly Databases ...................................................................................................... 197 Memory and Disk Use ........................................................................................................... 198 Table Memory Allocation ............................................................................................... 198 Result Set Memory Allocation ........................................................................................ 198 Temporary Memory Use During Operations ....................................................................... 198 Data Cache Memory Allocation ....................................................................................... 199 Object Pool Memory Allocation ...................................................................................... 199 Lob Memory Usage ...................................................................................................... 199 Disk Space .................................................................................................................. 200 Managing Database Connections ............................................................................................. 200 Tweaking the Mode of Operation ............................................................................................ 200 Application Development and Testing .............................................................................. 200 Embedded Databases in Desktop Applications .................................................................... 201 Embedded Databases in Server Applications ...................................................................... 201 Embedding a HyperSQL Server (Listener) ......................................................................... 202 Using HyperSQL Without Logging .................................................................................. 202 Bulk Inserts, Updates and Deletes .................................................................................... 202 Server Databases .......................................................................................................... 203 Persistence and Reliability .............................................................................................. 203 Upgrading Databases ............................................................................................................. 203 Upgrading From Older Versions ...................................................................................... 204 Manual Changes to the *.script File ................................................................................. 205 Backing Up Database Catalogs ................................................................................................ 206 Making Online Backups ................................................................................................. 206 Making Offline Backups ................................................................................................ 206 Examining Backups ....................................................................................................... 206 Restoring a Backup ....................................................................................................... 207 Encrypted Databases ............................................................................................................. 207 Creating and Accessing an Encrypted Database .................................................................. 207 Speed Considerations ..................................................................................................... 207 Security Considerations .................................................................................................. 208 Monitoring Database Operations .............................................................................................. 208 External Statement Level Monitoring ................................................................................ 208 Internal Statement Level Monitoring ................................................................................ 208 Internal Event Monitoring .............................................................................................. 208 Server Operation Monitoring ........................................................................................... 209 Backward Compatibility Issues ................................................................................................ 209 Compatibility With Other RDBMS .......................................................................................... 210 HyperSQL User Guide viii PostgreSQL Compatibility .............................................................................................. 210 MySQL Compatibility ................................................................................................... 211 Firebird Compatibility .................................................................................................... 212 Apache Derby Compatibility ........................................................................................... 212 Oracle Compatibility ..................................................................................................... 212 DB2 Compatibility ........................................................................................................ 213 MS SQLServer and Sybase Compatibility ......................................................................... 213 Database Security ................................................................................................................. 213 Security Defaults .......................................................................................................... 214 Authentication Control ................................................................................................... 215 Statements ........................................................................................................................... 215 System Operations ........................................................................................................ 215 Database Settings .......................................................................................................... 217 SQL Conformance Settings ............................................................................................. 219 Cache, Persistence and Files Settings ................................................................................ 225 Authentication Settings .................................................................................................. 228 Application HyperSQL Dependency Settings ............................................................................. 230 What version to Pull ..................................................................................................... 230 Dependency Specification Examples ................................................................................. 231 12. Properties ............................................................................................................................... 233 Connection URL ................................................................................................................... 233 Variables In Connection uploads/Finance/ guide - 2023-05-30T180628.463.pdf