Same table name in different schemas with PostgreSQL

[GoogleCodeExporter]

I just started to test activejdbc and I guess I found a bug with postgresql. 
I defined a model like this:
@Table("article")
@IdName("aid")
public class Article extends Model
{

}

The problem is, I have 2 tables named article in different schemas. One is in 
default schema('public') and another is in schema 'audit'. When I use 
@Table("article"), I can see the model merged the columns from both article 
tables. The find/get method will succeed but the save method will fail 
reporting some columns from audit.article don't exist on table 'article'.
If I use @Table("public.article"), activejdbc just simply reports "Failed to 
retrieve metadata for table: 'public.article'" with all methods.

Original issue reported on code.google.com by RuralHun...@gmail.com on 14 Mar 2012 at 3:38

[GoogleCodeExporter]

interesting defect. We had similar problems with Oracle which were resolved by 
access restrictions to unwanted table. I'd speculate this is not a defect, but 
rather incorrect permission/access on the DB side. Can you please conduct a 
simple experiment:

Base.findAll("select * from article");

and see if this merges results from two table. Use the same user and JDBC 
connection, please

Original comment by ipolevoy@gmail.com on 14 Mar 2012 at 4:00

[GoogleCodeExporter]

There are too many rows in public.article. I did this instead:
System.out.println("We have totally "+Article.count()+" records");
        Article a = (Article)Article.findFirst("url_hash = ?", "bb89689fb6b4b55cd742e514cdd46635");
        System.out.println(a);
The result shows it accesses only the public.article. But I see the columns 
from audit.article are all set to null with 'System.out.println(a)'

Original comment by RuralHun...@gmail.com on 14 Mar 2012 at 4:21

[GoogleCodeExporter]

ok, but the idea was to not use a model, rather use Base. Base class does not 
scan metadata, if you have too many records, limit like this:

Base.findAll("select * from article limit 1");

At least you will know if this is a permission/role problem

Original comment by ipolevoy@gmail.com on 14 Mar 2012 at 4:25

[GoogleCodeExporter]

well, to be more clear, I did this again:
System.out.println("We have totally "+Article.count()+" records");
        List l = Article.find("url_hash = ?", "bb89689fb6b4b55cd742e514cdd46635");
        for(Object a:l)            
            System.out.println(a);
There is one record with url_hash='bb89689fb6b4b55cd742e514cdd46635' in both 
tables. The result list only contains the record from public.article

Original comment by RuralHun...@gmail.com on 14 Mar 2012 at 4:26

[GoogleCodeExporter]

I don't understand the permission/role problem you are talking about. The id I 
used to connect to the db has select access on both tables.

Original comment by RuralHun...@gmail.com on 14 Mar 2012 at 4:27

[GoogleCodeExporter]

ok, I really am no expert on PostgreSQL, but we had the same problem in Oracle. 
This was solved by preventing a user from accessing a table in a different 
schema. 

As far as your comment #4, not sure I understand this, is this a desired result 
or unexpected. Is your primary schema "audit" or "public"?

If you only need to access data in audit schema, can you remove permission of 
this user from accessing public schema?

Original comment by ipolevoy@gmail.com on 14 Mar 2012 at 4:35

[GoogleCodeExporter]

Please, perform a test with Base class and see what columns it returns

Original comment by ipolevoy@gmail.com on 14 Mar 2012 at 4:36

[GoogleCodeExporter]

yes, for item 4, it's a desired result though it contains null columns from 
another unwanted table. 
I tested to remove the select auth on audit.article from the id. but the result 
is same.
in postgresql, if you don't prefix a table name with schema name, it will 
search in schema 'public' and the schema with same name of the user id. 
anyway, I can not remove the access from any of the 2 tables based on the 
business requirement. I guess the solution should be on 2 possible ways:
1. improve the meta data retrieving part to only get tables from 
default(public/userid) schemas.
2. support schema prefix in @Table annotation, @Table("public.article").
I think the 2nd way is better since sometime I do need to access some tables in 
other schema.

Original comment by RuralHun...@gmail.com on 14 Mar 2012 at 4:46

[GoogleCodeExporter]

This might require some time to investigate. A quick fix is to change name of a 
table, can you do this?

Original comment by ipolevoy@gmail.com on 14 Mar 2012 at 4:59

[GoogleCodeExporter]

there is also another way to improve the API:

@Schema("audit")

If we implement it, it will be with this annotation, bit for now see if you can 
simply rename a table 

BWT, related topic:
http://groups.google.com/group/activejdbc-group/browse_frm/thread/5a4aee530580f2
d/5448de98ea28524a?lnk=gst&q=JavaGirl#5448de98ea28524a

Original comment by ipolevoy@gmail.com on 14 Mar 2012 at 5:11

[GoogleCodeExporter]

Thanks for the hint but no, I can not change the table name since too many 
other things are related to it. but no need to hurry, I am just testing 
activejdbc out, not an urgent requirement to use it in my actual project.
one question is, why I can not use @Table("public.table"). PostgreSQL supports 
it in normal jdbc sqls: select * from public.article

Original comment by RuralHun...@gmail.com on 14 Mar 2012 at 5:18

[GoogleCodeExporter]

because this is a completely different call for metadata:
http://code.google.com/p/activejdbc/source/browse/trunk/activejdbc/src/main/java
/org/javalite/activejdbc/Registry.java#288

You cannot pass this value into this call. As you can see, the second argument 
is null, but I'm guessing that if I passed schema name there, all would work 
I can make a quick fix for you based on a system property, for example: 

-Dactivejdbc.schema=audit

If this property exists, I'd pass it there, if not, I'd pass null as usual.
I hate the fact that you only started using ActiveJDBC and found this defect :( 
and would like to help.

Original comment by ipolevoy@gmail.com on 14 Mar 2012 at 5:36

[GoogleCodeExporter]

no, don't worry. ActiveJDBC is a wonderful framework to me as I hate those XML 
based ORM framework. and don't hurry, I have time to wait for a more beautiful 
solution from you. :). meanwhile I will still test other things.

Original comment by RuralHun...@gmail.com on 14 Mar 2012 at 8:48

[GoogleCodeExporter]

ok, fair enough, thanks

Original comment by ipolevoy@gmail.com on 14 Mar 2012 at 3:24

[GoogleCodeExporter]

Same here! Need support for schemas on PostgreSQL as the project I work stores 
the tables in several schemas other than public for business needs.

Is there any chance some fix is landing into the API anytime soon? I have no 
experience with activejdbc, but I'd be available to help, depending on the time 
needed.

Original comment by andres.maneiro on 1 Jun 2012 at 2:40

[GoogleCodeExporter]

Andres, is there a way you can use a multi-DB capability for this as described 
here:
http://code.google.com/p/activejdbc/wiki/DatabaseConnectionManagement#Multiple_d
atabase_example

Original comment by chicagoe...@gmail.com on 1 Jun 2012 at 9:11

[GoogleCodeExporter]

Thanks for your comment. As far as I understand the example, I don't think I 
can use that. In our project, we use the schemas feature in PostgreSQL to 
organize tables and, AFAIK, activejdbc will search for tables in the "public" 
schema in PostgreSQL, making visible only those tables in it. I would need to 
access to other schemas and tables too.

Original comment by andres.maneiro on 11 Jun 2012 at 8:43

[GoogleCodeExporter]

OK,  this issue should not be difficult to fix, but I will need to rely on your 
help to do so because I'm not that familiar with PostgreSQL administration.

I'm pretty sure that the changes need to be done to parameters on this line:
http://code.google.com/p/activejdbc/source/browse/trunk/activejdbc/src/main/java
/org/javalite/activejdbc/Registry.java#283

What I need from you is to tell me which of these parameters is a name of the 
schema in PostgreSQL. You can build a primitive example based on just JDBC and 
experiment with parameters to this method. Once you get the result, please post 
it here, and I will see how to introduce  a schema name here so as not to break 
all existing projects out there. JDBC documentation is somewhat scant on this, 
I just need a definitive answer

thanks
igor

Original comment by ipolevoy@gmail.com on 11 Jun 2012 at 6:47

[GoogleCodeExporter]

In "con.getMetaData().getColumns(null, null, table.toLowerCase(), null);" the 
parameter related to schema is the second, so getColumns(catalog, schema, 
table, column).

Besides, I've put together an example using PostgreSQL to show where it fails 
(it uses the example/activejdbc-ant as base):
https://github.com/amaneiro/activejdbc-postgresql

In my tests, it fails when:

- there are 2 tables with the same tablename, although in different schemas 
(activejdbc seems to mix columns from both tables). 

- can't work with tables in a schema different than public.

Thanks,

Original comment by andres.maneiro on 13 Jun 2012 at 7:57

[GoogleCodeExporter]

Andres, this is great, I will dig into this, and will post results here when I 
get something tangible 

Original comment by ipolevoy@gmail.com on 13 Jun 2012 at 5:59

[GoogleCodeExporter]

Andres, sorry for delay. I committed the fix into the 1.4.2-SNAPSHOT.
The idea is that the schema name is tied to the database name as described here:
http://code.google.com/p/activejdbc/wiki/DatabaseConnectionManagement#Multiple_d
atabase_example

Basically, if you use a default database (you do not use DbName() annotation), 
simply add this system property:
-Dactivejdbc.default.schema=myschema

where "default" is a name of a database. 

In case you do use multiple connections to different databases and you use 
DbName annotation, replace "default" to your  DB name. For example:

@DbName("university")
public class Student extends Model{}

then you can add a property like this:
-Dactivejdbc.university.schema=myschema

This approach does not introduce new APIs and does not break existing projects, 

Please, try this, let me know if it works. 

thanks
igor

Original comment by i...@polevoy.org on 11 Jul 2012 at 2:43

[GoogleCodeExporter]

Original comment by ipolevoy@gmail.com on 11 Jul 2012 at 2:43

• Changed state: Started

[GoogleCodeExporter]

since no replies, I consider this fixed. closing

Original comment by i...@polevoy.org on 25 Jul 2012 at 4:16

• Changed state: Fixed

[GoogleCodeExporter]

Hello Igor,

My name is Hans Royer and I am a software engineer just assigned to a Java 
project for the Navy.  Part of my task is to refactor both the architecture and 
implementation to improve performance and stability, as well as make 
troubleshooting and future modifications to the system easier.  One of the 
first areas that I am looking at is the data access components, which are 
currently implemented using Spring/Hibernate.  While it may be possible to 
design a robust and extensible application with these frameworks, I have yet to 
see an example.  Having had previous experience with Rails systems, I 
immediately started looking around a Java implementation of Rail's ActiveRecord 
and ran across ActiveJDBC.

I must say that I very impressed with both the quality of the design, code, and 
documentation.  I was able to get a quick example working in less than 30 
minutes against a simple PostgreSQL database with the public schema and tables 
that followed convention. 

The project  will have to retrieve and insert data into more than one 
PostgreSQL database with already defined database names, schemas, and tables 
and I am unable to modify these attributes.  I started out trying to just 
retrieve data from one of the databases and one of the schemas.  I seem to have 
an issue that is similar to the one that Andres Maneiro reported.  I am also 
sending an email in addition to posting a comment on the Issue, since I am 
unsure if you would get notified of comments on closed issues

Following the instructions you provided to Andres, I used DB instead of Base, 
set the schema ("crdb") as a property, and annotated the Model with the DbName 
("gis") and Table ("jmeasset"), since the existing table names do not follow 
the ActiveRecord convention.  When running a modified version of the standalone 
example, I received the following error:

---
Exception in thread "main" org.javalite.activejdbc.DBException: 
org.postgresql.util.PSQLException: ERROR: relation "jmeasset" does not exist
  Position: 16, Query: SELECT  * FROM jmeasset WHERE  assetid = 1
---

Since I was only hitting one database initially, I tried using Base instead of 
DB and received the same error.

If I changed the Table annotation to "crdb.jmeasset", I receive the following 
error:

---
167 [main] WARN org.javalite.activejdbc.Registry - Failed to retrieve metadata 
for table: 'crdb.jmeasset'. Are you sure this table exists? For some databases 
table names are case sensitive.
Exception in thread "main" org.javalite.activejdbc.DBException: 
org.javalite.activejdbc.InitException: Failed to find table: crdb.jmeasset
---

After his seems to indicate that the the schema name is being used to retrieve 
table metadata in the first instance, but not being set for the sql query 
string in DB.find() and elsewhere.  I also tried DB.count("crdb.jmeasset") and 
it returned the correct row count.

I have attached the relevant files, including full stack traces of the two 
errors.  The two Java classes are just modifications to your standalone example.

I have checked out the source and will take a look at towards the end of next 
week to see if I can locate the problem.  In the meantime, I just wanted to 
make you aware of the issue.

Thanks again for all of your work on this project.

v/r
Hans

Original comment by peng...@gmail.com on 13 Sep 2012 at 6:15

Attachments:

• Asset.java
• Driver.java
• first_error.txt
• second_error.txt

[GoogleCodeExporter]

Hans, I got both of your messages:)

First, thanks for a good word on ActiveJDBC - we have done a ton of work to 
make it better. I also share your feeling towards Spring/Hibernate. 
If you like ActiveJDBC, you will also like ActiveWeb: 
http://code.google.com/p/activeweb/

Now, to the point. ActiveJDBC has a two layer architecture, where low level is 
implemented by DB and Base. Neither of these classes pay attention to any 
system properties. They do not try to assume anything and do not introduce any 
magic. They execute code against a JDBC connection verbatim, and this is why 
this property did not affect them. 
The models are a second layer - they generate SQL, obey system properties, load 
different SQL dialects for different databases, etc. 
Higher level - models - are using lower level under the  hood.

In any case, here is the combination that should work:

@DbName("gis")
@Table("jmeasset")
public class Asset extends Model {}
---

java -Dactivejdbc.gis.schema=crdb #... the rest of command line

----

public static void main(String[] args) {

        DB db = new DB("gis");
        db.open("org.postgresql.Driver", "jdbc:postgresql://localhost/gis", "****", "****");
        List<Asset> assets = Asset.where("assetid = 1");
        System.out.println("Number of assets: " + assets.size());       
        db.close();
}

From your comment, it seems that  you tried this and it does not work, but 
please check again, should be working.
If still does not work, lets fix this together. ActiveJDBC pulls metadata from 
the database, and in some edge cases, it cannot do this if you do not give it 
enough information, permissions, etc.
Please, see code: 
http://code.google.com/p/activejdbc/source/browse/trunk/activejdbc/src/main/java
/org/javalite/activejdbc/Registry.java#273
This is a place where it does it. Please, load sources, and run in debug mode, 
and see what is missing. 
Would be even better if you can figure out why this is not working and submit a 
patch to fix it.
Post results here, and we will integrate it into the framework

I set this issue into "Started" status again.

good luck,
igor

Original comment by i...@polevoy.org on 13 Sep 2012 at 10:39

• Changed state: Started

[GoogleCodeExporter]

Thanks for the update/info Igor.  I will take a look at this later in the week 
and get back to you.

Original comment by peng...@gmail.com on 18 Sep 2012 at 10:07

[GoogleCodeExporter]

Hi Igor. We are having the same issue with Oracle. 
You should change it to read the schema name from a Registry property instead 
of the System property. We need to set a different schema name for different 
releases in the same application server. 
Thanks, Ezio

Original comment by ezio.fer...@gmail.com on 19 Sep 2012 at 5:00

[GoogleCodeExporter]

Ezio, what Registry property? Can you please be more specific? I think the 
solution I provided fits cases when you access multiple databases such  that 
you can provide different schema names for each individual database. I'm not 
sure how this is not solving the problem. 

thank you,
igor

Original comment by i...@polevoy.org on 19 Sep 2012 at 5:08

[GoogleCodeExporter]

Igor, System properties are shared by all applications in the same virtual 
machine and are not tied to the class loader that started the application. 
If you can create a static method in Registry like setSchemaName or 
setProperties I could call it to set the schema name in each instance of my web 
application (we have two of it running on the same server and using two schemas 
on the same database instance). 
Thanks.

Original comment by ezio.fer...@gmail.com on 19 Sep 2012 at 6:00

[GoogleCodeExporter]

Ezio, if you are running two applications in the same JVM, what prevents you  
from defining two different system properties, one for one application and one 
for another?
thanks

Original comment by i...@expresspigeon.com on 20 Sep 2012 at 5:52

[GoogleCodeExporter]

different schema have same table structure same table name  

i want if modify one schema table it automatically change another schema table 

it is possible

Original comment by LOGANATH...@ssomens.com on 28 Feb 2013 at 1:30