def signIn(self,username,password):
try:
if (os.getenv('SERVER_SOFTWARE') and
os.getenv('SERVER_SOFTWARE').startswith('Google App Engine/')):
db = MySQLdb.connect(unix_socket='/cloudsql/' + _INSTANCE_NAME, db='DATABASE', user='******')
else:
db = MySQLdb.connect(host='HOSTIP', port=3306, db='DATABASE', user='******',passwd='PASSWORD')
cursor = db.cursor()
start_timeForInsert = time.time()#Start time of insert query
start_time = time.time()
#Validate user authentication by selecting username from table
cursor.execute('SELECT username,password FROM user where username IN (%s)',username)
threadlist=[]
for row in cursor.fetchall():
if row[0]==username:
if row[1]==password:
cursor.execute('SELECT thread_name FROM thread')
for row1 in cursor.fetchall():
threadlist.append(row1[0])
variables = {'usernameSaved':username,'threadlist' :threadlist}
template = JINJA_ENVIRONMENT.get_template('home.html')
self.response.write(template.render(variables))
else:
self.response.write('Please Enter Correct details')
#db.commit()
db.close()
except DeadlineExceededError, error_message:
logging.exception('Failed, exception happened - %s' % error_message)
self.response.write('<br />')
def signIn(self,username,password):
try:
if (os.getenv('SERVER_SOFTWARE') and
os.getenv('SERVER_SOFTWARE').startswith('Google App Engine/')):
db = MySQLdb.connect(unix_socket='/cloudsql/' + _INSTANCE_NAME, db='DATABASE', user='******')
else:
db = MySQLdb.connect(host='HOSTIP', port=3306, db='DATABASE', user='******',passwd='PASSWORD')
cursor = db.cursor()
start_timeForInsert = time.time()#Start time of insert query
start_time = time.time()
#Validate user authentication by selecting username from table
cursor.execute('SELECT username,password FROM user where username IN (%s)',username)
threadlist=[]
for row in cursor.fetchall():
if row[0]==username:
if row[1]==password:
cursor.execute('SELECT thread_name FROM thread')
for row1 in cursor.fetchall():
threadlist.append(row1[0])
variables = {'usernameSaved':username,'threadlist' :threadlist}
template = JINJA_ENVIRONMENT.get_template('home.html')
self.response.write(template.render(variables))
else:
self.response.write('Please Enter Correct details')
#db.commit()
db.close()
except DeadlineExceededError, error_message:
logging.exception('Failed, exception happened - %s' % error_message)
self.response.write('<br />')
def write_file(self,fileName,name):
if (os.getenv('SERVER_SOFTWARE') and
os.getenv('SERVER_SOFTWARE').startswith('Google App Engine/')):
db = MySQLdb.connect(unix_socket='/cloudsql/' + _INSTANCE_NAME, db='DATABASE', user='******')
else:
db = MySQLdb.connect(host='HOSTIP', port=3306, db='DATABASE', user='******',passwd='PASSWORD')
cursor = db.cursor()
threadname= cgi.escape(self.request.get('threadNm'))#Get threadname from html
username=self.request.get('usernameHd')#Get username from html
img_img = image = self.request.get("image")
#self.response.write('Creating file %s\n' % filename)
write_retry_params = gcs.RetryParams(backoff_factor=1.1)
gcs_file = gcs.open(fileName,
'w',
content_type='image/jpg',
retry_params=write_retry_params,
options={'x-goog-acl': 'public-read'}
)
gcs_file.write(img_img)
gcs_file.close()
#url of where image going to upload
url='Path of googleCloud Bucket'+name
try:#Inseting url,username and thread name to table
cursor.execute('INSERT INTO thread (username,imageref,thread_name) VALUES (%s,%s,%s)',(username,url,threadname))
except:
self.response.write('Thread Already Exists')
db.commit()
cursor.execute('SELECT thread_name FROM thread')
threadlist=[]
for row1 in cursor.fetchall():
threadlist.append(row1[0])
db.close()
self.tmp_filenames_to_clean_up.append(fileName)
#passing threadlist,username and threadname
variables={'threadlist' :threadlist,'usernameSaved':username,'threadname':threadname}
template = JINJA_ENVIRONMENT.get_template('home.html')
self.response.write(template.render(variables))
def write_file(self,fileName,name):
if (os.getenv('SERVER_SOFTWARE') and
os.getenv('SERVER_SOFTWARE').startswith('Google App Engine/')):
db = MySQLdb.connect(unix_socket='/cloudsql/' + _INSTANCE_NAME, db='DATABASE', user='******')
else:
db = MySQLdb.connect(host='HOSTIP', port=3306, db='DATABASE', user='******',passwd='PASSWORD')
cursor = db.cursor()
threadname= cgi.escape(self.request.get('threadNm'))#Get threadname from html
username=self.request.get('usernameHd')#Get username from html
img_img = image = self.request.get("image")
#self.response.write('Creating file %s\n' % filename)
write_retry_params = gcs.RetryParams(backoff_factor=1.1)
gcs_file = gcs.open(fileName,
'w',
content_type='image/jpg',
retry_params=write_retry_params,
options={'x-goog-acl': 'public-read'}
)
gcs_file.write(img_img)
gcs_file.close()
#url of where image going to upload
url='https://console.developers.google.com/m/cloudstorage/b/cloudbucket12/o/'+name
try:#Inseting url,username and thread name to table
cursor.execute('INSERT INTO thread (username,imageref,thread_name) VALUES (%s,%s,%s)',(username,url,threadname))
except:
self.response.write('Thread Already Exists')
db.commit()
cursor.execute('SELECT thread_name FROM thread')
threadlist=[]
for row1 in cursor.fetchall():
threadlist.append(row1[0])
db.close()
self.tmp_filenames_to_clean_up.append(fileName)
#passing threadlist,username and threadname
variables={'threadlist' :threadlist,'usernameSaved':username,'threadname':threadname}
template = JINJA_ENVIRONMENT.get_template('home.html')
self.response.write(template.render(variables))
def RealMain(argv, data=None):
os.environ['LC_ALL'] = 'C'
options, args = parser.parse_args(argv[1:])
srv = GetRpcServer(options)
backup = Proxy(BackupService_Stub(srv))
db = PgSQL.connect(database=options.dbname,
client_encoding="utf-8",
unicode_results=1)
db.cursor().execute("set client_encoding to unicode")
store = LocalStore(db)
print 'BEGIN BACKUP'
for kind_name in KINDS:
sys.stdout.write('\n')
cnt = 0
last_key = ''
while True:
sys.stdout.write('\r%-18s ... ' % kind_name)
r = NextChunkRequest()
r.kind = kind_name
r.last_key = last_key
r = backup.NextChunk(r)
if not r.entity:
break
for entity in r.entity:
cnt += 1
sys.stdout.write('\r%-18s ... %5d ' % (kind_name, cnt))
o = pickle.load(cStringIO.StringIO(entity.data))
getattr(store, 'save_%s' % kind_name)(entity, o)
last_key = entity.key
db.commit()
sys.stdout.write('\n')
print 'BACKUP DONE'
db.commit()
db.close()
def RealMain(argv, data=None):
os.environ['LC_ALL'] = 'C'
options, args = parser.parse_args(argv[1:])
srv = GetRpcServer(options)
backup = Proxy(BackupService_Stub(srv))
db = PgSQL.connect(database=options.dbname,
client_encoding="utf-8",
unicode_results=1)
db.cursor().execute("set client_encoding to unicode")
store = LocalStore(db)
print 'BEGIN BACKUP'
for kind_name in KINDS:
sys.stdout.write('\n')
cnt = 0
last_key = ''
while True:
sys.stdout.write('\r%-18s ... ' % kind_name)
r = NextChunkRequest()
r.kind = kind_name
r.last_key = last_key
r = backup.NextChunk(r)
if not r.entity:
break
for entity in r.entity:
cnt += 1
sys.stdout.write('\r%-18s ... %5d ' % (kind_name, cnt))
o = pickle.load(cStringIO.StringIO(entity.data))
getattr(store, 'save_%s' % kind_name)(entity, o)
last_key = entity.key
db.commit()
sys.stdout.write('\n')
print 'BACKUP DONE'
db.commit()
db.close()
def post(self):
threadname = self.request.get('threadnametorm')
loggedUserName=self.request.get('usernameLoggedIn')
if (os.getenv('SERVER_SOFTWARE') and
os.getenv('SERVER_SOFTWARE').startswith('Google App Engine/')):
db = MySQLdb.connect(unix_socket='/cloudsql/' + _INSTANCE_NAME, db='DATABASE', user='******')
else:
db = MySQLdb.connect(host='HOSTIP', port=3306, db='DATABASE', user='******',passwd='PASSWORD')
cursor = db.cursor()
cursor.execute("DELETE FROM thread WHERE thread_name = '%s';"% threadname.strip())
db.commit()
cursor.execute('SELECT thread_name FROM thread')
threadlist=[]
for row1 in cursor.fetchall():
threadlist.append(row1[0])
db.close()
variables={'threadlist' :threadlist,'usernameSaved':loggedUserName,'threadname':threadname}
template = JINJA_ENVIRONMENT.get_template('home.html')
self.response.write(template.render(variables))
def post(self):
threadname = self.request.get('threadnametorm')
loggedUserName=self.request.get('usernameLoggedIn')
if (os.getenv('SERVER_SOFTWARE') and
os.getenv('SERVER_SOFTWARE').startswith('Google App Engine/')):
db = MySQLdb.connect(unix_socket='/cloudsql/' + _INSTANCE_NAME, db='DATABASE', user='******')
else:
db = MySQLdb.connect(host='HOSTIP', port=3306, db='DATABASE', user='******',passwd='PASSWORD')
cursor = db.cursor()
cursor.execute("DELETE FROM thread WHERE thread_name = '%s';"% threadname.strip())
db.commit()
cursor.execute('SELECT thread_name FROM thread')
threadlist=[]
for row1 in cursor.fetchall():
threadlist.append(row1[0])
db.close()
variables={'threadlist' :threadlist,'usernameSaved':loggedUserName,'threadname':threadname}
template = JINJA_ENVIRONMENT.get_template('home.html')
self.response.write(template.render(variables))
def get(self):
self.response.headers['Content-Type'] = 'text/plain'
if (os.getenv('SERVER_SOFTWARE') and
os.getenv('SERVER_SOFTWARE').startswith('Google App Engine/')):
db = MySQLdb.connect(unix_socket='/cloudsql/' + _INSTANCE_NAME, db=_DB, user='******')
cursor = db.cursor()
cursor.execute('SHOW TABLES')
for r in cursor.fetchall():
self.response.write('%s\n' % str(r))
cursor.execute('SELECT * FROM mqtt_messages LIMIT 200')
for r in cursor.fetchall():
self.response.write('%s\n' % str(r))
cursor.execute('SELECT * FROM plugin_google_activity_recognition LIMIT 200')
for r in cursor.fetchall():
self.response.write('%s\n' % str(r))
cursor.execute('SELECT * FROM plugin_mode_of_transportation LIMIT 200')
for r in cursor.fetchall():
self.response.write('%s\n' % str(r))
cursor.execute('SELECT * FROM locations LIMIT 200')
for r in cursor.fetchall():
self.response.write('%s\n' % str(r))
cursor.execute("SELECT from_unixtime(locations.timestamp/1000,'%Y-%m-%d') as day_with_data, count(*) as records FROM locations GROUP by day_with_data;")
self.response.write("#Days with data from location data")
for r in cursor.fetchall():
self.response.write('%s\n' % str(r))
self.response.write("#activity per day")
cursor.execute("SELECT FROM_UNIXTIME(timestamp/1000,'%Y-%m-%d') as day, activity_name, (max(timestamp)-min(timestamp))/1000 as time_elapsed_seconds FROM plugin_google_activity_recognition GROUP BY day, activity_name, FROM_UNIXTIME(timestamp/1000,'%Y-%m-%d %H:%m');")
for r in cursor.fetchall():
self.response.write('%s\n' % str(r))
db.close()
else:
self.response.write('Need to connect from Google Appspot')
def signUp(self,username,password,cnpassword):
try:
if (os.getenv('SERVER_SOFTWARE') and
os.getenv('SERVER_SOFTWARE').startswith('Google App Engine/')):
db = MySQLdb.connect(unix_socket='/cloudsql/' + _INSTANCE_NAME, db='DATABASE', user='******')
else:
db = MySQLdb.connect(host='HOSTIP', port=3306, db='DATABASE', user='******',passwd='PASSWORD')
cursor = db.cursor()
start_timeForInsert = time.time()#Start time of insert query
#Inserting new user details inside table
cursor.execute('INSERT INTO user (username,password) VALUES (%s,%s)',(username,password))
self.response.write('<br />')
start_time = time.time()
template = JINJA_ENVIRONMENT.get_template('sign_up.html')
self.response.write(template.render())
db.commit()
db.close()
except DeadlineExceededError, error_message:
logging.exception('Failed, exception happened - %s' % error_message)
self.response.write('<br />')
def signUp(self,username,password,cnpassword):
try:
if (os.getenv('SERVER_SOFTWARE') and
os.getenv('SERVER_SOFTWARE').startswith('Google App Engine/')):
db = MySQLdb.connect(unix_socket='/cloudsql/' + _INSTANCE_NAME, db='DATABASE', user='******')
else:
db = MySQLdb.connect(host='HOSTIP', port=3306, db='DATABASE', user='******',passwd='PASSWORD')
cursor = db.cursor()
start_timeForInsert = time.time()#Start time of insert query
#Inserting new user details inside table
cursor.execute('INSERT INTO user (username,password) VALUES (%s,%s)',(username,password))
self.response.write('<br />')
start_time = time.time()
template = JINJA_ENVIRONMENT.get_template('sign_up.html')
self.response.write(template.render())
db.commit()
db.close()
except DeadlineExceededError, error_message:
logging.exception('Failed, exception happened - %s' % error_message)
self.response.write('<br />')
def get(self):
"""default landing page"""
if (os.getenv('SERVER_SOFTWARE') and
os.getenv('SERVER_SOFTWARE').startswith('Google App Engine/')):
db = MySQLdb.connect(unix_socket='/cloudsql/' + _INSTANCE_NAME,
db=_DB,
user='******')
cursor = db.cursor()
logging.info("making queries")
# some sample queries that will write examples of the sort of
# data we have collected to the log so you can get a sense of things
self.make_and_print_query(cursor, 'SHOW TABLES',
'Show the names of all tables')
self.make_and_print_query(
cursor, 'SELECT DISTINCT device_id FROM locations',
'List all device ids')
self.make_and_print_query(
cursor,
"SELECT * FROM mqtt_messages WHERE device_id = '{0}' LIMIT 10"
.format(_ID), 'Example contents of mqtt_messages')
self.make_and_print_query(
cursor,
"SELECT * FROM plugin_google_activity_recognition WHERE device_id = '{0}' LIMIT 10 "
.format(_ID),
'Example contents of plugin_google_activity_recognition')
self.make_and_print_query(
cursor,
"SELECT * FROM locations WHERE device_id = '{0}' LIMIT 10".
format(_ID), 'Example contents of locations')
# this query collects information about the number
# of log enteries for each day.
day = "FROM_UNIXTIME(timestamp/1000,'%Y-%m-%d')"
query = "SELECT {0} as day_with_data, count(*) as records FROM {1} WHERE device_id = '{2}' GROUP by day_with_data".format(
day, _LOCATIONS, _ID)
rows = self.make_query(cursor, query)
queries = [{"query": query, "results": rows}]
# this query lets us collect information about
# locations that are visited so we can bin them.
query = "SELECT double_latitude, double_longitude FROM {0} WHERE device_id = '{1}'".format(
_LOCATIONS, _ID)
locations = self.make_query(cursor, query)
#locations = self.make_and_print_query(cursor, query, "locatons")
bins = self.bin_locations(locations, _EPSILON)
for location in bins:
logging.info('%s\n' % str(location))
queries = queries + [{"query": query, "results": bins}]
# now get locations organized by day and hour
time_of_day = "FROM_UNIXTIME(timestamp/1000,'%H')"
day = "FROM_UNIXTIME(timestamp/1000,'%Y-%m-%d')"
query = "SELECT {0} as day, {1} as time_of_day, double_latitude, double_longitude FROM {2} WHERE device_id = '{3}' GROUP BY day, time_of_day".format(
day, time_of_day, _LOCATIONS, _ID)
locations = self.make_query(cursor, query)
# and get physical activity per day and hour
# activity name and duration in seconds
day_and_time_of_day = "FROM_UNIXTIME(timestamp/100, '%Y-%m-%d %H')"
elapsed_seconds = "(max(timestamp)-min(timestamp))/1000"
query = "SELECT {0} as day, {1} as time_of_day, activity_name, {2} as time_elapsed_seconds FROM {3} WHERE device_id = '{4}' GROUP BY day, activity_name, {5}".format(
day, time_of_day, elapsed_seconds, _ACTIVITY, _ID,
day_and_time_of_day)
activities = self.make_query(cursor, query)
# now we want to associate activities with locations. This will update the
# bins list with activities.
self.group_activities_by_location(bins, locations, activities,
_EPSILON)
db.close()
else:
queries = [{
"query": 'Need to connect from Google Appspot',
"results": []
}]
context = {"queries": queries}
logging.info("context")
logging.info(context)
# and render the response
self.render_response('index.html', context)
comments=comments+'ed=ed'+loggedUserName+'cmnet'+commentsNew
comments_split=comments.split("ed=ed")#Comments are added in format as username+cmnet+comment+ed=ed+username2+cmnet+comment+ed=ed
for items in comments_split:
list.append(items.split('cmnet'))
else:
comments=loggedUserName+'cmnet'+commentsNew
list.append(comments.split('cmnet'))
try:#Updating table to insert Comment into database
cursor.execute ('UPDATE thread SET comments=%s WHERE thread_name=%s',(comments,threadname))
except:
self.response.write('Error in update')
db.commit()
db.close()
variables = {'imageLink': imageref,'userName': userName,'commentsList':list,'threadname':threadname,'usernameSaved':loggedUserName}
template = JINJA_ENVIRONMENT.get_template('thread.html')
self.response.write(template.render(variables))
#Remove photo from database
class RemovePhoto(webapp2.RequestHandler):
def post(self):
threadname = self.request.get('threadnametorm')
loggedUserName=self.request.get('usernameLoggedIn')
if (os.getenv('SERVER_SOFTWARE') and
os.getenv('SERVER_SOFTWARE').startswith('Google App Engine/')):
db = MySQLdb.connect(unix_socket='/cloudsql/' + _INSTANCE_NAME, db='DATABASE', user='******')
else:
db = MySQLdb.connect(host='HOSTIP', port=3306, db='DATABASE', user='******',passwd='PASSWORD')
cursor = db.cursor()
db.rollback
self.response.out.write("<br>**********Database after insertion:")
sql = "SELECT * FROM gsdmarin.%s" % TABLE_NAME
cursor.execute(sql)
for row in cursor.fetchall():
self.response.out.write("<br>" + ' '.join(str(e) for e in row) )
self.response.out.write('<br>************************************')
else:
self.response.out.write('Table %s does not exist. Create it' % TABLE_NAME)
# TODO: table creation code
db.close()
self.response.out.write('<br><<------------------------Everything went well?------------------------------------------------<<')
######################
####################
#self.redirect('/parse/%s' % blob_info.key()) ### TO CALL DOWNLOAD
except:
logging.error('Error in Uploading/Parsing')
self.response.out.write('Error in processing the file')
###DOWNLOADING!!!! FOR FUTURE REF.
class Parse(blobstore_handlers.BlobstoreDownloadHandler):
def get(self, file_key):
if not blobstore.get(file_key):
def get(self):
"""default landing page"""
if (os.getenv('SERVER_SOFTWARE') and
os.getenv('SERVER_SOFTWARE').startswith('Google App Engine/')):
db = MySQLdb.connect(unix_socket='/cloudsql/' + _INSTANCE_NAME, db=_DB, user='******')
cursor = db.cursor()
logging.info("making queries")
# some sample queries that will write examples of the sort of
# data we have collected to the log so you can get a sense of things
self.make_and_print_query(cursor, 'SHOW TABLES', 'Show the names of all tables')
self.make_and_print_query(cursor, 'SELECT DISTINCT device_id FROM locations', 'List all device ids')
self.make_and_print_query(cursor, "SELECT * FROM mqtt_messages WHERE device_id = '{0}' LIMIT 10".format(_ID), 'Example contents of mqtt_messages')
self.make_and_print_query(cursor, "SELECT * FROM plugin_google_activity_recognition WHERE device_id = '{0}' LIMIT 10 ".format(_ID), 'Example contents of plugin_google_activity_recognition')
self.make_and_print_query(cursor, "SELECT * FROM locations WHERE device_id = '{0}' LIMIT 10".format(_ID), 'Example contents of locations')
# this query collects information about the number
# of log enteries for each day.
day = "FROM_UNIXTIME(timestamp/1000,'%Y-%m-%d')"
query = "SELECT {0} as day_with_data, count(*) as records FROM {1} WHERE device_id = '{2}' GROUP by day_with_data".format(day, _LOCATIONS, _ID)
rows = self.make_query(cursor, query)
queries = [{"query": query, "results": rows}]
# this query lets us collect information about
# locations that are visited so we can bin them.
query = "SELECT double_latitude, double_longitude FROM {0} WHERE device_id = '{1}'".format(_LOCATIONS, _ID)
locations = self.make_query(cursor, query)
#locations = self.make_and_print_query(cursor, query, "locatons")
bins = self.bin_locations(locations, _EPSILON)
for location in bins:
logging.info('%s\n' % str(location))
queries = queries + [{"query": query, "results": bins}]
# now get locations organized by day and hour
time_of_day = "FROM_UNIXTIME(timestamp/1000,'%H')"
day = "FROM_UNIXTIME(timestamp/1000,'%Y-%m-%d')"
query = "SELECT {0} as day, {1} as time_of_day, double_latitude, double_longitude FROM {2} WHERE device_id = '{3}' GROUP BY day, time_of_day".format(day, time_of_day, _LOCATIONS, _ID)
locations = self.make_query(cursor, query)
# and get physical activity per day and hour
# activity name and duration in seconds
day_and_time_of_day = "FROM_UNIXTIME(timestamp/100, '%Y-%m-%d %H')"
elapsed_seconds = "(max(timestamp)-min(timestamp))/1000"
query = "SELECT {0} as day, {1} as time_of_day, activity_name, {2} as time_elapsed_seconds FROM {3} WHERE device_id = '{4}' GROUP BY day, activity_name, {5}".format(day, time_of_day, elapsed_seconds, _ACTIVITY, _ID, day_and_time_of_day)
activities = self.make_query(cursor, query)
# now we want to associate activities with locations. This will update the
# bins list with activities.
self.group_activities_by_location(bins, locations, activities, _EPSILON)
db.close()
else:
queries = [{"query": 'Need to connect from Google Appspot', "results": []}]
context = {"queries": queries}
logging.info("context")
logging.info(context)
# and render the response
self.render_response('index.html', context)
