/* * QueryBuilder.java */ package dblpconverter; /** * * @author Gersk * * QueryBuolder class provide to dinamically construct the query from the Element data. */ public class QueryBuilder { private Window window; private DatabaseManager dbManager; private Cite cite; private boolean tablefile = false; public QueryBuilder() { } public void setWindow(Window window) { this.window = window; } public void setDBManager(DatabaseManager dbManager) { this.dbManager = dbManager; } public void setTableFile(boolean tablefile) { this.tablefile = tablefile; } public void createTables() { query("CREATE TABLE IF NOT EXISTS articles(id VARCHAR(150), " + "mdate VARCHAR(20), " + "reviewid VARCHAR(20), " + "rating VARCHAR(10), " + "title VARCHAR(400), " + "url VARCHAR(100), " + "month VARCHAR(10)," + "year VARCHAR(10)," + "crossref VARCHAR(100), " + "ee VARCHAR(100)," + "note VARCHAR(20), " + "PRIMARY KEY(id)) " + "ENGINE=InnoDB "); query("CREATE TABLE IF NOT EXISTS proceedings(id VARCHAR(150), " + "mdate VARCHAR(20)," + "title VARCHAR(500)," + "url VARCHAR(200), " + "month VARCHAR(10)," + "year VARCHAR(10)," + "isbn VARCHAR(100), " + "ee VARCHAR(100)," + "note VARCHAR(20)," + "PRIMARY KEY(id)) " + "ENGINE=InnoDB "); query("CREATE TABLE IF NOT EXISTS inproceedings(id VARCHAR(150), " + "mdate VARCHAR(20)," + "title VARCHAR(400)," + "url VARCHAR(200)," + "crossref VARCHAR(30)," + "booktitle VARCHAR(250)," + "pages VARCHAR(10)," + "month VARCHAR(10)," + "year VARCHAR(10)," + "cdrom VARCHAR(20)," + "ee VARCHAR(100), " + "note VARCHAR(20)," + "PRIMARY KEY(id)) " + "ENGINE=InnoDB "); query("CREATE TABLE IF NOT EXISTS incollections(id VARCHAR(150), " + "mdate VARCHAR(20)," + "title VARCHAR(400)," + "crossref VARCHAR(100)," + "booktitle VARCHAR(250)," + "chapter VARCHAR(10)," + "pages VARCHAR(10)," + "url VARCHAR(200)," + "month VARCHAR(10)," + "year VARCHAR(10)," + "ee VARCHAR(100)," + "note VARCHAR(300)," + "PRIMARY KEY(id)) " + "ENGINE=InnoDB "); query("CREATE TABLE IF NOT EXISTS master_Thesis(id VARCHAR(150), " + "mdate VARCHAR(20)," + "title VARCHAR(400)," + "school VARCHAR(150)," + "month VARCHAR(10)," + "year VARCHAR(10)," + "note VARCHAR(300)," + "PRIMARY KEY(id)) " + "ENGINE=InnoDB "); query("CREATE TABLE IF NOT EXISTS phd_Thesis(id VARCHAR(150), " + "mdate VARCHAR(20)," + "title VARCHAR(400)," + "school VARCHAR(150)," + "month VARCHAR(10)," + "year VARCHAR(10)," + "note VARCHAR(300)," + "PRIMARY KEY(id)) " + "ENGINE=InnoDB "); query("CREATE TABLE IF NOT EXISTS books(id VARCHAR(150)," + "mdate VARCHAR(20), " + "title VARCHAR(400)," + "isbn VARCHAR(20)," + "cdrom VARCHAR(20)," + "url VARCHAR(200)," + "month VARCHAR(10)," + "year VARCHAR(10)," + "note VARCHAR(300)," + "PRIMARY KEY(id)) " + "ENGINE=InnoDB "); query("CREATE TABLE IF NOT EXISTS publishers(name VARCHAR(100), " + "address VARCHAR(50)," + "href VARCHAR(50)," + "PRIMARY KEY(name)) " + "ENGINE=InnoDB "); query("CREATE TABLE IF NOT EXISTS series(name VARCHAR(100), " + "href VARCHAR(50)," + "PRIMARY KEY(name)) " + "ENGINE=InnoDB "); query("CREATE TABLE IF NOT EXISTS www(id VARCHAR(150), " + "mdate VARCHAR(20)," + "title VARCHAR(400)," + "url VARCHAR(200)," + "month VARCHAR(10)," + "year VARCHAR(10)," + "note VARCHAR(300)," + "PRIMARY KEY(id)) " + "ENGINE=InnoDB "); query("CREATE TABLE IF NOT EXISTS cites(id VARCHAR(150), " + "label VARCHAR(100)," + "text VARCHAR(300)," + "PRIMARY KEY(id, label)) " + "ENGINE=InnoDB "); query("CREATE TABLE IF NOT EXISTS authors(id VARCHAR(150), " + "name VARCHAR(100), " + "PRIMARY KEY (id, name)) " + "ENGINE=InnoDB "); query("CREATE TABLE IF NOT EXISTS editors(id VARCHAR(150), " + "name VARCHAR(100), " + "PRIMARY KEY (id, name)) " + "ENGINE=InnoDB "); query("CREATE TABLE IF NOT EXISTS published_1(id VARCHAR(150)," + "name VARCHAR(100)," + "PRIMARY KEY (id, name)," + "FOREIGN KEY (name) REFERENCES Publishers(name)," + "FOREIGN KEY (id) REFERENCES Proceedings(id)) " + "ENGINE=InnoDB "); query("CREATE TABLE IF NOT EXISTS published_2(id VARCHAR(150)," + "name VARCHAR(100)," + "PRIMARY KEY (id, name)," + "FOREIGN KEY (name) REFERENCES Publishers(name)," + "FOREIGN KEY (id) REFERENCES Books(id)) " + "ENGINE=InnoDB "); query("CREATE TABLE IF NOT EXISTS in_a(id VARCHAR(150)," + "name VARCHAR(100)," + "volume VARCHAR(20)," + "PRIMARY KEY (id, name)," + "FOREIGN KEY (id) REFERENCES Proceedings(id)," + "FOREIGN KEY (name) REFERENCES Series(name)) " + "ENGINE=InnoDB "); query("CREATE TABLE IF NOT EXISTS in_1(id VARCHAR(150)," + "name VARCHAR(150)," + "volume VARCHAR(20)," + "number VARCHAR(30)," + "pages VARCHAR(30)," + "PRIMARY KEY (id, name, volume, number)) " + "ENGINE=InnoDB "); tablefile = true; } public void dropTables() { query("DROP TABLE in_1"); query("DROP TABLE in_a"); query("DROP TABLE published_1"); query("DROP TABLE published_2"); query("DROP TABLE authors"); query("DROP TABLE editors"); query("DROP TABLE cites"); query("DROP TABLE www"); query("DROP TABLE series"); query("DROP TABLE publishers"); query("DROP TABLE books"); query("DROP TABLE phd_Thesis"); query("DROP TABLE master_Thesis"); query("DROP TABLE incollections"); query("DROP TABLE inproceedings"); query("DROP TABLE proceedings"); query("DROP TABLE articles"); } public void eraseData() { query("DELETE FROM in_1"); query("DELETE FROM in_a"); query("DELETE FROM published_1"); query("DELETE FROM published_2"); query("DELETE FROM authors"); query("DELETE FROM editors"); query("DELETE FROM cites"); query("DELETE FROM www"); query("DELETE FROM series"); query("DELETE FROM publishers"); query("DELETE FROM books"); query("DELETE FROM phd_Thesis"); query("DELETE FROM master_Thesis"); query("DELETE FROM incollections"); query("DELETE FROM inproceedings"); query("DELETE FROM proceedings"); query("DELETE FROM articles"); } public void setTableValue(boolean value) { this.tablefile = value; } public void addElement(Element element) { if(element.type == element.BOOK && window.booksCb.isSelected()) { query("INSERT INTO books(id, mdate, title, isbn, cdrom, url, month, year, note) " + "VALUES('" + element.key + "','" + element.mdate + "','" + element.title + "','" + element.isbn + "','" + element.cdrom + "','" + element.url + "','" + element.month + "','" + element.year + "','" + element.note + "') "); for(int i = 0 ; i < element.author.size() ; i++) query("INSERT INTO authors(id, name) VALUES('" + element.key + "','" + element.author.elementAt(i).toString() + "') "); for(int i = 0 ; i < element.editor.size() ; i++) query("INSERT INTO editors(id, name) VALUES('" + element.key + "','" + element.editor.elementAt(i).toString() + "') "); if(!element.publisher.equals("")) { query("INSERT INTO publishers(name, address, href) VALUES('" + element.publisher + "','" + element.address_publisher + "','" + element.href_publisher + "') "); query("INSERT INTO published_2(id, name) VALUES('" + element.key + "','" + element.publisher + "') "); } for(int i = 0 ; i < element.cites.size() ; i++) { cite = (Cite) element.cites.elementAt(i); query("INSERT INTO cites(id, label, text) VALUES('" + element.key + "','" + cite.label + "','" + cite.text + "') "); } } if(element.type == element.ARTICLE && window.articlesCb.isSelected()) { query("INSERT INTO articles(id, mdate, reviewid, rating, title, url, month, year, crossref, ee, note) " + "VALUES('" + element.key + "','" + element.mdate + "','" + element.reviewid + "','" + element.rating + "','" + element.title + "','" + element.url + "','" + element.month + "','" + element.year + "','" + element.crossref + "','" + element.ee + "','" + element.note + "') "); query("INSERT INTO in_1(id, name, volume, number, pages) " + "VALUES('" + element.key + "','" + element.name + "','" + element.volume + "','" + element.number + "','" + element.pages +"') "); for(int i = 0 ; i < element.author.size() ; i++) query("INSERT INTO authors(id, name) VALUES('" + element.key + "','" + element.author.elementAt(i).toString() + "') "); } if(element.type == element.INCOLLECTION && window.incollectionsCb.isSelected()) { query("INSERT INTO incollections(id, mdate, title, crossref, booktitle, chapter, pages, url, month, year, ee, note) " + "VALUES('" + element.key + "','" + element.mdate + "','" + element.title + "','" + element.crossref + "','" + element.booktitle + "','" + element.chapter + "','" + element.pages + "','" + element.url + "','" + element.month + "','" + element.year + "','" + element.ee + "','" + element.note + "') "); for(int i = 0 ; i < element.author.size() ; i++) query("INSERT INTO authors(id, name) VALUES('" + element.key + "','" + element.author.elementAt(i).toString() + "') "); for(int i = 0 ; i < element.editor.size() ; i++) query("INSERT INTO editors(id, name) VALUES('" + element.booktitle + "','" + element.editor.elementAt(i).toString() + "') "); } if(element.type == element.PROCEEDINGS && window.proceedingsCb.isSelected()) { query("INSERT INTO proceedings(id, mdate, title, url, month, year, isbn, ee, note) " + "VALUES('" + element.key + "','" + element.mdate + "','" + element.title + "','" + element.url + "','" + element.month + "','" + element.year + "','" + element.isbn + "','" + element.ee + "','" + element.note + "') "); for(int i = 0 ; i < element.editor.size() ; i++) query("INSERT INTO editors(id, name) VALUES('" + element.key + "','" + element.editor.elementAt(i).toString() + "') "); if(!element.publisher.equals("")) { query("INSERT INTO publishers(name, address, href) VALUES('" + element.publisher + "','" + element.address_publisher + "','" + element.href_publisher + "') "); query("INSERT INTO published_1(id, name) VALUES('" + element.key + "','" + element.publisher + "') "); } if(!element.series.equals("")) { query("INSERT INTO series(name, href) VALUES('" + element.series + "','" + element.href_series + "') "); query("INSERT INTO in_a(id, name, volume) VALUES('" + element.key + "','" + element.series + "','" + element.volume + "') "); } for(int i = 0 ; i < element.cites.size() ; i++) { cite = (Cite) element.cites.elementAt(i); query("INSERT INTO cites(id, text, label) VALUES('" + element.key + "','" + cite.text + "','" + cite.label + "') "); } } if(element.type == element.INPROCEEDINGS && window.incollectionsCb.isSelected()) { query("INSERT INTO inproceedings(id, mdate, title, url, crossref, booktitle, pages, month, year, cdrom, ee, note) " + "VALUES('" + element.key + "','" + element.mdate + "','" + element.title + "','" + element.url + "','" + element.crossref + "','" + element.booktitle + "','" + element.pages + "','" + element.month + "','" + element.year + "','" + element.cdrom + "','" + element.ee + "','" + element.note + "') "); for(int i = 0 ; i < element.author.size() ; i++) query("INSERT INTO authors(id, name) VALUES('" + element.key + "','" + element.author.elementAt(i).toString() + "') "); } if(element.type == element.PHDTHESIS && window.phd_thesisCb.isSelected()) { query("INSERT INTO phd_Thesis(id, mdate, title, school, month, year, note) " + "VALUES('" + element.key + "','" + element.mdate + "','" + element.title + "','" + element.school + "','" + element.month + "','" + element.year + "','" + element.note + "') "); for(int i = 0 ; i < element.author.size() ; i++) query("INSERT INTO authors(id, name) VALUES('" + element.key + "','" + element.author.elementAt(i).toString() + "') "); } if(element.type == element.MASTERTHESIS && window.master_thesisCb.isSelected()) { query("INSERT INTO master_Thesis(id, mdate, title, school, month, year, note) " + "VALUES('" + element.key + "','" + element.mdate + "','" + element.title + "','" + element.school + "','" + element.month + "','" + element.year + "','" + element.note + "') "); for(int i = 0 ; i < element.author.size() ; i++) query("INSERT INTO authors(id, name) VALUES('" + element.key + "','" + element.author.elementAt(i).toString() + "') "); } if(element.type == element.WWW && window.wwwCb.isSelected()) { query("INSERT INTO www(id, mdate, title, url, month, year, note) VALUES('" + element.key + "','" + element.mdate + "','" + element.title + "','" + element.url + "','" + element.month + "','" + element.year + "','" + element.note + "') "); for(int i = 0 ; i < element.author.size() ; i++) query("INSERT INTO authors(id, name) VALUES('" + element.key + "','" + element.author.elementAt(i).toString() + "') "); for(int i = 0 ; i < element.editor.size() ; i++) query("INSERT INTO editors(id, name) VALUES('" + element.key + "','" + element.editor.elementAt(i).toString() + "') "); } } public void query(String query) { if(tablefile == true) { dbManager.query(query,'s'); } else dbManager.query(query,'t'); } }