thanks !! I'll give it a try. Do you have a patch for the
jvye2 source ?
mike
On Tue, Oct 10, 2000 at 09:05:51PM +0200, Martin B�chler wrote:
> 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]
--
--------------------------------------------------------------
To subscribe: [EMAIL PROTECTED]
To unsubscribe: [EMAIL PROTECTED]
Archives and Other: <http://java.apache.org/main/mail.html>
Problems?: [EMAIL PROTECTED]
