[ckan-changes] [okfn/ckan] fb0fdb: update tracking tables
GitHub
noreply at github.com
Fri Apr 20 14:03:48 UTC 2012
Branch: refs/heads/enhancement-2251-tracking
Home: https://github.com/okfn/ckan
Commit: fb0fdb068d932242c5bf73413c6770559b96acff
https://github.com/okfn/ckan/commit/fb0fdb068d932242c5bf73413c6770559b96acff
Author: Toby <toby.junk at gmail.com>
Date: 2012-04-20 (Fri, 20 Apr 2012)
Changed paths:
M ckan/migration/versions/054_tracking.py
Log Message:
-----------
update tracking tables
diff --git a/ckan/migration/versions/054_tracking.py b/ckan/migration/versions/054_tracking.py
index edb60cd..5f2fe43 100644
--- a/ckan/migration/versions/054_tracking.py
+++ b/ckan/migration/versions/054_tracking.py
@@ -10,6 +10,9 @@ def upgrade(migrate_engine):
tracking_type character varying(10) NOT NULL,
access_timestamp timestamp without time zone DEFAULT current_timestamp
);
+ CREATE INDEX tracking_raw_url ON tracking_raw(url);
+ CREATE INDEX tracking_raw_user_key ON tracking_raw(user_key);
+ CREATE INDEX tracking_raw_access_timestamp ON tracking_raw(access_timestamp);
CREATE TABLE tracking_summary(
url text NOT NULL,
@@ -17,12 +20,13 @@ def upgrade(migrate_engine):
tracking_type character varying(10) NOT NULL,
count int NOT NULL,
running_total int NOT NULL DEFAULT 0,
- date date
+ recent_views int NOT NULL DEFAULT 0,
+ tracking_date date
);
CREATE INDEX tracking_summary_url ON tracking_summary(url);
CREATE INDEX tracking_summary_package_id ON tracking_summary(package_id);
- CREATE INDEX tracking_summary_date ON tracking_summary(date);
+ CREATE INDEX tracking_summary_date ON tracking_summary(tracking_date);
COMMIT;
'''
================================================================
Commit: 1c5c4dd7809aa66eaec10ace0cfd6bd667861fe3
https://github.com/okfn/ckan/commit/1c5c4dd7809aa66eaec10ace0cfd6bd667861fe3
Author: Toby <toby.junk at gmail.com>
Date: 2012-04-20 (Fri, 20 Apr 2012)
Changed paths:
M ckan/lib/helpers.py
Log Message:
-----------
remove old helper functions
diff --git a/ckan/lib/helpers.py b/ckan/lib/helpers.py
index e9a11c9..b4dd06e 100644
--- a/ckan/lib/helpers.py
+++ b/ckan/lib/helpers.py
@@ -658,32 +658,6 @@ def _auto_log_message():
return _('Edited settings.')
return ''
-def tracking_count_package(package_id):
- import ckan.model as model
- sql = '''SELECT running_total
- FROM tracking_summary
- WHERE package_id='%s'
- ORDER BY date DESC LIMIT 1''' % package_id
- result = model.Session.execute(sql).fetchall()
- if result:
- count = result[0]['running_total']
- return literal('<span title="') + 'Viewed %s times' % count + literal('"><i class="icon-eye-open"></i> %s</span>' % count)
- else:
- return literal('<span> </span>')
-
-def tracking_count_resource(url):
- import ckan.model as model
- sql = '''SELECT running_total
- FROM tracking_summary
- WHERE url='%s'
- ORDER BY date DESC LIMIT 1''' % url
- result = model.Session.execute(sql).fetchall()
- if result:
- count = result[0]['running_total']
- return literal('<span title="') + 'Downloaded %s times' % count + literal('"><i class="icon-eye-open"></i> %s</span>' % count)
- else:
- return literal('<span> </span>')
-
def activity_div(template, activity, actor, object=None, target=None):
actor = '<span class="actor">%s</span>' % actor
if object:
================================================================
Commit: f704db5511d18757f64d277e2e933e931d5ffdde
https://github.com/okfn/ckan/commit/f704db5511d18757f64d277e2e933e931d5ffdde
Author: Toby <toby.junk at gmail.com>
Date: 2012-04-20 (Fri, 20 Apr 2012)
Changed paths:
M ckan/lib/cli.py
Log Message:
-----------
date -> tracking_date changes
diff --git a/ckan/lib/cli.py b/ckan/lib/cli.py
index 6ca8e1f..8257180 100644
--- a/ckan/lib/cli.py
+++ b/ckan/lib/cli.py
@@ -905,11 +905,11 @@ def command(self):
# No date given. See when we last have data for and get data
# from 2 days before then in case new data is available.
# If no date here then use 2010-01-01 as the start date
- sql = '''SELECT date from tracking_summary
- ORDER BY date DESC LIMIT 1;'''
+ sql = '''SELECT tracking_date from tracking_summary
+ ORDER BY tracking_date DESC LIMIT 1;'''
result = engine.execute(sql).fetchall()
if result:
- start_date = result[0]['date']
+ start_date = result[0]['tracking_date']
start_date += datetime.timedelta(-2)
# convert date to datetime
combine = datetime.datetime.combine
@@ -928,20 +928,20 @@ def update_tracking(self, engine, summary_date):
PACKAGE_URL = '/dataset/'
# clear out existing data before adding new
sql = '''DELETE FROM tracking_summary
- WHERE date='%s'; ''' % summary_date
+ WHERE tracking_date='%s'; ''' % summary_date
engine.execute(sql)
sql = '''SELECT DISTINCT url, user_key,
- CAST(access_timestamp AS Date) AS date,
+ CAST(access_timestamp AS Date) AS tracking_date,
tracking_type INTO tracking_tmp
FROM tracking_raw
WHERE CAST(access_timestamp as Date)='%s';
INSERT INTO tracking_summary
- (url, count, date, tracking_type)
- SELECT url, count(user_key), date, tracking_type
+ (url, count, tracking_date, tracking_type)
+ SELECT url, count(user_key), tracking_date, tracking_type
FROM tracking_tmp
- GROUP BY url, date, tracking_type;
+ GROUP BY url, tracking_date, tracking_type;
DROP TABLE tracking_tmp;
COMMIT;''' % summary_date
@@ -962,9 +962,16 @@ def update_tracking(self, engine, summary_date):
SET running_total = (
SELECT sum(count)
FROM tracking_summary t2
- WHERE t1.url = t2.url AND t2.date <= t1.date)
- WHERE t1.running_total = 0
- AND tracking_type = 'resource';'''
+ WHERE t1.url = t2.url
+ AND t2.tracking_date <= t1.tracking_date
+ ) + t1.count
+ ,recent_views = (
+ SELECT sum(count)
+ FROM tracking_summary t2
+ WHERE t1.url = t2.url
+ AND t2.tracking_date <= t1.tracking_date AND t2.tracking_date >= t1.tracking_date - 14
+ ) + t1.count
+ WHERE t1.running_total = 0 AND tracking_type = 'resource';'''
engine.execute(sql)
# update summary totals for pages
@@ -973,7 +980,14 @@ def update_tracking(self, engine, summary_date):
SELECT sum(count)
FROM tracking_summary t2
WHERE t1.package_id = t2.package_id
- AND t2.date <= t1.date)
+ AND t2.tracking_date <= t1.tracking_date
+ ) + t1.count
+ ,recent_views = (
+ SELECT sum(count)
+ FROM tracking_summary t2
+ WHERE t1.package_id = t2.package_id
+ AND t2.tracking_date <= t1.tracking_date AND t2.tracking_date >= t1.tracking_date - 14
+ ) + t1.count
WHERE t1.running_total = 0 AND tracking_type = 'page'
AND t1.package_id IS NOT NULL
AND t1.package_id != '~~not~found~~';'''
================================================================
Commit: 49ae9866589b591b4a3f6b492cb35d9abbaa0eeb
https://github.com/okfn/ckan/commit/49ae9866589b591b4a3f6b492cb35d9abbaa0eeb
Author: Toby <toby.junk at gmail.com>
Date: 2012-04-20 (Fri, 20 Apr 2012)
Changed paths:
M ckan/config/solr/schema-1.3.xml
Log Message:
-----------
update solr schema
diff --git a/ckan/config/solr/schema-1.3.xml b/ckan/config/solr/schema-1.3.xml
index 5a65a71..1b7d578 100644
--- a/ckan/config/solr/schema-1.3.xml
+++ b/ckan/config/solr/schema-1.3.xml
@@ -133,6 +133,7 @@
<field name="child_of" type="text" indexed="true" stored="false" multiValued="true"/>
<field name="parent_of" type="text" indexed="true" stored="false" multiValued="true"/>
<field name="views" type="int" indexed="true" stored="false"/>
+ <field name="recent_views" type="int" indexed="true" stored="false"/>
<field name="metadata_created" type="date" indexed="true" stored="true" multiValued="false"/>
<field name="metadata_modified" type="date" indexed="true" stored="true" multiValued="false"/>
================================================================
Commit: cb480e242438b1a2e6ed33d45b7bddf567b18d18
https://github.com/okfn/ckan/commit/cb480e242438b1a2e6ed33d45b7bddf567b18d18
Author: Toby <toby.junk at gmail.com>
Date: 2012-04-20 (Fri, 20 Apr 2012)
Changed paths:
M ckan/lib/search/index.py
Log Message:
-----------
add recent views to search index
diff --git a/ckan/lib/search/index.py b/ckan/lib/search/index.py
index 600901c..1f9fbc2 100644
--- a/ckan/lib/search/index.py
+++ b/ckan/lib/search/index.py
@@ -136,6 +136,7 @@ def index_package(self, pkg_dict):
tracking_summary = pkg_dict.pop('tracking_summary', None)
if tracking_summary:
pkg_dict['views'] = tracking_summary['total']
+ pkg_dict['recent_views'] = tracking_summary['recent']
# flatten the structure for indexing:
for resource in pkg_dict.get('resources', []):
================================================================
Commit: 2727cb310f9fade0da50a51521112ffb25624e58
https://github.com/okfn/ckan/commit/2727cb310f9fade0da50a51521112ffb25624e58
Author: Toby <toby.junk at gmail.com>
Date: 2012-04-20 (Fri, 20 Apr 2012)
Changed paths:
M ckan/model/tracking.py
Log Message:
-----------
model/tracking changes
diff --git a/ckan/model/tracking.py b/ckan/model/tracking.py
index c138271..efac15a 100644
--- a/ckan/model/tracking.py
+++ b/ckan/model/tracking.py
@@ -7,29 +7,32 @@
Column('tracking_type', Unicode(10), nullable=False),
Column('count', Integer, nullable=False),
Column('running_total', Integer, nullable=False),
- Column('date', DateTime),
+ Column('recent_views ', Integer, nullable=False),
+ Column('tracking_date', DateTime),
)
class TrackingSummary(DomainObject):
@classmethod
def get_for_package(cls, package_id):
+ # FIXME should be ordered by date desc but didn't like order_by(date)
obj = Session.query(cls).autoflush(False)
data = obj.filter_by(package_id=package_id).first()
if data:
return {'total' : data.running_total,
- 'recent': 7,}#data.recent}
+ 'recent': data.recent_views}
return {'total' : 0, 'recent' : 0}
@classmethod
def get_for_resource(cls, url):
+ # FIXME should be ordered by date desc but didn't like order_by(date)
obj = Session.query(cls).autoflush(False)
data = obj.filter_by(url=url).first()
if data:
return {'total' : data.running_total,
- 'recent': 7,}#data.recent}
+ 'recent': data.recent_views}
return {'total' : 0, 'recent' : 0}
================================================================
Commit: 5d779d6772a8174bc6aea779e2a8bc9866cb1821
https://github.com/okfn/ckan/commit/5d779d6772a8174bc6aea779e2a8bc9866cb1821
Author: Toby <toby.junk at gmail.com>
Date: 2012-04-20 (Fri, 20 Apr 2012)
Changed paths:
M ckan/model/tracking.py
Log Message:
-----------
typo from hell bugfix
diff --git a/ckan/model/tracking.py b/ckan/model/tracking.py
index efac15a..044b1b5 100644
--- a/ckan/model/tracking.py
+++ b/ckan/model/tracking.py
@@ -7,7 +7,7 @@
Column('tracking_type', Unicode(10), nullable=False),
Column('count', Integer, nullable=False),
Column('running_total', Integer, nullable=False),
- Column('recent_views ', Integer, nullable=False),
+ Column('recent_views', Integer, nullable=False),
Column('tracking_date', DateTime),
)
================================================================
Commit: 71ebd118c2f67ce56a60c6cbfdd36719f3e4cc1b
https://github.com/okfn/ckan/commit/71ebd118c2f67ce56a60c6cbfdd36719f3e4cc1b
Author: Toby <toby.junk at gmail.com>
Date: 2012-04-20 (Fri, 20 Apr 2012)
Log Message:
-----------
Merge branch 'enhancement-2251-tracking' of github.com:okfn/ckan into enhancement-2251-tracking
================================================================
Compare: https://github.com/okfn/ckan/compare/680d751...71ebd11
More information about the ckan-changes
mailing list