• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Paul Clapham
  • Ron McLeod
Sheriffs:
  • Jeanne Boyarsky
  • Liutauras Vilda
Saloon Keepers:
  • Tim Holloway
  • Carey Brown
  • Roland Mueller
  • Piet Souris
Bartenders:

How to not reinvent the wheel - technologies/frameworks everywhere

 
Ranch Hand
Posts: 50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hello out there!
That's a rather generic issue so I hope I've chosen the correct forum in the end.

I'm about to write a DB-Query application.
The access is READ-ONLY and against already-existing schemas.

Let me explain the odds & ends:
A Client (provides the GUI for the queries) connects to a server/retrieves
a server-instance (JNDI/RMI) that provides a list of datapools.
A dataPool is configured using a plugin mechanism, where a plugin consists
of a xml-configuration, describing the db-host, the plugin/dataPool starter-class
and things like what will be presented to the user in the gui.
The "what the user will see" part consists of:
  • a query editor to write and execute plain, old SQL
  • Available pre-defined queries, with coresponding parameters
  • A list of Entities and their attributes, from which the user can drag/drop a sample table to define how the result list will look like and what the query constraints would be (this comes down to a QBE implementation)
    You may have seen this QBE approach in Paradox, Star/Openoffice (yes, Access is similar)


  • The crucial part: The list of entities is either generated from the database metadata,
    JDBC provides nice functionality for this, or provided by classes, delivered with the plugin, to
    provide the user with functional names/relations rather then the techy-named tables/columns.
    (the functional-to-technical translation may be done via config files or read from an existing repository
    like most CRMs bring with them).

    The helper classes from the plugin will know how to generate the SQL for a given Entity and their related Entities - if the user chose such things.

    Now the "reinvent the wheel" part. There are a lot of frameworks out there, in particular Hibernate that allow for abstract query (QBE) generation. I have no experience with this but from what I've read across the web it would be hard to form a QBE for:
    the relations: a(id), b(id), ab(aID, bID) and to retrieve "the 'a's that are linked to every 'b'" which would resolve to a query like

    or
    or (fancy stuff with HAVING)
    or ...
    You get the point.

    Now we need a question:
    Do you know of any QBE implementations that allow complex query generation
    (I've read that hibernate has an implementation of QBE and E-QBE but never worked with them, tbh, I've little experience with hibernate) ?

    Can you suggest frameworks for what I want to achieve?
    Is my intention clear at all, if not, what other information do you need?
    Is there something I should get straight in my thoughts about the problem ?

    Looking forward to your comments!

    Regards.
     
    Sheriff
    Posts: 67756
    173
    Mac Mac OS X IntelliJ IDE jQuery TypeScript Java iOS
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    "srm wasHere", please check your private messages for an important administrative matter.
     
    Marshal
    Posts: 80874
    506
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    Damn! You've beaten me to the warning. Is your middle name Rob by any chance?
     
    Stephan Mueller
    Ranch Hand
    Posts: 50
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    Wow.
    Not really the comment I'd wished for but first things first, right.
    Fixed and ready to go.
     
    Campbell Ritchie
    Marshal
    Posts: 80874
    506
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    Moving to our databases forum, where you will be more likely to get a useful answer.
     
    Bartender
    Posts: 2662
    19
    Netbeans IDE C++ Linux
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    I don't know any library that has these capabilities.

    Oracle has a product called Discoverer, that performs most of the capabilities you are describing (minus data pool / jndi)
    It is a business layer above database tables/views.
    The layer exists of objects with attributes.The links between these objects are defined in the layer.

    When creating a report(this is done by selecting objects and attributes, and setting conditions), Discoverer cunningly builds an sql statement against the underlying database tables / views.

    Not trivial! Difficult queries! Lots of grouping on lots of levels! Beware!
     
    Stephan Mueller
    Ranch Hand
    Posts: 50
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    Jan, thank you for the pointer to Oracle's tool. I'll see if I can have a look at this one and see, how they tried to solve these problems.
    On the one hand, it's good to hear that there are no state of the art solutions to this, so I'm probably not reinventing the wheel (as a sidenote, this application will be part of my diploma thesis), on the other hand, it would have been great to look into other solutions to get a feeling on what is to be done.
    I guess I have to dig into hibernates current qbe implementation, also a look into openoffice might be worth a try (even if they c++).

    Discoverer cunningly builds an sql statement


    Do you have any internas on this?

    Not trivial! Difficult queries! Lots of grouping on lots of levels! Beware!


    Yes, that's what I'm looking forward to (or what I'm afraid of).
    Another area of concern is how to provide a user interface, that allows the user to express his ideas in a way, it get's correctly interpreted and translated. The old QBE interfaces look rather formal und archaic.
    Let's see with what I can come up with.

     
    Jan Cumps
    Bartender
    Posts: 2662
    19
    Netbeans IDE C++ Linux
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator

    Do you have any internas on this?

    No. But at one time in my career I was responsible for solving issues like: "My figure in my report is wrong and it worked last month and I got this report from Jim who has left the company". I would then ask Discoverer to show me the sql statement it generated for that report, and would track back from there to the source data.
    These queries could be rather complex if the report was using summaries, grouping, breaks, totals, ... at various levels.
    There are also some challenges with circular references (the tool does its very best to detect them and warn the user).
    Kudos to Oracle for getting it done.
    And kudos to you.
     
    Stephan Mueller
    Ranch Hand
    Posts: 50
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    Thank you for the discussion, Jan.
    A particular "thank you" for the fact, that I could recognize that
    I'm not developing a (full fledged) reporting tool - rather an application that allows
    to transform raw technical data into a functional view which can be used for further
    business intelligence (et al) operation, this transformation is done by letting the user define how
    the result should look like (as stated in the above posts).
    So I'm taking a lot of complexity out of

    These queries could be rather complex if the report was using summaries, grouping, breaks, totals, ... at various levels.


    Still, there's enough to do and think through. Let's see what I can accomplish, and if my employer is ok with it, the core parts of the application will be open-source
    and I'll post it here to discuss this with the community.

    Regards,
    Stephan
     
    Jan Cumps
    Bartender
    Posts: 2662
    19
    Netbeans IDE C++ Linux
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
     
    Consider Paul's rocket mass heater.
    reply
      Bookmark Topic Watch Topic
    • New Topic