HyperSQL User Guide - HyperSQL Database …

1 HyperSQL user GuideHyperSQL Database engine by , Blaine Simpson, and Fred ToussiHyperSQL user Guide : HyperSQL Database engine , Blaine Simpson, and Fred Toussi$Revision: 5805 $Publication date 2018-05-20 12:12:26-0400 Copyright 2002-2018 Blaine Simpson, Fred Toussi and The HSQL Development Group. Permission is granted to distribute this document withoutany alteration under the terms of the HSQLDB license. You are not allowed to distribute or display this document on the web in an altered of ContentsPreface .. xiiiAvailable formats for this document .. xiii1. Running and Using HyperSQL .. 1 Introduction .. 1 The HSQLDB Jar .. 1 Running Database Access Tools .. 2A HyperSQL Database .. 2In-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.

2 5 Security Considerations .. 6 Using Multiple Databases .. 6 Accessing the Data .. 6 Closing the Database .. 7 Creating a New Database .. 72. 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 .. 27 Array Operations .. 28 Indexes and Query Speed.

3 30 Query Processing and Optimisation .. 31 Indexes and Conditions .. 31 Indexes and Operations .. 31 Indexes and ORDER BY, OFFSET and LIMIT .. 323. Sessions and Transactions .. 33 Overview .. 33 Session Attributes and Variables .. 33 Session Attributes .. 34 Session Variables .. 34 Session Tables .. 34 Transactions and Concurrency Control .. 35 HyperSQL user GuideivTwo Phase Locking .. 35 Two Phase Locking with Snapshot Isolation .. 36 Lock Contention in 2PL .. 36 Locks in SQL Routines and Triggers .. 36 MVCC .. 36 Choosing the Transaction Model .. 37 Schema and Database Change .. 38 Simultaneous Access to Tables .. 38 Viewing Sessions .. 38 Session and Transaction Control Statements .. 394. Schemas and Database Objects.

4 46 Overview .. 46 Schemas and Schema Objects .. 46 Names and References .. 47 Character Sets .. 47 Collations .. 48 Distinct Types .. 49 Domains .. 49 Number Sequences .. 49 Tables .. 51 Views .. 52 Constraints .. 52 Assertions .. 53 Triggers .. 53 Routines .. 54 Indexes .. 54 Synonyms .. 54 Statements for Schema Definition and Manipulation .. 54 Common Elements and Statements .. 54 Renaming Objects .. 56 Commenting Objects .. 56 Schema Creation .. 57 Table Creation .. 58 Table Manipulation .. 63 View Creation and Manipulation .. 68 Domain Creation and Manipulation .. 69 Trigger Creation .. 70 Routine Creation .. 72 Sequence Creation .. 74 SQL Procedure Statement.

5 76 Other Schema Object Creation .. 76 The Information Schema .. 80 Predefined Character Sets, Collations and Domains .. 80 Views in INFORMATION SCHEMA .. 80 Visibility of Information .. 80 Name Information .. 80 Data Type Information .. 81 Product Information .. 81 Operations Information .. 81 SQL Standard Views .. 815. Text Tables .. 88 Overview .. 88 The Implementation .. 88 Definition of Tables .. 88 HyperSQL user GuidevScope and Reassignment .. 88 Null Values in Columns of Text Tables .. 89 Configuration .. 89 Disconnecting Text Tables .. 91 Text File Usage .. 91 Text File Global Properties .. 92 Transactions .. 936. Access Control .. 94 Overview .. 94 Authorizations and Access Control .. 94 Built-In Roles and Users.

6 95 Listing Users and Roles .. 96 Access Rights .. 96 Statements for Authorization and Access Control .. 977. Data Access and Change .. 102 Overview .. 102 Cursors And Result Sets .. 102 Columns and Rows .. 102 Navigation .. 102 Updatability .. 103 Sensitivity .. 104 Holdability .. 104 Autocommit .. 104 JDBC Overview .. 104 JDBC Parameters .. 105 JDBC and Data Change Statements .. 105 JDBC Callable Statement .. 106 JDBC Returned Values .. 106 Cursor Declaration .. 107 Syntax Elements .. 107 Literals .. 107 References, etc.. 111 Value Expression .. 112 Predicates .. 119 Aggregate Functions .. 125 Other Syntax Elements .. 127 Data Access Statements .. 128 Select Statement.

7 129 Table .. 129 Subquery .. 129 Query Specification .. 130 Table Expression .. 130 Joined Table .. 133 Selection .. 135 Projection .. 135 Computed Columns .. 136 Naming .. 136 Grouping Operations .. 137 Aggregation .. 138 Set Operations .. 138 With Clause and Recursive Queries .. 138 Query Expression .. 139 Ordering .. 140 Slicing .. 141 HyperSQL user GuideviData Change Statements .. 142 Delete Statement .. 142 Truncate Statement .. 142 Insert Statement .. 143 Update Statement .. 144 Merge Statement .. 145 Diagnostics and State .. 1478. SQL-Invoked Routines .. 148 Routine Definition .. 149 Routine Characteristics .. 151 SQL Language Routines (PSM) .. 153 Advantages and Disadvantages.

8 153 Routine Statements .. 154 Compound Statement .. 155 Table Variables .. 155 Variables .. 156 Cursors .. 156 Handlers .. 157 Assignment Statement .. 158 Select Statement : Single Row .. 159 Formal Parameters .. 159 Iterated Statements .. 160 Iterated FOR Statement .. 160 Conditional Statements .. 161 Return Statement .. 162 Control Statements .. 163 Raising Exceptions .. 163 Routine Polymorphism .. 164 Returning Data From Procedures .. 164 Recursive Routines .. 166 Java Language Routines (SQL/JRT) .. 167 Polymorphism .. 168 Java Language Procedures .. 169 Java Static Methods .. 170 Legacy Support .. 171 Securing Access to Classes and Routines .. 171 Warning .. 172 user -Defined Aggregate Functions.

9 172 Definition of Aggregate Functions .. 172 SQL PSM Aggregate Functions .. 173 Java Aggregate Functions .. 1759. Triggers .. 176 Overview .. 176 BEFORE Triggers .. 176 AFTER Triggers .. 177 INSTEAD OF Triggers .. 177 Trigger Properties .. 177 Trigger Event .. 177 Granularity .. 177 Trigger Action Time .. 178 References to Rows .. 178 Trigger Condition .. 178 Trigger Action in SQL .. 178 Trigger Action in Java .. 179 HyperSQL user GuideviiTrigger Creation .. 18010. Built In Functions .. 183 Overview .. 183 String and Binary String Functions .. 184 Numeric Functions .. 190 Date Time and Interval Functions .. 195 Functions to Report the Time Zone.. 195 Functions to Report the Current Datetime.

10 196 Functions to Extract an Element of a Datetime .. 197 Functions for Datetime Arithmetic .. 199 Functions to Convert or Format a Datetime .. 202 Array Functions .. 205 General Functions .. 206 System Functions .. 20811. System Management .. 213 Modes of Operation .. 213 Deployment Types .. 213 Database Types .. 213 Tables .. 214 Large Objects .. 214 Deployment context .. 215 ACID, Persistence and Reliability .. 215 Atomicity, Consistency, Isolation, Durability .. 216 System Operations .. 216 Backing Up and Restoring Database Catalogs .. 217 Making Online Backups .. 217 Offline Backup Utility Syntax .. 217 Making Offline Backups .. 218 Examining Backups .. 218 Restoring a Backup .. 218 Encrypted Databases .. 218 Creating and Accessing an Encrypted Database .