Mike Haberman wrote:
>
> So I just downloaded jyve and installed it. I am getting
> SQL exceptions from the postgres driver (6.4).
>
> Postgress dosn't allow left outer joins or use of the 'if' function
> so the following query (there could be more) dosn't work:
>
> select question.question_id, question.question_value, question.topic_id,
> question.released,
> sum(if((answer.deleted='N' AND (answer.released='Y' OR
> answer.visitorid=-1)),1,0)) as answer_count
> from question left join answer
> on question.question_id = answer.question_id where question.topic_id in (6)
> and question.deleted='N' AND ( question.released='Y' OR
> question.visitorid=-1)
> group by question.question_id, question.question_value, question.topic_id
> order by question.topic_id,
>
Hi Mike
I adapted jyve to work with oracle and had the same problem. Here's my
solution, which extracts the answer count too:
public class DisplayTopics extends Screen
{
public ConcreteElement doBuild( RunData data ) throws Exception
{
data.setTitle(Localization.getString("DISPLAYTOPICS_TITLE"));
Boolean showEdit = (Boolean)data.getUser().getTemp("showedit",
new Boolean(false));
if ( showEdit == null )
showEdit = new Boolean(false);
JSecurityCheck secCheck = new JSecurityCheck(data);
// boolean
showReleaseButton=secCheck.permissionToRelease("topic");
boolean showUnreleased = ( secCheck.permissionToRelease("topic")
)
|| (
secCheck.permissionToSeeUnreleased("topic") );
int visitorId = data.getUser().getId();
Integer project_id = (Integer)
data.getUser().getTemp("project_id",
data.getParameters().getInteger("project_id", -1 ) );
if ( project_id == null || project_id.intValue() == -1 )
{
data.setMessage(Localization.getString("DISPLAYTOPICS_NOVALIDPRJ"));
return ScreenLoader.getInstance().eval ( data, "MainMenu" );
}
Integer faq_id = (Integer) data.getUser().getTemp("faq_id",
data.getParameters().getInteger("faq_id", -1 ) );
if ( faq_id == null || faq_id.intValue() == -1 )
{
data.setMessage(Localization.getString("DISPLAYTOPICS_NOVALIDFAQTOPIC"));
return ScreenLoader.getInstance().eval ( data, "DisplayFaqs"
);
}
// reset future screens so that TopNavBar renders properly
data.getUser().removeTemp ( "topic_id" );
data.getUser().removeTemp ( "topic_value" );
ElementContainer ec = new ElementContainer();
// show the top bar
ec.addElement ( new P() );
ec.addElement (
NavigationLoader.getInstance().eval ( data, "TopBar" ) );
ec.addElement ( new P() );
ec.addElement ( new
Font().addElement(Localization.getString("DISPLAYTOPICS_CHOOSETORQ")).setSize(4)
);
StringBuffer sql = new StringBuffer();
sql.append ( "select" );
sql.append ( " topic.topic_id, topic.topic_value,
topic.released" );
sql.append ( " from" );
sql.append ( " topic, faq" );
sql.append ( " where" );
sql.append ( " faq.faq_id=topic.faq_id" );
sql.append ( " and" );
sql.append ( " topic.faq_id=" );
sql.append ( faq_id );
sql.append ( " and" );
sql.append ( " topic.deleted='N'" );
if (!showUnreleased)
{
// select if the topic is released or the topic was added by
the current visitor
sql.append ( "AND ( topic.released='Y' OR
topic.visitorid=");
sql.append ( visitorId );
sql.append ( ")");
}
sql.append ( " and" );
sql.append ( " faq.deleted='N'" );
sql.append ( " order by" );
sql.append ( " topic.display_order" );
// Order alphabetical, if same order number
sql.append ( ",topic.topic_value" );
String colorTableBackground = TurbineResources.
getString("jyve.display.color.table.background");
Table table = new Table().setBorder(1)
.setBgColor(colorTableBackground).setWidth("100%")
.setCellPadding(25).setCellSpacing(1);
TD td = new TD();
buildTopicTree ( data, sql, td );
ec.addElement(table.addElement(new TR().addElement(td)));
if ( showEdit.booleanValue() )
{
ec.addElement ( new P() );
ec.addElement (
new A().setHref(
new DynamicURI(data, "NewTopic", "SetAll")
.addPathInfo("project_id", project_id)
.addPathInfo("faq_id", faq_id)
.toString()
).addElement(Localization.getString("DISPLAYTOPICS_ADDNEWTOPIC"))
);
}
return ec;
}
static void buildTopicTree( RunData data, StringBuffer sql, TD ec )
throws Exception
{
JSecurityCheck secCheck = new JSecurityCheck(data);
boolean showUnreleasedQuestion =
( secCheck.permissionToRelease("question") )
|| ( secCheck.permissionToSeeUnreleased("question") );
boolean showUnreleasedAnswer =
( secCheck.permissionToRelease("answer") )
|| ( secCheck.permissionToSeeUnreleased("answer") );
int visitorId = data.getUser().getId();
// get a connection to the db
DBConnection db = DBBroker.getInstance().getConnection();
Connection connection = db.getConnection();
// execute the query
QueryDataSet qds = new QueryDataSet( connection, sql.toString()
);
OL topic_ol = new OL();
try
{
qds.fetchRecords();
int size = qds.size();
if ( size == 0 )
{
ec.addElement ( new P() );
ec.addElement
(Localization.getString("DISPLAYTOPICS_NOTOPICS"));
return;
}
int[] topic_ids = new int[size];
String[] topic_values = new String[size];
boolean[] topic_released = new boolean[size];
for (int i=0;i<size;i++)
{
topic_ids[i] =
qds.getRecord(i).getValue("topic_id").asInt();
topic_values[i] =
qds.getRecord(i).getValue("topic_value").asString();
if (
qds.getRecord(i).getValue("released").asString().equalsIgnoreCase("Y") )
topic_released[i]=true;
else
topic_released[i]=false;
}
sql = new StringBuffer();
sql.append ( "select" );
sql.append ( " question.question_id,
question.question_value, question.topic_id, question.released" );
if ( !showUnreleasedAnswer )
{
// count if an answer is not deleted and
// if it is released or it was created by the current
visitor
/*
sql.append ( " , sum(if((answer.deleted='N' AND
(answer.released='Y' OR answer.visitorid=");
sql.append ( visitorId );
sql.append ( ")),1,0)) as answer_count");
*/
sql.append( " , (select count(answer.answer_id) from
answer where answer.question_id = question.question_id and
answer.deleted='N' AND (answer.released='Y' OR answer.visitorid=");
sql.append ( visitorId ) ;
sql.append ( ")) as answer_count" );
}
else
/*
sql.append ( " , sum(if(answer.deleted='N',1,0)) as
answer_count");
*/
sql.append( " , (select count(answer.answer_id) from
answer where answer.question_id = question.question_id and
answer.deleted='N') as answer_count");
sql.append ( " from" );
sql.append ( " question" );
/*
sql.append ( " left join answer on" );
sql.append ( " question.question_id = answer.question_id" );
*/
sql.append ( " where" );
sql.append ( " question.topic_id in (" );
boolean comma = false;
for (int i=0;i<size;i++)
{
if ( ! comma )
{
sql.append ( topic_ids[i] );
comma = true;
}
else
{
sql.append ( "," );
sql.append ( topic_ids[i] );
}
}
sql.append ( " )" );
sql.append ( " and" );
sql.append ( " question.deleted='N'" );
if (!showUnreleasedQuestion)
{
// select if the question is released or it was created
by the current visitor
sql.append ( "AND ( question.released='Y' OR
question.visitorid=");
sql.append ( visitorId );
sql.append ( ")");
}
//sql.append ( " group by" );
//sql.append ( " question.question_id,
question.question_value, question.topic_id" );
sql.append ( " order by" );
sql.append ( " question.topic_id, question.display_order" );
// Order alphabetical, if same order number
sql.append ( ",question.question_value" );
qds = new QueryDataSet( connection, sql.toString() );
qds.fetchRecords();
size = qds.size();
// re-get some values
Integer project_id = (Integer)
data.getUser().getTemp("project_id",
data.getParameters().getInteger("project_id", -1 ) );
Integer faq_id = (Integer) data.getUser().getTemp("faq_id",
data.getParameters().getInteger("faq_id", -1 ) );
OL question_ol = null;
for ( int i = 0; i<topic_ids.length; i++ )
{
topic_ol.addElement ( new LI().addElement (
new A().setHref(
new DynamicURI(data, "DisplayOneTopic", "SetAll")
.addPathInfo("project_id", project_id)
.addPathInfo("faq_id", faq_id)
.addPathInfo("topic_id", topic_ids[i])
.toString()
).addElement(topic_values[i])
) );
if ( !topic_released[i] )
topic_ol.addElement(new I().addElement(" (Not
released yet!)"));
// Localization missing!!!!!
question_ol = new OL();
for ( int j = 0; j<size; j++ )
{
int d_question_id =
qds.getRecord(j).getValue("question_id").asInt();
int d_topic_id =
qds.getRecord(j).getValue("topic_id").asInt();
String d_question_value =
qds.getRecord(j).getValue("question_value").asString();
int d_answer_count =
qds.getRecord(j).getValue("answer_count").asInt();
if ( d_topic_id == topic_ids[i] )
{
question_ol.addElement
( new LI().addElement
( new A().setHref
( new DynamicURI(data,
"DisplayQuestionAnswer", "SetAll")
.addPathInfo("project_id",
project_id)
.addPathInfo("faq_id", faq_id)
.addPathInfo("topic_id",
topic_ids[i])
.addPathInfo("question_id",
d_question_id)
.toString()
).addElement(d_question_value)
).addElement("
("+d_answer_count+")")
);
if (
!qds.getRecord(j).getValue("released").asString().equalsIgnoreCase("Y")
)
question_ol.addElement(new I().addElement("
(Not released yet!)"));
// Localization missing!!!!!
topic_ol.addElement ( question_ol );
}
}
}
ec.addElement (topic_ol);
}
finally
{
qds.close();
DBBroker.getInstance().releaseConnection(db);
}
}
}
--
Martin Buechler . DV-Entwickler Systems & Technology
Pixelpark AG . http://www.pixelpark.com
Rotherstr. 8 . 10245 Berlin . Germany
phone: + 49 30 5058 - 1891 fax: - 1600
-----BEGIN PGP PUBLIC KEY BLOCK-----
mQENAzk3eRUAAAEIALgzLrO+PxacblU1zvJ2Tl6oee81gXFT001MIOSD8e5DO6Qt
2of89tmtQvqo7QfzeqvQSCCmYDhUoPkDBKthvPpKLdfTIiRiWRPT3V0pShvUU9FV
6ceb4atBJu27wIGc/f54Eat0pbSSwuiN56jma2SPPoUenU+5E7zNl1398YQy2t0q
2pgWaB87qsaJPvrIUj0FR1B+W4NJr9hhk3ya8IjTjiP2YZDBkqCBOEnIfJfWsfO7
eXyje1zR67K5mdmKJGxTlGghk8AbA6uPOxwpXFr2I+EW/uTlMObCKhmVNZwGhhrP
KHPF1ydUMD2DbZAPx8rF5dkAN3YS18rhc0PGctMABRG0Lk1hcnRpbiBC/GNobGVy
IDxNYXJ0aW4uQnVlY2hsZXJAcElYRUxwYXJLLkNPTT6JARUDBRA5N3kV18rhc0PG
ctMBAaiSB/9AWQ8ONaHCK9nC2OsfwBnXidjB7epAJL4zFN6pqpf3WYbTjL8etve8
zW99p7mmHsSHc0p4kLxmfbMkdxjkLdxttZHkjRE+Wmd0dkPrnG/BbbI63YlTZ6fw
jGgwxu+sCYnnCHm2rnx7Xum+ZHd1ZUsgQjyDJ56sKqjyJx1s0hOc/rVyIngmhYwA
Ja+xVDaszovd/s/aqYah53TUi5cduZ8RXsOgtsGT/OLcbmCjgf5Y40t0JDFIATo3
I0n+Tni5ykUjUq6NtDj4k06VeLuuti15KWr2+4N+XG2jJWTZ7ngFI2IKuA4fXLYK
BZ2EkwQ32hPeF2d9KPyynJiZyrFr0J7p
=Vspv
-----END PGP PUBLIC KEY BLOCK-----
--
--------------------------------------------------------------
To subscribe: [EMAIL PROTECTED]
To unsubscribe: [EMAIL PROTECTED]
Archives and Other: <http://java.apache.org/main/mail.html>
Problems?: [EMAIL PROTECTED]
