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 query(request, db, q):
global DBS
if not DBS.has_key(db):
abort(404)
filters = request.url.replace(request.base_url, '', 1)
htsql = HTSQL(DBS[db]['connection'])
if filters and ('?' in q):
filters = '&%s' % filters.strip()[1:]
q = '/%s%s' % (q, filters or '')
data = htsql.produce(str(q))
return (data, data.records[0].__fields__, q)
def query(request, db, q):
global DBS
if not DBS.has_key(db):
abort(404)
filters = request.url.replace(request.base_url, '', 1)
htsql = HTSQL(DBS[db]['connection'])
if filters and ('?' in q):
filters = '&%s' % filters.strip()[1:]
q = '/%s%s' % (q, filters or '')
data = htsql.produce(str(q))
return (data, data.records[0].__fields__, q)
def query(request, db, q):
global DB_DIR
filters = request.url.replace(request.base_url, '', 1)
dbs = loadsqlites(DB_DIR)
if not dbs.has_key(db):
return '[!] missing db'
htsql = HTSQL(dbs[db])
if filters and ('?' in q):
filters = '&%s' % filters.strip()[1:]
q = '/%s%s' % (q, filters or '')
data = htsql.produce(str(q))
return (data, data.records[0].__fields__, q)
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 check(self):
filename = self.input.sql_as_filename
if filename is not None:
stream = open(self.input.sql)
source = stream.read()
stream.close()
else:
source = self.input.sql
from htsql import HTSQL
from htsql.core.error import Error
from htsql.core.connect import connect
from htsql.core.split_sql import split_sql
try:
app = HTSQL(self.input.connect)
except Exception:
self.ui.literal(traceback.format_exc())
self.ctl.failed("exception occured while"
" initializing an HTSQL application")
return
with app:
try:
statements = list(split_sql(source))
except ValueError, exc:
self.ctl.failed("cannot parse SQL: %s" % exc)
return
try:
connection = connect(with_autocommit=self.input.autocommit)
cursor = connection.cursor()
except Error, exc:
self.ui.literal(str(exc))
self.ctl.failed("failed to connect to the database")
return
def index(request):
htsql = HTSQL('pgsql://%s:%s@%s/%s' % (settings.DATABASES['default']['USER'],
settings.DATABASES['default']['PASSWORD'],
settings.DATABASES['default']['HOST'],
settings.DATABASES['default']['NAME']))
results = []
columns = []
query = ''
if request.method == 'POST':
query = request.POST['query']
results = htsql.produce(query)
for prop in dir( results[0] ):
if prop.startswith( '__' ) : continue
if prop in ['count', 'index', 'make', 'bit_length', 'conjugate', 'denominator', 'imag', 'numerator'] : continue
columns.append(prop)
return render_to_response('index.html',{'results': results, 'query': query, 'columns': columns}, context_instance = RequestContext(request))
def main(cls):
if len(sys.argv) != 4:
return cls.usage()
db, host, port = sys.argv[1:]
port = int(port)
htsql = HTSQL(db)
app = cls(htsql)
print("Starting the HTRAF demo on http://%s:%s/" \
% (host, port), file=sys.stderr)
httpd = wsgiref.simple_server.make_server(host, port, app)
httpd.serve_forever()
def run(self):
# Determine HTSQL initialization parameters.
parameters = [self.db]
# Ask for the database password if necessary.
if self.password:
password = getpass.getpass()
parameters.append({'htsql': {'password': password}})
# Load addon configuration.
parameters.extend(self.extensions)
if self.config is not None:
stream = open(self.config, 'rb')
loader = ConfigYAMLLoader(stream)
try:
config_extension = loader.load()
except yaml.YAMLError as exc:
raise ScriptError("failed to load application configuration:"
" %s" % exc)
if config_extension is not None:
parameters.append(config_extension)
# Load the default configuration from the RC file.
path = os.path.abspath(os.path.expanduser(self.default_path))
if os.path.exists(path):
stream = open(path, 'rb')
loader = ConfigYAMLLoader(stream)
try:
default_extension = loader.load()
except yaml.YAMLError as exc:
raise ScriptError("failed to load default configuration: %s" %
exc)
if default_extension is not None:
parameters.append(default_extension)
# Create the HTSQL application.
from htsql import HTSQL
try:
app = HTSQL(*parameters)
except ImportError as exc:
raise ScriptError("failed to construct application: %s" % exc)
# Run the routine-specific code.
self.start(app)
def check(self):
from htsql import HTSQL
self.ctl.state['htsql'] = None
try:
self.ctl.state['htsql'] = HTSQL(self.input.db,
self.input.extensions)
except Exception:
self.ui.literal(traceback.format_exc())
self.ctl.failed("exception occured while"
" initializing the HTSQL application")
return
# Record the configuration.
if self.input.save is not None:
self.ctl.state[self.input.save] = SavedDB(self.input.db,
self.input.extensions)
self.ctl.passed()
return
def check(self):
if not isinstance(self.ctl.state.get(self.input.load), SavedDB):
self.ctl.failed("unknown configuration %r" % self.input.load)
return
configuration = self.ctl.state[self.input.load]
db = configuration.db
extensions = configuration.extensions + (self.input.extensions, )
from htsql import HTSQL
self.ctl.state['htsql'] = None
try:
self.ctl.state['htsql'] = HTSQL(db, *extensions)
except Exception:
self.ui.literal(traceback.format_exc())
self.ctl.failed("exception occured while"
" initializing the HTSQL application")
return
# Record the configuration.
if self.input.save is not None:
self.ctl.state[self.input.save] = SavedDB(db, *extensions)
self.ctl.passed()
def datastore_search_htsql(context, data_dict):
'''Execute HTSQL-Queries on the datastore.
:param htsql: htsql statement
:type htsql: string
:returns: a dictionary containing the search results.
keys: fields: columns for results
records: results from the query
:rtype: dictionary
'''
query = _get_or_bust(data_dict, 'htsql')
query = str(query)
print query
uri = pylons.config['ckan.datastore.read_url']
engine = db._get_engine(None, {'connection_url': uri})
htsql = HTSQL(None, {
'tweak.sqlalchemy': {
'engine': engine
},
'tweak.timeout': {
'timeout': 1000
}
})
with htsql:
cmd = UniversalCmd(query)
plan = analyze(cmd)
sql = plan.statement.sql
data_dict['sql'] = sql
action = p.toolkit.get_action('datastore_search_sql')
result = action(context, data_dict)
result['htsql'] = query
return result
class AutoVivification(dict):
"""Implementation of perl's autovivification feature."""
def __getitem__(self, item):
try:
return dict.__getitem__(self, item)
except KeyError:
value = self[item] = type(self)()
return value
from htsql import HTSQL
import pprint
htsql = HTSQL("mysql://*****:*****@localhost/hesk")
rows = htsql.produce("/hesk_tickets{custom4,custom1, name, custom3, subject, priority}")
# +-----------------+---------------------+---------------------------+-------------+--------------------------------+----------+
# | custom4 | custom1 | name | custom3 | subject | priority |
# +-----------------+---------------------+---------------------------+-------------+--------------------------------+----------+
partitions = AutoVivification()
for row in rows:
partitions[row.custom4][row.custom1][row.name][row.custom3][row.subject] = row.priority
tipos = []
for tipo in partitions:
from htsql import HTSQL
import sys, decimal, datetime
db = __pbbt__['demo'].db
htsql = HTSQL(db)
uri = "/school{code, count(program), count(department)}"
print("URI:", uri)
for row in htsql.produce(uri):
print(row)
print()
uri = "/school{name, count(program)}?code=$school_code"
school_code = "bus"
print("URI:", uri)
print("$school_code: %r" % school_code)
for row in htsql.produce(uri, school_code=school_code):
print("%s: %s" % row)
print()
uri = "/school{name, num_prog:=count(program)}" \
"?num_prog>=$min_prog&num_prog<=$max_prog"
min_prog = 6
max_prog = 8
print("URI:", uri)
print("$min_prog: %r" % min_prog)
print("$max_prog: %r" % max_prog)
for row in htsql.produce(uri, min_prog=min_prog, max_prog=max_prog):
print("%s: %s" % (row.name, row.num_prog))
print()
from htsql import HTSQL
from db_conf import DB_URL
class AutoVivification(dict):
"""Implementation of perl's autovivification feature."""
def __getitem__(self, item):
try:
return dict.__getitem__(self, item)
except KeyError:
value = self[item] = type(self)()
return value
htsql = HTSQL(DB_URL)
rows = htsql.produce("/protocolos_gasto{proyecto{status, linea_de_investigacion{linea_investigacion_desc :as desc} :as linea, titulo}, descripcion, monto}")
# | proyecto | | |
# +---------------+----------------------------------+----------------------------------+ | |
# | | linea | | | |
# | +----------------------------------+ | | |
# | status | linea_investigacion_desc | titulo | descripcion | monto |
partitions = AutoVivification()
status = linea = titulo = concepto = ''
for row in rows:
if row.proyecto.linea:
import json
from htsql import HTSQL
from db_conf import DB_URL
htsql = HTSQL(DB_URL)
rows = htsql.produce("/helpdesk_ticket{ tipo.tipo, categoria.nombre, estado.estado, fecha_creacion}")
class AutoVivification(dict):
"""Implementation of perl's autovivification feature."""
def __getitem__(self, item):
try:
return dict.__getitem__(self, item)
except KeyError:
value = self[item] = type(self)()
return value
partitions = AutoVivification()
aguas = {}
for row in rows:
if not row.nombre:
nombre = 'sin categorizar'
else:
nombre = row.nombre
if not row.tipo:
tipo = 'default'
else:
tipo = row.tipo
#
# Copyright (c) 2006-2013, Prometheus Research, LLC
#
from htsql import HTSQL
from django.conf import settings
CONFIG = getattr(settings, 'HTSQL_CONFIG', {})
DEFAULT_CONFIG = {
'tweak.django': {},
}
instance = HTSQL(None, CONFIG, DEFAULT_CONFIG)
produce = instance.produce
cursorclass=MySQLdb.cursors.DictCursor)
except Exception as e:
print "Could not connect to SQL Server"
print repr(e)
sys.exit(2)
try:
c = db.cursor()
except Exception as e:
print "Could not acquire a DB cursor"
print repr(e)
sys.exit(3)
try:
htdb = HTSQL('mysql://%s:%s@localhost/%s' %
(db_credentials['db_username'], db_credentials['db_password'],
db_credentials['db_name']))
except Exception as e:
print "Could not connect to SQL Server using HTSQL"
print repr(e)
sys.exit(2)
@api.route("/")
def hello():
return "Hello WOrld!1!!"
@api.route("/GetFilesByMetadataViaHTSQL/<string:htquery>")
def GetFilesByMetadataViaHTSQL(htquery):
#return htquery
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
# http://htsql.org/doc/embed.html
# run: pip install HTSQL
from htsql import HTSQL
htsql = HTSQL("sqlite:vsee/db.sqlite3")
# get info for a specific company
company = "iWantTutor"
rows = htsql.produce("/companies_company{*}?name='" + company + "'")
for row in rows:
print row.name
print row.desc_cb
print row.desc_al
print row.cb_url
print row.al_url
print row.logo_url
print row.company_url
print row.market
print "------------------------------------------------------"
# get list of related companies sorted by similarity
company = "iWantTutor"
rows = htsql.produce("/companies_distance{score-, c1_id.name, c2_id.name}.filter(c1_id.name='" + company + "'|c2_id.name='" + company + "').limit(10)")
# list of related companies
similar = []
# scores
scores = []
for row in rows:
if not row[1] == company:
import os, yaml
from htsql import HTSQL
from wsgiref.simple_server import make_server
# create HTSQL application
config = yaml.load(open("config.yaml").read())
dburi = os.environ['DATABASE_URL'].replace('postgres', 'pgsql')
app = HTSQL(dburi, config)
# start webserver
port = int(os.environ['PORT'])
srv = make_server('0.0.0.0', port, app)
srv.serve_forever()
from datetime import datetime
from psycopg2._range import DateTimeRange
flask.ext.restful.representations.json.settings["cls"] = TolerantJSONEncoder
sqla_conn_string = os.getenv('SQLA_CONN')
if not sqla_conn_string:
print(
"Environment variable SQLA_CONN is empty; running against local `cfdm_test`"
)
sqla_conn_string = 'postgresql://:@/cfdm_test'
engine = sa.create_engine(sqla_conn_string)
conn = engine.connect()
htsql_conn_string = sqla_conn_string.replace('postgresql', 'pgsql')
htsql_conn = HTSQL(htsql_conn_string)
app = Flask(__name__)
api = restful.Api(app)
cmte_decoder = {
'P': 'Presidential',
'H': 'House',
'S': 'Senate',
'C': 'Communication Cost',
'D': 'Delegate Committee',
'E': 'Electioneering Communication',
'I': 'Independent Expenditor (Person or Group)',
'N': 'PAC - Nonqualified',
'O': 'Independent Expenditure-Only (Super PACs)',
'Q': 'PAC - Qualified',
from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy
from flask_cors import CORS, cross_origin
from sqlalchemy import create_engine
from htsql import HTSQL
# Define Flask app
app = Flask(__name__)
app.config.from_object('config')
db = SQLAlchemy(app)
# import models as a subclass
import models
# misc config objects
URI = 'mysql://*****:*****@127.0.0.1/ns_large'
engine = create_engine(URI)
connection = HTSQL(URI)
# CORS request headers
cors = CORS(app, headers="X-Requested-With, Content-Type", resources={r"/*": {"origins": "*"}})
#!/usr/bin/env python
from htsql import HTSQL
import time
db = HTSQL('sqlite:db.sqlite3')
queries = [
"""/snp.filter(daf_afr < 0.1 & daf_sea > 0.9).sort(daf_sea-).limit(100)""",
"""/snp.filter(chromosome = 'MAL1' & position > 100000 & position < 200000).sort(position).limit(100)""",
"""/snp.filter(genetext ~ 'pfmdr').sort(chromosome, position).limit(100)"""
]
for query in queries:
print query
for _ in range(5): # repeat each query 5 times
time.sleep(1) # sleep for 1 second between queries
before = time.clock()
result = db.produce(query)
count = sum(1 for _ in result)
after = time.clock()
print '%s rows in %ss' % (count, after - before)
" %s" % exc)
if config_extension is not None:
parameters.append(config_extension)
# Load the default configuration from the RC file.
path = os.path.abspath(os.path.expanduser(self.default_path))
if os.path.exists(path):
stream = open(path, 'rb')
loader = ConfigYAMLLoader(stream)
try:
default_extension = loader.load()
except yaml.YAMLError, exc:
raise ScriptError("failed to load default configuration: %s" %
exc)
if default_extension is not None:
parameters.append(default_extension)
# Create the HTSQL application.
from htsql import HTSQL
try:
app = HTSQL(*parameters)
except ImportError, exc:
raise ScriptError("failed to construct application: %s" % exc)
# Run the routine-specific code.
self.start(app)
def start(self, app):
# Override in subclasses.
raise NotImplementedError()
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 populate(self):
# We can't store the session as there is no way to pickle it
session = HTSQL(self._connexion)
self._data = session.produce(self.query, **self.parameters)
def htsql_conn():
if not hasattr(g, '_htsql_conn'):
htsql_conn_string = sqla_conn_string().replace('postgresql', 'pgsql')
g._htsql_conn = HTSQL(htsql_conn_string)
return g._htsql_conn
def get_slave_app():
from htsql import HTSQL
master = weakref.ref(context.app)
slave = HTSQL(None, {'tweak.meta.slave': {'master': master}})
return slave
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 connect(db_string):
return HTSQL(db_string)