Fossil SCM

A new, faster, implementation of the compute_fileage() utility that is implemented in SQL, using the files_of_checkin virtual tables and a common table expression.

drh 2014-12-15 17:53 UTC fileage-enhancement
Commit cc1e96550423b2b3efb5d6a0bbc3f2a74a2a1cd6
Parent a0cc614326fa40e…
1 file changed +39 -57
~ src/browse.c
M src/browse.c
+39 -57
--- src/browse.c
+++ src/browse.c
@@ -724,75 +724,58 @@
724724
 }else{
725725
 zClass = mprintf("file");
726726
 }
727727
 return zClass;
728728
 }
729
+
730
+/*
731
+** SQL used to compute the age of all files in checkin :ckin whose
732
+** names match :glob
733
+*/
734
+static const char zComputeFileAgeSetup[] =
735
+@ CREATE TABLE IF NOT EXISTS temp.fileage(
736
+@ fnid INTEGER PRIMARY KEY,
737
+@ fid INTEGER,
738
+@ mid INTEGER,
739
+@ mtime DATETIME,
740
+@ pathname TEXT
741
+@ );
742
+@ CREATE VIRTUAL TABLE IF NOT EXISTS temp.foci USING files_of_checkin;
743
+;
744
+
745
+static const char zComputeFileAgeRun[] =
746
+@ WITH RECURSIVE
747
+@ ckin(x) AS (VALUES(:ckin) UNION ALL
748
+@ SELECT pid FROM ckin, plink WHERE cid=x AND isprim)
749
+@ INSERT OR IGNORE INTO fileage(fnid, fid, mid, mtime, pathname)
750
+@ SELECT mlink.fnid, mlink.fid, x, event.mtime, filename.name
751
+@ FROM ckin, mlink, event, filename
752
+@ WHERE mlink.mid=ckin.x
753
+@ AND mlink.fnid IN (SELECT fnid FROM foci, filename
754
+@ WHERE foci.checkinID=:ckin
755
+@ AND filename.name=foci.filename
756
+@ AND filename.name GLOB :glob)
757
+@ AND filename.fnid=mlink.fnid
758
+@ AND event.objid=mlink.mid;
759
+;
760
+
729761
730762
 /*
731763
 ** Look at all file containing in the version "vid". Construct a
732764
 ** temporary table named "fileage" that contains the file-id for each
733765
 ** files, the pathname, the check-in where the file was added, and the
734766
 ** mtime on that checkin. If zGlob and *zGlob then only files matching
735767
 ** the given glob are computed.
736768
 */
737769
 int compute_fileage(int vid, const char* zGlob){
738
- int nFile = 0;
739
- double vmtime;
740
- Stmt q1, q2, q3;
741
- Stmt upd;
742
- if(zGlob && !*zGlob) zGlob = NULL;
743
- db_multi_exec(
744
- /*"DROP TABLE IF EXISTS temp.fileage;"*/
745
- "CREATE TEMP TABLE fileage(\n"
746
- " fid INTEGER,\n"
747
- " mid INTEGER,\n"
748
- " mtime DATETIME,\n"
749
- " pathname TEXT\n"
750
- ");\n"
751
- "CREATE INDEX fileage_fid ON fileage(fid);\n"
752
- "CREATE VIRTUAL TABLE temp.foci USING files_of_checkin;\n"
753
- "INSERT INTO temp.fileage(fid,pathname)"
754
- " SELECT blob.rid, foci.filename\n"
755
- " FROM foci, blob\n"
756
- " WHERE foci.checkinID=%d\n"
757
- " AND blob.uuid=foci.uuid\n"
758
- " AND foci.filename GLOB '%q';",
759
- vid, zGlob ? zGlob : "*"
760
- );
761
- nFile = db_int(0, "SELECT count(*) FROM fileage");
762
- db_prepare(&q1,"SELECT fid FROM mlink WHERE mid=:mid");
763
- db_prepare(&upd, "UPDATE fileage SET mid=:mid, mtime=:vmtime"
764
- " WHERE fid=:fid AND mid IS NULL");
765
- db_prepare(&q2,"SELECT pid FROM plink WHERE cid=:vid AND isprim");
766
- db_prepare(&q3,"SELECT mtime FROM event WHERE objid=:vid");
767
- while( nFile>0 && vid>0 ){
768
- db_bind_int(&q3, ":vid", vid);
769
- if( db_step(&q3)==SQLITE_ROW ){
770
- vmtime = db_column_double(&q3, 0);
771
- }else{
772
- break;
773
- }
774
- db_reset(&q3);
775
- db_bind_int(&q1, ":mid", vid);
776
- db_bind_int(&upd, ":mid", vid);
777
- db_bind_double(&upd, ":vmtime", vmtime);
778
- while( db_step(&q1)==SQLITE_ROW ){
779
- db_bind_int(&upd, ":fid", db_column_int(&q1, 0));
780
- db_step(&upd);
781
- nFile -= db_changes();
782
- db_reset(&upd);
783
- }
784
- db_reset(&q1);
785
- db_bind_int(&q2, ":vid", vid);
786
- if( db_step(&q2)!=SQLITE_ROW ) break;
787
- vid = db_column_int(&q2, 0);
788
- db_reset(&q2);
789
- }
790
- db_finalize(&q1);
791
- db_finalize(&upd);
792
- db_finalize(&q2);
793
- db_finalize(&q3);
770
+ Stmt q;
771
+ db_multi_exec(zComputeFileAgeSetup /*works-like:"constant"*/);
772
+ db_prepare(&q, zComputeFileAgeRun /*works-like:"constant"*/);
773
+ db_bind_int(&q, ":ckin", vid);
774
+ db_bind_text(&q, ":glob", zGlob && zGlob[0] ? zGlob : "*");
775
+ db_exec(&q);
776
+ db_finalize(&q);
794777
 return 0;
795778
 }
796779
797780
 /*
798781
 ** Render the number of days in rAge as a more human-readable time span.
@@ -813,11 +796,10 @@
813796
 return mprintf("%.1f days", rAge);
814797
 }else{
815798
 return mprintf("%.2f years", rAge/365.0);
816799
 }
817800
 }
818
-
819801
820802
 /*
821803
 ** COMMAND: test-fileage
822804
 **
823805
 ** Usage: %fossil test-fileage CHECKIN
824806
--- src/browse.c
+++ src/browse.c
@@ -724,75 +724,58 @@
724 }else{
725 zClass = mprintf("file");
726 }
727 return zClass;
728 }
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
729
730 /*
731 ** Look at all file containing in the version "vid". Construct a
732 ** temporary table named "fileage" that contains the file-id for each
733 ** files, the pathname, the check-in where the file was added, and the
734 ** mtime on that checkin. If zGlob and *zGlob then only files matching
735 ** the given glob are computed.
736 */
737 int compute_fileage(int vid, const char* zGlob){
738 int nFile = 0;
739 double vmtime;
740 Stmt q1, q2, q3;
741 Stmt upd;
742 if(zGlob && !*zGlob) zGlob = NULL;
743 db_multi_exec(
744 /*"DROP TABLE IF EXISTS temp.fileage;"*/
745 "CREATE TEMP TABLE fileage(\n"
746 " fid INTEGER,\n"
747 " mid INTEGER,\n"
748 " mtime DATETIME,\n"
749 " pathname TEXT\n"
750 ");\n"
751 "CREATE INDEX fileage_fid ON fileage(fid);\n"
752 "CREATE VIRTUAL TABLE temp.foci USING files_of_checkin;\n"
753 "INSERT INTO temp.fileage(fid,pathname)"
754 " SELECT blob.rid, foci.filename\n"
755 " FROM foci, blob\n"
756 " WHERE foci.checkinID=%d\n"
757 " AND blob.uuid=foci.uuid\n"
758 " AND foci.filename GLOB '%q';",
759 vid, zGlob ? zGlob : "*"
760 );
761 nFile = db_int(0, "SELECT count(*) FROM fileage");
762 db_prepare(&q1,"SELECT fid FROM mlink WHERE mid=:mid");
763 db_prepare(&upd, "UPDATE fileage SET mid=:mid, mtime=:vmtime"
764 " WHERE fid=:fid AND mid IS NULL");
765 db_prepare(&q2,"SELECT pid FROM plink WHERE cid=:vid AND isprim");
766 db_prepare(&q3,"SELECT mtime FROM event WHERE objid=:vid");
767 while( nFile>0 && vid>0 ){
768 db_bind_int(&q3, ":vid", vid);
769 if( db_step(&q3)==SQLITE_ROW ){
770 vmtime = db_column_double(&q3, 0);
771 }else{
772 break;
773 }
774 db_reset(&q3);
775 db_bind_int(&q1, ":mid", vid);
776 db_bind_int(&upd, ":mid", vid);
777 db_bind_double(&upd, ":vmtime", vmtime);
778 while( db_step(&q1)==SQLITE_ROW ){
779 db_bind_int(&upd, ":fid", db_column_int(&q1, 0));
780 db_step(&upd);
781 nFile -= db_changes();
782 db_reset(&upd);
783 }
784 db_reset(&q1);
785 db_bind_int(&q2, ":vid", vid);
786 if( db_step(&q2)!=SQLITE_ROW ) break;
787 vid = db_column_int(&q2, 0);
788 db_reset(&q2);
789 }
790 db_finalize(&q1);
791 db_finalize(&upd);
792 db_finalize(&q2);
793 db_finalize(&q3);
794 return 0;
795 }
796
797 /*
798 ** Render the number of days in rAge as a more human-readable time span.
@@ -813,11 +796,10 @@
813 return mprintf("%.1f days", rAge);
814 }else{
815 return mprintf("%.2f years", rAge/365.0);
816 }
817 }
818
819
820 /*
821 ** COMMAND: test-fileage
822 **
823 ** Usage: %fossil test-fileage CHECKIN
824
--- src/browse.c
+++ src/browse.c
@@ -724,75 +724,58 @@
724 }else{
725 zClass = mprintf("file");
726 }
727 return zClass;
728 }
729
730 /*
731 ** SQL used to compute the age of all files in checkin :ckin whose
732 ** names match :glob
733 */
734 static const char zComputeFileAgeSetup[] =
735 @ CREATE TABLE IF NOT EXISTS temp.fileage(
736 @ fnid INTEGER PRIMARY KEY,
737 @ fid INTEGER,
738 @ mid INTEGER,
739 @ mtime DATETIME,
740 @ pathname TEXT
741 @ );
742 @ CREATE VIRTUAL TABLE IF NOT EXISTS temp.foci USING files_of_checkin;
743 ;
744
745 static const char zComputeFileAgeRun[] =
746 @ WITH RECURSIVE
747 @ ckin(x) AS (VALUES(:ckin) UNION ALL
748 @ SELECT pid FROM ckin, plink WHERE cid=x AND isprim)
749 @ INSERT OR IGNORE INTO fileage(fnid, fid, mid, mtime, pathname)
750 @ SELECT mlink.fnid, mlink.fid, x, event.mtime, filename.name
751 @ FROM ckin, mlink, event, filename
752 @ WHERE mlink.mid=ckin.x
753 @ AND mlink.fnid IN (SELECT fnid FROM foci, filename
754 @ WHERE foci.checkinID=:ckin
755 @ AND filename.name=foci.filename
756 @ AND filename.name GLOB :glob)
757 @ AND filename.fnid=mlink.fnid
758 @ AND event.objid=mlink.mid;
759 ;
760
761
762 /*
763 ** Look at all file containing in the version "vid". Construct a
764 ** temporary table named "fileage" that contains the file-id for each
765 ** files, the pathname, the check-in where the file was added, and the
766 ** mtime on that checkin. If zGlob and *zGlob then only files matching
767 ** the given glob are computed.
768 */
769 int compute_fileage(int vid, const char* zGlob){
770 Stmt q;
771 db_multi_exec(zComputeFileAgeSetup /*works-like:"constant"*/);
772 db_prepare(&q, zComputeFileAgeRun /*works-like:"constant"*/);
773 db_bind_int(&q, ":ckin", vid);
774 db_bind_text(&q, ":glob", zGlob && zGlob[0] ? zGlob : "*");
775 db_exec(&q);
776 db_finalize(&q);
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
777 return 0;
778 }
779
780 /*
781 ** Render the number of days in rAge as a more human-readable time span.
@@ -813,11 +796,10 @@
796 return mprintf("%.1f days", rAge);
797 }else{
798 return mprintf("%.2f years", rAge/365.0);
799 }
800 }
 
801
802 /*
803 ** COMMAND: test-fileage
804 **
805 ** Usage: %fossil test-fileage CHECKIN
806

Keyboard Shortcuts

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