# H2 database

## Telosys database configuration

Below are examples of typical configurations for **H2** database.

### Since Telosys 4.3

```yaml
  - id: h2-srv
    name: my H2 database server with storage in file
    type: H2 
    # JDBC connection 
    url: jdbc:h2:tcp://localhost:9092/D:/Z/db-data/mydatabase
    user: sa
    password: sa
    # Metadata parameters (schema name in UPPER CASE)
    schema: 'CARS'
```

### Before Telosys 4.3

```yaml
  - id: h2-srv
    name: my H2 database server with storage in file
    type: H2 
    # JDBC connection 
    url: jdbc:h2:tcp://localhost:9092/D:/Z/db-data/mydatabase
    driver: org.h2.Driver
    user: sa
    password: sa
    # Metadata parameters (schema name in UPPER CASE)
    catalog: '!'
    schema: 'CARS'
    tableNamePattern: '%'
    tableTypes: TABLE

```

### JDBC driver

* The H2 JDBC driver is located in the single JAR provided by H2. 
* JAR file example:  `h2-2.3.232.jar`
* Driver class name : **org.h2.Driver**  

## Technical information about H2

### Structure

**Database** → **Schema** → Tables/Objects

<div align="left"><figure><img src="https://434201135-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-MZMZz3NJC5MgGbWIG7J%2Fuploads%2FjUBkAaXLkvwuwwp5If5d%2Fimage.png?alt=media&#x26;token=95fdd3aa-7012-4618-93e3-55d2bde6ec6d" alt="" width="246"><figcaption></figcaption></figure></div>

* **Database**: In H2, the "database" is basically the connection (in-memory or file). \
  There’s only one database per connection.
* **Schema**: H2 supports schemas, similar to PostgreSQL and SQL Server. \
  Default schema is `PUBLIC`. \
  You can create others (`CREATE SCHEMA sales;`). Objects belong to schemas.

### H2 case conversion rules

* **Unquoted identifiers**
  * Always **converted to UPPERCASE**, following the SQL standard.
  * Applies to both **table names** and **column names**.
* **Quoted identifiers**
  * Case is **preserved exactly as written**
  * They are **case-sensitive** in SQL requests

### H2 modes

H2 can run in **3 main modes**:

1. **Embedded**  (in-process)
   * URL with data storage **in memory**:\
     `jdbc:h2:`**`mem`**`:{database-name}`
   * URL with data storage **in file**: \
     `jdbc:h2:`**`{dir-path}`**`/{database-name}`
2. **Remote Server mode** (accepting external connections)

   * URL with data storage **in memory**:\
     `jdbc:h2:`<mark style="color:red;">**`tcp`**</mark><mark style="color:red;">`://{host}:{port}`</mark>`/`**`mem`**`:{database-name}`
   * URL with data storage **in file**: \
     `jdbc:h2:`<mark style="color:red;">**`tcp`**</mark><mark style="color:red;">`://{host}:{port}`</mark>`/`**`{dir-path}`**`/{database-name}`

   For "SSL" replace "tcp" by "ssl"
3. **Mixed mode** (embedded + server at the same time)