1 /*
2 *
3 * Fosstrak LLRP Commander (www.fosstrak.org)
4 *
5 * Copyright (C) 2008 ETH Zurich
6 *
7 * This program is free software: you can redistribute it and/or modify
8 * it under the terms of the GNU General Public License as published by
9 * the Free Software Foundation, either version 3 of the License, or
10 * (at your option) any later version.
11 *
12 * This program is distributed in the hope that it will be useful,
13 * but WITHOUT ANY WARRANTY; without even the implied warranty of
14 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
15 * GNU General Public License for more details.
16 *
17 * You should have received a copy of the GNU General Public License
18 * along with this program. If not, see <http://www.gnu.org/licenses/>
19 *
20 */
21
22 package org.fosstrak.llrp.client.repository.sql.roaccess;
23
24 import org.fosstrak.llrp.client.Repository;
25 import org.fosstrak.llrp.client.repository.sql.DerbyRepository;
26 import org.fosstrak.llrp.client.repository.sql.MySQLRepository;
27
28 /**
29 * Derby and MySQL implementation for the RO_ACCESS_REPORTS repository (table).
30 * Currently this implementation is used from two {@link Repository} - namely
31 * {@link DerbyRepository} and {@link MySQLRepository}.
32 * @author sawielan
33 *
34 */
35 public class DerbyROAccessReportsRepository extends AbstractSQLROAccessReportsRepository {
36
37 /**
38 * the columns of the RO_ACCESS_REPORTS table and the data types used in the
39 * database to store the values from the LLRP message. The first entry
40 * in the two dimensional array encodes the name of the db column, the
41 * second entry reflects the data type chosen.<br/>
42 * <strong>NOTICE:</strong> As java and MySQL both do not support unsigned
43 * values, we need to allocate extra large signed data types to store the
44 * unsigned ones. the allocation mapping is given below:<br/>
45 * <ul>
46 * <li>unsigned integer -> long -> BIGINT (8Byte value in derby)</li>
47 * <li>unsigned short -> integer -> INTEGER (4Byte value in derby)</li>
48 * <li>byte -> byte -> SMALLINT (2Byte value in derby)</li>
49 * </ul>
50 */
51 public static final String[][] COLUMN_NAMES_AND_TYPES = new String[][] {
52 {"LOG_Time", "TIMESTAMP"},
53 {"Adapter", "CHAR(64)"},
54 {"Reader", "CHAR(64)"},
55 {"EPC", "VARCHAR (2048)"}, // allow variable length
56 // NOTICE THAT DERBY DOES NOT PAD TO THE GIVEN LENGTH
57 {"ROSpecID", "BIGINT"}, // IN SPEC: UNSIGNED INTEGER
58 {"SpecIndex", "INTEGER"}, // IN SPEC: UNSIGNED SHORT
59 {"InventoryParameterSpecID", "INTEGER"}, // IN SPEC: UNSIGNED SHORT
60 {"AntennaID", "INTEGER"}, // IN SPEC: UNSIGNED SHORT
61 {"PeakRSSI", "SMALLINT"}, // IN SPEC: BYTE
62 {"ChannelIndex", "INTEGER"},// IN SPEC: UNSIGNED SHORT
63 {"FirstSeenTimestampUTC", "TIMESTAMP"}, // IN SPEC: UNSIGNED LONG MICROSECONDS TIMESTAMP
64 {"FirstSeenTimestampUptime", "TIMESTAMP"}, // IN SPEC: UNSIGNED LONG MICROSECONDS TIMESTAMP
65 {"LastSeenTimestampUTC", "TIMESTAMP"}, // IN SPEC: UNSIGNED LONG MICROSECONDS TIMESTAMP
66 {"LastSeenTimestampUptime", "TIMESTAMP"}, // IN SPEC: UNSIGNED LONG MICROSECONDS TIMESTAMP
67 {"TagSeenCount", "INTEGER"}, // IN SPEC: UNSIGNED SHORT
68 {"C1G2_CRC", "INTEGER"}, // IN SPEC: UNSIGNED SHORT
69 {"C1G2_PC", "INTEGER"}, // IN SPEC: UNSIGNED SHORT
70 {"AccessSpecID", "BIGINT"} // IN SPEC: UNSIGNED INTEGER
71 };
72
73 @Override
74 protected String sqlCreateTable() {
75 String fields = "";
76 final int len = COLUMN_NAMES_AND_TYPES.length;
77 final int lenm = len - 1;
78 for (int i=0; i<len; i++) {
79 fields += String.format("%s %s",
80 COLUMN_NAMES_AND_TYPES[i][0],
81 COLUMN_NAMES_AND_TYPES[i][1]);
82 // append a comma, if not last entry
83 if (i < lenm) fields += ",";
84 }
85 return String.format(
86 "create table %s (%s)", TABLE_RO_ACCESS_REPORTS, fields);
87 }
88
89 @Override
90 protected String sqlInsert() {
91 return String.format("insert into %s values ",
92 TABLE_RO_ACCESS_REPORTS) +
93 "(?, ?, ?, ?, ?, ?, ?, ?, " +
94 "?, ?, ?, ?, ?, ?, ?, ?, " +
95 "?, ?)";
96 }
97
98 @Override
99 protected String sqlDropTable() {
100 return String.format("DROP TABLE %s", TABLE_RO_ACCESS_REPORTS);
101 }
102
103 public DerbyROAccessReportsRepository() {
104 super();
105 }
106 }