def main():
print "So it begins..."
target_db_uri = Environment.instance().config.SQLALCHEMY_DATABASE_URI
target_connection = psycopg2.connect(target_db_uri)
target_cursor = target_connection.cursor()
db_uri = Environment.instance().config.SQLALCHEMY_BINDS['stats']
connection = psycopg2.connect(db_uri)
cursor = connection.cursor()
try:
for table_name, columns in TABLES:
print "Copying ", table_name
# first, we need to lock the source table (ideally)
# the problem with this is that we would need to modify permissions for production to allow
# the lock for the user configured in SQLALCHEMY_DATABASE_URI
# cursor.execute("LOCK TABLE %s" % table_name)
# we need to assert the table is empty
print "Counting ", table_name
target_cursor.execute("select count(*) from %s" % table_name)
count, = target_cursor.fetchone()
assert count == 0, "Table %s "
col_string = ','.join(columns)
str_string = ','.join(["%s"] * len(columns))
target_query = StringIO()
target_query.write('insert into %s(%s) values ' %
(table_name, col_string))
print "Reading ", table_name
cursor.execute('select %s from %s' % (col_string, table_name))
for rec in cursor:
target_query.write(
"(%s)," % target_cursor.mogrify(str_string, tuple(rec)))
print "Writing ", table_name
target_cursor.execute(target_query.getvalue()[:-1])
# now we need to reset the sequence associated with the id for this table
target_cursor.execute("select max(id) + 1 from %s" % table_name)
nextone, = target_cursor.fetchone()
print "Updating sequence for ", table_name
target_cursor.execute("SELECT setval('%s_id_seq', %s, false)" %
(table_name, nextone))
print "Done ", table_name
except Exception as e:
print "Error ", e
target_connection.rollback()
connection.rollback()
else:
print "Good, well done, excellent."
target_connection.commit()
connection.commit()
finally:
connection.close()
target_connection.close()
def main():
print "So it begins..."
target_db_uri = Environment.instance().config.SQLALCHEMY_DATABASE_URI
target_connection = psycopg2.connect(target_db_uri)
target_cursor = target_connection.cursor()
db_uri = Environment.instance().config.SQLALCHEMY_BINDS['stats']
connection = psycopg2.connect(db_uri)
cursor = connection.cursor()
try:
for table_name, columns in TABLES:
print "Copying ", table_name
# first, we need to lock the source table (ideally)
# the problem with this is that we would need to modify permissions for production to allow
# the lock for the user configured in SQLALCHEMY_DATABASE_URI
# cursor.execute("LOCK TABLE %s" % table_name)
# we need to assert the table is empty
print "Counting ", table_name
target_cursor.execute("select count(*) from %s" % table_name)
count, = target_cursor.fetchone()
assert count == 0, "Table %s "
col_string = ','.join(columns)
str_string = ','.join(["%s"] * len(columns))
target_query = StringIO()
target_query.write('insert into %s(%s) values ' % (table_name, col_string))
print "Reading ", table_name
cursor.execute('select %s from %s' % (col_string, table_name))
for rec in cursor:
target_query.write("(%s)," % target_cursor.mogrify(str_string, tuple(rec)))
print "Writing ", table_name
target_cursor.execute(target_query.getvalue()[:-1])
# now we need to reset the sequence associated with the id for this table
target_cursor.execute("select max(id) + 1 from %s" % table_name)
nextone, = target_cursor.fetchone()
print "Updating sequence for ", table_name
target_cursor.execute("SELECT setval('%s_id_seq', %s, false)" % (table_name, nextone))
print "Done ", table_name
except Exception as e:
print "Error ", e
target_connection.rollback()
connection.rollback()
else:
print "Good, well done, excellent."
target_connection.commit()
connection.commit()
finally:
connection.close()
target_connection.close()
def get_campaigns(account_id=None,
past=True,
in_flight=True,
scheduled=True,
utctoday=None):
from splice.environment import Environment
env = Environment.instance()
query = env.db.session.query(Campaign)
if account_id is not None:
query = query.filter(Campaign.account_id == account_id)
if utctoday is None:
utctoday = datetime.utcnow().date()
rows = query.order_by(Campaign.id.desc()).all()
campaigns = []
for row in rows:
ret = row_to_dict(row)
countries = []
for country in row.countries:
countries.append(country.country_code)
ret['countries'] = countries
# filter based on start and end dates unless an account ID is specified
if ((past and row.end_date.date() <= utctoday) or
(in_flight
and row.end_date.date() >= utctoday >= row.start_date.date())
or (scheduled and row.start_date.date() >= utctoday)):
campaigns.append(ret)
return campaigns
def get_all_distributions(limit=100):
"""
Obtain distributions, partitioned by channels with up to ``limit`` results
for each channel
"""
from splice.environment import Environment
env = Environment.instance()
dist_cte = (env.db.session.query(
Distribution.channel_id, Distribution.url, Distribution.created_at,
func.row_number().over(
partition_by=Distribution.channel_id,
order_by=Distribution.created_at.desc()).label('row_num'))).cte()
stmt = (env.db.session.query(
dist_cte.c.channel_id, dist_cte.c.url,
dist_cte.c.created_at).filter(dist_cte.c.row_num <= limit).order_by(
dist_cte.c.created_at.desc()))
rows = stmt.all()
channels = {}
for row in rows:
c_dists = channels.setdefault(row.channel_id, [])
c_dists.append({'url': row.url, 'created_at': row.created_at})
return channels
def tile_exists(target_url, bg_color, title, type, image_uri, enhanced_image_uri, locale, conn=None, *args, **kwargs):
"""
Return the id of a tile having the data provided
"""
from splice.environment import Environment
env = Environment.instance()
if conn is not None:
sm = sessionmaker(bind=conn)
session = sm()
else:
session = env.db.session
# we add order_by in the query although it shouldn't be necessary
# this is because of a previous bug where duplicate tiles could be created
results = (
session
.query(Tile.id)
.filter(Tile.target_url == target_url)
.filter(Tile.bg_color == bg_color)
.filter(Tile.title == title)
.filter(Tile.image_uri == image_uri)
.filter(Tile.enhanced_image_uri == enhanced_image_uri)
.filter(Tile.locale == locale)
.order_by(asc(Tile.id))
.first()
)
if results:
return results[0]
return results
def insert_distribution(url, channel_id, deployed, scheduled_dt, *args,
**kwargs):
from splice.environment import Environment
# ensure that on insert, a distribution is either deployed or scheduled, not both
if scheduled_dt is not None:
deployed = False
env = Environment.instance()
conn = env.db.engine.connect()
trans = conn.begin()
try:
conn.execute(text(
"INSERT INTO distributions ("
" url, channel_id, deployed, scheduled_start_date, created_at"
") "
"VALUES ("
" :url, :channel_id, :deployed, :scheduled_start_date, :created_at"
")"),
url=url,
channel_id=channel_id,
deployed=deployed,
scheduled_start_date=scheduled_dt,
created_at=datetime.utcnow())
trans.commit()
except:
trans.rollback()
raise
def get_campaigns(account_id=None, past=True, in_flight=True, scheduled=True, utctoday=None):
from splice.environment import Environment
env = Environment.instance()
query = env.db.session.query(Campaign)
if account_id is not None:
query = query.filter(Campaign.account_id == account_id)
if utctoday is None:
utctoday = datetime.utcnow().date()
rows = query.order_by(Campaign.id.desc()).all()
campaigns = []
for row in rows:
ret = row_to_dict(row)
countries = []
for country in row.countries:
countries.append(country.country_code)
ret['countries'] = countries
# filter based on start and end dates unless an account ID is specified
if ((past and row.end_date.date() <= utctoday) or
(in_flight and row.end_date.date() >= utctoday >= row.start_date.date()) or
(scheduled and row.start_date.date() >= utctoday)):
campaigns.append(ret)
return campaigns
def insert_distribution(url, channel_id, deployed, scheduled_dt, *args, **kwargs):
from splice.environment import Environment
# ensure that on insert, a distribution is either deployed or scheduled, not both
if scheduled_dt is not None:
deployed = False
env = Environment.instance()
conn = env.db.engine.connect()
trans = conn.begin()
try:
conn.execute(
text(
"INSERT INTO distributions ("
" url, channel_id, deployed, scheduled_start_date, created_at"
") "
"VALUES ("
" :url, :channel_id, :deployed, :scheduled_start_date, :created_at"
")"
),
url=url,
channel_id=channel_id,
deployed=deployed,
scheduled_start_date=scheduled_dt,
created_at=datetime.utcnow()
)
trans.commit()
except:
trans.rollback()
raise
def get_scheduled_distributions(minutes, dt_query=None):
"""
Returns distributions scheduled from a point in time, and a leniency period
within which a tasks could've been scheduled closed to that point.
As a regular task, it is intended to run at least once hourly.
:minutes: amount of time in the past from the query time which is still viable
:dt_query: optionally set the date time to find schedules for
"""
from splice.environment import Environment
env = Environment.instance()
if not minutes or not (0 < minutes < 60):
raise ValueError(
"minutes needs to be a number between 1..59 inclusive")
if dt_query is None:
dt_query = datetime.utcnow()
# getting around PEP8 E712 warning. This is necessary for SQLAlchemy
false_value = False
min_query_dt = dt_query - timedelta(minutes=minutes)
stmt = (env.db.session.query(Distribution).filter(
Distribution.deployed == false_value).filter(
Distribution.scheduled_start_date.between(min_query_dt, dt_query)))
dists = stmt.all()
return dists
def get_scheduled_distributions(minutes, dt_query=None):
"""
Returns distributions scheduled from a point in time, and a leniency period
within which a tasks could've been scheduled closed to that point.
As a regular task, it is intended to run at least once hourly.
:minutes: amount of time in the past from the query time which is still viable
:dt_query: optionally set the date time to find schedules for
"""
from splice.environment import Environment
env = Environment.instance()
if not minutes or not (0 < minutes < 60):
raise ValueError("minutes needs to be a number between 1..59 inclusive")
if dt_query is None:
dt_query = datetime.utcnow()
# getting around PEP8 E712 warning. This is necessary for SQLAlchemy
false_value = False
min_query_dt = dt_query - timedelta(minutes=minutes)
stmt = (
env.db.session
.query(Distribution)
.filter(Distribution.deployed == false_value)
.filter(Distribution.scheduled_start_date.between(min_query_dt, dt_query))
)
dists = stmt.all()
return dists
def get_tile(id):
from splice.environment import Environment
env = Environment.instance()
row = (env.db.session.query(Tile).get(id))
return row_to_dict(row) if row else None
def get_upcoming_distributions(limit=100, leniency_minutes=15, include_past=False):
"""
Obtain distributions, partitioned by channels with up to ``limit`` results
for each channel
:leniency_minutes: have a leniency in minutes up to the present when looking for distributions
:include_past: always return all past distributions
"""
from splice.environment import Environment
env = Environment.instance()
# getting around PEP8 E712 warning. This is necessary for SQLAlchemy
false_value = False
dist_cte = (
env.db.session
.query(
Distribution.id,
Distribution.channel_id,
Distribution.url,
Distribution.created_at,
Distribution.scheduled_start_date,
func.row_number().over(
partition_by=Distribution.channel_id,
order_by=Distribution.scheduled_start_date.asc())
.label('row_num')
)
.filter(Distribution.deployed == false_value))
if not include_past:
min_dt = datetime.utcnow() - timedelta(minutes=leniency_minutes)
dist_cte = (
dist_cte
.filter(Distribution.scheduled_start_date >= min_dt))
dist_cte = dist_cte.cte()
stmt = (
env.db.session
.query(
dist_cte.c.id,
dist_cte.c.channel_id,
dist_cte.c.url,
dist_cte.c.created_at,
dist_cte.c.scheduled_start_date)
.filter(dist_cte.c.row_num <= limit)
.order_by(dist_cte.c.scheduled_start_date.asc())
)
rows = stmt.all()
channels = {}
for row in rows:
c_dists = channels.setdefault(row.channel_id, [])
c_dists.append({'id': row.id, 'url': row.url, 'created_at': row.created_at, 'scheduled_at': row.scheduled_start_date})
return channels
def get_upcoming_distributions(limit=100, leniency_minutes=15, include_past=False):
"""
Obtain distributions, partitioned by channels with up to ``limit`` results
for each channel
:leniency_minutes: have a leniency in minutes up to the present when looking for distributions
:include_past: always return all past distributions
"""
from splice.environment import Environment
env = Environment.instance()
# getting around PEP8 E712 warning. This is necessary for SQLAlchemy
false_value = False
dist_cte = (
env.db.session
.query(
Distribution.id,
Distribution.channel_id,
Distribution.url,
Distribution.created_at,
Distribution.scheduled_start_date,
func.row_number().over(
partition_by=Distribution.channel_id,
order_by=Distribution.scheduled_start_date.asc())
.label('row_num')
)
.filter(Distribution.deployed == false_value))
if not include_past:
min_dt = datetime.utcnow() - timedelta(minutes=leniency_minutes)
dist_cte = (
dist_cte
.filter(Distribution.scheduled_start_date >= min_dt))
dist_cte = dist_cte.cte()
stmt = (
env.db.session
.query(
dist_cte.c.id,
dist_cte.c.channel_id,
dist_cte.c.url,
dist_cte.c.created_at,
dist_cte.c.scheduled_start_date)
.filter(dist_cte.c.row_num <= limit)
.order_by(dist_cte.c.scheduled_start_date.asc())
)
rows = stmt.all()
channels = {}
for row in rows:
c_dists = channels.setdefault(row.channel_id, [])
c_dists.append({'id': row.id, 'url': row.url, 'created_at': row.created_at, 'scheduled_at': row.scheduled_start_date})
return channels
def test_get_all_categories(self):
""" Test for getting all categories"""
url = url_for('api.init.init', target="categories")
response = self.client.get(url)
assert_equal(response.status_code, 200)
categories = json.loads(response.data)['results']
categories_fixture = Environment.instance()._load_categories()
assert_equal(categories, categories_fixture)
def get_accounts():
from splice.environment import Environment
env = Environment.instance()
rows = (env.db.session.query(Account).order_by(Account.id.desc()).all())
output = [row_to_dict(d) for d in rows]
return output
def _update_image(bucket, image_url, tile_id, column='image_uri'):
env = Environment.instance()
if image_url and not image_url.startswith('http'):
imgs = list(bucket.list(prefix="images/%s" % image_url))
if len(imgs):
uri = os.path.join('https://%s.s3.amazonaws.com' % env.config.S3['bucket'], imgs[0])
print "updating %s for tile=%s" % (column, tile_id)
return "update tiles set %s = '%s' where id = %s" % (column, uri, tile_id)
return None
def test_get_all_categories(self):
""" Test for getting all categories"""
url = url_for('api.init.init', target="categories")
response = self.client.get(url)
assert_equal(response.status_code, 200)
categories = json.loads(response.data)['results']
categories_fixture = Environment.instance()._load_categories()
assert_equal(categories, categories_fixture)
def test_get_all_locale(self):
""" Test for getting all locales"""
url = url_for('api.init.init', target="locales")
response = self.client.get(url)
assert_equal(response.status_code, 200)
locales = json.loads(response.data)['results']
locales_fixture = Environment.instance()._load_locales()[:-1]
locales_fixture.sort()
assert_equal(locales, locales_fixture)
def test_get_all_countries(self):
""" Test for getting all countries"""
url = url_for('api.init.init', target="countries")
response = self.client.get(url)
assert_equal(response.status_code, 200)
countries = json.loads(response.data)['results']
countries_fixture = Environment.instance()._load_countries()[:-1]
items = [{"country_code": code, "country_name": name} for code, name in countries_fixture]
assert_equal(countries, items)
def get_account(id):
from splice.environment import Environment
env = Environment.instance()
row = (
env.db.session
.query(Account).get(id)
)
return row_to_dict(row) if row else None
def test_get_all_locale(self):
""" Test for getting all locales"""
url = url_for('api.init.init', target="locales")
response = self.client.get(url)
assert_equal(response.status_code, 200)
locales = json.loads(response.data)['results']
locales_fixture = Environment.instance()._load_locales()[:-1]
locales_fixture.sort()
assert_equal(locales, locales_fixture)
def get_tiles(filters=None, limit_fields=None):
from splice.environment import Environment
fields = [getattr(Tile, field)
for field in limit_fields] if limit_fields else [Tile]
format_row = tuple_to_dict if limit_fields else row_to_dict
rows = Environment.instance().db.session.query(*fields)
rows = add_filters(rows, filters)
rows = rows.all()
return [format_row(r) for r in rows] if rows else None
def test_get_all_countries(self):
""" Test for getting all countries"""
url = url_for('api.init.init', target="countries")
response = self.client.get(url)
assert_equal(response.status_code, 200)
countries = json.loads(response.data)['results']
countries_fixture = Environment.instance()._load_countries()[:-1]
items = [{
"country_code": code,
"country_name": name
} for code, name in countries_fixture]
assert_equal(countries, items)
def session_scope():
from splice.environment import Environment
env = Environment.instance()
session = env.db.session
try:
yield session
session.commit()
except:
session.rollback()
raise
finally:
session.close()
def setup_s3(bucket="bucket"):
from splice.environment import Environment
from boto.s3.cors import CORSConfiguration
env = Environment.instance()
bucket = env.s3.get_bucket(env.config.S3[bucket])
cors = CORSConfiguration()
cors.add_rule("GET", "*", allowed_header="*")
bucket.set_cors(cors)
headers = {
'Cache-Control': 'public, max-age=31536000',
'Content-Disposition': 'inline',
}
return bucket, headers
def get_content(name):
from splice.environment import Environment
env = Environment.instance()
row = env.db.session.query(Content).filter(Content.name == name).first()
c = row_to_dict(row) if row else None
if c is not None:
versions = []
for version in row.versions:
versions.append(row_to_dict(version))
c['versions'] = versions
return c
def get_content(name):
from splice.environment import Environment
env = Environment.instance()
row = env.db.session.query(Content).filter(Content.name == name).first()
c = row_to_dict(row) if row else None
if c is not None:
versions = []
for version in row.versions:
versions.append(row_to_dict(version))
c['versions'] = versions
return c
def get_channels(limit=100):
from splice.environment import Environment
env = Environment.instance()
rows = (env.db.session.query(Channel.id, Channel.name,
Channel.created_at).order_by(
Channel.id.asc()).limit(limit).all())
# ensure items are a list of dicts
# KeyedTuples may serialize differently on other systems
output = [d._asdict() for d in rows]
return output
def get_accounts():
from splice.environment import Environment
env = Environment.instance()
rows = (
env.db.session
.query(Account)
.order_by(Account.id.desc())
.all()
)
output = [row_to_dict(d) for d in rows]
return output
def get_campaign(campaign_id):
from splice.environment import Environment
env = Environment.instance()
row = (env.db.session.query(Campaign).get(campaign_id))
if row:
ret = row_to_dict(row)
countries = []
for country in row.countries:
countries.append(country.country_code)
ret['countries'] = countries
return ret
else:
return None
def tile_exists(target_url, bg_color, title, typ, image_uri, enhanced_image_uri, locale,
frecent_sites, time_limits, frequency_caps, adgroup_name, explanation, check_inadjacency, channel_id, conn=None, *args, **kwargs):
"""
Return the id of a tile having the data provided
"""
from splice.environment import Environment
env = Environment.instance()
if conn is not None:
sm = sessionmaker(bind=conn)
session = sm()
else:
session = env.db.session
# we add order_by in the query although it shouldn't be necessary
# this is because of a previous bug where duplicate tiles could be created
results = (
session
.query(Tile.id, Tile.adgroup_id)
.filter(Tile.target_url == target_url)
.filter(Tile.bg_color == bg_color)
.filter(Tile.title == title)
.filter(Tile.type == typ)
.filter(Tile.image_uri == image_uri)
.filter(Tile.enhanced_image_uri == enhanced_image_uri)
.filter(Adgroup.locale == locale)
.filter(Adgroup.start_date == time_limits.get('start'))
.filter(Adgroup.end_date == time_limits.get('end'))
.filter(Adgroup.start_date_dt == time_limits.get('start_dt'))
.filter(Adgroup.end_date_dt == time_limits.get('end_dt'))
.filter(Adgroup.frequency_cap_daily == frequency_caps['daily'])
.filter(Adgroup.frequency_cap_total == frequency_caps['total'])
.filter(Adgroup.name == adgroup_name)
.filter(Adgroup.explanation == explanation)
.filter(Adgroup.check_inadjacency == check_inadjacency)
.filter(Adgroup.channel_id == channel_id)
.join(Adgroup.tiles)
.order_by(asc(Tile.id))
)
if results:
for tile_id, adgroup_id in results:
# now check frecent sites for this tile
db_frecents = get_frecent_sites_for_tile(tile_id, conn)
if db_frecents == sorted(set(frecent_sites)):
return tile_id, adgroup_id
return None, None
def get_adgroup(id):
from splice.environment import Environment
env = Environment.instance()
row = (env.db.session.query(Adgroup).get(id))
if row is None:
return None
new = row_to_dict(row)
categories = []
for category in row.categories:
categories.append(category.category)
new['categories'] = categories
return new
def test_single_creative_upload_endpoint(self):
"""Test the API endpoint for the single creative upload"""
from splice.environment import Environment
env = Environment.instance()
url = url_for('api.tile.handler_creative_upload')
with zipfile.ZipFile(self.zip_file, "r") as zf:
f = zf.getinfo("samples/firefox_mdn_a.png")
data = {'creative': (StringIO.StringIO(zf.read(f)), 'creative.png')}
response = self.client.post(url, data=data)
assert_equal(response.status_code, 200)
creative_url = json.loads(response.data)['result']
bucket = env.s3.get_bucket(env.config.S3["bucket"])
s3_key = os.path.basename(creative_url)
key = bucket.get_key(s3_key)
self.assertIsNotNone(key)
def get_contents():
from splice.environment import Environment
env = Environment.instance()
rows = (env.db.session.query(Content).order_by(Content.id.desc()).all())
output = []
for d in rows:
versions = []
for version in d.versions:
versions.append(row_to_dict(version))
c = row_to_dict(d)
c['versions'] = versions
output.append(c)
return output
def get_adgroups():
from splice.environment import Environment
env = Environment.instance()
rows = (
env.db.session
.query(Adgroup.id, Adgroup.locale)
.order_by(Adgroup.id)
.all()
)
# ensure items are a list of dicts
# KeyedTuples may serialize differently on other systems
output = [d._asdict() for d in rows]
return output
def get_tiles():
from splice.environment import Environment
env = Environment.instance()
rows = (
env.db.session
.query(Tile.id, Tile.adgroup_id, Tile.title, Tile.type, Tile.bg_color, Tile.target_url)
.order_by(Tile.id.asc())
.all()
)
# ensure items are a list of dicts
# KeyedTuples may serialize differently on other systems
output = [d._asdict() for d in rows]
return output
def unschedule_distribution(dist_id):
"""
Remove a distribution id if it is scheduled but not deployed yet
"""
from splice.environment import Environment
env = Environment.instance()
# getting around PEP8 E711 warning. This is necessary for SQLAlchemy
none_value = None
stmt = (env.db.session.query(Distribution).filter(
Distribution.id == dist_id).filter(
Distribution.scheduled_start_date != none_value))
dist = stmt.one()
dist.scheduled_start_date = None
env.db.session.commit()
def get_adgroups_by_campaign_id(campaign_id):
from splice.environment import Environment
env = Environment.instance()
rows = (env.db.session.query(Adgroup).filter(
Adgroup.campaign_id == campaign_id).order_by(Adgroup.id.desc()).all())
output = []
for d in rows:
new = row_to_dict(d)
categories = []
for category in d.categories:
categories.append(category.category)
new['categories'] = categories
output.append(new)
return output
def test_single_creative_upload_endpoint(self):
"""Test the API endpoint for the single creative upload"""
from splice.environment import Environment
env = Environment.instance()
url = url_for('api.tile.handler_creative_upload')
with zipfile.ZipFile(self.zip_file, "r") as zf:
f = zf.getinfo("samples/firefox_mdn_a.png")
data = {
'creative': (StringIO.StringIO(zf.read(f)), 'creative.png')
}
response = self.client.post(url, data=data)
assert_equal(response.status_code, 200)
creative_url = json.loads(response.data)['result']
bucket = env.s3.get_bucket(env.config.S3["bucket"])
s3_key = os.path.basename(creative_url)
key = bucket.get_key(s3_key)
self.assertIsNotNone(key)
def get_campaign(campaign_id):
from splice.environment import Environment
env = Environment.instance()
row = (
env.db.session
.query(Campaign).get(campaign_id)
)
if row:
ret = row_to_dict(row)
countries = []
for country in row.countries:
countries.append(country.country_code)
ret['countries'] = countries
return ret
else:
return None
def get_channels(limit=100):
from splice.environment import Environment
env = Environment.instance()
rows = (
env.db.session
.query(Channel.id, Channel.name, Channel.created_at)
.order_by(Channel.id.asc())
.limit(limit)
.all()
)
# ensure items are a list of dicts
# KeyedTuples may serialize differently on other systems
output = [d._asdict() for d in rows]
return output
def get_distributions(limit=100, *args, **kwargs):
from splice.environment import Environment
env = Environment.instance()
rows = (
env.db.session
.query(Distribution.url, Distribution.created_at)
.order_by(Distribution.id.desc())
.limit(limit)
.all()
)
# ensure items are lists of lists rather than KeyedTuples
# KeyedTuples may serialize differently on other systems
output = [list(d) for d in rows]
return output
def switch_to_cdn_url(image_uri):
"""Switch the S3 URI with the CDN URI
We store the S3 URI in the database to allow campaign managers to view the
uploaded images without suffering from the CDN latency. When preparing to
generate tiles for the Firefox, it's necessary to replace the S3 URIs with
the CDN ones, as Firefox only allows images hosted on a trusted URI, e.g.
"tiles.cdn.mozilla.net".
See https://github.com/oyiptong/splice/issues/203 for more details.
"""
from splice.environment import Environment
env = Environment.instance()
try:
basename = os.path.basename(image_uri)
except:
basename = image_uri # if the image_uri is a hash string, use it directly
return os.path.join(env.config.CLOUDFRONT_BASE_URL, "images/%s" % basename)
def get_adgroup(id):
from splice.environment import Environment
env = Environment.instance()
row = (
env.db.session
.query(Adgroup).get(id)
)
if row is None:
return None
new = row_to_dict(row)
categories = []
for category in row.categories:
categories.append(category.category)
new['categories'] = categories
return new
def switch_to_cdn_url(image_uri):
"""Switch the S3 URI with the CDN URI
We store the S3 URI in the database to allow campaign managers to view the
uploaded images without suffering from the CDN latency. When preparing to
generate tiles for the Firefox, it's necessary to replace the S3 URIs with
the CDN ones, as Firefox only allows images hosted on a trusted URI, e.g.
"tiles.cdn.mozilla.net".
See https://github.com/oyiptong/splice/issues/203 for more details.
"""
from splice.environment import Environment
env = Environment.instance()
try:
basename = os.path.basename(image_uri)
except:
basename = image_uri # if the image_uri is a hash string, use it directly
return os.path.join(env.config.CLOUDFRONT_BASE_URL, "images/%s" % basename)
def get_stats(group_by, filters=None, limit=60):
"""
Get aggregated stats based on a list of group_by fields and filters
"""
from splice.environment import Environment
env = Environment.instance()
isd = aliased(impression_stats_daily)
base_table = isd
local_filters = filters.copy()
has_cross_db_filters = bool(
CROSS_DB_COLUMNS.intersection(filters)) if filters else False
cross_db_group_by = list(CROSS_DB_COLUMNS.intersection(group_by))
# Build base table and list of tiles
if cross_db_group_by:
base_table = build_subquery_table(env=env,
stats_table=isd,
group_by=group_by,
cross_db_group_by=cross_db_group_by,
filters=filters)
# No tiles were found, so no stats
if base_table is None:
return None
elif has_cross_db_filters:
tiles_result = get_tiles(limit_fields=['id'], filters=filters)
# No tiles were found, so no stats
if not tiles_result:
return None
local_filters['tile_id'] = [t['id'] for t in tiles_result]
# Build query
rows = build_base_query(env=env, group_by=group_by, base_table=base_table)
rows = add_filters(query=rows,
base_table=base_table,
filters=local_filters)
rows = rows.order_by(base_table.c[group_by[0]]).limit(limit)
rows = rows.all()
return [tuple_to_dict(r) for r in rows] if rows else None
def tile_exists(target_url, bg_color, title, typ, image_uri, enhanced_image_uri, locale,
frecent_sites, time_limits, channel_id, conn=None, *args, **kwargs):
"""
Return the id of a tile having the data provided
"""
from splice.environment import Environment
env = Environment.instance()
if conn is not None:
sm = sessionmaker(bind=conn)
session = sm()
else:
session = env.db.session
# we add order_by in the query although it shouldn't be necessary
# this is because of a previous bug where duplicate tiles could be created
results = (
session
.query(Tile.id, Tile.adgroup_id)
.filter(Tile.target_url == target_url)
.filter(Tile.bg_color == bg_color)
.filter(Tile.title == title)
.filter(Tile.image_uri == image_uri)
.filter(Tile.enhanced_image_uri == enhanced_image_uri)
.filter(Adgroup.locale == locale)
.filter(Adgroup.start_date == time_limits.get('start'))
.filter(Adgroup.end_date == time_limits.get('end'))
.filter(Adgroup.start_date_dt == time_limits.get('start_dt'))
.filter(Adgroup.end_date_dt == time_limits.get('end_dt'))
.filter(Adgroup.channel_id == channel_id)
.join(Adgroup.tiles)
.order_by(asc(Tile.id))
)
if results:
for tile_id, adgroup_id in results:
# now check frecent sites for this tile
db_frecents = get_frecent_sites_for_tile(tile_id, conn)
if db_frecents == sorted(set(frecent_sites)):
return tile_id, adgroup_id
return None, None
def get_all_distributions(limit=100):
"""
Obtain distributions, partitioned by channels with up to ``limit`` results
for each channel
"""
from splice.environment import Environment
env = Environment.instance()
dist_cte = (
env.db.session
.query(
Distribution.channel_id,
Distribution.url,
Distribution.created_at,
func.row_number().over(
partition_by=Distribution.channel_id,
order_by=Distribution.created_at.desc())
.label('row_num')
)
).cte()
stmt = (
env.db.session
.query(
dist_cte.c.channel_id,
dist_cte.c.url,
dist_cte.c.created_at)
.filter(dist_cte.c.row_num <= limit)
.order_by(dist_cte.c.created_at.desc())
)
rows = stmt.all()
channels = {}
for row in rows:
c_dists = channels.setdefault(row.channel_id, [])
c_dists.append({'url': row.url, 'created_at': row.created_at})
return channels
def unschedule_distribution(dist_id):
"""
Remove a distribution id if it is scheduled but not deployed yet
"""
from splice.environment import Environment
env = Environment.instance()
# getting around PEP8 E711 warning. This is necessary for SQLAlchemy
none_value = None
stmt = (
env.db.session
.query(Distribution)
.filter(Distribution.id == dist_id)
.filter(Distribution.scheduled_start_date != none_value)
)
dist = stmt.one()
dist.scheduled_start_date = None
env.db.session.commit()
def get_tile_ids_by_group(group_by, filters=None):
from splice.environment import Environment
env = Environment.instance()
group_by_field = {
'category': AdgroupCategory.category,
'account_id': Account.id,
'campaign_id': Campaign.id,
'adgroup_id': Adgroup.id
}.get(group_by)
rows = (env.db.session.query(
group_by_field.label(group_by),
func.array_agg(Tile.id).label('tile_ids')).select_from(Tile).group_by(
group_by_field))
rows = add_joins_for_group_by(query=rows, group_by=group_by)
rows = add_filters(rows, filters, group_by)
rows = rows.all()
return [tuple_to_dict(r) for r in rows] if rows else None
def get_contents():
from splice.environment import Environment
env = Environment.instance()
rows = (
env.db.session
.query(Content)
.order_by(Content.id.desc())
.all()
)
output = []
for d in rows:
versions = []
for version in d.versions:
versions.append(row_to_dict(version))
c = row_to_dict(d)
c['versions'] = versions
output.append(c)
return output
def insert_tile(target_url, bg_color, title, type, image_uri, enhanced_image_uri, locale, conn=None, *args, **kwargs):
from splice.environment import Environment
env = Environment.instance()
trans = None
if conn is None:
conn = env.db.engine.connect()
trans = conn.begin()
try:
conn.execute(
text(
"INSERT INTO tiles ("
" target_url, bg_color, title, type, image_uri, enhanced_image_uri, locale, created_at"
") "
"VALUES ("
" :target_url, :bg_color, :title, :type, :image_uri, :enhanced_image_uri, :locale, :created_at"
")"
),
target_url=target_url,
bg_color=bg_color,
title=title,
type=type,
image_uri=image_uri,
enhanced_image_uri=enhanced_image_uri,
locale=locale,
created_at=datetime.utcnow()
)
result = conn.execute("SELECT MAX(id) FROM tiles;").scalar()
if trans is not None:
trans.commit()
return result
except:
if trans is not None:
trans.rollback()
raise
def setup_routes(app):
env = Environment.instance()
global register_flask_restful
if "signing" in env.config.ALLOWED_APPS:
import splice.web.api.content
splice.web.api.content.register_routes(app)
if "tiles" in env.config.ALLOWED_APPS:
import splice.web.views
splice.web.views.register_routes(app)
import splice.web.api.heartbeat
splice.web.api.heartbeat.register_routes(app)
if not register_flask_restful:
import splice.web.api.init
splice.web.api.init.register_routes(app)
import splice.web.api.account
splice.web.api.account.register_routes(app)
import splice.web.api.campaign
splice.web.api.campaign.register_routes(app)
import splice.web.api.adgroup
splice.web.api.adgroup.register_routes(app)
import splice.web.api.tile
splice.web.api.tile.register_routes(app)
import splice.web.api.reporting
splice.web.api.reporting.register_routes(app)
import splice.web.api.distribution
splice.web.api.distribution.register_routes(app)
register_flask_restful = True
def setup_routes(app):
env = Environment.instance()
global register_flask_restful
if "signing" in env.config.ALLOWED_APPS:
import splice.web.api.content
splice.web.api.content.register_routes(app)
if "tiles" in env.config.ALLOWED_APPS:
import splice.web.views
splice.web.views.register_routes(app)
import splice.web.api.heartbeat
splice.web.api.heartbeat.register_routes(app)
if not register_flask_restful:
import splice.web.api.init
splice.web.api.init.register_routes(app)
import splice.web.api.account
splice.web.api.account.register_routes(app)
import splice.web.api.campaign
splice.web.api.campaign.register_routes(app)
import splice.web.api.adgroup
splice.web.api.adgroup.register_routes(app)
import splice.web.api.tile
splice.web.api.tile.register_routes(app)
import splice.web.api.reporting
splice.web.api.reporting.register_routes(app)
import splice.web.api.distribution
splice.web.api.distribution.register_routes(app)
register_flask_restful = True
def setUp(self):
super(TestReporting, self).setUp()
def values(fd, date_index=0):
for line in fd:
row = [el.decode('utf-8') for el in line.split(',')]
# sqlalchemy doesn't like date strings....
row[date_index] = datetime.strptime(row[date_index], "%Y-%m-%d")
yield row
# load db
from splice.models import impression_stats_daily, newtab_stats_daily
conn = Environment.instance().db.engine.connect()
with open(self.get_fixture_path('impression_stats.csv')) as fd:
for row in values(fd, 1):
ins = impression_stats_daily.insert().values(row)
conn.execute(ins)
with open(self.get_fixture_path('newtabs.csv')) as fd:
for row in values(fd):
ins = newtab_stats_daily.insert().values(row)
conn.execute(ins)
def get_adgroups_by_campaign_id(campaign_id):
from splice.environment import Environment
env = Environment.instance()
rows = (
env.db.session
.query(Adgroup)
.filter(Adgroup.campaign_id == campaign_id)
.order_by(Adgroup.id.desc())
.all()
)
output = []
for d in rows:
new = row_to_dict(d)
categories = []
for category in d.categories:
categories.append(category.category)
new['categories'] = categories
output.append(new)
return output
def insert_distribution(url, *args, **kwargs):
from splice.environment import Environment
env = Environment.instance()
conn = env.db.engine.connect()
trans = conn.begin()
try:
conn.execute(
text(
"INSERT INTO distributions ("
" url, created_at"
") "
"VALUES ("
" :url, :created_at"
")"
),
url=url,
created_at=datetime.utcnow()
)
trans.commit()
except:
trans.rollback()
raise
def get(self, target):
"""Returns the init data including locales, countries, channels etc.
Params: target string, [all|locales|countries|channels]
"""
target = target.lower()
if target == "all":
locales = Environment.instance()._load_locales()[:-1]
locales.sort()
countries = Environment.instance()._load_countries()[:-1]
country_items = [{"country_code": code, "country_name": name} for code, name in countries]
channels = get_channels()
categories = Environment.instance()._load_categories()
data = {
"countries": country_items,
"channels": channels,
"locales": locales,
"categories": categories,
}
return {'result': marshal(data, all_fields)}
elif target == "locales":
# the last item is 'ERROR', client won't need this
locales = Environment.instance()._load_locales()[:-1]
locales.sort()
return marshal({"results": locales}, locale_fields)
elif target == "countries":
# the last item is 'ERROR', client won't need this
countries = Environment.instance()._load_countries()[:-1]
country_items = [{"country_code": code, "country_name": name} for code, name in countries]
return {'results': marshal(country_items, country_fields)}
elif target == "channels":
channels = get_channels()
return {'results': marshal(channels, channel_fields)}
elif target == "categories":
categories = Environment.instance()._load_categories()
return marshal({"results": categories}, category_fields)
else:
return {"message": "Unknown target, must be one of [all|locales|countries|channels]"}, 404
import calendar
from datetime import datetime, timedelta
from nose.tools import assert_equal
from flask import url_for, json
from mock import Mock, PropertyMock
from tests.base import BaseTestCase
from tests.test_scheduling import ScheduleTest
import splice.ingest
from splice.queries import (
get_scheduled_distributions,
get_all_distributions,
get_channels)
from splice.environment import Environment
env = Environment.instance()
class TestAuthoring(BaseTestCase):
def setUp(self):
self.key_mock = Mock()
self.key_mock.name = PropertyMock()
self.bucket_mock = Mock()
def bucket_get_key_mock(*args, **kwargs):
return None
self.bucket_mock.get_key = Mock(side_effect=bucket_get_key_mock)
def get_key_mock(*args, **kwargs):
return self.key_mock
splice.ingest.Key = Mock(side_effect=get_key_mock)
