1   package com.atlassian.user.util.migration;
2   
3   import com.atlassian.user.User;
4   import com.atlassian.user.impl.DefaultUser;
5   import org.springframework.jdbc.core.RowCallbackHandler;
6   import org.springframework.jdbc.core.support.JdbcDaoSupport;
7   
8   import javax.sql.DataSource;
9   import java.sql.ResultSet;
10  import java.sql.SQLException;
11  import java.util.HashMap;
12  import java.util.Map;
13  import java.util.List;
14  import java.util.LinkedList;
15  
16  public class OSUserDao extends JdbcDaoSupport
17  {
18      private static final String USERNAME_COL = "username";
19      private static final String ID_COL = "id";
20      private static final String PASSWORD_COL = "passwd";
21      private static final String KEY_COL = "entity_key";
22      private static final String VALUE_COL = "string_val";
23  
24      private static final String OSUSER_TABLE = "os_user";
25      private static final String OSGROUP_TABLE = "os_group";
26      private static final String OS_USER_GROUP_TABLE = "os_user_group";
27      private static final String OSPROPERTYSET_TABLE = "OS_PROPERTYENTRY";
28      private static final String GROUP_NAME_CAL = "groupname";
29      private static final String GROUP_ID_COL = "group_id";
30      private static final String USER_ID_COL = "user_id";
31  
32      OSUserDao(DataSource dataSource)
33      {
34          setDataSource(dataSource);
35      }
36  
37      /**
38       * Gets all users populated with their 'basic' properties from the OSUSER_TABLE and the OSPROPERTYSET_TABLE
39       *
40       * @return a collection of {@link com.atlassian.user.impl.DefaultUser}
41       */
42      Map<Long, DefaultUser> findAllUsers()
43      {
44          final Map<Long, DefaultUser> users = new HashMap<Long, DefaultUser>();
45  
46          // get username and password from OSUSER_TABLE
47          getJdbcTemplate().query("select * from " + OSUSER_TABLE, new RowCallbackHandler()
48          {
49              public void processRow(ResultSet rs) throws SQLException
50              {
51                  final Long id = rs.getLong(ID_COL);
52  
53                  final DefaultUser user = new DefaultUser(rs.getString(USERNAME_COL));
54                  user.setPassword(rs.getString(PASSWORD_COL));
55  
56                  users.put(id, user);
57              }
58          });
59  
60          // get fullname and email from OSPROPERTYSET_TABLE
61          getJdbcTemplate().query("SELECT * FROM " + OSPROPERTYSET_TABLE + " WHERE entity_name='OSUser_user' " +
62              "AND ( entity_key='fullName' OR entity_key='email')", new RowCallbackHandler()
63          {
64              public void processRow(ResultSet resultSet) throws SQLException
65              {
66                  final Long id = resultSet.getLong("entity_id");
67                  final User user = users.get(id);
68                  if (user != null)
69                  {
70                      final String key = resultSet.getString(KEY_COL);
71                      final String value = resultSet.getString(VALUE_COL);
72                      if ("fullName".equals(key))
73                      {
74                          user.setFullName(value);
75                      }
76                      else if ("email".equals(key))
77                      {
78                          user.setEmail(value);
79                      }
80                  }
81              }
82          });
83  
84          return users;
85      }
86  
87      /**
88       * Builds a map that contains the mapping between a username and a list of the group names this user
89       * belongs to.
90       */
91      public Map<String, List<String>> findAllUserGroups(final Map users)
92      {
93          final Map<String, List<String>> userGroups = new HashMap<String, List<String>>();
94          final Map<Long, String> groups = new HashMap<Long, String>();
95  
96          // get group names
97          getJdbcTemplate().query("select * from " + OSGROUP_TABLE, new RowCallbackHandler()
98          {
99              public void processRow(ResultSet rs) throws SQLException
100             {
101                 final Long groupId = rs.getLong(ID_COL);
102                 groups.put(groupId, rs.getString(GROUP_NAME_CAL));
103             }
104         });
105 
106         getJdbcTemplate().query("select * from " + OS_USER_GROUP_TABLE, new RowCallbackHandler()
107         {
108             public void processRow(ResultSet rs) throws SQLException
109             {
110                 final Long groupId = rs.getLong(GROUP_ID_COL);
111                 final Long userId = rs.getLong(USER_ID_COL);
112                 String userName = ((User) users.get(userId)).getName();
113                 if (userName != null){
114                     List<String> gr = userGroups.get(userName);
115                     if (gr == null){
116                         gr = new LinkedList<String>();
117                         userGroups.put(userName, gr);
118                     }
119 
120                     String groupName = groups.get(groupId);
121                     if (groupName != null){
122                         gr.add(groupName);
123                     }
124 
125                 }
126 
127             }
128         });
129 
130         return userGroups;
131     }
132 }