Uploaded image for project: 'Cayenne'
  1. Cayenne
  2. CAY-2466

New internal API to build SQL

Agile BoardRank to TopRank to BottomAttach filesAttach ScreenshotBulk Copy AttachmentsBulk Move AttachmentsVotersWatch issueWatchersCreate sub-taskConvert to sub-taskLinkCloneLabelsUpdate Comment AuthorReplace String in CommentUpdate Comment VisibilityDelete Comments
    XMLWordPrintableJSON

Details

    Description

      As a part of CAY-2465 we need a better way to construct a SQL strings, than a simple in-place string concatenation.

      It can build a SQL tree that can be modified later for specific DB dialect and easily processed into a final SQL.

      At first it will be used as part of new SelectTranslator but other usages (including some public API) should be kept in mind.

      In my prototype I came to something like this:

      // import static org.apache.cayenne.access.sqlbuilder.SQLBuilder.*;
      
      Node slqNode = select(table("a").column("ARTIST_ID").as("a_id"),
              count(table("p").column("PAINTING_TITLE")).as("p_count"))
              .distinct()
              .from(table("ARTIST").as("a"))
              .from(leftJoin(table("PAINTING").as("p"))
                              .on(table("a").column("ARTIST_ID")
                                      .eq(table("p").column("ARTIST_ID"))
                                      .and(table("p").column("ESTIMATED_PRICE").gt(value(10)))))
              .where(
                      table("a").column("ARTIST_NAME")
                              .eq(value("Picasso"))
                              .and(exists(select(all())
                                              .from(table("GALLERY").as("g"))
                                     .where(table("g").column("GALLERY_ID").eq(table("p").column("GALLERY_ID")))))
                              .and(value(1).eq(value(1)))
                              .or(value(false)))
              .groupBy(table("a").column("ARTIST_ID"))
              .having(not(count(table("p").column("PAINTING_TITLE")).gt(value(3))))
              .orderBy(column("p_count").desc())
              .build();
      
      SQLGenerationVisitor visitor = new SQLGenerationVisitor();
      slqNode.visit(visitor);
      String sql = visitor.getSQLString();
      

      Attachments

        Issue Links

        Activity

          This comment will be Viewable by All Users Viewable by All Users
          Cancel

          People

            ntimofeev Nikita Timofeev
            ntimofeev Nikita Timofeev
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Slack

                Issue deployment