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("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("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 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()
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()
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