118 ############################# |
118 ############################# |
119 ### simple sqlite caching ### |
119 ### simple sqlite caching ### |
120 ############################# |
120 ############################# |
121 |
121 |
122 _sqliteschema = [ |
122 _sqliteschema = [ |
123 """CREATE TABLE range(rev INTEGER NOT NULL, |
123 r"""CREATE TABLE range(rev INTEGER NOT NULL, |
124 idx INTEGER NOT NULL, |
124 idx INTEGER NOT NULL, |
125 PRIMARY KEY(rev, idx));""", |
125 PRIMARY KEY(rev, idx));""", |
126 """CREATE TABLE subranges(listidx INTEGER NOT NULL, |
126 r"""CREATE TABLE subranges(listidx INTEGER NOT NULL, |
127 suprev INTEGER NOT NULL, |
127 suprev INTEGER NOT NULL, |
128 supidx INTEGER NOT NULL, |
128 supidx INTEGER NOT NULL, |
129 subrev INTEGER NOT NULL, |
129 subrev INTEGER NOT NULL, |
130 subidx INTEGER NOT NULL, |
130 subidx INTEGER NOT NULL, |
131 PRIMARY KEY(listidx, suprev, supidx), |
131 PRIMARY KEY(listidx, suprev, supidx), |
132 FOREIGN KEY (suprev, supidx) REFERENCES range(rev, idx), |
132 FOREIGN KEY (suprev, supidx) REFERENCES range(rev, idx), |
133 FOREIGN KEY (subrev, subidx) REFERENCES range(rev, idx) |
133 FOREIGN KEY (subrev, subidx) REFERENCES range(rev, idx) |
134 );""", |
134 );""", |
135 "CREATE INDEX subranges_index ON subranges (suprev, supidx);", |
135 r"CREATE INDEX subranges_index ON subranges (suprev, supidx);", |
136 "CREATE INDEX superranges_index ON subranges (subrev, subidx);", |
136 r"CREATE INDEX superranges_index ON subranges (subrev, subidx);", |
137 "CREATE INDEX range_index ON range (rev, idx);", |
137 r"CREATE INDEX range_index ON range (rev, idx);", |
138 """CREATE TABLE meta(schemaversion INTEGER NOT NULL, |
138 r"""CREATE TABLE meta(schemaversion INTEGER NOT NULL, |
139 tiprev INTEGER NOT NULL, |
139 tiprev INTEGER NOT NULL, |
140 tipnode BLOB NOT NULL |
140 tipnode BLOB NOT NULL |
141 );""", |
141 );""", |
142 ] |
142 ] |
143 _newmeta = "INSERT INTO meta (schemaversion, tiprev, tipnode) VALUES (?,?,?);" |
143 _newmeta = r"INSERT INTO meta (schemaversion, tiprev, tipnode) VALUES (?,?,?);" |
144 _updatemeta = "UPDATE meta SET tiprev = ?, tipnode = ?;" |
144 _updatemeta = r"UPDATE meta SET tiprev = ?, tipnode = ?;" |
145 _updaterange = "INSERT INTO range(rev, idx) VALUES (?,?);" |
145 _updaterange = r"INSERT INTO range(rev, idx) VALUES (?,?);" |
146 _updatesubranges = """INSERT |
146 _updatesubranges = r"""INSERT |
147 INTO subranges(listidx, suprev, supidx, subrev, subidx) |
147 INTO subranges(listidx, suprev, supidx, subrev, subidx) |
148 VALUES (?,?,?,?,?);""" |
148 VALUES (?,?,?,?,?);""" |
149 _queryexist = "SELECT name FROM sqlite_master WHERE type='table' AND name='meta';" |
149 _queryexist = r"SELECT name FROM sqlite_master WHERE type='table' AND name='meta';" |
150 _querymeta = "SELECT schemaversion, tiprev, tipnode FROM meta;" |
150 _querymeta = r"SELECT schemaversion, tiprev, tipnode FROM meta;" |
151 _queryrange = "SELECT * FROM range WHERE (rev = ? AND idx = ?);" |
151 _queryrange = r"SELECT * FROM range WHERE (rev = ? AND idx = ?);" |
152 _querysubranges = """SELECT subrev, subidx |
152 _querysubranges = r"""SELECT subrev, subidx |
153 FROM subranges |
153 FROM subranges |
154 WHERE (suprev = ? AND supidx = ?) |
154 WHERE (suprev = ? AND supidx = ?) |
155 ORDER BY listidx;""" |
155 ORDER BY listidx;""" |
156 |
156 |
157 _querysuperrangesmain = """SELECT DISTINCT suprev, supidx |
157 _querysuperrangesmain = r"""SELECT DISTINCT suprev, supidx |
158 FROM subranges |
158 FROM subranges |
159 WHERE %s;""" |
159 WHERE %s;""" |
160 |
160 |
161 _querysuperrangesbody = '(subrev = %d and subidx = %d)' |
161 _querysuperrangesbody = r'(subrev = %d and subidx = %d)' |
162 |
162 |
163 def _make_querysuperranges(ranges): |
163 def _make_querysuperranges(ranges): |
164 # building a tree of OR would allow for more ranges |
164 # building a tree of OR would allow for more ranges |
165 body = ' OR '.join(_querysuperrangesbody % r for r in ranges) |
165 body = r' OR '.join(_querysuperrangesbody % r for r in ranges) |
166 return _querysuperrangesmain % body |
166 return _querysuperrangesmain % body |
167 |
167 |
168 class stablerangesqlbase(stablerange.stablerangecached): |
168 class stablerangesqlbase(stablerange.stablerangecached): |
169 """class that can handle all the bits needed to store range into sql |
169 """class that can handle all the bits needed to store range into sql |
170 """ |
170 """ |