1 /**************************************************************
2  *
3  * Licensed to the Apache Software Foundation (ASF) under one
4  * or more contributor license agreements.  See the NOTICE file
5  * distributed with this work for additional information
6  * regarding copyright ownership.  The ASF licenses this file
7  * to you under the Apache License, Version 2.0 (the
8  * "License"); you may not use this file except in compliance
9  * with the License.  You may obtain a copy of the License at
10  *
11  *   http://www.apache.org/licenses/LICENSE-2.0
12  *
13  * Unless required by applicable law or agreed to in writing,
14  * software distributed under the License is distributed on an
15  * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
16  * KIND, either express or implied.  See the License for the
17  * specific language governing permissions and limitations
18  * under the License.
19  *
20  *************************************************************/
21 
22 
23 package connectivity.tools;
24 
25 import com.sun.star.beans.PropertyValue;
26 import com.sun.star.beans.PropertyState;
27 import com.sun.star.beans.XPropertySet;
28 import com.sun.star.container.ElementExistException;
29 import com.sun.star.frame.XStorable;
30 import com.sun.star.lang.XMultiServiceFactory;
31 import com.sun.star.sdb.XOfficeDatabaseDocument;
32 import com.sun.star.sdbc.SQLException;
33 import com.sun.star.sdbcx.XAppend;
34 import com.sun.star.sdbcx.XTablesSupplier;
35 import com.sun.star.uno.UnoRuntime;
36 
37 import helper.URLHelper;
38 import java.util.HashMap;
39 import java.util.Iterator;
40 import java.util.Set;
41 import java.io.File;
42 
43 /**
44  *
45  * @author fs93730
46  */
47 public class HsqlDatabase extends AbstractDatabase
48 {
49 
50     // --------------------------------------------------------------------------------------------------------
HsqlDatabase(final XMultiServiceFactory orb)51     public HsqlDatabase(final XMultiServiceFactory orb) throws Exception
52     {
53         super(orb);
54         createDBDocument();
55     }
56 
57     // --------------------------------------------------------------------------------------------------------
HsqlDatabase(final XMultiServiceFactory orb, final String _existingDocumentURL)58     public HsqlDatabase(final XMultiServiceFactory orb, final String _existingDocumentURL) throws Exception
59     {
60         super(orb, _existingDocumentURL);
61     }
62 
63     /** creates an empty database document in a temporary location
64      */
createDBDocument()65     private void createDBDocument() throws Exception
66     {
67         final File documentFile = File.createTempFile("testdb", ".odb");
68         if ( documentFile.exists() )
69             documentFile.delete();
70         m_databaseDocumentFile = URLHelper.getFileURLFromSystemPath(documentFile);
71 
72         m_databaseDocument = (XOfficeDatabaseDocument) UnoRuntime.queryInterface(
73                 XOfficeDatabaseDocument.class, m_orb.createInstance("com.sun.star.sdb.OfficeDatabaseDocument"));
74         m_dataSource = new DataSource(m_orb, m_databaseDocument.getDataSource());
75 
76         final XPropertySet dsProperties = (XPropertySet) UnoRuntime.queryInterface(XPropertySet.class, m_databaseDocument.getDataSource());
77         dsProperties.setPropertyValue("URL", "sdbc:embedded:hsqldb");
78 
79         final XStorable storable = (XStorable) UnoRuntime.queryInterface(XStorable.class, m_databaseDocument);
80         storable.storeAsURL( m_databaseDocumentFile, new PropertyValue[]
81 			{	new PropertyValue( "PickListEntry", 0, false, PropertyState.DIRECT_VALUE )
82 			} );
83     }
84 
85     /** drops the table with a given name
86 
87     @param _name
88     the name of the table to drop
89     @param _ifExists
90     TRUE if it should be dropped only when it exists.
91      */
dropTable(final String _name, final boolean _ifExists)92     public void dropTable(final String _name, final boolean _ifExists) throws SQLException
93     {
94         final StringBuffer dropStatement = new StringBuffer("DROP TABLE \"");
95         dropStatement.append(_name);
96         if (_ifExists)
97         {
98             dropStatement.append("\" IF EXISTS");
99         }
100         executeSQL(dropStatement.toString());
101     }
102 
createTable(final HsqlTableDescriptor _tableDesc, final boolean _dropIfExists)103     public void createTable(final HsqlTableDescriptor _tableDesc, final boolean _dropIfExists) throws SQLException
104     {
105         if (_dropIfExists)
106         {
107             dropTable(_tableDesc.getName(), true);
108         }
109         createTable(_tableDesc);
110     }
111 
112     /** creates a table
113      */
createTable(final HsqlTableDescriptor _tableDesc)114     public void createTable(final HsqlTableDescriptor _tableDesc) throws SQLException
115     {
116         StringBuffer createStatement = new StringBuffer("CREATE CACHED TABLE \"");
117         createStatement.append(_tableDesc.getName());
118         createStatement.append("\" ( ");
119 
120         String primaryKeyList = "";
121 
122         final HashMap foreignKeys = new HashMap();
123         final HashMap foreignKeyRefs = new HashMap();
124 
125         final HsqlColumnDescriptor[] columns = _tableDesc.getColumns();
126         for (int i = 0; i < columns.length; ++i)
127         {
128             if (i > 0)
129             {
130                 createStatement.append(", ");
131             }
132 
133             createStatement.append("\"" + columns[i].getName());
134             createStatement.append("\" " + columns[i].getTypeName());
135 
136             if (columns[i].isRequired())
137             {
138                 createStatement.append(" NOT NULL");
139             }
140 
141             if (columns[i].isPrimaryKey())
142             {
143                 if (primaryKeyList.length() > 0)
144                 {
145                     primaryKeyList += ", ";
146                 }
147                 primaryKeyList += "\"" + columns[i].getName() + "\"";
148             }
149 
150             if (columns[i].isForeignKey())
151             {
152                 final String foreignTable = columns[i].getForeignTable();
153 
154                 String foreignKeysForTable = foreignKeys.containsKey(foreignTable) ? (String) foreignKeys.get(foreignTable) : "";
155                 if (foreignKeysForTable.length() > 0)
156                 {
157                     foreignKeysForTable += ", ";
158                 }
159                 foreignKeysForTable += "\"" + columns[i].getName() + "\"";
160                 foreignKeys.put(foreignTable, foreignKeysForTable);
161 
162                 final StringBuffer foreignKeyRefsForTable = new StringBuffer(foreignKeyRefs.containsKey(foreignTable) ? (String) foreignKeyRefs.get(foreignTable) : "");
163                 if (foreignKeyRefsForTable.length() > 0)
164                 {
165                     foreignKeyRefsForTable.append(", ");
166                 }
167                 foreignKeyRefsForTable.append("\"" + columns[i].getForeignColumn() + "\"");
168                 foreignKeyRefs.put(foreignTable, foreignKeyRefsForTable.toString());
169             }
170         }
171 
172         if (primaryKeyList.length() > 0)
173         {
174             createStatement.append(", PRIMARY KEY (");
175             createStatement.append(primaryKeyList);
176             createStatement.append(')');
177         }
178 
179         final Set foreignKeyTables = foreignKeys.keySet();
180         for (final Iterator foreignKey = foreignKeyTables.iterator();
181                 foreignKey.hasNext();)
182         {
183             final String foreignTable = (String) foreignKey.next();
184 
185             createStatement.append(", FOREIGN KEY (");
186             createStatement.append((String) foreignKeys.get(foreignTable));
187             createStatement.append(") REFERENCES \"");
188             createStatement.append(foreignTable);
189             createStatement.append("\"(");
190             createStatement.append((String) foreignKeyRefs.get(foreignTable));
191             createStatement.append(')');
192         }
193 
194         createStatement.append(')');
195 
196         //System.err.println( createStatement );
197         executeSQL(createStatement.toString());
198     }
199 
200     /** creates a table in the database. using the SDBCX-API
201      */
createTableInSDBCX(final HsqlTableDescriptor _tableDesc)202     public void createTableInSDBCX(final HsqlTableDescriptor _tableDesc) throws SQLException, ElementExistException
203     {
204         final XPropertySet sdbcxDescriptor = _tableDesc.createSdbcxDescriptor(defaultConnection());
205         final XTablesSupplier suppTables = UnoRuntime.queryInterface( XTablesSupplier.class, defaultConnection().getXConnection() );
206         final XAppend appendTable = UnoRuntime.queryInterface( XAppend.class, suppTables.getTables() );
207         appendTable.appendByDescriptor(sdbcxDescriptor);
208     }
209 }
210