Changeset 3214

Timestamp:

07/31/13 11:37:47 (11 years ago)

Author:

olhsha

Message:

permission tables added to the test database, new bugs (connected to new integrity constraints) are fixed: before deleting an annotation delete all the rows with this annotation id in "annotations_principals_permissions" and "notebooks_annotations" tables

Location:

DASISH/t5.6/backend/annotator-backend/trunk/annotator-backend/src

Files:

• main/java/eu/dasish/annotation/backend/dao/impl/JdbcAnnotationDao.java (modified) (1 diff)
• main/java/eu/dasish/annotation/backend/dao/impl/JdbcResourceDao.java (modified) (1 diff)
• main/sql/DashishAnnotatorCreate.sql (modified) (1 diff)
• test/resources/test-data/InsertTestData.sql (modified) (2 diffs)

DASISH/t5.6/backend/annotator-backend/trunk/annotator-backend/src/main/java/eu/dasish/annotation/backend/dao/impl/JdbcAnnotationDao.java

@@ -219 +219 @@
      public int deleteAnnotation(Number annotationId) throws SQLException{
         String sqlAnnotation = "DELETE FROM " + annotationTableName + " where "+annotation_id + " = ?";
+        String sqlPermissions = "DELETE FROM " + permissionsTableName + " where "+annotation_id + " = ?";
+        String sqlNotebooks = "DELETE FROM " + notebooksAnnotationsTableName + " where "+annotation_id + " = ?";
+        int affectedNotebooks = getSimpleJdbcTemplate().update(sqlNotebooks, annotationId);
+        int affectedPermissions = getSimpleJdbcTemplate().update(sqlPermissions, annotationId);
         int affectedAnnotations = getSimpleJdbcTemplate().update(sqlAnnotation, annotationId);
         if (affectedAnnotations>1) {

DASISH/t5.6/backend/annotator-backend/trunk/annotator-backend/src/main/java/eu/dasish/annotation/backend/dao/impl/JdbcResourceDao.java

@@ -38 +38 @@
     final static protected String principalTableName = "principal";    
     final static protected String notebooksAnnotationsTableName = "notebooks_annotations";
-    
+    final static protected String permissionsTableName = "annotations_principals_permissions";
     
     // base string constants: field Names

DASISH/t5.6/backend/annotator-backend/trunk/annotator-backend/src/main/sql/DashishAnnotatorCreate.sql

@@ -219 +219 @@
 --     </xs:complexType>
 
-CREATE TABLE annotations_users_permissions (
+CREATE TABLE annotations_principals_permissions (
 annotation_id integer REFERENCES annotation(annotation_id),
 principal_id integer REFERENCES principal(principal_id),
 permission_  text,
 unique(annotation_id, principal_id),
-CHECK(permission_ = 'reader' OR permission_='writer' OR permission_='owner')
-)
+CHECK(permission_ = 'reader' OR permission_='writer' OR permission_='owner'),
+-- soundness: owner in this table must be the same as the owner in the annotation table ---
+-- check for any pair (annotation, principal) there must be exactly one row in this table -- ???
+);
 
 ---------------------------------------------------------------------------------------------

DASISH/t5.6/backend/annotator-backend/trunk/annotator-backend/src/test/resources/test-data/InsertTestData.sql

@@ -50 +50 @@
 INSERT INTO notebooks_annotations (notebook_id,annotation_id) VALUES (3,3);
 INSERT INTO notebooks_annotations (notebook_id,annotation_id) VALUES (4,4);
+INSERT INTO notebooks_annotations (notebook_id,annotation_id) VALUES (5,5);
 
 
@@ -136 +137 @@
 INSERT INTO versions_cached_representations (version_id, cached_representation_id) VALUES (3, 2);
 INSERT INTO versions_cached_representations (version_id, cached_representation_id) VALUES (4, 4);
+
+
+---- PERMISSIONS --------------------------------------------------------------------------------------------
+
+INSERT INTO annotations_principals_permissions (annotation_id, principal_id, permission_) VALUES (2, 3, 'owner');
+INSERT INTO annotations_principals_permissions (annotation_id, principal_id, permission_) VALUES (2, 4, 'writer');
+INSERT INTO annotations_principals_permissions (annotation_id, principal_id, permission_) VALUES (2, 5, 'reader');
+
+INSERT INTO annotations_principals_permissions (annotation_id, principal_id, permission_) VALUES (3, 4, 'owner');
+INSERT INTO annotations_principals_permissions (annotation_id, principal_id, permission_) VALUES (3, 3, 'reader');
+INSERT INTO annotations_principals_permissions (annotation_id, principal_id, permission_) VALUES (3, 5, 'writer');
+
+INSERT INTO annotations_principals_permissions (annotation_id, principal_id, permission_) VALUES (4, 5, 'owner');
+INSERT INTO annotations_principals_permissions (annotation_id, principal_id, permission_) VALUES (4, 3, 'reader');
+INSERT INTO annotations_principals_permissions (annotation_id, principal_id, permission_) VALUES (4, 4, 'reader');
+
+INSERT INTO annotations_principals_permissions (annotation_id, principal_id, permission_) VALUES (5, 3, 'owner');
+INSERT INTO annotations_principals_permissions (annotation_id, principal_id, permission_) VALUES (5, 4, 'writer');
+INSERT INTO annotations_principals_permissions (annotation_id, principal_id, permission_) VALUES (5, 5, 'writer');