def insert_log_info_to_preprocess_db(dic_my_activity_video_search, preprocess_db_path):
query = 'INSERT INTO parse_my_activity_video_search \
(timestamp, service, type, keyword, keyword_url, used_device) \
VALUES(%d, "%s", "%s", "%s", "%s", "%s")' % \
(int(dic_my_activity_video_search['timestamp']), dic_my_activity_video_search['service'], dic_my_activity_video_search['type'], \
dic_my_activity_video_search['keyword'], dic_my_activity_video_search['keyword_url'], dic_my_activity_video_search['used_device'])
SQLite3.execute_commit_query(query, preprocess_db_path)
def insert_log_info_to_preprocess_db(dic_browser_history,
preprocess_db_path):
query = 'INSERT INTO parse_chrome \
(timestamp, page_transition, url, title, client_id, favicon_url, used_device) \
VALUES(%d, "%s", "%s", "%s", "%s", "%s", "%s")' % \
(int(dic_browser_history['timestamp']), dic_browser_history['page_transition'], dic_browser_history['url'], \
dic_browser_history['title'], dic_browser_history['client_id'], dic_browser_history['favicon_url'], dic_browser_history['used_device'])
SQLite3.execute_commit_query(query, preprocess_db_path)
def insert_log_info_to_preprocess_db(dic_location_history,
preprocess_db_path):
query = 'INSERT INTO parse_location_history \
(timestamp, latitude, longitude, altitude, accuracy) \
VALUES(%d, "%s", "%s", "%s", "%s")' % \
(int(dic_location_history['timestamp']), dic_location_history['latitude'], dic_location_history['longitude'], \
dic_location_history['altitude'], dic_location_history['accuracy'])
SQLite3.execute_commit_query(query, preprocess_db_path)
def insert_log_info_to_preprocess_db(dic_my_activity_gmail,
preprocess_db_path):
query = 'INSERT INTO parse_my_activity_gmail \
(service, timestamp, type, keyword, keyword_url) \
VALUES("%s", %d, "%s", "%s", "%s")' % \
(dic_my_activity_gmail['service'], int(dic_my_activity_gmail['timestamp']), dic_my_activity_gmail['type'], \
dic_my_activity_gmail['keyword'], dic_my_activity_gmail['keyword_url'])
SQLite3.execute_commit_query(query, preprocess_db_path)
def insert_log_info_to_preprocess_db(dic_my_activity_assistant,
preprocess_db_path):
query = 'INSERT INTO parse_my_activity_assistant \
(timestamp, service, type, keyword, keyword_url, result, result_url, latitude, longitude, geodata_description, filepath, used_device) \
VALUES(%d, "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s")' % \
(int(dic_my_activity_assistant['timestamp']), dic_my_activity_assistant['service'], dic_my_activity_assistant['type'], \
dic_my_activity_assistant['keyword'], dic_my_activity_assistant['keyword_url'], dic_my_activity_assistant['result'], \
dic_my_activity_assistant['result_url'], dic_my_activity_assistant['latitude'], dic_my_activity_assistant['longitude'], \
dic_my_activity_assistant['geodata_description'], dic_my_activity_assistant['filepath'], dic_my_activity_assistant['used_device'])
SQLite3.execute_commit_query(query, preprocess_db_path)
def insert_log_info_to_preprocess_db(dic_google_photo, preprocess_db_path):
query = 'INSERT INTO parse_google_photo \
(parentpath, album_name, filename, extension, bytes, \
album_created_time, photo_taken_time, photo_created_time, photo_modified_time, file_modified_time, \
latitude, longitude, latitude_span, longitude_span, \
exif_latitude, exif_longitude, exif_latitude_span, exif_longitude_span, filepath) \
VALUES("%s", "%s", "%s", "%s", %d, %d, %d, %d, %d, %d, "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s")' % \
(dic_google_photo['parentpath'], dic_google_photo['album_name'], dic_google_photo['filename'], dic_google_photo['extension'], int(dic_google_photo['bytes']),\
int(dic_google_photo['album_created_time']), int(dic_google_photo['photo_taken_time']), int(dic_google_photo['photo_created_time']), int(dic_google_photo['photo_modified_time']), int(dic_google_photo['file_modified_time']), \
dic_google_photo['latitude'], dic_google_photo['longitude'], dic_google_photo['latitude_span'], dic_google_photo['longitude_span'], \
dic_google_photo['exif_latitude'], dic_google_photo['exif_longitude'], dic_google_photo['exif_latitude_span'], dic_google_photo['exif_longitude_span'], dic_google_photo['filepath'])
SQLite3.execute_commit_query(query, preprocess_db_path)
def insert_log_info_to_preprocess_db(dic_semantic_location_history,
preprocess_db_path):
query = 'INSERT INTO parse_semantic_location_history \
(type, stimestamp, slatitude, slongitude, place_name, place_addr, \
etimestamp, elatitude, elongitude, duration, distance, \
transportation, confidence, device_tag) \
VALUES("%s", %d, "%s", "%s", "%s", "%s", %d, "%s", "%s", %d, %d, "%s", "%s", "%s")' % \
(dic_semantic_location_history['type'], int(dic_semantic_location_history['stimestamp']), dic_semantic_location_history['slatitude'], dic_semantic_location_history['slongitude'], \
dic_semantic_location_history['place_name'], dic_semantic_location_history['place_addr'], \
dic_semantic_location_history['etimestamp'], dic_semantic_location_history['elatitude'], dic_semantic_location_history['elongitude'], \
dic_semantic_location_history['duration'], dic_semantic_location_history['distance'], dic_semantic_location_history['transportation'], dic_semantic_location_history['confidence'],\
dic_semantic_location_history['device_tag'])
SQLite3.execute_commit_query(query, preprocess_db_path)
def create_preprocess_db(self):
# def create_analysis_db(self):
# if os.path.exists(self.analysis_db_path):
# print("exist")
# ret = SQLite3.is_exist_table('parse_my_activity_assistant', self.analysis_db_path)
# print('table: ', ret)
# return self.analysis_db_path
list_query = list()
query_create_parse_chrome = "CREATE TABLE IF NOT EXISTS parse_chrome \
(timestamp INTEGER, page_transition TEXT, url TEXT, title TEXT, client_id TEXT, favicon_url TEXT, used_device TEXT)"
query_create_parse_contacts = "CREATE TABLE IF NOT EXISTS parse_contacts \
(category TEXT, name TEXT, tel TEXT, email TEXT, photo TEXT, note TEXT)"
query_create_parse_drive = "CREATE TABLE IF NOT EXISTS parse_drive \
(parentpath TEXT, filename TEXT, extension TEXT, modified_time INTEGER, bytes INTEGER, filepath TEXT)"
query_create_parse_google_photo = "CREATE TABLE IF NOT EXISTS parse_google_photo \
(parentpath TEXT, album_name TEXT, filename TEXT, extension TEXT, bytes INTEGER, \
album_created_time INTEGER, photo_taken_time INTEGER, photo_created_time INTEGER, photo_modified_time INTEGER, file_modified_time INTEGER, \
latitude TEXT, longitude TEXT, latitude_span TEXT, longitude_span TEXT, \
exif_latitude TEXT, exif_longitude TEXT, exif_latitude_span TEXT, exif_longitude_span TEXT, filepath TEXT)"
query_create_parse_location_history = "CREATE TABLE IF NOT EXISTS parse_location_history \
(timestamp INTEGER, latitude TEXT, longitude TEXT, altitude TEXT, accuracy TEXT)"
query_create_parse_semantic_location_history = "CREATE TABLE IF NOT EXISTS parse_semantic_location_history \
(type TEXT, stimestamp INTEGER, slatitude TEXT, slongitude TEXT, place_name TEXT, place_addr TEXT, \
etimestamp INTEGER, elatitude TEXT, elongitude TEXT, duration INTEGER, distance INTEGER, \
transportation TEXT, confidence TEXT, device_tag TEXT)"
query_create_parse_my_activity_android = "CREATE TABLE IF NOT EXISTS parse_my_activity_android \
(timestamp INTEGER, service TEXT, type TEXT, keyword TEXT, keyword_url TEXT, package_name TEXT, used_device TEXT)"
query_create_parse_my_activity_assistant = "CREATE TABLE IF NOT EXISTS parse_my_activity_assistant \
(timestamp INTEGER, service TEXT, type TEXT, keyword TEXT, keyword_url TEXT, result TEXT, result_url TEXT, \
latitude TEXT, longitude TEXT, geodata_description TEXT, filepath TEXT, used_device TEXT)"
query_create_parse_my_activity_chrome = "CREATE TABLE IF NOT EXISTS parse_my_activity_chrome \
(timestamp INTEGER, service TEXT, type TEXT, keyword TEXT, keyword_url TEXT, used_device TEXT)"
query_create_parse_my_activity_gmail = "CREATE TABLE IF NOT EXISTS parse_my_activity_gmail \
(timestamp INTEGER, service TEXT, type TEXT, keyword TEXT, keyword_url TEXT)"
query_create_parse_my_activity_google_analytics = "CREATE TABLE IF NOT EXISTS parse_my_activity_google_analytics \
(timestamp INTEGER, service TEXT, type TEXT, keyword TEXT, keyword_url TEXT, used_device TEXT)"
query_create_parse_my_activity_map = "CREATE TABLE IF NOT EXISTS parse_my_activity_map \
(timestamp INTEGER, service TEXT, type TEXT, keyword TEXT, keyword_url TEXT, keyword_latitude TEXT, keyword_longitude TEXT, \
latitude TEXT, longitude TEXT, geodata_description TEXT, used_device TEXT)"
query_create_parse_my_activity_video_search = "CREATE TABLE IF NOT EXISTS parse_my_activity_video_search \
(timestamp INTEGER, service TEXT, type TEXT, keyword TEXT, keyword_url TEXT, used_device TEXT)"
query_create_parse_my_activity_voice_audio = "CREATE TABLE IF NOT EXISTS parse_my_activity_voice_audio \
(timestamp INTEGER, service TEXT, type TEXT, keyword TEXT, keyword_url TEXT, filepath TEXT, used_device TEXT)"
query_create_parse_my_activity_youtube = "CREATE TABLE IF NOT EXISTS parse_my_activity_youtube \
(timestamp INTEGER, service TEXT, type TEXT, keyword TEXT, keyword_url TEXT, channel_name TEXT, channel_url TEXT)"
# dic_my_activity_assistant = {'service':"", 'type':"", 'url':"", 'keyword':"", 'answer':"", 'timestamp':"", 'geodata_latitude':"", 'geodata_longitude':"", 'geodata_description':"", 'attachment_voice_file':""}
# query_create_application_list_table = "CREATE TABLE application_list (is_deleted INTEGER, category TEXT, package_name TEXT, app_name TEXT, version TEXT, installed_time INTEGER, apk_changed_time INTEGER, updated_time INTEGER, deleted_time INTEGER, fs_ctime INTEGER, fs_crtime INTEGER, fs_atime INTEGER, fs_mtime INTEGER, is_updated INTEGER, source TEXT)"
# query_create_id_password_hash_table = "CREATE TABLE id_password_hash (package_name TEXT, url TEXT, account TEXT, pwd TEXT, contents TEXT, timestamp TEXT, source TEXT)"
# query_create_call_history_table = "CREATE TABLE call_history (package_name TEXT, timestamp TEXT, time_duration TEXT, phonenumber TEXT, account TEXT, digit_positive TEXT, file TEXT, contents TEXT, source TEXT)"
# query_create_geodata_table = "CREATE TABLE geodata (package_name TEXT, timestamp TEXT, geodata TEXT, file TEXT, contents TEXT, source TEXT)"
# query_create_web_brwoser_history_table = "CREATE TABLE web_browser_history (package_name TEXT, timestamp TEXT, url TEXT, account TEXT, digit_positive TEXT, file TEXT, contents TEXT, source TEXT)"
# query_create_file_history_table = "CREATE TABLE file_history (package_name TEXT, timestamp TEXT, file TEXT, phonenumber TEXT, account TEXT, contents TEXT, source TEXT)"
# query_create_embedded_filetable = "CREATE TABLE embedded_file (is_compressed INTEGER, parent_path TEXT, name TEXT, extension TEXT, mod_time TEXT, size INTEGER, compressed_size INTEGER, CRC INTEGER, create_system TEXT, source_path TEXT, source TEXT)"
list_query.append(query_create_parse_chrome)
list_query.append(query_create_parse_contacts)
list_query.append(query_create_parse_drive)
list_query.append(query_create_parse_google_photo)
list_query.append(query_create_parse_location_history)
list_query.append(query_create_parse_semantic_location_history)
# list_query.append(query_create_parse_location_history)
list_query.append(query_create_parse_my_activity_android)
list_query.append(query_create_parse_my_activity_assistant)
list_query.append(query_create_parse_my_activity_chrome)
list_query.append(query_create_parse_my_activity_gmail)
list_query.append(query_create_parse_my_activity_google_analytics)
list_query.append(query_create_parse_my_activity_map)
list_query.append(query_create_parse_my_activity_video_search)
list_query.append(query_create_parse_my_activity_voice_audio)
list_query.append(query_create_parse_my_activity_youtube)
# list_query.append(query_create_parse_my_activity_voice_audio)
# list_query.append(query_create_application_list_table)
# list_query.append(query_create_id_password_hash_table)
# list_query.append(query_create_call_history_table)
# list_query.append(query_create_geodata_table)
# list_query.append(query_create_web_brwoser_history_table)
# list_query.append(query_create_file_history_table)
# list_query.append(query_create_embedded_filetable)
SQLite3.execute_commit_query(list_query, self.preprocess_db_path)
def insert_album_info_to_preprocess_db(dic_album_info, preprocess_db_path):
query = 'UPDATE parse_google_photo set album_created_time = %d WHERE album_name = "%s"' % \
(int(dic_album_info['album_created_time']), dic_album_info['album_name'])
SQLite3.execute_commit_query(query, preprocess_db_path)
def is_exist_record(filepath, preprocess_db_path):
query = 'SELECT count(*) FROM parse_google_photo WHERE filepath = "%s"' % filepath
return SQLite3.execute_fetch_query(query, preprocess_db_path)[0]
def insert_log_info_to_preprocess_db(dic_drive, preprocess_db_path):
query = 'INSERT INTO parse_drive \
(parentpath, filename, extension, modified_time, bytes, filepath) \
VALUES("%s", "%s", "%s", %d, %d, "%s")' % \
(dic_drive['parentpath'], dic_drive['filename'], dic_drive['extension'], dic_drive['modified_time'], dic_drive['bytes'], dic_drive['filepath'])
SQLite3.execute_commit_query(query, preprocess_db_path)
def insert_log_info_to_preprocess_db(dic_contacts, preprocess_db_path):
query = 'INSERT INTO parse_contacts \
(category, name, tel, email, photo, note) \
VALUES("%s", "%s", "%s", "%s", "%s", "%s")' % \
(dic_contacts['category'], dic_contacts['name'], dic_contacts['tel'], dic_contacts['email'], dic_contacts['photo'], dic_contacts['note'])
SQLite3.execute_commit_query(query, preprocess_db_path)