[Checkins] SVN: relstorage/trunk/relstorage/adapters/ De-duplicated
the undo code by writing a SQL script that works
Shane Hathaway
shane at hathawaymix.org
Fri Feb 15 04:40:12 EST 2008
Log message for revision 83847:
De-duplicated the undo code by writing a SQL script that works
with all three supported databases.
Changed:
U relstorage/trunk/relstorage/adapters/common.py
U relstorage/trunk/relstorage/adapters/mysql.py
U relstorage/trunk/relstorage/adapters/oracle.py
U relstorage/trunk/relstorage/adapters/postgresql.py
-=-
Modified: relstorage/trunk/relstorage/adapters/common.py
===================================================================
--- relstorage/trunk/relstorage/adapters/common.py 2008-02-15 07:56:46 UTC (rev 83846)
+++ relstorage/trunk/relstorage/adapters/common.py 2008-02-15 09:40:11 UTC (rev 83847)
@@ -13,6 +13,8 @@
##############################################################################
"""Code common to most adapters."""
+from ZODB.POSException import UndoError
+
import logging
log = logging.getLogger("relstorage.adapters.common")
@@ -58,6 +60,23 @@
ORDER BY tid DESC
LIMIT 1
""",
+
+ 'create_temp_pack_visit': """
+ CREATE TEMPORARY TABLE temp_pack_visit (
+ zoid BIGINT NOT NULL
+ );
+ CREATE UNIQUE INDEX temp_pack_visit_zoid ON temp_pack_visit (zoid)
+ """,
+
+ 'create_temp_undo': """
+ CREATE TEMPORARY TABLE temp_undo (
+ zoid BIGINT NOT NULL,
+ prev_tid BIGINT NOT NULL
+ );
+ CREATE UNIQUE INDEX temp_undo_zoid ON temp_undo (zoid)
+ """,
+
+ 'reset_temp_undo': "DROP TABLE temp_undo",
}
@@ -143,6 +162,112 @@
return iter(cursor)
+ def verify_undoable(self, cursor, undo_tid):
+ """Raise UndoError if it is not safe to undo the specified txn."""
+ stmt = """
+ SELECT 1 FROM transaction
+ WHERE tid = %(undo_tid)s
+ AND packed = %(FALSE)s
+ """
+ self._run_script_stmt(cursor, stmt, {'undo_tid': undo_tid})
+ if not cursor.fetchall():
+ raise UndoError("Transaction not found or packed")
+
+ # Rule: we can undo an object if the object's state in the
+ # transaction to undo matches the object's current state.
+ # If any object in the transaction does not fit that rule,
+ # refuse to undo.
+ stmt = """
+ SELECT prev_os.zoid, current_object.tid
+ FROM object_state prev_os
+ JOIN object_state cur_os ON (prev_os.zoid = cur_os.zoid)
+ JOIN current_object ON (cur_os.zoid = current_object.zoid
+ AND cur_os.tid = current_object.tid)
+ WHERE prev_os.tid = %(undo_tid)s
+ AND cur_os.md5 != prev_os.md5
+ """
+ self._run_script_stmt(cursor, stmt, {'undo_tid': undo_tid})
+ if cursor.fetchmany():
+ raise UndoError(
+ "Some data were modified by a later transaction")
+
+ # Rule: don't allow the creation of the root object to
+ # be undone. It's hard to get it back.
+ stmt = """
+ SELECT 1
+ FROM object_state
+ WHERE tid = %(undo_tid)s
+ AND zoid = 0
+ AND prev_tid = 0
+ """
+ self._run_script_stmt(cursor, stmt, {'undo_tid': undo_tid})
+ if cursor.fetchall():
+ raise UndoError("Can't undo the creation of the root object")
+
+
+ def undo(self, cursor, undo_tid, self_tid):
+ """Undo a transaction.
+
+ Parameters: "undo_tid", the integer tid of the transaction to undo,
+ and "self_tid", the integer tid of the current transaction.
+
+ Returns the list of OIDs undone.
+ """
+ stmt = self._scripts['create_temp_undo']
+ if stmt:
+ self._run_script(cursor, stmt)
+
+ stmt = """
+ DELETE FROM temp_undo;
+
+ -- Put into temp_undo the list of objects to be undone and
+ -- the tid of the transaction that has the undone state.
+ INSERT INTO temp_undo (zoid, prev_tid)
+ SELECT zoid, prev_tid
+ FROM object_state
+ WHERE tid = %(undo_tid)s;
+
+ -- Override previous undo operations within this transaction
+ -- by resetting the current_object pointer and deleting
+ -- copied states from object_state.
+ UPDATE current_object
+ SET tid = (
+ SELECT prev_tid
+ FROM object_state
+ WHERE zoid = current_object.zoid
+ AND tid = %(self_tid)s
+ )
+ WHERE zoid IN (SELECT zoid FROM temp_undo)
+ AND tid = %(self_tid)s;
+
+ DELETE FROM object_state
+ WHERE zoid IN (SELECT zoid FROM temp_undo)
+ AND tid = %(self_tid)s;
+
+ -- Add new undo records.
+ INSERT INTO object_state (zoid, tid, prev_tid, md5, state)
+ SELECT temp_undo.zoid, %(self_tid)s, current_object.tid,
+ prev.md5, prev.state
+ FROM temp_undo
+ JOIN current_object ON (temp_undo.zoid = current_object.zoid)
+ LEFT JOIN object_state prev
+ ON (prev.zoid = temp_undo.zoid
+ AND prev.tid = temp_undo.prev_tid);
+
+ -- List the changed OIDs.
+ SELECT zoid FROM object_state WHERE tid = %(undo_tid)s
+ """
+ self._run_script(cursor, stmt,
+ {'undo_tid': undo_tid, 'self_tid': self_tid})
+ res = [oid_int for (oid_int,) in cursor]
+
+ stmt = self._scripts['reset_temp_undo']
+ if stmt:
+ self._run_script(cursor, stmt)
+
+ return res
+
+
def choose_pack_transaction(self, pack_point):
"""Return the transaction before or at the specified pack time.
@@ -258,7 +383,9 @@
"""
self._run_script(cursor, stmt, {'pack_tid': pack_tid})
- self._create_temp_pack_visit(cursor)
+ stmt = self._scripts['create_temp_pack_visit']
+ if stmt:
+ self._run_script(cursor, stmt)
# Each of the packable objects to be kept might
# refer to other objects. If some of those references
@@ -307,20 +434,6 @@
break
- def _create_temp_pack_visit(self, cursor):
- """Create a workspace for listing objects to visit.
-
- Subclasses can override this.
- """
- stmt = """
- CREATE TEMPORARY TABLE temp_pack_visit (
- zoid BIGINT NOT NULL
- );
- CREATE UNIQUE INDEX temp_pack_visit_zoid ON temp_pack_visit (zoid)
- """
- self._run_script(cursor, stmt)
-
-
def _fill_nonpacked_refs(self, cursor, pack_tid, get_references):
"""Fill object_ref for all transactions that will not be packed."""
stmt = """
Modified: relstorage/trunk/relstorage/adapters/mysql.py
===================================================================
--- relstorage/trunk/relstorage/adapters/mysql.py 2008-02-15 07:56:46 UTC (rev 83846)
+++ relstorage/trunk/relstorage/adapters/mysql.py 2008-02-15 09:40:11 UTC (rev 83847)
@@ -51,7 +51,7 @@
import logging
import MySQLdb
import time
-from ZODB.POSException import ConflictError, StorageError, UndoError
+from ZODB.POSException import StorageError
from common import Adapter
@@ -412,10 +412,7 @@
return tid, timestamp
def add_transaction(self, cursor, tid, username, description, extension):
- """Add a transaction.
-
- Raises ConflictError if the given tid has already been used.
- """
+ """Add a transaction."""
stmt = """
INSERT INTO transaction
(tid, username, description, extension)
@@ -523,101 +520,6 @@
cursor.execute(stmt)
- def verify_undoable(self, cursor, undo_tid):
- """Raise UndoError if it is not safe to undo the specified txn."""
- stmt = """
- SELECT 1 FROM transaction WHERE tid = %s AND packed = FALSE
- """
- cursor.execute(stmt, (undo_tid,))
- if not cursor.rowcount:
- raise UndoError("Transaction not found or packed")
-
- # Rule: we can undo an object if the object's state in the
- # transaction to undo matches the object's current state.
- # If any object in the transaction does not fit that rule,
- # refuse to undo.
- stmt = """
- SELECT prev_os.zoid, current_object.tid
- FROM object_state prev_os
- JOIN object_state cur_os ON (prev_os.zoid = cur_os.zoid)
- JOIN current_object ON (cur_os.zoid = current_object.zoid
- AND cur_os.tid = current_object.tid)
- WHERE prev_os.tid = %s
- AND cur_os.md5 != prev_os.md5
- LIMIT 1
- """
- cursor.execute(stmt, (undo_tid,))
- if cursor.rowcount:
- raise UndoError(
- "Some data were modified by a later transaction")
-
- # Rule: don't allow the creation of the root object to
- # be undone. It's hard to get it back.
- stmt = """
- SELECT 1
- FROM object_state
- WHERE tid = %s
- AND zoid = 0
- AND prev_tid = 0
- """
- cursor.execute(stmt, (undo_tid,))
- if cursor.rowcount:
- raise UndoError("Can't undo the creation of the root object")
-
-
- def undo(self, cursor, undo_tid, self_tid):
- """Undo a transaction.
-
- Parameters: "undo_tid", the integer tid of the transaction to undo,
- and "self_tid", the integer tid of the current transaction.
-
- Returns the list of OIDs undone.
- """
- stmt = """
- CREATE TEMPORARY TABLE temp_undo_state (
- zoid BIGINT NOT NULL PRIMARY KEY,
- md5 CHAR(32),
- state LONGBLOB
- );
-
- -- Copy the states to revert to into temp_undo_state.
- -- Some of the states can be null, indicating object uncreation.
- INSERT INTO temp_undo_state
- SELECT undoing.zoid, prev.md5, prev.state
- FROM object_state undoing
- LEFT JOIN object_state prev
- ON (prev.zoid = undoing.zoid
- AND prev.tid = undoing.prev_tid)
- WHERE undoing.tid = %(undo_tid)s;
-
- -- Update records produced by earlier undo operations
- -- within this transaction. Change the state, but not
- -- prev_tid, since prev_tid is still correct.
- UPDATE object_state
- JOIN temp_undo_state USING (zoid)
- SET object_state.md5 = temp_undo_state.md5,
- object_state.state = temp_undo_state.state
- WHERE tid = %(self_tid)s;
-
- -- Add new undo records.
- INSERT INTO object_state (zoid, tid, prev_tid, md5, state)
- SELECT zoid, %(self_tid)s, tid, md5, state
- FROM temp_undo_state
- JOIN current_object USING (zoid)
- WHERE zoid NOT IN (
- SELECT zoid FROM object_state WHERE tid = %(self_tid)s);
-
- DROP TABLE temp_undo_state;
- """
- self._run_script(cursor, stmt,
- {'undo_tid': undo_tid, 'self_tid': self_tid})
-
- # List the changed OIDs.
- stmt = "SELECT zoid FROM object_state WHERE tid = %s"
- cursor.execute(stmt, (undo_tid,))
- return [oid_int for (oid_int,) in cursor]
-
-
def pre_pack(self, pack_tid, get_references, gc=True):
"""Decide what to pack.
@@ -636,19 +538,6 @@
self.close(conn, cursor)
- def _create_temp_pack_visit(self, cursor):
- """Create a workspace for listing objects to visit.
-
- This overrides the method by the same name in common.Adapter.
- """
- stmt = """
- CREATE TEMPORARY TABLE temp_pack_visit (
- zoid BIGINT NOT NULL PRIMARY KEY
- )
- """
- cursor.execute(stmt)
-
-
def _hold_commit_lock(self, cursor):
"""Hold the commit lock for packing.
Modified: relstorage/trunk/relstorage/adapters/oracle.py
===================================================================
--- relstorage/trunk/relstorage/adapters/oracle.py 2008-02-15 07:56:46 UTC (rev 83846)
+++ relstorage/trunk/relstorage/adapters/oracle.py 2008-02-15 09:40:11 UTC (rev 83847)
@@ -18,7 +18,7 @@
import thread
import time
import cx_Oracle
-from ZODB.POSException import ConflictError, StorageError, UndoError
+from ZODB.POSException import StorageError
from common import Adapter
@@ -55,6 +55,10 @@
AND tid <= %(tid)s
AND packed = 'N'
""",
+
+ 'create_temp_pack_visit': None,
+ 'create_temp_undo': None,
+ 'reset_temp_undo': "DELETE FROM temp_undo",
}
def __init__(self, user, password, dsn, twophase=False, arraysize=64):
@@ -190,6 +194,13 @@
CREATE GLOBAL TEMPORARY TABLE temp_pack_visit (
zoid NUMBER(20) NOT NULL PRIMARY KEY
);
+
+ -- Temporary state during undo: a list of objects
+ -- to be undone and the tid of the undone state.
+ CREATE GLOBAL TEMPORARY TABLE temp_undo (
+ zoid NUMBER(20) NOT NULL PRIMARY KEY,
+ prev_tid NUMBER(20) NOT NULL
+ );
"""
self._run_script(cursor, stmt)
@@ -479,21 +490,15 @@
return tid, self._parse_dsinterval(now)
def add_transaction(self, cursor, tid, username, description, extension):
- """Add a transaction.
-
- Raises ConflictError if the given tid has already been used.
+ """Add a transaction."""
+ stmt = """
+ INSERT INTO transaction
+ (tid, username, description, extension)
+ VALUES (:1, :2, :3, :4)
"""
- try:
- stmt = """
- INSERT INTO transaction
- (tid, username, description, extension)
- VALUES (:1, :2, :3, :4)
- """
- cursor.execute(stmt, (
- tid, username or '-', description or '-',
- cx_Oracle.Binary(extension)))
- except cx_Oracle.IntegrityError:
- raise ConflictError
+ cursor.execute(stmt, (
+ tid, username or '-', description or '-',
+ cx_Oracle.Binary(extension)))
def detect_conflict(self, cursor):
"""Find one conflict in the data about to be committed.
@@ -601,109 +606,6 @@
pass
- def verify_undoable(self, cursor, undo_tid):
- """Raise UndoError if it is not safe to undo the specified txn."""
- stmt = """
- SELECT 1 FROM transaction WHERE tid = :1 AND packed = 'N'
- """
- cursor.execute(stmt, (undo_tid,))
- if not cursor.fetchall():
- raise UndoError("Transaction not found or packed")
-
- # Rule: we can undo an object if the object's state in the
- # transaction to undo matches the object's current state.
- # If any object in the transaction does not fit that rule,
- # refuse to undo.
- stmt = """
- SELECT prev_os.zoid, current_object.tid
- FROM object_state prev_os
- JOIN object_state cur_os ON (prev_os.zoid = cur_os.zoid)
- JOIN current_object ON (cur_os.zoid = current_object.zoid
- AND cur_os.tid = current_object.tid)
- WHERE prev_os.tid = :1
- AND cur_os.md5 != prev_os.md5
- """
- cursor.execute(stmt, (undo_tid,))
- if cursor.fetchmany():
- raise UndoError(
- "Some data were modified by a later transaction")
-
- # Rule: don't allow the creation of the root object to
- # be undone. It's hard to get it back.
- stmt = """
- SELECT 1
- FROM object_state
- WHERE tid = :1
- AND zoid = 0
- AND prev_tid = 0
- """
- cursor.execute(stmt, (undo_tid,))
- if cursor.fetchall():
- raise UndoError("Can't undo the creation of the root object")
-
-
- def undo(self, cursor, undo_tid, self_tid):
- """Undo a transaction.
-
- Parameters: "undo_tid", the integer tid of the transaction to undo,
- and "self_tid", the integer tid of the current transaction.
-
- Returns the list of OIDs undone.
- """
- # Update records produced by earlier undo operations
- # within this transaction. Change the state, but not
- # prev_tid, since prev_tid is still correct.
- # Table names: 'undoing' refers to the transaction being
- # undone and 'prev' refers to the object state identified
- # by undoing.prev_tid.
- stmt = """
- UPDATE object_state SET (md5, state) = (
- SELECT prev.md5, prev.state
- FROM object_state undoing
- LEFT JOIN object_state prev
- ON (prev.zoid = undoing.zoid
- AND prev.tid = undoing.prev_tid)
- WHERE undoing.tid = %(undo_tid)s
- AND undoing.zoid = object_state.zoid
- )
- WHERE tid = %(self_tid)s
- AND zoid IN (
- SELECT zoid FROM object_state WHERE tid = %(undo_tid)s);
-
- -- Add new undo records.
-
- INSERT INTO object_state (zoid, tid, prev_tid, md5, state)
- SELECT undoing.zoid, %(self_tid)s, current_object.tid,
- prev.md5, prev.state
- FROM object_state undoing
- JOIN current_object ON (current_object.zoid = undoing.zoid)
- LEFT JOIN object_state prev
- ON (prev.zoid = undoing.zoid
- AND prev.tid = undoing.prev_tid)
- WHERE undoing.tid = %(undo_tid)s
- AND undoing.zoid NOT IN (
- SELECT zoid FROM object_state WHERE tid = %(self_tid)s);
-
- -- List the changed OIDs.
-
- SELECT zoid FROM object_state WHERE tid = %(undo_tid)s
- """
- self._run_script(cursor, stmt,
- {'undo_tid': undo_tid, 'self_tid': self_tid})
-
- return [oid_int for (oid_int,) in cursor]
-
-
- def _create_temp_pack_visit(self, cursor):
- """Create a workspace for listing objects to visit.
-
- This overrides the method by the same name in common.Adapter.
- """
- # The temp_pack_visit table is a global temporary table,
- # so it does not need to be created here.
- pass
-
-
def _add_object_ref_rows(self, cursor, add_rows):
"""Add rows to object_ref.
Modified: relstorage/trunk/relstorage/adapters/postgresql.py
===================================================================
--- relstorage/trunk/relstorage/adapters/postgresql.py 2008-02-15 07:56:46 UTC (rev 83846)
+++ relstorage/trunk/relstorage/adapters/postgresql.py 2008-02-15 09:40:11 UTC (rev 83847)
@@ -16,7 +16,7 @@
from base64 import decodestring, encodestring
import logging
import psycopg2, psycopg2.extensions
-from ZODB.POSException import ConflictError, StorageError, UndoError
+from ZODB.POSException import StorageError
from common import Adapter
@@ -424,20 +424,14 @@
return cursor.fetchone()
def add_transaction(self, cursor, tid, username, description, extension):
- """Add a transaction.
-
- Raises ConflictError if the given tid has already been used.
+ """Add a transaction."""
+ stmt = """
+ INSERT INTO transaction
+ (tid, username, description, extension)
+ VALUES (%s, %s, %s, decode(%s, 'base64'))
"""
- try:
- stmt = """
- INSERT INTO transaction
- (tid, username, description, extension)
- VALUES (%s, %s, %s, decode(%s, 'base64'))
- """
- cursor.execute(stmt, (
- tid, username, description, encodestring(extension)))
- except psycopg2.IntegrityError, e:
- raise ConflictError(e)
+ cursor.execute(stmt, (
+ tid, username, description, encodestring(extension)))
def detect_conflict(self, cursor):
"""Find one conflict in the data about to be committed.
@@ -561,107 +555,6 @@
pass
- def verify_undoable(self, cursor, undo_tid):
- """Raise UndoError if it is not safe to undo the specified txn."""
- stmt = """
- SELECT 1 FROM transaction WHERE tid = %s AND packed = FALSE
- """
- cursor.execute(stmt, (undo_tid,))
- if not cursor.rowcount:
- raise UndoError("Transaction not found or packed")
-
- # Rule: we can undo an object if the object's state in the
- # transaction to undo matches the object's current state.
- # If any object in the transaction does not fit that rule,
- # refuse to undo.
- stmt = """
- SELECT prev_os.zoid, current_object.tid
- FROM object_state prev_os
- JOIN object_state cur_os ON (prev_os.zoid = cur_os.zoid)
- JOIN current_object ON (cur_os.zoid = current_object.zoid
- AND cur_os.tid = current_object.tid)
- WHERE prev_os.tid = %s
- AND cur_os.md5 != prev_os.md5
- LIMIT 1
- """
- cursor.execute(stmt, (undo_tid,))
- if cursor.rowcount:
- raise UndoError(
- "Some data were modified by a later transaction")
-
- # Rule: don't allow the creation of the root object to
- # be undone. It's hard to get it back.
- stmt = """
- SELECT 1
- FROM object_state
- WHERE tid = %s
- AND zoid = 0
- AND prev_tid = 0
- """
- cursor.execute(stmt, (undo_tid,))
- if cursor.rowcount:
- raise UndoError("Can't undo the creation of the root object")
-
-
- def undo(self, cursor, undo_tid, self_tid):
- """Undo a transaction.
-
- Parameters: "undo_tid", the integer tid of the transaction to undo,
- and "self_tid", the integer tid of the current transaction.
-
- Returns the list of OIDs undone.
- """
- # Update records produced by earlier undo operations
- # within this transaction. Change the state, but not
- # prev_tid, since prev_tid is still correct.
- # Table names: 'undoing' refers to the transaction being
- # undone and 'prev' refers to the object state identified
- # by undoing.prev_tid.
- stmt = """
- UPDATE object_state SET state = (
- SELECT prev.state
- FROM object_state undoing
- LEFT JOIN object_state prev
- ON (prev.zoid = undoing.zoid
- AND prev.tid = undoing.prev_tid)
- WHERE undoing.tid = %(undo_tid)s
- AND undoing.zoid = object_state.zoid
- ),
- md5 = (
- SELECT prev.md5
- FROM object_state undoing
- LEFT JOIN object_state prev
- ON (prev.zoid = undoing.zoid
- AND prev.tid = undoing.prev_tid)
- WHERE undoing.tid = %(undo_tid)s
- AND undoing.zoid = object_state.zoid
- )
- WHERE tid = %(self_tid)s
- AND zoid IN (
- SELECT zoid FROM object_state WHERE tid = %(undo_tid)s);
-
- -- Add new undo records.
-
- INSERT INTO object_state (zoid, tid, prev_tid, md5, state)
- SELECT undoing.zoid, %(self_tid)s, current_object.tid,
- prev.md5, prev.state
- FROM object_state undoing
- JOIN current_object USING (zoid)
- LEFT JOIN object_state prev
- ON (prev.zoid = undoing.zoid
- AND prev.tid = undoing.prev_tid)
- WHERE undoing.tid = %(undo_tid)s
- AND undoing.zoid NOT IN (
- SELECT zoid FROM object_state WHERE tid = %(self_tid)s);
-
- -- List the changed OIDs.
-
- SELECT zoid FROM object_state WHERE tid = %(undo_tid)s
- """
- cursor.execute(stmt, {'undo_tid': undo_tid, 'self_tid': self_tid})
- return [oid_int for (oid_int,) in cursor]
-
-
def poll_invalidations(self, conn, cursor, prev_polled_tid, ignore_tid):
"""Polls for new transactions.
More information about the Checkins
mailing list