// Fees update method private void updateFeesData(long id) { String columns[] = {"Course", "Fees Payed", "Total fees", "Installments"}; try { Database db = new Database(); panel_7.removeAll(); feestablemodel = new MyTableModel(db.getFeeData(id), columns); feestable = new JTable(feestablemodel); feestable.setAutoResizeMode(JTable.AUTO_RESIZE_OFF); feestable.getSelectionModel().addListSelectionListener(this); feesscrollpane = new JScrollPane(feestable); panel_7.add(feesscrollpane); // change fees payed label feespayedlabel.setText("Fees Payed"); feesduelabel.setText("Fees Due"); totalfeeslabel.setText("Total Fees"); panel_7.revalidate(); } catch (Exception e) { JOptionPane.showMessageDialog(this, e.getMessage(), null, JOptionPane.ERROR_MESSAGE); } }
/** * opens the chosen file, reads in the file, and prints out a receipt * * @param chosenFile */ private void readSource(File chosenFile) { String chosenFileName = chosenFile.getName(); TextFileInput inFile = new TextFileInput(chosenFileName); Container myContentPane = jframe.getContentPane(); // chosenFile TextArea myTextArea = new TextArea(); myContentPane.add(myTextArea); int count = 0; float priceTotal = 0.0f; Database db = new Database("database2.txt"); String[] transaction = new String[100]; String line = inFile.readLine(); DecimalFormat df = new DecimalFormat("#00.00"); while (line != null) { StringTokenizer tokenized = new StringTokenizer(line, ","); String code = tokenized.nextToken(); float weight = Float.parseFloat(tokenized.nextToken()); String name; float price; try { name = db.getName(code); } catch (ItemNotFoundException e) { name = JOptionPane.showInputDialog(null, "Item " + code + " not found. Enter Name: "); } try { price = db.getPrice(code); } catch (ItemNotFoundException e) { price = Float.valueOf( JOptionPane.showInputDialog( null, "Price for " + name + " not found. Enter price: ")); } float itemTotal = weight * price; priceTotal += itemTotal; transaction[count] = name + "\t" + price + "\t" + df.format(weight) + "\t $" + df.format(itemTotal); count++; line = inFile.readLine(); } // while myTextArea.setText("ITEM: \t PRICE\\LB: \t POUNDS: \t TOTAL:"); myTextArea.append("\n"); for (int i = 0; i < count; i++) { myTextArea.append(transaction[i]); myTextArea.append("\n"); } myTextArea.append("\t\t TOTAL: $" + df.format(priceTotal)); jframe.setVisible(true); } // openFile
// Add fee method private void addFees() { try { float feesadded = Float.parseFloat(addfeestextfield.getText()); int id = table.getSelectionModel().getMinSelectionIndex(); long studentid = (long) table.getValueAt(id, 0); int index = feestable.getSelectionModel().getMinSelectionIndex(); String coursename = (String) feestable.getValueAt(index, 0); Database db = new Database(); db.addFees(feesadded, studentid, coursename); updateFeesData(studentid); addfeestextfield.setText(""); } catch (Exception e) { JOptionPane.showMessageDialog(this, e.getMessage(), null, JOptionPane.ERROR_MESSAGE); } }
// Add course method public void addCourse() { try { Database db = new Database(); String coursename = (String) coursecombobox.getSelectedItem(); if (coursecombobox.getSelectedIndex() == 0) { throw new Exception("No course selected"); } float fees = db.getCoursefees(coursename); float totalfees = fees + (ims.main.Settings.getInstallment() * (int) spinner.getValue()); long id = (long) table.getValueAt((int) table.getSelectionModel().getMinSelectionIndex(), 0); db.addCourseToCurrentStudent(id, totalfees, (int) spinner.getValue(), coursename); updateFeesData(id); courseReset(); } catch (Exception e) { JOptionPane.showMessageDialog(this, e.getMessage(), null, JOptionPane.ERROR_MESSAGE); } }
/** * Helper function to display a Swing window with a tree representation of the specified list of * joins. See {@link #orderJoins}, which may want to call this when the analyze flag is true. * * @param js the join plan to visualize * @param pc the PlanCache accumulated whild building the optimal plan * @param stats table statistics for base tables * @param selectivities the selectivities of the filters over each of the tables (where tables are * indentified by their alias or name if no alias is given) */ private void printJoins( Vector<LogicalJoinNode> js, PlanCache pc, HashMap<String, TableStats> stats, HashMap<String, Double> selectivities) { JFrame f = new JFrame("Join Plan for " + p.getQuery()); // Set the default close operation for the window, // or else the program won't exit when clicking close button f.setDefaultCloseOperation(WindowConstants.DISPOSE_ON_CLOSE); f.setVisible(true); f.setSize(300, 500); HashMap<String, DefaultMutableTreeNode> m = new HashMap<String, DefaultMutableTreeNode>(); // int numTabs = 0; // int k; DefaultMutableTreeNode root = null, treetop = null; HashSet<LogicalJoinNode> pathSoFar = new HashSet<LogicalJoinNode>(); boolean neither; System.out.println(js); for (LogicalJoinNode j : js) { pathSoFar.add(j); System.out.println("PATH SO FAR = " + pathSoFar); String table1Name = Database.getCatalog().getTableName(this.p.getTableId(j.t1Alias)); String table2Name = Database.getCatalog().getTableName(this.p.getTableId(j.t2Alias)); // Double c = pc.getCost(pathSoFar); neither = true; root = new DefaultMutableTreeNode( "Join " + j + " (Cost =" + pc.getCost(pathSoFar) + ", card = " + pc.getCard(pathSoFar) + ")"); DefaultMutableTreeNode n = m.get(j.t1Alias); if (n == null) { // never seen this table before n = new DefaultMutableTreeNode( j.t1Alias + " (Cost = " + stats.get(table1Name).estimateScanCost() + ", card = " + stats.get(table1Name).estimateTableCardinality(selectivities.get(j.t1Alias)) + ")"); root.add(n); } else { // make left child root n root.add(n); neither = false; } m.put(j.t1Alias, root); n = m.get(j.t2Alias); if (n == null) { // never seen this table before n = new DefaultMutableTreeNode( j.t2Alias == null ? "Subplan" : (j.t2Alias + " (Cost = " + stats.get(table2Name).estimateScanCost() + ", card = " + stats .get(table2Name) .estimateTableCardinality(selectivities.get(j.t2Alias)) + ")")); root.add(n); } else { // make right child root n root.add(n); neither = false; } m.put(j.t2Alias, root); // unless this table doesn't join with other tables, // all tables are accessed from root if (!neither) { for (String key : m.keySet()) { m.put(key, root); } } treetop = root; } JTree tree = new JTree(treetop); JScrollPane treeView = new JScrollPane(tree); tree.setShowsRootHandles(true); // Set the icon for leaf nodes. ImageIcon leafIcon = new ImageIcon("join.jpg"); DefaultTreeCellRenderer renderer = new DefaultTreeCellRenderer(); renderer.setOpenIcon(leafIcon); renderer.setClosedIcon(leafIcon); tree.setCellRenderer(renderer); f.setSize(300, 500); f.add(treeView); for (int i = 0; i < tree.getRowCount(); i++) { tree.expandRow(i); } if (js.size() == 0) { f.add(new JLabel("No joins in plan.")); } f.pack(); }
/** * Return true if field is a primary key of the specified table, false otherwise * * @param tableAlias The alias of the table in the query * @param field The pure name of the field */ private boolean isPkey(String tableAlias, String field) { int tid1 = p.getTableId(tableAlias); String pkey1 = Database.getCatalog().getPrimaryKey(tid1); return pkey1.equals(field); }
/** * This is a helper method that computes the cost and cardinality of joining joinToRemove to * joinSet (joinSet should contain joinToRemove), given that all of the subsets of size * joinSet.size() - 1 have already been computed and stored in PlanCache pc. * * @param stats table stats for all of the tables, referenced by table names rather than alias * (see {@link #orderJoins}) * @param filterSelectivities the selectivities of the filters over each of the tables (where * tables are indentified by their alias or name if no alias is given) * @param joinToRemove the join to remove from joinSet * @param joinSet the set of joins being considered * @param bestCostSoFar the best way to join joinSet so far (minimum of previous invocations of * computeCostAndCardOfSubplan for this joinSet, from returned CostCard) * @param pc the PlanCache for this join; should have subplans for all plans of size * joinSet.size()-1 * @return A {@link CostCard} objects desribing the cost, cardinality, optimal subplan * @throws ParsingException when stats, filterSelectivities, or pc object is missing tables * involved in join */ @SuppressWarnings("unchecked") private CostCard computeCostAndCardOfSubplan( HashMap<String, TableStats> stats, HashMap<String, Double> filterSelectivities, LogicalJoinNode joinToRemove, Set<LogicalJoinNode> joinSet, double bestCostSoFar, PlanCache pc) throws ParsingException { LogicalJoinNode j = joinToRemove; Vector<LogicalJoinNode> prevBest; if (this.p.getTableId(j.t1Alias) == null) throw new ParsingException("Unknown table " + j.t1Alias); if (this.p.getTableId(j.t2Alias) == null) throw new ParsingException("Unknown table " + j.t2Alias); String table1Name = Database.getCatalog().getTableName(this.p.getTableId(j.t1Alias)); String table2Name = Database.getCatalog().getTableName(this.p.getTableId(j.t2Alias)); String table1Alias = j.t1Alias; String table2Alias = j.t2Alias; Set<LogicalJoinNode> news = (Set<LogicalJoinNode>) ((HashSet<LogicalJoinNode>) joinSet).clone(); news.remove(j); double t1cost, t2cost; int t1card, t2card; boolean leftPkey, rightPkey; if (news.isEmpty()) { // base case -- both are base relations prevBest = new Vector<LogicalJoinNode>(); t1cost = stats.get(table1Name).estimateScanCost(); t1card = stats.get(table1Name).estimateTableCardinality(filterSelectivities.get(j.t1Alias)); leftPkey = isPkey(j.t1Alias, j.f1PureName); t2cost = table2Alias == null ? 0 : stats.get(table2Name).estimateScanCost(); t2card = table2Alias == null ? 0 : stats.get(table2Name).estimateTableCardinality(filterSelectivities.get(j.t2Alias)); rightPkey = table2Alias == null ? false : isPkey(table2Alias, j.f2PureName); } else { // news is not empty -- figure best way to join j to news prevBest = pc.getOrder(news); // possible that we have not cached an answer, if subset // includes a cross product if (prevBest == null) { return null; } double prevBestCost = pc.getCost(news); int bestCard = pc.getCard(news); // estimate cost of right subtree if (doesJoin(prevBest, table1Alias)) { // j.t1 is in prevBest t1cost = prevBestCost; // left side just has cost of whatever // left // subtree is t1card = bestCard; leftPkey = hasPkey(prevBest); t2cost = j.t2Alias == null ? 0 : stats.get(table2Name).estimateScanCost(); t2card = j.t2Alias == null ? 0 : stats .get(table2Name) .estimateTableCardinality(filterSelectivities.get(j.t2Alias)); rightPkey = j.t2Alias == null ? false : isPkey(j.t2Alias, j.f2PureName); } else if (doesJoin(prevBest, j.t2Alias)) { // j.t2 is in prevbest // (both // shouldn't be) t2cost = prevBestCost; // left side just has cost of whatever // left // subtree is t2card = bestCard; rightPkey = hasPkey(prevBest); t1cost = stats.get(table1Name).estimateScanCost(); t1card = stats.get(table1Name).estimateTableCardinality(filterSelectivities.get(j.t1Alias)); leftPkey = isPkey(j.t1Alias, j.f1PureName); } else { // don't consider this plan if one of j.t1 or j.t2 // isn't a table joined in prevBest (cross product) return null; } } // case where prevbest is left double cost1 = estimateJoinCost(j, t1card, t2card, t1cost, t2cost); LogicalJoinNode j2 = j.swapInnerOuter(); double cost2 = estimateJoinCost(j2, t2card, t1card, t2cost, t1cost); if (cost2 < cost1) { boolean tmp; j = j2; cost1 = cost2; tmp = rightPkey; rightPkey = leftPkey; leftPkey = tmp; } if (cost1 >= bestCostSoFar) return null; CostCard cc = new CostCard(); cc.card = estimateJoinCardinality(j, t1card, t2card, leftPkey, rightPkey, stats); cc.cost = cost1; cc.plan = (Vector<LogicalJoinNode>) prevBest.clone(); cc.plan.addElement(j); // prevbest is left -- add new join to end return cc; }
public void setContent(String cat) { cat = cat.trim(); selectAllCB.setVisible(false); selectAllCB.setSelected(false); deleteBut.setVisible(false); restoreBut.setVisible(false); refreshBut.setVisible(true); Object columns[] = null; int count = 0; switch (cat) { case "Inbox": columns = new Object[] {"", "From", "Date", "Subject", "Content"}; count = Database.getCount("Inbox"); workingSet = db.getData("SELECT * FROM messages WHERE tag='inbox' ORDER BY msg_id desc"); ; break; case "SentMail": columns = new Object[] {"", "To", "Date", "Subject", "Content"}; count = Database.getCount("Sentmail"); workingSet = db.getData("SELECT * FROM messages WHERE tag='sentmail' ORDER BY msg_id desc"); break; case "Draft": columns = new Object[] {"", "To", "Date", "Subject", "Content"}; count = Database.getCount("Draft"); workingSet = db.getData("SELECT * FROM messages WHERE tag='draft' ORDER BY msg_id desc"); break; case "Outbox": columns = new Object[] {"", "To", "Date", "Subject", "Content"}; count = Database.getCount("Outbox"); workingSet = db.getData("SELECT * FROM messages WHERE tag='outbox' ORDER BY msg_id desc"); break; case "Trash": // restoreBut.setVisible(true); columns = new Object[] {"", "To/From", "Date", "Subject", "Content"}; count = Database.getCount("Trash"); workingSet = db.getData( "SELECT * FROM messages,trash WHERE messages.tag='trash' and messages.msg_id=trash.msg_id ORDER BY deleted_at desc"); break; default: System.out.println("in default case"); } if (count > 0) { selectAllCB.setVisible(true); rows = new Object[count][]; msgID = new int[count]; try { workingSet.beforeFirst(); for (int i = 0; i < count && workingSet.next(); i++) { msgID[i] = workingSet.getInt(1); rows[i] = new Object[] { false, workingSet.getString(2), workingSet.getDate(3), workingSet.getString(4), workingSet.getString(5) }; } } catch (SQLException sqlExc) { JOptionPane.showMessageDialog(null, sqlExc, "EXCEPTION", JOptionPane.ERROR_MESSAGE); sqlExc.printStackTrace(); } tableModel = new MyDefaultTableModel(rows, columns); table = new JTable(tableModel); table.getSelectionModel().addListSelectionListener(this); table.addMouseListener(this); table.getTableHeader().setOpaque(true); table.getTableHeader().setReorderingAllowed(false); // table.getTableHeader().setBackground(Color.blue); table.getTableHeader().setForeground(Color.blue); // table.setRowSelectionAllowed(false); // table.setColumnSelectionAllowed(false); table.setFont(new Font(Font.SANS_SERIF, Font.PLAIN, 14)); table.setRowHeight(20); table.setFillsViewportHeight(true); TableColumn column = null; for (int i = 0; i < 5; i++) { column = table.getColumnModel().getColumn(i); if (i == 0) { column.setPreferredWidth(6); } else if (i == 3) { column.setPreferredWidth(250); } else if (i == 4) { column.setPreferredWidth(450); } else { column.setPreferredWidth(40); } } table.setAutoResizeMode(JTable.AUTO_RESIZE_SUBSEQUENT_COLUMNS); remove(contentPan); contentPan = new JScrollPane(table); contentPan.setBackground(Color.orange); contentPan.setOpaque(true); contentPan.setBorder(BorderFactory.createEmptyBorder(0, 0, 0, 0)); add(contentPan, "Center"); Home.home.homeFrame.setVisible(true); } else { JPanel centPan = new JPanel(new GridBagLayout()); centPan.setBackground(new Color(52, 86, 70)); JLabel label = new JLabel("No Messages In This Category"); label.setFont(new Font(Font.SANS_SERIF, Font.BOLD, 22)); label.setForeground(Color.orange); centPan.add(label); remove(contentPan); contentPan = new JScrollPane(centPan); contentPan.setBorder(BorderFactory.createEmptyBorder(0, 0, 0, 0)); add(contentPan, "Center"); contentPan.repaint(); } }
@Override public void actionPerformed(ActionEvent axnEve) { Object obj = axnEve.getSource(); if (obj == selectAllCB) { Boolean state; if (selectAllCB.isSelected()) { state = true; deleteBut.setVisible(true); if (Home.titlePan.getTitle().equals("Trash")) { restoreBut.setVisible(true); } } else { state = false; deleteBut.setVisible(false); if (Home.titlePan.getTitle().equals("Trash")) { restoreBut.setVisible(false); } } for (int i = 0; i < table.getRowCount(); i++) { table.setValueAt(state, i, 0); } } else if (obj == refreshBut || obj == backBut) { setContent(Home.titlePan.getTitle()); backBut.setVisible(false); } else if (obj == deleteBut) { ArrayList selectedMessages = getSelectedMessages(); if (selectedMessages.isEmpty()) { FootPan.setMessage(FootPan.NO_SELECTION_MESSAGE); } else { int option = JOptionPane.showConfirmDialog( Home.home.homeFrame, "Are You Sure?", "DELETE", JOptionPane.YES_NO_OPTION, JOptionPane.WARNING_MESSAGE); if (option == 0) { Database.deleteMessages(selectedMessages, Home.titlePan.getTitle()); setContent(Home.titlePan.getTitle()); } } } else if (obj == restoreBut) { ArrayList selectedMessages = getSelectedMessages(); if (selectedMessages.isEmpty()) { FootPan.setMessage(FootPan.NO_SELECTION_MESSAGE); } else { int option = JOptionPane.showConfirmDialog( Home.home.homeFrame, "Are You Sure?", "RESTORE", JOptionPane.YES_NO_OPTION, JOptionPane.WARNING_MESSAGE); if (option == 0) { Database.restoreMessages(selectedMessages); setContent(Home.titlePan.getTitle()); } } } }
public void update_table() { Database.showDB("transaction", transaction_column, info_data, info_table); }
public void update_table() { Database.showDB("member", member_column, info_data, info_table); }
@SuppressWarnings({"unchecked", "rawtypes"}) private void initializeComponent() { // Initialize panels panel_1 = new JPanel(); panel_2 = new JPanel(); panel_3 = new JPanel(); panel_4 = new JPanel(); panel_5 = new JPanel(); panel_6 = new JPanel(); panel_7 = new JPanel(); // Initialize spinner spinnermodel = new SpinnerNumberModel(0, 0, 10, 1); spinner = new JSpinner(spinnermodel); // Initialize combo box searchcombobox = new JComboBox(); searchcombobox.addItem("All"); searchcombobox.addItem("By Name"); searchcombobox.addItem("By Guardian Name"); searchcombobox.addItem("By Course"); searchcombobox.addItem("Due Fees"); searchcombobox.setSelectedIndex(0); coursecombobox = new JComboBox(); coursecombobox.addItem("Select Course"); Database db = new Database(); String courses[] = db.getCourses(); if (courses != null) { for (String x : courses) { coursecombobox.addItem(x); } } // Initialize text field searchtextfield = new JTextField(10); addfeestextfield = new JTextField(8); // Initialize button searchbutton = new JButton("Search"); addfeesbutton = new JButton("Add fees"); addcoursebutton = new JButton("Add Course"); // Initialize check box allfieldcheckbox = new JCheckBox("Show all fields"); allfieldcheckbox.setSelected(true); // Initialize label totalstudentlabel = new JLabel("Total Student"); feespayedlabel = new JLabel("Fees Payed"); feesduelabel = new JLabel("Fees due"); totalfeeslabel = new JLabel("Total fees"); // Add Item listener to check box allfieldcheckbox.addItemListener(this); // Add Action Listener to button searchbutton.addActionListener(this); addfeesbutton.addActionListener(this); addcoursebutton.addActionListener(this); // Add Key Listener to button searchbutton.addKeyListener(this); addfeesbutton.addKeyListener(this); addcoursebutton.addKeyListener(this); }
// Method to update Table and related components private void update() { Database db = new Database(); String column[] = { "ID", "NAME", "GENDER", "GUARDIAN_ROLE", "GUARDIAN_NAME", "PRESENT_ADDRESS", "PRESENT_CITY", "PRESENT_PHONE" }; try { // Update table if (searchcombobox.getSelectedIndex() == 0 && allfieldcheckbox.isSelected()) { mytablemodel = new MyTableModel(db.getAllStudent(), db.getColumnNameFromStudent()); } else if (searchcombobox.getSelectedIndex() == 0 && !allfieldcheckbox.isSelected()) { mytablemodel = new MyTableModel(db.getSomeFieldsFromStudent(), column); } else if (searchcombobox.getSelectedIndex() == 1 && allfieldcheckbox.isSelected()) { String query = "SELECT * FROM Student WHERE NAME = \'" + searchtextfield.getText().trim() + "\'"; mytablemodel = new MyTableModel(db.getData(query), db.getColumnNameFromStudent()); } else if (searchcombobox.getSelectedIndex() == 1 && !allfieldcheckbox.isSelected()) { String query = "SELECT ID, NAME, GENDER, GUARDIAN_ROLE, GUARDIAN_NAME, PRESENT_ADDRESS, PRESENT_CITY, PRESENT_PHONE FROM Student WHERE NAME = \'" + searchtextfield.getText().trim() + "\'"; mytablemodel = new MyTableModel(db.getData(query), column); } else if (searchcombobox.getSelectedIndex() == 2 && allfieldcheckbox.isSelected()) { String query = "SELECT * FROM Student WHERE GUARDIAN_NAME = \'" + searchtextfield.getText().trim() + "\'"; mytablemodel = new MyTableModel(db.getData(query), db.getColumnNameFromStudent()); } else if (searchcombobox.getSelectedIndex() == 2 && !allfieldcheckbox.isSelected()) { String query = "SELECT ID, NAME, GENDER, GUARDIAN_ROLE, GUARDIAN_NAME, PRESENT_ADDRESS, PRESENT_CITY, PRESENT_PHONE FROM Student WHERE GUARDIAN_NAME = \'" + searchtextfield.getText().trim() + "\'"; mytablemodel = new MyTableModel(db.getData(query), column); } else if (searchcombobox.getSelectedIndex() == 3 && allfieldcheckbox.isSelected()) { String query = "SELECT * FROM Student WHERE ID = ANY(SELECT SID FROM Fee WHERE CID = ANY(SELECT ID FROM CourseInfo WHERE NAME = \'" + searchtextfield.getText().trim() + "\'))"; mytablemodel = new MyTableModel(db.getData(query), db.getColumnNameFromStudent()); } else if (searchcombobox.getSelectedIndex() == 3 && !allfieldcheckbox.isSelected()) { String query = "SELECT ID, NAME, GENDER, GUARDIAN_ROLE, GUARDIAN_NAME, PRESENT_ADDRESS, PRESENT_CITY, PRESENT_PHONE FROM Student WHERE ID = ANY(SELECT SID FROM Fee WHERE CID = ANY(SELECT ID FROM CourseInfo WHERE NAME = \'" + searchtextfield.getText().trim() + "\'))"; mytablemodel = new MyTableModel(db.getData(query), column); } else if (searchcombobox.getSelectedIndex() == 4 && allfieldcheckbox.isSelected()) { String query = "SELECT * FROM Student WHERE ID = ANY(SELECT SID FROM Fee WHERE TOTAL_FEES - FEES_PAYED >= 0)"; mytablemodel = new MyTableModel(db.getData(query), db.getColumnNameFromStudent()); } else { String query = "SELECT ID, NAME, GENDER, GUARDIAN_ROLE, GUARDIAN_NAME, PRESENT_ADDRESS, PRESENT_CITY, PRESENT_PHONE FROM Student WHERE ID = ANY(SELECT SID FROM Fee WHERE TOTAL_FEES - FEES_PAYED >= 0)"; mytablemodel = new MyTableModel(db.getData(query), column); } table = new JTable(mytablemodel); table.setAutoResizeMode(JTable.AUTO_RESIZE_OFF); // Add list selection listener to table table.getSelectionModel().addListSelectionListener(this); TableColumn col = null; for (int i = 3; i < table.getColumnCount(); i++) { col = table.getColumnModel().getColumn(i); col.setPreferredWidth(200); } scrollpane = new JScrollPane(table); panel_3.removeAll(); panel_3.add(scrollpane); // Update total student label int total = db.getTotalStudent(); totalstudentlabel.setText("Total Student = " + total); // Clear search combo box searchtextfield.setText(""); this.revalidate(); } catch (Exception e) { JOptionPane.showMessageDialog(this, e.getMessage(), "Error", JOptionPane.ERROR_MESSAGE); } }
/** * This is a helper method that computes the cost and cardinality of joining a LogicalJoinNode j * to the current greedy plan we have built up. * * @param j the join to try adding to our plan * @param plan the current plan we have built so far from the greedy algorithm, a Vector of * LogicalJoinNodes that we've so far chosen. * @param planCardinalities given the join order from plan, we also keep track of how large joined * tables are, so we can help estimate the cardinality and cost of this next join * @param planCosts given the join order from plan, we also keep track of how expensive executing * some joins are, so we can help estimate the cardinality and cost of this next join * @param stats table stats for all of the tables, referenced by table names rather than alias * (see {@link #orderGreedyJoins(HashMap, HashMap)}) * @param filterSelectivities the selectivities of the filters over each of the tables (where * tables are indentified by their alias or name if no alias is given) * @return A {@link CostCard} objects desribing the cost, cardinality, optimal subplan * @throws ParsingException when stats, filterSelectivities, or pc object is missing tables * involved in join */ private CostCard costGreedyJoin( LogicalJoinNode j, Vector<LogicalJoinNode> plan, Vector<Integer> planCardinalities, Vector<Double> planCosts, HashMap<String, TableStats> stats, HashMap<String, Double> filterSelectivities) throws ParsingException { if (this.p.getTableId(j.t1Alias) == null) throw new ParsingException("Unknown table " + j.t1Alias); if (this.p.getTableId(j.t2Alias) == null) throw new ParsingException("Unknown table " + j.t2Alias); String table1Name = Database.getCatalog().getTableName(this.p.getTableId(j.t1Alias)); String table2Name = Database.getCatalog().getTableName(this.p.getTableId(j.t2Alias)); String table1Alias = j.t1Alias; String table2Alias = j.t2Alias; double t1cost, t2cost; int t1card, t2card; boolean leftPkey, rightPkey; // estimate cost of right subtree if (doesJoin(plan, table1Alias)) { // j.t1 is in plan already CostCard c = getCostCard(plan, planCardinalities, planCosts, table1Alias); t1cost = c.cost; // left side just has cost of whatever left subtree is t1card = c.card; leftPkey = hasPkey(plan); t2cost = j.t2Alias == null ? 0 : stats.get(table2Name).estimateScanCost(); t2card = j.t2Alias == null ? 0 : stats.get(table2Name).estimateTableCardinality(filterSelectivities.get(j.t2Alias)); rightPkey = j.t2Alias == null ? false : isPkey(j.t2Alias, j.f2PureName); } else if (doesJoin(plan, j.t2Alias)) { // j.t2 is in plan // (else if since both j.t1 and j.t2 shouldn't both be) CostCard c = getCostCard(plan, planCardinalities, planCosts, table2Alias); t2cost = c.cost; t2card = c.card; rightPkey = hasPkey(plan); t1cost = stats.get(table1Name).estimateScanCost(); t1card = stats.get(table1Name).estimateTableCardinality(filterSelectivities.get(j.t1Alias)); leftPkey = isPkey(j.t1Alias, j.f1PureName); } else { // Neither is a plan, both are just single tables t1cost = stats.get(table1Name).estimateScanCost(); t1card = stats.get(table1Name).estimateTableCardinality(filterSelectivities.get(j.t1Alias)); leftPkey = isPkey(j.t1Alias, j.f1PureName); t2cost = table2Alias == null ? 0 : stats.get(table2Name).estimateScanCost(); t2card = table2Alias == null ? 0 : stats.get(table2Name).estimateTableCardinality(filterSelectivities.get(j.t2Alias)); rightPkey = table2Alias == null ? false : isPkey(table2Alias, j.f2PureName); } double cost1 = estimateJoinCost(j, t1card, t2card, t1cost, t2cost); LogicalJoinNode j2 = j.swapInnerOuter(); double cost2 = estimateJoinCost(j2, t2card, t1card, t2cost, t1cost); if (cost2 < cost1) { boolean tmp; j = j2; cost1 = cost2; tmp = rightPkey; rightPkey = leftPkey; leftPkey = tmp; } CostCard cc = new CostCard(); cc.card = estimateJoinCardinality(j, t1card, t2card, leftPkey, rightPkey, stats); cc.cost = cost1; return cc; }