Fossil SCM

Initial draft of type event type argument for /reports. Seems to work but is missing labels and link integration (i.e. can only be used by explicitly passing y=TYPE for the time being).

stephan 2013-10-02 21:28 UTC trunk
Commit 331a8b7112acbe7054de6519490d67f8f60cd57f
Parent e3a2d8f381a1fd5…
1 file changed +72 -4
~ src/timeline.c
M src/timeline.c
+72 -4
--- src/timeline.c
+++ src/timeline.c
@@ -1850,10 +1850,75 @@
18501850
 @ <a href="%s(g.zTop)/timeline?p=%S(zUuid)&amp;d=%S(zUuid)">%S(zUuid)</a>
18511851
 }
18521852
 db_finalize(&q);
18531853
 style_footer();
18541854
 }
1855
+
1856
+/*
1857
+** Creates a TEMP VIEW named v_reports which is a wrapper around the
1858
+** EVENT table filtered on event.type. It looks for the request
1859
+** parameter 'y' (for consistency with /timeline) and expects it to
1860
+** contain one of the conventional values from event.type or the value
1861
+** "all", which is treated as equivalent to "*". By default (if no
1862
+** 'y' is specified), "*" is assumed (that is also the default for
1863
+** invalid/unknown filter values). That 'y' filter is the one used for
1864
+** the event list. Note that a filter of "*" or "all" is equivalent to
1865
+** querying against the full event table. The view, however, adds an
1866
+** abstraction level to simplify the implementation code for the
1867
+** various /reports pages.
1868
+**
1869
+** Returns one of: 'c', 'w', 'g', 't', 'e', representing the type of
1870
+** filter it applies, or 0 (NUL) if no filter is applied (i.e. if
1871
+** "all" is used).
1872
+*/
1873
+static char stats_report_init_view(){
1874
+ char const * zType = PD("y","*"); /* analog to /timeline?y=... */
1875
+ char const * zRealType = NULL; /* normalized form of zType */
1876
+ char rc = 0; /* result code */
1877
+ switch( (zType && *zType) ? *zType : '*' ){
1878
+ case 'c':
1879
+ case 'C':
1880
+ zRealType = "ci";
1881
+ rc = *zRealType;
1882
+ break;
1883
+ case 'e':
1884
+ case 'E':
1885
+ zRealType = "e";
1886
+ rc = *zRealType;
1887
+ break;
1888
+ case 'g':
1889
+ case 'G':
1890
+ zRealType = "g";
1891
+ rc = *zRealType;
1892
+ break;
1893
+ case 't':
1894
+ case 'T':
1895
+ zRealType = "t";
1896
+ rc = *zRealType;
1897
+ break;
1898
+ case 'w':
1899
+ case 'W':
1900
+ zRealType = "w";
1901
+ rc = *zRealType;
1902
+ break;
1903
+ default:
1904
+ rc = '*';
1905
+ break;
1906
+ }
1907
+ assert(0 != rc);
1908
+ if(zRealType){
1909
+ assert(*zRealType);
1910
+ db_multi_exec("CREATE TEMP VIEW v_reports AS "
1911
+ "SELECT * FROM event WHERE type GLOB %Q",
1912
+ zRealType);
1913
+ }else{
1914
+ db_multi_exec("CREATE TEMP VIEW v_reports AS "
1915
+ "SELECT * FROM event");
1916
+ }
1917
+ return rc;
1918
+}
1919
+
18551920
18561921
 /*
18571922
 ** Helper for stats_report_by_month_year(), which generates a list of
18581923
 ** week numbers. zTimeframe should be either a timeframe in the form YYYY
18591924
 ** or YYYY-MM.
@@ -1864,11 +1929,11 @@
18641929
 memcpy(yearPart, zTimeframe, 4);
18651930
 db_prepare(&stWeek,
18661931
 "SELECT DISTINCT strftime('%%W',mtime) AS wk, "
18671932
 "count(*) AS n, "
18681933
 "substr(date(mtime),1,%d) AS ym "
1869
- "FROM event "
1934
+ "FROM v_reports "
18701935
 "WHERE ym=%Q AND mtime < current_timestamp "
18711936
 "GROUP BY wk ORDER BY wk",
18721937
 strlen(zTimeframe),
18731938
 zTimeframe);
18741939
 while( SQLITE_ROW == db_step(&stWeek) ){
@@ -1908,16 +1973,17 @@
19081973
 Blob header = empty_blob; /* Page header text */
19091974
 int nMaxEvents = 1; /* for calculating length of graph
19101975
 bars. */
19111976
 int iterations = 0; /* number of weeks/months we iterate
19121977
 over */
1978
+ stats_report_init_view();
19131979
 blob_appendf(&header, "Timeline Events by year%s",
19141980
 (includeMonth ? "/month" : ""));
19151981
 blob_appendf(&sql,
19161982
 "SELECT substr(date(mtime),1,%d) AS timeframe, "
19171983
 "count(*) AS eventCount "
1918
- "FROM event ",
1984
+ "FROM v_reports ",
19191985
 includeMonth ? 7 : 4);
19201986
 if(zUserName&&*zUserName){
19211987
 blob_appendf(&sql, " WHERE user=%Q ", zUserName);
19221988
 blob_appendf(&header," for user %q", zUserName);
19231989
 }
@@ -2053,14 +2119,15 @@
20532119
 int rowClass = 0; /* counter for alternating
20542120
 row colors */
20552121
 Blob sql = empty_blob; /* SQL */
20562122
 int nMaxEvents = 1; /* max number of events for
20572123
 all rows. */
2124
+ stats_report_init_view();
20582125
 blob_append(&sql,
20592126
 "SELECT user, "
20602127
 "COUNT(*) AS eventCount "
2061
- "FROM event "
2128
+ "FROM v_reports "
20622129
 "GROUP BY user ORDER BY eventCount DESC",
20632130
 -1);
20642131
 db_prepare(&query, blob_str(&sql));
20652132
 blob_reset(&sql);
20662133
 @ <h1>Timeline Events by User</h1>
@@ -2121,14 +2188,15 @@
21212188
 Blob sql = empty_blob;
21222189
 int nMaxEvents = 1; /* max number of events for
21232190
 all rows. */
21242191
 int iterations = 0; /* # of active time periods. */
21252192
2193
+ stats_report_init_view();
21262194
 cgi_printf("Select year: ");
21272195
 blob_append(&sql,
21282196
 "SELECT DISTINCT substr(date(mtime),1,4) AS y "
2129
- "FROM event WHERE 1 ", -1);
2197
+ "FROM v_reports WHERE 1 ", -1);
21302198
 if(zUserName&&*zUserName){
21312199
 blob_appendf(&sql,"AND user=%Q ", zUserName);
21322200
 }
21332201
 blob_append(&sql,"GROUP BY y ORDER BY y", -1);
21342202
 db_prepare(&qYears, blob_str(&sql));
21352203
--- src/timeline.c
+++ src/timeline.c
@@ -1850,10 +1850,75 @@
1850 @ <a href="%s(g.zTop)/timeline?p=%S(zUuid)&amp;d=%S(zUuid)">%S(zUuid)</a>
1851 }
1852 db_finalize(&q);
1853 style_footer();
1854 }
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
1855
1856 /*
1857 ** Helper for stats_report_by_month_year(), which generates a list of
1858 ** week numbers. zTimeframe should be either a timeframe in the form YYYY
1859 ** or YYYY-MM.
@@ -1864,11 +1929,11 @@
1864 memcpy(yearPart, zTimeframe, 4);
1865 db_prepare(&stWeek,
1866 "SELECT DISTINCT strftime('%%W',mtime) AS wk, "
1867 "count(*) AS n, "
1868 "substr(date(mtime),1,%d) AS ym "
1869 "FROM event "
1870 "WHERE ym=%Q AND mtime < current_timestamp "
1871 "GROUP BY wk ORDER BY wk",
1872 strlen(zTimeframe),
1873 zTimeframe);
1874 while( SQLITE_ROW == db_step(&stWeek) ){
@@ -1908,16 +1973,17 @@
1908 Blob header = empty_blob; /* Page header text */
1909 int nMaxEvents = 1; /* for calculating length of graph
1910 bars. */
1911 int iterations = 0; /* number of weeks/months we iterate
1912 over */
 
1913 blob_appendf(&header, "Timeline Events by year%s",
1914 (includeMonth ? "/month" : ""));
1915 blob_appendf(&sql,
1916 "SELECT substr(date(mtime),1,%d) AS timeframe, "
1917 "count(*) AS eventCount "
1918 "FROM event ",
1919 includeMonth ? 7 : 4);
1920 if(zUserName&&*zUserName){
1921 blob_appendf(&sql, " WHERE user=%Q ", zUserName);
1922 blob_appendf(&header," for user %q", zUserName);
1923 }
@@ -2053,14 +2119,15 @@
2053 int rowClass = 0; /* counter for alternating
2054 row colors */
2055 Blob sql = empty_blob; /* SQL */
2056 int nMaxEvents = 1; /* max number of events for
2057 all rows. */
 
2058 blob_append(&sql,
2059 "SELECT user, "
2060 "COUNT(*) AS eventCount "
2061 "FROM event "
2062 "GROUP BY user ORDER BY eventCount DESC",
2063 -1);
2064 db_prepare(&query, blob_str(&sql));
2065 blob_reset(&sql);
2066 @ <h1>Timeline Events by User</h1>
@@ -2121,14 +2188,15 @@
2121 Blob sql = empty_blob;
2122 int nMaxEvents = 1; /* max number of events for
2123 all rows. */
2124 int iterations = 0; /* # of active time periods. */
2125
 
2126 cgi_printf("Select year: ");
2127 blob_append(&sql,
2128 "SELECT DISTINCT substr(date(mtime),1,4) AS y "
2129 "FROM event WHERE 1 ", -1);
2130 if(zUserName&&*zUserName){
2131 blob_appendf(&sql,"AND user=%Q ", zUserName);
2132 }
2133 blob_append(&sql,"GROUP BY y ORDER BY y", -1);
2134 db_prepare(&qYears, blob_str(&sql));
2135
--- src/timeline.c
+++ src/timeline.c
@@ -1850,10 +1850,75 @@
1850 @ <a href="%s(g.zTop)/timeline?p=%S(zUuid)&amp;d=%S(zUuid)">%S(zUuid)</a>
1851 }
1852 db_finalize(&q);
1853 style_footer();
1854 }
1855
1856 /*
1857 ** Creates a TEMP VIEW named v_reports which is a wrapper around the
1858 ** EVENT table filtered on event.type. It looks for the request
1859 ** parameter 'y' (for consistency with /timeline) and expects it to
1860 ** contain one of the conventional values from event.type or the value
1861 ** "all", which is treated as equivalent to "*". By default (if no
1862 ** 'y' is specified), "*" is assumed (that is also the default for
1863 ** invalid/unknown filter values). That 'y' filter is the one used for
1864 ** the event list. Note that a filter of "*" or "all" is equivalent to
1865 ** querying against the full event table. The view, however, adds an
1866 ** abstraction level to simplify the implementation code for the
1867 ** various /reports pages.
1868 **
1869 ** Returns one of: 'c', 'w', 'g', 't', 'e', representing the type of
1870 ** filter it applies, or 0 (NUL) if no filter is applied (i.e. if
1871 ** "all" is used).
1872 */
1873 static char stats_report_init_view(){
1874 char const * zType = PD("y","*"); /* analog to /timeline?y=... */
1875 char const * zRealType = NULL; /* normalized form of zType */
1876 char rc = 0; /* result code */
1877 switch( (zType && *zType) ? *zType : '*' ){
1878 case 'c':
1879 case 'C':
1880 zRealType = "ci";
1881 rc = *zRealType;
1882 break;
1883 case 'e':
1884 case 'E':
1885 zRealType = "e";
1886 rc = *zRealType;
1887 break;
1888 case 'g':
1889 case 'G':
1890 zRealType = "g";
1891 rc = *zRealType;
1892 break;
1893 case 't':
1894 case 'T':
1895 zRealType = "t";
1896 rc = *zRealType;
1897 break;
1898 case 'w':
1899 case 'W':
1900 zRealType = "w";
1901 rc = *zRealType;
1902 break;
1903 default:
1904 rc = '*';
1905 break;
1906 }
1907 assert(0 != rc);
1908 if(zRealType){
1909 assert(*zRealType);
1910 db_multi_exec("CREATE TEMP VIEW v_reports AS "
1911 "SELECT * FROM event WHERE type GLOB %Q",
1912 zRealType);
1913 }else{
1914 db_multi_exec("CREATE TEMP VIEW v_reports AS "
1915 "SELECT * FROM event");
1916 }
1917 return rc;
1918 }
1919
1920
1921 /*
1922 ** Helper for stats_report_by_month_year(), which generates a list of
1923 ** week numbers. zTimeframe should be either a timeframe in the form YYYY
1924 ** or YYYY-MM.
@@ -1864,11 +1929,11 @@
1929 memcpy(yearPart, zTimeframe, 4);
1930 db_prepare(&stWeek,
1931 "SELECT DISTINCT strftime('%%W',mtime) AS wk, "
1932 "count(*) AS n, "
1933 "substr(date(mtime),1,%d) AS ym "
1934 "FROM v_reports "
1935 "WHERE ym=%Q AND mtime < current_timestamp "
1936 "GROUP BY wk ORDER BY wk",
1937 strlen(zTimeframe),
1938 zTimeframe);
1939 while( SQLITE_ROW == db_step(&stWeek) ){
@@ -1908,16 +1973,17 @@
1973 Blob header = empty_blob; /* Page header text */
1974 int nMaxEvents = 1; /* for calculating length of graph
1975 bars. */
1976 int iterations = 0; /* number of weeks/months we iterate
1977 over */
1978 stats_report_init_view();
1979 blob_appendf(&header, "Timeline Events by year%s",
1980 (includeMonth ? "/month" : ""));
1981 blob_appendf(&sql,
1982 "SELECT substr(date(mtime),1,%d) AS timeframe, "
1983 "count(*) AS eventCount "
1984 "FROM v_reports ",
1985 includeMonth ? 7 : 4);
1986 if(zUserName&&*zUserName){
1987 blob_appendf(&sql, " WHERE user=%Q ", zUserName);
1988 blob_appendf(&header," for user %q", zUserName);
1989 }
@@ -2053,14 +2119,15 @@
2119 int rowClass = 0; /* counter for alternating
2120 row colors */
2121 Blob sql = empty_blob; /* SQL */
2122 int nMaxEvents = 1; /* max number of events for
2123 all rows. */
2124 stats_report_init_view();
2125 blob_append(&sql,
2126 "SELECT user, "
2127 "COUNT(*) AS eventCount "
2128 "FROM v_reports "
2129 "GROUP BY user ORDER BY eventCount DESC",
2130 -1);
2131 db_prepare(&query, blob_str(&sql));
2132 blob_reset(&sql);
2133 @ <h1>Timeline Events by User</h1>
@@ -2121,14 +2188,15 @@
2188 Blob sql = empty_blob;
2189 int nMaxEvents = 1; /* max number of events for
2190 all rows. */
2191 int iterations = 0; /* # of active time periods. */
2192
2193 stats_report_init_view();
2194 cgi_printf("Select year: ");
2195 blob_append(&sql,
2196 "SELECT DISTINCT substr(date(mtime),1,4) AS y "
2197 "FROM v_reports WHERE 1 ", -1);
2198 if(zUserName&&*zUserName){
2199 blob_appendf(&sql,"AND user=%Q ", zUserName);
2200 }
2201 blob_append(&sql,"GROUP BY y ORDER BY y", -1);
2202 db_prepare(&qYears, blob_str(&sql));
2203

Keyboard Shortcuts

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