Bug 4963 - CAS test sql Bug
: CAS test sql Bug
Status: ASSIGNED
: CAS/SAML utilities
CAS
: 4.0.2
: HP Linux
: P3 normal
: ---
Assigned To:
:
:
:
:
  Show dependency treegraph
 
Reported: 2007-01-16 01:39 by
Modified: 2007-09-12 14:50 (History)


Attachments


Note

You need to log in before you can comment on or make changes to this bug.


Description From 2007-01-16 01:39:20
I'm using postgresql8.1.4+GT4.0.3,
And I have installed CAS according to 
"GT 4.0: Security: Community Authorization Service" Admin Guide.
When I'm doing testing .I found there are so many SQL errors happend.
Let me give a example:
  Codes below is from
gt4.0.3-all-source-installer/source-trees/ws-cas/service/java/test/unit/src/org/globus/cas/impl/databaseAccess/DatabaseDelete.java
         ...................
            statement.addBatch("drop sequence service_action_seq ");
            statement.addBatch("drop sequence object_seq ");
            statement.addBatch("drop sequence policy_seq ");
            statement.addBatch("create sequence service_action_seq");
            statement.addBatch("create sequence object_seq");    
            statement.addBatch("create sequence policy_seq");    
         ..........................................................
where in the
$GLOBUS_LOCATION/etc/globus_cas_service/casDbSchema/cas_pgsql_database_schema.sql
We can see that :
create sequence service_action_seq;
create table service_type_action
(
        service_action_id int default nextval('service_action_seq'),
        service_type_name text,
        action_name text not null,
        constraint sa_primary primary key(service_action_id),
        constraint sa_unique unique(service_type_name, action_name),
        constraint sa_service_reference foreign key(service_type_name)
references service_type
);

It will be for sure to throw an error when executing statement.addBatch("drop
sequence service_action_seq ");
Because of this dependency:  service_action_id int default
nextval('service_action_seq');
And this error is so clear.


I have change the code to be the following in order to pass the test:
            statement.addBatch("ALTER SEQUENCE service_action_seq RESTART WITH
1 ");
            statement.addBatch("ALTER SEQUENCE object_seq RESTART WITH 1");
            statement.addBatch("ALTER SEQUENCE policy_seq RESTART WITH 1");


And there are also other sql erros:
When doing the second test 
casadmin$ ant -f share/globus_cas_unit_test/cas-test-build.xml user1TestService
I got this error:
ERROR:  insert or update on table "object_table" violates foreign key
constraint "objgp_ns_reference"
DETAIL:  Key (namespace_nickname)=(nickname) is not present in table
"namespace_table".
It is clearly that nickname does not exists in namespace_table.
Then I searched the table namespace_table , 'nickname' really does no exists,so
the foreign key constraint failed.

Because we use so many foreign keys when creating tables, it seems that the
program is not so correctly updated.


These problems happened a lot.I can not go on my work.Because I have to change
the sourcecode for so many programs.I really hope there will be an fix.

Thanks.
------- Comment #1 From 2007-01-18 11:17:56 -------
I am unable to replicate both the issues reported here.

(1) I don't see any issue with the delete statements there. To verify I ran the
code in the DatabaseDelete class against a database created on version 7.4.7
PostGres and saw no issues. Please send in a stack trace of the issue you see
with the DatabaseDelete class. 

(2) The service tests also worked ok and I did not see any failures. The tests
also exercise expected failure scenarios, so you might see logging for errors,
but that is expected. At the end of the run did you see a "BUILD FAILED" ? If
so please upload the test reports that are generated. They should be in
"cas-test-reports" directory
------- Comment #2 From 2007-01-20 21:55:42 -------
Maybe it has something with the Postgresql Version.
Because i'm using the 8.1x.
And of course I see build failed when doing test1 database test.

In order to confirm this ,I have test the sql in postgresql comment line.
>create sequence service_action_seq;
created.
>create table service_type_action
(
        service_action_id int default nextval('service_action_seq'),
        service_type_name text,
        action_name text not null,
        constraint sa_primary primary key(service_action_id),
        constraint sa_unique unique(service_type_name, action_name),
        constraint sa_service_reference foreign key(service_type_name)
references service_type
);
created
>drop sequence service_action_seq
Can't drop sequence service_action_seq because of depedency service_type_action
(service_action_id ).


So if you can pass this test in postgresql7.x,Maybe it is because of the
version 8.x is not supported well.
------- Comment #3 From 2007-01-22 09:25:42 -------
The DatabaseDelete.java does not do things in the order you have shown above,
it deletes things from service_type_action table before dropping the sequence.
Relevant code from that class:

statement.addBatch("delete from " + 
                               CasDBConstants.TABLE_SERVICEACTIONGP_ENTRY);
            statement.addBatch("delete from " + 
                               CasDBConstants.TABLE_SERVICEACTIONGP);
            statement.addBatch("delete from " + 
                               CasDBConstants.TABLE_SERVICETYPE_ACTION);
            statement.addBatch("delete from " 
                               + CasDBConstants.TABLE_SERVICETYPE);
            statement.addBatch("delete from " + CasDBConstants.TABLE_POLICY);
            // sequence
            statement.addBatch("drop sequence service_action_seq");
            statement.addBatch("drop sequence object_seq");
            statement.addBatch("drop sequence policy_seq");

Do you see issues even with that order of commands ?
------- Comment #4 From 2007-01-22 19:35:48 -------
Yes.

In order to find the reason of the error.
I have copy all of the statements and save in notepad.
Then I run these statements in Postgresql command line.
I got the following error:
casDatabase=> delete from object_table;
delete from object_group_entry;
delete from object_group_table;
delete from namespace_table;
delete from user_group_entry;
delete from user_group_table;
delete from user_table;
delete from trust_anchor_table;
delete from service_action_group_entry;
delete from service_action_group;
delete from service_type_action;
delete from service_type;
delete from policy_table;
drop sequence service_action_seq;
drop sequence object_seq;
DELETE 3
casDatabase=> delete from object_group_entry;
DELETE 1
casDatabase=> delete from object_group_table;
drop sequence policy_seq;
DELETE 1
casDatabase=> delete from namespace_table;
DELETE 3
casDatabase=> delete from user_group_entry;
DELETE 7
casDatabase=> delete from user_group_table;
create sequence service_action_seq;
DELETE 5
casDatabase=> delete from user_table;
DELETE 3
casDatabase=> delete from trust_anchor_table;
DELETE 2
casDatabase=> delete from service_action_group_entry;
create sequence object_seq;
DELETE 0
casDatabase=> delete from service_action_group;
DELETE 0
casDatabase=> delete from service_type_action;
DELETE 24
casDatabase=> delete from service_type;
DELETE 2
casDatabase=> delete from policy_table;
create sequence policy_seq;DELETE 15
casDatabase=> drop sequence service_action_seq;
NOTICE:  default for table service_type_action column service_action_id depends
on sequence service_action_seq
ERROR:  cannot drop sequence service_action_seq because other objects depend on
it
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
casDatabase=> drop sequence object_seq;
NOTICE:  default for table object_table column object_id depends on sequence
object_seq
ERROR:  cannot drop sequence object_seq because other objects depend on it
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
casDatabase=> drop sequence policy_seq;
NOTICE:  default for table policy_table column policy_id depends on sequence
policy_seq
ERROR:  cannot drop sequence policy_seq because other objects depend on it
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
casDatabase=> create sequence service_action_seq;
ERROR:  relation "service_action_seq" already exists
casDatabase=> create sequence object_seq;
ERROR:  relation "object_seq" already exists
casDatabase=> create sequence policy_seq;




> The DatabaseDelete.java does not do things in the order you have shown above,
> it deletes things from service_type_action table before dropping the sequence.
> Relevant code from that class:
> statement.addBatch("delete from " + 
>                                CasDBConstants.TABLE_SERVICEACTIONGP_ENTRY);
>             statement.addBatch("delete from " + 
>                                CasDBConstants.TABLE_SERVICEACTIONGP);
>             statement.addBatch("delete from " + 
>                                CasDBConstants.TABLE_SERVICETYPE_ACTION);
>             statement.addBatch("delete from " 
>                                + CasDBConstants.TABLE_SERVICETYPE);
>             statement.addBatch("delete from " + CasDBConstants.TABLE_POLICY);
>             // sequence
>             statement.addBatch("drop sequence service_action_seq");
>             statement.addBatch("drop sequence object_seq");
>             statement.addBatch("drop sequence policy_seq");
> Do you see issues even with that order of commands ?
------- Comment #5 From 2007-03-23 15:33:41 -------
*** Bug 4964 has been marked as a duplicate of this bug. ***
------- Comment #6 From 2007-03-23 15:34:03 -------
*** Bug 4965 has been marked as a duplicate of this bug. ***