· 6 years ago · Apr 22, 2019, 04:56 AM
1package DataBase;
2
3import java.sql.*;
4
5public class DBManager {
6
7 private Connection connection;
8
9 public Connection getConnection(){
10 return connection;
11 }
12
13 public UserSet userSet;
14 public MapSet mapSet;
15 public LayerSet layerSet;
16
17 public DBManager(String dbName){
18 connection = connect(dbName);
19 userSet = new UserSet(connection);
20 mapSet = new MapSet(connection);
21 layerSet = new LayerSet(connection);
22 }
23
24 /**
25 * Connect to a database
26 *
27 * @param dbName the database file name without extension
28 */
29 private static Connection connect(String dbName) {
30 Connection conn = null;
31 try {
32 // db parameters
33 String url = "jdbc:sqlite:./db/"+dbName+".db";
34 // create a connection to the database
35 conn = DriverManager.getConnection(url);
36
37 System.out.println("Connection to SQLite has been established.");
38 } catch (SQLException e) {
39 System.out.println(e.getMessage());
40 }
41 return conn;
42 }
43
44 /**
45 * Connect to a sample database
46 *
47 * @param dbName the database file name
48 */
49 private static void createDatabase(String dbName) {
50
51 String url = "jdbc:sqlite:./db/" + dbName + ".db";
52
53 try (Connection conn = DriverManager.getConnection(url)) {
54 if (conn != null) {
55 DatabaseMetaData meta = conn.getMetaData();
56 System.out.println("The driver name is " + meta.getDriverName());
57 System.out.println("A new database has been created.");
58 }
59 } catch (SQLException e) {
60 System.out.println(e.getMessage());
61 }
62 }
63
64 /**
65 * Create a new table in the test database
66 */
67 private static void createTables(Connection connection) {
68 // SQL statement for creating a new table
69 String sqlUser = "CREATE TABLE IF NOT EXISTS Users"
70 + " (\nid integer PRIMARY KEY,\n"
71 + " login text NOT NULL UNIQUE,\n"
72 + " token text NOT NULL UNIQUE,\n"
73 + " password text NOT NULL\n"
74 + ");";
75
76 String sqlMap = "CREATE TABLE IF NOT EXISTS Maps"
77 + " (\nid integer PRIMARY KEY,\n"
78 + " name text NOT NULL,\n"
79 + " x double NOT NULL,\n"
80 + " y double NOT NULL,\n"
81 + " zoom integer NOT NULL,\n"
82 + " user_id integer NOT NULL,\n"
83 + " FOREIGN KEY(user_id) REFERENCES Users(id)\n"
84 + " ON DELETE CASCADE);";
85
86 String sqlLayers = "CREATE TABLE IF NOT EXISTS Layers"
87 + " (\nid integer PRIMARY KEY,"
88 + " name text NOT NULL,\n"
89 + " link text NOT NULL,\n"
90 + " map_id integer NOT NULL,\n"
91 + " FOREIGN KEY(map_id) REFERENCES Maps(id)\n"
92 + " ON DELETE CASCADE);";
93
94 try (Statement stmt = connection.createStatement()) {
95 // create a new table
96 stmt.execute(sqlUser);
97 stmt.execute(sqlMap);
98 stmt.execute(sqlLayers);
99 System.out.println("Tables have been added!");
100 } catch (SQLException e) {
101 System.out.println(e.getMessage());
102 }
103 }
104
105 /**
106 * Initializes db: creates file and tables
107 *
108 * @param dbName
109 */
110 public static void initDataBase(String dbName){
111 createDatabase(dbName);
112 Connection connection = connect(dbName);
113 createTables(connection);
114 }
115}
116
117_______________________________________________________________________
118package Core;
119
120import DataBase.DBManager;
121import Types.MsgType;
122import com.sun.org.apache.xml.internal.utils.WrongParserException;
123
124import java.util.*;
125
126public class Interpreter {
127
128 List<String> commands = Arrays.asList("create", "update", "delete", "show", "login");
129 List<String> objects = Arrays.asList("map", "layer");
130
131 DBManager manager;
132
133 private int userId;
134
135 public Interpreter(DBManager manager){
136 this.manager = manager;
137 }
138
139 /**
140 * Decides what to do with incoming data
141 * @param input
142 * @return
143 */
144 public String start(String input){
145 String[] data = input.split("-",2);
146 System.out.println("Parts: "+Integer.toString(data.length));
147 if (data.length == 1) // Login or registration ot token
148 {
149 if (input.equals("token"))
150 return manager.userSet.generateToken();
151 if (authenticate(input))
152 return "Success";
153 }
154 else
155 {
156 System.out.println("Processing commands");
157 if (data.length == 2) {
158 String token = data[0];
159 if (authenticated(token))
160 return interpret(data[1]);
161 }
162 }
163 return "Authentication failed";
164 }
165
166 /**
167 * Checks if user's token is authenticated
168 * @param token
169 * @return
170 */
171 private boolean authenticated(String token){
172 userId = manager.userSet.findByToken(token);
173 return userId != -1;
174 }
175
176 /**
177 * Authorizes user
178 * @param code
179 * @return
180 */
181 private boolean authenticate(String code){
182 String[] cmds = code.trim().split(" ");
183 if (cmds.length == 4 && cmds[0].equals("login"))
184 return manager.userSet.login(cmds[1], cmds[2], cmds[3]);
185 if (cmds.length == 3 && cmds[0].equals("register"))
186 return manager.userSet.add(cmds[1], cmds[2]);
187 return false;
188 }
189
190 /**
191 * Runs all the commands in code
192 * @param code
193 * @return
194 */
195 public String interpret(String code){
196 String[] lines = code.split("\n");
197 StringBuilder sb = new StringBuilder();
198
199 for (String line: lines) {
200 Map<String, String> args = new HashMap<String, String>();
201 String[] words = line.split(" ", 3);
202 String result;
203 try {
204 // Basic syntax check
205 if (words.length != 3 ||
206 !objects.contains(words[0]) ||
207 !commands.contains(words[1]))
208 throw new WrongParserException(line);
209
210 String[] argList = words[2].split(" ");
211 if (argList.length % 2 == 1)
212 throw new WrongParserException(line);
213
214 // Set arguments
215 for (int i = 0; i < argList.length - 1; i += 2) {
216 args.put(argList[i], argList[i + 1]);
217 }
218 result = runCommand(words[0], words[1], args);
219 }
220 catch (Exception e) {
221 result = msg(MsgType.syntax, e.getMessage());
222 }
223 System.out.print(result);
224 sb.append(result);
225 sb.append("<br>");
226 }
227 return sb.toString();
228 }
229
230 /**
231 * Returns message by type with args
232 * @param type
233 * @param arg
234 * @return
235 */
236 private String msg(MsgType type, String arg){
237 String message = "none";
238 switch (type){
239 case syntax:
240 message = String.format("Wrong command syntax: %s", arg);
241 break;
242 case args:
243 message = String.format("Wrong arguments count: %s", arg);
244 break;
245 }
246 return message;
247 }
248
249 /**
250 * Interprete one line of code
251 * @param object
252 * @param command
253 * @param args
254 * @return String result
255 */
256 private String runCommand(String object, String command, Map<String, String> args){
257 try {
258 switch (object) {
259 case "map":
260 if (command.equals("create")) {
261 if (manager.mapSet.add(args.get("-map"),
262 Double.parseDouble(args.get("-x")),
263 Double.parseDouble(args.get("-y")),
264 Integer.parseInt(args.get("-zoom")),
265 userId))
266 return String.format("Map %s has been created", args.get("-map"));
267 return "Failed to create map";
268 }
269 MyMap map = manager.mapSet.find(args.get("-map"));
270 if (map == null)
271 return String.format("Map %s does not exist", args.get("-map"));
272 if (command.equals("show"))
273 return new MapGenerator().generateMap(
274 manager.mapSet.getLayers(map.getId()), map);
275
276 if (command.equals("update"))
277 if (manager.mapSet.update(map.getId(), args))
278 return String.format("Map %s has been updated", args.get("-map"));
279
280 if (command.equals("delete"))
281 if (manager.mapSet.delete(map.getId()))
282 return String.format("Map %s has been deleted", args.get("-map"));
283 break;
284 case "layer":
285 if (command.equals("create")) {
286 if (manager.layerSet.add(args.get("-layer"), args.get("-link"),
287 manager.mapSet.find(args.get("-map")).getId()))
288 return String.format("Layer %s has been added", args.get("-layer"));
289 return "Failed to create layer";
290 }
291 Layer layer = manager.layerSet.find(args.get("-layer"));
292 if (layer == null)
293 return String.format("Layer %s does not exist", args.get("-layer"));
294
295 if (command.equals("update"))
296 if (manager.layerSet.update(layer.getId(), args))
297 return "Layer has been updated";
298
299 if (command.equals("show")) {
300 return manager.layerSet.show(layer.getId());
301 }
302
303 if (command.equals("delete")) {
304 if (manager.layerSet.delete(layer.getId()))
305 return "Layer has been deleted";
306 }
307
308 break;
309 }
310 }
311 catch (Exception e){
312 System.out.println(e.getMessage());
313 return "Wrong set of arguments";
314 }
315 return "Failed to run the command";
316 }
317}
318_____________________________________________________
319package Core;
320
321import java.util.ArrayList;
322import java.util.List;
323import java.util.Locale;
324
325public class MapGenerator {
326
327 String begin = "<!DOCTYPE html><html>"+
328 " <head>\n" +
329 " <!-- Leaflet's CSS -->\n" +
330 " <link rel=\"stylesheet\" href=\"https://unpkg.com/leaflet@1.4.0/dist/leaflet.css\"\n" +
331 " integrity=\"sha512-puBpdR0798OZvTTbP4A8Ix/l+A4dHDD0DGqYW6RQ+9jxkRFclaxxQb/SJAWZfWAkuyeQUytO7+7N4QKrDh+drA==\"\n" +
332 " crossorigin=\"\"/>\n" +
333 " <!-- Leaflet JavaScript -->\n" +
334 " <script src=\"https://unpkg.com/leaflet@1.4.0/dist/leaflet.js\"\n" +
335 " integrity=\"sha512-QVftwZFqvtRNi0ZyCtsznlKSWOStnDORoefr1enyq5mVL4tmKB3S/EnC3rRJcxCPavG10IcrVGSmPh6Qw5lwrg==\"\n" +
336 " crossorigin=\"\"></script>\n" +
337 "\n" +
338 " <script>\n" +
339 "\t\thtml, body {\n" +
340 "\t\t\theight: 100%;\n" +
341 "\t\t\tmargin: 0;\n" +
342 "\t\t}\n" +
343 "\t\t#map {\n" +
344 "\t\t\twidth: 600px;\n" +
345 "\t\t\theight: 400px;\n" +
346 "\t\t}\n" +
347 " </script>\n" +
348 "\n" +
349 " <script type=\"text/javascript\" src=\"chrome-extension://aggiiclaiamajehmlfpkjmlbadmkledi/popup.js\" async=\"\"></script>\n" +
350 " <script type=\"text/javascript\" src=\"chrome-extension://aggiiclaiamajehmlfpkjmlbadmkledi/tat_popup.js\" async=\"\"></script>\n" +
351 " </head>" +
352 " <body>\n" +
353 " <!-- map div -->\n" +
354 " <div id=\"mapid\" style=\"width: 600px; height: 400px; position: relative;\"></div>\n"+
355 "<script> \n"+
356 " var mbAttr = 'Map data © <a href=\"https://www.openstreetmap.org/\">OpenStreetMap</a> contributors, <a href=\"https://creativecommons.org/licenses/by-sa/2.0/\">CC-BY-SA</a>, Imagery © <a href=\"https://www.mapbox.com/\">Mapbox</a>';\n"+
357 " var mbUrl = 'https://api.tiles.mapbox.com/v4/{id}/{z}/{x}/{y}.png?access_token=pk.eyJ1IjoiYWRyYXgiLCJhIjoiY2p1OTQ0dW1kMTV5bzN5bnpsemlpbTh3NSJ9.evyvRseaJS0WSTlgR38MXg';\n"+
358 " var layer_map_id = 'mapbox.streets';";
359 String end = "\nvar control = L.control.layers(baseMaps, overlayMaps);\n" +
360 " control.addTo(mymap);" +
361 " </script>\n" +
362 " </body>\n" +
363 "</html>";
364
365
366 /**
367 * Generates html map with Leaflet using map and its layers
368 * @param layers
369 * @param map
370 * @return
371 */
372 public String generateMap(List<Layer> layers, MyMap map) {
373 StringBuilder html = new StringBuilder();
374 html.append(begin);
375 StringBuilder layersList = new StringBuilder();
376 StringBuilder baseMaps = new StringBuilder();
377 baseMaps.append("var baseMaps = {");
378 StringBuilder overlayMaps = new StringBuilder();
379 overlayMaps.append("var overlayMaps = {");
380
381 for (Layer layer: layers) {
382
383 html.append(String.format("var %s = L.tileLayer(mbUrl, {id: '%s', attribution: mbAttr});",
384 layer.getName(), layer.getLink()));
385
386 //html.append(buildLayer(layer.getName(), layer.getMapBoxUrl(),layer.getMapBoxAttr(),layer.getLayerId()));
387 html.append("\n");
388 // Build layers lists
389 if (layer.getType().equals("base")) {
390 baseMaps.append(String.format("\" %s\":%s,", layer.getName(), layer.getName()));
391 }
392 else{
393 overlayMaps.append(String.format("\" %s\":%s,", layer.getName(), layer.getName()));
394 }
395 layersList.append(layer.getName());
396 layersList.append(',');
397 }
398 baseMaps.append("};");
399 overlayMaps.append("};");
400
401 String mapHtml = String.format(Locale.US, "var mymap = L.map('mapid', {center: [%.2f, %.2f],zoom: %d,layers:[]});",
402 map.getX(), map.getY(), map.getZoom());
403
404 html.append(mapHtml);
405 html.append(baseMaps.toString());
406 html.append(overlayMaps.toString());
407 html.append(end);
408 return html.toString();
409 }
410
411 /**
412 * Should be used to make layer js string from Layer
413 * @return
414 */
415 public String buildLayer(String name, String mbUrl, String mbAttr, String id ){
416 String layer = String.format("var %s = L.tileLayer('%s', {id: '%s', attribution: '%s'});",
417 name, mbUrl, id, mbAttr);
418 return layer;
419 }
420 public List<Layer> generateLayers(){
421 List<Layer> layers = new ArrayList<Layer>();
422 String mbUrl = "https://api.tiles.mapbox.com/v4/{id}/{z}/{x}/{y}.png?access_token=pk.eyJ1IjoiYWRyYXgiLCJhIjoiY2p1OTQ0dW1kMTV5bzN5bnpsemlpbTh3NSJ9.evyvRseaJS0WSTlgR38MXg";
423 String mbAttr = "Map data © <a href=\"https://www.openstreetmap.org/\">OpenStreetMap</a> contributors, <a href=\"https://creativecommons.org/licenses/by-sa/2.0/\">CC-BY-SA</a>, Imagery © <a href=\"https://www.mapbox.com/\">Mapbox</a>";
424 /*for(int i = 0; i<5; i++){
425 Layer layer = new Layer("layer"+Integer.toString(i),
426 "base",
427 "mapbox.light",
428 mbUrl,
429 mbAttr);
430 layers.add(layer);
431 }
432 */
433 //Map map = new Map("map1",39.73, -104.99, 6);
434 //writeHtml(new MapGenerator().generateMap(layers, map));
435
436 return layers;
437 }
438
439}
440_______________________________________________________________________________
441package DataBase;
442
443import Core.Layer;
444import Core.MyMap;
445
446import java.sql.*;
447import java.util.ArrayList;
448import java.util.List;
449import java.util.Map;
450
451public class MapSet {
452
453 private Connection connection;
454 public void setConnection(Connection conn){
455 connection = conn;
456 }
457
458 public MapSet(Connection connection){
459 this.connection = connection;
460 }
461
462 /**
463 * Adds new map to the DataBase
464 *
465 * @param name
466 * @param x
467 * @param y
468 * @param zoom
469 * @param userId
470 * @return
471 */
472 public boolean add(String name, double x, double y, int zoom, int userId) {
473 String sql = "INSERT INTO Maps(name, x, y, zoom, user_id) VALUES(?,?,?,?,?)";
474 // Check if map already exists
475 if (find(name) != null)
476 return false;
477 try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
478 pstmt.setString(1, name);
479 pstmt.setDouble(2, x);
480 pstmt.setDouble(3, y);
481 pstmt.setInt(4, zoom);
482 pstmt.setInt(5, userId);
483 pstmt.executeUpdate();
484 System.out.println(String.format("Map %s has been added!", name));
485 return true;
486 } catch (SQLException e) {
487 System.out.println(e.getMessage());
488 }
489 return false;
490 }
491
492 /**
493 * Update name of a map specified by the id
494 *
495 * @param id
496 * @param args
497 */
498 public boolean update(int id, Map<String, String> args) {
499 // Generate sql string
500 StringBuilder sb = new StringBuilder();
501 sb.append("UPDATE Maps SET");
502 if (args.containsKey("-name")) {
503 // Check if layer already exists
504 if (find(args.get("-name")) != null)
505 return false;
506 sb.append(" name = ?,");
507 }
508 if (args.containsKey("-x"))
509 sb.append(" x = ?,");
510 if (args.containsKey("-y"))
511 sb.append(" y = ?,");
512 if (args.containsKey("-zoom"))
513 sb.append(" zoom = ?,");
514 sb.setLength(sb.length() - 1);
515 sb.append(" WHERE id = ?");
516 String sql = sb.toString();
517 try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
518 int counter = 1;
519 // Set corresponding parameters
520 if (args.containsKey("-name")) {
521 pstmt.setString(counter, args.get("-name"));
522 counter++;
523 }
524 if (args.containsKey("-x")) {
525 pstmt.setDouble(counter, Double.parseDouble(args.get("-x")));
526 counter++;
527 }
528 if (args.containsKey("-y")){
529 pstmt.setDouble(counter, Double.parseDouble(args.get("-y")));
530 counter++;
531 }
532 if (args.containsKey("-zoom")){
533 pstmt.setDouble(counter, Integer.parseInt(args.get("-zoom")));
534 counter++;
535 }
536 pstmt.setInt(counter, id);
537 // Update
538 pstmt.executeUpdate();
539 System.out.println("Map's has been updated!");
540 return true;
541 } catch (SQLException e) {
542 System.out.println(e.getMessage());
543 }
544 return false;
545 }
546
547 /**
548 * Delete a map specified by the id
549 *
550 * @param id
551 */
552 public boolean delete(int id) {
553 String sql = "DELETE FROM Maps WHERE id = ?";
554
555 try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
556 pstmt.setInt(1, id);
557 pstmt.executeUpdate();
558 System.out.println(String.format("Map with id %d is deleted!", id));
559 return true;
560 } catch (SQLException e) {
561 System.out.println(e.getMessage());
562 return false;
563 }
564 }
565
566 /**
567 * Searches for a map by name
568 */
569 public MyMap find(String name){
570 MyMap map = null;
571 String sql = "SELECT id, name, x, y, zoom, user_id FROM Maps WHERE name = ?";
572 ResultSet rs = null;
573 try (PreparedStatement pstmt = connection.prepareStatement(sql)){
574
575 pstmt.setString(1, name);
576 rs = pstmt.executeQuery();
577
578 // loop through the result set
579 while (rs.next()) {
580 map = new MyMap( rs.getInt("id"),
581 rs.getString("name"),
582 rs.getDouble("x"),
583 rs.getDouble("y"),
584 rs.getInt("zoom"));
585 System.out.println(rs.getInt("id") + "\t" +
586 rs.getString("name") + "\t" +
587 rs.getDouble("x") + "\t" +
588 rs.getDouble("y") + "\t" +
589 rs.getInt("zoom") + "\t" +
590 rs.getInt("user_id"));
591 return map;
592 }
593 } catch (SQLException e) {
594 System.out.println(e.getMessage());
595 }
596 return map;
597 }
598
599 /**
600 * Searches for a map by name
601 */
602 public List<Layer> getLayers(int mapId){
603 String sql = "SELECT id, name, link FROM Layers WHERE map_id = ?";
604 ResultSet rs = null;
605 List<Layer> layers = new ArrayList<Layer>();
606 try (PreparedStatement pstmt = connection.prepareStatement(sql)){
607
608 pstmt.setInt(1, mapId);
609 rs = pstmt.executeQuery();
610
611 // loop through the result set
612 while (rs.next()) {
613 layers.add(new Layer(rs.getInt("id"),
614 rs.getString("name"),
615 rs.getString("link")));
616 System.out.println(rs.getInt("id") + "\t" +
617 rs.getString("name") + "\t" +
618 rs.getString("link"));
619 }
620 } catch (SQLException e) {
621 System.out.println(e.getMessage());
622 }
623 return layers;
624 }
625
626 /**
627 * Select all rows in the Maps table
628 */
629 public void selectAll(){
630 String sql = "SELECT id, name, user_id FROM Maps";
631
632 try (Statement stmt = connection.createStatement();
633 ResultSet rs = stmt.executeQuery(sql)){
634
635 // loop through the result set
636 while (rs.next()) {
637 System.out.println(rs.getInt("id") + "\t" +
638 rs.getString("name") + "\t" +
639 rs.getString("user_id"));
640 }
641 } catch (SQLException e) {
642 System.out.println(e.getMessage());
643 }
644 }
645}
646____________________________________________________________________
647package DataBase;
648
649import Core.Layer;
650
651import java.sql.*;
652import java.util.Map;
653
654public class LayerSet {
655
656 private Connection connection;
657 public void setConnection(Connection conn){
658 connection = conn;
659 }
660
661 public LayerSet(Connection connection){
662 this.connection = connection;
663 }
664
665 /**
666 * Adds new map to the DataBase
667 *
668 * @param link
669 * @param mapId
670 */
671 public boolean add(String name, String link, int mapId) {
672 String sql = "INSERT INTO Layers (name, link, map_id) VALUES(?,?,?)";
673 // Check if layer already exists
674 if (find(name) != null)
675 return false;
676 try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
677 pstmt.setString(1, name);
678 pstmt.setString(2, link);
679 pstmt.setInt(3, mapId);
680 pstmt.executeUpdate();
681 System.out.println(String.format("Layer \" %s \" has been added!", link));
682 return true;
683 } catch (SQLException e) {
684 System.out.println(e.getMessage());
685 }
686 return false;
687 }
688
689 /**
690 * Update name of a map specified by the id
691 *
692 * @param id
693 * @param args
694 */
695 public boolean update(int id, Map<String, String> args) {
696 // Cook sql request
697 StringBuilder sb = new StringBuilder();
698 sb.append("UPDATE Layers SET");
699 if (args.containsKey("-name")) {
700 // Check if layer with "new name" already exists
701 if (find(args.get("-name")) != null)
702 return false;
703 sb.append(" name = ?,");
704 }
705 if (args.containsKey("-link"))
706 sb.append(" link = ?,");
707 sb.setLength(sb.length() - 1);
708 sb.append(" WHERE id = ?");
709 String sql = sb.toString();
710 try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
711 int counter = 1;
712 // Set corresponding parameters
713 if (args.containsKey("-name")) {
714 pstmt.setString(counter, args.get("-name"));
715 counter++;
716 }
717 if (args.containsKey("-link")) {
718 pstmt.setString(counter, args.get("-link"));
719 counter++;
720 }
721 pstmt.setInt(counter, id);
722 // update
723 pstmt.executeUpdate();
724 System.out.println("Layer has been changed");
725 return true;
726 } catch (SQLException e) {
727 System.out.println(e.getMessage());
728 }
729 return false;
730 }
731
732 /**
733 * Delete a map specified by the id
734 * @param id
735 * @return true|false
736 */
737 public boolean delete(int id) {
738 String sql = "DELETE FROM Layers WHERE id = ?";
739
740 try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
741 pstmt.setInt(1, id);
742 pstmt.executeUpdate();
743 System.out.println(String.format("Layer with id %d is deleted!", id));
744 return true;
745 } catch (SQLException e) {
746 System.out.println(e.getMessage());
747 return false;
748 }
749 }
750
751 /**
752 * Searches for a map by name
753 * @param name
754 * @return Layer
755 */
756 public Layer find(String name){
757 Layer layer = null;
758 String sql = "SELECT id, name, link, map_id FROM Layers WHERE name = ?";
759 ResultSet rs = null;
760 try (PreparedStatement pstmt = connection.prepareStatement(sql)){
761
762 pstmt.setString(1, name);
763 rs = pstmt.executeQuery();
764
765 // loop through the result set
766 while (rs.next()) {
767 layer = new Layer( rs.getInt("id"),
768 rs.getString("name"),
769 rs.getString("link"));
770 System.out.println(rs.getInt("id") + "\t" +
771 rs.getString("name") + "\t" +
772 rs.getString("link") + "\t" +
773 rs.getInt("map_id"));
774 return layer;
775 }
776 } catch (SQLException e) {
777 System.out.println(e.getMessage());
778 }
779 return layer;
780 }
781
782 /**
783 * Make a representation of layer with its attributes
784 * @param id
785 * @return String
786 */
787 public String show(int id){
788 String sql = "SELECT id, name, link, map_id FROM Layers WHERE id = ?";
789 StringBuilder sb = new StringBuilder();
790 ResultSet rs = null;
791 try (PreparedStatement pstmt = connection.prepareStatement(sql)){
792 pstmt.setInt(1, id);
793 rs = pstmt.executeQuery();
794
795 // loop through the result set
796 while (rs.next()) {
797 sb.append(rs.getString("id"));
798 sb.append("\t");
799 sb.append(rs.getString("name"));
800 sb.append("\t");
801 sb.append(rs.getString("link"));
802 sb.append("\t");
803 sb.append(rs.getString("map_id"));
804 }
805 System.out.println(sb.toString());
806 return sb.toString();
807 } catch (SQLException e) {
808 System.out.println(e.getMessage());
809 }
810 return "Failed";
811 }
812
813 /**
814 * Select all rows in the Layers table
815 */
816 public String selectAll(){
817 String sql = "SELECT id, name, link, map_id FROM Layers";
818 StringBuilder sb = new StringBuilder();
819 try (Statement stmt = connection.createStatement();
820 ResultSet rs = stmt.executeQuery(sql)){
821
822 // loop through the result set
823 while (rs.next()) {
824 sb.append(rs.getString("id") + "\t" +
825 rs.getString("name") + "\t" +
826 rs.getString("link") + "\t" +
827 rs.getString("map_id") + "\n");
828 System.out.println(rs.getInt("id") + "\t" +
829 rs.getString("name") + "\t" +
830 rs.getString("link") + "\t" +
831 rs.getInt("map_id"));
832 }
833 return sb.toString();
834 } catch (SQLException e) {
835 System.out.println(e.getMessage());
836 }
837 return "Failed";
838 }
839}
840__________________________________________________________________________
841package DataBase;
842
843import java.security.SecureRandom;
844import java.sql.*;
845
846public class UserSet {
847
848 private Connection connection;
849 public void setConnection(Connection conn){
850 connection = conn;
851 }
852
853 public UserSet(Connection connection){
854 this.connection = connection;
855 }
856
857
858 public String generateToken(){
859 SecureRandom random = new SecureRandom();
860 byte bytes[] = new byte[20];
861 random.nextBytes(bytes);
862 String token = bytes.toString();
863 return token;
864 }
865
866 /**
867 * Adds new user to the DataBase
868 *
869 * @param login
870 * @param password
871 */
872 public boolean add(String login, String password) {
873 String sql = "INSERT INTO Users(login, password, token) VALUES(?,?,?)";
874
875 try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
876 pstmt.setString(1, login);
877 pstmt.setString(2, password);
878 pstmt.setString(3, generateToken());
879 pstmt.executeUpdate();
880 System.out.println(String.format("User %s has been added!", login));
881 return true;
882 } catch (SQLException e) {
883 System.out.println(e.getMessage());
884 }
885 return false;
886 }
887
888 /**
889 * Update data of a user specified by the id
890 *
891 * @param id
892 * @param login name of the warehouse
893 * @param password capacity of the warehouse
894 */
895 public void update(int id, String login, String password) {
896 String sql = "UPDATE Users SET login = ? , "
897 + "password = ? "
898 + "WHERE id = ?";
899
900 try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
901
902 // set the corresponding param
903 pstmt.setString(1, login);
904 pstmt.setString(2, password);
905 pstmt.setInt(3, id);
906 // update
907 pstmt.executeUpdate();
908 System.out.println(String.format("User %s has been updated!", login));
909 } catch (SQLException e) {
910 System.out.println(e.getMessage());
911 }
912 }
913
914 /**
915 * Delete a user specified by the id
916 *
917 * @param id
918 */
919 public void delete(int id) {
920 String sql = "DELETE FROM Users WHERE id = ?";
921
922 try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
923
924 // set the corresponding param
925 pstmt.setInt(1, id);
926 // execute the delete statement
927 pstmt.executeUpdate();
928 System.out.println(String.format("User with id %d is deleted!", id));
929 } catch (SQLException e) {
930 System.out.println(e.getMessage());
931 }
932 }
933
934 /**
935 * Select all rows in the Users table
936 */
937 public void selectAll(){
938 String sql = "SELECT id, login, password FROM Users";
939
940 try (Statement stmt = connection.createStatement();
941 ResultSet rs = stmt.executeQuery(sql)){
942
943 // loop through the result set
944 while (rs.next()) {
945 System.out.println(rs.getInt("id") + "\t" +
946 rs.getString("login") + "\t" +
947 rs.getString("password"));
948 }
949 } catch (SQLException e) {
950 System.out.println(e.getMessage());
951 }
952 }
953
954 /**
955 * Searches for a user by login
956 */
957 public int findByToken(String token){
958 String sql = "SELECT id, login FROM Users WHERE token = ?";
959 ResultSet rs = null;
960 int userId = -1;
961 try (PreparedStatement pstmt = connection.prepareStatement(sql)){
962
963 pstmt.setString(1, token);
964 rs = pstmt.executeQuery();
965
966 // loop through the result set
967 while (rs.next()) {
968 userId = rs.getInt("id");
969 System.out.println(rs.getInt("id") + "\t" +
970 rs.getString("login"));
971 }
972 } catch (SQLException e) {
973 System.out.println(e.getMessage());
974 }
975 return userId;
976 }
977
978 /**
979 * Searches for a user by login
980 */
981 public boolean login(String login, String password, String token){
982 String sql = "SELECT id, login, password FROM Users WHERE login = ?";
983 ResultSet rs = null;
984 int userId = -1;
985 System.out.println(String.format("Requested %s %s", login, password));
986 try (PreparedStatement pstmt = connection.prepareStatement(sql)){
987
988 pstmt.setString(1, login);
989 rs = pstmt.executeQuery();
990
991 // loop through the result set
992 while (rs.next()) {
993 System.out.println(rs.getInt("id") + "\t" +
994 rs.getString("login") + "\t" +
995 rs.getString("password"));
996 if (password.equals(rs.getString("password"))) {
997 updateToken(rs.getInt("id"), token);
998 return true;
999 }
1000 }
1001 } catch (SQLException e) {
1002 System.out.println(e.getMessage());
1003 }
1004 return false;
1005 }
1006
1007 private void updateToken(int id, String token) {
1008 String sql = "UPDATE Users SET token = ? "
1009 + "WHERE id = ?";
1010
1011 try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
1012
1013 // set the corresponding param
1014 pstmt.setString(1, token);
1015 pstmt.setInt(2, id);
1016 // update
1017 pstmt.executeUpdate();
1018 System.out.println(String.format("Token for User %s has been updated!", id));
1019 } catch (SQLException e) {
1020 System.out.println(e.getMessage());
1021 }
1022 }
1023}
1024_______________________________________________________________________________________
1025import Core.Interpreter;
1026import DataBase.DBManager;
1027import io.netty.channel.ChannelHandlerContext;
1028import io.netty.channel.ChannelInboundHandlerAdapter;
1029import io.netty.handler.codec.http.websocketx.*;
1030
1031public class WebSocketHandler extends ChannelInboundHandlerAdapter {
1032
1033 @Override
1034 public void channelRead(ChannelHandlerContext ctx, Object msg) {
1035
1036 if (msg instanceof WebSocketFrame) {
1037 System.out.println("This is a WebSocket frame");
1038 System.out.println("Client Channel : " + ctx.channel());
1039 if (msg instanceof BinaryWebSocketFrame) {
1040 System.out.println("BinaryWebSocketFrame Received : ");
1041 System.out.println(((BinaryWebSocketFrame) msg).content());
1042 } else if (msg instanceof TextWebSocketFrame) {
1043 System.out.println("TextWebSocketFrame Received : ");
1044 String input = ((TextWebSocketFrame) msg).text();
1045 System.out.println(input);
1046 String result = processCode(input);
1047 ctx.channel().writeAndFlush(new TextWebSocketFrame(result));
1048 System.out.println(result);
1049 } else if (msg instanceof PingWebSocketFrame) {
1050 System.out.println("PingWebSocketFrame Received : ");
1051 System.out.println(((PingWebSocketFrame) msg).content());
1052 } else if (msg instanceof PongWebSocketFrame) {
1053 System.out.println("PongWebSocketFrame Received : ");
1054 System.out.println(((PongWebSocketFrame) msg).content());
1055 } else if (msg instanceof CloseWebSocketFrame) {
1056 System.out.println("CloseWebSocketFrame Received : ");
1057 System.out.println("ReasonText :" + ((CloseWebSocketFrame) msg).reasonText());
1058 System.out.println("StatusCode : " + ((CloseWebSocketFrame) msg).statusCode());
1059 } else {
1060 System.out.println("Unsupported WebSocketFrame");
1061 ctx.channel().writeAndFlush(new TextWebSocketFrame("What do you mean?"));
1062 }
1063 }
1064 }
1065
1066 private String processCode(String code){
1067 String result = "none";
1068 try {
1069 DBManager manager = new DBManager("new");
1070 result = new Interpreter(manager).start(code);
1071 } catch (NullPointerException e) {
1072 System.out.println(e.toString());
1073 }
1074 return result;
1075 }
1076}
1077_________________________________________________________________
1078import Core.Interpreter;
1079import DataBase.DBManager;
1080import io.netty.buffer.ByteBuf;
1081
1082import io.netty.channel.ChannelHandlerContext;
1083import io.netty.channel.ChannelInboundHandlerAdapter;
1084import io.netty.handler.codec.http.HttpContentEncoder;
1085import io.netty.handler.codec.http.HttpHeaderNames;
1086import io.netty.handler.codec.http.HttpHeaders;
1087import io.netty.handler.codec.http.HttpRequest;
1088import io.netty.handler.codec.http.websocketx.WebSocketServerHandshaker;
1089import io.netty.handler.codec.http.websocketx.WebSocketServerHandshakerFactory;
1090import io.netty.util.CharsetUtil;
1091
1092/**
1093 * Handles a server-side channel.
1094 */
1095public class ServerHandler extends ChannelInboundHandlerAdapter {
1096
1097 WebSocketServerHandshaker handshaker;
1098
1099 @Override
1100 public void channelRead(ChannelHandlerContext ctx, Object msg) {
1101 //((ByteBuf) msg).release();
1102 if (msg instanceof HttpRequest) {
1103
1104 HttpRequest httpRequest = (HttpRequest) msg;
1105
1106 System.out.println("Http Request Received");
1107
1108 HttpHeaders headers = httpRequest.headers();
1109 System.out.println("Connection : " +headers.toString());
1110
1111 if ("Upgrade".equalsIgnoreCase(headers.get(HttpHeaderNames.CONNECTION)) &&
1112 "WebSocket".equalsIgnoreCase(headers.get(HttpHeaderNames.UPGRADE))) {
1113
1114 //Adding new handler to the existing pipeline to handle WebSocket Messages
1115 ctx.pipeline().replace(this, "websocketHandler", new WebSocketHandler());
1116
1117 System.out.println("WebSocketHandler added to the pipeline");
1118 System.out.println("Opened Channel : " + ctx.channel());
1119 //Do the Handshake to upgrade connection from HTTP to WebSocket protocol
1120 handleHandshake(ctx, httpRequest);
1121 System.out.println("Handshake is done");
1122 }
1123 }
1124 else
1125 {
1126 System.out.println("Unsupported message was received");
1127 }
1128 }
1129
1130 /* Do the handshaking for WebSocket request */
1131 protected void handleHandshake(ChannelHandlerContext ctx, HttpRequest req) {
1132 WebSocketServerHandshakerFactory wsFactory =
1133 new WebSocketServerHandshakerFactory(getWebSocketURL(req), null, true);
1134 handshaker = wsFactory.newHandshaker(req);
1135 if (handshaker == null) {
1136 WebSocketServerHandshakerFactory.sendUnsupportedVersionResponse(ctx.channel());
1137 } else {
1138 handshaker.handshake(ctx.channel(), req);
1139 }
1140 }
1141 protected String getWebSocketURL(HttpRequest req) {
1142 String url = "ws://" + req.headers().get("Host");
1143 System.out.println("Constructed URL : " + url);
1144 return url;
1145 }
1146
1147 @Override
1148 public void exceptionCaught(ChannelHandlerContext ctx, Throwable cause) { // (4)
1149 // Close the connection when an exception is raised.
1150 cause.printStackTrace();
1151 ctx.close();
1152 }
1153}
1154_______________________________________________________
1155import Core.Interpreter;
1156import DataBase.DBManager;
1157import io.netty.bootstrap.ServerBootstrap;
1158import io.netty.channel.ChannelFuture;
1159import io.netty.channel.ChannelInitializer;
1160import io.netty.channel.ChannelOption;
1161import io.netty.channel.EventLoopGroup;
1162import io.netty.channel.nio.NioEventLoopGroup;
1163import io.netty.channel.socket.SocketChannel;
1164import io.netty.channel.socket.nio.NioServerSocketChannel;
1165import io.netty.handler.codec.http.HttpServerCodec;
1166
1167import java.io.BufferedWriter;
1168import java.io.FileWriter;
1169import java.io.IOException;
1170
1171/**
1172 * Discards any incoming data.
1173 */
1174public class Server {
1175
1176 private int port;
1177
1178 public Server(int port) {
1179 this.port = port;
1180 }
1181
1182 public void run() throws Exception {
1183 EventLoopGroup bossGroup = new NioEventLoopGroup();
1184 EventLoopGroup workerGroup = new NioEventLoopGroup();
1185 try {
1186 ServerBootstrap b = new ServerBootstrap();
1187 b.group(bossGroup, workerGroup)
1188 .channel(NioServerSocketChannel.class)
1189 .childHandler(new ChannelInitializer<SocketChannel>() {
1190 @Override
1191 public void initChannel(SocketChannel ch) throws Exception {
1192 ch.pipeline().addLast("httpServerCodec", new HttpServerCodec());
1193 ch.pipeline().addLast("httpHandler", new ServerHandler());
1194 }
1195 })
1196 .option(ChannelOption.SO_BACKLOG, 128)
1197 .childOption(ChannelOption.SO_KEEPALIVE, true);
1198
1199 // Bind and start to accept incoming connections.
1200 ChannelFuture f = b.bind(port).sync();
1201
1202 // Wait until the server socket is closed.
1203 // In this example, this does not happen, but you can do that to gracefully
1204 // shut down your server.
1205 f.channel().closeFuture().sync();
1206 } finally {
1207 workerGroup.shutdownGracefully();
1208 bossGroup.shutdownGracefully();
1209 }
1210 }
1211
1212 public static void main(String[] args) throws Exception {
1213
1214 int port = 9090;
1215 if (args.length > 0) {
1216 port = Integer.parseInt(args[0]);
1217 }
1218 //initDB();
1219 //initUser();
1220
1221 new Server(port).run();
1222
1223 //DBManager manager = new DBManager("new");
1224 //System.out.println(new Interpreter(manager).interpret("map create -map map2 -x 1 -y 2 -zoom 3"));
1225 //test();
1226 }
1227
1228 private static void initDB(){
1229 DBManager.initDataBase("new");
1230 }
1231
1232 private static void initUser(){
1233 DBManager manager = new DBManager("new");
1234 manager.userSet.add("Gosha", "pswd");
1235 }
1236
1237 private static void test() {
1238 //DBManager.initDataBase("new");
1239 DBManager manager = new DBManager("new");
1240 //manager.userSet.add("Gosha", "password");
1241 //String code = "create map new_map 3 5 4";
1242 String code = "update map new_map newMap";
1243 String result = new Interpreter(manager).start(code);
1244 System.out.print(result);
1245 //writeHtml(result);
1246 manager.mapSet.selectAll();
1247 }
1248
1249 public static void writeHtml(String text) throws IOException
1250 {
1251 BufferedWriter writer = new BufferedWriter(new FileWriter("samplefile1.html"));
1252 writer.write(text);
1253 writer.close();
1254 }
1255}