Transcription
HyperSQL User GuideHyperSQL Database Engine 2.6.1Edited by , Blaine Simpson, and Fred Toussi
HyperSQL User Guide: HyperSQL Database Engine 2.6.1by , Blaine Simpson, and Fred Toussi Revision: 6369 Publication date 2021-10-21Copyright 2002-2021 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 form.
Table of ContentsPreface . xivAvailable formats for this document . xiv1. Running and Using HyperSQL . 1Introduction . 1The HSQLDB Jar . 1Running Database Access Tools . 2A HyperSQL Database . 2In-Process Access to Database Catalogs . 3Server Modes . 4HyperSQL HSQL Server . 4HyperSQL HTTP Server . 5HyperSQL HTTP Servlet . 5Connecting to a Database Server . 5Security Considerations . 6Using Multiple Databases . 6Accessing the Data . 6Closing the Database . 7Creating a New Database . 72. SQL Language . 9SQL Standards Support . 9Definition Statements (DDL and others) . 10Data Manipulation Statements (DML) . 10Data Query Statements (DQL) . 11Calling User Defined Procedures and Functions . 11Setting Properties for the Database and the Session . 11General Operations on Database . 11Transaction Statements . 12Comments in Statements . 12Statements in SQL Routines . 13SQL Data and Tables . 13Case Sensitivity . 13Persistent Tables . 13Temporary Tables . 14Short Guide to Data Types . 14Data Types and Operations . 16Numeric Types . 17Boolean Type . 19Character String Types . 19Binary String Types . 20Bit String Types . 21Lob Data . 21Storage and Handling of Java Objects . 22Type Length, Precision and Scale . 23Datetime types . 23Interval Types . 27Arrays . 31Array Definition . 31Array Reference . 32Array Operations . 333. Schemas and Database Objects . 36Overview . 36iii
HyperSQL User GuideSchemas and Schema Objects . 36Names and References . 37Character Sets . 37Collations . 38Distinct Types . 39Domains . 39Number Sequences . 39Tables . 42Views . 42Constraints . 42Assertions . 44Triggers . 44Routines . 44Indexes . 44Synonyms . 44Statements for Schema Definition and Manipulation . 45Common Elements and Statements . 45Renaming Objects . 46Commenting Objects . 47Schema Creation . 47Table Creation . 48Temporal System-Versioned Tables and SYSTEM TIME Period . 54Table Settings . 55Table Manipulation . 57View Creation and Manipulation . 61Domain Creation and Manipulation . 62Trigger Creation . 63Routine Creation . 64Sequence Creation . 67SQL Procedure Statement . 68Other Schema Objects Creation and Alteration . 69The Information Schema . 72References to Database Objects . 73Predefined Character Sets, Collations and Domains . 73Views in INFORMATION SCHEMA . 73Visibility of Information . 73Name Information . 74Data Type Information . 74Product Information . 74Operations Information . 74SQL Standard Views . 754. Built In Functions . 82Overview . 82String and Binary String Functions . 83Numeric Functions . 90Date Time and Interval Functions . 95Functions to Report the Time Zone. . 95Functions to Report the Current Datetime . 96Functions to Extract an Element of a Datetime . 97Functions for Datetime Arithmetic . 99Functions to Convert or Format a Datetime . 102Array Functions . 105General Functions . 106System Functions . 109iv
HyperSQL User Guide5. Data Access and Change .Overview .Cursors And Result Sets .Columns and Rows .Navigation .Updatability .Sensitivity .Holdability .Autocommit .JDBC Overview .JDBC Parameters .JDBC and Data Change Statements .JDBC Callable Statement .JDBC Returned Values .Cursor Declaration .Syntax Elements .Literals .References, etc. .Value Expression .Predicates .Aggregate Functions .Other Syntax Elements .Data Access Statements .Select Statement .Table .Subquery .Query Specification .Table Expression .Joined Table .Selection .Projection .Computed Columns .Naming .Grouping Operations .Aggregation .Set Operations .With Clause and Recursive Queries .Query Expression .Ordering .Slicing .Indexes Used in SELECT and DML Statements .Data Change Statements .Delete Statement .Truncate Statement .Insert Statement .Update Statement .Merge Statement .Diagnostics and State .6. Sessions and Transactions .Overview .Session Attributes and Variables .Session Attributes .Session Variables .Session Tables 62162163163163
HyperSQL User GuideTransactions and Concurrency Control .Two Phase Locking .Two Phase Locking with Snapshot Isolation .Lock Contention in 2PL .Locks in SQL Routines and Triggers .MVCC .Choosing the Transaction Model .Schema and Database Change .Simultaneous Access to Tables .Viewing Sessions .Session and Transaction Control Statements .7. Text Tables .Overview .The Implementation .Definition of Tables .Scope and Reassignment .Null Values in Columns of Text Tables .Configuration .Disconnecting Text Tables .Text File Usage .Text File Global Properties .Transactions .8. Access Control .Overview .Authorizations and Access Control .Built-In Roles and Users .Listing Users and Roles .Access Rights .Fine-Grained Data Access Control .Statements for Authorization and Access Control .9. SQL-Invoked Routines .Overview .Routine Definition .Routine Characteristics .SQL Language Routines (PSM) .Advantages and Disadvantages .Routine Statements .Compound Statement .Table Variables .Variables .Cursors .Handlers .Assignment Statement .Select Statement : Single Row .Formal Parameters .Iterated Statements .Iterated FOR Statement .Conditional Statements .Return Statement .Control Statements .Raising Exceptions .Routine Polymorphism .Returning Data From Procedures .Recursive Routines 206206207208209
HyperSQL User GuideJava Language Routines (SQL/JRT) .Polymorphism .Java Language Procedures .Java Static Methods .Legacy Support .Securing Access to Classes and Routines .Warning .User-Defined Aggregate Functions .Definition of Aggregate Functions .SQL PSM Aggregate Functions .Java Aggregate Functions .10. Triggers .Overview .BEFORE Triggers .AFTER Triggers .INSTEAD OF Triggers .Trigger Properties .Trigger Event .
HyperSQL User Guide HyperSQL Database Engine
