Adding a SQL Filter

There is a mechanism for achieving this by allowing a custom row filter for any IJC Entity to be defined as a SQL fragment that will be appended to all SELECT statements (e.g. query results). To do this:

1. Open the Schema Editor and find the Entity that you want to restrict access to.

2. Go to the 'Extra Attributes' tab and click on the 'Add' button. The Extra attributes editor will open.

3. Select the 'SQL Filter' property, enter the SQL filter you need and click the 'Add button'.

4. Click the 'Apply' button in the Entity editor to apply the changes.

5. Disconnect from the schema and reconnect so that you can test the results.

The process for editing an existing filter is almost identical. Just select the 'SQL Filter' property in the 'Extra Attributes' tab and click 'Edit'. Similarly to remove a filter click 'Remove'.

Simple example

So how do you know what filter to enter? Of course the exact filter will vary in each case, but some guiding principles and an example will help you work out what is needed. As an example we will start with a very simple filter that restricts the compounds you can see in the Pubchem Demo sample data (included in the sample project in IJC) to those with a molecular weight of less than 500. Of course your filter will be more complex and we cover that below, but this simple example lets you experiment with SQL Filters using the sample data in IJC to get experience.

Data is retrieved from the database using a SQL SELECT statement that might also include a query (e.g. Mol Formula starts with 'C12'). This would look something like this as a SQL filter (structure queries don't operate quite like this, but the principle is the same and the SQL filter is also applied to structure searches for JChemBase and JChem cartridge tables):

When you specify a SQL Filter you should think of it being applied like this:

where

is replaced by your specified SQL filter. In our example we want rows where the molweight is less than 500 so our SQL filter (what you enter into the Extra attributes editor) need to look like this:

so when substituted into the full SELECT statement will look like this:

We suggest you try this with the PubChem demo data to get started on using this feature. If you do so, when you open the Pubchem grid view you will only see the 863 compounds whose mol weight is less than 500.

Specifying the IJC username

In a real world example you will almost certainly want to be able to specify the IJC username of the current user. You do this by using the _IJC_USERNAME_ (that's two underscores at each end) value in your SQL filter and the current user's username will be substituted into the SQL Filter before it is added to the SELECT statement. The username is the username the user logs in with and values will be found in the USERNAME column of the IJC_USER table.

So let's extend our example and assume that the username is also stored in the data table (e.g. lets assume that the same pubchem_demo table now has an extra column with the username of the user who entered the compound. The SQL Filter would now look like this:

note the quotes around the

Including data from other tables

Of course in a real scenario you will not just be using data from a single table, and your select statement will need to use data from other tables. For instance, lets assume that your compounds table had a column (proj) that defined the project id that the compound belonged to and you had a project table that defined your projects and a user_project table that asociated each user to one or more projects. In this case your SQL Filter would look something like this:

Note the check for proj being null (we assume that not all compounds are assigned to projects and we want all users to see those). Also note the use of a sub-select as part of filter. Your 'real world' scenario will of course be different, and may well be more complex, but the same principles apply. You are recommended to test this using a SQL editor to establish the correct filter before applying it to IJC. Its up to you to work out what is the right filter for your particular needs.

When applying row level filters to more than one table in a schema and in particular if those tables are linked by a relationship then one must be aware to use fully qualified SQL statements, else it is possible to encounter an error. Below are examples of a SQL filter statements for a parent and child table found in the demo data of IJC:

We welcome feedback on this feature. Please provide feedback via the Instant JChem forum .