View Javadoc
1   /**
2    * 
3    */
4   package com.gisgraphy.domain.repository;
5   
6   import java.io.BufferedInputStream;
7   import java.io.BufferedReader;
8   import java.io.File;
9   import java.io.FileInputStream;
10  import java.io.FileNotFoundException;
11  import java.io.IOException;
12  import java.io.InputStream;
13  import java.io.InputStreamReader;
14  import java.io.UnsupportedEncodingException;
15  import java.sql.SQLException;
16  import java.util.ArrayList;
17  import java.util.List;
18  
19  import javax.persistence.PersistenceException;
20  
21  import org.hibernate.Query;
22  import org.hibernate.Session;
23  import org.hibernate.cfg.AnnotationConfiguration;
24  import org.hibernate.tool.hbm2ddl.SchemaExport;
25  import org.hibernatespatial.postgis.PostgisDialectNG;
26  import org.slf4j.Logger;
27  import org.slf4j.LoggerFactory;
28  import org.springframework.orm.hibernate3.HibernateCallback;
29  import org.springframework.orm.hibernate3.support.HibernateDaoSupport;
30  import org.springframework.stereotype.Component;
31  import org.springframework.util.Assert;
32  
33  import com.gisgraphy.domain.valueobject.Constants;
34  import com.gisgraphy.helper.FileHelper;
35  import com.gisgraphy.helper.FileLineFilter;
36  
37  /**
38   * Default implementation of {@link IDatabaseHelper}
39   * 
40   * @author <a href="mailto:david.masclet@gisgraphy.com">David Masclet</a>
41   */
42  @Component
43  public class DatabaseHelper extends HibernateDaoSupport implements IDatabaseHelper {
44  
45  	protected static final Logger logger = LoggerFactory.getLogger(DatabaseHelper.class);
46  	
47  	public static String[] TABLES_NAME_THAT_MUST_BE_KEPT_WHEN_RESETING_IMPORT= {"app_user","role","user_role","StatsUsage"};
48  	
49  	public static final String NORMALIZE_TEXT_FUNCTION_NAME = "normalize_text";
50  	
51  	public static final String NORMALIZE_TEXT_POSTRES_FUNCTION_BODY = String.format("CREATE OR REPLACE FUNCTION %s(text) RETURNS text AS 'BEGIN RETURN replace(replace(replace(replace(replace(translate(trim(lower($1)),''âãäåāăąàèéêëēĕėęěìíîïìĩīĭóôõöōŏőðøùúûüũūŭůçñÿ'',''aaaaaaaaeeeeeeeeeiiiiiiiiooooooooouuuuuuuucny''),''-'','' ''),''.'','' ''),''\\;'','' ''),''\"'','' ''),'''''''','' '') \u003B END '  LANGUAGE 'plpgsql' RETURNS NULL ON NULL INPUT ",DatabaseHelper.NORMALIZE_TEXT_FUNCTION_NAME);
52  
53  	/* (non-Javadoc)
54  	 * @see com.gisgraphy.domain.repository.IDatabaseHelper#execute(java.io.File, boolean)
55  	 */
56  	@SuppressWarnings("unchecked")
57  	public List<String> execute(final File file, final boolean continueOnError) throws Exception {
58  		if (file == null) {
59  			throw new IllegalArgumentException("Can not execute a null file");
60  		}
61  
62  		if (!file.exists()) {
63  			throw new IllegalArgumentException("The specified file does not exists and can not be executed : " + file.getAbsolutePath());
64  		}
65  		logger.info("will execute sql file " + file.getAbsolutePath());
66  
67  		return (List<String>) this.getHibernateTemplate().execute(new HibernateCallback() {
68  			public Object doInHibernate(Session session) throws PersistenceException {
69  			    	List<String> exceptionMessageList = new ArrayList<String>();
70  				BufferedReader reader;
71  				
72  				
73  				InputStream inInternal = null;
74  				// uses a BufferedInputStream for better performance
75  				try {
76  				    inInternal = new BufferedInputStream(new FileInputStream(file));
77  				} catch (FileNotFoundException e) {
78  				    throw new RuntimeException(e);
79  				}
80  				try {
81  				    reader = new BufferedReader(new InputStreamReader(inInternal,
82  					    Constants.CHARSET));
83  				} catch (UnsupportedEncodingException e) {
84  				    throw new RuntimeException(e);
85  				}
86  				String line;
87  				int count = 0;
88  				try {
89  					while ((line = reader.readLine()) != null) {
90  						line = line.trim();
91  						 // comment or empty or psql command
92  						if (line.startsWith("--") || line.length() == 0 || line.startsWith("\\"))
93  						{
94  							continue;
95  						} 
96  						Query createIndexQuery = session.createSQLQuery(line);
97  						try {
98  						    int nbupdate = createIndexQuery.executeUpdate();
99  						    logger.info("execution of line : "+line+" modify "+nbupdate+" lines");
100 						} catch (Exception e) {
101 							String msg = "Error on line "+count+" ("+line +") :" +e.getCause();
102 							logger.error(msg,e);
103 							exceptionMessageList.add(msg);
104 							if (!continueOnError){
105 							    throw new PersistenceException(e.getCause());
106 							}
107 						} 
108 					}
109 				} catch (IOException e) {
110 					logger.error("error on line "+count+" : "+e,e);
111 				} 
112 				return exceptionMessageList;
113 			}
114 		});
115 	}
116 
117 	/* (non-Javadoc)
118 	 * @see com.gisgraphy.domain.repository.IDatabaseHelper#generateSqlCreationSchemaFile(java.io.File)
119 	 */
120 	public void generateSqlCreationSchemaFile(File outputFile){
121 	    logger.info("Will generate file to create tables");
122 	   createSqlSchemaFile(outputFile,true,false,false);
123 	}
124 	
125 	/* (non-Javadoc)
126 	 * @see com.gisgraphy.domain.repository.IDatabaseHelper#generateSqlDropSchemaFile(java.io.File)
127 	 */
128 	public void generateSQLDropSchemaFile(File outputFile){
129 	    logger.info("Will generate file to drop tables");
130 	    createSqlSchemaFile(outputFile,false,true,false);
131 	}
132 	
133 	
134 
135 	private List<SQLException> createSqlSchemaFile(File outputFile,boolean create, boolean drop, boolean execute ){
136 	Assert.notNull(outputFile,"Can not create a sql schema in a null file, please specify a valid one");
137 	AnnotationConfiguration config = new AnnotationConfiguration();
138 	config.setProperty("hibernate.dialect",PostgisDialectNG.class.getName());
139 		config.configure();
140 		SchemaExport schemaExporter =null;
141 		if (execute == true){
142 		java.sql.Connection connection = getSession().connection();
143 		schemaExporter = new SchemaExport(config,connection);
144 		} else {
145 		   schemaExporter = new SchemaExport(config);
146 		}
147 		if (outputFile != null){
148 		    schemaExporter.setOutputFile(outputFile.getAbsolutePath());
149 		}
150 		logger.info("will create the Database schema");
151 		if (create == true){
152 		    schemaExporter.create(true, true);
153 		}else if (drop == true){
154 		    schemaExporter.drop(true, true);
155 		}
156 		schemaExporter.execute(true, execute, drop, create);
157 		return schemaExporter.getExceptions();
158 	}
159 
160 	
161 
162 	public void generateSQLCreationSchemaFileToRerunImport(File outputFile) {
163 	    logger.info("Will generate file to create tables to reset import");
164 	    File tempDir = FileHelper.createTempDir(this.getClass().getSimpleName());
165 	    File fileToBeFiltered = new File(tempDir.getAbsolutePath() + System.getProperty("file.separator") + "createAllTables.sql");
166 	    generateSqlCreationSchemaFile(fileToBeFiltered);
167 	    FileLineFilter filter = new FileLineFilter(DatabaseHelper.TABLES_NAME_THAT_MUST_BE_KEPT_WHEN_RESETING_IMPORT);
168 	    filter.filter(fileToBeFiltered, outputFile);
169 	    fileToBeFiltered.delete();
170 	    tempDir.delete();
171 	}
172 
173 	public void generateSqlDropSchemaFileToRerunImport(File outputFile) {
174 	    logger.info("Will generate file to drop tables to reset import");
175 	    File tempDir = FileHelper.createTempDir(this.getClass().getSimpleName());
176 	    File fileToBeFiltered = new File(tempDir.getAbsolutePath() + System.getProperty("file.separator") + "dropAllTables.sql");
177 	    generateSQLDropSchemaFile(fileToBeFiltered);
178 	    FileLineFilter filter = new FileLineFilter(DatabaseHelper.TABLES_NAME_THAT_MUST_BE_KEPT_WHEN_RESETING_IMPORT);
179 	    filter.filter(fileToBeFiltered, outputFile);
180 	    fileToBeFiltered.delete();
181 	    tempDir.delete();
182 	}
183 
184 	/* (non-Javadoc)
185 	 * @see com.gisgraphy.domain.repository.IDatabaseHelper#createNormalize_textFunction()
186 	 */
187 	public void createNormalize_textFunction() {
188 	    logger.info("will create "+DatabaseHelper.NORMALIZE_TEXT_FUNCTION_NAME+" function");
189 	     this.getHibernateTemplate().execute(
190 			new HibernateCallback() {
191 
192 			    public Object doInHibernate(Session session)
193 				    throws PersistenceException {
194 				
195 				Query qry = session.createSQLQuery(NORMALIZE_TEXT_POSTRES_FUNCTION_BODY);
196 				qry.executeUpdate();
197 				return  null;
198 			    }
199 			});
200 	     logger.info(DatabaseHelper.NORMALIZE_TEXT_FUNCTION_NAME+" function has been created");
201 	    
202 	}
203 	
204 	/* (non-Javadoc)
205 	 * @see com.gisgraphy.domain.repository.IDatabaseHelper#isNormalize_textFunctionCreated()
206 	 */
207 	public boolean isNormalize_textFunctionCreated() {
208 	    try {
209 		this.getHibernateTemplate().execute(
210 			new HibernateCallback() {
211 
212 			    public Object doInHibernate(Session session)
213 				    throws PersistenceException {
214 				
215 				Query qry = session.createSQLQuery("select "+DatabaseHelper.NORMALIZE_TEXT_FUNCTION_NAME+"('é-è.ê''à\"ù')");
216 				Object result = qry.uniqueResult();
217 				if ("e e e a u"!= result){
218 				    logger.info(DatabaseHelper.NORMALIZE_TEXT_FUNCTION_NAME+" function does not return the expected value : we consider that the function is not created");
219 				    return false;
220 				}
221 				return true;
222 			    }
223 			});
224 	    } catch (Exception e) {
225 		  logger.info(DatabaseHelper.NORMALIZE_TEXT_FUNCTION_NAME+" function has generate an exception : we consider that the function is not created : "+e);
226 		  return false;
227 	    }
228 	     logger.info(DatabaseHelper.NORMALIZE_TEXT_FUNCTION_NAME+" function has been successfully called : we consider that the function is created");
229 	     return true;
230 	    
231 	}
232 	
233 	
234 	/* (non-Javadoc)
235 	 * @see com.gisgraphy.domain.repository.IDatabaseHelper#dropNormalize_textFunction()
236 	 */
237 	public void dropNormalize_textFunction(){
238 	    logger.info("will drop "+DatabaseHelper.NORMALIZE_TEXT_FUNCTION_NAME+" function");
239 	     this.getHibernateTemplate().execute(
240 			new HibernateCallback() {
241 
242 			    public Object doInHibernate(Session session)
243 				    throws PersistenceException {
244 				
245 				Query qry = session.createSQLQuery("DROP FUNCTION IF EXISTS "+DatabaseHelper.NORMALIZE_TEXT_FUNCTION_NAME+"normalize_text(text)");
246 				qry.executeUpdate();
247 				return  null;
248 			    }
249 			});
250 	     logger.info(DatabaseHelper.NORMALIZE_TEXT_FUNCTION_NAME+" function has been drop");
251 	}
252 	
253 	
254 }