The use case is to allow 3rd parties access to the same database, but to isolate the records that belong to each party automatically in each table (without their knowledge). When they create a table, extra fields are added that identifies the party and a few other things. When they execute an INSERT query, these fields need to be added to whatever query they supplied (otherwise the query will fail). When they execute a SELECT query, these fields need to be added to the query (so the party can only see the data that belongs to them) but not be a part of the ResultSet so the party doesnt know of the fields existance. Its the same for UPDATE/DELETE (but of course extra checks will be in place to try and stop them gaining access to another parties data).
Why dont we use separate databases? *Some* parties can see other parties data for reporting purposes (for example, in the franchisor->franchisee model). The other reason is that our platform (which is a website hosting platform that hosts multiple sites in the same system) also has applications attached to each website (things like CRM etc). In order to not have a separate installation of each application per website, it was decided that the data stored would have metadata attached (depending on which website the application was invoked from). However, the powers that be want our platform opened up to 3rd party developers, who obviously cannot be relied on to supply the necessary (or correct) metadata in order to ensure that they cannot view another websites data (they could easily create an application that simply shows the data in every table). So it was decided that they can ignore it completely and the system takes care of adding the necessary fields to the query so that the data can be isolated to a single website. I have since learned I probably cannot use the H2 query parser - the Insert object only contains setters (no getters). I used reflection to gain access to its private members and added extra columns/expressions, but I was unable to get its toString() to include these modifications so I began to look elsewhere On Sat, Jun 26, 2010 at 4:08 AM, Thomas Mueller <thomas.tom.muel...@gmail.com> wrote: > Hi, > >> I have a need to take a user-supplied SQL query and perform a few >> modifications to it before allowing it to execute against the >> database. Mostly, this consists of injecting in extra field names and >> conditions. I need this to be transparent to the user because the user >> cannot be relied on to include this information. > > Could you describe the use case? I'm just curious. > >> Is it possible to use H2 to take a SQL string, convert it into a >> datastructure, then allow me to manipulate that datastructure (to add >> extra tables/fields/conditions/expressions), then convert it back into >> a SQL statement for executing against the database? > > It's possible as Kerry has described, however this is not an official > public API, so it may change at any time. > > Regards, > Thomas > > -- > You received this message because you are subscribed to the Google Groups "H2 > Database" group. > To post to this group, send email to h2-datab...@googlegroups.com. > To unsubscribe from this group, send email to > h2-database+unsubscr...@googlegroups.com. > For more options, visit this group at > http://groups.google.com/group/h2-database?hl=en. > > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to h2-datab...@googlegroups.com. To unsubscribe from this group, send email to h2-database+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
