CommandFunctions.java

import java.io.RandomAccessFile;
import java.io.FileReader;
import java.io.File;
import java.util.Scanner;
import java.util.SortedMap;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Date;
import java.text.SimpleDateFormat;

public class CommandFunctions{
	
	
	private static RandomAccessFile davisbasetable;
	private static RandomAccessFile davisbasecolumn;

	// test case
	public static void main(String[] args){}

	// function to show all tables in the database
	public static void showTable(){
		
		String[] compare = new String[0];
		String[] columns = {"table_name"};
		String tablename = "davisbase_tables";
		selectTable(tablename, columns, compare);
	}

    //CommandFunctions creation
	public static void createTable(String table, String[] columns){
		try{	
			//file
			RandomAccessFile file = new RandomAccessFile("data/"+table+".tbl", "rw");
			file.setLength(512);
			file.seek(0);
			file.writeByte(0x0D);
			file.close();
			// table
			file = new RandomAccessFile("data/davisbase_tables.tbl", "rw");
			int numPages = pages(file);
			int page = 1;
			for(int p = 1; p <= numPages; p++){
				int rm = BPlusTreeFilePageNav.getRightpointer(file, p);
				if(rm == 0)
					page = p;
			}
			int[] keyArray = BPlusTreeFilePageNav.getKeys(file, page);
			int l = keyArray[0];
			for(int i = 0; i < keyArray.length; i++)
				if(l < keyArray[i])
					l = keyArray[i];
			file.close();
			String[] values = {Integer.toString(l+1), table};
			insertTable("davisbase_tables", values);

			RandomAccessFile cfile = new RandomAccessFile("data/davisbase_columns.tbl", "rw");
			Buffer buffer = new Buffer();
			String[] columnName = {"rowid", "table_name", "column_name", "data_type", "ordinal_position", "is_nullable"};
			String[] cmp = {};
			filter(cfile, cmp, columnName, buffer);
			l = buffer.content.size();

			for(int i = 0; i < columns.length; i++){
				l = l + 1;
				String[] token = columns[i].split(" ");
				String n = "YES";
				if(token.length > 2)
					n = "NO";
				String col_name = token[0];
				String dt = token[1].toUpperCase();
				String pos = Integer.toString(i+1);
				String[] v = {Integer.toString(l), table, col_name, dt, pos, n};
				insertTable("davisbase_columns", v);
			}
			file.close();
		}catch(Exception e){
			System.out.println("Error at createTable");
			e.printStackTrace();
		}
	}
	
	
	

	/*
	 * Deletes the table entries from the database...
	 */
	public static void dropTable(String table){
		try{
			// clear meta-table
			RandomAccessFile file = new RandomAccessFile("data/davisbase_tables.tbl", "rw");
			int numPages = pages(file);
			for(int page = 1; page <= numPages; page ++){
				file.seek((page-1)*512);
				byte type = file.readByte();
				if(type == 0x05)
					continue;
				else{
					short[] cells = BPlusTreeFilePageNav.getCells(file, page);
					int i = 0;
					for(int j = 0; j < cells.length; j++){
						long loc = BPlusTreeFilePageNav.getCelloffnum(file, page, j);
						String[] pl = retrievePayload(file, loc);
						String tb = pl[1];
						if(!tb.equals(table)){
							BPlusTreeFilePageNav.setCellOffset(file, page, i, cells[j]);
							i++;
						}
					}
					BPlusTreeFilePageNav.settotalcells(file, page, (byte)i);
				}
			}

			// clear meta-column
			file = new RandomAccessFile("data/davisbase_columns.tbl", "rw");
			numPages = pages(file);
			for(int page = 1; page <= numPages; page ++){
				file.seek((page-1)*512);
				byte type = file.readByte();
				if(type == 0x05)
					continue;
				else{
					short[] cells = BPlusTreeFilePageNav.getCells(file, page);
					int i = 0;
					for(int j = 0; j < cells.length; j++){
						long loc = BPlusTreeFilePageNav.getCelloffnum(file, page, j);
						String[] pl = retrievePayload(file, loc);
						String tb = pl[1];
						if(!tb.equals(table)){
							BPlusTreeFilePageNav.setCellOffset(file, page, i, cells[j]);
							i++;
						}
					}
					BPlusTreeFilePageNav.settotalcells(file, page, (byte)i);
				}
			}

			//delete file
			File anOldFile = new File("data", table+".tbl"); 
			anOldFile.delete();
		}catch(Exception e){
			System.out.println("Error at drop");
			System.out.println(e);
		}

	}

	public static String[] retrievePayload(RandomAccessFile file, long loc){
		String[] payload = new String[0];
		final String dp = "yyyy-MM-dd_HH:mm:ss";
		try{
			Long tmp;
			SimpleDateFormat formater = new SimpleDateFormat (dp);

			// get stc
			file.seek(loc);
			int plsize = file.readShort();
			int key = file.readInt();
			int num_cols = file.readByte();
			byte[] stc = new byte[num_cols];
			int temp = file.read(stc);
			payload = new String[num_cols+1];
			payload[0] = Integer.toString(key);
			// get payLoad
			for(int i=1; i <= num_cols; i++){
				switch(stc[i-1]){
					case 0x00:  payload[i] = Integer.toString(file.readByte());
								payload[i] = "null";
								break;

					case 0x01:  payload[i] = Integer.toString(file.readShort());
								payload[i] = "null";
								break;

					case 0x02:  payload[i] = Integer.toString(file.readInt());
								payload[i] = "null";
								break;

					case 0x03:  payload[i] = Long.toString(file.readLong());
								payload[i] = "null";
								break;

					case 0x04:  payload[i] = Integer.toString(file.readByte());
								break;

					case 0x05:  payload[i] = Integer.toString(file.readShort());
								break;

					case 0x06:  payload[i] = Integer.toString(file.readInt());
								break;

					case 0x07:  payload[i] = Long.toString(file.readLong());
								break;

					case 0x08:  payload[i] = String.valueOf(file.readFloat());
								break;

					case 0x09:  payload[i] = String.valueOf(file.readDouble());
								break;

					case 0x0A:  tmp = file.readLong();
								Date dateTime = new Date(tmp);
								payload[i] = formater.format(dateTime);
								break;

					case 0x0B:  tmp = file.readLong();
								Date date = new Date(tmp);
								payload[i] = formater.format(date).substring(0,10);
								break;

					default:    int len = new Integer(stc[i-1]-0x0C);
								byte[] bytes = new byte[len];
								for(int j = 0; j < len; j++)
									bytes[j] = file.readByte();
								payload[i] = new String(bytes);
								break;
				}
			}

		}catch(Exception e){
			System.out.println("Error at retrievePayload");
		}

		return payload;
	}


	public static void updateTable(String table, String[] set, String[] cmp){
		try{
			int key = new Integer(cmp[2]);
			RandomAccessFile file = new RandomAccessFile("data/"+table+".tbl", "rw");
			int numPages = pages(file);
			int page = 1;

			for(int p = 1; p <= numPages; p++)
				if(BPlusTreeFilePageNav.hasKey(file, p, key)){
					page = p;
				}
			int[] array = BPlusTreeFilePageNav.getKeys(file, page);
			int id = 0;
			for(int i = 0; i < array.length; i++)
				if(array[i] == key)
					id = i;
			int offset = BPlusTreeFilePageNav.getCellOffset(file, page, id);
			long loc = BPlusTreeFilePageNav.getCelloffnum(file, page, id);
			String[] array_s = getColName(table);
			int num_cols = array_s.length - 1;
			String[] values = retrievePayload(file, loc);


			// fix date time type value to string format before update
			String[] type = getDataType(table);
			for(int i=0; i < type.length; i++)
				if(type[i].equals("DATE") || type[i].equals("DATETIME"))
					values[i] = "'"+values[i]+"'";


			// update value on a column
			for(int i = 0; i < array_s.length; i++)
				if(array_s[i].equals(set[0]))
					id = i;
			values[id] = set[2];

			// check null value violation
			String[] nullable = isNullable(table);

			for(int i = 0; i < nullable.length; i++){
				if(values[i].equals("null") && nullable[i].equals("NO")){
					System.out.println("NULL value constraint violation");
					System.out.println();
					return;
				}
			}



			//check uniqueness violation
			// for(int p = 1; p <= numPages; p++)
			// 	if(BPlusTreeFilePageNav.hasKey(file, p, key)){
			// 		page = p;
			// }

			byte[] stc = new byte[array_s.length-1];
			int plsize = calPayloadSize(table, values, stc);
			BPlusTreeFilePageNav.updateLeafnode(file, page, offset, plsize, key, stc, values);

			file.close();

		}catch(Exception e){
			System.out.println("Error at update");
			System.out.println(e);
		}
	}

	public static void insertTable(RandomAccessFile file, String table, String[] values){
		String[] dtype = getDataType(table);
		String[] nullable = isNullable(table);

		for(int i = 0; i < nullable.length; i++)
			if(values[i].equals("null") && nullable[i].equals("NO")){
				System.out.println("NULL value constraint violation");
				System.out.println();
				return;
			}


		int key = new Integer(values[0]);
		int page = searchKey(file, key);
		if(page != 0)
			if(BPlusTreeFilePageNav.hasKey(file, page, key)){
				System.out.println("Uniqueness constraint violation");
				System.out.println();
				return;
			}
		if(page == 0)
			page = 1;


		byte[] stc = new byte[dtype.length-1];
		short plSize = (short) calPayloadSize(table, values, stc);
		int cellSize = plSize + 6;
		int offset = BPlusTreeFilePageNav.checkLeafSpace(file, page, cellSize);


		if(offset != -1){
			BPlusTreeFilePageNav.addLeafNode(file, page, offset, plSize, key, stc, values);
			//BPlusTreeFilePageNav.sortCellArray(file, page);
		}else{
			BPlusTreeFilePageNav.ExpandOverflowLeaf(file, page);
			insertTable(file, table, values);
		}
	}

	public static void insertTable(String table, String[] values){
		try{
			RandomAccessFile file = new RandomAccessFile("data/"+table+".tbl", "rw");
			insertTable(file, table, values);
			file.close();

		}catch(Exception e){
			System.out.println("Error at insertInto table");
			e.printStackTrace();
		}
	}

	// calculate the payload size
	public static int calPayloadSize(String table, String[] vals, byte[] stc){
		String[] dataType = getDataType(table);
		int size = 1;
		size = size + dataType.length - 1;
		for(int i = 1; i < dataType.length; i++){
			byte tmp = stcCode(vals[i], dataType[i]);
			stc[i - 1] = tmp;
			size = size + feildLength(tmp);
		}
		return size;
	}

	//calculate value length by STC code
	public static short feildLength(byte stc){
		switch(stc){
			case 0x00: return 1;
			case 0x01: return 2;
			case 0x02: return 4;
			case 0x03: return 8;
			case 0x04: return 1;
			case 0x05: return 2;
			case 0x06: return 4;
			case 0x07: return 8;
			case 0x08: return 4;
			case 0x09: return 8;
			case 0x0A: return 8;
			case 0x0B: return 8;
			default:   return (short)(stc - 0x0C);
		}
	}

	// return STC
	public static byte stcCode(String val, String dataType){
		if(val.equals("null")){
			switch(dataType){
				case "TINYINT":     return 0x00;
				case "SMALLINT":    return 0x01;
				case "INT":			return 0x02;
				case "BIGINT":      return 0x03;
				case "REAL":        return 0x02;
				case "DOUBLE":      return 0x03;
				case "DATETIME":    return 0x03;
				case "DATE":        return 0x03;
				case "TEXT":        return 0x03;
				default:			return 0x00;
			}							
		}else{
			switch(dataType){
				case "TINYINT":     return 0x04;
				case "SMALLINT":    return 0x05;
				case "INT":			return 0x06;
				case "BIGINT":      return 0x07;
				case "REAL":        return 0x08;
				case "DOUBLE":      return 0x09;
				case "DATETIME":    return 0x0A;
				case "DATE":        return 0x0B;
				case "TEXT":        return (byte)(val.length()+0x0C);
				default:			return 0x00;
			}
		}
	}

	public static int searchKey(RandomAccessFile file, int key){
		int val = 1;
		try{
			int numPages = pages(file);
			for(int page = 1; page <= numPages; page++){
				file.seek((page - 1)*512);
				byte pageType = file.readByte();
				if(pageType == 0x0D){
					int[] keys = BPlusTreeFilePageNav.getKeys(file, page);
					if(keys.length == 0)
						return 0;
					int rm = BPlusTreeFilePageNav.getRightpointer(file, page);
					if(keys[0] <= key && key <= keys[keys.length - 1]){
						return page;
					}else if(rm == 0 && keys[keys.length - 1] < key){
						return page;
					}
				}
			}
		}catch(Exception e){
			System.out.println("Error at searchKey");
			System.out.println(e);
		}

		return val;
	}


	public static String[] getDataType(String table){
		String[] dataType = new String[0];
		try{
			RandomAccessFile file = new RandomAccessFile("data/davisbase_columns.tbl", "rw");
			Buffer buffer = new Buffer();
			String[] columnName = {"rowid", "table_name", "column_name", "data_type", "ordinal_position", "is_nullable"};
			String[] cmp = {"table_name","=",table};
			filter(file, cmp, columnName, buffer);
			HashMap<Integer, String[]> content = buffer.content;
			ArrayList<String> array = new ArrayList<String>();
			for(String[] i : content.values()){
				array.add(i[3]);
			}
			dataType = array.toArray(new String[array.size()]);
			file.close();
			return dataType;
		}catch(Exception e){
			System.out.println("Error at getDataType");
			System.out.println(e);
		}
		return dataType;
	}

	public static String[] getColName(String table){
		String[] c = new String[0];
		try{
			RandomAccessFile file = new RandomAccessFile("data/davisbase_columns.tbl", "rw");
			Buffer buffer = new Buffer();
			String[] columnName = {"rowid", "table_name", "column_name", "data_type", "ordinal_position", "is_nullable"};
			String[] cmp = {"table_name","=",table};
			filter(file, cmp, columnName, buffer);
			HashMap<Integer, String[]> content = buffer.content;
			ArrayList<String> array = new ArrayList<String>();
			for(String[] i : content.values()){
				array.add(i[2]);
			}
			c = array.toArray(new String[array.size()]);
			file.close();
			return c;
		}catch(Exception e){
			System.out.println("Error at getColName");
			System.out.println(e);
		}
		return c;
	}

	public static String[] isNullable(String table){
		String[] n = new String[0];
		try{
			RandomAccessFile file = new RandomAccessFile("data/davisbase_columns.tbl", "rw");
			Buffer buffer = new Buffer();
			String[] columnName = {"rowid", "table_name", "column_name", "data_type", "ordinal_position", "is_nullable"};
			String[] cmp = {"table_name","=",table};
			filter(file, cmp, columnName, buffer);
			HashMap<Integer, String[]> content = buffer.content;
			ArrayList<String> array = new ArrayList<String>();
			for(String[] i : content.values()){
				array.add(i[5]);
			}
			n = array.toArray(new String[array.size()]);
			file.close();
			return n;
		}catch(Exception e){
			System.out.println("Error at getNullable");
			System.out.println(e);
		}
		return n;
	}

	public static void selectTable(String table, String[] cols, String[] cmp){
		try{
			Buffer buffer = new Buffer();
			String[] columnName = getColName(table);
			String[] type = getDataType(table);

			RandomAccessFile file = new RandomAccessFile("data/"+table+".tbl", "rw");
			filter(file, cmp, columnName, type, buffer);
			buffer.display(cols);
			file.close();
		}catch(Exception e){
			System.out.println("Error at select");
			System.out.println(e);
		}
	}

	// filter fuction for select
	public static void filter(RandomAccessFile file, String[] cmp, String[] columnName, String[] type, Buffer buffer){
		try{
			int numPages = pages(file);
			// get column_name
			for(int page = 1; page <= numPages; page++){
				file.seek((page-1)*512);
				byte pageType = file.readByte();
				if(pageType == 0x05)
					continue;
				else{
					byte numCells = BPlusTreeFilePageNav.gettotalcells(file, page);

					for(int i=0; i < numCells; i++){
						//System.out.println("check point");
						long loc = BPlusTreeFilePageNav.getCelloffnum(file, page, i);
						file.seek(loc+2); // seek to rowid
						int rowid = file.readInt(); // read rowid
						int num_cols = new Integer(file.readByte()); // read # of columns other than rowid

						String[] payload = retrievePayload(file, loc);

						for(int j=0; j < type.length; j++)
							if(type[j].equals("DATE") || type[j].equals("DATETIME"))
								payload[j] = "'"+payload[j]+"'";
						// check
						boolean check = cmpCheck(payload, rowid, cmp, columnName);

						// convert back date type
						for(int j=0; j < type.length; j++)
							if(type[j].equals("DATE") || type[j].equals("DATETIME"))
								payload[j] = payload[j].substring(1, payload[j].length()-1);

						if(check)
							buffer.add(rowid, payload);
					}
				}
			}

			buffer.columnName = columnName;
			buffer.format = new int[columnName.length];

		}catch(Exception e){
			System.out.println("Error at filter");
			e.printStackTrace();
		}

	}

	// filter function for getDT getNull
	public static void filter(RandomAccessFile file, String[] cmp, String[] columnName, Buffer buffer){
		try{
			int numPages = pages(file);
			// get column_name
			for(int page = 1; page <= numPages; page++){
				file.seek((page-1)*512);
				byte pageType = file.readByte();
				if(pageType == 0x05)
					continue;
				else{
					byte numCells = BPlusTreeFilePageNav.gettotalcells(file, page);

					for(int i=0; i < numCells; i++){
						long loc = BPlusTreeFilePageNav.getCelloffnum(file, page, i);
						file.seek(loc+2); // seek to rowid
						int rowid = file.readInt(); // read rowid
						int num_cols = new Integer(file.readByte()); // read # of columns other than rowid
						String[] payload = retrievePayload(file, loc);

						boolean check = cmpCheck(payload, rowid, cmp, columnName);
						if(check)
							buffer.add(rowid, payload);
					}
				}
			}

			buffer.columnName = columnName;
			buffer.format = new int[columnName.length];

		}catch(Exception e){
			System.out.println("Error at filter");
			e.printStackTrace();
		}

	}

	// return number of page of the file
	public static int pages(RandomAccessFile file){
		int num_pages = 0;
		try{
			num_pages = (int)(file.length()/(new Long(512)));
		}catch(Exception e){
			System.out.println("Error at makeInteriorPage");
		}

		return num_pages;
	}

	// to check the select condition as per the operator...
	public static boolean cmpCheck(String[] payload, int rowid, String[] cmp, String[] columnName){

		boolean check = false;
		if(cmp.length == 0){
			check = true;
		}else{
			int colPos = 1;
			for(int i = 0; i < columnName.length; i++){
				if(columnName[i].equals(cmp[0])){
					colPos = i + 1;
					break;
				}
			}
			String opt = cmp[1];
			String val = cmp[2];
			if(colPos == 1){
				switch(opt){
					case "=": if(rowid == Integer.parseInt(val)) 
								check = true;
							  else
							  	check = false;
							  break;
					case ">": if(rowid > Integer.parseInt(val)) 
								check = true;
							  else
							  	check = false;
							  break;
					case "<": if(rowid < Integer.parseInt(val)) 
								check = true;
							  else
							  	check = false;
							  break;
					case ">=": if(rowid >= Integer.parseInt(val)) 
								check = true;
							  else
							  	check = false;	
							  break;
					case "<=": if(rowid <= Integer.parseInt(val)) 
								check = true;
							  else
							  	check = false;	
							  break;
					case "<>": if(rowid != Integer.parseInt(val))  // TODO: check the operator
								check = true;
							  else
							  	check = false;	
							  break;						  							  							  							
				}
			}else{
				if(val.equals(payload[colPos-1]))
					check = true;
				else
					check = false;
			}
		}
		return check;
	}

	public static void BackgroundFileCreation() {

		/** Create data directory at the current OS location to hold */
		try {
			File dataDir = new File("data");
			dataDir.mkdir();
			String[] oldTableFiles;
			oldTableFiles = dataDir.list();
			for (int i=0; i<oldTableFiles.length; i++) {
				File anOldFile = new File(dataDir, oldTableFiles[i]); 
				anOldFile.delete();
			}
		}
		catch (SecurityException se) {
			System.out.println("Unable to create data container directory");
			System.out.println(se);
		}

		try {
			davisbasetable = new RandomAccessFile("data/davisbase_tables.tbl", "rw");
			davisbasetable.setLength(512);
			davisbasetable.seek(0);
			davisbasetable.write(0x0D);// page type
			davisbasetable.write(0x02);// num cell
			int[] offset=new int[2];
			int size1=24;//table size
			int size2=25;// column size
			offset[0]=512-size1;
			offset[1]=offset[0]-size2;
			davisbasetable.writeShort(offset[1]);// content offset
			davisbasetable.writeInt(0);// rightmost
			davisbasetable.writeInt(10);// parent
			davisbasetable.writeShort(offset[1]);// cell arrary 1
			davisbasetable.writeShort(offset[0]);// cell arrary 2
			davisbasetable.seek(offset[0]);
			davisbasetable.writeShort(20);
			davisbasetable.writeInt(1); 
			davisbasetable.writeByte(1);
			davisbasetable.writeByte(28);
			davisbasetable.writeBytes("davisbase_tables");
			davisbasetable.seek(offset[1]);
			davisbasetable.writeShort(21);
			davisbasetable.writeInt(2); 
			davisbasetable.writeByte(1);
			davisbasetable.writeByte(29);
			davisbasetable.writeBytes("davisbase_columns");
		}
		catch (Exception e) {
			System.out.println("Unable to create the database_tables file");
			System.out.println(e);
		}
		try {
			davisbasecolumn = new RandomAccessFile("data/davisbase_columns.tbl", "rw");
			davisbasecolumn.setLength(512);
			davisbasecolumn.seek(0);       
			davisbasecolumn.writeByte(0x0D); // page type: leaf page
			davisbasecolumn.writeByte(0x08); // number of cells
			int[] offset=new int[10];
			offset[0]=512-43;
			offset[1]=offset[0]-47;
			offset[2]=offset[1]-44;
			offset[3]=offset[2]-48;
			offset[4]=offset[3]-49;
			offset[5]=offset[4]-47;
			offset[6]=offset[5]-57;
			offset[7]=offset[6]-49;
			offset[8]=offset[7]-49;
			davisbasecolumn.writeShort(offset[8]); // content offset
			davisbasecolumn.writeInt(0); // rightmost
			davisbasecolumn.writeInt(0); // parent
			// cell array
			for(int i=0;i<9;i++)
				davisbasecolumn.writeShort(offset[i]);

			// data
			davisbasecolumn.seek(offset[0]);
			davisbasecolumn.writeShort(33); // 34
			davisbasecolumn.writeInt(1); 
			davisbasecolumn.writeByte(5);
			davisbasecolumn.writeByte(28);
			davisbasecolumn.writeByte(17);
			davisbasecolumn.writeByte(15);
			davisbasecolumn.writeByte(4);
			davisbasecolumn.writeByte(14);
			//davisbasecolumn.writeByte(15);
			davisbasecolumn.writeBytes("davisbase_tables"); // 16
			davisbasecolumn.writeBytes("rowid"); // 5
			davisbasecolumn.writeBytes("INT"); // 3
			davisbasecolumn.writeByte(1); // 1
			davisbasecolumn.writeBytes("NO"); // 2
			//davisbasecolumn.writeBytes("PRI");
			
			davisbasecolumn.seek(offset[1]);
			davisbasecolumn.writeShort(39); // 38
			davisbasecolumn.writeInt(2); 
			davisbasecolumn.writeByte(5);
			davisbasecolumn.writeByte(28);
			davisbasecolumn.writeByte(22);
			davisbasecolumn.writeByte(16);
			davisbasecolumn.writeByte(4);
			davisbasecolumn.writeByte(14);
			//davisbasecolumn.writeByte(0);
			davisbasecolumn.writeBytes("davisbase_tables"); // 16
			davisbasecolumn.writeBytes("table_name"); // 10  
			davisbasecolumn.writeBytes("TEXT"); // 4
			davisbasecolumn.writeByte(2); // 1
			davisbasecolumn.writeBytes("NO"); // 2
			//davisbasecolumn.writeByte(0);
			
			davisbasecolumn.seek(offset[2]);
			davisbasecolumn.writeShort(34); // 35
			davisbasecolumn.writeInt(3); 
			davisbasecolumn.writeByte(5);
			davisbasecolumn.writeByte(29);
			davisbasecolumn.writeByte(17);
			davisbasecolumn.writeByte(15);
			davisbasecolumn.writeByte(4);
			davisbasecolumn.writeByte(14);
			//davisbasecolumn.writeByte(15);
			davisbasecolumn.writeBytes("davisbase_columns");
			davisbasecolumn.writeBytes("rowid");
			davisbasecolumn.writeBytes("INT");
			davisbasecolumn.writeByte(1);
			davisbasecolumn.writeBytes("NO");
			//davisbasecolumn.writeBytes("PRI");
			
			davisbasecolumn.seek(offset[3]);
			davisbasecolumn.writeShort(40); // 39
			davisbasecolumn.writeInt(4); 
			davisbasecolumn.writeByte(5);
			davisbasecolumn.writeByte(29);
			davisbasecolumn.writeByte(22);
			davisbasecolumn.writeByte(16);
			davisbasecolumn.writeByte(4);
			davisbasecolumn.writeByte(14);
			//davisbasecolumn.writeByte(0);
			davisbasecolumn.writeBytes("davisbase_columns");
			davisbasecolumn.writeBytes("table_name");
			davisbasecolumn.writeBytes("TEXT");
			davisbasecolumn.writeByte(2);
			davisbasecolumn.writeBytes("NO");
			//davisbasecolumn.writeByte(0);

			
			davisbasecolumn.seek(offset[4]);
			davisbasecolumn.writeShort(41); // 40
			davisbasecolumn.writeInt(5); 
			davisbasecolumn.writeByte(5);
			davisbasecolumn.writeByte(29);
			davisbasecolumn.writeByte(23);
			davisbasecolumn.writeByte(16);
			davisbasecolumn.writeByte(4);
			davisbasecolumn.writeByte(14);
			//davisbasecolumn.writeByte(0);
			davisbasecolumn.writeBytes("davisbase_columns");
			davisbasecolumn.writeBytes("column_name");
			davisbasecolumn.writeBytes("TEXT");
			davisbasecolumn.writeByte(3);
			davisbasecolumn.writeBytes("NO");
			//davisbasecolumn.writeByte(0);
			
			davisbasecolumn.seek(offset[5]);
			davisbasecolumn.writeShort(39); // 38
			davisbasecolumn.writeInt(6); 
			davisbasecolumn.writeByte(5);
			davisbasecolumn.writeByte(29);
			davisbasecolumn.writeByte(21);
			davisbasecolumn.writeByte(16);
			davisbasecolumn.writeByte(4);
			davisbasecolumn.writeByte(14);
			//davisbasecolumn.writeByte(0);
			davisbasecolumn.writeBytes("davisbase_columns");
			davisbasecolumn.writeBytes("data_type");
			davisbasecolumn.writeBytes("TEXT");
			davisbasecolumn.writeByte(4);
			davisbasecolumn.writeBytes("NO");
			//davisbasecolumn.writeByte(0);
			
			davisbasecolumn.seek(offset[6]);
			davisbasecolumn.writeShort(49); // 48
			davisbasecolumn.writeInt(7); 
			davisbasecolumn.writeByte(5);
			davisbasecolumn.writeByte(29);
			davisbasecolumn.writeByte(28);
			davisbasecolumn.writeByte(19);
			davisbasecolumn.writeByte(4);
			davisbasecolumn.writeByte(14);
			//davisbasecolumn.writeByte(0);
			davisbasecolumn.writeBytes("davisbase_columns");
			davisbasecolumn.writeBytes("ordinal_position");
			davisbasecolumn.writeBytes("TINYINT");
			davisbasecolumn.writeByte(5);
			davisbasecolumn.writeBytes("NO");
			//davisbasecolumn.writeByte(0);
			
			davisbasecolumn.seek(offset[7]);
			davisbasecolumn.writeShort(41); // 40
			davisbasecolumn.writeInt(8); 
			davisbasecolumn.writeByte(5);
			davisbasecolumn.writeByte(29);
			davisbasecolumn.writeByte(23);
			davisbasecolumn.writeByte(16);
			davisbasecolumn.writeByte(4);
			davisbasecolumn.writeByte(14);
			davisbasecolumn.writeBytes("davisbase_columns");
			davisbasecolumn.writeBytes("is_nullable");
			davisbasecolumn.writeBytes("TEXT");
			davisbasecolumn.writeByte(6);
			davisbasecolumn.writeBytes("NO");
		}
		catch (Exception e) {
			System.out.println("Unable to create the database_columns file");
			System.out.println(e);
		}
	}
}


// store and display retrieved data
class Buffer{
	public int num_row; // number of rows in the buffer
	public HashMap<Integer, String[]> content; // hash map to store data. Integer represent rowid(key), String[] represent all data of each row
	public int[] format; // format controller. store display format info -- the length of longest string of each column
	public String[] columnName; // column name of all columns

	// initializer, 0 row stored and empty content container
	public Buffer(){
		num_row = 0;
		content = new HashMap<Integer, String[]>();
	}

	// add data into content container. increase stored row number
	public void add(int rowid, String[] val){
		content.put(rowid, val);
		num_row = num_row + 1;
	}

	// update the format controller
	public void updateFormat(){
		for(int i = 0; i < format.length; i++)
			format[i] = columnName[i].length();
		for(String[] i : content.values()){
			for(int j = 0; j < i.length; j++)
				if(format[j] < i[j].length())
					format[j] = i[j].length();
		}
	}

	// make the string s to be fix length of len. filled with space
	public String fix(int len, String s) {
		return String.format("%-"+(len+3)+"s", s);
	}

	// make a length of len using compose with stirng s
	public String line(String s,int len) {
		String a = "";
		for(int i=0;i<len;i++) {
			a += s;
		}
		return a;
	}

	// display content according to the format controller. col specify selected columns to display
	public void display(String[] col){
		// if the content container if empty, output info
		if(num_row == 0){
			System.out.println("Empty set.");
		}else{
			// called function to update format controller
			updateFormat();
			// if selectd column is "*", means display all columns
			if(col[0].equals("*")){
				// print line
				for(int l: format)
					System.out.print(line("-", l+3));
				System.out.println();
				// print column name
				for(int j = 0; j < columnName.length; j++)
					System.out.print(fix(format[j], columnName[j])+"|");
				System.out.println();
				// print line
				for(int l: format)
					System.out.print(line("-", l+3));
				System.out.println();
				// print data
				for(String[] i : content.values()){
					for(int j = 0; j < i.length; j++)
						System.out.print(fix(format[j], i[j])+"|");
					System.out.println();
				}
				System.out.println();
			// else output selected column
			}else{
				int[] control = new int[col.length];
				for(int j = 0; j < col.length; j++)
					for(int i = 0; i < columnName.length; i++)
						if(col[j].equals(columnName[i]))
							control[j] = i;
				// print line
				for(int j = 0; j < control.length; j++)
					System.out.print(line("-", format[control[j]]+3));
				System.out.println();
				// print column name
				for(int j = 0; j < control.length; j++)
					System.out.print(fix(format[control[j]], columnName[control[j]])+"|");
				System.out.println();
				// print line
				for(int j = 0; j < control.length; j++)
					System.out.print(line("-", format[control[j]]+3));
				System.out.println();
				// print data
				for(String[] i : content.values()){
					for(int j = 0; j < control.length; j++)
						System.out.print(fix(format[control[j]], i[control[j]])+"|");
					System.out.println();
				}
				System.out.println();
			}
		}
	}
}