void deleteFromTable(string tableName, string whereCondition) { if(whereCondition.empty()){ if(!schemaManager.relationExists(tableName)) { cout<<"Illegal Table Name"<<endl; return; } Relation *relation = schemaManager.getRelation(tableName); while(relation->getNumOfBlocks()) relation->deleteBlocks(relation->getNumOfBlocks()-1); } else { Relation *relation = schemaManager.getRelation(tableName); for(int i=0;i<relation->getNumOfBlocks();i++) { relation->getBlock(i,0); Block *block = mainMemory.getBlock(0); vector<Tuple> tuples = block->getTuples(); for(int j=0;j<tuples.size();j++) { if(!tuples[j].isNull() && whereConditionEvaluator(whereCondition, tuples[j])) { block->nullTuple(j); } } relation->setBlock(i,0); } } }
void dropTable(string tableName) { if(schemaManager.relationExists(tableName)) { schemaManager.deleteRelation(tableName); } else cout<<"Table "<<tableName<<" doesn't exist"<<endl; }
int main(int argc, char ** argv) { std::cout << "Hello :-)" << std::endl; BufferManager* bm = new BufferManager(20); SchemaManager* sm = new SchemaManager(*bm); sm->dropSchema("test"); int size = sizeof(struct Schema) + 1*sizeof(struct Schema::Attribute); struct Schema * schema = reinterpret_cast<struct Schema *>(malloc(size)); schema->attr_count = 1; schema->primary_key_index[0] = 0; schema->primary_key_index[1] = 5; schema->primary_key_index[2] = 5; schema->primary_key_index[3] = 5; schema->primary_key_index[4] = 5; strncpy(schema->name, "test", 11); schema->attributes[0].type = Schema::Attribute::Type::Integer; strncpy(schema->attributes[0].name, "id", 3); schema->attributes[0].notNull = true; sm->addSchema(*schema); int size2 = sizeof(struct Schema) + 2*sizeof(struct Schema::Attribute); struct Schema * schema2 = reinterpret_cast<struct Schema *>(malloc(size2)); schema2->attr_count = 2; schema2->primary_key_index[0] = 0; schema2->primary_key_index[1] = 5; schema2->primary_key_index[2] = 5; schema2->primary_key_index[3] = 5; schema2->primary_key_index[4] = 5; strncpy(schema2->name, "test-table", 11); schema2->attributes[0].type = Schema::Attribute::Type::Integer; strncpy(schema2->attributes[0].name, "id", 3); schema2->attributes[0].notNull = true; schema2->attributes[1].type = Schema::Attribute::Type::Varchar; schema2->attributes[1].length = 20; strncpy(schema2->attributes[1].name, "name", 5); schema2->attributes[1].notNull = true; sm->addSchema(*schema2); sm->incrementPagesCount("test-table"); sm->incrementPagesCount("test"); const struct Schema * schema3 = sm->getSchema("test"); std::cout << schema3->name << ": " << schema3->page_count << std::endl; const struct Schema * schema4 = sm->getSchema("test-table"); std::cout << schema4->name << ": " << schema4->page_count << std::endl; const struct Schema * nullSchema = sm->getSchema("nonexistent"); std::cout << (nullSchema == NULL) << std::endl; delete sm; delete bm; return 0; }
/* void addTupleToBlock(Tuple tuple, MainMemory &mem) { //===================Block============================= cout << "===================Block=============================" << endl; // Set up a block in the memory // cout << "Clear the memory block 0" << endl; // Block* block_ptr=mem.getBlock(0); //access to memory block 0 // block_ptr->clear(); //clear the block // A block stores at most 2 tuples in this case // -----------first tuple----------- cout << "Set the tuple at offset 0 of the memory block 0" << endl; block_ptr->setTuple(0,tuple); // You can also use appendTuple() cout << "Now the memory block 0 contains:" << endl; cout << *block_ptr << endl; cout << "The block is full? " << (block_ptr->isFull()==1?"true":"false") << endl; cout << "The block currently has " << block_ptr->getNumTuples() << " tuples" << endl; cout << "The tuple at offset 0 of the block is:" << endl; cout << block_ptr->getTuple(0) << endl << endl; return; } */ void processInsert(string line, vector<string> cmdStr, SchemaManager schema_manager, MainMemory &mem) { string tableName = cmdStr[2]; Relation* relation_ptr = schema_manager.getRelation(tableName); cout << "Inside the INSERT function" << endl; int memory_block_index=0; //====================Tuple============================= cout << "====================Tuple=============================" << endl; // Set up the first tuple Tuple tuple = relation_ptr->createTuple(); //The only way to create a tuple is to call "Relation" printRelation(relation_ptr); vector<string> insertStr = splitString(line, "\()"); vector<string> attr = splitString(insertStr[1], ", "); vector<string> val = splitString(insertStr[3], ", "); /* TODO commented unordered insert giving errors for (int i = 0; i < attr.size(); i++) { if (tuple.getSchema().getFieldType(i)==INT){ cout << "Errored?? " << endl; tuple.setField(attr[i], atoi(val[i].c_str())); } else{ tuple.setField(attr[i], val[i]); } } */ for (int i = 0; i < attr.size(); i++) { //cout << "HAHAHHAHHA " << tuple.getField(attr[i])[0] << endl << endl; if (tuple.getSchema().getFieldType(attr[i])==INT){ //if (tuple.getField(attr[i])==INT){ cout << "Errored?? " << endl; tuple.setField(attr[i], atoi(val[i].c_str())); } else{ tuple.setField(attr[i], val[i]); } } // TODO to send file //see that tuple was properly filled printTuple(tuple); cout << "My test function on blocks " << endl; memory_block_index = findBlockForTuple(mem); appendTupleToRelation(relation_ptr, mem, memory_block_index, tuple); //addTupleToBlock(tuple, mem); cout << "After insertion of tuble now the reation stuff is " << endl; printRelation(relation_ptr); // Now write the tuple in a Disk Block. /* vector<string>::iterator it; cout << "Printing new things" << endl; for(it=str.begin(); it!=str.end(); ++it) { cout << *it << endl; } */ return; }
void preProcess(const vector<string> &tables, vector<string> &words, SchemaManager &schema_manager){ for (int i = 0; i < words.size(); i++){ bool is_column = false; // has no "." for (int j = 0; j < tables.size(); j++){ if (words[i].find('.') == string::npos){ if (schema_manager.getSchema(tables[j]).fieldNameExists(words[i])){ words[i] = tables[j] + "." + words[i]; is_column = true; break; } } // term or value if (!is_column){ string legal_word; // removing tailing and head spaces for our custom test case string::iterator it = words[i].begin(); while(it != words[i].end() && *it == ' ') words[i].erase(it++); reverse(words[i].begin(), words[i].end()); it = words[i].begin(); while(it != words[i].end() && *it == ' ') words[i].erase(it++); reverse(words[i].begin(), words[i].end()); for (int k = 0; k < words[i].size(); k++){ if (words[i][k] != '"'){ legal_word.push_back(words[i][k]); } } words[i] = legal_word; } } } }
void printSelectStar(vector<string> attrVec, vector<string> tableVec, SchemaManager schema_manager) { if(tableVec.size() == 1) { Relation* relation_ptr = schema_manager.getRelation(tableVec[0]); cout << "Found relation " << tableVec[0] << endl; cout << *relation_ptr << endl << endl; } }
void insertIntoTable(string tableName, vector<string> fieldNames, vector<string> fieldValues) { if(!schemaManager.relationExists(tableName)) { cout<<"Illegal Tablename"<<endl; return; } Relation *relation = schemaManager.getRelation(tableName); Tuple tuple = relation->createTuple(); Schema schema = relation->getSchema(); vector<string>::iterator it,it1; for(it = fieldNames.begin(),it1 = fieldValues.begin();it!=fieldNames.end();it++, it1++) { string str=*it,str1=*it1; str = removeSpaces(str); int type = schema.getFieldType(str); if(!type) { str1 = removeSpaces(str1); if(isNumber(str1)) { tuple.setField(str,stoi(str1)); } else { cout<<"Data type is not supported\n"; return; } } else { regex exp("\\ *\"(.*)\""); cmatch match; if(regex_match(str1.c_str(),match,exp)) { str1 = match[1]; if(str1.length()>20) { cout<<"Data type is not supported\n"; return; } else tuple.setField(str,str1); } else { cout<<"Data type is not supported\n"; return; } } } insertTuple(tableName, tuple); cout<<disk.getDiskIOs()<<endl; }
bool validate(vector<string> tableNames) { for(int i=0;i<tableNames.size();i++) { if(!schemaManager.relationExists(tableNames[i])) { cout<<"Invalid Table Name "<< tableNames[i]<<endl; return true; } } return false; }
void join(Tuple tuple1, Tuple tuple2, string tableName1, string tableName2, string whereCondition, bool multi, vector<string> attributes) { Relation *relation = schemaManager.getRelation(tableName2+"_join"); Tuple tuple =relation->createTuple(); if(!multi) { for(int i=0;i<tuple1.getNumOfFields();i++) { if(tuple1.getSchema().getFieldType(i) == INT) tuple.setField(tableName1+"."+tuple1.getSchema().getFieldName(i), tuple1.getField(i).integer); else tuple.setField(tableName1+"."+tuple1.getSchema().getFieldName(i), *(tuple1.getField(i).str) ); } } else { for(int i=0;i<tuple1.getNumOfFields();i++) { if(tuple1.getSchema().getFieldType(i) == INT) tuple.setField(tuple1.getSchema().getFieldName(i), tuple1.getField(i).integer); else tuple.setField(tuple1.getSchema().getFieldName(i), *(tuple1.getField(i).str) ); } } for(int i=0;i<tuple2.getNumOfFields();i++) { if(tuple2.getSchema().getFieldType(i) == INT) tuple.setField(tableName2+"."+tuple2.getSchema().getFieldName(i), tuple2.getField(i).integer); else tuple.setField(tableName2+"."+tuple2.getSchema().getFieldName(i), *(tuple2.getField(i).str) ); } if((attributes.size()==1 && attributes[0]=="*") || multi) { if(whereConditionEvaluator(whereCondition, tuple)) insertTuple(tableName2+"_join", tuple); } else { Relation *relation1 = schemaManager.getRelation(tableName2+"_joinp"); Tuple tuplep = relation1->createTuple(); for(int i=0;i<attributes.size();i++) { if(tuplep.getSchema().getFieldType(attributes[i]) == INT) tuplep.setField(attributes[i], tuple.getField(attributes[i]).integer); else tuplep.setField(attributes[i], *(tuple.getField(attributes[i]).str)); } if(whereConditionEvaluator(whereCondition, tuple)) insertTuple(tableName2+"_joinp", tuplep); } }
/* * database access functions * create, drop, insert, delete, select statements */ void createTable(string tableName, vector<string> fieldNames, vector<string> fieldTypes) { vector<enum FIELD_TYPE> enumTypes; vector<string>::iterator it; for(it = fieldTypes.begin();it!=fieldTypes.end();it++){ string str = *it; if(str=="INT"||str=="int") enumTypes.push_back(INT); else enumTypes.push_back(STR20); } Schema schema(fieldNames,enumTypes); Relation *relation = schemaManager.createRelation(tableName,schema); }
void processInsert(string line, vector<string> cmdStr, SchemaManager schema_manager, MainMemory &mem) { string tableName = cmdStr[2]; Relation* relation_ptr = schema_manager.getRelation(tableName); cout << "Inside the INSERT function" << endl; //====================Tuple============================= cout << "====================Tuple=============================" << endl; // Set up the first tuple Tuple tuple = relation_ptr->createTuple(); //The only way to create a tuple is to call "Relation" //printRelation(relation_ptr); vector<string> insertStr = splitString(line, "\()"); vector<string> attr = splitString(insertStr[1], ", "); vector<string> val = splitString(insertStr[3], ", "); for (int i = 0; i < attr.size(); i++) { if (tuple.getSchema().getFieldType(i)==INT){ tuple.setField(attr[i], atoi(val[i].c_str())); } else{ tuple.setField(attr[i], val[i]); } } //see that tuple was properly filled printTuple(tuple); // Now write the tuple in a Disk Block. /* vector<string>::iterator it; cout << "Printing new things" << endl; for(it=str.begin(); it!=str.end(); ++it) { cout << *it << endl; } */ return; }
void insertTuple(string tableName, Tuple tuple) { Relation *relation = schemaManager.getRelation(tableName); if(relation->getNumOfBlocks()>0){ relation->getBlock(relation->getNumOfBlocks()-1,9); Block *block = mainMemory.getBlock(9); if(block->isFull()){ block->clear(); block->appendTuple(tuple); relation->setBlock(relation->getNumOfBlocks(),9); } else { block->appendTuple(tuple); relation->setBlock(relation->getNumOfBlocks()-1,9); } } else { Block *block = mainMemory.getBlock(9); block->clear(); block->setTuple(0,tuple); relation->setBlock(0,9); } }
//comparision operator defined to compare two tables based on table size //Each table size is evaluated in terms of no of blocks and not the no //of tuples bool CompareTableSize(string relOne, string relTwo) { int noOfBlocksOne = schema_manager.getRelation(relOne)->getNumOfBlocks(); int noOfBlocksTwo = schema_manager.getRelation(relTwo)->getNumOfBlocks(); return noOfBlocksOne < noOfBlocksTwo; };
/** * Perform the initial setup of tables and data. */ int initial_setup() { cout << endl << "INITIAL SETUP BEGINS........................................." << endl; cout << "............................................................." << endl << endl; run_query ("create table course (sid int, homework int, project int, exam int, grade str20)",0,0,0,false); run_query ("create table course2 (sid int, homework int, project int, exam int, grade str20)",0,0,0,false); srand(time(NULL)); string msg; char buffer[50], rr[6]; int r = 0; cout << "How many initial random tuples do you want the course table to have? "; gets (rr); r = atoi(rr); if ( r<0 || r > 100000 ) return -1; cout << endl << endl; for (int i = 0; i<r; i++) { msg = "insert into course(sid, homework,grade, project, exam) values("; itoa(rand()%200, buffer, 10); msg += buffer; msg+= ","; itoa(rand()%100, buffer, 10); msg += buffer; msg+= ",\"stt"; itoa(rand()%10, buffer, 10); msg += buffer; msg+= "\","; itoa(rand()%50, buffer, 10); msg += buffer; msg+= ","; itoa(rand()%5, buffer, 10); msg += buffer; msg+= ")"; cout << msg << endl; run_query (msg,0,0,0,false); msg = "insert into course2(sid, homework,grade, project, exam) values("; itoa(rand()%200, buffer, 10); msg += buffer; msg+= ","; itoa(rand()%100, buffer, 10); msg += buffer; msg+= ",\"stt"; itoa(rand()%10, buffer, 10); msg += buffer; msg+= "\","; itoa(rand()%50, buffer, 10); msg += buffer; msg+= ","; itoa(rand()%5, buffer, 10); msg += buffer; msg+= ")"; cout << msg << endl; run_query (msg,0,0,0,false); } Relation* thisrelation = schemaMgr.getRelation("course"); thisrelation->printRelation(); cout << endl << "Initial setup complete.." << endl << endl; return 0; }
//compare two tables based on number of blocks bool Size_Comparison(string first_relation, string second_relation) { int blocks_first_relation = schema_manager.getRelation(first_relation)->getNumOfBlocks(); int blocks_second_relation = schema_manager.getRelation(second_relation)->getNumOfBlocks(); return blocks_first_relation < blocks_second_relation; };
string distinct(string tableName) { Relation *relation = schemaManager.getRelation(tableName); Schema schema = relation->getSchema(); int size = relation->getNumOfBlocks(); vector<Tuple> tuples; bool flag = true; //one-pass if(size<=10) { relation->getBlocks(0,0,size); tuples = mainMemory.getTuples(0,size); tuples = getDistinctTuples(tuples); Relation *relation1 = schemaManager.createRelation(tableName+"_distinct",schema); insertTuples(tableName+"_distinct",tuples); } //two pass else { int index = 0, loadSize=10; while(size>0) { relation->getBlocks(index,0,loadSize); for(int i=0;i<loadSize;i++) { Block *block = mainMemory.getBlock(i); for(int j=0;j<block->getNumTuples();j++) { tuples.push_back(block->getTuple(j)); } } tuples = getDistinctTuples(tuples); //partition(tuples, 0, tuples.size()-1); if(flag) { Relation *relation2= schemaManager.createRelation(tableName+"_dis", schema); flag = false; } insertTuples(tableName+"_dis", tuples); Relation *relation2 = schemaManager.getRelation(tableName+"_dis"); tuples.clear(); index = index+10; size = size-10; if(size<10) loadSize = size; } if(size<=100) { Relation *relation2 = schemaManager.createRelation(tableName+"_distinct", schema); relation = schemaManager.getRelation(tableName+"_dis"); int buckets = relation->getNumOfBlocks()/10; vector<Tuple> tuples; for(int i=0;i<10;i++) { for(int j=0;j<buckets;j++) { if(j*10+i > relation->getNumOfBlocks()) break; relation->getBlock(i+10*j,j); Block *block = mainMemory.getBlock(j); for(int k=0;k<block->getNumTuples();k++) { tuples.push_back(block->getTuple(k)); } } } tuples = getDistinctTuples(tuples); insertTuples(tableName+"_distinct", tuples); tuples.clear(); schemaManager.deleteRelation(tableName+"_dis"); } else cerr<<"Table size exceeds the limit size(mainMemory)^2"<<endl; } return tableName+"_distinct"; }
void selectFromTable(bool dis, string attributes, string tabs, string whereCondition, string orderBy) { int disk0 = disk.getDiskIOs(); vector<string> tableNames = split(tabs, ','); vector<string> attributeNames = split(attributes, ','); string tableName; if(validate(tableNames)) return; if(tableNames.size()==1) { Relation *relation = schemaManager.getRelation(tableNames[0]); string pName = projection(attributeNames, tableNames[0], whereCondition); string d; relation = schemaManager.getRelation(pName); if(dis) { d = distinct(pName); relation = schemaManager.getRelation(d); } cout<<*relation<<endl; if(!(attributeNames.size()==1 && attributeNames[0]=="*" && whereCondition.empty())) schemaManager.deleteRelation(pName); if(dis) schemaManager.deleteRelation(d); } else { vector<string>::iterator it; vector<string> projections; if(tableNames.size()==2) { string ptemp = crossJoin(attributeNames, tableNames[0], tableNames[1], whereCondition, false); string d; Relation *relation = schemaManager.getRelation(ptemp); if(dis) { d = distinct(ptemp); relation = schemaManager.getRelation(d); } cout<<*relation<<endl; schemaManager.deleteRelation(ptemp); if(dis) schemaManager.deleteRelation(d); } else { bool flag =true; vector<string> blah; string str = crossJoin(blah, tableNames[0],tableNames[1], whereCondition, false); for(int i=2;i<tableNames.size();i++) { str = crossJoin(blah, str, tableNames[i], whereCondition, true); } Relation *relation = schemaManager.getRelation(str); cout<<*relation<<endl; schemaManager.deleteRelation(str); } } cout<<"No. of disk IO's used for this opertaion are "<<disk.getDiskIOs()-disk0<<endl; }
string projection(vector<string> attributes, string tableName, string whereCondition) { Relation *relation = schemaManager.getRelation(tableName); Schema tableSchema = relation->getSchema(); vector<string> fieldNames; vector<enum FIELD_TYPE> fieldTypes; vector<string>::iterator it; int flag=-1; bool print=true; for(it=attributes.begin();it!=attributes.end();it++) { for(int i=0;i<tableSchema.getNumOfFields();i++) { string temp = *it; if(tableSchema.getFieldName(i)==temp || tableName+"."+tableSchema.getFieldName(i) == temp) flag=i; } if(flag!=-1) { fieldNames.push_back(tableSchema.getFieldName(flag)); fieldTypes.push_back(tableSchema.getFieldType(flag)); flag = -1; } } if(attributes.size()==1 && attributes[0] == "*") { if(whereCondition.empty()) return tableName; fieldNames = tableSchema.getFieldNames(); fieldTypes = tableSchema.getFieldTypes(); } Schema dupSchema(fieldNames,fieldTypes); Relation *relationDup = schemaManager.createRelation(tableName.append("_dup"), dupSchema); Tuple tuple = relationDup->createTuple(); vector<Tuple>::iterator it1; Block *block = mainMemory.getBlock(9); block->clear(); int index=0; for(int i=0;i<relation->getNumOfBlocks();i++) { relation->getBlock(i,0); vector<Tuple> t = mainMemory.getBlock(0)->getTuples(); for(it1=t.begin();it1!=t.end();it1++) { if(!it1->isNull()){ for(int j=0;j<fieldNames.size();j++) { if(fieldTypes[j]==INT) tuple.setField(fieldNames[j],it1->getField(fieldNames[j]).integer); else tuple.setField(fieldNames[j],*(it1->getField(fieldNames[j]).str)); } bool ttp = whereConditionEvaluator(whereCondition, *it1); if(ttp) { if(!block->isFull()) block->appendTuple(tuple); else { relationDup->setBlock(index,9); index++; block->clear(); block->appendTuple(tuple); } } } } } if(index!=relationDup->getNumOfBlocks()-1) relationDup->setBlock(index, 9); return tableName; }
string crossJoin(vector<string> attributes, string tableName1, string tableName2, string whereCondition, bool multi) { string small,big; bool proj = false; if(schemaManager.getRelation(tableName1)->getNumOfBlocks()<=schemaManager.getRelation(tableName2)->getNumOfBlocks()) { small = tableName1; big = tableName2; } else { small=tableName2; big=tableName1; } Schema schema1 = schemaManager.getSchema(small); Schema schema2 = schemaManager.getSchema(big); vector<string> fieldNames; vector<enum FIELD_TYPE> fieldTypes; if(!multi) { for(int i=0;i<schema1.getNumOfFields();i++) { fieldNames.push_back(small+"."+schema1.getFieldName(i)); fieldTypes.push_back(schema1.getFieldType(i)); } } else { for(int i=0;i<schema1.getNumOfFields();i++) { fieldNames.push_back(schema1.getFieldName(i)); fieldTypes.push_back(schema1.getFieldType(i)); } } for(int i=0;i<schema2.getNumOfFields();i++) { fieldNames.push_back(big+"."+schema2.getFieldName(i)); fieldTypes.push_back(schema2.getFieldType(i)); } Schema schema(fieldNames,fieldTypes); Relation *relation = schemaManager.createRelation(big+"_join",schema); Relation *relation1 = schemaManager.getRelation(small); Relation *relation2 = schemaManager.getRelation(big); int size1 = relation1->getNumOfBlocks(), size2 = relation2->getNumOfBlocks(); if(!((attributes.size()==1 && attributes[0]=="*") || multi)) { vector<string> fieldNames1; vector<enum FIELD_TYPE> fieldTypes1; for(int i=0;i<attributes.size();i++) { int temp = schema.getFieldOffset(attributes[i]); fieldNames1.push_back(schema.getFieldName(temp)); fieldTypes1.push_back(schema.getFieldType(attributes[i])); } Schema schema1(fieldNames1, fieldTypes1); proj = true; Relation *relationp = schemaManager.createRelation(big+"_joinp", schema1); } if(size1<=10) { relation1->getBlocks(0,0,size1); vector<Tuple> tuples = mainMemory.getTuples(0,size1); for(int x=0;x<tuples.size();x++) { for(int i=0;i<size2;i++) { relation2->getBlock(i,1); Block *block = mainMemory.getBlock(1); for(int j=0;j<block->getNumTuples();j++) { Tuple tuple2 = block->getTuple(j); join(tuples[x], tuple2, small, big, whereCondition, multi, attributes); } } } } else { for(int x=0;x<size1;x++) { relation1->getBlock(x,0); Block *block0 = mainMemory.getBlock(0); for(int y=0;y<block0->getNumTuples();y++) { Tuple tuple1 = block0->getTuple(y); for(int i=0;i<size2;i++) { relation2->getBlock(i,1); Block *block = mainMemory.getBlock(1); for(int j=0;j<block->getNumTuples();j++) { Tuple tuple2 = block->getTuple(j); join(tuple1, tuple2, small, big, whereCondition, multi, attributes); } } } } } string rt = big+"_join"; if(proj) { schemaManager.deleteRelation(rt); rt = big+"_joinp"; } return rt; }