def testCircularFks(self):
schema = test_schema + "circular_fks"
tdf = TicDatFactory(table_one=[["A Field"], []],
table_two=[["B Field"], []],
table_three=[["C Field"], []])
tdf.add_foreign_key("table_one", "table_two", ["A Field", "B Field"])
tdf.add_foreign_key("table_two", "table_three", ["B Field", "C Field"])
tdf.add_foreign_key("table_three", "table_one", ["C Field", "A Field"])
tdf.pgsql.write_schema(self.engine,
schema,
include_ancillary_info=False)
t_ = [["a"], ["b"], ["c"]]
dat = tdf.TicDat(table_one=t_, table_two=t_, table_three=t_)
tdf.pgsql.write_data(dat, self.engine, schema)
# this version of the file uses CPLEX
import time
import datetime
import os
from docplex.mp.model import Model
from ticdat import TicDatFactory, Progress, LogFile, Slicer, standard_main
# ------------------------ define the input schema --------------------------------
# There are three input tables, with 4 primary key fields and 4 data fields.
dataFactory = TicDatFactory(sites=[['name'], ['demand', 'center_status']],
distance=[['source', 'destination'], ['distance']],
parameters=[["key"], ["value"]])
# add foreign key constraints
dataFactory.add_foreign_key("distance", "sites", ['source', 'name'])
dataFactory.add_foreign_key("distance", "sites", ['destination', 'name'])
# center_status is a flag field which can take one of two string values.
dataFactory.set_data_type(
"sites",
"center_status",
number_allowed=False,
strings_allowed=["Can Be Center", "Pure Demand Point"])
# The default type of non infinite, non negative works for distance
dataFactory.set_data_type("distance", "distance")
# ---------------------------------------------------------------------------------
# ------------------------ define the output schema -------------------------------
# There are three solution tables, with 2 primary key fields and 3
# data fields amongst them.
input_schema.set_data_type("forecast_sales",
"Cost Per Unit",
min=0,
max=float("inf"))
input_schema.set_data_type("max_promotions",
"Product Family",
number_allowed=False,
strings_allowed=("Dark", "Clear"))
input_schema.set_data_type("max_promotions",
"Max Promotions",
min=0,
max=float("inf"),
inclusive_max=False,
must_be_int=True)
input_schema.add_foreign_key("products", "max_promotions",
["Family", "Product Family"])
input_schema.add_foreign_key("forecast_sales", "products", ["Product", "Name"])
solution_schema = TicDatFactory(
parameters=[["Key"], ["Value"]],
product_pricing=[["Product"],
["Cost Per Unit", "Family", "Promotional Status"]])
def solve(dat):
assert input_schema.good_tic_dat_object(dat)
assert not input_schema.find_foreign_key_failures(dat)
assert not input_schema.find_data_type_failures(dat)
normal_price = {pdct: 0 for pdct in dat.products}
for pdct, price in dat.forecast_sales:
# will read from a model stored in .csv files in the csv_data directory
# and write the solution to .csv files in the solution_csv_data directory
from ticdat import TicDatFactory, standard_main, Model, Slicer
# ------------------------ define the input schema --------------------------------
input_schema = TicDatFactory (
commodities = [["Name"],[]],
nodes = [["Name"],[]],
arcs = [["Source", "Destination"],["Capacity"]],
cost = [["Commodity", "Source", "Destination"], ["Cost"]],
inflow = [["Commodity", "Node"],["Quantity"]]
)
# Define the foreign key relationships
input_schema.add_foreign_key("arcs", "nodes", ['Source', 'Name'])
input_schema.add_foreign_key("arcs", "nodes", ['Destination', 'Name'])
input_schema.add_foreign_key("cost", "nodes", ['Source', 'Name'])
input_schema.add_foreign_key("cost", "nodes", ['Destination', 'Name'])
input_schema.add_foreign_key("cost", "commodities", ['Commodity', 'Name'])
input_schema.add_foreign_key("inflow", "commodities", ['Commodity', 'Name'])
input_schema.add_foreign_key("inflow", "nodes", ['Node', 'Name'])
# Define the data types
input_schema.set_data_type("arcs", "Capacity", min=0, max=float("inf"),
inclusive_min=True, inclusive_max=True)
input_schema.set_data_type("cost", "Cost", min=0, max=float("inf"),
inclusive_min=True, inclusive_max=False)
input_schema.set_data_type("inflow", "Quantity", min=-float("inf"), max=float("inf"),
inclusive_min=False, inclusive_max=False)
from ticdat import TicDatFactory
from tts_diet.tooltips import input_schema_tooltips, solution_schema_tooltips
# ------------------------ define the input schema --------------------------------
# There are three input tables, with 4 primary key fields and 4 data fields.
input_schema = TicDatFactory(categories=[["Name"],
["Min Nutrition", "Max Nutrition"]],
foods=[["Name"], ["Cost"]],
nutrition_quantities=[["Food", "Category"],
["Quantity"]])
for (tbl, fld), tip in input_schema_tooltips.items():
input_schema.set_tooltip(tbl, fld, tip)
# Define the foreign key relationships
input_schema.add_foreign_key("nutrition_quantities", "foods", ["Food", "Name"])
input_schema.add_foreign_key("nutrition_quantities", "categories",
["Category", "Name"])
# Define the data types
input_schema.set_data_type("categories",
"Min Nutrition",
min=0,
max=float("inf"),
inclusive_min=True,
inclusive_max=False)
input_schema.set_data_type("categories",
"Max Nutrition",
min=0,
max=float("inf"),
inclusive_min=True,
# this version of the file uses Gurobi
import gurobipy as gu
# ------------------------ define the input schema --------------------------------
input_schema = TicDatFactory (
parameters = [["Key"],["Value"]],
players = [['Player Name'],
['Position', 'Average Draft Position', 'Expected Points', 'Draft Status']],
roster_requirements = [['Position'],
['Min Num Starters', 'Max Num Starters', 'Min Num Reserve', 'Max Num Reserve',
'Flex Status']],
my_draft_positions = [['Draft Position'],[]]
)
# add foreign key constraints (optional, but helps with preventing garbage-in, garbage-out)
input_schema.add_foreign_key("players", "roster_requirements", ['Position', 'Position'])
# set data types (optional, but helps with preventing garbage-in, garbage-out)
input_schema.set_data_type("parameters", "Key", number_allowed = False,
strings_allowed = ["Starter Weight", "Reserve Weight",
"Maximum Number of Flex Starters"])
input_schema.set_data_type("parameters", "Value", min=0, max=float("inf"),
inclusive_min = True, inclusive_max = False)
input_schema.set_data_type("players", "Average Draft Position", min=0, max=float("inf"),
inclusive_min = False, inclusive_max = False)
input_schema.set_data_type("players", "Expected Points", min=-float("inf"), max=float("inf"),
inclusive_min = False, inclusive_max = False)
input_schema.set_data_type("players", "Draft Status",
strings_allowed = ["Un-drafted", "Drafted By Me", "Drafted By Someone Else"])
for fld in ("Min Num Starters", "Min Num Reserve", "Max Num Reserve"):
input_schema.set_data_type("roster_requirements", fld, min=0, max=float("inf"),
# to solution_data.xlsx.
#
# Note that file requires diet.lng to be in the same directory
from ticdat import TicDatFactory, standard_main, lingo_run
# ------------------------ define the input schema --------------------------------
# There are three input tables, with 4 primary key fields and 4 data fields.
input_schema = TicDatFactory(categories=[["Name"],
["Min Nutrition", "Max Nutrition"]],
foods=[["Name"], ["Cost"]],
nutrition_quantities=[["Food", "Category"],
["Quantity"]])
# add foreign key constraints
input_schema.add_foreign_key("nutrition_quantities", "foods", ['Food', 'Name'])
input_schema.add_foreign_key("nutrition_quantities", "categories",
['Category', 'Name'])
# Define the data types
input_schema.set_data_type("categories",
"Min Nutrition",
min=0,
max=float("inf"),
inclusive_min=True,
inclusive_max=False)
input_schema.set_data_type("categories",
"Max Nutrition",
min=0,
max=float("inf"),
inclusive_min=True,
input_schema = TicDatFactory(
parameters=[["Parameter"], ["Value"]],
machines=[["Name"], []],
jobs=[["Name"], ["Machine1", "Durations1", "Machine2", "Durations2"]])
input_schema.set_data_type("parameters",
"Parameter",
number_allowed=False,
strings_allowed=("Load Duration", ))
input_schema.set_data_type("parameters",
"Value",
min=0,
max=float("inf"),
inclusive_max=False)
input_schema.add_foreign_key("jobs", "machines", ["Machine1", "Name"])
input_schema.add_foreign_key("jobs", "machines", ["Machine2", "Name"])
input_schema.set_data_type("jobs",
"Durations1",
min=0,
max=float("inf"),
inclusive_max=False,
must_be_int=True)
input_schema.set_data_type("jobs",
"Durations2",
min=0,
max=float("inf"),
inclusive_max=False,
must_be_int=True)
# the sum of the slacks, and minimize the total payment to workers.
# Finally, we minimize a quadratic objective that
# tries to balance the workload among the workers.
#
import gurobipy as gu
from ticdat import TicDatFactory, standard_main, gurobi_env
# ------------------------ define the input schema --------------------------------
input_schema = TicDatFactory(
workers=[["Name"], ["Payment"]],
shifts=[["Name"], ["Requirement"]],
availability=[["Worker", "Shift"], []]
)
# Define the foreign key relationships
input_schema.add_foreign_key("availability", "workers", ['Worker', 'Name'])
input_schema.add_foreign_key("availability", "shifts", ['Shift', 'Name'])
# Define the data types
input_schema.set_data_type("workers", "Payment", min=0, max=float("inf"),
inclusive_min=True, inclusive_max=True)
input_schema.set_data_type("shifts", "Requirement", min=0, max=float("inf"),
inclusive_min=True, inclusive_max=True)
# ---------------------------------------------------------------------------------
# ------------------------ define the output schema -------------------------------
solution_schema = TicDatFactory(
assignments=[["Worker", "Shift"], []],
slacks = [["Shift"], ["Slack"]],
total_shifts=[["Worker"], ["Total Number Of Shifts"]],
parameters=[["Parameter"], ["Value"]]
from ticdat import TicDatFactory, dat_restricted, sln_restricted
input_schema = TicDatFactory (
categories = [["Name"],["Min Nutrition", "Max Nutrition"]],
foods = [["Name"],["Cost"]],
nutrition_quantities = [["Food", "Category"], ["Quantity"]],
stupid_table = [["Fo", "Cat"],["Junk"]])
input_schema.add_foreign_key("nutrition_quantities", "foods", ["Food", "Name"])
input_schema.add_foreign_key("stupid_table", "nutrition_quantities", [["Fo", "Food"],["Cat", "Category"]])
input_schema.add_foreign_key("nutrition_quantities", "categories",
["Category", "Name"])
input_schema.set_data_type("categories", "Min Nutrition", min=0, max=float("inf"),
inclusive_min=True, inclusive_max=False)
input_schema.set_data_type("categories", "Max Nutrition", min=0, max=float("inf"),
inclusive_min=True, inclusive_max=True)
input_schema.set_data_type("foods", "Cost", min=0, max=float("inf"),
inclusive_min=True, inclusive_max=False)
input_schema.set_data_type("nutrition_quantities", "Quantity", min=0, max=float("inf"),
inclusive_min=True, inclusive_max=False)
input_schema.add_data_row_predicate(
"categories", predicate_name="Min Max Check",
predicate=lambda row : row["Max Nutrition"] >= row["Min Nutrition"])
input_schema.set_default_value("categories", "Max Nutrition", float("inf"))
solution_schema = TicDatFactory(
parameters = [["Parameter"],["Value"]],
buy_food = [["Food"],["Quantity"]],
consume_nutrition = [["Category"],["Quantity"]],
weird_table = [["This","That"],["Thother"]])
hard_coded_solution_dict = {
# there is a deliberate flaw in the data - we are consuming 91.9 not 91.0 units of protein
"Maximize Percent High Service Demand"
],
number_allowed=False)
input_schema.set_data_type("cities",
"Demand",
min=0,
max=float("inf"),
inclusive_min=True,
inclusive_max=False)
input_schema.set_data_type("distances",
"Distance",
min=0,
max=float("inf"),
inclusive_min=True,
inclusive_max=False)
input_schema.add_foreign_key("distances", "cities", ['Source', 'Name'])
input_schema.add_foreign_key("distances", "cities", ['Destination', 'Name'])
# The distance matrix is bi-directionally safe. I.e. if the same source/dest and dest/source exist then the
# distances must match. If only one is present, it can fall back to the other in the code.
def _distance_matrix(dat):
return {
"distance_matrix":
{k: v["Distance"]
for k, v in dat.distances.items()}
}
input_schema.add_data_row_predicate(
"distances",
def _testFantop(modelType, sqlFile):
dataFactory = TicDatFactory(parameters=[["Key"], ["Value"]],
players=[['Player Name'],
[
'Position',
'Average Draft Position',
'Expected Points', 'Draft Status'
]],
roster_requirements=[['Position'],
[
'Min Num Starters',
'Max Num Starters',
'Min Num Reserve',
'Max Num Reserve',
'Flex Status'
]],
my_draft_positions=[['Draft Position'], []])
# add foreign key constraints (optional, but helps with preventing garbage-in, garbage-out)
dataFactory.add_foreign_key("players", "roster_requirements",
['Position', 'Position'])
# set data types (optional, but helps with preventing garbage-in, garbage-out)
dataFactory.set_data_type("parameters",
"Key",
number_allowed=False,
strings_allowed=[
"Starter Weight", "Reserve Weight",
"Maximum Number of Flex Starters"
])
dataFactory.set_data_type("parameters",
"Value",
min=0,
max=float("inf"),
inclusive_min=True,
inclusive_max=False)
dataFactory.set_data_type("players",
"Average Draft Position",
min=0,
max=float("inf"),
inclusive_min=False,
inclusive_max=False)
dataFactory.set_data_type("players",
"Expected Points",
min=-float("inf"),
max=float("inf"),
inclusive_min=False,
inclusive_max=False)
dataFactory.set_data_type("players",
"Draft Status",
strings_allowed=[
"Un-drafted", "Drafted By Me",
"Drafted By Someone Else"
])
for fld in ("Min Num Starters", "Min Num Reserve", "Max Num Reserve"):
dataFactory.set_data_type("roster_requirements",
fld,
min=0,
max=float("inf"),
inclusive_min=True,
inclusive_max=False,
must_be_int=True)
dataFactory.set_data_type("roster_requirements",
"Max Num Starters",
min=0,
max=float("inf"),
inclusive_min=False,
inclusive_max=True,
must_be_int=True)
dataFactory.set_data_type(
"roster_requirements",
"Flex Status",
number_allowed=False,
strings_allowed=["Flex Eligible", "Flex Ineligible"])
dataFactory.set_data_type("my_draft_positions",
"Draft Position",
min=0,
max=float("inf"),
inclusive_min=False,
inclusive_max=False,
must_be_int=True)
solutionFactory = TicDatFactory(my_draft=[[
'Player Name'
], [
'Draft Position', 'Position', 'Planned Or Actual', 'Starter Or Reserve'
]])
dat = dataFactory.sql.create_tic_dat_from_sql(os.path.join(
_codeDir(), sqlFile),
freeze_it=True)
assert dataFactory.good_tic_dat_object(dat)
assert not dataFactory.find_foreign_key_failures(dat)
assert not dataFactory.find_data_type_failures(dat)
expected_draft_position = {}
# for our purposes, its fine to assume all those drafted by someone else are drafted
# prior to any players drafted by me
for player_name in sorted(
dat.players,
key=lambda _p: {
"Un-drafted": dat.players[_p]["Average Draft Position"],
"Drafted By Me": -1,
"Drafted By Someone Else": -2
}[dat.players[_p]["Draft Status"]]):
expected_draft_position[player_name] = len(expected_draft_position) + 1
assert max(expected_draft_position.values()) == len(
set(expected_draft_position.values())) == len(dat.players)
assert min(expected_draft_position.values()) == 1
already_drafted_by_me = {
player_name
for player_name, row in dat.players.items()
if row["Draft Status"] == "Drafted By Me"
}
can_be_drafted_by_me = {
player_name
for player_name, row in dat.players.items()
if row["Draft Status"] != "Drafted By Someone Else"
}
m = Model(modelType, 'fantop')
my_starters = {
player_name: m.add_var(type="binary", name="starter_%s" % player_name)
for player_name in can_be_drafted_by_me
}
my_reserves = {
player_name: m.add_var(type="binary", name="reserve_%s" % player_name)
for player_name in can_be_drafted_by_me
}
for player_name in can_be_drafted_by_me:
if player_name in already_drafted_by_me:
m.add_constraint(my_starters[player_name] +
my_reserves[player_name] == 1,
name="already_drafted_%s" % player_name)
else:
m.add_constraint(
my_starters[player_name] + my_reserves[player_name] <= 1,
name="cant_draft_twice_%s" % player_name)
for i, draft_position in enumerate(sorted(dat.my_draft_positions)):
m.add_constraint(m.sum(
my_starters[player_name] + my_reserves[player_name]
for player_name in can_be_drafted_by_me
if expected_draft_position[player_name] < draft_position) <= i,
name="at_most_%s_can_be_ahead_of_%s" %
(i, draft_position))
my_draft_size = m.sum(my_starters[player_name] + my_reserves[player_name]
for player_name in can_be_drafted_by_me)
m.add_constraint(my_draft_size >= len(already_drafted_by_me) + 1,
name="need_to_extend_by_at_least_one")
m.add_constraint(my_draft_size <= len(dat.my_draft_positions),
name="cant_exceed_draft_total")
for position, row in dat.roster_requirements.items():
players = {
player_name
for player_name in can_be_drafted_by_me
if dat.players[player_name]["Position"] == position
}
starters = m.sum(my_starters[player_name] for player_name in players)
reserves = m.sum(my_reserves[player_name] for player_name in players)
m.add_constraint(starters >= row["Min Num Starters"],
name="min_starters_%s" % position)
m.add_constraint(starters <= row["Max Num Starters"],
name="max_starters_%s" % position)
m.add_constraint(reserves >= row["Min Num Reserve"],
name="min_reserve_%s" % position)
m.add_constraint(reserves <= row["Max Num Reserve"],
name="max_reserve_%s" % position)
if "Maximum Number of Flex Starters" in dat.parameters:
players = {
player_name
for player_name in can_be_drafted_by_me
if dat.roster_requirements[dat.players[player_name]["Position"]]
["Flex Status"] == "Flex Eligible"
}
m.add_constraint(
m.sum(my_starters[player_name] for player_name in players) <=
dat.parameters["Maximum Number of Flex Starters"]["Value"],
name="max_flex")
starter_weight = dat.parameters["Starter Weight"][
"Value"] if "Starter Weight" in dat.parameters else 1
reserve_weight = dat.parameters["Reserve Weight"][
"Value"] if "Reserve Weight" in dat.parameters else 1
m.set_objective(m.sum(dat.players[player_name]["Expected Points"] *
(my_starters[player_name] * starter_weight +
my_reserves[player_name] * reserve_weight)
for player_name in can_be_drafted_by_me),
sense="maximize")
if not m.optimize():
return
sln = solutionFactory.TicDat()
def almostone(x):
return abs(m.get_solution_value(x) - 1) < 0.0001
picked = sorted([
player_name for player_name in can_be_drafted_by_me
if almostone(my_starters[player_name])
or almostone(my_reserves[player_name])
],
key=lambda _p: expected_draft_position[_p])
assert len(picked) <= len(dat.my_draft_positions)
if len(picked) < len(dat.my_draft_positions):
print(
"Your model is over-constrained, and thus only a partial draft was possible"
)
draft_yield = 0
for player_name, draft_position in zip(picked,
sorted(dat.my_draft_positions)):
draft_yield += dat.players[player_name]["Expected Points"] * \
(starter_weight if almostone(my_starters[player_name]) else reserve_weight)
assert draft_position <= expected_draft_position[player_name]
sln.my_draft[player_name]["Draft Position"] = draft_position
sln.my_draft[player_name]["Position"] = dat.players[player_name][
"Position"]
sln.my_draft[player_name][
"Planned Or Actual"] = "Actual" if player_name in already_drafted_by_me else "Planned"
sln.my_draft[player_name]["Starter Or Reserve"] = \
"Starter" if almostone(my_starters[player_name]) else "Reserve"
return sln, draft_yield
input_schema = TicDatFactory(
roster=[["Name"], ["Grade"]],
positions=[["Position"], ["Position Importance", "Position Group"]],
player_ratings=[["Name", "Position Group"], ["Rating"]],
innings=[["Inning"], ["Inning Group"]],
position_constraints=[["Position Group", "Inning Group", "Grade"],
["Min Players", "Max Players"]])
input_schema.set_data_type("positions",
"Position Importance",
min=0,
max=float("inf"),
inclusive_min=True,
inclusive_max=False)
input_schema.add_foreign_key("player_ratings", "roster", ["Name", "Name"])
input_schema.add_foreign_key("player_ratings", "positions",
["Position Group", "Position Group"])
input_schema.set_data_type("player_ratings",
"Rating",
min=0,
max=float("inf"),
inclusive_min=True,
inclusive_max=False)
input_schema.add_foreign_key("position_constraints", "positions",
["Position Group", "Position Group"])
input_schema.add_foreign_key("position_constraints", "innings",
["Inning Group", "Inning Group"])
input_schema.add_foreign_key("position_constraints", "roster",
["Grade", "Grade"])
# Solve a multi-commodity flow problem.
from gurobipy import *
from ticdat import TicDatFactory
# define the input schema.
dataFactory = TicDatFactory (
commodities = [["name"],[]],
nodes = [["name"],[]],
arcs = [["source", "destination"],["capacity"]],
cost = [["commodity", "source", "destination"], ["cost"]],
inflow = [["commodity", "node"],["quantity"]]
)
# add foreign key constraints
dataFactory.add_foreign_key("arcs", "nodes", ['source', 'name'])
dataFactory.add_foreign_key("arcs", "nodes", ['destination', 'name'])
dataFactory.add_foreign_key("cost", "nodes", ['source', 'name'])
dataFactory.add_foreign_key("cost", "nodes", ['destination', 'name'])
dataFactory.add_foreign_key("cost", "commodities", ['commodity', 'name'])
dataFactory.add_foreign_key("inflow", "commodities", ['commodity', 'name'])
dataFactory.add_foreign_key("inflow", "nodes", ['node', 'name'])
# the whole schema has only three data fields to type
dataFactory.set_data_type("arcs", "capacity")
dataFactory.set_data_type("cost", "cost")
# except quantity which allows negatives
dataFactory.set_data_type("inflow", "quantity", min=-float("inf"), inclusive_min=False)
solutionFactory = TicDatFactory(
flow = [["commodity", "source", "destination"], ["quantity"]])
import time
import datetime
import os
import gurobipy as gu
from ticdat import TicDatFactory, Progress, LogFile, Slicer, standard_main, gurobi_env
# ------------------------ define the input schema --------------------------------
# There are three input tables, with 4 primary key fields and 4 data fields.
input_schema = TicDatFactory(sites=[['name'], ['demand', 'center_status']],
distance=[['source', 'destination'],
['distance']],
parameters=[["key"], ["value"]])
# add foreign key constraints
input_schema.add_foreign_key("distance", "sites", ['source', 'name'])
input_schema.add_foreign_key("distance", "sites", ['destination', 'name'])
# center_status is a flag field which can take one of two string values.
input_schema.set_data_type(
"sites",
"center_status",
number_allowed=False,
strings_allowed=["Can Be Center", "Pure Demand Point"])
# The default type of non infinite, non negative works for distance
input_schema.set_data_type("distance", "distance")
# There are three types of parameters
input_schema.set_data_type(
"parameters",
"key",
