1   /*
2    * Copyright 2002-2006 the original author or authors.
3    * 
4    * Licensed under the Apache License, Version 2.0 (the "License"); you may not
5    * use this file except in compliance with the License. You may obtain a copy of
6    * the License at
7    * 
8    * http://www.apache.org/licenses/LICENSE-2.0
9    * 
10   * Unless required by applicable law or agreed to in writing, software
11   * distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
12   * WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
13   * License for the specific language governing permissions and limitations under
14   * the License.
15   */
16  package org.springframework.richclient.settings.jdbc;
17  
18  import java.util.List;
19  import java.util.Map;
20  
21  import javax.sql.DataSource;
22  
23  import org.springframework.jdbc.core.JdbcTemplate;
24  import org.springframework.jdbc.datasource.DriverManagerDataSource;
25  import org.springframework.richclient.settings.Settings;
26  import org.springframework.richclient.settings.SettingsAbstractTests;
27  
28  /**
29   * @author Peter De Bruycker
30   */
31  public class JdbcSettingsTests extends SettingsAbstractTests {
32      private DataSource dataSource;
33      private JdbcTemplate jdbcTemplate;
34  
35      public JdbcSettingsTests() {
36      }
37  
38      protected Settings createSettings() {
39          return new JdbcSettings(dataSource, "user", Integer.valueOf(5), "test");
40      }
41  
42      protected void doSetUp() throws Exception {
43          dataSource = createDataSource();
44          jdbcTemplate = new JdbcTemplate(dataSource);
45  
46          // setup the schema
47          jdbcTemplate.execute("DROP TABLE SETTINGS_VALUES IF EXISTS");
48          jdbcTemplate.execute("DROP TABLE SETTINGS IF EXISTS");
49  
50          jdbcTemplate.execute("CREATE TABLE SETTINGS (ID INTEGER IDENTITY, KEY VARCHAR(250) NOT NULL, PARENT INTEGER, USER VARCHAR(250) NOT NULL, CONSTRAINT SYS_CT_52 UNIQUE(KEY,USER))");
51          jdbcTemplate.execute("CREATE TABLE SETTINGS_VALUES (SETTINGS_ID INTEGER NOT NULL, KEY VARCHAR(250) NOT NULL, VALUE VARCHAR(250), PRIMARY KEY(SETTINGS_ID,KEY), CONSTRAINT SYS_FK_48 FOREIGN KEY(SETTINGS_ID) REFERENCES SETTINGS(ID))");
52      }
53  
54      /**
55       * Creates a <code>DataSource</code> using hsqldb in memory-only mode
56       * @return the <code>DataSource</code>
57       */
58      private static DataSource createDataSource() {
59          DriverManagerDataSource ds = new DriverManagerDataSource();
60          ds.setDriverClassName("org.hsqldb.jdbcDriver");
61          ds.setUrl("jdbc:hsqldb:mem:test-database");
62          ds.setUsername("sa");
63  
64          return ds;
65      }
66  
67      public void testLoadExistingSettings() throws Exception {
68          jdbcTemplate.execute("INSERT INTO SETTINGS (ID, KEY, USER) VALUES (55, 'test-key', 'test-user')");
69          jdbcTemplate.execute("INSERT INTO SETTINGS_VALUES (SETTINGS_ID, KEY, VALUE) VALUES (55, 'key0', 'true')");
70          jdbcTemplate.execute("INSERT INTO SETTINGS_VALUES (SETTINGS_ID, KEY, VALUE) VALUES (55, 'key1', '25')");
71  
72          JdbcSettings settings = new JdbcSettings(dataSource, "test-user", Integer.valueOf(55), "test-key");
73          settings.load();
74      }
75  
76      public void testLoadHierarchy() throws Exception {
77  
78      }
79  
80      public void testSaveHierarchy() throws Exception {
81          JdbcSettings settings = new JdbcSettings(dataSource, "test-user", null, "test-key");
82          settings.setBoolean("boolean-value", true);
83  
84          JdbcSettings childSettings = (JdbcSettings)settings.getSettings("child");
85          childSettings.setString("string", "test");
86          childSettings.save();
87          
88          assertEquals(Integer.valueOf(0), settings.getId());
89          assertEquals(Integer.valueOf(1), childSettings.getId());
90      }
91  
92      public void testSaveNewSettings() throws Exception {
93          JdbcSettings settings = new JdbcSettings(dataSource, "test-user", null, "test-key");
94  
95          assertEquals("name not set", "test-key", settings.getName());
96          assertEquals("user not set", "test-user", settings.getUser());
97          assertNull("id must be null until first save", settings.getId());
98  
99          settings.setBoolean("boolean-value", true);
100         settings.setString("string-value", "value");
101 
102         settings.save();
103 
104         assertEquals(Integer.valueOf(0), settings.getId());
105 
106         assertEquals(1, jdbcTemplate.queryForInt("SELECT count(*) FROM SETTINGS"));
107         Map map = jdbcTemplate.queryForMap("SELECT * FROM SETTINGS WHERE ID = 0");
108         assertEquals(Integer.valueOf(0), map.get("ID"));
109         assertEquals("test-key", map.get("KEY"));
110         assertEquals(null, map.get("PARENT"));
111         assertEquals("test-user", map.get("USER"));
112 
113         assertEquals(2, jdbcTemplate.queryForInt("SELECT count(*) FROM SETTINGS_VALUES"));
114         List values = jdbcTemplate.queryForList("SELECT * FROM SETTINGS_VALUES");
115         assertEquals(2, values.size());
116         Map first = (Map) values.get(0);
117         Map second = (Map) values.get(1);
118 
119         assertEquals(Integer.valueOf(0), first.get("SETTINGS_ID"));
120         assertEquals(Integer.valueOf(0), second.get("SETTINGS_ID"));
121 
122         assertEquals("boolean-value", first.get("KEY"));
123         assertEquals("true", first.get("VALUE"));
124 
125         assertEquals("string-value", second.get("KEY"));
126         assertEquals("value", second.get("VALUE"));
127     }
128 }