def gen_nodes(n, db, start_lat, stop_lat, start_long, stop_long):
assert n > 0
cmd = "INSERT INTO catan_nodes VALUES "
# generate n random nodes, centered around Cambridge
for i in range(n):
# random lat, long
lat = round(random.uniform(start_lat, stop_lat), 6)
lng = round(random.uniform(start_long, stop_long), 6)
# node_id, gps_lat, gps_long, gps_acc, path, timestamp
sql_cmd = cmd + "(%d, %.6f, %.6f, %.6f, %.6f, %.6f)" % (i, lat, lng, 0, 0, 0)
db._sql(sql_cmd)
def print_database():
db = catan.db.CatanDatabase(0)
cmd = ('SELECT '
'db_person_bio.person_id, '
'db_person_bio.origin_node_id, '
'db_person_bio.name_family, '
'db_person_bio.name_given, '
'db_person_bio.age, '
'db_person_bio.sex, '
'db_person_messages.submission_id, '
'db_person_messages.origin_node_id, '
'db_person_messages.status_gps_latitude, '
'db_person_messages.status_gps_longitude, '
'db_person_messages.status_gps_accuracy, '
'db_person_messages.status, '
'db_person_messages.status_location, '
'db_submitter_info.timestamp '
'FROM db_person_bio '
'LEFT JOIN db_person_messages ON db_person_messages.person_id = db_person_bio.person_id '
'LEFT JOIN db_submitter_info ON db_submitter_info.submission_id = db_person_messages.submission_id')
for r in db._sql(cmd).fetchall():
print r
def populate_db():
db = catan.db.CatanDatabase(0)
# insert some test nodes
# for cambridge
gen_nodes(32, db, 42.354823, 42.368315, -71.114484, -71.084422)
gen_people(100, db, 42.354823, 42.368315, -71.114484, -71.084422)
cmd = (
"SELECT "
"db_person_bio.person_id, "
"db_person_bio.origin_node_id, "
"db_person_bio.name_family, "
"db_person_bio.name_given, "
"db_person_bio.age, "
"db_person_bio.sex, "
"db_person_messages.submission_id, "
"db_person_messages.origin_node_id, "
"db_person_messages.status_gps_latitude, "
"db_person_messages.status_gps_longitude, "
"db_person_messages.status_gps_accuracy, "
"db_person_messages.status, "
"db_person_messages.status_location, "
"db_submitter_info.timestamp "
"FROM db_person_bio "
"LEFT JOIN db_person_messages ON db_person_messages.person_id = db_person_bio.person_id "
"LEFT JOIN db_submitter_info ON db_submitter_info.submission_id = db_person_messages.submission_id"
)
for r in db._sql(cmd).fetchall():
print r
def gen_nodes(n, db, start_lat, stop_lat, start_long, stop_long):
assert n > 0
cmd = "INSERT INTO catan_nodes VALUES "
# generate n random nodes, centered around Cambridge
for i in range(n):
# random lat, long
lat = round(random.uniform(start_lat, stop_lat), 6)
lng = round(random.uniform(start_long, stop_long), 6)
# node_id, gps_lat, gps_long, gps_acc, path, timestamp
sql_cmd = cmd + "(%d, %.6f, %.6f, %.6f, %.6f, %.6f)" % (i, lat, lng, 0,
0, 0)
db._sql(sql_cmd)
def populate_db():
db = catan.db.CatanDatabase(0)
# insert some test nodes
# for cambridge
gen_nodes(32, db, 42.354823, 42.368315, -71.114484, -71.084422)
gen_people(100, db, 42.354823, 42.368315, -71.114484, -71.084422)
cmd = (
'SELECT '
'db_person_bio.person_id, '
'db_person_bio.origin_node_id, '
'db_person_bio.name_family, '
'db_person_bio.name_given, '
'db_person_bio.age, '
'db_person_bio.sex, '
'db_person_messages.submission_id, '
'db_person_messages.origin_node_id, '
'db_person_messages.status_gps_latitude, '
'db_person_messages.status_gps_longitude, '
'db_person_messages.status_gps_accuracy, '
'db_person_messages.status, '
'db_person_messages.status_location, '
'db_submitter_info.timestamp '
'FROM db_person_bio '
'LEFT JOIN db_person_messages ON db_person_messages.person_id = db_person_bio.person_id '
'LEFT JOIN db_submitter_info ON db_submitter_info.submission_id = db_person_messages.submission_id'
)
for r in db._sql(cmd).fetchall():
print r
def print_database():
db = catan.db.CatanDatabase(0)
cmd = (
'SELECT '
'db_person_bio.person_id, '
'db_person_bio.origin_node_id, '
'db_person_bio.name_family, '
'db_person_bio.name_given, '
'db_person_bio.age, '
'db_person_bio.sex, '
'db_person_messages.submission_id, '
'db_person_messages.origin_node_id, '
'db_person_messages.status_gps_latitude, '
'db_person_messages.status_gps_longitude, '
'db_person_messages.status_gps_accuracy, '
'db_person_messages.status, '
'db_person_messages.status_location, '
'db_submitter_info.timestamp '
'FROM db_person_bio '
'LEFT JOIN db_person_messages ON db_person_messages.person_id = db_person_bio.person_id '
'LEFT JOIN db_submitter_info ON db_submitter_info.submission_id = db_person_messages.submission_id'
)
for r in db._sql(cmd).fetchall():
print r
