-
Notifications
You must be signed in to change notification settings - Fork 4
/
Copy pathsql.py
236 lines (183 loc) · 9.47 KB
/
sql.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
#!/usr/bin/env python
# -*- coding: utf-8 -*-
#
# Google2Piwik - exporting Google Analytics to Piwik
#
# @link http://clearcode.cc/
# @license http://www.gnu.org/licenses/gpl-3.0.html GPL v3 or later
#
import MySQLdb
import warnings
import datetime
warnings.filterwarnings("ignore", category=MySQLdb.Warning)
T_LOGVA = "log_link_visit_action"
T_LOGA = "log_action"
T_LOGV = "log_visit"
T_SITE = "site"
INSERT_LOG_VISIT_ACTION = """INSERT INTO {{LVA}} (idvisit, idvisitor, server_time, idsite, idaction_url,
idaction_url_ref, idaction_name, time_spent_ref_action, idaction_name_ref)
VALUES (%s, binary(unhex(substring(%s,1,16))), %s, %s, %s, %s, %s, %s, 0) """
LOGV_TEMPLATE = u""" INSERT INTO {{LV}} (idsite, visitor_localtime, idvisitor, visitor_returning, config_id,
visit_first_action_time, visit_last_action_time,
visit_exit_idaction_url, visit_entry_idaction_url, visit_total_actions,
visit_total_time, referer_type, referer_name, visit_goal_converted,
referer_url, referer_keyword, config_os, config_browser_name,
config_browser_version, config_resolution, config_pdf, config_flash,
config_java, config_director, config_quicktime, config_realplayer,
config_windowsmedia, config_gears, config_silverlight, config_cookie,
location_ip, location_browser_lang, location_country{0},
visitor_count_visits, visitor_days_since_last, visitor_days_since_first,
visit_exit_idaction_name, visit_entry_idaction_name{1})
VALUES ( %(idsite)s, %(visitor_localtime)s, binary(unhex(substring(%(visitor_idcookie)s,1,16))),
%(visitor_returning)s, binary(unhex(substring(%(config_md5config)s,1,16))),
%(visit_first_action_time)s, %(visit_last_action_time)s,
%(visit_exit_idaction_url)s, %(visit_entry_idaction_url)s, %(visit_total_actions)s,
%(visit_total_time)s, %(referer_type)s, %(referer_name)s, 0,
%(referer_url)s, %(referer_keyword)s, %(config_os)s, %(config_browser_name)s,
%(config_browser_version)s, %(config_resolution)s, 0, %(config_flash)s,
%(config_java)s, 0, 0, 0, 0, 0, 0, 0, 0,
%(location_browser_lang)s, %(location_country)s{2},
%(visitor_count_visits)s, %(visitor_days_since_last)s, 0, 0, 0{3}) """
INSERT_LOG_VISIT = {
1.9: (", location_city", ", location_region", ", %(location_city)s", ", %(location_region)s"),
1.8: (", location_continent", "", ", %(location_continent)s", "")
}
INSERT_LOG_ACTION = {
1.8: "INSERT INTO {{LA}} (name, hash, type) VALUES (%s, %s, %s) ",
1.9: "INSERT INTO {{LA}} (name, hash, type, url_prefix) VALUES (%s, %s, %s, %s)"
}
SELECT_NB_VISITS = "SELECT count(*) FROM {{LV}} WHERE visitor_localtime = %s and idsite = %s"
def initialize(mysql_data):
global T_LOGVA, T_LOGA, T_LOGV, T_SITE
global db, cursor
global INSERT_LOG_VISIT_ACTION, INSERT_LOG_ACTION, INSERT_LOG_VISIT
global SELECT_NB_VISITS
global LOGV_TEMPLATE
prefix = mysql_data["table_prefix"]
T_LOGVA = "%s%s" % (prefix, T_LOGVA) if prefix else T_LOGVA
T_LOGA = "%s%s" % (prefix, T_LOGA) if prefix else T_LOGA
T_LOGV = "%s%s" % (prefix, T_LOGV) if prefix else T_LOGV
T_SITE = "%s%s" % (prefix, T_SITE) if prefix else T_SITE
INSERT_LOG_VISIT_ACTION = INSERT_LOG_VISIT_ACTION.replace("{{LVA}}", T_LOGVA)
SELECT_NB_VISITS = SELECT_NB_VISITS.replace("{{LV}}", T_LOGV)
LOGV_TEMPLATE = LOGV_TEMPLATE.replace("{{LV}}", T_LOGV)
for k, v in INSERT_LOG_VISIT.iteritems():
INSERT_LOG_VISIT[k] = LOGV_TEMPLATE.format(*INSERT_LOG_VISIT[k])
for k, v in INSERT_LOG_ACTION.iteritems():
INSERT_LOG_ACTION[k] = INSERT_LOG_ACTION[k].replace("{{LA}}", T_LOGA)
db = init_db(mysql_data)
db.set_character_set('utf8')
cursor = db.cursor()
def insert_log_action(values, version):
values_no = INSERT_LOG_ACTION[version].count('%s')
cursor.execute(INSERT_LOG_ACTION[version], values[:values_no])
return cursor.lastrowid
def insert_log_visit(values, version):
try:
cursor.execute(INSERT_LOG_VISIT[version], values)
except:
pass
return cursor.lastrowid
def insert_log_visit_action(values):
cursor.execute(INSERT_LOG_VISIT_ACTION, values)
return cursor.lastrowid
def init_db(mysql_data):
try:
db = MySQLdb.connect(mysql_data["host"], mysql_data["user"], mysql_data["passwd"],
mysql_data["db"], int(mysql_data["port"]))
return db
except MySQLdb.OperationalError, e:
print "There was problem connecting to your MySQL Service:"
print "Exception: ", e
exit()
def test_db(mysql_data):
global db, cursor
db = MySQLdb.connect(mysql_data["host"], mysql_data["user"], mysql_data["passwd"],
mysql_data["db"], int(mysql_data["port"]))
db.set_character_set('utf8')
cursor = db.cursor()
def get_sites(prefix):
select_site_sql = "SELECT idsite, name, main_url from {SITE_TABLE}".format(SITE_TABLE=prefix + T_SITE)
cursor.execute(select_site_sql)
return [{"id": id, "name": name, "url": url} for (id, name, url) in cursor.fetchall()]
def get_version(prefix):
t_option = "%soption" % (prefix) if prefix else "option"
select_version_sql = "SELECT option_value FROM {table} WHERE option_name = 'version_core'".format(table=t_option)
cursor.execute(select_version_sql)
version = cursor.fetchone()[0]
return version
def check_tables(table_prefix):
global cursor
failed = []
for table in ["log_action", "log_visit", "log_link_visit_action"]:
table_name = table if not table_prefix else "%s%s" % (table_prefix, table)
try:
cursor.execute("SELECT * FROM {name}".format(name=table_name))
except MySQLdb.ProgrammingError:
failed.append(table_name)
return failed
def check_site(site_id):
select_site_sql = "SELECT count(*) from {SITE_TABLE} WHERE idsite = %s".format(SITE_TABLE=T_SITE)
cursor.execute(select_site_sql, site_id)
return cursor.fetchone()[0] == 1
def update_site_ts_created(site_id, date):
current_start = datetime.datetime(date.year, date.month, date.day)
select_site_sql = "SELECT ts_created from {SITE_TABLE} WHERE idsite = %s".format(SITE_TABLE=T_SITE)
cursor.execute(select_site_sql, site_id)
ts_created = cursor.fetchone()[0]
if ts_created > current_start:
update_site_sql = "UPDATE {SITE_TABLE} SET ts_created = %s WHERE idsite = %s".format(SITE_TABLE=T_SITE)
cursor.execute(update_site_sql, (current_start, site_id))
def nb_visits_day(date, site_id):
cursor.execute(SELECT_NB_VISITS, (date, site_id))
return cursor.fetchone()[0]
def update_visit_actions(start_date, end_date):
raw_sql = """UPDATE {LV} AS lv
LEFT JOIN (
SELECT idvisit, COUNT(*) AS visit_actions
FROM
{LVA}
GROUP BY
idvisit
) AS m ON
m.idvisit = lv.idvisit
SET lv.visit_total_actions = m.visit_actions
WHERE visit_last_action_time >= %s
AND visit_last_action_time <= %s
""".format(LV=T_LOGV, LVA=T_LOGVA)
cursor.execute(raw_sql, (start_date, end_date))
def update_total_visit_actions():
raw_sql = """UPDATE {LV} AS lv
LEFT JOIN (
SELECT idvisit, COUNT(*) AS visit_actions
FROM
{LVA}
GROUP BY
idvisit
) AS m ON
m.idvisit = lv.idvisit
SET lv.visit_total_actions = m.visit_actions
""".format(LV=T_LOGV, LVA=T_LOGVA)
cursor.execute(raw_sql)
def finalize():
raw_sql = """UPDATE {LV}
SET visit_exit_idaction_name = visit_exit_idaction_url+1,
visit_entry_idaction_name = visit_entry_idaction_url+1;""".format(LV=T_LOGV)
cursor.execute(raw_sql)
raw_sql = """UPDATE {LVA}
SET idaction_name_ref = idaction_url_ref + 1;""".format(LVA=T_LOGVA)
cursor.execute(raw_sql)
def clear_archives():
query = cursor.execute('SHOW TABLES')
tables = cursor.fetchall()
to_drop = []
for col in tables:
if 'archive' in col[0]:
to_drop.append(col[0])
if to_drop:
raw_sql = 'DROP TABLE ' + (', ').join(to_drop)
cursor.execute(raw_sql)
def close():
global db
db.commit()
db.close()