@Override
public ArrayList<Page> getNonParents(Page page) {
ArrayList<Page> result = new ArrayList<Page>();
if (page != null) {
try {
cmdString =
"select id from pages where project = ? except (select distinct children.parent from children where children.child = ?)";
PreparedStatement s = db.prepareStatement(cmdString);
s.setString(1, page.getProjectID());
s.setString(2, page.getID());
ResultSet row = s.executeQuery();
while (row.next()) {
String id = row.getString("id");
result.add(getPage(id));
}
} catch (Exception e) {
processSQLError(e);
}
}
return result;
}
@Override
public void setParent(Page parent, Page child) {
try {
cmdString = "insert into children (parent, child) values (?, ?)";
PreparedStatement s = db.prepareStatement(cmdString);
s.setString(1, parent.getID());
s.setString(2, child.getID());
s.executeUpdate();
} catch (Exception e) {
processSQLError(e);
}
}
@Override
public String updatePage(Project project, Page page) {
try {
cmdString = "update pages set title = ?, body = ?, views = ? where id = ?";
PreparedStatement s = db.prepareStatement(cmdString);
s.setString(1, page.getTitle());
s.setString(2, page.getMarkdown());
s.setInt(3, page.getViewCount());
s.setString(4, page.getID());
s.executeUpdate();
} catch (Exception e) {
processSQLError(e);
}
return null;
}
@Override
public String status(Page page) {
if (page == null || page.getID() == null) return "Null Data";
try {
cmdString = "select * from pages where (id = ? or title = ?) and project = ?";
PreparedStatement s = db.prepareStatement(cmdString);
s.setString(1, page.getID());
s.setString(2, page.getTitle());
s.setString(3, page.getProjectID());
ResultSet row = s.executeQuery();
if (row.next()) {
String id = row.getString("id");
String title = row.getString("title");
if (id.equals(page.getID())) {
return "ID Exists";
} else if (title.equals(page.getTitle())) {
return "Duplicate Title Exists";
}
}
row.close();
} catch (Exception e) {
processSQLError(e);
return "Error";
}
return "Unique";
}
@Override
public String insertPage(Project project, Page page) {
if (page == null) {
return "ERROR: Page cannot be null";
}
try {
cmdString = "insert into pages (id, body, title, views, project) values (?, ?, ?, ?, ?)";
PreparedStatement s = db.prepareStatement(cmdString);
s.setString(1, page.getID());
s.setString(2, page.getMarkdown());
s.setString(3, page.getTitle());
s.setInt(4, 0);
s.setString(5, page.getProjectID());
// On delete cascade will delete categories and pages involved.
s.executeUpdate();
} catch (Exception e) {
processSQLError(e);
}
return null;
}
@Override
public String deletePage(Project project, Page page) {
try {
cmdString = "delete from pages where id = ?";
PreparedStatement s = db.prepareStatement(cmdString);
s.setString(1, page.getID());
s.executeUpdate();
} catch (Exception e) {
processSQLError(e);
}
return null;
}
@Override
public ArrayList<Page> getChildren(Page page) {
ArrayList<Page> result = new ArrayList<Page>();
try {
cmdString = "select child from children where parent = ?";
PreparedStatement s = db.prepareStatement(cmdString);
s.setString(1, page.getID());
ResultSet row = s.executeQuery();
while (row.next()) {
String child = row.getString("child");
result.add(getPage(child));
}
row.close();
} catch (Exception e) {
processSQLError(e);
}
return result;
}
@Override
public ArrayList<Page> getParents(Page page) {
ArrayList<Page> result = new ArrayList<Page>();
try {
cmdString = "select * from children where child = ?";
PreparedStatement statement = db.prepareStatement(cmdString);
statement.setString(1, page.getID());
ResultSet row = statement.executeQuery();
// This only ever gets the first one!!!
while (row.next()) {
String parent = row.getString("parent");
result.add(getPage(parent));
}
row.close();
} catch (Exception e) {
processSQLError(e);
}
return result;
}