def sql_create_table(user_table):
model_class = opengis.create_model(user_table)
style = no_style()
tables = connection.introspection.table_names()
seen_models = connection.introspection.installed_models(tables)
pending_references = {}
sql, references = connection.creation.sql_create_model(model_class, style)
for refto, refs in references.items():
pending_references.setdefault(refto, []).extend(refs)
if refto in seen_models:
sql.extend(connection.creation.sql_for_pending_references(refto, style, pending_references))
sql.extend(connection.creation.sql_for_pending_references(model_class, style, pending_references))
cursor = connection.cursor()
for statement in sql:
cursor.execute(statement)
transaction.commit_unless_managed()
custom_sql = custom_sql_for_model(model_class, style)
if custom_sql:
try:
for sql in custom_sql:
cursor.execute(sql)
except Exception, e:
transaction.rollback_unless_managed()
else:
transaction.commit_unless_managed()
def view_user_table(request, username, table_name):
account = Account.objects.get(user=request.user)
user_table = get_object_or_404(UserTable, account=account, table_name=table_name)
user_table.columns = UserTableColumn.objects.filter(table=user_table).order_by('created')
user_table.tags = UserTableTag.objects.filter(table=user_table)
table_model = opengis.create_model(user_table)
table_data = table_model.objects.all()
return render_to_response(settings.OPENGIS_TEMPLATE_PREFIX + "table_view.html", {'account':account, 'user_table':user_table, 'table_data':table_data}, context_instance=RequestContext(request))
def _extract_query_result(starter_table, query_result, result_columns):
result = list()
for datum in query_result:
result_row = list()
for result_column in result_columns:
if result_column['column_hierarchy']:
hierarchy_list = result_column['column_hierarchy'].split(".")
if type(datum).__name__ != 'dict':
try:
attr = datum
for hierarchy in hierarchy_list: attr = getattr(attr, hierarchy)
result_row.append(getattr(attr, result_column['physical_name']))
except:
result_row.append("")
else: # query_result returns as a list of dict (cause by using group by)
growing_hierarchy = ""
hierarchy_user_table = starter_table
hierarchy_data = None
for index, hierarchy in enumerate(hierarchy_list):
try:
value = hierarchy_data.id
except:
value = datum[hierarchy]
table_column = utilities.list_find(lambda table_column: table_column.physical_column_name==hierarchy, UserTableColumn.objects.filter(table=hierarchy_user_table))
related_table = table_column.related_table
hierarchy_user_table = UserTable.objects.get(pk=related_table)
hierarchy_model_object = opengis.create_model(hierarchy_user_table)
hierarchy_data = hierarchy_model_object.objects.get(pk=value)
result_row.append(getattr(hierarchy_data, result_column['physical_name']))
else:
try:
result_row.append(getattr(datum, result_column['physical_name']))
except:
try:
result_row.append(datum[result_column['physical_name']])
except:
result_row.append("")
if result_row: result.append(result_row)
return result
def api_table_empty(request):
if request.method == 'POST':
user_table = UserTable.objects.get(pk=request.POST.get('table_id'))
user_table_columns = UserTableColumn.objects.filter(table=user_table)
table_model = opengis.create_model(user_table)
table_model.objects.all().delete()
return api.APIResponse(api.API_RESPONSE_SUCCESS)
else:
return api.APIResponse(api.API_RESPONSE_POSTONLY)
def api_table_save_row(request):
# TODO: Check permissions.
if request.method == 'POST':
account = Account.objects.get(user=request.user)
table_id = int(request.POST.get('table_id'))
row = request.POST.getlist('row[]')
row_id = int(request.POST.get('row_id', 0))
op = request.POST.get('op')
if not table_id: return api.APIResponse(api.API_RESPONSE_ERROR, response_meta={'error':'required_table_id'})
user_table = UserTable.objects.filter(account=account, pk=table_id);
if not user_table.count():
return api.APIResponse(api.API_RESPONSE_ERROR, response_meta={'error':'missing_table_id'})
user_table = user_table[0]
table_columns = UserTableColumn.objects.filter(table=user_table)
target_model = opengis.create_model(user_table)
if row_id:
model_obj = target_model.objects.get(pk=row_id)
else:
model_obj = target_model()
print model_obj.id
if op == 'delete':
model_obj.delete()
return api.APIResponse(api.API_RESPONSE_SUCCESS)
error = []
for index, column in enumerate(row):
column_name, column_data = column.split('=')
table_column = utilities.list_find(lambda table_column: table_column.column_name == column_name, table_columns)
physical_column_name = table_column.physical_column_name
print column_data
column_data = utilities.convert_string_to_data_with_format(column_data, table_column)
if not column_data['error']:
setattr(model_obj, physical_column_name, column_data['value'])
else:
error += [str(index + 1) + '. ' + column_data['error']]
if not len(error):
model_obj.save()
return api.APIResponse(api.API_RESPONSE_SUCCESS, result=model_obj.id)
else:
return api.APIResponse(api.API_RESPONSE_ERROR, result='\n'.join(error))
else:
return api.APIResponse(api.API_RESPONSE_POSTONLY)
def get_user_table_json(request, table_name): account = Account.objects.get(user=request.user) user_table = get_object_or_404(UserTable, table_name=table_name) user_table.columns = UserTableColumn.objects.filter(table=user_table) model_class = opengis.create_model(user_table) data = model_class.objects.all() result = list() for datum in model_class.objects.all(): row_dict = dict() for index, column in enumerate(user_table.columns): row_dict[column.column_name] = getattr(datum, column.column_name) result.append(row_dict) return HttpResponse(simplejson.dumps(result), content_type='text/plain; charset=UTF-8')
def generate_relate_table(column_info): html = '' if column_info.data_type != sql.TYPE_USER_TABLE: return '' relate_table = UserTable.objects.get(pk=column_info.related_table) display_column = relate_table.display_column relate_table = opengis.create_model(relate_table) html += '<select id="user-table-' + column_info.column_name + '">' html += '<option value="">- none -</option>' for row in relate_table.objects.all(): row_id = str(getattr(row, 'id')) row_val = str(getattr(row, display_column)) html += '<option value="' + row_val + ' [id:' + row_id + ']">'+ row_val + ' [id:' + row_id + ']</option>' html += '</select>' return html
def convert_string_to_data_with_format(column_data, table_column):
data = {'value': column_data, 'error': ''}
if column_data.strip():
data_type = table_column.data_type
try:
if data_type == sql.TYPE_CHARACTER: data['value'] = str(column_data)
elif data_type == sql.TYPE_NUMBER: data['value'] = float(column_data)
elif data_type == sql.TYPE_DATETIME: data['value'] = datetime.strptime(column_data, '%Y-%m-%d %H:%M:%S')
elif data_type == sql.TYPE_DATE: data['value'] = datetime.strptime(column_data, '%Y-%m-%d').date()
elif data_type == sql.TYPE_TIME: data['value'] = datetime.strptime(column_data, '%H:%M:%S').time()
elif data_type == sql.TYPE_REGION: data['value'] = GEOSGeometry(column_data)
elif data_type == sql.TYPE_LOCATION: data['value'] = GEOSGeometry(column_data)
elif data_type == sql.TYPE_USER_TABLE or data_type == sql.TYPE_BUILT_IN_TABLE:
related_user_table = UserTable.objects.get(pk=table_column.related_table)
related_model = opengis.create_model(related_user_table)
related_model_row_id = re.search(r'\[id\:(\d+)\]', column_data)
column_data = related_model_row_id.group(1)
data['value'] = related_model.objects.get(pk=column_data)
except ValueError as error:
data['error'] = table_column.column_name + ': ' + error.message
else:
data['value'] = None
return data
def execute_query(user_query, parameters, result_limit=None):
# column_manager = opengis.TableColumnManager(user_query.starter_table)
display_columns = UserQueryDisplayColumn.objects.filter(query=user_query)
# Generate 'columns' JSON
result_columns = list()
for display_column in display_columns:
if display_column.is_aggregate:
column_info = {'id':display_column.column_id.id,'name':display_column.column_id.id,'type':sql.TYPE_NUMBER,'physical_name':display_column.column_id.id,'related_table':''}
else:
column_info = _to_column_dict(display_column.column)
if display_column.display_name: column_info['name'] = display_column.display_name
column_info['column_hierarchy'] = display_column.column_hierarchy
result_columns.append(column_info)
# Create Starter Model
starter_model = opengis.create_model(user_query.starter_table)
data_objects = starter_model.objects.all()
# Virtual Columns -- WILL DO
# Figure out how to store virtual column login in database
# Entry.objects.extra(select={'is_recent': "pub_date > '2006-01-01'"})
# Group By
group_by_columns = UserQueryGroupByColumn.objects.filter(query=user_query)
for group_by in group_by_columns:
data_objects = data_objects.values(group_by.column.physical_column_name)
# Aggregate Columns
aggregate_columns = UserQueryAggregateColumn.objects.filter(query=user_query)
if group_by_columns: # If using 'values', we must use annotate, instead of aggregate
for aggregate_column in aggregate_columns:
column_info = _to_column_dict(aggregate_column.column)
data_objects = data_objects.annotate(query.sql_aggregate(aggregate_column, column_info))
else:
for aggregate_column in aggregate_columns:
column_info = _to_column_dict(aggregate_column.column)
data_objects = data_objects.aggregate(query.sql_aggregate(aggregate_column, column_info))
# Filter
for filter in UserQueryFilter.objects.filter(query=user_query):
if filter.is_variable:
filter.filter_value = parameters.get(filter.filter_value)
if not filter.filter_value: continue
column_info = _to_column_dict(filter.column)
data_objects = query.sql_filter(filter, column_info, data_objects)
# Order by
order_by_columns = UserQueryOrderByColumn.objects.filter(query=user_query).order_by('order_priority') # Less has more priority
order_fields = list()
for order_by_column in order_by_columns:
if order_by_column.column_hierarchy:
column_hierarchy = order_by_column.column_hierarchy.replace(".", "__") + "__"
else:
column_hierarchy = ""
order_fields.append('-' if order_by_column.is_desc else '' + column_hierarchy + order_by_column.column_name)
if order_fields: data_objects = data_objects.order_by(*order_fields)
# Distinct
if user_query.is_distinct:
args = list()
for result_column in result_columns:
if result_column['column_hierarchy']:
column_hierarchy = result_column['column_hierarchy'].replace(".", "__") + "__"
else:
column_hierarchy = ""
args.append(column_hierarchy + result_column['physical_name'])
data_objects = data_objects.values(*args)
data_objects = data_objects.distinct()
# Dump result in a list of list
result = list()
print data_objects
if aggregate_columns and not group_by_columns: # using aggregate without grouping by will have result as a dict
result_row = list()
for result_column in result_columns:
try:
result_row.append(data_objects[result_column['physical_name']])
except KeyError:
result_row.append("")
result.append(result_row)
elif user_query.is_distinct: # Using distinct, result will be a list of dict that has a key like 'link1__link2__column1'
for datum in data_objects:
result_row = list()
for result_column in result_columns:
if result_column['column_hierarchy']:
column_hierarchy = result_column['column_hierarchy'].replace(".", "__") + "__"
else:
column_hierarchy = ""
result_row.append(datum[column_hierarchy + result_column['physical_name']])
if result_row: result.append(result_row)
else:
result = _extract_query_result(user_query.starter_table, data_objects, result_columns)
# Result limitation -- either define it in user query table or 'limit' parameter in request URL
if user_query.result_limit or result_limit:
limit = user_query.result_limit
if result_limit: limit = int(result_limit)
result = result[0:limit]
return {'columns':result_columns,'values':result}
def import_table(user_table, account, request):
temp_csv_file = settings.TEMP_CSV_PATH + '/temp_' + str(account.user.id) + "_" + str(long(round(time.time()))) + '.csv'
destination = open(temp_csv_file, 'wb')
for chunk in request.FILES['file'].chunks(): destination.write(chunk)
destination.close()
destination = open(temp_csv_file, 'rb')
csv_reader = csv.reader(destination)
table_columns = UserTableColumn.objects.filter(table=user_table)
target_model = opengis.create_model(user_table)
target_model.objects.all().delete()
column_mapping = list()
for row in csv_reader:
if not column_mapping: # csv.reader object is unsubscriptable
# Map logical column name used in CSV to physical database column name
for index, column_name in enumerate(row):
(parent_column, separator, child_column) = column_name.partition("---")
column_info = dict()
table_column = utilities.list_find(lambda table_column: table_column.column_name == parent_column, table_columns)
if child_column:
column_info['physical_column_name'] = table_column.physical_column_name
column_info['related_table'] = table_column.related_table
related_user_table = UserTable.objects.get(pk=table_column.related_table)
related_table_columns = UserTableColumn.objects.filter(table=related_user_table)
related_column = utilities.list_find(lambda related_column: related_column.column_name == child_column, related_table_columns)
column_info['related_column'] = related_column.physical_column_name
else:
column_info['physical_column_name'] = table_column.physical_column_name
column_mapping.append(column_info)
else:
model_obj = target_model()
for index, column_data in enumerate(row):
column_info = column_mapping[index]
if column_info.get('related_table'):
related_user_table = UserTable.objects.get(pk=column_info['related_table'])
related_model = opengis.create_model(related_user_table)
related_model_object = related_model.objects.get(**{str(column_info['related_column']):column_data})
setattr(model_obj, column_info['physical_column_name'], related_model_object)
else:
setattr(model_obj, column_info['physical_column_name'], column_data)
model_obj.save()
destination.close()
import os
os.remove(temp_csv_file)
from django.contrib.gis import admin import opengis from opengis.models import * admin.site.register(Account) admin.site.register(UserTable) admin.site.register(UserTableColumn) admin.site.register(UserQuery) admin.site.register(UserQueryDisplayColumn) admin.site.register(UserQueryFilter) admin.site.register(UserQueryAggregateColumn) admin.site.register(UserQueryGroupByColumn) # Register user tables user_tables = UserTable.objects.all() for user_table in user_tables: model_class = opengis.create_model(user_table) admin.site.register(model_class, admin.GeoModelAdmin)
