labelingsystem-server  Version 0.1.0.0
Static Public Member Functions | Static Private Attributes | List of all members
SQlite Class Reference
Collaboration diagram for SQlite:
Collaboration graph
[legend]

Static Public Member Functions

static String getUserInfoAndCheckPassword (String user, String password) throws SQLException, ClassNotFoundException, AccessDeniedException, IOException
 
static boolean insertUser (String user, String password) throws ClassNotFoundException, IOException, NoSuchAlgorithmException
 
static boolean deactivateUser (String user_name) throws ClassNotFoundException, IOException
 
static boolean activateUser (String user_name) throws ClassNotFoundException, IOException
 
static JSONArray getUsersInfo () throws SQLException, ClassNotFoundException, AccessDeniedException, IOException
 
static JSONObject getUserInfo (String user_name) throws SQLException, ClassNotFoundException, AccessDeniedException, IOException
 
static boolean setLogin (String user, String role) throws ClassNotFoundException, IOException, NoSuchAlgorithmException
 
static String[] getLoginStatus (String userAndToken) throws SQLException, ClassNotFoundException, AccessDeniedException, IOException
 
static boolean setLogout (String user) throws ClassNotFoundException, IOException
 
static boolean insertRetcatString (String vocabulary, String retcatString) throws ClassNotFoundException, IOException
 
static boolean deleteRetcatEntry (String vocabulary) throws ClassNotFoundException, IOException
 
static String getRetcatByVocabulary (String vocabulary) throws SQLException, ClassNotFoundException, AccessDeniedException, IOException
 
static boolean insertRetcatStringForList (String vocabulary, String vocabID) throws ClassNotFoundException, IOException
 
static boolean deleteRetcatEntryForList (String vocabulary) throws ClassNotFoundException, IOException
 
static String getRetcatByVocabularyForList (String vocabulary) throws SQLException, ClassNotFoundException, AccessDeniedException, IOException
 
static void insertStatisticsForVocabuary (String vocabID, String lastModifyAction, int wayback, int translations, int descriptions, int linksexternal, int linksinternal, int linkscount, int labelscount) throws ClassNotFoundException, IOException, SQliteException
 
static void deleteStatisticsForVocabuary (String vocabID) throws ClassNotFoundException, IOException, SQliteException
 
static JSONObject getStatisticsForVocabuary (String vocabulary) throws SQLException, ClassNotFoundException, AccessDeniedException, IOException, SQliteException
 

Static Private Attributes

static final String DBDRIVER = "org.sqlite.JDBC"
 

Member Function Documentation

◆ activateUser()

static boolean activateUser ( String  user_name) throws ClassNotFoundException, IOException
static
90  {
91  boolean ret = false;
92  String activationToken = "-1";
93  Class.forName(DBDRIVER);
94  try (Connection c = DriverManager.getConnection("jdbc:sqlite:" + ConfigProperties.getPropertyParam("sqlite"))) {
95  try (Statement stmt = c.createStatement()) {
96  String sql = "UPDATE users SET activation_token = '" + activationToken + "' WHERE user_name = '" + user_name + "';";
97  stmt.executeUpdate(sql);
98  }
99  ret = true;
100  } catch (Exception e) {
101  throw new NullPointerException(e.toString());
102  } finally {
103  return ret;
104  }
105  }

References SQlite.DBDRIVER, and ConfigProperties.getPropertyParam().

Referenced by AgentsResource.updateAgent().

◆ deactivateUser()

static boolean deactivateUser ( String  user_name) throws ClassNotFoundException, IOException
static
73  {
74  boolean ret = false;
75  String activationToken = UUID.randomUUID().toString();
76  Class.forName(DBDRIVER);
77  try (Connection c = DriverManager.getConnection("jdbc:sqlite:" + ConfigProperties.getPropertyParam("sqlite"))) {
78  try (Statement stmt = c.createStatement()) {
79  String sql = "UPDATE users SET activation_token = '" + activationToken + "' WHERE user_name = '" + user_name + "';";
80  stmt.executeUpdate(sql);
81  }
82  ret = true;
83  } catch (Exception e) {
84  throw new NullPointerException(e.toString());
85  } finally {
86  return ret;
87  }
88  }

References SQlite.DBDRIVER, and ConfigProperties.getPropertyParam().

Referenced by AgentsResource.updateAgent().

◆ deleteRetcatEntry()

static boolean deleteRetcatEntry ( String  vocabulary) throws ClassNotFoundException, IOException
static
231  {
232  boolean ret = false;
233  Class.forName(DBDRIVER);
234  try (Connection c = DriverManager.getConnection("jdbc:sqlite:" + ConfigProperties.getPropertyParam("sqlite"))) {
235  try (Statement stmt = c.createStatement()) {
236  String sql = "DELETE FROM retcat WHERE vocabulary = '" + vocabulary + "'";
237  stmt.executeUpdate(sql);
238  }
239  ret = true;
240  } catch (Exception e) {
241  throw new NullPointerException(e.toString());
242  } finally {
243  return ret;
244  }
245  }

References SQlite.DBDRIVER, and ConfigProperties.getPropertyParam().

Referenced by TestsResource.initTest(), RetcatResource.setRetcatForVocabulary(), and RetcatResource.updateRetcatForVocabulary().

◆ deleteRetcatEntryForList()

static boolean deleteRetcatEntryForList ( String  vocabulary) throws ClassNotFoundException, IOException
static
282  {
283  boolean ret = false;
284  Class.forName(DBDRIVER);
285  try (Connection c = DriverManager.getConnection("jdbc:sqlite:" + ConfigProperties.getPropertyParam("sqlite"))) {
286  try (Statement stmt = c.createStatement()) {
287  String sql = "DELETE FROM retcatlist WHERE vocabulary = '" + vocabulary + "'";
288  stmt.executeUpdate(sql);
289  }
290  ret = true;
291  } catch (Exception e) {
292  throw new NullPointerException(e.toString());
293  } finally {
294  return ret;
295  }
296  }

References SQlite.DBDRIVER, and ConfigProperties.getPropertyParam().

Referenced by TestsResource.initTest(), RetcatResource.setRetcatForVocabularyForList(), and RetcatResource.updateRetcatForVocabularyForList().

◆ deleteStatisticsForVocabuary()

static void deleteStatisticsForVocabuary ( String  vocabID) throws ClassNotFoundException, IOException, SQliteException
static
330  {
331  Class.forName(DBDRIVER);
332  try (Connection c = DriverManager.getConnection("jdbc:sqlite:" + ConfigProperties.getPropertyParam("sqlite"))) {
333  try (Statement stmt = c.createStatement()) {
334  String sql = "DELETE FROM statistics WHERE vocabulary = '" + vocabID + "'";
335  stmt.executeUpdate(sql);
336  }
337  } catch (Exception e) {
338  throw new SQliteException("cannot delete statistics for vocabulary. " + e.toString());
339  }
340  }

References SQlite.DBDRIVER, and ConfigProperties.getPropertyParam().

Referenced by Transformer.writeVocabularyStatisticsToDatabase().

◆ getLoginStatus()

static String [] getLoginStatus ( String  userAndToken) throws SQLException, ClassNotFoundException, AccessDeniedException, IOException
static
179  {
180  String[] ret = new String[2];
181  Class.forName(DBDRIVER);
182  try (Connection c = DriverManager.getConnection("jdbc:sqlite:" + ConfigProperties.getPropertyParam("sqlite"))) {
183  try (Statement stmt = c.createStatement()) {
184  String sql = "SELECT * FROM login WHERE user_name = '" + userAndToken + "'";
185  try (ResultSet rs = stmt.executeQuery(sql)) {
186  while (rs.next()) {
187  ret[0] = rs.getString("role");
188  ret[1] = rs.getString("date");
189  }
190  }
191  }
192  } catch (Exception e) {
193  throw new NullPointerException(e.toString());
194  }
195  return ret;
196  }

References SQlite.DBDRIVER, and ConfigProperties.getPropertyParam().

Referenced by AuthResource.loginUser(), and AuthResource.statusUser().

◆ getRetcatByVocabulary()

static String getRetcatByVocabulary ( String  vocabulary) throws SQLException, ClassNotFoundException, AccessDeniedException, IOException
static
247  {
248  String ret = null;
249  Class.forName(DBDRIVER);
250  try (Connection c = DriverManager.getConnection("jdbc:sqlite:" + ConfigProperties.getPropertyParam("sqlite"))) {
251  try (Statement stmt = c.createStatement()) {
252  String sql = "SELECT retcat FROM retcat WHERE vocabulary = '" + vocabulary + "'";
253  try (ResultSet rs = stmt.executeQuery(sql)) {
254  while (rs.next()) {
255  ret = rs.getString("retcat");
256  }
257  }
258  }
259  } catch (Exception e) {
260  throw new NullPointerException(e.toString());
261  }
262  return ret;
263  }

References SQlite.DBDRIVER, and ConfigProperties.getPropertyParam().

Referenced by RetcatResource.getRetcatListByVocabulary(), RetcatResource.setRetcatForVocabulary(), and RetcatResource.updateRetcatForVocabulary().

◆ getRetcatByVocabularyForList()

static String getRetcatByVocabularyForList ( String  vocabulary) throws SQLException, ClassNotFoundException, AccessDeniedException, IOException
static
298  {
299  String ret = null;
300  Class.forName(DBDRIVER);
301  try (Connection c = DriverManager.getConnection("jdbc:sqlite:" + ConfigProperties.getPropertyParam("sqlite"))) {
302  try (Statement stmt = c.createStatement()) {
303  String sql = "SELECT list FROM retcatlist WHERE vocabulary = '" + vocabulary + "'";
304  try (ResultSet rs = stmt.executeQuery(sql)) {
305  while (rs.next()) {
306  ret = rs.getString("list");
307  }
308  }
309  }
310  } catch (Exception e) {
311  throw new NullPointerException(e.toString());
312  }
313  return ret;
314  }

References SQlite.DBDRIVER, and ConfigProperties.getPropertyParam().

Referenced by RetcatResource.getRetcatListByVocabularyForList(), RetcatResource.setRetcatForVocabularyForList(), and RetcatResource.updateRetcatForVocabularyForList().

◆ getStatisticsForVocabuary()

static JSONObject getStatisticsForVocabuary ( String  vocabulary) throws SQLException, ClassNotFoundException, AccessDeniedException, IOException, SQliteException
static
342  {
343  JSONObject statistics = new JSONObject();
344  JSONObject links = new JSONObject();
345  JSONObject labels = new JSONObject();
346  JSONObject descriptive = new JSONObject();
347  Class.forName(DBDRIVER);
348  try (Connection c = DriverManager.getConnection("jdbc:sqlite:" + ConfigProperties.getPropertyParam("sqlite"))) {
349  try (Statement stmt = c.createStatement()) {
350  String sql = "SELECT * FROM statistics WHERE vocabulary = '" + vocabulary + "'";
351  try (ResultSet rs = stmt.executeQuery(sql)) {
352  while (rs.next()) {
353  links.put("external", Integer.parseInt(rs.getString("linksexternal")));
354  links.put("internal", Integer.parseInt(rs.getString("linksinternal")));
355  links.put("count", Integer.parseInt(rs.getString("linkscount")));
356  statistics.put("links", links);
357  labels.put("count", Integer.parseInt(rs.getString("labelscount")));
358  statistics.put("labels", labels);
359  descriptive.put("wayback", Integer.parseInt(rs.getString("wayback")));
360  descriptive.put("descriptions", Integer.parseInt(rs.getString("descriptions")));
361  descriptive.put("translations", Integer.parseInt(rs.getString("translations")));
362  statistics.put("descriptive", descriptive);
363  statistics.put("lastModifyAction", rs.getString("lastModifyAction"));
364  }
365  }
366  }
367  } catch (Exception e) {
368  throw new SQliteException("cannot get statistics for vocabulary. " + e.toString());
369  }
370  return statistics;
371  }

References SQlite.DBDRIVER, and ConfigProperties.getPropertyParam().

Referenced by Transformer.vocabulary_GET().

◆ getUserInfo()

static JSONObject getUserInfo ( String  user_name) throws SQLException, ClassNotFoundException, AccessDeniedException, IOException
static
133  {
134  JSONObject userObject = new JSONObject();
135  Class.forName(DBDRIVER);
136  try (Connection c = DriverManager.getConnection("jdbc:sqlite:" + ConfigProperties.getPropertyParam("sqlite"))) {
137  try (Statement stmt = c.createStatement()) {
138  String sql = "SELECT * FROM users WHERE user_name = '" + user_name + "';";
139  try (ResultSet rs = stmt.executeQuery(sql)) {
140  while (rs.next()) {
141  userObject.put("username", rs.getString("user_name"));
142  userObject.put("role", rs.getString("role"));
143  if (rs.getString("activation_token").equals("-1")) {
144  userObject.put("status", "active");
145  } else {
146  userObject.put("status", "inactive");
147  }
148  }
149  }
150  }
151  return userObject;
152  } catch (Exception e) {
153  throw new AccessDeniedException(e.toString());
154  }
155  }

References SQlite.DBDRIVER, and ConfigProperties.getPropertyParam().

Referenced by AgentsResource.getAgent(), AgentsResource.getAgent_JSON(), AgentsResource.getAgents(), AgentsResource.postAgent(), and AgentsResource.updateAgent().

◆ getUserInfoAndCheckPassword()

static String getUserInfoAndCheckPassword ( String  user,
String  password 
) throws SQLException, ClassNotFoundException, AccessDeniedException, IOException
static
23  {
24  String ret = null;
25  String db_password = "";
26  String activation_token = "";
27  String role = "";
28  Class.forName(DBDRIVER);
29  try (Connection c = DriverManager.getConnection("jdbc:sqlite:" + ConfigProperties.getPropertyParam("sqlite"))) {
30  try (Statement stmt = c.createStatement()) {
31  String sql = "SELECT * FROM users WHERE user_name = '" + user + "'";
32  try (ResultSet rs = stmt.executeQuery(sql)) {
33  while (rs.next()) {
34  db_password = rs.getString("pwd");
35  activation_token = rs.getString("activation_token");
36  role = rs.getString("role");
37  }
38  }
39  }
40  // check password
41  String salt = db_password.substring(0, 25);
42  String echo = salt + Crypt.SHA1(salt + password);
43  if (echo.equals(db_password) && activation_token.equals("-1")) {
44  ret = role;
45  } else {
46  throw new AccessDeniedException();
47  }
48  } catch (Exception e) {
49  throw new AccessDeniedException(e.toString());
50  }
51  return ret;
52  }

References SQlite.DBDRIVER, ConfigProperties.getPropertyParam(), and Crypt.SHA1().

Referenced by AuthResource.loginUser().

◆ getUsersInfo()

static JSONArray getUsersInfo ( ) throws SQLException, ClassNotFoundException, AccessDeniedException, IOException
static
107  {
108  JSONArray users = new JSONArray();
109  Class.forName(DBDRIVER);
110  try (Connection c = DriverManager.getConnection("jdbc:sqlite:" + ConfigProperties.getPropertyParam("sqlite"))) {
111  try (Statement stmt = c.createStatement()) {
112  String sql = "SELECT * FROM users";
113  try (ResultSet rs = stmt.executeQuery(sql)) {
114  while (rs.next()) {
115  JSONObject userObject = new JSONObject();
116  userObject.put("username", rs.getString("user_name"));
117  userObject.put("role", rs.getString("role"));
118  if (rs.getString("activation_token").equals("-1")) {
119  userObject.put("status", "active");
120  } else {
121  userObject.put("status", "inactive");
122  }
123  users.add(userObject);
124  }
125  }
126  }
127  return users;
128  } catch (Exception e) {
129  throw new AccessDeniedException(e.toString());
130  }
131  }

References SQlite.DBDRIVER, and ConfigProperties.getPropertyParam().

◆ insertRetcatString()

static boolean insertRetcatString ( String  vocabulary,
String  retcatString 
) throws ClassNotFoundException, IOException
static
215  {
216  boolean ret = false;
217  Class.forName(DBDRIVER);
218  try (Connection c = DriverManager.getConnection("jdbc:sqlite:" + ConfigProperties.getPropertyParam("sqlite"))) {
219  try (Statement stmt = c.createStatement()) {
220  String sql = "INSERT INTO retcat (vocabulary,retcat) VALUES ('" + vocabulary + "','" + retcatString + "')";
221  stmt.executeUpdate(sql);
222  }
223  ret = true;
224  } catch (Exception e) {
225  throw new NullPointerException(e.toString());
226  } finally {
227  return ret;
228  }
229  }

References SQlite.DBDRIVER, and ConfigProperties.getPropertyParam().

Referenced by RetcatResource.setRetcatForVocabulary(), and RetcatResource.updateRetcatForVocabulary().

◆ insertRetcatStringForList()

static boolean insertRetcatStringForList ( String  vocabulary,
String  vocabID 
) throws ClassNotFoundException, IOException
static
266  {
267  boolean ret = false;
268  Class.forName(DBDRIVER);
269  try (Connection c = DriverManager.getConnection("jdbc:sqlite:" + ConfigProperties.getPropertyParam("sqlite"))) {
270  try (Statement stmt = c.createStatement()) {
271  String sql = "INSERT INTO retcatlist (vocabulary,list) VALUES ('" + vocabulary + "','" + vocabID + "')";
272  stmt.executeUpdate(sql);
273  }
274  ret = true;
275  } catch (Exception e) {
276  throw new NullPointerException(e.toString());
277  } finally {
278  return ret;
279  }
280  }

References SQlite.DBDRIVER, and ConfigProperties.getPropertyParam().

Referenced by RetcatResource.setRetcatForVocabularyForList(), and RetcatResource.updateRetcatForVocabularyForList().

◆ insertStatisticsForVocabuary()

static void insertStatisticsForVocabuary ( String  vocabID,
String  lastModifyAction,
int  wayback,
int  translations,
int  descriptions,
int  linksexternal,
int  linksinternal,
int  linkscount,
int  labelscount 
) throws ClassNotFoundException, IOException, SQliteException
static
316  {
317  Class.forName(DBDRIVER);
318  try (Connection c = DriverManager.getConnection("jdbc:sqlite:" + ConfigProperties.getPropertyParam("sqlite"))) {
319  try (Statement stmt = c.createStatement()) {
320  String sql = "INSERT INTO statistics "
321  + "(vocabulary,lastModifyAction,wayback,translations,descriptions,linksexternal,linksinternal,linkscount,labelscount) "
322  + "VALUES ('" + vocabID + "','" + lastModifyAction + "'," + wayback + "," + translations + "," + descriptions + "," + linksexternal + "," + linksinternal + "," + linkscount + "," + labelscount + ")";
323  stmt.executeUpdate(sql);
324  }
325  } catch (Exception e) {
326  throw new SQliteException("cannot insert statistics for vocabulary. " + e.toString());
327  }
328  }

References SQlite.DBDRIVER, and ConfigProperties.getPropertyParam().

Referenced by Transformer.writeVocabularyStatisticsToDatabase().

◆ insertUser()

static boolean insertUser ( String  user,
String  password 
) throws ClassNotFoundException, IOException, NoSuchAlgorithmException
static
54  {
55  // hash password
56  String salt = Crypt.generateHash();
57  password = salt + Crypt.SHA1(salt + password);
58  boolean ret = false;
59  Class.forName(DBDRIVER);
60  try (Connection c = DriverManager.getConnection("jdbc:sqlite:" + ConfigProperties.getPropertyParam("sqlite"))) {
61  try (Statement stmt = c.createStatement()) {
62  String sql = "INSERT INTO users (user_name,pwd,activation_token,role) VALUES ('" + user + "','" + password + "','" + "-1" + "','" + "user" + "')";
63  stmt.executeUpdate(sql);
64  }
65  ret = true;
66  } catch (Exception e) {
67  throw new NullPointerException(e.toString());
68  } finally {
69  return ret;
70  }
71  }

References SQlite.DBDRIVER, Crypt.generateHash(), ConfigProperties.getPropertyParam(), and Crypt.SHA1().

Referenced by AgentsResource.postAgent().

◆ setLogin()

static boolean setLogin ( String  user,
String  role 
) throws ClassNotFoundException, IOException, NoSuchAlgorithmException
static
158  {
159  boolean ret = false;
160  Calendar cal = Calendar.getInstance();
161  java.util.Date time = cal.getTime();
162  DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSSZ");
163  String d = formatter.format(time);
164  Class.forName(DBDRIVER);
165  try (Connection c = DriverManager.getConnection("jdbc:sqlite:" + ConfigProperties.getPropertyParam("sqlite"))) {
166  try (Statement stmt = c.createStatement()) {
167  String sql = "INSERT INTO login (user_name,role,date) VALUES ('" + user + "','" + role + "','" + d + "')";
168  stmt.executeUpdate(sql);
169  }
170  ret = true;
171  } catch (Exception e) {
172  System.out.println(e.toString());
173  throw new NullPointerException(e.toString());
174  } finally {
175  return ret;
176  }
177  }

References SQlite.DBDRIVER, and ConfigProperties.getPropertyParam().

Referenced by AuthResource.loginUser().

◆ setLogout()

static boolean setLogout ( String  user) throws ClassNotFoundException, IOException
static
198  {
199  boolean ret = false;
200  Class.forName(DBDRIVER);
201  try (Connection c = DriverManager.getConnection("jdbc:sqlite:" + ConfigProperties.getPropertyParam("sqlite"))) {
202  try (Statement stmt = c.createStatement()) {
203  String sql = "DELETE FROM login WHERE user_name LIKE '" + user + ";%'";
204  stmt.executeUpdate(sql);
205  }
206  ret = true;
207  } catch (Exception e) {
208  throw new NullPointerException(e.toString());
209  } finally {
210  return ret;
211  }
212  }

References SQlite.DBDRIVER, and ConfigProperties.getPropertyParam().

Referenced by AuthResource.logoutUser().

Member Data Documentation

◆ DBDRIVER

final String DBDRIVER = "org.sqlite.JDBC"
staticprivate

Referenced by SQlite.activateUser(), SQlite.deactivateUser(), SQlite.deleteRetcatEntry(), SQlite.deleteRetcatEntryForList(), SQlite.deleteStatisticsForVocabuary(), SQlite.getLoginStatus(), SQlite.getRetcatByVocabulary(), SQlite.getRetcatByVocabularyForList(), SQlite.getStatisticsForVocabuary(), SQlite.getUserInfo(), SQlite.getUserInfoAndCheckPassword(), SQlite.getUsersInfo(), SQlite.insertRetcatString(), SQlite.insertRetcatStringForList(), SQlite.insertStatisticsForVocabuary(), SQlite.insertUser(), SQlite.setLogin(), and SQlite.setLogout().

v1.utils.db.SQlite.DBDRIVER
static final String DBDRIVER
Definition: SQlite.java:20
Exception