def GetFilesByMetadataViaHTSQL(htquery): #return htquery rows = htdb.produce('/%s' % (htquery)) from htsql.core.fmt.emit import emit with htdb: text = ''.join(emit('x-htsql/json', rows)) return text return jsonify(results=rows) return htquery
def get_nested(): test = HTSQL("mysql://*****:*****@localhost/nestedsetspoc") #rows = test.produce("/evaluation{*,site{*,address,geoposition},evaluator}?" + criterion) rows = test.produce("/clinical_data{id, patient_sid :as sid,string_value :as value,attribute{attribute_value}}") # rows = test.produce("/attribute{attribute_value, clinical_data{patient_sid :as sid,string_value :as value}}") with test: text = ''.join(emit('x-htsql/json', rows)) #print text, rows return text
def get_factor(): #test = HTSQL("mysql://*****:*****@localhost/test") test = HTSQL("pgsql://*****:*****@localhost/test") rows = test.produce("/factor") with test: text = ''.join(emit('x-htsql/json', rows)) if mode == 'test': print text, rows return text
def action_htsql_query(self, query): """Executes a HTSQL Query""" try: from htsql.core.fmt.emit import emit from htsql.core.error import Error as HTSQL_Error from htsql import HTSQL except ImportError: return False, "HTSQL installation not found" # Resolve RDBMSs to their respective HTSQL engines engines = { 'postgres': 'pgsql', 'sqlite': 'sqlite', 'mysql': 'mysql', 'oracle': 'oracle', 'mssql': 'mssql', } if db_settings.password: password = "******" + urllib.quote_plus(db_settings.password) else: password = "" authority = '%s%s@%s:%s' % ( db_settings.username, password, db_settings.address, db_settings.port) uri = '%s://%s/%s' % ( engines[db_settings.rdbms], authority, db_settings.dbname) exts = [{ 'tweak.override': { 'unique_keys': 'product(sellable_id)', 'globals': { 'between($date, $start, $end)': '($date >= $start & $date <= $end)', 'trunc_hour($d)': 'datetime(year($d), month($d), day($d), hour($d))', 'trunc_day($d)': 'datetime(year($d), month($d), day($d))', 'trunc_month($d)': 'datetime(year($d), month($d), 01)', }}, }] store = HTSQL(uri, *exts) try: rows = store.produce(query) except HTSQL_Error as e: return False, str(e) with store: json = ''.join(emit('x-htsql/json', rows)) return True, json
def action_htsql_query(self, query): """Executes a HTSQL Query""" try: from htsql.core.fmt.emit import emit from htsql.core.error import Error as HTSQL_Error from htsql import HTSQL except ImportError: return False, "HTSQL installation not found" # Resolve RDBMSs to their respective HTSQL engines engines = {"postgres": "pgsql", "sqlite": "sqlite", "mysql": "mysql", "oracle": "oracle", "mssql": "mssql"} if db_settings.password: password = "******" + urllib.quote_plus(db_settings.password) else: password = "" authority = "%s%s@%s:%s" % (db_settings.username, password, db_settings.address, db_settings.port) uri = "%s://%s/%s" % (engines[db_settings.rdbms], authority, db_settings.dbname) # FIXME We should find a way to share this piece of code between Stoq # Portal and Stoq Server, without having to duplicate it every time exts = [ { "tweak.override": { "field-labels": { # Sellable fixes "sellable.price": ( "(if (on_sale_start_date <= now() &" "now() <= on_sale_end_date, on_sale_price," "base_price))" ), # Sale fixes # When the sale is not confirmed yet, the total_amount will be 0 # (just like the discount and surcharge). "sale.subtotal": "(total_amount + discount_value - surcharge_value)", "sale.discount_percentage": "(if(subtotal > 0," " (discount_value / subtotal), 0))", "sale.surcharge_percentage": "(if(subtotal > 0," " (surcharge_value / subtotal), 0))", "sale.cmv": "(sum(sale_item.total_cost))", "sale.returned_items_cost": "(sum(returned_sale_via_new_sale." " returned_sale_item.sale_item.total_cost))", "sale.profit_margin": "(if((cmv - returned_items_cost) > 0," " ((total_amount /" " (cmv - returned_items_cost)) - 1) * 100, 0))", # SaleItem Fixes "sale_item.total_price": "(price + ipi_info.v_ipi)", "sale_item.sale_discount": "(total_price * sale.discount_percentage)", "sale_item.sale_surcharge": "(total_price * sale.surcharge_percentage)", "sale_item.price_with_discount": ("(total_price - sale_discount + sale_surcharge)"), "sale_item.subtotal": "(total_price * quantity)", "sale_item.total_with_discount": "(price_with_discount * quantity)", "sale_item.total_cost": "(quantity * if(average_cost > 0," " average_cost," " sellable.cost))", # Other fixes "branch.main_address": "(person.address.filter(is_main_address))", "transfer_order.branch": "(source_branch)", "branch.description": ( "(if(is_null(person.company.fancy_name)," " person.name," " person.company.fancy_name))" ), }, "globals": { "identifier_str": ( "branch.acronym + head('0000', 5 - " "length(string(identifier)))" "+ string(identifier)" ), "trunc_month($d)": "datetime(year($d), month($d), 01)", "trunc_day($d)": "datetime(year($d), month($d), day($d))", "trunc_hour($d)": "datetime(year($d), month($d), day($d), hour($d))", "between($date, $start, $end)": "($date >= $start & $date <= $end)", # FIXME supplier name cannot be on field labels because # databases without nfe schema will cause an error when # this method is called "supplier_name": ( "(if(is_null(nfe_supplier.name)," " nfe_supplier.fancy_name," " nfe_supplier.name))" ), }, } } ] # FIXME: This is to support old stoq versions, which didn't have # a UNIQUE constraint on product.sellable_id column if stoq.stoq_version < (1, 10, 90): exts[0]["tweak.override"]["unique_keys"] = "product(sellable_id)" store = HTSQL(uri, *exts) try: rows = store.produce(query) except HTSQL_Error as e: return False, str(e) with store: json = "".join(emit("x-htsql/json", rows)) return True, json
def action_htsql_query(self, query): """Executes a HTSQL Query""" try: from htsql.core.fmt.emit import emit from htsql.core.error import Error as HTSQL_Error from htsql import HTSQL except ImportError: return False, "HTSQL installation not found" # Resolve RDBMSs to their respective HTSQL engines engines = { 'postgres': 'pgsql', 'sqlite': 'sqlite', 'mysql': 'mysql', 'oracle': 'oracle', 'mssql': 'mssql', } if db_settings.password: password = "******" + urllib.quote_plus(db_settings.password) else: password = "" authority = '%s%s@%s:%s' % (db_settings.username, password, db_settings.address, db_settings.port) uri = '%s://%s/%s' % (engines[db_settings.rdbms], authority, db_settings.dbname) # FIXME We should find a way to share this piece of code between Stoq # Portal and Stoq Server, without having to duplicate it every time exts = [{ 'tweak.override': { 'field-labels': { # Sellable fixes 'sellable.price': ('(if (on_sale_start_date <= now() &' 'now() <= on_sale_end_date, on_sale_price,' 'base_price))'), # Sale fixes # When the sale is not confirmed yet, the total_amount will be 0 # (just like the discount and surcharge). 'sale.subtotal': '(total_amount + discount_value - surcharge_value)', 'sale.discount_percentage': '(if(subtotal > 0,' ' (discount_value / subtotal), 0))', 'sale.surcharge_percentage': '(if(subtotal > 0,' ' (surcharge_value / subtotal), 0))', 'sale.cmv': '(sum(sale_item.total_cost))', 'sale.returned_items_cost': '(sum(returned_sale_via_new_sale.' ' returned_sale_item.sale_item.total_cost))', 'sale.profit_margin': '(if((cmv - returned_items_cost) > 0,' ' ((total_amount /' ' (cmv - returned_items_cost)) - 1) * 100, 0))', # SaleItem Fixes 'sale_item.total_price': '(price + ipi_info.v_ipi)', 'sale_item.sale_discount': '(total_price * sale.discount_percentage)', 'sale_item.sale_surcharge': '(total_price * sale.surcharge_percentage)', 'sale_item.price_with_discount': ('(total_price - sale_discount + sale_surcharge)'), 'sale_item.subtotal': '(total_price * quantity)', 'sale_item.total_with_discount': '(price_with_discount * quantity)', 'sale_item.total_cost': '(quantity * if(average_cost > 0,' ' average_cost,' ' sellable.cost))', # Other fixes 'branch.main_address': '(person.address.filter(is_main_address))', 'transfer_order.branch': '(source_branch)', 'branch.description': ('(if(is_null(person.company.fancy_name),' ' person.name,' ' person.company.fancy_name))'), }, 'globals': { 'identifier_str': ("branch.acronym + head('0000', 5 - " "length(string(identifier)))" "+ string(identifier)"), 'trunc_month($d)': 'datetime(year($d), month($d), 01)', 'trunc_day($d)': 'datetime(year($d), month($d), day($d))', 'trunc_hour($d)': 'datetime(year($d), month($d), day($d), hour($d))', 'between($date, $start, $end)': '($date >= $start & $date <= $end)', # FIXME supplier name cannot be on field labels because # databases without nfe schema will cause an error when # this method is called 'supplier_name': ('(if(is_null(nfe_supplier.name),' ' nfe_supplier.fancy_name,' ' nfe_supplier.name))'), } } }] # FIXME: This is to support old stoq versions, which didn't have # a UNIQUE constraint on product.sellable_id column if stoq.stoq_version < (1, 10, 90): exts[0]['tweak.override']['unique_keys'] = 'product(sellable_id)' store = HTSQL(uri, *exts) try: rows = store.produce(query) except HTSQL_Error as e: return False, str(e) with store: json = ''.join(emit('x-htsql/json', rows)) return True, json
def get_data_json(arg): rows = connection.produce(arg) with connection: return ''.join(emit('x-htsql/json', rows))
def get_htsql(): #if mode == 'test': # print criterion #test = HTSQL("mysql://*****:*****@localhost/test") test = HTSQL("pgsql://*****:*****@localhost/test") #rows = test.produce("/evaluation{*,site{*,address,geoposition},evaluator}?" + criterion) #rows = test.produce("/evaluation{*,site{*,address,geoposition},evaluator}") #test = HTSQL("mysql://*****:*****@localhost/test") rows = test.produce("/evaluation{*,site{*,address,geoposition},evaluator}") # rows = test.produce("/evaluation{*,site{*,address,geoposition},evaluator}") # http://127.0.0.1:8080/evaluation{comments,site{geoposition{id}?latitude=46&longitude%3E47},site{geoposition{accuracy}?latitude=46&longitude%3E47},evaluator{first_name}}?evaluator.first_name='you'/:sql # http://127.0.0.1:8080/site{site_name :as location, count(evaluation) :as 'N visits'} # http://127.0.0.1:8080/site{site_name :as location, count(evaluation) :as 'N visits'}?site_name!='Home' # http://127.0.0.1:8080/person{first_name + ' ' + last_name :as name, count(evaluation) :as 20'N visits'}?count(evaluation)>0 # /evaluation{comments,site{geoposition?accuracy=46.0&latitude%3C42},evaluator{first_name}}?evaluator.first_name='you' # # SELECT "evaluation"."comments", # "site"."?_1", # "site"."?_2", # "site"."id_1", # "site"."latitude", # "site"."longitude", # "site"."accuracy", # "site"."timestamp", # "person_2"."?", # "person_2"."first_name" # FROM "evaluation" # LEFT OUTER JOIN "person" AS "person_1" # ON ("evaluation"."evaluator_id" = "person_1"."id") # LEFT OUTER JOIN (SELECT TRUE AS "?_1", # "geoposition"."?" AS "?_2", # "geoposition"."id" AS "id_1", # "geoposition"."latitude", # "geoposition"."longitude", # "geoposition"."accuracy", # "geoposition"."timestamp", # "site"."id" AS "id_2" # FROM "site" # LEFT OUTER JOIN (SELECT TRUE AS "?", # "geoposition"."id", # "geoposition"."latitude", # "geoposition"."longitude", # "geoposition"."accuracy", # "geoposition"."timestamp" # FROM "geoposition" # WHERE ("geoposition"."accuracy" = 46.0::FLOAT8) # AND ("geoposition"."latitude" < 42.0::FLOAT8)) AS "geoposition" # ON ("site"."geoposition_id" = "geoposition"."id")) AS "site" # ON ("evaluation"."site_id" = "site"."id_2") # LEFT OUTER JOIN (SELECT TRUE AS "?", # "person"."first_name", # "person"."id" # FROM "person") AS "person_2" # ON ("evaluation"."evaluator_id" = "person_2"."id") # WHERE ("person_1"."first_name" = 'you') # ORDER BY "evaluation"."id" ASC with test: text = ''.join(emit('x-htsql/json', rows)) if mode == 'test': print text, rows return text