1   /*
2    *  PostgresDataStore.java
3    *
4    *  Copyright (c) 1998-2005, The University of Sheffield.
5    *
6    *  This file is part of GATE (see http://gate.ac.uk/), and is free
7    *  software, licenced under the GNU Library General Public License,
8    *  Version 2, June 1991 (in the distribution as file licence.html,
9    *  and also available at http://gate.ac.uk/gate/licence.html).
10   *
11   *  Marin Dimitrov, 18/Mar/2001
12   *
13   *  $Id: PostgresDataStore.java,v 1.37 2005/01/11 13:51:36 ian Exp $
14   */
15  
16  package gate.persist;
17  
18  import java.io.*;
19  import java.net.URL;
20  import java.sql.*;
21  import java.util.*;
22  
23  import junit.framework.Assert;
24  
25  import gate.*;
26  import gate.corpora.DatabaseCorpusImpl;
27  import gate.corpora.DatabaseDocumentImpl;
28  import gate.security.SecurityException;
29  import gate.security.SecurityInfo;
30  import gate.util.MethodNotImplementedException;
31  import gate.util.SimpleFeatureMapImpl;
32  
33  public class PostgresDataStore extends JDBCDataStore {
34  
35    /** Name of this resource */
36    private static final String DS_COMMENT = "GATE PostgreSQL datastore";
37  
38    /** the icon for this resource */
39    public static final String DS_ICON_NAME = "pgsql_ds.gif";
40  
41    /** Debug flag */
42    private static final boolean DEBUG = true;
43  
44    public PostgresDataStore() {
45  
46      super();
47      this.datastoreComment = DS_COMMENT;
48      this.iconName = DS_ICON_NAME;
49    }
50  
51  
52    public void setSecurityInfo(LanguageResource parm1, SecurityInfo parm2) throws gate.persist.PersistenceException, gate.security.SecurityException {
53      /**@todo: implement this gate.persist.JDBCDataStore abstract method*/
54      throw new MethodNotImplementedException();
55    }
56  
57    public List findLrIds(List constraints, String lrType) throws gate.persist.PersistenceException {
58      /**@todo: implement this gate.persist.JDBCDataStore abstract method*/
59      throw new MethodNotImplementedException();
60    }
61  
62  /*  public LanguageResource getLr(String lrClassName, Object lrPersistenceId) throws gate.security.SecurityException, gate.persist.PersistenceException {
63      throw new MethodNotImplementedException();
64    }
65  */
66  
67  /*  public void delete(String lrClassName, Object lrId) throws gate.security.SecurityException, gate.persist.PersistenceException {
68  
69      throw new MethodNotImplementedException();
70    }
71  */
72  
73    public List findLrIds(List constraints) throws gate.persist.PersistenceException {
74      /**@todo: implement this gate.persist.JDBCDataStore abstract method*/
75      throw new MethodNotImplementedException();
76    }
77  
78  
79    /**
80     * Releases the exlusive lock on a resource from the persistent store.
81     */
82    public void unlockLr(LanguageResource lr)
83    throws PersistenceException,SecurityException {
84  
85      //0. preconditions
86      Assert.assertNotNull(lr);
87      Assert.assertTrue(lr instanceof DatabaseDocumentImpl ||
88                        lr instanceof DatabaseCorpusImpl);
89      Assert.assertNotNull(lr.getLRPersistenceId());
90      Assert.assertEquals(lr.getDataStore(),this);
91  
92      //1. check session
93      if (null == this.session) {
94        throw new SecurityException("session not set");
95      }
96  
97      if (false == this.ac.isValidSession(this.session)) {
98        throw new SecurityException("invalid session supplied");
99      }
100 
101     //2. check permissions
102     if (false == canWriteLR(lr.getLRPersistenceId())) {
103       throw new SecurityException("no write access granted to the user");
104     }
105 
106     //3. try to unlock
107     PreparedStatement pstmt = null;
108     boolean lockSucceeded = false;
109 
110     try {
111       String sql = " select persist_unlock_lr(?,?) ";
112       pstmt = this.jdbcConn.prepareStatement(sql);
113       pstmt.setLong(1,((Long)lr.getLRPersistenceId()).longValue());
114       pstmt.setLong(2,this.session.getUser().getID().longValue());
115       pstmt.execute();
116       //we don't care about the result set
117     }
118     catch(SQLException sqle) {
119 
120       switch(sqle.getErrorCode()) {
121         case DBHelper.X_ORACLE_INVALID_LR:
122           throw new PersistenceException("invalid LR ID supplied ["+sqle.getMessage()+"]");
123         default:
124           throw new PersistenceException(
125                 "can't unlock LR in DB : ["+ sqle.getMessage()+"]");
126       }
127     }
128     finally {
129       DBHelper.cleanup(pstmt);
130     }
131   }
132 
133 
134   /**
135    * Checks if the user (identified by the sessionID)
136    * has some access (read/write) to the LR
137    */
138   protected boolean canAccessLR(Long lrID,int mode)
139     throws PersistenceException, SecurityException{
140 
141     //0. preconditions
142     Assert.assertTrue(DBHelper.READ_ACCESS == mode || DBHelper.WRITE_ACCESS == mode);
143 
144     //1. is session initialised?
145     if (null == this.session) {
146       throw new SecurityException("user session not set");
147     }
148 
149     //2.first check the session and then check whether the user is member of the group
150     if (this.ac.isValidSession(this.session) == false) {
151       throw new SecurityException("invalid session supplied");
152     }
153 
154     PreparedStatement pstmt = null;
155     ResultSet rs = null;
156 
157     try {
158       String sql = "select security_has_access_to_lr(?,?,?,?)";
159       pstmt = this.jdbcConn.prepareStatement(sql);
160       pstmt.setLong(1,lrID.longValue());
161       pstmt.setLong(2,this.session.getUser().getID().longValue());
162       pstmt.setLong(3,this.session.getGroup().getID().longValue());
163       pstmt.setLong(4,mode);
164       pstmt.execute();
165       rs = pstmt.getResultSet();
166 
167       if (false == rs.next()) {
168         throw new PersistenceException("empty result set");
169       }
170 
171       return rs.getBoolean(1);
172     }
173     catch(SQLException sqle) {
174       throw new PersistenceException("can't check permissions in DB: ["+ sqle.getMessage()+"]");
175     }
176     finally {
177       DBHelper.cleanup(rs);
178       DBHelper.cleanup(pstmt);
179     }
180 
181   }
182 
183 
184 
185   /**
186    * Try to acquire exlusive lock on a resource from the persistent store.
187    * Always call unlockLR() when the lock is no longer needed
188    */
189   public boolean lockLr(LanguageResource lr)
190   throws PersistenceException,SecurityException {
191 
192     //0. preconditions
193     Assert.assertNotNull(lr);
194     Assert.assertTrue(lr instanceof DatabaseDocumentImpl ||
195                       lr instanceof DatabaseCorpusImpl);
196     Assert.assertNotNull(lr.getLRPersistenceId());
197     Assert.assertEquals(lr.getDataStore(),this);
198 
199     //1. delegate
200     return _lockLr((Long)lr.getLRPersistenceId());
201   }
202 
203 
204   /**
205    *  helper for lockLR()
206    *  never call directly
207    */
208   private boolean _lockLr(Long lrID)
209   throws PersistenceException,SecurityException {
210 
211     //0. preconditions
212     Assert.assertNotNull(lrID);
213 
214     //1. check session
215     if (null == this.session) {
216       throw new SecurityException("session not set");
217     }
218 
219     if (false == this.ac.isValidSession(this.session)) {
220       throw new SecurityException("invalid session supplied");
221     }
222 
223     //2. check permissions
224     if (false == canWriteLR(lrID)) {
225       throw new SecurityException("no write access granted to the user");
226     }
227 
228     //3. try to lock
229     PreparedStatement pstmt = null;
230     ResultSet rset = null;
231     boolean lockSucceeded = false;
232 
233     try {
234       pstmt = this.jdbcConn.prepareStatement(" select persist_lock_lr(?,?,?) ");
235       pstmt.setLong(1,lrID.longValue());
236       pstmt.setLong(2,this.session.getUser().getID().longValue());
237       pstmt.setLong(3,this.session.getGroup().getID().longValue());
238 
239       pstmt.execute();
240       rset = pstmt.getResultSet();
241 
242       if (false == rset.next()) {
243         throw new PersistenceException("empty result set");
244       }
245 
246       lockSucceeded = rset.getBoolean(1);
247     }
248     catch(SQLException sqle) {
249 
250       switch(sqle.getErrorCode()) {
251         case DBHelper.X_ORACLE_INVALID_LR:
252           throw new PersistenceException("invalid LR ID supplied ["+sqle.getMessage()+"]");
253         default:
254           throw new PersistenceException(
255                 "can't lock LR in DB : ["+ sqle.getMessage()+"]");
256       }
257     }
258     finally {
259       DBHelper.cleanup(rset);
260       DBHelper.cleanup(pstmt);
261     }
262 
263     return lockSucceeded;
264   }
265 
266 
267 /*  protected Corpus createCorpus(Corpus corp,SecurityInfo secInfo, boolean newTransPerDocument)
268     throws PersistenceException,SecurityException {
269 
270     throw new MethodNotImplementedException();
271   }
272 */
273   /**
274    *  helper for adopt()
275    *  never call directly
276    */
277   protected Long createLR(String lrType,
278                           String lrName,
279                           SecurityInfo si,
280                           Long lrParentID)
281     throws PersistenceException,SecurityException {
282 
283     //0. preconditions
284     Assert.assertNotNull(lrName);
285 
286     //1. check the session
287 //    if (this.ac.isValidSession(s) == false) {
288 //      throw new SecurityException("invalid session provided");
289 //    }
290 
291     //2. create a record in DB
292     PreparedStatement pstmt = null;
293     ResultSet rset = null;
294 
295     try {
296       String sql = " select persist_create_lr(?,?,?,?,?,?) ";
297       pstmt = this.jdbcConn.prepareStatement(sql);
298       pstmt.setLong(1,si.getUser().getID().longValue());
299       pstmt.setLong(2,si.getGroup().getID().longValue());
300       pstmt.setString(3,lrType);
301       pstmt.setString(4,lrName);
302       pstmt.setInt(5,si.getAccessMode());
303       if (null == lrParentID) {
304         pstmt.setNull(6,java.sql.Types.INTEGER);
305       }
306       else {
307         pstmt.setLong(6,lrParentID.longValue());
308       }
309 
310       pstmt.execute();
311       rset = pstmt.getResultSet();
312       if (false == rset.next()) {
313         throw new PersistenceException("empty result set");
314       }
315 
316       Long result =  new Long(rset.getLong(1));
317 
318       return result;
319     }
320     catch(SQLException sqle) {
321 
322       switch(sqle.getErrorCode()) {
323         case DBHelper.X_ORACLE_INVALID_LR_TYPE:
324           throw new PersistenceException("can't create LR [step 3] in DB, invalid LR Type");
325         default:
326           throw new PersistenceException(
327                 "can't create LR [step 3] in DB : ["+ sqle.getMessage()+"]");
328       }
329     }
330     finally {
331       DBHelper.cleanup(rset);
332       DBHelper.cleanup(pstmt);
333     }
334   }
335 
336 
337   /**
338    * helper for adopt
339    * never call directly
340    */
341   protected Long createDoc(Long _lrID,
342                           URL _docURL,
343                           String _docEncoding,
344                           Long _docStartOffset,
345                           Long _docEndOffset,
346                           Boolean _docIsMarkupAware,
347                           Long _corpusID)
348     throws PersistenceException {
349 
350     PreparedStatement pstmt = null;
351     ResultSet rset = null;
352     Long docID = null;
353 
354     try {
355       pstmt = this.jdbcConn.prepareStatement(
356                 " select persist_create_document(?,?,?,?,?,?,?) ");
357       pstmt.setLong(1,_lrID.longValue());
358       pstmt.setString(2,_docURL != null ? _docURL.toString() : "" );
359       //do we have doc encoding?
360       if (null == _docEncoding) {
361         pstmt.setNull(3,java.sql.Types.VARCHAR);
362       }
363       else {
364         pstmt.setString(3,_docEncoding);
365       }
366       //do we have start offset?
367       if (null==_docStartOffset) {
368         pstmt.setNull(4,java.sql.Types.INTEGER);
369       }
370       else {
371         pstmt.setLong(4,_docStartOffset.longValue());
372       }
373       //do we have end offset?
374       if (null==_docEndOffset) {
375         pstmt.setNull(5,java.sql.Types.INTEGER);
376       }
377       else {
378         pstmt.setLong(5,_docEndOffset.longValue());
379       }
380 
381       pstmt.setBoolean(6,_docIsMarkupAware.booleanValue());
382 
383       //is the document part of a corpus?
384       if (null == _corpusID) {
385         pstmt.setNull(7,java.sql.Types.BIGINT);
386       }
387       else {
388         pstmt.setLong(7,_corpusID.longValue());
389       }
390 
391       pstmt.execute();
392       rset = pstmt.getResultSet();
393       if (false == rset.next()) {
394         throw new PersistenceException("empty result set");
395       }
396 
397       docID = new Long(rset.getLong(1));
398 
399       return docID;
400 
401     }
402     catch(SQLException sqle) {
403       throw new PersistenceException("can't create document [step 4] in DB: ["+ sqle.getMessage()+"]");
404     }
405     finally {
406       DBHelper.cleanup(rset);
407       DBHelper.cleanup(pstmt);
408     }
409 
410   }
411 
412 
413   /** creates an entry for annotation set in the database */
414   protected void createAnnotationSet(Long lrID, AnnotationSet aset)
415     throws PersistenceException {
416 
417     //1. create a-set
418     String asetName = aset.getName();
419     Long asetID = null;
420 
421     //DB stuff
422     PreparedStatement pstmt = null;
423     ResultSet rs = null;
424 
425     try {
426       String sql = "select persist_create_annotation_set(?,?)";
427       pstmt = this.jdbcConn.prepareStatement(sql);
428 
429       pstmt.setLong(1,lrID.longValue());
430       if (null == asetName) {
431         pstmt.setNull(2,java.sql.Types.VARCHAR);
432       }
433       else {
434         pstmt.setString(2,asetName);
435       }
436       pstmt.execute();
437       rs = pstmt.getResultSet();
438 
439       if (false == rs.next()) {
440         throw new PersistenceException("empty result set");
441       }
442 
443       asetID = new Long(rs.getLong(1));
444     }
445     catch(SQLException sqle) {
446       throw new PersistenceException("can't create a-set [step 1] in DB: ["+ sqle.getMessage()+"]");
447     }
448     finally {
449       DBHelper.cleanup(rs);
450       DBHelper.cleanup(pstmt);
451     }
452 
453 
454     //2. insert annotations/nodes for DEFAULT a-set
455     //for now use a stupid cycle
456     //TODO: pass all the data with one DB call (?)
457 
458     try {
459       String sql = "select persist_create_annotation(?,?,?,?,?,?,?,?) ";
460       pstmt = this.jdbcConn.prepareStatement(sql);
461 
462 
463       Iterator itAnnotations = aset.iterator();
464 
465       while (itAnnotations.hasNext()) {
466         Annotation ann = (Annotation)itAnnotations.next();
467         Node start = (Node)ann.getStartNode();
468         Node end = (Node)ann.getEndNode();
469         String type = ann.getType();
470 
471         //DB stuff
472         Long annGlobalID = null;
473         pstmt.setLong(1,lrID.longValue());
474         pstmt.setLong(2,ann.getId().longValue());
475         pstmt.setLong(3,asetID.longValue());
476         pstmt.setLong(4,start.getId().longValue());
477         pstmt.setLong(5,start.getOffset().longValue());
478         pstmt.setLong(6,end.getId().longValue());
479         pstmt.setLong(7,end.getOffset().longValue());
480         pstmt.setString(8,type);
481         pstmt.execute();
482         rs = pstmt.getResultSet();
483 
484         if (false == rs.next()) {
485           throw new PersistenceException("empty result set");
486         }
487 
488         annGlobalID = new Long(rs.getLong(1));
489         DBHelper.cleanup(rs);
490 
491         //2.1. set annotation features
492         FeatureMap features = ann.getFeatures();
493         Assert.assertNotNull(features);
494         createFeatures(annGlobalID,DBHelper.FEATURE_OWNER_ANNOTATION,features);
495 //        createFeaturesBulk(annGlobalID,DBHelper.FEATURE_OWNER_ANNOTATION,features);
496       } //while
497     }//try
498     catch(SQLException sqle) {
499 
500       switch(sqle.getErrorCode()) {
501 
502         case DBHelper.X_ORACLE_INVALID_ANNOTATION_TYPE:
503           throw new PersistenceException(
504                               "can't create annotation in DB, [invalid annotation type]");
505         default:
506           throw new PersistenceException(
507                 "can't create annotation in DB: ["+ sqle.getMessage()+"]");
508       }//switch
509     }//catch
510     finally {
511       DBHelper.cleanup(pstmt);
512     }
513   }
514 
515   /**
516    *  updates the content of the document if it is binary or a long string
517    *  (that does not fit into VARCHAR2)
518    */
519   protected void updateDocumentContent(Long docID,DocumentContent content)
520     throws PersistenceException {
521 
522     //1. get LOB locators from DB
523     PreparedStatement pstmt = null;
524     try {
525       String sql =  " update  t_doc_content "      +
526                     " set     dc_character_content = ?,  " +
527                     "         dc_content_type = ? " +
528                     " where   dc_id = (select doc_content_id " +
529                     "                   from t_document " +
530                     "                   where doc_id = ?) ";
531 
532       pstmt = this.jdbcConn.prepareStatement(sql);
533       pstmt.setString(1,content.toString());
534       pstmt.setInt(2,DBHelper.CHARACTER_CONTENT);
535       pstmt.setLong(3,docID.longValue());
536       pstmt.executeUpdate();
537     }
538     catch(SQLException sqle) {
539       throw new PersistenceException("can't update document content in DB : ["+
540                                       sqle.getMessage()+"]");
541     }
542     finally {
543       DBHelper.cleanup(pstmt);
544     }
545 
546   }
547 
548 
549 
550 
551   /**
552    *  creates a feature with the specified type/key/value for the specified entity
553    *  entitties are either LRs ot Annotations
554    *  valid values are: boolean,
555    *                    int,
556    *                    long,
557    *                    string,
558    *                    float,
559    *                    Object,
560    *                    boolean List,
561    *                    int List,
562    *                    long List,
563    *                    string List,
564    *                    float List,
565    *                    Object List
566    *
567    */
568 
569   private void createFeature(Long entityID, int entityType,String key, Object value, PreparedStatement pstmt)
570     throws PersistenceException {
571 
572     //1. what kind of feature value is this?
573     int valueType = findFeatureType(value);
574 
575     //2. how many elements do we store?
576     Vector elementsToStore = new Vector();
577 
578     switch(valueType) {
579       case DBHelper.VALUE_TYPE_NULL:
580       case DBHelper.VALUE_TYPE_BINARY:
581       case DBHelper.VALUE_TYPE_BOOLEAN:
582       case DBHelper.VALUE_TYPE_FLOAT:
583       case DBHelper.VALUE_TYPE_INTEGER:
584       case DBHelper.VALUE_TYPE_LONG:
585       case DBHelper.VALUE_TYPE_STRING:
586         elementsToStore.add(value);
587         break;
588 
589       default:
590         //arrays
591         List arr = (List)value;
592         Iterator itValues = arr.iterator();
593 
594         while (itValues.hasNext()) {
595           elementsToStore.add(itValues.next());
596         }
597 
598         //normalize , i.e. ignore arrays
599         if (valueType == DBHelper.VALUE_TYPE_BINARY_ARR)
600           valueType = DBHelper.VALUE_TYPE_BINARY;
601         else if (valueType == DBHelper.VALUE_TYPE_BOOLEAN_ARR)
602           valueType = DBHelper.VALUE_TYPE_BOOLEAN;
603         else if (valueType == DBHelper.VALUE_TYPE_FLOAT_ARR)
604           valueType = DBHelper.VALUE_TYPE_FLOAT;
605         else if (valueType == DBHelper.VALUE_TYPE_INTEGER_ARR)
606           valueType = DBHelper.VALUE_TYPE_INTEGER;
607         else if (valueType == DBHelper.VALUE_TYPE_LONG_ARR)
608           valueType = DBHelper.VALUE_TYPE_LONG;
609         else if (valueType == DBHelper.VALUE_TYPE_STRING_ARR)
610           valueType = DBHelper.VALUE_TYPE_STRING;
611     }
612 
613     //3. for all elements:
614     for (int i=0; i< elementsToStore.size(); i++) {
615 
616         Object currValue = elementsToStore.elementAt(i);
617 
618         //3.1. create a dummy feature [LOB hack]
619         Long featID = _createFeature(entityID,entityType,key,currValue,valueType,pstmt);
620     }
621 
622   }
623 
624 
625 
626   /**
627    *  helper metod
628    *  iterates a FeatureMap and creates all its features in the database
629    */
630   protected void createFeatures(Long entityID, int entityType, FeatureMap features)
631     throws PersistenceException {
632 
633     //0. prepare statement ad use it for all features
634     PreparedStatement pstmt = null;
635 
636     try {
637       String sql = "select persist_create_feature(?,?,?,?,?,?,?,?) ";
638       pstmt = this.jdbcConn.prepareStatement(sql);
639     }
640     catch (SQLException sqle) {
641       throw new PersistenceException(sqle);
642     }
643 
644     /* when some day Java has macros, this will be a macro */
645     Set entries = features.entrySet();
646     Iterator itFeatures = entries.iterator();
647     while (itFeatures.hasNext()) {
648       Map.Entry entry = (Map.Entry)itFeatures.next();
649       String key = (String)entry.getKey();
650       Object value = entry.getValue();
651       createFeature(entityID,entityType,key,value,pstmt);
652     }
653 
654     //3. cleanup
655     DBHelper.cleanup(pstmt);
656   }
657 
658   protected void createFeaturesBulk(Long entityID, int entityType, FeatureMap features)
659     throws PersistenceException {
660 
661     throw new MethodNotImplementedException();
662   }
663 
664   /**
665    *  creates a feature of the specified type/value/valueType/key for the specified entity
666    *  Entity is one of: LR, Annotation
667    *  Value types are: boolean, int, long, string, float, Object
668    */
669   private Long _createFeature(Long entityID,
670                               int entityType,
671                               String key,
672                               Object value,
673                               int valueType,
674                               PreparedStatement pstmt)
675     throws PersistenceException {
676 
677     //1. store in DB
678     Long featID = null;
679     ResultSet rs = null;
680 
681     try {
682 
683       //1.1 set known values + NULLs
684       pstmt.setLong(1,entityID.longValue());
685       pstmt.setInt(2,entityType);
686       pstmt.setString(3,key);
687       pstmt.setNull(4,java.sql.Types.BIGINT);
688       pstmt.setNull(5,java.sql.Types.DOUBLE);
689       pstmt.setNull(6,java.sql.Types.LONGVARCHAR);
690       pstmt.setNull(7,java.sql.Types.LONGVARBINARY);
691       pstmt.setInt(8,valueType);
692 
693       //1.2 set proper data
694       switch(valueType) {
695 
696         case DBHelper.VALUE_TYPE_NULL:
697           break;
698 
699         case DBHelper.VALUE_TYPE_BOOLEAN:
700 
701           boolean b = ((Boolean)value).booleanValue();
702           pstmt.setLong(4, b ? DBHelper.TRUE : DBHelper.FALSE);
703           break;
704 
705         case DBHelper.VALUE_TYPE_INTEGER:
706 
707           pstmt.setLong(4,((Integer)value).intValue());
708           break;
709 
710         case DBHelper.VALUE_TYPE_LONG:
711 
712           pstmt.setLong(4,((Long)value).longValue());
713           break;
714 
715         case DBHelper.VALUE_TYPE_FLOAT:
716 
717           Double d = (Double)value;
718           pstmt.setDouble(5,d.doubleValue());
719           break;
720 
721         case DBHelper.VALUE_TYPE_BINARY:
722           //we serialize the value (object) in the DB
723           ByteArrayOutputStream baos = new ByteArrayOutputStream();
724           ObjectOutputStream oos = new ObjectOutputStream(baos);
725           oos.writeObject(value);
726           oos.close();
727           baos.close();
728           byte[] buff = baos.toByteArray();
729           ByteArrayInputStream bais = new ByteArrayInputStream(buff);
730           pstmt.setBinaryStream(7,bais,buff.length);
731           bais.close();
732           break;
733 
734         case DBHelper.VALUE_TYPE_STRING:
735 
736           String s = (String)value;
737           //does it fin into a varchar2?
738           pstmt.setString(6,s);
739           break;
740 
741         default:
742           throw new IllegalArgumentException("unsuppoeted feature type");
743       }
744 
745       pstmt.execute();
746       rs = pstmt.getResultSet();
747 
748       if (false == rs.next()) {
749         throw new PersistenceException("empty result set");
750       }
751 
752       featID = new Long(rs.getLong(1));
753     }
754     catch(IOException ioe) {
755       throw new PersistenceException("can't write binary data ["+ioe.getMessage()+"]");
756     }
757     catch(SQLException sqle) {
758 
759       switch(sqle.getErrorCode()) {
760         case DBHelper.X_ORACLE_INVALID_FEATURE_TYPE:
761           throw new PersistenceException("can't create feature [step 1],"+
762                       "[invalid feature type] in DB: ["+ sqle.getMessage()+"]");
763         default:
764           throw new PersistenceException("can't create feature [step 1] in DB: ["+
765                                                       sqle.getMessage()+"]");
766       }
767     }
768     finally {
769       DBHelper.cleanup(rs);
770 //      DBHelper.cleanup(stmt);
771     }
772 
773     return featID;
774   }
775 
776 
777   /**
778    *  updates the value of a feature where the value is string (>4000 bytes, stored as CLOB)
779    *  or Object (stored as BLOB)
780    */
781 /*  private void _updateFeatureLOB(Long featID,Object value, int valueType)
782     throws PersistenceException {
783 
784     throw new MethodNotImplementedException();
785   }
786 */
787   /** helper for sync() - saves a Corpus in the database */
788 /*  protected void syncCorpus(Corpus corp)
789     throws PersistenceException,SecurityException {
790 
791     throw new MethodNotImplementedException();
792   }
793 */
794 
795   /**
796    *  helper for sync()
797    *  NEVER call directly
798    */
799   protected void _syncLR(LanguageResource lr)
800     throws PersistenceException,SecurityException {
801 
802     //0.preconditions
803     Assert.assertTrue(lr instanceof DatabaseDocumentImpl ||
804                       lr instanceof DatabaseCorpusImpl);;
805     Assert.assertNotNull(lr.getLRPersistenceId());
806 
807     PreparedStatement pstmt = null;
808 
809     try {
810       pstmt = this.jdbcConn.prepareStatement("select persist_update_lr(?,?,?)");
811       pstmt.setLong(1,((Long)lr.getLRPersistenceId()).longValue());
812       pstmt.setString(2,lr.getName());
813       //do we have a parent resource?
814       if (lr instanceof Document &&
815           null != lr.getParent()) {
816         pstmt.setLong(3,((Long)lr.getParent().getLRPersistenceId()).longValue());
817       }
818       else {
819         pstmt.setNull(3,java.sql.Types.BIGINT);
820       }
821 
822       pstmt.execute();
823     }
824     catch(SQLException sqle) {
825 
826       switch(sqle.getErrorCode()) {
827         case DBHelper.X_ORACLE_INVALID_LR:
828           throw new PersistenceException("can't set LR name in DB: [invalid LR ID]");
829         default:
830           throw new PersistenceException(
831                 "can't set LR name in DB: ["+ sqle.getMessage()+"]");
832       }
833 
834     }
835     finally {
836       DBHelper.cleanup(pstmt);
837     }
838 
839   }
840 
841   /** helper for sync() - never call directly */
842   protected void _syncDocumentHeader(Document doc)
843     throws PersistenceException {
844 
845     Long lrID = (Long)doc.getLRPersistenceId();
846 
847     PreparedStatement pstmt = null;
848 
849     try {
850       pstmt = this.jdbcConn.prepareStatement("select persist_update_document(?,?,?,?,?)");
851       pstmt.setLong(1,lrID.longValue());
852       if(doc.getSourceUrl() != null){
853         pstmt.setString(2, doc.getSourceUrl().toString());
854       }else{
855         pstmt.setString(2, "");
856       }
857       //do we have start offset?
858       if (null==doc.getSourceUrlStartOffset()) {
859         pstmt.setNull(3,java.sql.Types.INTEGER);
860       }
861       else {
862         pstmt.setLong(3,doc.getSourceUrlStartOffset().longValue());
863       }
864       //do we have end offset?
865       if (null==doc.getSourceUrlEndOffset()) {
866         pstmt.setNull(4,java.sql.Types.INTEGER);
867       }
868       else {
869         pstmt.setLong(4,doc.getSourceUrlEndOffset().longValue());
870       }
871 
872       pstmt.setBoolean(5,doc.getMarkupAware().booleanValue());
873       pstmt.execute();
874     }
875     catch(SQLException sqle) {
876 
877       switch(sqle.getErrorCode()) {
878         case DBHelper.X_ORACLE_INVALID_LR :
879           throw new PersistenceException("invalid LR supplied: no such document: ["+
880                                                             sqle.getMessage()+"]");
881         default:
882           throw new PersistenceException("can't change document data: ["+
883                                                             sqle.getMessage()+"]");
884       }
885     }
886     finally {
887       DBHelper.cleanup(pstmt);
888     }
889 
890   }
891 
892   /** helper for sync() - never call directly */
893   protected void _syncDocumentContent(Document doc)
894     throws PersistenceException {
895 
896     //0.
897     Assert.assertNotNull(doc);
898     Assert.assertNotNull(doc.getLRPersistenceId());
899     Assert.assertTrue(doc instanceof DatabaseDocumentImpl);
900 
901     PreparedStatement pstmt = null;
902     //1.
903     try {
904       pstmt = this.jdbcConn.prepareStatement("select persist_update_document_content(?,?)");
905       pstmt.setLong(1,((Long)doc.getLRPersistenceId()).longValue());
906 
907       DocumentContent dc = doc.getContent();
908       if (dc.size().longValue() > 0) {
909         pstmt.setString(2,dc.toString());
910       }
911       else {
912         pstmt.setNull(2,java.sql.Types.LONGVARCHAR);
913       }
914 
915       pstmt.execute();
916     }
917     catch(SQLException sqle) {
918       throw new PersistenceException("Cannot update document content ["+
919                                       sqle.getMessage()+"]");
920     }
921     finally {
922       DBHelper.cleanup(pstmt);
923     }
924   }
925 
926   /** helper for sync() - never call directly */
927   protected void _syncFeatures(LanguageResource lr)
928     throws PersistenceException {
929 
930     //0. preconditions
931     Assert.assertNotNull(lr);
932     Assert.assertNotNull(lr.getLRPersistenceId());
933     Assert.assertEquals(((DatabaseDataStore)lr.getDataStore()).getDatabaseID(),
934                       this.getDatabaseID());
935     Assert.assertTrue(lr instanceof Document || lr instanceof Corpus);
936     //we have to be in the context of transaction
937 
938     //1, get ID  in the DB
939     Long lrID = (Long)lr.getLRPersistenceId();
940     int  entityType;
941 
942     //2. delete features
943     PreparedStatement pstmt = null;
944     try {
945       Assert.assertTrue(false == this.jdbcConn.getAutoCommit());
946       pstmt = this.jdbcConn.prepareStatement("select persist_delete_features(?,?) ");
947       pstmt.setLong(1,lrID.longValue());
948 
949       if (lr instanceof Document) {
950         entityType = DBHelper.FEATURE_OWNER_DOCUMENT;
951       }
952       else if (lr instanceof Corpus) {
953         entityType = DBHelper.FEATURE_OWNER_CORPUS;
954       }
955       else {
956         throw new IllegalArgumentException();
957       }
958 
959       pstmt.setInt(2,entityType);
960       pstmt.execute();
961     }
962     catch(SQLException sqle) {
963       throw new PersistenceException("can't delete features in DB: ["+ sqle.getMessage()+"]");
964     }
965     finally {
966       DBHelper.cleanup(pstmt);
967     }
968 
969     //3. recreate them
970     createFeatures(lrID,entityType, lr.getFeatures());
971   }
972 
973   /** helper for sync() - never call directly */
974 /*  protected void _syncAnnotationSets(Document doc,Collection removedSets,Collection addedSets)
975     throws PersistenceException {
976 
977     throw new MethodNotImplementedException();
978   }
979 */
980 
981   /** helper for sync() - never call directly */
982 /*  protected void _syncAddedAnnotations(Document doc, AnnotationSet as, Collection changes)
983     throws PersistenceException {
984 
985     throw new MethodNotImplementedException();
986   }
987 */
988 
989   /** helper for sync() - never call directly */
990 /*  protected void _syncRemovedAnnotations(Document doc,AnnotationSet as, Collection changes)
991     throws PersistenceException {
992 
993     throw new MethodNotImplementedException();
994   }
995 */
996   /** helper for sync() - never call directly */
997 /*  protected void _syncChangedAnnotations(Document doc,AnnotationSet as, Collection changes)
998     throws PersistenceException {
999 
1000    throw new MethodNotImplementedException();
1001  }
1002*/
1003
1004  /**
1005   *  reads the features of an entity
1006   *  entities are of type LR or Annotation
1007   */
1008  protected FeatureMap readFeatures(Long entityID, int entityType)
1009    throws PersistenceException {
1010
1011    //0. preconditions
1012    Assert.assertNotNull(entityID);
1013    Assert.assertTrue(entityType == DBHelper.FEATURE_OWNER_ANNOTATION ||
1014                  entityType == DBHelper.FEATURE_OWNER_CORPUS ||
1015                  entityType == DBHelper.FEATURE_OWNER_DOCUMENT);
1016
1017
1018    PreparedStatement pstmt = null;
1019    ResultSet rs = null;
1020    FeatureMap fm = new SimpleFeatureMapImpl();
1021
1022    //1. read from DB
1023    try {
1024      String sql = " select ftkey.fk_string, " +
1025                   "        ft.ft_value_type, " +
1026                   "        ft.ft_int_value, " +
1027                   "        ft.ft_float_value, " +
1028                   "        ft.ft_binary_value, " +
1029                   "        ft.ft_character_value " +
1030                   " from   t_feature ft, " +
1031                   "        t_feature_key ftkey " +
1032                   " where  ft.ft_entity_id = ? " +
1033                   "        and ft.ft_entity_type = ? " +
1034                   "        and ft.ft_key_id = ftkey.fk_id " +
1035                   " order by ftkey.fk_string,ft.ft_id";
1036
1037      pstmt = this.jdbcConn.prepareStatement(sql);
1038      pstmt.setLong(1,entityID.longValue());
1039      pstmt.setLong(2,entityType);
1040      pstmt.execute();
1041      rs = pstmt.getResultSet();
1042
1043      //3. fill feature map
1044      Vector arrFeatures = new Vector();
1045      String prevKey = null;
1046      String currKey = null;
1047      Object currFeature = null;
1048
1049
1050      while (rs.next()) {
1051        //NOTE: because there are LOBs in the resulset
1052        //the columns should be read in the order they appear
1053        //in the query
1054        currKey = rs.getString("fk_string");
1055
1056        Long valueType = new Long(rs.getLong("ft_value_type"));
1057
1058        //we don't quite know what is the type of the NUMBER
1059        //stored in DB
1060        Object numberValue = null;
1061
1062        //for all numeric types + boolean -> read from DB as appropriate
1063        //Java object
1064        switch(valueType.intValue()) {
1065
1066          case DBHelper.VALUE_TYPE_BOOLEAN:
1067            numberValue = new Boolean(rs.getBoolean("ft_int_value"));
1068            break;
1069
1070          case DBHelper.VALUE_TYPE_FLOAT:
1071            numberValue = new Double(rs.getDouble("ft_float_value"));
1072            break;
1073
1074          case DBHelper.VALUE_TYPE_INTEGER:
1075            numberValue = new Integer(rs.getInt("ft_int_value"));
1076            break;
1077
1078          case DBHelper.VALUE_TYPE_LONG:
1079            numberValue = new Long(rs.getLong("ft_int_value"));
1080            break;
1081        }
1082
1083        //don't forget to read the rest of the current row
1084        InputStream blobValue = rs.getBinaryStream("ft_binary_value");
1085        String stringValue = rs.getString("ft_character_value");
1086
1087        switch(valueType.intValue()) {
1088
1089          case DBHelper.VALUE_TYPE_NULL:
1090            currFeature = null;
1091            break;
1092
1093          case DBHelper.VALUE_TYPE_BOOLEAN:
1094          case DBHelper.VALUE_TYPE_FLOAT:
1095          case DBHelper.VALUE_TYPE_INTEGER:
1096          case DBHelper.VALUE_TYPE_LONG:
1097            currFeature = numberValue;
1098            break;
1099
1100          case DBHelper.VALUE_TYPE_BINARY:
1101            //deserialize a java object
1102            ObjectInputStream ois = new ObjectInputStream(blobValue);
1103            currFeature = ois.readObject();
1104            ois.close();
1105            blobValue.close();
1106            break;
1107
1108          case DBHelper.VALUE_TYPE_STRING:
1109            currFeature = stringValue;
1110            break;
1111
1112          default:
1113            throw new PersistenceException("Invalid feature type found in DB, type is ["+valueType.intValue()+"]");
1114        }//switch
1115
1116        //new feature or part of an array?
1117        if (currKey.equals(prevKey) && prevKey != null) {
1118          //part of array
1119          arrFeatures.add(currFeature);
1120        }
1121        else {
1122          //add prev feature to feature map
1123
1124          //is the prev feature an array or a single object?
1125          if (arrFeatures.size() > 1) {
1126            //put a clone, because this is a temp array that will
1127            //be cleared in few lines
1128            fm.put(prevKey, new Vector(arrFeatures));
1129          }
1130          else if (arrFeatures.size() == 1) {
1131            fm.put(prevKey,arrFeatures.elementAt(0));
1132          }
1133          else {
1134            //do nothing, this is the dummy feature
1135            ;
1136          }//if
1137
1138          //now clear the array from previous fesature(s) and put the new
1139          //one there
1140          arrFeatures.clear();
1141
1142          prevKey = currKey;
1143          arrFeatures.add(currFeature);
1144        }//if
1145      }//while
1146
1147      //add the last feature
1148      if (arrFeatures.size() > 1) {
1149        fm.put(currKey,arrFeatures);
1150      }
1151      else if (arrFeatures.size() == 1) {
1152        fm.put(currKey,arrFeatures.elementAt(0));
1153      }
1154    }//try
1155    catch(SQLException sqle) {
1156      throw new PersistenceException("can't read features from DB: ["+ sqle.getMessage()+"]");
1157    }
1158    catch(IOException ioe) {
1159      throw new PersistenceException("can't read features from DB: ["+ ioe.getMessage()+"]");
1160    }
1161    catch(ClassNotFoundException cnfe) {
1162      throw new PersistenceException("can't read features from DB: ["+ cnfe.getMessage()+"]");
1163    }
1164    finally {
1165      DBHelper.cleanup(rs);
1166      DBHelper.cleanup(pstmt);
1167    }
1168
1169    return fm;
1170  }
1171
1172
1173  /**
1174   *  helper method for delete()
1175   *  never call it directly beause proper events will not be fired
1176   */
1177  protected void deleteDocument(Long lrId)
1178  throws PersistenceException {
1179    //0. preconditions
1180    Assert.assertNotNull(lrId);
1181
1182    PreparedStatement pstmt = null;
1183
1184    //1. delete from DB
1185    try {
1186      pstmt = this.jdbcConn.prepareStatement("select persist_delete_document(?) ");
1187      pstmt.setLong(1,lrId.longValue());
1188      pstmt.execute();
1189    }
1190    catch(SQLException sqle) {
1191      throw new PersistenceException("can't delete LR from DB: ["+ sqle.getMessage()+"]");
1192    }
1193    finally {
1194      DBHelper.cleanup(pstmt);
1195    }
1196  }
1197
1198  /**
1199   *  helper method for delete()
1200   *  never call it directly beause proper events will not be fired
1201   */
1202  protected void deleteCorpus(Long lrId)
1203    throws PersistenceException {
1204
1205    Long ID = (Long)lrId;
1206
1207    PreparedStatement pstmt = null;
1208
1209    try {
1210      pstmt = this.jdbcConn.prepareStatement("select persist_delete_corpus(?)");
1211      pstmt.setLong(1,ID.longValue());
1212      pstmt.execute();
1213    }
1214    catch(SQLException sqle) {
1215      throw new PersistenceException("can't delete LR from DB: ["+ sqle.getMessage()+"]");
1216    }
1217    finally {
1218      DBHelper.cleanup(pstmt);
1219    }
1220  }
1221
1222
1223  /** helper for sync() - never call directly */
1224  protected void _syncRemovedDocumentsFromCorpus(List docLRIDs, Long corpLRID)
1225    throws PersistenceException {
1226
1227    //0.preconditions
1228    Assert.assertNotNull(docLRIDs);
1229    Assert.assertNotNull(corpLRID);
1230    Assert.assertTrue(docLRIDs.size() > 0);
1231
1232    PreparedStatement pstmt = null;
1233
1234    try {
1235      pstmt = this.jdbcConn.prepareStatement("select persist_remove_doc_from_corpus(?,?)");
1236
1237      Iterator it = docLRIDs.iterator();
1238      while (it.hasNext()) {
1239        Long currLRID = (Long)it.next();
1240        pstmt.setLong(1,currLRID.longValue());
1241        pstmt.setLong(2,corpLRID.longValue());
1242        pstmt.execute();
1243      }
1244    }
1245    catch(SQLException sqle) {
1246
1247      switch(sqle.getErrorCode()) {
1248        case DBHelper.X_ORACLE_INVALID_LR :
1249          throw new PersistenceException("invalid LR supplied: no such document: ["+
1250                                                            sqle.getMessage()+"]");
1251        default:
1252          throw new PersistenceException("can't change document data: ["+
1253                                                            sqle.getMessage()+"]");
1254      }
1255    }
1256    finally {
1257      DBHelper.cleanup(pstmt);
1258    }
1259
1260  }
1261
1262  /**
1263   *   adds document to corpus in the database
1264   *   if the document is already part of the corpus nothing
1265   *   changes
1266   */
1267  protected void addDocumentToCorpus(Long docID,Long corpID)
1268  throws PersistenceException,SecurityException {
1269
1270    //0. preconditions
1271    Assert.assertNotNull(docID);
1272    Assert.assertNotNull(corpID);
1273
1274    //1. check session
1275    if (null == this.session) {
1276      throw new SecurityException("session not set");
1277    }
1278
1279    if (false == this.ac.isValidSession(this.session)) {
1280      throw new SecurityException("invalid session supplied");
1281    }
1282
1283    //2. check permissions
1284    if (false == canWriteLR(corpID)) {
1285      throw new SecurityException("no write access granted to the user");
1286    }
1287
1288    if (false == canWriteLR(docID)) {
1289      throw new SecurityException("no write access granted to the user");
1290    }
1291
1292    //3. database
1293    PreparedStatement pstmt = null;
1294
1295    try {
1296      pstmt = this.jdbcConn.prepareStatement("select persist_add_document_to_corpus(?,?) ");
1297      pstmt.setLong(1,docID.longValue());
1298      pstmt.setLong(2,corpID.longValue());
1299      pstmt.execute();
1300    }
1301    catch(SQLException sqle) {
1302
1303      switch(sqle.getErrorCode()) {
1304        case DBHelper.X_ORACLE_INVALID_LR:
1305          throw new PersistenceException("invalid LR ID supplied ["+sqle.getMessage()+"]");
1306        default:
1307          throw new PersistenceException(
1308                "can't add document to corpus : ["+ sqle.getMessage()+"]");
1309      }
1310    }
1311    finally {
1312      DBHelper.cleanup(pstmt);
1313    }
1314  }
1315
1316
1317}