def main(cls, args):
# db = SimpleDB("studentdb", 400, 8)
fm = FileMgr(File("recoverytest"), 400)
lm = LogMgr(fm, "simpledb.log")
filename = "simpledb.log"
lastblock = fm.length(filename) - 1
blk = BlockId(filename, lastblock)
p = Page(fm.blockSize())
fm.read(blk, p)
iterator = lm.iterator()
while iterator.hasNext():
byte_array = iterator.next()
rec = LogRecord.createLogRecord(byte_array)
print(rec)
def main(cls, args):
# db = SimpleDB("studentdb")
fm = FileMgr(File("plannertest2"), 400)
lm = LogMgr(fm, "simpledb.log")
bm = BufferMgr(fm, lm, 8)
tx = Transaction(fm, lm, bm)
isnew = fm.isNew()
mdm = MetadataMgr(isnew, tx)
qp = BasicQueryPlanner(mdm)
up = BasicUpdatePlanner(mdm)
planner = Planner(qp, up)
# part 1: Process a query
qry = "select sname, gradyear from student"
p = planner.createQueryPlan(qry, tx)
s = p.open()
while s.next:
print(s.getString("sname") + " " + str(s.getInt("gradyear")))
s.close()
# part 2: Process an update command
cmd = "delete from STUDENT where MajorId = 30"
num = planner.executeUpdate(cmd, tx)
print(str(num) + " students were deleted")
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 main(cls, args):
# db = SimpleDB("buffermgrtest", 400, 3) # only 3 buffers
# bm = db.bufferMgr()
fm = FileMgr(File("buffertest"), 400)
lm = LogMgr(fm, "logfile")
bm = BufferMgr(fm, lm, 3)
buff = [None] * 6
buff[0] = bm.pin(BlockId("testfile", 0))
buff[1] = bm.pin(BlockId("testfile", 1))
buff[2] = bm.pin(BlockId("testfile", 2))
bm.unpin(buff[1])
buff[1] = None
buff[3] = bm.pin(BlockId("testfile", 0)) # block 0 pinned twice
buff[4] = bm.pin(BlockId("testfile", 1)) # block 1 repinned
print("Available buffers: " + str(bm.available()))
try:
print("Attempting to pin block 3...")
buff[5] = bm.pin(BlockId("testfile",
3)) # will not work; no buffers left
except BufferAbortException as e:
print("Exception: No available buffers\n")
bm.unpin(buff[2])
buff[2] = None
buff[5] = bm.pin(BlockId("testfile", 3)) # now this works
print("Final Buffer Allocation:")
for i in range(len(buff)):
b = buff[i]
if b is not None:
print("buff[" + str(i) + "] pinned to block " +
b.block().__str__())
def main(cls, args):
# db = SimpleDB("tblmgrtest", 400, 8)
fm = FileMgr(File("tblmgrtest"), 400)
lm = LogMgr(fm, "simpledb.log")
bm = BufferMgr(fm, lm, 8)
tx = Transaction(fm, lm, bm)
tm = TableMgr(True, tx)
sch = Schema()
sch.addIntField("A")
sch.addStringField("B", 9)
tm.createTable("MyTable", sch, tx)
layout = tm.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)
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 main(cls, args):
# db = SimpleDB("plannertest1")
fm = FileMgr(File("plannertest1"), 400)
lm = LogMgr(fm, "simpledb.log")
bm = BufferMgr(fm, lm, 8)
tx = Transaction(fm, lm, bm)
isnew = fm.isNew()
mdm = MetadataMgr(isnew, tx)
qp = BasicQueryPlanner(mdm)
up = BasicUpdatePlanner(mdm)
planner = Planner(qp, up)
cmd = "create table T1(A int, B varchar(9))"
planner.executeUpdate(cmd, tx)
n = 200
print("Inserting " + str(n) + " random records.")
for i in range(n):
a = random.randint(0, 50)
b = "rec" + str(a)
cmd = "insert into T1(A,B) values(" + str(a) + ", '" + b + "')"
planner.executeUpdate(cmd, tx)
qry = "select B from T1 where A=10"
p = planner.createQueryPlan(qry, tx)
s = p.open()
while s.next:
print(s.getString("b"))
s.close()
tx.commit()
def __init__(self, *args):
if len(args) == 3:
#
# * A constructor useful for debugging.
# * @param dirname the name of the database directory
# * @param blocksize the block size
# * @param buffsize the number of buffers
#
dirname, blocksize, buffsize = args
dbDirectory = File(dirname)
self.fm = FileMgr(dbDirectory, blocksize)
self.lm = LogMgr(self.fm, SimpleDB.LOG_FILE)
self.bm = BufferMgr(self.fm, self.lm, buffsize)
else:
#
# * A simpler constructor for most situations. Unlike the
# * 3-arg constructor, it also initializes the metadata tables.
# * @param dirname the name of the database directory
#
dirname = args[0]
self.__init__(dirname, self.BLOCK_SIZE, self.BUFFER_SIZE)
tx = self.newTx()
isnew = self.fm.isNew()
if isnew:
print("creating new database")
else:
print("recovering existing database")
tx.recover()
self.mdm = MetadataMgr(isnew, tx)
qp = BasicQueryPlanner(self.mdm)
up = BasicUpdatePlanner(self.mdm)
# QueryPlanner qp = new HeuristicQueryPlanner(mdm);
# UpdatePlanner up = new IndexUpdatePlanner(mdm);
self._planner = Planner(qp, up)
tx.commit()
def main(cls, args):
# db = SimpleDB("recordtest", 400, 8)
fm = FileMgr(File("recordtest"), 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))
blk = tx.append("testfile")
tx.pin(blk)
rp = RecordPage(tx, blk, layout)
rp.format()
print("Filling the page with random records.")
slot = rp.insertAfter(-1)
while slot >= 0:
n = random.randint(0, 50)
rp.setInt(slot, "A", n)
rp.setString(slot, "B", "rec" + str(n))
print("inserting into slot " + str(slot) + ": {" + str(n) + ", " + "rec" + str(n) + "}")
slot = rp.insertAfter(slot)
print("Deleting these records, whose A-values are less than 25.")
count = 0
slot = rp.nextAfter(-1)
while slot >= 0:
a = rp.getInt(slot, "A")
b = rp.getString(slot, "B")
if a < 25:
count += 1
print("slot " + str(slot) + ": {" + str(a) + ", " + str(b) + "}")
rp.delete(slot)
slot = rp.nextAfter(slot)
print(str(count) + " values under 25 were deleted.\n")
print("Here are the remaining records.")
slot = rp.nextAfter(-1)
while slot >= 0:
a = rp.getInt(slot, "A")
b = rp.getString(slot, "B")
print("slot " + str(slot) + ": {" + str(a) + ", " + str(b) + "}")
slot = rp.nextAfter(slot)
tx.unpin(blk)
tx.commit()
def main(cls, args):
# initialize the database system
# db = SimpleDB("concurrencytest", 400, 8)
cls.fm = FileMgr(File("recoverytest"), 400)
cls.lm = LogMgr(cls.fm, "simpledb.log")
cls.bm = BufferMgr(cls.fm, cls.lm, 8)
a = cls.A()
a.start()
b = cls.B()
b.start()
c = cls.C()
c.start()
class LogTest(object):
fm = FileMgr(File("filetest"), 400)
lm = LogMgr(fm, "logfile")
@classmethod
def main(cls, args):
# cls.createRecords(1, 35)
# cls.printLogRecords("The log file now has these records:")
# db = SimpleDB("logtest", 400, 8)
# cls.lm = db.logMgr()
cls.printLogRecords("The initial empty log file:")
# print an empty log file
print("done")
cls.createRecords(1, 35)
cls.printLogRecords("The log file now has these records:")
cls.createRecords(36, 70)
cls.lm.flush(65)
cls.printLogRecords("The log file now has these records:")
@classmethod
def printLogRecords(cls, msg):
print(msg)
iterator = cls.lm.iterator()
while iterator.hasNext():
rec = iterator.next()
p = Page(rec)
s = p.getString(0)
npos = Page.maxLength(len(s))
val = p.getInt(npos)
print("[" + s + ", " + str(val) + "]")
print()
@classmethod
def createRecords(cls, start, end):
print("Creating records: ")
for i in range(start, end + 1):
rec = cls.createLogRecord("record" + str(i), i + 100)
lsn = cls.lm.append(rec)
print(str(lsn) + " ", end='')
print()
# Create a log record having two values: a string and an integer.
@classmethod
def createLogRecord(cls, s, n):
spos = 0
npos = spos + Page.maxLength(len(s))
b = bytearray(npos + Integer.BYTES)
p = Page(b)
p.setString(spos, s)
p.setInt(npos, n)
return b
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("recoverytest", 400, 8)
cls.fm = FileMgr(File("recoverytest"), 400)
cls.lm = LogMgr(cls.fm, "simpledb.log")
cls.bm = BufferMgr(cls.fm, cls.lm, 8)
cls.blk0 = BlockId("testfile", 0)
cls.blk1 = BlockId("testfile", 1)
if cls.fm.length("testfile") == 0:
cls.initialize()
cls.modify()
else:
cls.recover()
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 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 main(cls, args):
# db = SimpleDB("txtest", 400, 8)
fm = FileMgr(File("txtest"), 400)
lm = LogMgr(fm, "simpledb.log")
bm = BufferMgr(fm, lm, 8)
tx1 = Transaction(fm, lm, bm)
blk = BlockId("testfile", 1)
tx1.pin(blk)
# The block initially contains unknown bytes,
# so don't log those values here.
tx1.setInt(blk, 80, 1, False)
tx1.setString(blk, 40, "one", False)
tx1.commit()
tx2 = Transaction(fm, lm, bm)
tx2.pin(blk)
ival = tx2.getInt(blk, 80)
sval = tx2.getString(blk, 40)
print("initial value at location 80 = " + str(ival))
print("initial value at location 40 = " + str(sval))
newival = ival + 1
newsval = sval + "!"
tx2.setInt(blk, 80, newival, True)
tx2.setString(blk, 40, newsval, True)
tx2.commit()
tx3 = Transaction(fm, lm, bm)
tx3.pin(blk)
print("new value at location 80 = " + str(tx3.getInt(blk, 80)))
print("new value at location 40 = " + tx3.getString(blk, 40))
tx3.setInt(blk, 80, 9999, True)
print("pre-rollback value at location 80 = " +
str(tx3.getInt(blk, 80)))
tx3.rollback()
tx4 = Transaction(fm, lm, bm)
tx4.pin(blk)
print("post-rollback at location 80 = " + str(tx4.getInt(blk, 80)))
tx4.commit()
def main(cls, args):
# db = SimpleDB("bufferfiletest", 400, 8)
# bm = db.bufferMgr()
fm = FileMgr(File("buffertest"), 400)
lm = LogMgr(fm, "logfile")
bm = BufferMgr(fm, lm, 8)
blk = BlockId("testfile", 2)
pos1 = 88
b1 = bm.pin(blk)
p1 = b1.contents()
p1.setString(pos1, "abcdefghijklm")
size = Page.maxLength(len("abcdefghijklm"))
pos2 = pos1 + size
p1.setInt(pos2, 345)
b1.setModified(1, 0)
bm.unpin(b1)
b2 = bm.pin(blk)
p2 = b2.contents()
print("offset " + str(pos2) + " contains " + str(p2.getInt(pos2)))
print("offset " + str(pos1) + " contains " + p2.getString(pos1))
bm.unpin(b2)
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("buffertest", 400, 3) # only 3 buffers
# bm = db.bufferMgr()
fm = FileMgr(File("buffertest"), 400)
lm = LogMgr(fm, "logfile")
bm = BufferMgr(fm, lm, 3)
buff1 = bm.pin(BlockId("testfile", 1))
p = buff1.contents()
n = p.getInt(80)
p.setInt(80, n + 1)
buff1.setModified(1, 0) # placeholder values
print("The new value is " + str(n + 1))
bm.unpin(buff1)
# One of these pins will flush buff1 to disk:
buff2 = bm.pin(BlockId("testfile", 2))
buff3 = bm.pin(BlockId("testfile", 3))
buff4 = bm.pin(BlockId("testfile", 4))
bm.unpin(buff2)
buff2 = bm.pin(BlockId("testfile", 1))
p2 = buff2.contents()
p2.setInt(80, 9999) # This modification
buff2.setModified(1, 0) # won't get written to disk.
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()