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 2.5.0 Edited by , Blaine Simpson,

HyperSQL User Guide HyperSQL Database Engine 2.5.0 Edited by , Blaine Simpson, and Fred Toussi HyperSQL User Guide: HyperSQL Database Engine 2.5.0 by , Blaine Simpson, and Fred Toussi $Revision: 5941 $ Publication date 2019-06-02 Copyright 2002-2019 Blaine Simpson, Fred Toussi and 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 Introduction ............................................................................................................................. 1 The HSQLDB Jar .................................................................................................................... 1 Running Database Access Tools ................................................................................................. 2 A HyperSQL Database .............................................................................................................. 2 In-Process Access to Database Catalogs ....................................................................................... 3 Server Modes .......................................................................................................................... 4 HyperSQL HSQL Server ................................................................................................... 4 HyperSQL HTTP Server ................................................................................................... 5 HyperSQL HTTP Servlet ................................................................................................... 5 Connecting to a Database Server ......................................................................................... 5 Security Considerations ..................................................................................................... 6 Using Multiple Databases .................................................................................................. 6 Accessing the Data ................................................................................................................... 6 Closing the Database ................................................................................................................ 7 Creating a New Database .......................................................................................................... 7 2. SQL Language ............................................................................................................................. 9 Standards Support .................................................................................................................... 9 SQL Data and Tables ............................................................................................................... 9 Temporary Tables ........................................................................................................... 10 Persistent Tables ............................................................................................................ 10 Short Guide to Data Types ....................................................................................................... 11 Data Types and Operations ...................................................................................................... 12 Numeric Types .............................................................................................................. 12 Boolean Type ................................................................................................................ 14 Character String Types .................................................................................................... 14 Binary String Types ........................................................................................................ 15 Bit String Types ............................................................................................................. 16 Lob Data ...................................................................................................................... 17 Storage and Handling of Java Objects ................................................................................ 17 Type Length, Precision and Scale ...................................................................................... 18 Datetime types ....................................................................................................................... 19 Interval Types ........................................................................................................................ 23 Arrays .................................................................................................................................. 26 Array Definition ............................................................................................................. 26 Array Reference ............................................................................................................. 28 Array Operations ............................................................................................................ 28 Indexes and Query Speed ......................................................................................................... 30 Query Processing and Optimisation ........................................................................................... 31 Indexes and Conditions ................................................................................................... 31 Indexes and Operations ................................................................................................... 32 Indexes and ORDER BY, OFFSET and LIMIT .................................................................... 32 3. Sessions and Transactions ............................................................................................................ 34 Overview .............................................................................................................................. 34 Session Attributes and Variables ............................................................................................... 34 Session Attributes ........................................................................................................... 35 Session Variables ........................................................................................................... 35 Session Tables ............................................................................................................... 35 Transactions and Concurrency Control ....................................................................................... 36 HyperSQL User Guide iv Two Phase Locking ........................................................................................................ 36 Two Phase Locking with Snapshot Isolation ........................................................................ 37 Lock Contention in 2PL .................................................................................................. 37 Locks in SQL Routines and Triggers ................................................................................. 37 MVCC ......................................................................................................................... 37 Choosing the Transaction Model ....................................................................................... 38 Schema and Database Change ........................................................................................... 39 Simultaneous Access to Tables ......................................................................................... 39 Viewing Sessions ........................................................................................................... 39 Session and Transaction Control Statements ................................................................................ 40 4. Schemas and Database Objects ...................................................................................................... 47 Overview .............................................................................................................................. 47 Schemas and Schema Objects ................................................................................................... 47 Names and References .................................................................................................... 48 Character Sets ................................................................................................................ 48 Collations ...................................................................................................................... 49 Distinct Types ................................................................................................................ 50 Domains ....................................................................................................................... 50 Number Sequences ......................................................................................................... 50 Tables .......................................................................................................................... 52 Views ........................................................................................................................... 53 Constraints .................................................................................................................... 53 Assertions ..................................................................................................................... 54 Triggers ........................................................................................................................ 54 Routines ....................................................................................................................... 55 Indexes ......................................................................................................................... 55 Synonyms ..................................................................................................................... 55 Statements for Schema Definition and Manipulation ..................................................................... 55 Common Elements and Statements .................................................................................... 55 Renaming Objects .......................................................................................................... 57 Commenting Objects ....................................................................................................... 57 Schema Creation ............................................................................................................ 58 Table Creation ............................................................................................................... 59 Temporal System-Versioned Tables and SYSTEM_TIME Period ............................................. 64 Table Settings ................................................................................................................ 65 Table Manipulation ......................................................................................................... 67 View Creation and Manipulation ....................................................................................... 71 Domain Creation and Manipulation .................................................................................... 72 Trigger Creation ............................................................................................................. 73 Routine Creation ............................................................................................................ 74 Sequence Creation .......................................................................................................... 77 SQL Procedure Statement ................................................................................................ 78 Other Schema Object Creation .......................................................................................... 79 The Information Schema .......................................................................................................... 82 Predefined Character Sets, Collations and Domains ............................................................... 83 Views in INFORMATION SCHEMA ................................................................................ 83 Visibility of Information .................................................................................................. 83 Name Information .......................................................................................................... 83 Data Type Information .................................................................................................... 84 Product Information ........................................................................................................ 84 Operations Information .................................................................................................... 84 SQL Standard Views ....................................................................................................... 84 5. Text Tables ................................................................................................................................ 92 Overview .............................................................................................................................. 92 HyperSQL User Guide v The Implementation ................................................................................................................ 92 Definition of Tables ........................................................................................................ 92 Scope and Reassignment .................................................................................................. 92 Null Values in Columns of Text Tables .............................................................................. 93 Configuration ................................................................................................................. 93 Disconnecting Text Tables ............................................................................................... 95 Text File Usage ..................................................................................................................... 95 Text File Global Properties ...................................................................................................... 96 Transactions .......................................................................................................................... 97 6. Access Control ........................................................................................................................... 98 Overview .............................................................................................................................. 98 Authorizations and Access Control ............................................................................................ 98 Built-In Roles and Users .................................................................................................. 99 Listing Users and Roles ................................................................................................. 100 Access Rights .............................................................................................................. 100 Fine-Grained Data Access Control ................................................................................... 101 Statements for Authorization and Access Control ........................................................................ 102 7. Data Access and Change ............................................................................................................ 106 Overview ............................................................................................................................. 106 Cursors And Result Sets ........................................................................................................ 106 Columns and Rows ....................................................................................................... 106 Navigation ................................................................................................................... 106 Updatability ................................................................................................................. 107 Sensitivity ................................................................................................................... 108 Holdability ................................................................................................................... 108 Autocommit ................................................................................................................. 108 JDBC Overview ........................................................................................................... 108 JDBC Parameters .......................................................................................................... 109 JDBC and Data Change Statements .................................................................................. 109 JDBC Callable Statement ............................................................................................... 110 JDBC Returned Values .................................................................................................. 110 Cursor Declaration ........................................................................................................ 111 Syntax Elements ................................................................................................................... 111 Literals ....................................................................................................................... 111 References, etc. ............................................................................................................ 115 Value Expression .......................................................................................................... 116 Predicates .................................................................................................................... 122 Aggregate Functions ...................................................................................................... 129 Other Syntax Elements .................................................................................................. 130 Data Access Statements ......................................................................................................... 132 Select Statement ........................................................................................................... 133 Table .......................................................................................................................... 133 Subquery ..................................................................................................................... 133 Query Specification ....................................................................................................... 134 Table Expression .......................................................................................................... 134 Joined Table ................................................................................................................ 138 Selection ..................................................................................................................... 140 Projection .................................................................................................................... 140 Computed Columns ....................................................................................................... 140 Naming ....................................................................................................................... 140 Grouping Operations ..................................................................................................... 141 Aggregation ................................................................................................................. 142 Set Operations .............................................................................................................. 142 With Clause and Recursive Queries .................................................................................. 142 HyperSQL User Guide vi Query Expression .......................................................................................................... 143 Ordering ...................................................................................................................... 144 Slicing ........................................................................................................................ 145 Data Change Statements ......................................................................................................... 146 Delete Statement ........................................................................................................... 146 Truncate Statement ........................................................................................................ 146 Insert Statement ............................................................................................................ 147 Update Statement .......................................................................................................... 149 Merge Statement ........................................................................................................... 150 Diagnostics and State ............................................................................................................ 151 8. SQL-Invoked Routines ............................................................................................................... 153 Routine Definition ................................................................................................................ 154 Routine Characteristics .................................................................................................. 156 SQL Language Routines (PSM) ............................................................................................... 158 Advantages and Disadvantages ........................................................................................ 158 Routine Statements ........................................................................................................ 159 Compound Statement ..................................................................................................... 160 Table Variables ............................................................................................................ 160 Variables ..................................................................................................................... 161 Cursors ....................................................................................................................... 161 Handlers ...................................................................................................................... 162 Assignment Statement .................................................................................................... 163 Select Statement : Single Row ......................................................................................... 164 Formal Parameters ........................................................................................................ 164 Iterated Statements ........................................................................................................ 165 Iterated FOR Statement .................................................................................................. 165 Conditional Statements .................................................................................................. 166 Return Statement .......................................................................................................... 167 Control Statements ........................................................................................................ 168 Raising Exceptions ........................................................................................................ 168 Routine Polymorphism ................................................................................................... 169 Returning Data From Procedures ..................................................................................... 169 Recursive Routines ........................................................................................................ 171 Java Language Routines (SQL/JRT) ......................................................................................... 172 Polymorphism .............................................................................................................. 173 Java Language Procedures .............................................................................................. 174 Java Static Methods ...................................................................................................... 175 Legacy Support ............................................................................................................ 176 Securing Access to Classes and Routines .......................................................................... 176 Warning ...................................................................................................................... 177 User-Defined Aggregate Functions ........................................................................................... 177 Definition of Aggregate Functions ................................................................................... 177 SQL PSM Aggregate Functions ....................................................................................... 178 Java Aggregate Functions ............................................................................................... 180 9. Triggers ................................................................................................................................... 181 Overview ............................................................................................................................. 181 BEFORE Triggers ......................................................................................................... 181 AFTER Triggers ........................................................................................................... 182 INSTEAD OF Triggers .................................................................................................. 182 Trigger Properties ................................................................................................................. 182 Trigger Event ............................................................................................................... 182 Granularity .................................................................................................................. 182 Trigger Action Time ..................................................................................................... 183 References to Rows ....................................................................................................... 183 HyperSQL User Guide vii Trigger Condition ......................................................................................................... 183 Trigger Action in SQL ................................................................................................... 183 Trigger Action in Java ................................................................................................... 184 Trigger Creation ................................................................................................................... 185 10. Built In Functions .................................................................................................................... 188 Overview ............................................................................................................................. 188 String and Binary String Functions .......................................................................................... 189 Numeric Functions ................................................................................................................ 195 Date Time and Interval Functions ............................................................................................ 200 Functions to Report the Time Zone. ................................................................................. 200 Functions to Report the Current Datetime .......................................................................... 201 Functions to Extract an Element of a Datetime ................................................................... 202 Functions for Datetime Arithmetic ................................................................................... 204 Functions to Convert or Format a Datetime ....................................................................... 207 Array Functions .................................................................................................................... 210 General Functions ................................................................................................................. 211 System Functions .................................................................................................................. 214 11. System Management ................................................................................................................ 218 Modes of Operation .............................................................................................................. 218 Deployment Types ........................................................................................................ 218 Database Types ............................................................................................................ 218 Tables ......................................................................................................................... 219 Large Objects ............................................................................................................... 219 Deployment context ...................................................................................................... 220 ACID, Persistence and Reliability ............................................................................................ 220 Atomicity, Consistency, Isolation, Durability ..................................................................... 221 System Operations ........................................................................................................ 221 Temporal System-Versioned Tables ......................................................................................... 222 Backing Up and Restoring Database Catalogs ............................................................................ 222 Making Online Backups ................................................................................................. 223 Offline Backup Utility Syntax ......................................................................................... 223 Making Offline Backups ................................................................................................ 223 Examining Backups ....................................................................................................... 224 Restoring a Backup ....................................................................................................... 224 Encrypted Databases ............................................................................................................. 224 Creating and Accessing an Encrypted Database .................................................................. 224 Speed Considerations ..................................................................................................... 225 Security Considerations .................................................................................................. 225 Monitoring Database Operations .............................................................................................. 225 External Statement Level Monitoring ................................................................................ 226 Internal Statement Level Monitoring ................................................................................ 226 Internal Event Monitoring .............................................................................................. 226 Log4J and JDK logging ................................................................................................. 226 Server Operation Monitoring ........................................................................................... 226 Database Security ................................................................................................................. 226 Basic Security Recommendations ..................................................................................... 227 Beyond Security Defaults ............................................................................................... 227 Authentication Control ................................................................................................... 228 Statements ........................................................................................................................... 228 System Operations ........................................................................................................ 228 Data Management Statements ......................................................................................... 230 Database Settings .......................................................................................................... 231 SQL Conformance Settings ............................................................................................. 235 Cache, Persistence and Files Settings ................................................................................ 244 HyperSQL User Guide viii Authentication Settings .................................................................................................. 248 12. Compatibility With Other DBMS ............................................................................................... 250 Compatibility Overview ......................................................................................................... 250 PostgreSQL Compatibility .............................................................................................. 250 MySQL Compatibility ................................................................................................... 251 Firebird Compatibility .................................................................................................... 253 Apache Derby Compatibility ........................................................................................... 253 Oracle Compatibility ..................................................................................................... 253 DB2 Compatibility ........................................................................................................ 254 MS SQLServer and Sybase Compatibility ......................................................................... 254 13. Properties ............................................................................................................................... 256 Connection URL ................................................................................................................... 256 Variables In Connection URL ................................................................................................. 257 Properties for Individual Connections ....................................................................................... 257 Properties for the Database ..................................................................................................... 260 SQL Conformance Properties .......................................................................................... 261 Database Operations Properties ....................................................................................... 266 Database File and Memory Properties ............................................................................... 268 Crypt Properties ............................................................................................................ 273 System Properties ................................................................................................................. 274 14. HyperSQL Network Listeners (Servers) ....................................................................................... 276 Listeners ............................................................................................................................. 276 HyperSQL Server ......................................................................................................... 276 HyperSQL HTTP Server ................................................................................................ 276 HyperSQL HTTP Servlet ............................................................................................... 277 Server uploads/Finance/ hsqldb-guide 2 .pdf