Fossil SCM

Cherry-pick [f2ebd7e52d16891bdbf2eb423891ad007e744f61|f2ebd7e52d]: Make use of a recursive query capability (if available) to replace the compute_ancestors() function with a single query.

jan.nijtmans 2014-04-10 15:16 UTC branch-1.28 merge
Commit 52d80260457755b53403ab78bce58b455815d48c
Parent c779b6890464cae…
2 files changed +51 -38 +51 -38
~ src/descendants.c ~ src/descendants.c
M src/descendants.c
+51 -38
--- src/descendants.c
+++ src/descendants.c
@@ -157,42 +157,61 @@
157157
 /*
158158
 ** Load the record ID rid and up to N-1 closest ancestors into
159159
 ** the "ok" table.
160160
 */
161161
 void compute_ancestors(int rid, int N, int directOnly){
162
- Bag seen;
163
- PQueue queue;
164
- Stmt ins;
165
- Stmt q;
166
- bag_init(&seen);
167
- pqueuex_init(&queue);
168
- bag_insert(&seen, rid);
169
- pqueuex_insert(&queue, rid, 0.0, 0);
170
- db_prepare(&ins, "INSERT OR IGNORE INTO ok VALUES(:rid)");
171
- db_prepare(&q,
172
- "SELECT a.pid, b.mtime FROM plink a LEFT JOIN plink b ON b.cid=a.pid"
173
- " WHERE a.cid=:rid %s",
174
- directOnly ? " AND a.isprim" : ""
175
- );
176
- while( (N--)>0 && (rid = pqueuex_extract(&queue, 0))!=0 ){
177
- db_bind_int(&ins, ":rid", rid);
178
- db_step(&ins);
179
- db_reset(&ins);
180
- db_bind_int(&q, ":rid", rid);
181
- while( db_step(&q)==SQLITE_ROW ){
182
- int pid = db_column_int(&q, 0);
183
- double mtime = db_column_double(&q, 1);
184
- if( bag_insert(&seen, pid) ){
185
- pqueuex_insert(&queue, pid, -mtime, 0);
186
- }
187
- }
188
- db_reset(&q);
189
- }
190
- bag_clear(&seen);
191
- pqueuex_clear(&queue);
192
- db_finalize(&ins);
193
- db_finalize(&q);
162
+#if USE_SYSTEM_SQLITE+0==1
163
+ if( sqlite3_libversion_number()<3008003 ){
164
+ Bag seen;
165
+ PQueue queue;
166
+ Stmt ins;
167
+ Stmt q;
168
+ bag_init(&seen);
169
+ pqueuex_init(&queue);
170
+ bag_insert(&seen, rid);
171
+ pqueuex_insert(&queue, rid, 0.0, 0);
172
+ db_prepare(&ins, "INSERT OR IGNORE INTO ok VALUES(:rid)");
173
+ db_prepare(&q,
174
+ "SELECT a.pid, b.mtime FROM plink a LEFT JOIN plink b ON b.cid=a.pid"
175
+ " WHERE a.cid=:rid %s",
176
+ directOnly ? " AND a.isprim" : ""
177
+ );
178
+ while( (N--)>0 && (rid = pqueuex_extract(&queue, 0))!=0 ){
179
+ db_bind_int(&ins, ":rid", rid);
180
+ db_step(&ins);
181
+ db_reset(&ins);
182
+ db_bind_int(&q, ":rid", rid);
183
+ while( db_step(&q)==SQLITE_ROW ){
184
+ int pid = db_column_int(&q, 0);
185
+ double mtime = db_column_double(&q, 1);
186
+ if( bag_insert(&seen, pid) ){
187
+ pqueuex_insert(&queue, pid, -mtime, 0);
188
+ }
189
+ }
190
+ db_reset(&q);
191
+ }
192
+ bag_clear(&seen);
193
+ pqueuex_clear(&queue);
194
+ db_finalize(&ins);
195
+ db_finalize(&q);
196
+ } else
197
+#endif
198
+ db_multi_exec(
199
+ "WITH RECURSIVE "
200
+ " ancestor(rid, mtime) AS ("
201
+ " SELECT %d, mtime FROM event WHERE objid=%d "
202
+ " UNION "
203
+ " SELECT plink.pid, event.mtime"
204
+ " FROM ancestor, plink, event"
205
+ " WHERE plink.cid=ancestor.rid"
206
+ " AND event.objid=plink.pid %s"
207
+ " ORDER BY mtime DESC LIMIT %d"
208
+ " )"
209
+ "INSERT INTO ok"
210
+ " SELECT rid FROM ancestor;",
211
+ rid, rid, directOnly ? "AND plink.isPrim" : "", N
212
+ );
194213
 }
195214
196215
 /*
197216
 ** Compute up to N direct ancestors (merge ancestors do not count)
198217
 ** for the check-in rid and put them in a table named "ancestor".
@@ -464,16 +483,10 @@
464483
 db_prepare(&q, "%s ORDER BY event.mtime DESC", blob_str(&sql));
465484
 blob_reset(&sql);
466485
 www_print_timeline(&q, TIMELINE_LEAFONLY, 0, 0, 0);
467486
 db_finalize(&q);
468487
 @ <br />
469
- @ <script type="text/JavaScript">
470
- @ function xin(id){
471
- @ }
472
- @ function xout(id){
473
- @ }
474
- @ </script>
475488
 style_footer();
476489
 }
477490
478491
 #if INTERFACE
479492
 /* Flag parameters to compute_uses_file() */
480493
--- src/descendants.c
+++ src/descendants.c
@@ -157,42 +157,61 @@
157 /*
158 ** Load the record ID rid and up to N-1 closest ancestors into
159 ** the "ok" table.
160 */
161 void compute_ancestors(int rid, int N, int directOnly){
162 Bag seen;
163 PQueue queue;
164 Stmt ins;
165 Stmt q;
166 bag_init(&seen);
167 pqueuex_init(&queue);
168 bag_insert(&seen, rid);
169 pqueuex_insert(&queue, rid, 0.0, 0);
170 db_prepare(&ins, "INSERT OR IGNORE INTO ok VALUES(:rid)");
171 db_prepare(&q,
172 "SELECT a.pid, b.mtime FROM plink a LEFT JOIN plink b ON b.cid=a.pid"
173 " WHERE a.cid=:rid %s",
174 directOnly ? " AND a.isprim" : ""
175 );
176 while( (N--)>0 && (rid = pqueuex_extract(&queue, 0))!=0 ){
177 db_bind_int(&ins, ":rid", rid);
178 db_step(&ins);
179 db_reset(&ins);
180 db_bind_int(&q, ":rid", rid);
181 while( db_step(&q)==SQLITE_ROW ){
182 int pid = db_column_int(&q, 0);
183 double mtime = db_column_double(&q, 1);
184 if( bag_insert(&seen, pid) ){
185 pqueuex_insert(&queue, pid, -mtime, 0);
186 }
187 }
188 db_reset(&q);
189 }
190 bag_clear(&seen);
191 pqueuex_clear(&queue);
192 db_finalize(&ins);
193 db_finalize(&q);
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
194 }
195
196 /*
197 ** Compute up to N direct ancestors (merge ancestors do not count)
198 ** for the check-in rid and put them in a table named "ancestor".
@@ -464,16 +483,10 @@
464 db_prepare(&q, "%s ORDER BY event.mtime DESC", blob_str(&sql));
465 blob_reset(&sql);
466 www_print_timeline(&q, TIMELINE_LEAFONLY, 0, 0, 0);
467 db_finalize(&q);
468 @ <br />
469 @ <script type="text/JavaScript">
470 @ function xin(id){
471 @ }
472 @ function xout(id){
473 @ }
474 @ </script>
475 style_footer();
476 }
477
478 #if INTERFACE
479 /* Flag parameters to compute_uses_file() */
480
--- src/descendants.c
+++ src/descendants.c
@@ -157,42 +157,61 @@
157 /*
158 ** Load the record ID rid and up to N-1 closest ancestors into
159 ** the "ok" table.
160 */
161 void compute_ancestors(int rid, int N, int directOnly){
162 #if USE_SYSTEM_SQLITE+0==1
163 if( sqlite3_libversion_number()<3008003 ){
164 Bag seen;
165 PQueue queue;
166 Stmt ins;
167 Stmt q;
168 bag_init(&seen);
169 pqueuex_init(&queue);
170 bag_insert(&seen, rid);
171 pqueuex_insert(&queue, rid, 0.0, 0);
172 db_prepare(&ins, "INSERT OR IGNORE INTO ok VALUES(:rid)");
173 db_prepare(&q,
174 "SELECT a.pid, b.mtime FROM plink a LEFT JOIN plink b ON b.cid=a.pid"
175 " WHERE a.cid=:rid %s",
176 directOnly ? " AND a.isprim" : ""
177 );
178 while( (N--)>0 && (rid = pqueuex_extract(&queue, 0))!=0 ){
179 db_bind_int(&ins, ":rid", rid);
180 db_step(&ins);
181 db_reset(&ins);
182 db_bind_int(&q, ":rid", rid);
183 while( db_step(&q)==SQLITE_ROW ){
184 int pid = db_column_int(&q, 0);
185 double mtime = db_column_double(&q, 1);
186 if( bag_insert(&seen, pid) ){
187 pqueuex_insert(&queue, pid, -mtime, 0);
188 }
189 }
190 db_reset(&q);
191 }
192 bag_clear(&seen);
193 pqueuex_clear(&queue);
194 db_finalize(&ins);
195 db_finalize(&q);
196 } else
197 #endif
198 db_multi_exec(
199 "WITH RECURSIVE "
200 " ancestor(rid, mtime) AS ("
201 " SELECT %d, mtime FROM event WHERE objid=%d "
202 " UNION "
203 " SELECT plink.pid, event.mtime"
204 " FROM ancestor, plink, event"
205 " WHERE plink.cid=ancestor.rid"
206 " AND event.objid=plink.pid %s"
207 " ORDER BY mtime DESC LIMIT %d"
208 " )"
209 "INSERT INTO ok"
210 " SELECT rid FROM ancestor;",
211 rid, rid, directOnly ? "AND plink.isPrim" : "", N
212 );
213 }
214
215 /*
216 ** Compute up to N direct ancestors (merge ancestors do not count)
217 ** for the check-in rid and put them in a table named "ancestor".
@@ -464,16 +483,10 @@
483 db_prepare(&q, "%s ORDER BY event.mtime DESC", blob_str(&sql));
484 blob_reset(&sql);
485 www_print_timeline(&q, TIMELINE_LEAFONLY, 0, 0, 0);
486 db_finalize(&q);
487 @ <br />
 
 
 
 
 
 
488 style_footer();
489 }
490
491 #if INTERFACE
492 /* Flag parameters to compute_uses_file() */
493
M src/descendants.c
+51 -38
--- src/descendants.c
+++ src/descendants.c
@@ -157,42 +157,61 @@
157157
 /*
158158
 ** Load the record ID rid and up to N-1 closest ancestors into
159159
 ** the "ok" table.
160160
 */
161161
 void compute_ancestors(int rid, int N, int directOnly){
162
- Bag seen;
163
- PQueue queue;
164
- Stmt ins;
165
- Stmt q;
166
- bag_init(&seen);
167
- pqueuex_init(&queue);
168
- bag_insert(&seen, rid);
169
- pqueuex_insert(&queue, rid, 0.0, 0);
170
- db_prepare(&ins, "INSERT OR IGNORE INTO ok VALUES(:rid)");
171
- db_prepare(&q,
172
- "SELECT a.pid, b.mtime FROM plink a LEFT JOIN plink b ON b.cid=a.pid"
173
- " WHERE a.cid=:rid %s",
174
- directOnly ? " AND a.isprim" : ""
175
- );
176
- while( (N--)>0 && (rid = pqueuex_extract(&queue, 0))!=0 ){
177
- db_bind_int(&ins, ":rid", rid);
178
- db_step(&ins);
179
- db_reset(&ins);
180
- db_bind_int(&q, ":rid", rid);
181
- while( db_step(&q)==SQLITE_ROW ){
182
- int pid = db_column_int(&q, 0);
183
- double mtime = db_column_double(&q, 1);
184
- if( bag_insert(&seen, pid) ){
185
- pqueuex_insert(&queue, pid, -mtime, 0);
186
- }
187
- }
188
- db_reset(&q);
189
- }
190
- bag_clear(&seen);
191
- pqueuex_clear(&queue);
192
- db_finalize(&ins);
193
- db_finalize(&q);
162
+#if USE_SYSTEM_SQLITE+0==1
163
+ if( sqlite3_libversion_number()<3008003 ){
164
+ Bag seen;
165
+ PQueue queue;
166
+ Stmt ins;
167
+ Stmt q;
168
+ bag_init(&seen);
169
+ pqueuex_init(&queue);
170
+ bag_insert(&seen, rid);
171
+ pqueuex_insert(&queue, rid, 0.0, 0);
172
+ db_prepare(&ins, "INSERT OR IGNORE INTO ok VALUES(:rid)");
173
+ db_prepare(&q,
174
+ "SELECT a.pid, b.mtime FROM plink a LEFT JOIN plink b ON b.cid=a.pid"
175
+ " WHERE a.cid=:rid %s",
176
+ directOnly ? " AND a.isprim" : ""
177
+ );
178
+ while( (N--)>0 && (rid = pqueuex_extract(&queue, 0))!=0 ){
179
+ db_bind_int(&ins, ":rid", rid);
180
+ db_step(&ins);
181
+ db_reset(&ins);
182
+ db_bind_int(&q, ":rid", rid);
183
+ while( db_step(&q)==SQLITE_ROW ){
184
+ int pid = db_column_int(&q, 0);
185
+ double mtime = db_column_double(&q, 1);
186
+ if( bag_insert(&seen, pid) ){
187
+ pqueuex_insert(&queue, pid, -mtime, 0);
188
+ }
189
+ }
190
+ db_reset(&q);
191
+ }
192
+ bag_clear(&seen);
193
+ pqueuex_clear(&queue);
194
+ db_finalize(&ins);
195
+ db_finalize(&q);
196
+ } else
197
+#endif
198
+ db_multi_exec(
199
+ "WITH RECURSIVE "
200
+ " ancestor(rid, mtime) AS ("
201
+ " SELECT %d, mtime FROM event WHERE objid=%d "
202
+ " UNION "
203
+ " SELECT plink.pid, event.mtime"
204
+ " FROM ancestor, plink, event"
205
+ " WHERE plink.cid=ancestor.rid"
206
+ " AND event.objid=plink.pid %s"
207
+ " ORDER BY mtime DESC LIMIT %d"
208
+ " )"
209
+ "INSERT INTO ok"
210
+ " SELECT rid FROM ancestor;",
211
+ rid, rid, directOnly ? "AND plink.isPrim" : "", N
212
+ );
194213
 }
195214
196215
 /*
197216
 ** Compute up to N direct ancestors (merge ancestors do not count)
198217
 ** for the check-in rid and put them in a table named "ancestor".
@@ -464,16 +483,10 @@
464483
 db_prepare(&q, "%s ORDER BY event.mtime DESC", blob_str(&sql));
465484
 blob_reset(&sql);
466485
 www_print_timeline(&q, TIMELINE_LEAFONLY, 0, 0, 0);
467486
 db_finalize(&q);
468487
 @ <br />
469
- @ <script type="text/JavaScript">
470
- @ function xin(id){
471
- @ }
472
- @ function xout(id){
473
- @ }
474
- @ </script>
475488
 style_footer();
476489
 }
477490
478491
 #if INTERFACE
479492
 /* Flag parameters to compute_uses_file() */
480493
--- src/descendants.c
+++ src/descendants.c
@@ -157,42 +157,61 @@
157 /*
158 ** Load the record ID rid and up to N-1 closest ancestors into
159 ** the "ok" table.
160 */
161 void compute_ancestors(int rid, int N, int directOnly){
162 Bag seen;
163 PQueue queue;
164 Stmt ins;
165 Stmt q;
166 bag_init(&seen);
167 pqueuex_init(&queue);
168 bag_insert(&seen, rid);
169 pqueuex_insert(&queue, rid, 0.0, 0);
170 db_prepare(&ins, "INSERT OR IGNORE INTO ok VALUES(:rid)");
171 db_prepare(&q,
172 "SELECT a.pid, b.mtime FROM plink a LEFT JOIN plink b ON b.cid=a.pid"
173 " WHERE a.cid=:rid %s",
174 directOnly ? " AND a.isprim" : ""
175 );
176 while( (N--)>0 && (rid = pqueuex_extract(&queue, 0))!=0 ){
177 db_bind_int(&ins, ":rid", rid);
178 db_step(&ins);
179 db_reset(&ins);
180 db_bind_int(&q, ":rid", rid);
181 while( db_step(&q)==SQLITE_ROW ){
182 int pid = db_column_int(&q, 0);
183 double mtime = db_column_double(&q, 1);
184 if( bag_insert(&seen, pid) ){
185 pqueuex_insert(&queue, pid, -mtime, 0);
186 }
187 }
188 db_reset(&q);
189 }
190 bag_clear(&seen);
191 pqueuex_clear(&queue);
192 db_finalize(&ins);
193 db_finalize(&q);
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
194 }
195
196 /*
197 ** Compute up to N direct ancestors (merge ancestors do not count)
198 ** for the check-in rid and put them in a table named "ancestor".
@@ -464,16 +483,10 @@
464 db_prepare(&q, "%s ORDER BY event.mtime DESC", blob_str(&sql));
465 blob_reset(&sql);
466 www_print_timeline(&q, TIMELINE_LEAFONLY, 0, 0, 0);
467 db_finalize(&q);
468 @ <br />
469 @ <script type="text/JavaScript">
470 @ function xin(id){
471 @ }
472 @ function xout(id){
473 @ }
474 @ </script>
475 style_footer();
476 }
477
478 #if INTERFACE
479 /* Flag parameters to compute_uses_file() */
480
--- src/descendants.c
+++ src/descendants.c
@@ -157,42 +157,61 @@
157 /*
158 ** Load the record ID rid and up to N-1 closest ancestors into
159 ** the "ok" table.
160 */
161 void compute_ancestors(int rid, int N, int directOnly){
162 #if USE_SYSTEM_SQLITE+0==1
163 if( sqlite3_libversion_number()<3008003 ){
164 Bag seen;
165 PQueue queue;
166 Stmt ins;
167 Stmt q;
168 bag_init(&seen);
169 pqueuex_init(&queue);
170 bag_insert(&seen, rid);
171 pqueuex_insert(&queue, rid, 0.0, 0);
172 db_prepare(&ins, "INSERT OR IGNORE INTO ok VALUES(:rid)");
173 db_prepare(&q,
174 "SELECT a.pid, b.mtime FROM plink a LEFT JOIN plink b ON b.cid=a.pid"
175 " WHERE a.cid=:rid %s",
176 directOnly ? " AND a.isprim" : ""
177 );
178 while( (N--)>0 && (rid = pqueuex_extract(&queue, 0))!=0 ){
179 db_bind_int(&ins, ":rid", rid);
180 db_step(&ins);
181 db_reset(&ins);
182 db_bind_int(&q, ":rid", rid);
183 while( db_step(&q)==SQLITE_ROW ){
184 int pid = db_column_int(&q, 0);
185 double mtime = db_column_double(&q, 1);
186 if( bag_insert(&seen, pid) ){
187 pqueuex_insert(&queue, pid, -mtime, 0);
188 }
189 }
190 db_reset(&q);
191 }
192 bag_clear(&seen);
193 pqueuex_clear(&queue);
194 db_finalize(&ins);
195 db_finalize(&q);
196 } else
197 #endif
198 db_multi_exec(
199 "WITH RECURSIVE "
200 " ancestor(rid, mtime) AS ("
201 " SELECT %d, mtime FROM event WHERE objid=%d "
202 " UNION "
203 " SELECT plink.pid, event.mtime"
204 " FROM ancestor, plink, event"
205 " WHERE plink.cid=ancestor.rid"
206 " AND event.objid=plink.pid %s"
207 " ORDER BY mtime DESC LIMIT %d"
208 " )"
209 "INSERT INTO ok"
210 " SELECT rid FROM ancestor;",
211 rid, rid, directOnly ? "AND plink.isPrim" : "", N
212 );
213 }
214
215 /*
216 ** Compute up to N direct ancestors (merge ancestors do not count)
217 ** for the check-in rid and put them in a table named "ancestor".
@@ -464,16 +483,10 @@
483 db_prepare(&q, "%s ORDER BY event.mtime DESC", blob_str(&sql));
484 blob_reset(&sql);
485 www_print_timeline(&q, TIMELINE_LEAFONLY, 0, 0, 0);
486 db_finalize(&q);
487 @ <br />
 
 
 
 
 
 
488 style_footer();
489 }
490
491 #if INTERFACE
492 /* Flag parameters to compute_uses_file() */
493

Keyboard Shortcuts

Open search /
Next entry (timeline) j
Previous entry (timeline) k
Open focused entry Enter
Show this help ?
Toggle theme Top nav button