def createView(self, vname, vdef, tx):
layout = self.tblMgr.getLayout("viewcat", tx)
ts = TableScan(tx, "viewcat", layout)
ts.insert()
ts.setString("viewname", vname)
ts.setString("viewdef", vdef)
ts.close()
def createIndex(self, idxname, tblname, fldname, tx):
ts = TableScan(tx, "idxcat", self.layout)
ts.insert()
ts.setString("indexname", idxname)
ts.setString("tablename", tblname)
ts.setString("fieldname", fldname)
ts.close()
def getViewDef(self, vname, tx):
result = None
layout = self.tblMgr.getLayout("viewcat", tx)
ts = TableScan(tx, "viewcat", layout)
while ts.next():
if ts.getString("viewname") == vname:
result = ts.getString("viewdef")
break
ts.close()
return result
def calcTableStats(self, tblname, layout, tx):
numRecs = 0
numblocks = 0
ts = TableScan(tx, tblname, layout)
while ts.next():
numRecs += 1
numblocks = ts.getRid().blockNumber() + 1
ts.close()
return StatInfo(numblocks, numRecs)
def getIndexInfo(self, tblname, tx):
result = {}
ts = TableScan(tx, "idxcat", self.layout)
while ts.next():
if ts.getString("tablename") == tblname:
idxname = ts.getString("indexname")
fldname = ts.getString("fieldname")
tblLayout = self.tblmgr.getLayout(tblname, tx)
tblsi = self.statmgr.getStatInfo(tblname, tblLayout, tx)
ii = IndexInfo(idxname, fldname, tblLayout.schema(), tx, tblsi)
result[fldname] = ii
ts.close()
return result
def refreshStatistics(self, tx):
self.tablestats = {}
self.numcalls = 0
tcatlayout = self.tblMgr.getLayout("tblcat", tx)
tcat = TableScan(tx, "tblcat", tcatlayout)
while tcat.next():
tblname = tcat.getString("tblname")
layout = self.tblMgr.getLayout(tblname, tx)
self.lock.release()
si = self.calcTableStats(tblname, layout, tx)
self.lock.acquire()
self.tablestats[tblname] = si
tcat.close()
def main(cls, args):
# db = SimpleDB("scantest2")
fm = FileMgr(File("scantest2"), 400)
lm = LogMgr(fm, "simpledb.log")
bm = BufferMgr(fm, lm, 8)
tx = Transaction(fm, lm, bm)
sch1 = Schema()
sch1.addIntField("A")
sch1.addStringField("B", 9)
layout1 = Layout(sch1)
us1 = TableScan(tx, "T1", layout1)
us1.beforeFirst()
n = 200
print("Inserting " + str(n) + " records into T1.")
for i in range(n):
us1.insert()
us1.setInt("A", i)
us1.setString("B", "bbb" + str(i))
us1.close()
sch2 = Schema()
sch2.addIntField("C")
sch2.addStringField("D", 9)
layout2 = Layout(sch2)
us2 = TableScan(tx, "T2", layout2)
us2.beforeFirst()
print("Inserting " + str(n) + " records into T2.")
for i in range(n):
us2.insert()
us2.setInt("C", n - i - 1)
us2.setString("D", "ddd" + str((n - i - 1)))
us2.close()
s1 = TableScan(tx, "T1", layout1)
s2 = TableScan(tx, "T2", layout2)
s3 = ProductScan(s1, s2)
# selecting all records where A=C
t = Term(Expression("A"), Expression("C"))
pred = Predicate(t)
print("The predicate is " + pred.__str__())
s4 = SelectScan(s3, pred)
# projecting on [B,D]
c = ["B", "D"]
s5 = ProjectScan(s4, c)
while s5.next():
print(s5.getString("B") + " " + s5.getString("D"))
s5.close()
tx.commit()
def main(cls, args):
# db = SimpleDB("metadatamgrtest", 400, 8)
fm = FileMgr(File("metadatamgrtest"), 400)
lm = LogMgr(fm, "simpledb.log")
bm = BufferMgr(fm, lm, 8)
tx = Transaction(fm, lm, bm)
mdm = MetadataMgr(True, tx)
sch = Schema()
sch.addIntField("A")
sch.addStringField("B", 9)
# Part 1: Table Metadata
mdm.createTable("MyTable", sch, tx)
layout = mdm.getLayout("MyTable", tx)
size = layout.slotSize()
sch2 = layout.schema()
print("MyTable has slot size " + str(size))
print("Its fields are:")
for fldname in sch2.fields():
if sch2.type(fldname) == INTEGER:
_type = "int"
else:
strlen = sch2.length(fldname)
_type = "varchar(" + str(strlen) + ")"
print(fldname + ": " + _type)
# Part 2: Statistics Metadata
ts = TableScan(tx, "MyTable", layout)
for i in range(50):
ts.insert()
n = random.randint(0, 50)
ts.setInt("A", n)
ts.setString("B", "rec" + str(n))
si = mdm.getStatInfo("MyTable", layout, tx)
print("B(MyTable) = " + str(si.blocksAccessed()))
print("R(MyTable) = " + str(si.recordsOutput()))
print("V(MyTable,A) = " + str(si.distinctValues("A")))
print("V(MyTable,B) = " + str(si.distinctValues("B")))
# Part 3: View Metadata
viewdef = "select B from MyTable where A = 1"
mdm.createView("viewA", viewdef, tx)
v = mdm.getViewDef("viewA", tx)
print("View def = " + v)
# Part 4: Index Metadata
mdm.createIndex("indexA", "MyTable", "A", tx)
mdm.createIndex("indexB", "MyTable", "B", tx)
idxmap = mdm.getIndexInfo("MyTable", tx)
ii = idxmap.get('A')
print(ii)
print("B(indexA) = " + str(ii.blocksAccessed()))
print("R(indexA) = " + str(ii.recordsOutput()))
print("V(indexA,A) = " + str(ii.distinctValues("A")))
print("V(indexA,B) = " + str(ii.distinctValues("B")))
ii = idxmap.get("B")
print("B(indexB) = " + str(ii.blocksAccessed()))
print("R(indexB) = " + str(ii.recordsOutput()))
print("V(indexB,A) = " + str(ii.distinctValues("A")))
print("V(indexB,B) = " + str(ii.distinctValues("B")))
tx.commit()
def open(self):
return TableScan(self.tx, self.tblname, self.layout)
def main(cls, args):
# db = SimpleDB("scantest1")
fm = FileMgr(File("scantest1"), 400)
lm = LogMgr(fm, "simpledb.log")
bm = BufferMgr(fm, lm, 8)
tx = Transaction(fm, lm, bm)
sch1 = Schema()
sch1.addIntField("A")
sch1.addStringField("B", 9)
layout = Layout(sch1)
s1 = TableScan(tx, "T", layout)
s1.beforeFirst()
n = 200
print("Inserting " + str(n) + " random records.")
for i in range(n):
s1.insert()
k = random.randint(0, 50)
s1.setInt("A", k)
s1.setString("B", "rec" + str(k))
s1.close()
s2 = TableScan(tx, "T", layout)
# selecting all records where A=10
c = Constant(10)
t = Term(Expression("A"), Expression(c))
pred = Predicate(t)
print("The predicate is " + pred.__str__())
s3 = SelectScan(s2, pred)
fields = ["B"]
s4 = ProjectScan(s3, fields)
while s4.next():
print(s4.getString("B"))
s4.close()
tx.commit()
def main(cls, args):
# db = SimpleDB("tabletest", 400, 8)
fm = FileMgr(File("tabletest"), 400)
lm = LogMgr(fm, "simpledb.log")
bm = BufferMgr(fm, lm, 8)
tx = Transaction(fm, lm, bm)
tm = TableMgr(True, tx)
tcatLayout = tm.getLayout("tblcat", tx)
print("Here are all the tables and their lengths.")
ts = TableScan(tx, "tblcat", tcatLayout)
while ts.next():
tname = ts.getString("tblname")
slotsize = ts.getInt("slotsize")
print(tname + " " + str(slotsize))
ts.close()
print("\nHere are the fields for each table and their offsets")
fcatLayout = tm.getLayout("fldcat", tx)
ts = TableScan(tx, "fldcat", fcatLayout)
while ts.next():
tname = ts.getString("tblname")
fname = ts.getString("fldname")
offset = ts.getInt("offset")
print(tname + " " + fname + " " + str(offset))
ts.close()
def getLayout(self, tblname, tx):
size = -1
tcat = TableScan(tx, "tblcat", self.tcatLayout)
while tcat.next():
if tcat.getString("tblname") == tblname:
size = tcat.getInt("slotsize")
break
tcat.close()
sch = Schema()
offsets = {}
fcat = TableScan(tx, "fldcat", self.fcatLayout)
while fcat.next():
if fcat.getString("tblname") == tblname:
fldname = fcat.getString("fldname")
fldtype = fcat.getInt("type")
fldlen = fcat.getInt("length")
offset = fcat.getInt("offset")
offsets[fldname] = offset
sch.addField(fldname, fldtype, fldlen)
fcat.close()
return Layout(sch, offsets, size)
def beforeFirst(self, searchkey):
self.close()
self.searchkey = searchkey
bucket = searchkey.__hash__() % HashIndex.NUM_BUCKETS
tblname = self.idxname + bucket
self.ts = TableScan(self.tx, tblname, self.layout)
class HashIndex(Index):
NUM_BUCKETS = 100
#
# * Opens a hash index for the specified index.
# * @param idxname the name of the index
# * @param sch the schema of the index records
# * @param tx the calling transaction
#
def __init__(self, tx, idxname, layout):
super(HashIndex, self).__init__()
self.tx = tx
self.idxname = idxname
self.layout = layout
#
# * Positions the index before the first index record
# * having the specified search key.
# * The method hashes the search key to determine the bucket,
# * and then opens a table scan on the file
# * corresponding to the bucket.
# * The table scan for the previous bucket (if any) is closed.
# * @see simpledb.index.Index#beforeFirst(simpledb.query.Constant)
#
def beforeFirst(self, searchkey):
self.close()
self.searchkey = searchkey
bucket = searchkey.__hash__() % HashIndex.NUM_BUCKETS
tblname = self.idxname + bucket
self.ts = TableScan(self.tx, tblname, self.layout)
#
# * Moves to the next record having the search key.
# * The method loops through the table scan for the bucket,
# * looking for a matching record, and returning false
# * if there are no more such records.
# * @see simpledb.index.Index#next()
#
def next(self):
while self.ts.next():
if self.ts.getVal("dataval") == self.searchkey:
return True
return False
#
# * Retrieves the dataRID from the current record
# * in the table scan for the bucket.
# * @see simpledb.index.Index#getDataRid()
#
def getDataRid(self):
blknum = self.ts.getInt("block")
_id = self.ts.getInt("id")
return RID(blknum, _id)
#
# * Inserts a new record into the table scan for the bucket.
# * @see simpledb.index.Index#insert(simpledb.query.Constant, simpledb.record.RID)
#
def insert(self, val, rid):
self.beforeFirst(val)
self.ts.insert()
self.ts.setInt("block", rid.blockNumber())
self.ts.setInt("id", rid.slot())
self.ts.setVal("dataval", val)
#
# * Deletes the specified record from the table scan for
# * the bucket. The method starts at the beginning of the
# * scan, and loops through the records until the
# * specified record is found.
# * @see simpledb.index.Index#delete(simpledb.query.Constant, simpledb.record.RID)
#
def delete(self, val, rid):
self.beforeFirst(val)
while self.next():
if self.getDataRid() == rid:
self.ts.delete()
return
#
# * Closes the index by closing the current table scan.
# * @see simpledb.index.Index#close()
#
def close(self):
if self.ts is not None:
self.ts.close()
#
# * Returns the cost of searching an index file having the
# * specified number of blocks.
# * The method assumes that all buckets are about the
# * same size, and so the cost is simply the size of
# * the bucket.
# * @param numblocks the number of blocks of index records
# * @param rpb the number of records per block (not used here)
# * @return the cost of traversing the index
#
@staticmethod
def searchCost(numblocks, rpb):
return numblocks / HashIndex.NUM_BUCKETS
def main(cls, args):
# db = SimpleDB("tabletest", 400, 8)
fm = FileMgr(File("tabletest"), 400)
lm = LogMgr(fm, "simpledb.log")
bm = BufferMgr(fm, lm, 8)
tx = Transaction(fm, lm, bm)
sch = Schema()
sch.addIntField("A")
sch.addStringField("B", 9)
layout = Layout(sch)
for fldname in layout.schema().fields():
offset = layout.offset(fldname)
print(fldname + " has offset " + str(offset))
print("Filling the table with 50 random records.")
ts = TableScan(tx, "T", layout)
for i in range(50):
ts.insert()
n = random.randint(0, 50)
ts.setInt("A", n)
ts.setString("B", "rec" + str(n))
print("inserting into slot " + ts.getRid().__str__() + ": {" +
str(n) + ", " + "rec" + str(n) + "}")
print("Deleting these records, whose A-values are less than 25.")
count = 0
ts.beforeFirst()
while ts.next():
a = ts.getInt("A")
b = ts.getString("B")
if a < 25:
count += 1
print("slot " + ts.getRid().__str__() + ": {" + str(a) + ", " +
b + "}")
ts.delete()
print(str(count) + " values under 10 were deleted.\n")
print("Here are the remaining records.")
ts.beforeFirst()
while ts.next():
a = ts.getInt("A")
b = ts.getString("B")
print("slot " + ts.getRid().__str__() + ": {" + str(a) + ", " + b +
"}")
ts.close()
tx.commit()
def main(cls, args):
# db = SimpleDB("producttest")
fm = FileMgr(File("producttest"), 400)
lm = LogMgr(fm, "simpledb.log")
bm = BufferMgr(fm, lm, 8)
tx = Transaction(fm, lm, bm)
sch1 = Schema()
sch1.addIntField("A")
sch1.addStringField("B", 9)
layout1 = Layout(sch1)
ts1 = TableScan(tx, "T1", layout1)
sch2 = Schema()
sch2.addIntField("C")
sch2.addStringField("D", 9)
layout2 = Layout(sch2)
ts2 = TableScan(tx, "T2", layout2)
ts1.beforeFirst()
n = 200
print("Inserting " + str(n) + " records into T1.")
for i in range(n):
ts1.insert()
ts1.setInt("A", i)
ts1.setString("B", "aaa" + str(i))
ts1.close()
ts2.beforeFirst()
print("Inserting " + str(n) + " records into T2.")
for i in range(n):
ts2.insert()
ts2.setInt("C", n - i - 1)
ts2.setString("D", "bbb" + str((n - i - 1)))
ts2.close()
s1 = TableScan(tx, "T1", layout1)
s2 = TableScan(tx, "T2", layout2)
s3 = ProductScan(s1, s2)
while s3.next():
print(s3.getString("B"))
s3.close()
tx.commit()
def createTable(self, tblname, sch, tx):
layout = Layout(sch)
# insert one record into tblcat
tcat = TableScan(tx, "tblcat", self.tcatLayout)
tcat.insert()
tcat.setString("tblname", tblname)
tcat.setInt("slotsize", layout.slotSize())
tcat.close()
# insert a record into fldcat for each field
fcat = TableScan(tx, "fldcat", self.fcatLayout)
for fldname in sch.fields():
fcat.insert()
fcat.setString("tblname", tblname)
fcat.setString("fldname", fldname)
fcat.setInt("type", sch.type(fldname))
fcat.setInt("length", sch.length(fldname))
fcat.setInt("offset", layout.offset(fldname))
fcat.close()
