| 109 | |
| 110 | |
| 111 | class SqliteStatus(_StatusBase): |
| 112 | SCHEMA_VERSION = 1 |
| 113 | |
| 114 | def __init__(self, path=':memory:'): |
| 115 | self._path = path |
| 116 | self._c = sqlite3.connect(path) |
| 117 | self._c.isolation_level = None # turn off idiocy of DB-API |
| 118 | self._c.row_factory = sqlite3.Row |
| 119 | self._update_schema() |
| 120 | |
| 121 | def _update_schema(self): |
| 122 | if self._is_latest_version(): |
| 123 | return |
| 124 | |
| 125 | # If we ever bump the schema version, we will need a way to migrate |
| 126 | # data. |
| 127 | with _exclusive_transaction(self._c) as c: |
| 128 | c.execute('CREATE TABLE meta ( "version" INTEGER PRIMARY KEY )') |
| 129 | c.execute('INSERT INTO meta (version) VALUES (?)', |
| 130 | (self.SCHEMA_VERSION,)) |
| 131 | |
| 132 | # I know that this is a bad schema, but right there is just too |
| 133 | # little gain in deduplicating the .._a and .._b columns. |
| 134 | c.execute('''CREATE TABLE status ( |
| 135 | "ident" TEXT PRIMARY KEY NOT NULL, |
| 136 | "href_a" TEXT, |
| 137 | "href_b" TEXT, |
| 138 | "hash_a" TEXT NOT NULL, |
| 139 | "hash_b" TEXT NOT NULL, |
| 140 | "etag_a" TEXT, |
| 141 | "etag_b" TEXT |
| 142 | ); ''') |
| 143 | c.execute('CREATE UNIQUE INDEX by_href_a ON status(href_a)') |
| 144 | c.execute('CREATE UNIQUE INDEX by_href_b ON status(href_b)') |
| 145 | |
| 146 | # We cannot add NOT NULL here because data is first fetched for the |
| 147 | # storage a, then storage b. Inbetween the `_b`-columns are filled |
| 148 | # with NULL. |
| 149 | # |
| 150 | # In an ideal world we would be able to start a transaction with |
| 151 | # one cursor, write our new data into status and simultaneously |
| 152 | # query the old status data using a different cursor. |
| 153 | # Unfortunately sqlite enforces NOT NULL constraints immediately, |
| 154 | # not just at commit. Since there is also no way to alter |
| 155 | # constraints on a table (disable constraints on start of |
| 156 | # transaction and reenable on end), it's a separate table now that |
| 157 | # just gets copied over before we commit. That's a lot of copying, |
| 158 | # sadly. |
| 159 | c.execute('''CREATE TABLE new_status ( |
| 160 | "ident" TEXT PRIMARY KEY NOT NULL, |
| 161 | "href_a" TEXT, |
| 162 | "href_b" TEXT, |
| 163 | "hash_a" TEXT, |
| 164 | "hash_b" TEXT, |
| 165 | "etag_a" TEXT, |
| 166 | "etag_b" TEXT |
| 167 | ); ''') |
| 168 |
no outgoing calls