RelStorage

https://github.com/zodb/relstorage
Admin
RelStorage is a storage implementation for ZODB that stores pickles in a relational database,...
Tokens:21,337
Snippets:318
Trust Score:6.6
Update:3 months ago
Show doc for...
Tokens:
Raw
### Combined URI and query string example

Source: https://github.com/zodb/relstorage/blob/master/docs/zodburi.rst

An example demonstrating a PostgreSQL connection string with additional query parameters.

```text
postgres://someuser:somepass@somehost:5432/somedb?connection_cache_size=20000
```

--------------------------------

### Start MySQL utility

Source: https://github.com/zodb/relstorage/blob/master/docs/mysql/setup.rst

Command to initiate the MySQL client with root privileges.

```shell
$ mysql -u root -p
```

--------------------------------

### Install RelStorage database adapter extras

Source: https://github.com/zodb/relstorage/blob/master/docs/install.rst

Installs the recommended database driver for the specified backend using pip extras.

```bash
pip install "RelStorage[mysql]"
pip install "RelStorage[postgresql]"
pip install "RelStorage[oracle]"
pip install "RelStorage[sqlite3]"
```

--------------------------------

### Start Oracle SYS session

Source: https://github.com/zodb/relstorage/blob/master/docs/developing/running.rst

Commands to initiate a SYS session for Oracle database setup.

```shell
$ su - oracle
    $ sqlplus / as sysdba
```

--------------------------------

### Install test dependencies

Source: https://github.com/zodb/relstorage/blob/master/docs/developing/running.rst

Installs RelStorage with test and database-specific dependencies.

```shell
pip install -e ".[test,mysql]"
```

--------------------------------

### Initial ZlibStorage Configuration

Source: https://github.com/zodb/relstorage/blob/master/docs/zodbconvert.rst

Example of a standard configuration using a zlibstorage wrapper around a filestorage.

```xml
<zlibstorage>
  <filestorage>
    path /zope/var/Data.fs
  </filestorage>
</zlibstorage>
```

--------------------------------

### Database URI connection strings

Source: https://github.com/zodb/relstorage/blob/master/docs/zodburi.rst

Examples of connection strings for PostgreSQL and MySQL databases.

```text
postgres://someuser:somepass@somehost:5432/somedb?connection_cache_size=20000
mysql://someuser:somepass@somehost:5432/somedb?connection_cache_size=20000
```

--------------------------------

### Connect via SQLPlus as Sysdba

Source: https://github.com/zodb/relstorage/blob/master/docs/developing/running.rst

Example command for connecting to the database as sysdba.

```bash
sqlplus 'sys/oracle@192.168.1.131/orcl' as sysdba
```

--------------------------------

### Install RelStorage via pip

Source: https://github.com/zodb/relstorage/blob/master/docs/install.rst

Standard installation command for the RelStorage package.

```bash
pip install RelStorage
```

--------------------------------

### Example ZConfig configuration file for zodbpack

Source: https://github.com/zodb/relstorage/blob/master/docs/zodbpack.rst

A sample configuration file format for defining storage packing settings.

```text
<relstorage>
  pack-gc true
  <mysql>
    db zodb
  </mysql>
</relstorage>
```

--------------------------------

### PostgreSQL DSN Configuration

Source: https://github.com/zodb/relstorage/blob/master/docs/postgresql/options.rst

Example of a standard PostgreSQL Data Source Name (DSN) string.

```text
dbname='zodb' user='username' host='localhost' password='pass'
```

--------------------------------

### ZConfig Database Configurations

Source: https://github.com/zodb/relstorage/blob/master/docs/configure-application.rst

Examples of defining database-specific storage backends within a <relstorage> element.

```zconfig
      <relstorage>
        <postgresql>
          # The dsn is optional, as are each of the parameters in the dsn.
          dsn dbname='zodb' user='username' host='localhost' password='pass'
        </postgresql>
      </relstorage>
```

```zconfig
      <relstorage>
        <mysql>
          # Most of the options provided by MySQLdb are available.
          # See component.xml.
          db zodb
        </mysql>
      </relstorage>
```

```zconfig
      <relstorage>
        <oracle>
          user username
          password pass
          dsn XE
        </oracle>
     </relstorage>
```

```zconfig
    <relstorage>
        keep-history false
        cache-local-mb 0
        <sqlite3>
           data-dir /path/to/database/
        </sqlite3>
    </relstorage>
```

--------------------------------

### Start a SYS session in Oracle

Source: https://github.com/zodb/relstorage/blob/master/docs/oracle/setup.rst

Use these shell commands to initiate a session with SYS privileges on Oracle 10g XE.

```shell
$ su - oracle
$ sqlplus / as sysdba
```

--------------------------------

### Configure SQLite PRAGMAs

Source: https://github.com/zodb/relstorage/blob/master/docs/sqlite3/options.rst

Example configuration for optimizing SQLite performance by adjusting PRAGMA settings within the <sqlite3> ZConfig section.

```text
<sqlite3>
    data-dir /dev/shm
    <pragmas>
        synchronous off
        checkpoint_fullfsync off
        defer_foreign_keys on
        fullfsync off
        ignore_check_constraints on
        foreign_keys off
    </pragmas>
</sqlite3>
```

--------------------------------

### Enable Explicit Transaction Management

Source: https://github.com/zodb/relstorage/blob/master/docs/things-to-know.rst

Configures the global transaction manager to require explicit begin and commit/abort calls, improving efficiency and preventing unintended transaction starts.

```python
import transaction
transaction.manager.explicit = True
transaction.begin()
try:
    ...
except:
    transaction.abort()
    raise
else:
    transaction.commit()
```

--------------------------------

### Create user and database

Source: https://github.com/zodb/relstorage/blob/master/docs/mysql/setup.rst

SQL statements to initialize the database, create a dedicated user, and grant necessary privileges.

```sql
CREATE USER 'zodbuser'@'localhost' IDENTIFIED BY 'mypassword';
CREATE DATABASE zodb;
GRANT ALL ON zodb.* TO 'zodbuser'@'localhost';
FLUSH PRIVILEGES;
```

--------------------------------

### Create RelStorage User and Database via SQL

Source: https://github.com/zodb/relstorage/blob/master/docs/postgresql/setup.rst

Alternative method to create a user and database using the psql client.

```shell
$ psql -U postgres -c "CREATE USER zodbuser WITH PASSWORD 'relstoragetest';"
$ psql -U postgres -c "CREATE DATABASE zodb OWNER zodbuser;"
```

--------------------------------

### Build RelStorage with multiple database drivers

Source: https://github.com/zodb/relstorage/blob/master/BUILDOUT.rst

Use this command to include support for multiple databases during the buildout process.

```bash
bin/buildout db=postgresql,mysql,oracle
```

--------------------------------

### Build RelStorage with a single database driver

Source: https://github.com/zodb/relstorage/blob/master/BUILDOUT.rst

Use this command to include support for only the PostgreSQL database driver.

```bash
bin/buildout db=postgresql
```

--------------------------------

### Run Cache Analysis Script

Source: https://github.com/zodb/relstorage/blob/master/src/relstorage/cache/tests/cache_trace_analysis.rst

Initializes the cache analysis environment and executes the cache run simulation.

```python
>>> from relstorage.cache.tests.test_cache_stats import cache_run
>>> import ZEO.scripts.cache_stats
>>> import ZEO.scripts.cache_simul
>>> cache_run('cache', 2)
```

--------------------------------

### Create RelStorage User and Database via CLI

Source: https://github.com/zodb/relstorage/blob/master/docs/postgresql/setup.rst

Use these commands to create a dedicated user and database for RelStorage when logged in as the postgres system user.

```shell
$ sudo su - postgres
$ createuser --pwprompt zodbuser
$ createdb -O zodbuser zodb
```

--------------------------------

### Sample zodbconvert configuration file

Source: https://github.com/zodb/relstorage/blob/master/docs/zodbconvert.rst

A ZConfig-style configuration defining a FileStorage source and a MySQL RelStorage destination.

```guess
<filestorage source>
  path /zope/var/Data.fs
</filestorage>

<relstorage destination>
  <mysql>
    db zodb
  </mysql>
</relstorage>
```

--------------------------------

### Simulate ZEO Cache Performance

Source: https://github.com/zodb/relstorage/blob/master/docs/cache-tracing.rst

Run the cache simulation script with specified cache sizes to observe hit rates and eviction statistics.

```bash
$ python -m ZEO.scripts.cache_simul.py -s 4 /tmp/cachetrace.log
```

```bash
$ python -m ZEO.scripts.cache_simul.py -s 8 /tmp/cachetrace.log
```

```bash
$ python -m ZEO.scripts.cache_simul.py /tmp/cachetrace.log
```

```bash
$ python -m ZEO.scripts.cache_simul.py -s 100 /tmp/cachetrace.log
```

--------------------------------

### Manual ZODB Configuration in Python

Source: https://github.com/zodb/relstorage/blob/master/docs/configure-application.rst

Opening a ZODB database manually using ZConfig files or strings.

```python
   path = "path/to/configuration"
   import ZODB.config
   db = ZODB.config.databaseFromURL(path)
   conn = db.open()
   ...
```

```python
   conf = """
   %%import relstorage
   <zodb main>
     <relstorage>
      <mysql>
        db %s
        user %s
        passwd %s
      </mysql>
    </relstorage>
   </zodb>
   """ % (dbname, user, passwd)

   db = ZODB.config.databaseFromString(conf)
   conn = db.open()
   ...
```

--------------------------------

### Configure RelStorage in buildout

Source: https://github.com/zodb/relstorage/blob/master/docs/configure-application.rst

Defines the database connection parameters for RelStorage within a configuration block.

```ini
rel-storage =
    type mysql
    db plone
    user plone
    passwd PASSWORD
```

--------------------------------

### Zodbconvert Help Command

Source: https://github.com/zodb/relstorage/blob/master/docs/zodbconvert.rst

Command to display help options for the zodbconvert utility.

```bash
zodbconvert --help
```

--------------------------------

### Simulate Cache Behavior

Source: https://github.com/zodb/relstorage/blob/master/src/relstorage/cache/tests/cache_trace_analysis.rst

Runs a circular cache simulation with a specified size using a trace file.

```python
>>> ZEO.scripts.cache_simul.main('-s 4 relstorage-trace-cache.0.trace'.split())
```

--------------------------------

### Create Oracle Test Users

Source: https://github.com/zodb/relstorage/blob/master/docs/developing/running.rst

SQL commands to create and grant necessary privileges to test users for RelStorage.

```sql
CREATE USER relstoragetest IDENTIFIED BY relstoragetest;
GRANT CONNECT, RESOURCE, CREATE TABLE, CREATE SEQUENCE, CREATE VIEW TO relstoragetest;
GRANT EXECUTE ON DBMS_LOCK TO relstoragetest;
CREATE USER relstoragetest2 IDENTIFIED BY relstoragetest;
GRANT CONNECT, RESOURCE, CREATE TABLE, CREATE SEQUENCE, CREATE VIEW TO relstoragetest2;
GRANT EXECUTE ON DBMS_LOCK TO relstoragetest2;
CREATE USER relstoragetest_hf IDENTIFIED BY relstoragetest;
GRANT CONNECT, RESOURCE, CREATE TABLE, CREATE SEQUENCE, CREATE VIEW TO relstoragetest_hf;
GRANT EXECUTE ON DBMS_LOCK TO relstoragetest_hf;
CREATE USER relstoragetest2_hf IDENTIFIED BY relstoragetest;
GRANT CONNECT, RESOURCE, CREATE TABLE, CREATE SEQUENCE, CREATE VIEW TO relstoragetest2_hf;
GRANT EXECUTE ON DBMS_LOCK TO relstoragetest2_hf;
```

--------------------------------

### repoze.zodbconn Configuration

Source: https://github.com/zodb/relstorage/blob/master/docs/configure-application.rst

Using repoze.zodbconn to create a ZODB database from a ZConfig URI.

```zconfig
    %import relstorage
    <zodb main>
      <relstorage>
        <mysql>
          db zodb
        </mysql>
      </relstorage>
      cache-size 100000
    </zodb>
```

```python
    from repoze.zodbconn.uri import db_from_uri
    db = db_from_uri('zconfig://path/to/configuration#main')
```

--------------------------------

### Run Cache Simulation with ZEO Scripts

Source: https://github.com/zodb/relstorage/blob/master/src/relstorage/cache/tests/cache_trace_analysis.rst

Executes a circular cache simulation using a trace file and specified parameters.

```python
>>> ZEO.scripts.cache_simul.main('-s 2 -i 5 relstorage-trace-cache.0.trace'.split())
CircularCacheSimulation, cache size 2,097,152 bytes
  START TIME   DUR.   LOADS    HITS INVALS WRITES HITRATE  EVICTS   INUSE
Jul 11 12:11   3:17     180       1      0    195    0.6%       0    10.6
Jul 11 12:15   4:59     272      19      0    279    7.0%       0    26.3
Jul 11 12:20   4:59     273      35      0    260   12.8%       0    39.9
Jul 11 12:25   4:59     273      53      0    245   19.4%       0    54.1
Jul 11 12:30   4:59     272      60      0    232   22.1%       0    66.0
Jul 11 12:35   4:59     273      68      0    226   24.9%       0    78.4
Jul 11 12:40   4:59     273      85      0    207   31.1%       0    89.5
Jul 11 12:45   4:59     273      85      0    208   31.1%      36    99.1
Jul 11 12:50   4:59     272     106      0    185   39.0%     187    99.0
Jul 11 12:55   4:59     273     106      0    190   38.8%     179    99.2
Jul 11 13:00   4:59     273      91      0    197   33.3%     210    99.3
Jul 11 13:05   4:59     273     105      0    187   38.5%     181    99.0
Jul 11 13:10   4:59     272     101      0    183   37.1%     181    98.8
Jul 11 13:15   4:59     273      92      0    198   33.7%     220    98.4
Jul 11 13:20   4:59     273      99      0    192   36.3%     193    99.1
Jul 11 13:25   4:59     272      93      0    195   34.2%     199    98.8
Jul 11 13:30   4:59     273      80      0    206   29.3%     207    98.8
Jul 11 13:35   4:59     273      99      0    192   36.3%     182    99.2
Jul 11 13:40   4:59     273      95      0    199   34.8%     198    99.4
Jul 11 13:45   4:59     272      83      0    211   30.5%     215    98.7
Jul 11 13:50   4:59     273      85      0    207   31.1%     205    99.2
Jul 11 13:55   4:59     273      87      0    202   31.9%     194    98.6
Jul 11 14:00   4:59     273      95      0    194   34.8%     180    98.9
Jul 11 14:05   4:59     272      99      0    190   36.4%     196    98.8
Jul 11 14:10   4:59     273     112      0    180   41.0%     172    98.8
Jul 11 14:15   4:59     273      96      0    195   35.2%     193    99.1
Jul 11 14:20   4:59     272      87      0    196   32.0%     196    99.3
Jul 11 14:25   4:59     273      92      0    199   33.7%     221    98.9
Jul 11 14:30   4:59     273     100      0    187   36.6%     197    98.9
Jul 11 14:35   4:59     273      94      0    194   34.4%     181    99.4
Jul 11 14:40   4:59     272     105      0    185   38.6%     192    98.9
Jul 11 14:45   4:59     273      84      0    207   30.8%     216    99.4
Jul 11 14:50   4:59     273      94      0    199   34.4%     205    99.1
Jul 11 14:55   4:59     273      93      0    200   34.1%     198    99.3
Jul 11 15:00   4:59     272     101      0    191   37.1%     190    99.2
Jul 11 15:05   4:59     273      98      0    190   35.9%     189    99.0
Jul 11 15:10   4:59     273      97      0    191   35.5%     164    99.3
Jul 11 15:15      1       2       1      0      1   50.0%       2    99.2
--------------------------------------------------------------------------
Jul 11 12:45 2:30:01    8184    2855      0   5851   34.9%    5679    99.2
```

--------------------------------

### Configure PostgreSQL authentication

Source: https://github.com/zodb/relstorage/blob/master/docs/developing/running.rst

Required entries for pg_hba.conf to allow test database access.

```sql
local   relstoragetest     relstoragetest   md5
    local   relstoragetest2    relstoragetest   md5
    local   relstoragetest_hf  relstoragetest   md5
    local   relstoragetest2_hf relstoragetest   md5
    host    relstoragetest     relstoragetest   127.0.0.1/32 md5
    host    relstoragetest_hf  relstoragetest   127.0.0.1/32 md5
```

--------------------------------

### Run Oracle Tests

Source: https://github.com/zodb/relstorage/blob/master/docs/developing/running.rst

Command to execute tests using the Oracle layer.

```bash
ztest --layer Oracle
```

--------------------------------

### Re-applying Wrapper for RelStorage

Source: https://github.com/zodb/relstorage/blob/master/docs/zodbconvert.rst

Configuration to re-apply the zlibstorage wrapper after the conversion is complete.

```xml
<zlibstorage>
  <relstorage>
    <mysql>
      db zodb
    </mysql>
  </relstorage>
</zlibstorage>
```

--------------------------------

### Update Boost library version

Source: https://github.com/zodb/relstorage/blob/master/include/README.rst

Use a one-liner to synchronize files from a local Boost distribution directory to the project's include directory.

```bash
for i in  `find . -type f`; do gcp -f ../boost_1_75_0/boost/${i:2} $i; done
```

```bash
for i in  `find . -type f`; do gcp -f ../boost_1_83_0/boost/${i:2} $i; done
```

--------------------------------

### Simulate Cache Performance

Source: https://github.com/zodb/relstorage/blob/master/docs/cache-tracing.rst

Use the cache_simul.py script to predict cache behavior with different sizes based on recorded trace events.

```bash
python -m ZEO.scripts.cache_simul --help
```

--------------------------------

### Run Circular Cache Simulation

Source: https://github.com/zodb/relstorage/blob/master/src/relstorage/cache/tests/cache_trace_analysis.rst

Executes the cache simulation script with a specified cache size and trace file.

```python
>>> ZEO.scripts.cache_simul.main('-s 1 relstorage-trace-cache.0.trace'.split())
```

--------------------------------

### Analyze Cache Statistics

Source: https://github.com/zodb/relstorage/blob/master/src/relstorage/cache/tests/cache_trace_analysis.rst

Generates a performance report from a trace file, showing hit rates and action counts.

```python
>>> ZEO.scripts.cache_stats.main('relstorage-trace-cache4.0.trace'.split())
```

```python
>>> ZEO.scripts.cache_stats.main('relstorage-trace-cache1.0.trace'.split())
```

--------------------------------

### Run tests with zope.testrunner

Source: https://github.com/zodb/relstorage/blob/master/docs/developing/running.rst

Executes the test suite using zope.testrunner, which supports advanced features like layers.

```shell
zope-testrunner --test-path=src
```

--------------------------------

### Run tests with tox

Source: https://github.com/zodb/relstorage/blob/master/docs/developing/running.rst

Executes tests for a specific database and Python version using tox.

```shell
tox -e py27-mysql
```

--------------------------------

### Generate Cache Statistics

Source: https://github.com/zodb/relstorage/blob/master/src/relstorage/cache/tests/cache_trace_analysis.rst

Executes the cache statistics main function on a trace file to display load, hit, and write metrics.

```python
>>> ZEO.scripts.cache_stats.main(['relstorage-trace-cache.0.trace'])
                   loads    hits  inv(h)  writes hitrate
Jul 11 12:11-11       0       0       0       0     n/a
Jul 11 12:11:41 ==================== Restart ====================
Jul 11 12:11-14     180       0       0     198    0.0%
Jul 11 12:15-29     818      16       0     884    2.0%
Jul 11 12:30-44     818      53       0     847    6.5%
Jul 11 12:45-59     818      93       0     807   11.4%
Jul 11 13:00-14     818     120       0     780   14.7%
Jul 11 13:15-29     818     207       0     693   25.3%
Jul 11 13:30-44     819     235       0     665   28.7%
Jul 11 13:45-59     818     258       0     642   31.5%
Jul 11 14:00-14     818     318       0     582   38.9%
Jul 11 14:15-29     818     383       0     517   46.8%
Jul 11 14:30-44     818     427       0     473   52.2%
Jul 11 14:45-59     819     453       0     447   55.3%
Jul 11 15:00-14     818     463       0     437   56.6%
Jul 11 15:15-15       2       2       0       0  100.0%
<BLANKLINE>
Read 17,973 trace records (611,074 bytes) in 0.0 seconds
Versions:   0 records used a version
First time: Sun Jul 11 12:11:41 2010
Last time:  Sun Jul 11 15:15:01 2010
Duration:   11,000 seconds
Data recs:  11,000 (61.2%), average size 1105 bytes
Hit rate:   30.3% (load hits / loads)
<BLANKLINE>
        Count Code Function (action)
            1  00  _setup_trace (initialization)
        6,972  20  load (miss)
        3,028  22  load (hit)
        7,972  52  store (current, non-version)
```

--------------------------------

### Enable plpgsql in PostgreSQL

Source: https://github.com/zodb/relstorage/blob/master/docs/migration/migrate-to-1.5.rst

Required command to enable the plpgsql language in the database, typically executed by a superuser.

```sql
CREATE LANGUAGE plpgsql;
```

--------------------------------

### Analyze Cache Trace Statistics

Source: https://github.com/zodb/relstorage/blob/master/docs/cache-tracing.rst

Use the cache_stats.py script to generate a summary of cache hit rates and activity statistics from a trace file.

```bash
python -m ZEO.scripts.cache_stats --help
```

--------------------------------

### Migrate PostgreSQL 8.2 and below to RelStorage 1.0

Source: https://github.com/zodb/relstorage/blob/master/docs/migration/migrate-to-1.0.rst

Use this script with the psql command for older PostgreSQL versions. Adjust the encoding if necessary.

```sql
ALTER TABLE transaction
    ALTER username TYPE BYTEA USING
        (decode(replace(convert(username, 'UTF-8'), '\\', '\\\\'), 'escape')),
    ALTER description TYPE BYTEA USING
        (decode(replace(convert(description, 'UTF-8'), '\\', '\\\\'), 'escape'));
```

--------------------------------

### List available test layers

Source: https://github.com/zodb/relstorage/blob/master/docs/developing/running.rst

Discovers and lists available test layers using the test runner.

```console
$ ztest --list | grep Listing | sort
   Listing .MySQLCMySQLConnector_Python_SharedHistoryFreeBlobTests tests:
   ...
   Listing .Oraclecx_Oracle_SharedHistoryFreeBlobTests tests:
   Listing .Oraclecx_Oracle_SharedHistoryPreservingBlobTests tests:
   ...
   Listing .PostgreSQLgeventPsycopg2Driver_SharedHistoryFreeBlobTests tests:
   ...
   Listing .Sqlite3sqlite3_SharedHistoryFreeBlobTests tests:
   ...
```

--------------------------------

### Configure pg_hba.conf for Local Access

Source: https://github.com/zodb/relstorage/blob/master/docs/postgresql/setup.rst

Add this rule to the pg_hba.conf file to allow the zodbuser to access the zodb database via local connections.

```text
local  zodb  zodbuser  md5
```

--------------------------------

### SQLite data directory configuration

Source: https://github.com/zodb/relstorage/blob/master/docs/zodburi.rst

SQLite connections require a path to the directory where database files are stored.

```text
sqlite://?data_dir=/the/path/to/database
```

--------------------------------

### Create Oracle user and grant permissions

Source: https://github.com/zodb/relstorage/blob/master/docs/oracle/setup.rst

Execute these SQL statements to create a dedicated ZODB user and grant the necessary privileges, including DBMS_LOCK access.

```sql
CREATE USER zodb IDENTIFIED BY mypassword;
GRANT CONNECT, RESOURCE, CREATE TABLE, CREATE SEQUENCE, CREATE VIEW TO zodb;
GRANT EXECUTE ON DBMS_LOCK TO zodb;
```

--------------------------------

### Migrate PGStorage to RelStorage

Source: https://github.com/zodb/relstorage/blob/master/docs/migration/migrate-to-1.0.rst

A comprehensive SQL script to migrate an existing PGStorage database to RelStorage, including schema updates and table replacements.

```sql
-- Migration from PGStorage to RelStorage

-- Do all the work in a transaction
BEGIN;

-- Remove the commit_order information (RelStorage has a better solution).
DROP SEQUENCE commit_seq;
ALTER TABLE transaction DROP commit_order;

-- Make the special transaction 0 match RelStorage
UPDATE transaction SET username='system',
  description='special transaction for object creation'
  WHERE tid = 0;

-- Add the MD5 column and some more constraints.
ALTER TABLE object_state
  ADD CONSTRAINT object_state_tid_check CHECK (tid > 0),
  ADD CONSTRAINT object_state_prev_tid_fkey FOREIGN KEY (prev_tid)
      REFERENCES transaction,
  ADD COLUMN md5 CHAR(32);
UPDATE object_state SET md5=md5(state) WHERE state IS NOT NULL;

-- Replace the temporary tables used for packing.
DROP TABLE pack_operation;
DROP TABLE pack_transaction;
DROP TABLE pack_keep;
DROP TABLE pack_garbage;
CREATE TABLE pack_lock ();
CREATE TABLE object_ref (
    zoid        BIGINT NOT NULL,
    tid         BIGINT NOT NULL,
    to_zoid     BIGINT NOT NULL
);
CREATE INDEX object_ref_from ON object_ref (zoid);
CREATE INDEX object_ref_tid ON object_ref (tid);
CREATE INDEX object_ref_to ON object_ref (to_zoid);
CREATE TABLE object_refs_added (
    tid         BIGINT NOT NULL PRIMARY KEY
);
CREATE TABLE pack_object (
    zoid        BIGINT NOT NULL PRIMARY KEY,
    keep        BOOLEAN NOT NULL,
    keep_tid    BIGINT
);
CREATE INDEX pack_object_keep_zoid ON pack_object (keep, zoid);

-- Now commit everything
COMMIT;
```

--------------------------------

### Identify unnecessary Boost headers

Source: https://github.com/zodb/relstorage/blob/master/include/README.rst

Use gcc -H to generate a header include tree to identify unused portions of the included Boost library.

```bash
gcc -H src/relstorage/cache/c_cache.cpp -I include/ -I path/to/python -o /tmp/foo.o -c 2>&1 | grep boost
```

--------------------------------

### Reload PostgreSQL Configuration

Source: https://github.com/zodb/relstorage/blob/master/docs/postgresql/setup.rst

Execute this command to apply changes made to the pg_hba.conf file.

```shell
/etc/init.d/postgresql reload
```

--------------------------------

### Oracle DSN connection string

Source: https://github.com/zodb/relstorage/blob/master/docs/zodburi.rst

Oracle connections require a DSN string provided in the URI.

```text
oracle://?dsn="HERE GOES THE DSN"
```

--------------------------------

### Uncompressed RelStorage Configuration

Source: https://github.com/zodb/relstorage/blob/master/docs/zodbconvert.rst

Configuration that results in an uncompressed RelStorage destination despite the source being wrapped.

```xml
<zlibstorage>
  <filestorage source>
    path /zope/var/Data.fs
  </filestorage>
</zlibstorage>

<relstorage destination>
  <mysql>
    db zodb
  </mysql>
</relstorage>
```

--------------------------------

### Run tests with Python

Source: https://github.com/zodb/relstorage/blob/master/docs/developing/running.rst

Executes a specific test module directly using the Python interpreter.

```shell
python -m relstorage.tests.test_zodbconvert
```

--------------------------------

### Recreate object_refs_added table for history-free storage

Source: https://github.com/zodb/relstorage/blob/master/docs/migration/migrate-to-1.4.rst

Drop and recreate the object_refs_added table for history-free databases. Do not perform this on history-preserving databases.

```sql
DROP TABLE object_refs_added;
CREATE TABLE object_refs_added (
    zoid        BIGINT NOT NULL PRIMARY KEY,
    tid         BIGINT NOT NULL
);
```

```sql
DROP TABLE object_refs_added;
CREATE TABLE object_refs_added (
    zoid        BIGINT NOT NULL PRIMARY KEY,
    tid         BIGINT NOT NULL
) ENGINE = MyISAM;
```

```sql
DROP TABLE object_refs_added;
CREATE TABLE object_refs_added (
    zoid        NUMBER(20) NOT NULL PRIMARY KEY,
    tid         NUMBER(20) NOT NULL
);
```

--------------------------------

### Update Oracle schema for RelStorage 1.1.2

Source: https://github.com/zodb/relstorage/blob/master/docs/migration/migrate-to-1.1.2.rst

Execute these SQL commands on an Oracle database to recreate the temp_pack_visit table with the required schema.

```sql
DROP TABLE temp_pack_visit;
CREATE GLOBAL TEMPORARY TABLE temp_pack_visit (
    zoid        NUMBER(20) NOT NULL PRIMARY KEY,
    keep_tid    NUMBER(20)
);
```

--------------------------------

### Migrate Oracle to RelStorage 1.0

Source: https://github.com/zodb/relstorage/blob/master/docs/migration/migrate-to-1.0.rst

Use this script with the sqlplus command to migrate the transaction table structure and data.

```sql
ALTER TABLE transaction ADD (
    new_username    RAW(500),
    new_description RAW(2000),
    new_extension   RAW(2000));

UPDATE transaction
    SET new_username = UTL_I18N.STRING_TO_RAW(username, 'UTF8'),
        new_description = UTL_I18N.STRING_TO_RAW(description, 'UTF8'),
        new_extension = extension;

ALTER TABLE transaction DROP (username, description, extension);
ALTER TABLE transaction RENAME COLUMN new_username TO username;
ALTER TABLE transaction RENAME COLUMN new_description TO description;
ALTER TABLE transaction RENAME COLUMN new_extension TO extension;
```

--------------------------------

### Zope 2 RelStorage Integration

Source: https://github.com/zodb/relstorage/blob/master/docs/configure-application.rst

Configuring RelStorage within the zope.conf file for Zope 2.

```zconfig
    %import relstorage
    <zodb_db main>
      mount-point /
      <relstorage>
        <postgresql>
          dsn dbname='zodb' user='username' host='localhost' password='pass'
        </postgresql>
      </relstorage>
    </zodb_db>
```

--------------------------------

### ZConfig Blob Storage Configuration

Source: https://github.com/zodb/relstorage/blob/master/docs/configure-application.rst

Configuring ZODB blob support by adding a blob-dir option to the relstorage element.

```zconfig
      <relstorage>
        blob-dir ./blobs
        <postgresql>
          dsn dbname='zodb' user='username' host='localhost' password='pass'
        </postgresql>
      </relstorage>
```

--------------------------------

### Register Database Close Handler

Source: https://github.com/zodb/relstorage/blob/master/docs/things-to-know.rst

Uses atexit to ensure the database is closed upon program termination, which is critical for flushing persistent caches in RelStorage.

```python
import atexit
from zope import component
from ZODB.interfaces import IDatabase

def close_database():
   db = component.getGlobalSiteManager().queryUtility(IDatabase)
   if db is None:
       return # already closed
   db.close()
   component.getGlobalSiteManager().unregisterUtility(db, IDatabase)

atexit.register(close_database)
db = ZODB.config.databaseFromFile('path/to/config')
component.getGlobalSiteManager().registerUtility(db, IDatabase)
try:
    serve_forever()
except:
     close_database()
     raise
```

--------------------------------

### Use a Non-Global Transaction Manager

Source: https://github.com/zodb/relstorage/blob/master/docs/things-to-know.rst

Initializes a dedicated transaction manager in explicit mode for a specific ZODB connection, avoiding reliance on the global manager.

```python
import transaction
txm = transaction.TransactionManager(explicit=True)
db = ZODB.config.databaseFromFile('path/to/config')
conn = db.open(txm)
txm.begin()
try:
    ...
except:
    transaction.abort()
    raise
else:
    transaction.commit()
```
Tokens:
Raw