Projekt

Obecné

Profil

« Předchozí | Další » 

Revize d4e61447

Přidáno uživatelem Tomáš Šimandl před více než 6 roky(ů)

Changed diagram table and Diagram class

Zobrazit rozdíly:

sources/create_table.sql
28 28

  
29 29
CREATE TABLE IF NOT EXISTS `diagram` (
30 30
  `id` int(11) NOT NULL,
31
  `session_id` varchar(32) COLLATE utf8_czech_ci NOT NULL,
32 31
  `name` varchar(255) COLLATE utf8_czech_ci NOT NULL,
33
  `component_count` int(11) NOT NULL DEFAULT '0',
34 32
  `created` datetime NOT NULL,
33
  `last_update` datetime NOT NULL,
35 34
  `user_id` int(11) NOT NULL,
36
  `vertices_position` text COLLATE utf8_czech_ci NOT NULL,
37
  `hash` varchar(32) COLLATE utf8_czech_ci NOT NULL DEFAULT '',
38 35
  `public` tinyint(1) NOT NULL DEFAULT '0'
36
  `graph_json` text COLLATE utf8_czech_ci NOT NULL DEFAULT '',
37
--   `session_id` varchar(32) COLLATE utf8_czech_ci NOT NULL,
38
--   `component_count` int(11) NOT NULL DEFAULT '0',
39
--   `vertices_position` text COLLATE utf8_czech_ci NOT NULL,
40
--   `hash` varchar(32) COLLATE utf8_czech_ci NOT NULL DEFAULT '',
39 41
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;
40 42

  
41 43
-- --------------------------------------------------------
sources/src/main/java/cz/zcu/kiv/offscreen/servlets/actions/DeleteComponent.java
48 48

  
49 49
            DB db = new DB(getServletContext());
50 50
            Diagram diagram = new Diagram(db, diagramId);
51
            Map<String, String> diag_param = diagram.getDiagramParam(diagramId);
51
            Map<String, String> diag_param = diagram.getDiagram();
52 52

  
53 53
            if (request.getSession().getAttribute("logged_user_id") == null) {
54 54
        		doDeleteComponent = false;
sources/src/main/java/cz/zcu/kiv/offscreen/servlets/actions/DiagramEndpoint.java
30 30
        Integer diagramId = Integer.parseInt(request.getParameter("diagram_id"));
31 31

  
32 32
        DB db = new DB(getServletContext());
33
        Diagram diagram = new Diagram(db);
34
        Map<String, String> diag_param = diagram.getDiagramParam(diagramId);
33
        Diagram diagram = new Diagram(db, diagramId);
34
        Map<String, String> diag_param = diagram.getDiagram();
35 35

  
36 36
        // user is not owner of the diagram
37 37
        if (!request.getSession().getAttribute("logged_user_id").toString().equals(diag_param.get("user_id"))) {
......
68 68

  
69 69
        DB db = new DB(getServletContext());
70 70
        Diagram diagram = new Diagram(db, diagramId);
71
        Map<String, String> diag_param = diagram.getDiagramParam(diagramId);
71
        Map<String, String> diag_param = diagram.getDiagram();
72 72

  
73 73
        // user is not owner of the diagram
74 74
        if (!request.getSession().getAttribute("logged_user_id").toString().equals(diag_param.get("user_id"))) {
......
77 77
        }
78 78

  
79 79
        String verticesPosition = request.getParameter("vertices_position");
80
        diagram.updateVerticesPosition(verticesPosition);
80
        //diagram.updateVerticesPosition(verticesPosition); REMOVED FROM DATABASE
81 81

  
82 82
        // send response
83 83
        response.setContentType("application/json");
......
110 110

  
111 111
        DB db = new DB(getServletContext());
112 112
        Diagram diagram = new Diagram(db, diagramId);
113
        Map<String, String> diag_param = diagram.getDiagramParam(diagramId);
113
        Map<String, String> diag_param = diagram.getDiagram();
114 114

  
115 115
        // user is not owner of the diagram
116 116
        if (!request.getSession().getAttribute("logged_user_id").toString().equals(diag_param.get("user_id"))) {
sources/src/main/java/cz/zcu/kiv/offscreen/servlets/actions/SaveLog.java
82 82

  
83 83
    private boolean checkUserId(int diagramID, int userId, DB db) {
84 84

  
85
        Diagram diag = new Diagram(db);
86
        HashMap<String,String> param = (HashMap<String, String>) diag.getDiagramParam(diagramID);
85
        Diagram diag = new Diagram(db, diagramID);
86
        HashMap<String,String> param = (HashMap<String, String>) diag.getDiagram();
87 87
        String idStr = param.get("user_id");
88 88
        int id = Integer.parseInt(idStr);
89 89
        if (userId==0){
sources/src/main/java/cz/zcu/kiv/offscreen/user/Diagram.java
1 1
package cz.zcu.kiv.offscreen.user;
2 2

  
3
import java.sql.PreparedStatement;
3 4
import java.sql.ResultSet;
4 5
import java.sql.SQLException;
5
import java.sql.Statement;
6
import java.text.SimpleDateFormat;
7
import java.util.ArrayList;
8
import java.util.Date;
9
import java.util.HashMap;
10
import java.util.LinkedHashMap;
11
import java.util.Map;
12

  
13
import javax.servlet.ServletContext;
6
import java.util.*;
14 7

  
15 8
/**
16
 * Class is usefull for saving and loading diagram params from database.
17
 * 
18
 * @author Daniel Bureš
9
 * Class is used for saving and loading diagram params from database.
19 10
 *
11
 * @author Daniel Bureš
12
 * @author Tomáš Šimandl
20 13
 */
21 14
public class Diagram {
22
	private DB db = null;
23
	private int id = 0;
24
	private ResultSet rs;
25
	 
26
	
27
	public Diagram(DB db) {			
28
		this(db,0);
29
	}
30
	
31
	
32
	public Diagram(DB db,int id) {
33
		this.db = db;	
34
		this.id = id;
35
	}
36
	
37
	/**
38
	 * Method returns object id.
39
	 * 
40
	 * @return id
41
	 */
42
	public int getId(){
43
		return this.id;
44
	}
45
	
46
	/**
47
	 * Method returns id of object as string.
48
	 * 
49
	 * @return
50
	 */
51
	public String getIdStr(){
52
		return Integer.toString(this.id);		
53
	}
54
	
55
	/**
56
	 * Method saves new diagram into database.
57
	 * 
58
	 * @param param - diagram parameters
59
	 */
60
	public void update(Map<String, String> param){
61
		try {
62
			if ( this.id  == 0 ) {
63
				  
64
				    String diagram_name = param.get("diagram_name")  ;
65
				    String hash = param.get("hash")  ;  
66
				    String component_count = param.get("component_count")  ;
67
				    String user_id = param.get("user_id")  ;
68
				    String public_diag = param.get("public")  ;
69
				
70
					String qy = "INSERT INTO diagram (id, created, user_id,component_count, name, session_id, hash,public ) " +
71
										"VALUES ('0', NOW(), '"+user_id+"', '"+component_count+"', '"+diagram_name+"', '', '" + hash + "' , '" + public_diag + "' ) ";
72
					Statement st = db.getConn().createStatement();
73
					st.executeUpdate(qy, Statement.RETURN_GENERATED_KEYS);
74
					ResultSet rs = st.getGeneratedKeys();
75
					
76
					if(rs.next()){							
77
						this.id = rs.getInt(1);						
78
					}					
79
					
80
				
81
			}else{
82
				    String diagram_name = param.get("diagram_name")  ;
83
				    String component_count = param.get("component_count")  ;
84
				    String public_diag = param.get("public")  ;  
85
				
86
					String qy = "UPDATE diagram SET component_count = '" + component_count + "', name = '"+diagram_name+"', public = '"+public_diag+"' " + 
87
										" WHERE id= '" + this.id + "'";
88
					Statement st = db.getConn().createStatement();
89
					st.executeUpdate(qy, Statement.RETURN_GENERATED_KEYS);
90
					ResultSet rs = st.getGeneratedKeys();
91

  
92
			}
93
					  
94
			
95
		
96
		} catch (SQLException e) {
97
			e.printStackTrace();
98
		}		
99
	}
100
	
101
	/**
102
	 * Method deletes diagram from database.
103
	 */
104
	public void delete(){
105
		String qy = "DELETE FROM diagram WHERE id = '"+ this.id+"' LIMIT 1";
106
		
107
		db.exStatement(qy);
108
						
109
		
110
	}
111
	
112
	/*
113
	public void updateViewport(String viewport_html){
114
		String qy = "UPDATE diagram SET viewport_html = '"+ viewport_html+"' WHERE id = '"+ this.id+"' LIMIT 1";
115
		try {
116
			db.exStatement(qy);
117
						
118
		} catch (SQLException e) {
119
			
120
			e.printStackTrace();
121
		}
122
	}
123

  
124
	public void updateRightpanel(String rightpanel_html){
125
		String qy = "UPDATE diagram SET rightpanel_html = '"+ rightpanel_html+"' WHERE id = '"+ this.id+"' LIMIT 1";
126
		try {
127
			db.exStatement(qy);
128
						
129
		} catch (SQLException e) {
130
			
131
			e.printStackTrace();
132
		}
133
	}*/
134
	
135
	/**
136
	 * Method updates vertices position in database. Position of vertices are saved in json.
137
	 * 
138
	 * @param vertices_position_json
139
	 */
140
	public void updateVerticesPosition(String vertices_position_json){
141
		String qy = "UPDATE diagram SET vertices_position = '"+ vertices_position_json+"' WHERE id = '"+ this.id+"' LIMIT 1";
142
		
143
			db.exStatement(qy);
144
						
145
		
146
	}
147
	
148
	/**
149
	 * Method returns parameters of diagram.
150
	 * @param diagram_id
151
	 * @return
152
	 */
153
	public Map<String,String> getDiagramParam(int diagram_id){
154
		HashMap<String, String> item_map = new HashMap<String, String>();
155
		String qy = "SELECT * FROM diagram WHERE id = '"+diagram_id+"' ORDER BY created DESC ";
156
		try {
157
			rs = db.exQuery(qy);			
158
			while ( rs != null && rs.next() ) {
159
				
160
				
161
				item_map.put("id", String.valueOf(rs.getInt("id")) );
162
				item_map.put("name", rs.getString("name")  );
163
				item_map.put("hash", rs.getString("hash")  );
164
				item_map.put("public", String.valueOf(rs.getInt("public")) );
165
				item_map.put("user_id", String.valueOf(rs.getInt("user_id")) );
166
				item_map.put("vertices_position", rs.getString("vertices_position")  );
167
				item_map.put("component_count", String.valueOf(rs.getInt("component_count")) );
168
				item_map.put("created", Util.formatDate(rs.getString("created") ) );
169
				item_map.put("session_id", rs.getString("session_id") );
170
				
171
				
172
			}
173
			
174
		} catch (SQLException e) {
175
			
176
			e.printStackTrace();
177
		}
178
		
179
		return item_map;
180
		
181
	}
182
	
183
	/**
184
	 * Method returns list of digrams, which are uploaded by user.
185
	 * 
186
	 * @param user_id
187
	 * @return
188
	 */
189
	public ArrayList<Map<String,String>> getDiagramListByUserId(int user_id){		
190
		ArrayList<Map<String,String>> diagram_list = new ArrayList<Map<String,String>>();
191
		String qy = "SELECT * FROM diagram WHERE user_id = '"+user_id+"' ORDER BY created DESC ";
192
		try {
193
			rs = db.exQuery(qy);			
194
			while ( rs != null && rs.next() ) {
195
				HashMap<String, String> item_map = new HashMap<String, String>();
196
				
197
				item_map.put("id", String.valueOf(rs.getInt("id")) );
198
				item_map.put("name", (rs.getString("name").length()==0?"No name":rs.getString("name"))  );
199
				item_map.put("public", String.valueOf(rs.getInt("public")) );
200
				item_map.put("component_count", String.valueOf(rs.getInt("component_count")) );
201
				item_map.put("hash", rs.getString("hash")  );
202
				item_map.put("created", Util.formatDate(rs.getString("created") ) );
203
				item_map.put("session_id", rs.getString("session_id") );
204
				
205
				diagram_list.add(item_map);
206
			}
207
			
208
		} catch (SQLException e) {
209
			
210
			e.printStackTrace();
211
		}
212
		
213
		return diagram_list;
214
		
215
	}
216
	
217
	
218
	/**
219
	 * Method returns list of public diagrams.
220
	 * @return
221
	 */
222
	public ArrayList<Map<String,String>> getDiagramPublicList(){
223
		
224
		ArrayList<Map<String,String>> diagram_list = new ArrayList<Map<String,String>>();
225
		String qy = "SELECT * FROM diagram WHERE public = 1 ORDER BY name ASC ";
226
		try {
227
			rs = db.exQuery(qy);			
228
			while ( rs != null && rs.next() ) {
229
				HashMap<String, String> item_map = new HashMap<String, String>();
230
				
231
				item_map.put("id", String.valueOf(rs.getInt("id")) );
232
				item_map.put("name", (rs.getString("name").length()==0?"No name":rs.getString("name"))  );
233
				item_map.put("public", String.valueOf(rs.getInt("public")) );
234
				item_map.put("component_count", String.valueOf(rs.getInt("component_count")) );
235
				item_map.put("hash", rs.getString("hash")  );
236
				item_map.put("created", Util.formatDate(rs.getString("created") ) );
237
				item_map.put("session_id", rs.getString("session_id") );
238
				
239
				diagram_list.add(item_map);
240
			}
241
			
242
		} catch (SQLException e) {
243
			
244
			e.printStackTrace();
245
		}
246
		
247
		return diagram_list;
248
		
249
	}
250
	
15
    private DB db = null;
16
    private int id = -1;
17

  
18

  
19
    public Diagram(DB db) {
20
        this(db, -1);
21
    }
22

  
23
    public Diagram(DB db, int id) {
24
        this.db = db;
25
        this.id = id;
26
    }
27

  
28
    /**
29
     * Method returns object id.
30
     *
31
     * @return id
32
     */
33
    public int getId() {
34
        return this.id;
35
    }
36

  
37
    public int getUserId(){
38
        if(this.id < 0) return -1;
39

  
40
        String qy = "SELECT user_id FROM diagram WHERE id = '" + this.id + "'";
41
        ResultSet rs = db.exQuery(qy);
42

  
43
        try{
44
            if (rs != null && rs.next()) {
45
                return rs.getInt("user_id");
46
            }
47
        }  catch (SQLException e){
48
            e.printStackTrace();
49
        }
50
        return -1;
51
    }
52

  
53
    /**
54
     * Method return map of values of actual diagram or empty map if id of diagram is invalid.
55
     *
56
     * @return created map.
57
     */
58
    public Map<String, String> getDiagram() {
59
        if (this.id < 0) return Collections.emptyMap();
60

  
61
        String qy = "SELECT * FROM diagram WHERE id = '" + this.id + "'";
62

  
63
        try {
64

  
65
            ResultSet rs = db.exQuery(qy);
66
            if (rs != null && rs.next()) {
67
                return createMap(rs);
68
            }
69

  
70
        } catch (SQLException e) {
71
            e.printStackTrace();
72
        }
73

  
74
        return Collections.emptyMap();
75
    }
76

  
77
    /**
78
     * Method saves new diagram into database or update existing diagram.
79
     *
80
     * @param param - diagram parameters
81
     */
82
    public void update(Map<String, String> param) {
83
        try {
84
            if (this.id < 0) {
85
                // crating new diagram
86

  
87
                String name = param.get("name");
88
                String userId = param.get("user_id");
89
                String isPublic = param.get("public");
90
                String graphJson = param.get("graph_json");
91

  
92

  
93
                String qy = "INSERT INTO diagram (name, created, last_update, user_id, public, graph_json ) " +
94
                        "VALUES (?, NOW(), NOW(), ?, ?, ?) ";
95

  
96
                PreparedStatement pst = db.getConn().prepareStatement(qy);
97
                pst.setString(1, name);
98
                pst.setString(2, userId);
99
                pst.setString(3, isPublic);
100
                pst.setString(4, graphJson);
101
                pst.executeUpdate();
102

  
103
                ResultSet rs = pst.getGeneratedKeys();
104

  
105
                if (rs.next()) {
106
                    this.id = rs.getInt(1);
107
                }
108
            } else {
109
                String name = param.get("name");
110
                String isPublic = param.get("public");
111
                String graphJson = param.get("graph_json");
112

  
113
                String qy = "UPDATE diagram SET name = ?, public = ?, graph_json = ?, last_update = NOW() WHERE id= ?";
114

  
115
                PreparedStatement pst = db.getConn().prepareStatement(qy);
116
                pst.setString(1, name);
117
                pst.setString(2, isPublic);
118
                pst.setString(3, graphJson);
119
                pst.setInt(4, this.id);
120
                pst.executeUpdate();
121
            }
122
        } catch (SQLException e) {
123
            e.printStackTrace();
124
        }
125
    }
126

  
127
    /**
128
     * Method deletes diagram from database.
129
     */
130
    public void delete() {
131
        if (id < 0) return;
132

  
133
        String qy = "DELETE FROM diagram WHERE id = '" + this.id + "' LIMIT 1";
134
        db.exStatement(qy);
135
    }
136

  
137
    /**
138
     * Method change graph_json in diagram and return number of affected rows in database.
139
     *
140
     * @param graphJson json of diagram
141
     * @return number of affected rows in database
142
     */
143
    public int updateGraphJson(String graphJson){
144
        if(this.id < 0) return 0;
145

  
146
        String qy = "UPDATE diagram SET graph_json = ? WHERE id = ?";
147

  
148
        try {
149
            PreparedStatement pst = db.getConn().prepareStatement(qy);
150
            pst.setString(1, graphJson);
151
            pst.setInt(2, this.id);
152
            pst.executeUpdate();
153
            return pst.getUpdateCount();
154

  
155
        } catch (SQLException e) {
156
            e.printStackTrace();
157
        }
158
        return 0;
159
    }
160

  
161
    /**
162
     * Method returns list of diagrams, which are uploaded by given user.
163
     *
164
     * @param user_id id of user
165
     * @return created list of diagram params.
166
     */
167
    public ArrayList<Map<String, String>> getDiagramListByUserId(int user_id) {
168
        String qy = "SELECT * FROM diagram WHERE user_id = '" + user_id + "' ORDER BY created DESC ";
169

  
170
        return createListOfMap(db.exQuery(qy));
171
    }
172

  
173

  
174
    /**
175
     * Method returns list of all public diagrams.
176
     *
177
     * @return created list of diagram params
178
     */
179
    public ArrayList<Map<String, String>> getDiagramPublicList() {
180

  
181
        String qy = "SELECT * FROM diagram WHERE public = 1 ORDER BY name ASC ";
182

  
183
        return createListOfMap(db.exQuery(qy));
184
    }
185

  
186
    /**
187
     * Iterate over all items in input ResultSet and return list of all founded diagrams (map of diagram parameters).
188
     *
189
     * @param rs result set which contains diagram rows.
190
     * @return created list of diagrams.
191
     */
192
    private ArrayList<Map<String,String>> createListOfMap(ResultSet rs) {
193
        ArrayList<Map<String, String>> diagram_list = new ArrayList<>();
194

  
195
        try {
196
            while (rs != null && rs.next()) {
197
                diagram_list.add(createMap(rs));
198
            }
199
            return diagram_list;
200

  
201
        } catch (SQLException e){
202
            e.printStackTrace();
203
        }
204

  
205
        return new ArrayList<>();
206
    }
207

  
208
    /**
209
     * Method take from input ResultSet all parameters of diagram. Result set must point to some row and can not be null.
210
     *
211
     * @param rs not null result set
212
     * @return map of all parameters.
213
     * @throws SQLException
214
     */
215
    private Map<String,String> createMap(ResultSet rs) throws SQLException {
216
        HashMap<String, String> item_map = new HashMap<>();
217

  
218
        item_map.put("id", String.valueOf(rs.getInt("id")));
219
        item_map.put("name", rs.getString("name"));
220
        item_map.put("created", Util.formatDate(rs.getString("created")));
221
        item_map.put("last_update", Util.formatDate(rs.getString("last_update")));
222
        item_map.put("user_id", String.valueOf(rs.getInt("user_id")));
223
        item_map.put("public", String.valueOf(rs.getInt("public")));
224
        item_map.put("graph_json", String.valueOf(rs.getString("graph_json")));
225

  
226
        return item_map;
227
    }
251 228
}

Také k dispozici: Unified diff