def executeModify(self, data, tx):
tblname = data.tableName()
fldname = data.targetField()
p = TablePlan(tx, tblname, self.mdm)
p = SelectPlan(p, data.pred())
ii = self.mdm.getIndexInfo(tblname, tx).get(fldname)
idx = None if (ii is None) else ii.open()
s = p.open()
count = 0
while s.next():
# first, update the record
newval = data.newValue().evaluate(s)
oldval = s.getVal(fldname)
s.setVal(data.targetField(), newval)
# then update the appropriate index, if it exists
if idx is not None:
rid = s.getRid()
idx.delete(oldval, rid)
idx.insert(newval, rid)
count += 1
if idx is not None:
idx.close()
s.close()
return count
def main(cls, args):
db = SimpleDB("studentdb")
tx = db.newTx()
mdm = db.mdMgr()
# Open a scan on the data table.
studentplan = TablePlan(tx, "student", mdm)
studentscan = studentplan.open()
# Open the index on MajorId.
indexes = mdm.getIndexInfo("student", tx)
ii = indexes.get("majorid")
idx = ii.open()
# Retrieve all index records having a dataval of 20.
idx.beforeFirst(Constant(20))
while idx.next():
# Use the datarid to go to the corresponding STUDENT record.
datarid = idx.getDataRid()
studentscan.moveToRid(datarid)
print(studentscan.getString("sname"))
# Close the index and the data table.
idx.close()
studentscan.close()
tx.commit()
def main(cls, args):
# db = SimpleDB("studentdb")
fm = FileMgr(File("studentdb"), 400)
lm = LogMgr(fm, "simpledb.log")
bm = BufferMgr(fm, lm, 8)
tx = Transaction(fm, lm, bm)
isnew = fm.isNew()
mdm = MetadataMgr(isnew, tx)
# the STUDENT node
p1 = TablePlan(tx, "student", mdm)
# the DEPT node
p2 = TablePlan(tx, "dept", mdm)
# the Product node for student x dept
p3 = ProductPlan(p1, p2)
# the Select node for "majorid = did"
t = Term(Expression("majorid"), Expression("did"))
pred = Predicate(t)
p4 = SelectPlan(p3, pred)
# Look at R(p) and B(p) for each plan p.
MultiTablePlanTest.printStats(1, p1)
MultiTablePlanTest.printStats(2, p2)
MultiTablePlanTest.printStats(3, p3)
MultiTablePlanTest.printStats(4, p4)
# Change p3 to be p4 to see the select scan in action.
s = p3.open()
while s.next:
print(s.getString("sname") + " " + s.getString("dname"))
s.close()
def test2():
db = SimpleDB("studentdb")
mdm = db.mdMgr()
tx = db.newTx()
p1 = TablePlan(tx, "student", mdm)
s = p1.open()
while s.next():
print(s.getString("sname"), s.getInt("gradyear"))
def main(cls, args):
db = SimpleDB("studentdb")
tx = db.newTx()
mdm = db.mdMgr()
studentplan = TablePlan(tx, "student", mdm)
studentscan = studentplan.open()
# Create a map containing all indexes for STUDENT.
indexes = {}
idxinfo = mdm.getIndexInfo("student", tx)
for fldname in idxinfo.keySet():
idx = idxinfo.get(fldname).open()
indexes[fldname] = idx
# Task 1: insert a new STUDENT record for Sam
# First, insert the record into STUDENT.
studentscan.insert()
studentscan.setInt("sid", 11)
studentscan.setString("sname", "sam")
studentscan.setInt("gradyear", 2023)
studentscan.setInt("majorid", 30)
# Then insert a record into each of the indexes.
datarid = studentscan.getRid()
for fldname in indexes.keys():
dataval = studentscan.getVal(fldname)
idx = indexes.get(fldname)
idx.insert(dataval, datarid)
# Task 2: find and delete Joe's record
studentscan.beforeFirst()
while studentscan.next():
if studentscan.getString("sname") == "joe":
# First, delete the index records for Joe.
joeRid = studentscan.getRid()
for fldname in indexes.keys():
dataval = studentscan.getVal(fldname)
idx = indexes.get(fldname)
idx.delete(dataval, joeRid)
# Then delete Joe's record in STUDENT.
studentscan.delete()
break
# Print the records to verify the updates.
studentscan.beforeFirst()
while studentscan.next():
print(
studentscan.getString("sname") + " " +
str(studentscan.getInt("sid")))
studentscan.close()
for idx in indexes.values():
idx.close()
tx.commit()
def executeDelete(self, data, tx):
p = TablePlan(tx, data.tableName(), self.mdm)
p = SelectPlan(p, data.pred())
us = p.open()
count = 0
while us.next():
us.delete()
count += 1
us.close()
return count
def executeInsert(self, data, tx):
p = TablePlan(tx, data.tableName(), self.mdm)
us = p.open()
us.insert()
iterator = data.vals().__iter__()
for fldname in data.fields():
val = iterator.__next__()
us.setVal(fldname, val)
us.close()
return 1
def executeModify(self, data, tx):
p = TablePlan(tx, data.tableName(), self.mdm)
p = SelectPlan(p, data.pred())
us = p.open()
count = 0
while us.next():
val = data.newValue().evaluate(us)
us.setVal(data.targetField(), val)
count += 1
us.close()
return count
def createPlan(self, data, tx):
# Step 1: Create a plan for each mentioned table or view.
plans = []
for tblname in data.tables():
viewdef = self.mdm.getViewDef(tblname, tx)
if viewdef is not None: # Recursively plan the view.
parser = Parser(viewdef)
viewdata = parser.query()
plans.append(self.createPlan(viewdata, tx))
else:
plans.append(TablePlan(tx, tblname, self.mdm))
# Step 2: Create the product of all table plans
p = plans.pop(0)
for nextplan in plans:
# Try both orderings and choose the one having lowest cost
choice1 = ProductPlan(nextplan, p)
choice2 = ProductPlan(p, nextplan)
if choice1.blocksAccessed() < choice2.blocksAccessed():
p = choice1
else:
p = choice2
# Step 3: Add a selection plan for the predicate
p = SelectPlan(p, data.pred())
# Step 4: Project on the field names
p = ProjectPlan(p, data.fields())
return p
def main(cls, args):
db = SimpleDB("studentdb")
mdm = db.mdMgr()
tx = db.newTx()
# Find the index on StudentId.
indexes = mdm.getIndexInfo("enroll", tx)
sidIdx = indexes.get("studentid")
# Get plans for the Student and Enroll tables
studentplan = TablePlan(tx, "student", mdm)
enrollplan = TablePlan(tx, "enroll", mdm)
# Two different ways to use the index in simpledb:
IndexJoinTest.useIndexManually(studentplan, enrollplan, sidIdx, "sid")
IndexJoinTest.useIndexScan(studentplan, enrollplan, sidIdx, "sid")
tx.commit()
def executeDelete(self, data, tx):
tblname = data.tableName()
p = TablePlan(tx, tblname, self.mdm)
p = SelectPlan(p, data.pred())
indexes = self.mdm.getIndexInfo(tblname, tx)
s = p.open()
count = 0
while s.next():
# first, delete the record's RID from every index
rid = s.getRid()
for fldname in indexes.keys():
val = s.getVal(fldname)
idx = indexes.get(fldname).open()
idx.delete(val, rid)
idx.close()
# then delete the record
s.delete()
count += 1
s.close()
return count
def executeInsert(self, data, tx):
tblname = data.tableName()
p = TablePlan(tx, tblname, self.mdm)
# first, insert the record
s = p.open()
s.insert()
rid = s.getRid()
# then modify each field, inserting an index record if appropriate
indexes = self.mdm.getIndexInfo(tblname, tx)
valIter = data.vals().__iter__()
for fldname in data.fields():
val = valIter.__next__()
s.setVal(fldname, val)
ii = indexes.get(fldname)
if ii is not None:
idx = ii.open()
idx.insert(val, rid)
idx.close()
s.close()
return 1
def main(cls, args):
db = SimpleDB("studentdb")
mdm = db.mdMgr()
tx = db.newTx()
# Find the index on StudentId.
indexes = mdm.getIndexInfo("enroll", tx)
sidIdx = indexes.get("studentid")
# Get the plan for the Enroll table
enrollplan = TablePlan(tx, "enroll", mdm)
# Create the selection constant
c = Constant(6)
# Two different ways to use the index in simpledb:
IndexSelectTest.useIndexManually(sidIdx, enrollplan, c)
IndexSelectTest.useIndexScan(sidIdx, enrollplan, c)
tx.commit()
def main(cls, args):
# db = SimpleDB("studentdb")
fm = FileMgr(File("studentdb"), 400)
lm = LogMgr(fm, "simpledb.log")
bm = BufferMgr(fm, lm, 8)
tx = Transaction(fm, lm, bm)
isnew = fm.isNew()
mdm = MetadataMgr(isnew, tx)
# the STUDENT node
p1 = TablePlan(tx, "student", mdm)
# the Select node for "major = 10"
t = Term(Expression("majorid"), Expression(Constant(10)))
pred = Predicate(t)
p2 = SelectPlan(p1, pred)
# the Select node for "gradyear = 2020"
t2 = Term(Expression("gradyear"), Expression(Constant(2020)))
pred2 = Predicate(t2)
p3 = SelectPlan(p2, pred2)
# the Project node
c = ["sname", "majorid", "gradyear"]
p4 = ProjectPlan(p3, c)
# Look at R(p) and B(p) for each plan p.
SingleTablePlanTest.printStats(1, p1)
SingleTablePlanTest.printStats(2, p2)
SingleTablePlanTest.printStats(3, p3)
SingleTablePlanTest.printStats(4, p4)
# Change p2 to be p2, p3, or p4 to see the other scans in action.
# Changing p2 to p4 will throw an exception because SID is not in the projection list.
s = p2.open()
while s.next:
print(
str(s.getInt("sid")) + " " + s.getString("sname") + " " +
str(s.getInt("majorid")) + " " + str(s.getInt("gradyear")))
s.close()
def createPlan(self, data, tx):
# Step 1: Create a plan for each mentioned table or view.
plans = []
for tblname in data.tables():
viewdef = self.mdm.getViewDef(tblname, tx)
if viewdef is not None: # Recursively plan the view.
parser = Parser(viewdef)
viewdata = parser.query()
plans.append(self.createPlan(viewdata, tx))
else:
plans.append(TablePlan(tx, tblname, self.mdm))
# Step 2: Create the product of all table plans
p = plans.pop(0)
for nextplan in plans:
p = ProductPlan(p, nextplan)
# Step 3: Add a selection plan for the predicate
p = SelectPlan(p, data.pred())
# Step 4: Project on the field names
p = ProjectPlan(p, data.fields())
return p
def __init__(self, tblname, mypred, tx, mdm):
self.mypred = mypred
self.tx = tx
self.myplan = TablePlan(tx, tblname, mdm)
self.myschema = self.myplan.schema()
self.indexes = mdm.getIndexInfo(tblname, tx)
class TablePlanner(object):
#
# * Creates a new table planner.
# * The specified predicate applies to the entire query.
# * The table planner is responsible for determining
# * which portion of the predicate is useful to the table,
# * and when indexes are useful.
# * @param tblname the name of the table
# * @param mypred the query predicate
# * @param tx the calling transaction
#
def __init__(self, tblname, mypred, tx, mdm):
self.mypred = mypred
self.tx = tx
self.myplan = TablePlan(tx, tblname, mdm)
self.myschema = self.myplan.schema()
self.indexes = mdm.getIndexInfo(tblname, tx)
#
# * Constructs a select plan for the table.
# * The plan will use an indexselect, if possible.
# * @return a select plan for the table.
#
def makeSelectPlan(self):
p = self.makeIndexSelect()
if p is None:
p = self.myplan
return self.addSelectPred(p)
#
# * Constructs a join plan of the specified plan
# * and the table. The plan will use an indexjoin, if possible.
# * (Which means that if an indexselect is also possible,
# * the indexjoin operator takes precedence.)
# * The method returns null if no join is possible.
# * @param current the specified plan
# * @return a join plan of the plan and this table
#
def makeJoinPlan(self, current):
currsch = current.schema()
joinpred = self.mypred.joinSubPred(self.myschema, currsch)
if joinpred is None:
return None
p = self.makeIndexJoin(current, currsch)
if p is None:
p = self.makeProductJoin(current, currsch)
return p
#
# * Constructs a product plan of the specified plan and
# * this table.
# * @param current the specified plan
# * @return a product plan of the specified plan and this table
#
def makeProductPlan(self, current):
p = self.addSelectPred(self.myplan)
return MultibufferProductPlan(self.tx, current, p)
def makeIndexSelect(self):
for fldname in self.indexes.keys():
val = self.mypred.equatesWithConstant(fldname)
if val is not None:
ii = self.indexes.get(fldname)
print("index on " + fldname + " used")
return IndexSelectPlan(self.myplan, ii, val)
return None
def makeIndexJoin(self, current, currsch):
for fldname in self.indexes.keys():
outerfield = self.mypred.equatesWithField(fldname)
if outerfield is not None and currsch.hasField(outerfield):
ii = self.indexes.get(fldname)
p = IndexJoinPlan(current, self.myplan, ii, outerfield)
p = self.addSelectPred(p)
return self.addJoinPred(p, currsch)
return None
def makeProductJoin(self, current, currsch):
p = self.makeProductPlan(current)
return self.addJoinPred(p, currsch)
def addSelectPred(self, p):
selectpred = self.mypred.selectSubPred(self.myschema)
if selectpred is not None:
return SelectPlan(p, selectpred)
else:
return p
def addJoinPred(self, p, currsch):
joinpred = self.mypred.joinSubPred(currsch, self.myschema)
if joinpred is not None:
return SelectPlan(p, joinpred)
else:
return p
