Open GoogleCodeExporter opened 8 years ago
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
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
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
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
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
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
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
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
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
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
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
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
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
ok, fair enough, thanks
Original comment by ipolevoy@gmail.com
on 14 Mar 2012 at 3:24
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
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
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
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
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
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
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
Original comment by ipolevoy@gmail.com
on 11 Jul 2012 at 2:43
since no replies, I consider this fixed. closing
Original comment by i...@polevoy.org
on 25 Jul 2012 at 4:16
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:
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
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
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
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
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
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
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
Original issue reported on code.google.com by
RuralHun...@gmail.com
on 14 Mar 2012 at 3:38