def wsq_to_txt(table_name, date):
	"""
	Vuelca en tmp/dumped.txt el resultado de la query a la BD squidlogs
	"""
	if(table_name == 'visited'):
		query = "select date(f_date_time), substr(dayname(f_date_time),1,2), " + \
			"f_lang_id, f_ns_id, count(*) " + \
			"from Filtered where f_action_id is null " + \
			"and date(f_date_time) = '" + date.strftime('%Y-%m-%d') + "' " + \
			"group by date(f_date_time), f_lang_id, f_ns_id;"
	elif(table_name == 'saved'):
		query = "select date(f_date_time), substr(dayname(f_date_time),1,2), " + \
			"f_lang_id, f_ns_id, count(*) " + \
			"from Filtered where f_action_id = 2 " + \
			"and date(f_date_time) = '" + date.strftime('%Y-%m-%d') + "' " + \
			"group by date(f_date_time), f_lang_id, f_ns_id;"
	elif(table_name == 'actions'):
		query = "select date(f_date_time), substr(dayname(f_date_time),1,2), " + \
			"f_action_id, f_lang_id, f_ns_id, count(*) " + \
			"from Filtered where f_action_id in (0, 1, 3, 4) " + \
			"and date(f_date_time) = '" + date.strftime('%Y-%m-%d') + "' " + \
			"group by date(f_date_time), f_action_id, f_lang_id, f_ns_id;"

	log_msg4("Creando dump para " + table_name)

	exec_mysql(getConfig().db_name_squidlogs, query=query, dumped=True)

	log_msg_ok4()
Пример #2
0
def del_row_query_for_squidlogs(date):
	"""
	Ejecuta la query sobre squidlogs para eliminar los registros para la fecha dada
	"""
	query = "DELETE FROM Filtered " + \
			"WHERE date(f_date_time) = '" + date.strftime('%Y-%m-%d') + "'"

	exec_mysql(getConfig().db_name_squidlogs, query=query)
Пример #3
0
def del_table_query_for_analysis(table_name, year):
	"""
	Ejecuta la query sobre analysis para eliminar las tablas para
	el año dado
	"""
	# http://dev.mysql.com/doc/refman/5.0/en/delete.html
	query = "DROP TABLE IF EXISTS " + table_name + year

	exec_mysql(getConfig().db_name_analysis, query=query)
Пример #4
0
def del_row_query_for_analysis(table_name, date):
	"""
	Ejecuta la query sobre analysis para eliminar los registros
	de una tabla y fecha dada
	"""
	# http://dev.mysql.com/doc/refman/5.0/en/delete.html
	query = "DELETE FROM " + table_name + date.strftime('%Y') + \
			" WHERE day = '" + str(date) + "'"

	exec_mysql(getConfig().db_name_analysis, query=query)
def create_table(table_name):
	"""
	Crea nueva tabla
	"""
	table_name_year = get_table_name_year(table_name)
	query = "DROP TABLE IF EXISTS " + table_name_year + ";" + \
			"CREATE TABLE " + table_name_year + " (" + \
				"day DATE," + \
				"dayWeek VARCHAR(2),"

	# si la tabla es actions hay que añadir este campo
	if(table_name == 'actions'):
		query += "action TINYINT,"

	query += "lang VARCHAR(2)," + \
				"ns TINYINT," + \
				"count int" + \
			");" + \
			"alter table " + table_name_year + " add index (day, dayWeek, lang, ns);"

	exec_mysql(DB_NAME, query=query)
def txt_to_table(table_name):
	"""
	Populo la tabla con los datos del _result.txt
	"""
	table_name_year = get_table_name_year(table_name)

	# si el fichero no tiene nada no hay nada que pasar a la BD
	if is_empty(TXT_FILE):
		log_msg4("WARNING: No se populó la tabla. dump.txt vacío!!")
		return

	# http://stackoverflow.com/questions/3971541/what-file-and-directory-permissions-are-required-for-mysql-load-data-infile
	# http://www.markhneedham.com/blog/2011/01/18/mysql-the-used-command-is-not-allowed-with-this-mysql-version/
	query = "LOAD DATA LOCAL INFILE '" + TXT_FILE + "' INTO TABLE " + \
		table_name_year + " IGNORE 1 LINES;"

	log_msg4("Volcando sobre " + table_name_year)

	exec_mysql(DB_NAME, query=query, options=['local-infile'])

	log_msg_ok4()