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 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 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 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
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()
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 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()
#
# 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_slave_app():
from htsql import HTSQL
master = weakref.ref(context.app)
slave = HTSQL(None, {'tweak.meta.slave': {'master': master}})
return slave
" %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 connect(db_string):
return HTSQL(db_string)
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": "*"}})
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 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
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',
