List of usage examples for com.badlogic.gdx.sql SQLiteGdxDatabaseCursor getLong
public long getLong(int columnIndex);
From source file:de.longri.cachebox3.sqlite.dao.CacheDAO.java
License:Open Source License
Cache ReadFromCursor(SQLiteGdxDatabaseCursor reader, boolean fullDetails, boolean withDescription) { try {// w ww . j a v a2 s.c o m Cache cache = new Cache(reader.getDouble(2), reader.getDouble(3), fullDetails); cache.Id = reader.getLong(0); cache.setGcCode(reader.getString(1).trim()); cache.setName(reader.getString(4).trim()); cache.Size = CacheSizes.parseInt(reader.getInt(5)); cache.setDifficulty(((float) reader.getShort(6)) / 2); cache.setTerrain(((float) reader.getShort(7)) / 2); cache.setArchived(reader.getInt(8) != 0); cache.setAvailable(reader.getInt(9) != 0); cache.setFound(reader.getInt(10) != 0); cache.Type = CacheTypes.values()[reader.getShort(11)]; cache.setOwner(reader.getString(12).trim()); cache.NumTravelbugs = reader.getInt(13); cache.setGcId(reader.getString(14)); cache.Rating = (reader.getShort(15)) / 100.0f; if (reader.getInt(16) > 0) cache.setFavorite(true); else cache.setFavorite(false); if (reader.getInt(17) > 0) cache.setHasUserData(true); else cache.setHasUserData(false); if (reader.getInt(18) > 0) cache.setListingChanged(true); else cache.setListingChanged(false); if (reader.getInt(19) > 0) cache.setCorrectedCoordinates(true); else cache.setCorrectedCoordinates(false); if (fullDetails) { readDetailFromCursor(reader, cache.detail, fullDetails, withDescription); } return cache; } catch (Exception exc) { log.error("Read Cache", exc); return null; } }
From source file:de.longri.cachebox3.sqlite.dao.CacheDAO.java
License:Open Source License
private boolean readDetailFromCursor(SQLiteGdxDatabaseCursor reader, CacheDetail detail, boolean withReaderOffset, boolean withDescription) { // Reader includes Compleate Cache or Details only int readerOffset = withReaderOffset ? 20 : 0; detail.PlacedBy = reader.getString(readerOffset + 0).trim(); if (reader.isNull(readerOffset + 5)) detail.ApiStatus = Cache.NOTLIVE; else//from w ww . ja va 2 s . c om detail.ApiStatus = (byte) reader.getInt(readerOffset + 5); String sDate = reader.getString(readerOffset + 1); DateFormat iso8601Format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); try { detail.DateHidden = iso8601Format.parse(sDate); } catch (ParseException e) { e.printStackTrace(); } detail.Url = reader.getString(readerOffset + 2).trim(); if (reader.getString(readerOffset + 3) != null) detail.TourName = reader.getString(readerOffset + 3).trim(); else detail.TourName = ""; if (reader.getString(readerOffset + 4) != "") detail.GPXFilename_ID = reader.getLong(readerOffset + 4); else detail.GPXFilename_ID = -1; detail.setAttributesPositive(new DLong(reader.getLong(readerOffset + 7), reader.getLong(readerOffset + 6))); detail.setAttributesNegative(new DLong(reader.getLong(readerOffset + 9), reader.getLong(readerOffset + 8))); if (reader.getString(readerOffset + 10) != null) detail.setHint(reader.getString(readerOffset + 10).trim()); else detail.setHint(""); if (withDescription) { detail.longDescription = reader.getString(readerOffset + 11); detail.tmpSolver = reader.getString(readerOffset + 12); detail.tmpNote = reader.getString(readerOffset + 13); detail.shortDescription = reader.getString(readerOffset + 14); } return true; }
From source file:de.longri.cachebox3.sqlite.dao.CacheListDAO.java
License:Open Source License
public CacheList ReadCacheList(CacheList cacheList, String join, String where, boolean withDescription, boolean fullDetails, boolean loadAllWaypoints) { if (cacheList == null) return null; // Clear List before read cacheList.clear();//from w w w.jav a2 s . c o m boolean error = false; log.trace("ReadCacheList 1.Waypoints"); LongMap<CB_List<Waypoint>> waypoints = new LongMap<CB_List<Waypoint>>(); // zuerst alle Waypoints einlesen CB_List<Waypoint> wpList = new CB_List<Waypoint>(); long aktCacheID = -1; String sql = fullDetails ? WaypointDAO.SQL_WP_FULL : WaypointDAO.SQL_WP; if (!((fullDetails || loadAllWaypoints))) { // when CacheList should be loaded without full details and without all Waypoints // do not load all waypoints from db! sql += " WHERE IsStart=\"true\" or Type=" + CacheTypes.Final.ordinal(); // StartWaypoint or CacheTypes.Final } sql += " ORDER BY 'CacheId'"; SQLiteGdxDatabaseCursor reader = Database.Data.rawQuery(sql, null); if (reader == null) return cacheList; reader.moveToFirst(); while (!reader.isAfterLast()) { WaypointDAO waypointDAO = new WaypointDAO(); Waypoint wp = waypointDAO.getWaypoint(reader, fullDetails); if (!(fullDetails || loadAllWaypoints)) { // wenn keine FullDetails geladen werden sollen dann sollen nur die Finals und Start-Waypoints geladen werden if (!(wp.IsStart || wp.Type == CacheTypes.Final)) { reader.moveToNext(); continue; } } if (wp.CacheId != aktCacheID) { aktCacheID = wp.CacheId; wpList = new CB_List<Waypoint>(); waypoints.put(aktCacheID, wpList); } wpList.add(wp); reader.moveToNext(); } reader.close(); log.debug(wpList.size() + " Waypoints readed!"); log.debug("ReadCacheList 2.Caches"); try { if (fullDetails) { sql = SQL.SQL_GET_CACHE + ", " + SQL.SQL_DETAILS; if (withDescription) { // load Cache with Description, Solver, Notes for Transfering Data from Server to ACB sql += "," + SQL.SQL_GET_DETAIL_WITH_DESCRIPTION; } } else { sql = SQL.SQL_GET_CACHE; } sql += " FROM `Caches` AS `c` " + join + " " + ((where.length() > 0) ? "WHERE " + where : where); reader = Database.Data.rawQuery(sql, null); } catch (Exception e) { log.error("CacheList.LoadCaches() sql+ \n" + sql, e); error = true; } if (!error) { reader.moveToFirst(); CacheDAO cacheDAO = new CacheDAO(); while (!reader.isAfterLast()) { Cache cache = cacheDAO.ReadFromCursor(reader, fullDetails, withDescription); boolean doAdd = true; if (FilterInstances.hasCorrectedCoordinates != 0) { if (waypoints.containsKey(cache.Id)) { CB_List<Waypoint> tmpwaypoints = waypoints.get(cache.Id); for (int i = 0, n = tmpwaypoints.size(); i < n; i++) { cache.waypoints.add(tmpwaypoints.get(i)); } } boolean hasCorrectedCoordinates = cache.CorrectedCoordiantesOrMysterySolved(); if (FilterInstances.hasCorrectedCoordinates < 0) { // show only those without corrected ones if (hasCorrectedCoordinates) doAdd = false; } else if (FilterInstances.hasCorrectedCoordinates > 0) { // only those with corrected ones if (!hasCorrectedCoordinates) doAdd = false; } } if (doAdd) { cacheList.add(cache); cache.waypoints.clear(); if (waypoints.containsKey(cache.Id)) { CB_List<Waypoint> tmpwaypoints = waypoints.get(cache.Id); for (int i = 0, n = tmpwaypoints.size(); i < n; i++) { cache.waypoints.add(tmpwaypoints.get(i)); } waypoints.remove(cache.Id); } } // ++Global.CacheCount; reader.moveToNext(); } reader.close(); } else { log.error("Corrupt database try cache by cache"); // get all id's reader = Database.Data.rawQuery(SQL.SQL_ALL_CACHE_IDS, null); reader.moveToFirst(); ArrayList<Long> idList = new ArrayList<Long>(reader.getCount()); while (!reader.isAfterLast()) { idList.add(reader.getLong(0)); reader.moveToNext(); } CacheDAO cacheDAO = new CacheDAO(); for (Long id : idList) { Cache cache = null; try { cache = cacheDAO.getFromDbByCacheId(id); } catch (Exception e) { log.error("Can't read Cache (id:" + id + ") from database."); try { Database.Data.delete("Caches", "id=" + id, null); } catch (Exception e1) { log.error("Can't delete this Cache. Skip it!"); } continue; } boolean doAdd = true; if (FilterInstances.hasCorrectedCoordinates != 0) { if (waypoints.containsKey(cache.Id)) { CB_List<Waypoint> tmpwaypoints = waypoints.get(cache.Id); for (int i = 0, n = tmpwaypoints.size(); i < n; i++) { cache.waypoints.add(tmpwaypoints.get(i)); } } boolean hasCorrectedCoordinates = cache.CorrectedCoordiantesOrMysterySolved(); if (FilterInstances.hasCorrectedCoordinates < 0) { // show only those without corrected ones if (hasCorrectedCoordinates) doAdd = false; } else if (FilterInstances.hasCorrectedCoordinates > 0) { // only those with corrected ones if (!hasCorrectedCoordinates) doAdd = false; } } if (doAdd) { cacheList.add(cache); cache.waypoints.clear(); if (waypoints.containsKey(cache.Id)) { CB_List<Waypoint> tmpwaypoints = waypoints.get(cache.Id); for (int i = 0, n = tmpwaypoints.size(); i < n; i++) { cache.waypoints.add(tmpwaypoints.get(i)); } waypoints.remove(cache.Id); } } } } // clear other never used WP`s from Mem waypoints.clear(); waypoints = null; // do it manual (or automated after fix), got hanging app on startup // log.debug( "ReadCacheList 3.Sorting"); try { // Collections.sort(cacheList); } catch ( Exception e) { // log.error( "CacheListDAO.ReadCacheList()", "Sort ERROR", e); } // log.debug( "ReadCacheList 4. ready"); return cacheList; }
From source file:de.longri.cachebox3.sqlite.dao.CategoryDAO.java
License:Open Source License
public Category ReadFromCursor(SQLiteGdxDatabaseCursor reader) { Category result = new Category(); result.Id = reader.getLong(0); result.GpxFilename = reader.getString(1); result.pinned = reader.getInt(2) != 0; // alle GpxFilenames einlesen SQLiteGdxDatabaseCursor reader2 = Database.Data.rawQuery( "select ID, GPXFilename, Imported, CacheCount from GpxFilenames where CategoryId=?", new String[] { String.valueOf(result.Id) }); reader2.moveToFirst();/* w w w .j a v a2 s. c o m*/ while (reader2.isAfterLast() == false) { GpxFilenameDAO gpxFilenameDAO = new GpxFilenameDAO(); GpxFilename gpx = gpxFilenameDAO.ReadFromCursor(reader2); result.add(gpx); reader2.moveToNext(); } reader2.close(); return result; }
From source file:de.longri.cachebox3.sqlite.dao.CategoryDAO.java
License:Open Source License
public Category CreateNewCategory(String filename) { filename = new File(filename).getName(); // neue Category in DB anlegen Category result = new Category(); Parameters args = new Parameters(); args.put("GPXFilename", filename); try {/*from ww w .j a v a 2 s. c om*/ Database.Data.insert("Category", args); } catch (Exception exc) { log.error(filename, exc); } long Category_ID = 0; SQLiteGdxDatabaseCursor reader = Database.Data.rawQuery("Select max(ID) from Category", null); reader.moveToFirst(); if (reader.isAfterLast() == false) { Category_ID = reader.getLong(0); } reader.close(); result.Id = Category_ID; result.GpxFilename = filename; result.Checked = true; result.pinned = false; return result; }
From source file:de.longri.cachebox3.sqlite.dao.CategoryDAO.java
License:Open Source License
public GpxFilename CreateNewGpxFilename(Category category, String filename) { filename = new File(filename).getName(); Parameters args = new Parameters(); args.put("GPXFilename", filename); args.put("CategoryId", category.Id); DateFormat iso8601Format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); String stimestamp = iso8601Format.format(new Date()); args.put("Imported", stimestamp); try {/*from w w w. ja v a2 s . com*/ Database.Data.insert("GpxFilenames", args); } catch (Exception exc) { log.error(filename, exc); } long GPXFilename_ID = 0; SQLiteGdxDatabaseCursor reader = Database.Data.rawQuery("Select max(ID) from GpxFilenames", null); reader.moveToFirst(); if (reader.isAfterLast() == false) { GPXFilename_ID = reader.getLong(0); } reader.close(); GpxFilename result = new GpxFilename(GPXFilename_ID, filename, category.Id); category.add(result); return result; }
From source file:de.longri.cachebox3.sqlite.dao.GpxFilenameDAO.java
License:Open Source License
public GpxFilename ReadFromCursor(SQLiteGdxDatabaseCursor reader) { long id;/* w w w.j a va2 s . c om*/ String gpxFileName; long categoryId = -1; id = reader.getLong(0); gpxFileName = reader.getString(1); GpxFilename result = new GpxFilename(id, gpxFileName, categoryId); if (reader.isNull(2)) result.Imported = new Date(); else { String sDate = reader.getString(2); DateFormat iso8601Format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); try { result.Imported = iso8601Format.parse(sDate); } catch (ParseException e) { result.Imported = new Date(); } } if (reader.isNull(3)) result.CacheCount = 0; else result.CacheCount = reader.getInt(3); return result; }
From source file:de.longri.cachebox3.sqlite.dao.WaypointDAO.java
License:Open Source License
/** * Create Waypoint Object from Reader.//from ww w. jav a 2s . co m * * @param reader * @param full Waypoints as FullWaypoints (true) or Waypoint (false) * @return */ public Waypoint getWaypoint(SQLiteGdxDatabaseCursor reader, boolean full) { Waypoint WP = null; WP = new Waypoint(reader.getDouble(2), reader.getDouble(3), full); WP.setGcCode(reader.getString(0)); WP.CacheId = reader.getLong(1); WP.Type = CacheTypes.values()[reader.getShort(4)]; WP.IsSyncExcluded = reader.getInt(5) == 1; WP.IsUserWaypoint = reader.getInt(6) == 1; WP.setTitle(reader.getString(7).trim()); WP.IsStart = reader.getInt(8) == 1; if (full) { WP.setClue(reader.getString(10)); WP.setDescription(reader.getString(9)); WP.setCheckSum(createCheckSum(WP)); } return WP; }
From source file:de.longri.cachebox3.sqlite.Database.java
License:Open Source License
/** * @return Set To CB.Categories// ww w . j a v a 2s . c o m */ public Categories GPXFilenameUpdateCacheCount() { // welche GPXFilenamen sind in der DB erfasst beginTransaction(); try { SQLiteGdxDatabaseCursor reader = rawQuery( "select GPXFilename_ID, Count(*) as CacheCount from Caches where GPXFilename_ID is not null Group by GPXFilename_ID", null); reader.moveToFirst(); while (reader.isAfterLast() == false) { long GPXFilename_ID = reader.getLong(0); long CacheCount = reader.getLong(1); Parameters val = new Parameters(); val.put("CacheCount", CacheCount); update("GPXFilenames", val, "ID = " + GPXFilename_ID, null); reader.moveToNext(); } delete("GPXFilenames", "Cachecount is NULL or CacheCount = 0", null); delete("GPXFilenames", "ID not in (Select GPXFilename_ID From Caches)", null); reader.close(); setTransactionSuccessful(); } catch (Exception e) { } finally { endTransaction(); } //TODO ??? Categories categories = new Categories(); return categories; }
From source file:de.longri.cachebox3.sqlite.Database.java
License:Open Source License
protected void AlterDatabase(int lastDatabaseSchemeVersion) { switch (databaseType) { case CacheBox: beginTransaction();//from w ww . ja va 2s . co m try { if (lastDatabaseSchemeVersion <= 0) { // First Initialization of the Database execSQL("CREATE TABLE [Caches] ([Id] bigint NOT NULL primary key,[GcCode] nvarchar (12) NULL,[GcId] nvarchar (255) NULL,[Latitude] float NULL,[Longitude] float NULL,[Name] nchar (255) NULL,[Size] int NULL,[Difficulty] smallint NULL,[Terrain] smallint NULL,[Archived] bit NULL,[Available] bit NULL,[Found] bit NULL,[Type] smallint NULL,[PlacedBy] nvarchar (255) NULL,[Owner] nvarchar (255) NULL,[DateHidden] datetime NULL,[Hint] ntext NULL,[Description] ntext NULL,[Url] nchar (255) NULL,[NumTravelbugs] smallint NULL,[Rating] smallint NULL,[Vote] smallint NULL,[VotePending] bit NULL,[Notes] ntext NULL,[Solver] ntext NULL,[Favorit] bit NULL,[AttributesPositive] bigint NULL,[AttributesNegative] bigint NULL,[TourName] nchar (255) NULL,[GPXFilename_Id] bigint NULL,[HasUserData] bit NULL,[ListingCheckSum] int NULL DEFAULT 0,[ListingChanged] bit NULL,[ImagesUpdated] bit NULL,[DescriptionImagesUpdated] bit NULL,[CorrectedCoordinates] bit NULL);"); execSQL("CREATE INDEX [archived_idx] ON [Caches] ([Archived] ASC);"); execSQL("CREATE INDEX [AttributesNegative_idx] ON [Caches] ([AttributesNegative] ASC);"); execSQL("CREATE INDEX [AttributesPositive_idx] ON [Caches] ([AttributesPositive] ASC);"); execSQL("CREATE INDEX [available_idx] ON [Caches] ([Available] ASC);"); execSQL("CREATE INDEX [Difficulty_idx] ON [Caches] ([Difficulty] ASC);"); execSQL("CREATE INDEX [Favorit_idx] ON [Caches] ([Favorit] ASC);"); execSQL("CREATE INDEX [found_idx] ON [Caches] ([Found] ASC);"); execSQL("CREATE INDEX [GPXFilename_Id_idx] ON [Caches] ([GPXFilename_Id] ASC);"); execSQL("CREATE INDEX [HasUserData_idx] ON [Caches] ([HasUserData] ASC);"); execSQL("CREATE INDEX [ListingChanged_idx] ON [Caches] ([ListingChanged] ASC);"); execSQL("CREATE INDEX [NumTravelbugs_idx] ON [Caches] ([NumTravelbugs] ASC);"); execSQL("CREATE INDEX [placedby_idx] ON [Caches] ([PlacedBy] ASC);"); execSQL("CREATE INDEX [Rating_idx] ON [Caches] ([Rating] ASC);"); execSQL("CREATE INDEX [Size_idx] ON [Caches] ([Size] ASC);"); execSQL("CREATE INDEX [Terrain_idx] ON [Caches] ([Terrain] ASC);"); execSQL("CREATE INDEX [Type_idx] ON [Caches] ([Type] ASC);"); execSQL("CREATE TABLE [CelltowerLocation] ([CellId] nvarchar (20) NOT NULL primary key,[Latitude] float NULL,[Longitude] float NULL);"); execSQL("CREATE TABLE [GPXFilenames] ([Id] integer not null primary key autoincrement,[GPXFilename] nvarchar (255) NULL,[Imported] datetime NULL, [Name] nvarchar (255) NULL,[CacheCount] int NULL);"); execSQL("CREATE TABLE [Logs] ([Id] bigint NOT NULL primary key, [CacheId] bigint NULL,[Timestamp] datetime NULL,[Finder] nvarchar (128) NULL,[Type] smallint NULL,[Comment] ntext NULL);"); execSQL("CREATE INDEX [log_idx] ON [Logs] ([CacheId] ASC);"); execSQL("CREATE INDEX [timestamp_idx] ON [Logs] ([Timestamp] ASC);"); execSQL("CREATE TABLE [PocketQueries] ([Id] integer not null primary key autoincrement,[PQName] nvarchar (255) NULL,[CreationTimeOfPQ] datetime NULL);"); execSQL("CREATE TABLE [Waypoint] ([GcCode] nvarchar (12) NOT NULL primary key,[CacheId] bigint NULL,[Latitude] float NULL,[Longitude] float NULL,[Description] ntext NULL,[Clue] ntext NULL,[Type] smallint NULL,[SyncExclude] bit NULL,[UserWaypoint] bit NULL,[Title] ntext NULL);"); execSQL("CREATE INDEX [UserWaypoint_idx] ON [Waypoint] ([UserWaypoint] ASC);"); execSQL("CREATE TABLE [Config] ([Key] nvarchar (30) NOT NULL, [Value] nvarchar (255) NULL);"); execSQL("CREATE INDEX [Key_idx] ON [Config] ([Key] ASC);"); execSQL("CREATE TABLE [Replication] ([Id] integer not null primary key autoincrement, [ChangeType] int NOT NULL, [CacheId] bigint NOT NULL, [WpGcCode] nvarchar (12) NULL, [SolverCheckSum] int NULL, [NotesCheckSum] int NULL, [WpCoordCheckSum] int NULL);"); execSQL("CREATE INDEX [Replication_idx] ON [Replication] ([Id] ASC);"); execSQL("CREATE INDEX [ReplicationCache_idx] ON [Replication] ([CacheId] ASC);"); } if (lastDatabaseSchemeVersion < 1003) { execSQL("CREATE TABLE [Locations] ([Id] integer not null primary key autoincrement, [Name] nvarchar (255) NULL, [Latitude] float NULL, [Longitude] float NULL);"); execSQL("CREATE INDEX [Locatioins_idx] ON [Locations] ([Id] ASC);"); execSQL("CREATE TABLE [SdfExport] ([Id] integer not null primary key autoincrement, [Description] nvarchar(255) NULL, [ExportPath] nvarchar(255) NULL, [MaxDistance] float NULL, [LocationID] Bigint NULL, [Filter] ntext NULL, [Update] bit NULL, [ExportImages] bit NULL, [ExportSpoilers] bit NULL, [ExportMaps] bit NULL, [OwnRepository] bit NULL, [ExportMapPacks] bit NULL, [MaxLogs] int NULL);"); execSQL("CREATE INDEX [SdfExport_idx] ON [SdfExport] ([Id] ASC);"); execSQL("ALTER TABLE [CACHES] ADD [FirstImported] datetime NULL;"); execSQL("CREATE TABLE [Category] ([Id] integer not null primary key autoincrement, [GpxFilename] nvarchar(255) NULL, [Pinned] bit NULL default 0, [CacheCount] int NULL);"); execSQL("CREATE INDEX [Category_idx] ON [Category] ([Id] ASC);"); execSQL("ALTER TABLE [GpxFilenames] ADD [CategoryId] bigint NULL;"); execSQL("ALTER TABLE [Caches] add [state] nvarchar(50) NULL;"); execSQL("ALTER TABLE [Caches] add [country] nvarchar(50) NULL;"); } if (lastDatabaseSchemeVersion < 1015) { // GpxFilenames mit Kategorien verknpfen // alte Category Tabelle lschen delete("Category", "", null); HashMap<Long, String> gpxFilenames = new HashMap<Long, String>(); HashMap<String, Long> categories = new HashMap<String, Long>(); try { SQLiteGdxDatabaseCursor reader = rawQuery("select ID, GPXFilename from GPXFilenames", null); reader.moveToFirst(); while (!reader.isAfterLast()) { long id = reader.getLong(0); String gpxFilename = reader.getString(1); gpxFilenames.put(id, gpxFilename); reader.moveToNext(); } reader.close(); } catch (Exception e) { //no GPXFilenames stored } for (Entry<Long, String> entry : gpxFilenames.entrySet()) { if (!categories.containsKey(entry.getValue())) { // add new Category Categories cs = new Categories(); Category category = cs.createNewCategory(entry.getValue()); // and store categories.put(entry.getValue(), category.Id); } if (categories.containsKey(entry.getValue())) { // and store CategoryId in GPXFilenames Parameters args = new Parameters(); args.put("CategoryId", categories.get(entry.getValue())); try { Database.Data.update("GpxFilenames", args, "Id=" + entry.getKey(), null); } catch (Exception exc) { log.error("Update_CategoryId", exc); } } } } if (lastDatabaseSchemeVersion < 1016) { execSQL("ALTER TABLE [CACHES] ADD [ApiStatus] smallint NULL default 0;"); } if (lastDatabaseSchemeVersion < 1017) { execSQL("CREATE TABLE [Trackable] ([Id] integer not null primary key autoincrement, [Archived] bit NULL, [GcCode] nvarchar (12) NULL, [CacheId] bigint NULL, [CurrentGoal] ntext, [CurrentOwnerName] nvarchar (255) NULL, [DateCreated] datetime NULL, [Description] ntext, [IconUrl] nvarchar (255) NULL, [ImageUrl] nvarchar (255) NULL, [path] nvarchar (255) NULL, [OwnerName] nvarchar (255), [Url] nvarchar (255) NULL);"); execSQL("CREATE INDEX [cacheid_idx] ON [Trackable] ([CacheId] ASC);"); execSQL("CREATE TABLE [TbLogs] ([Id] integer not null primary key autoincrement, [TrackableId] integer not NULL, [CacheID] bigint NULL, [GcCode] nvarchar (12) NULL, [LogIsEncoded] bit NULL DEFAULT 0, [LogText] ntext, [LogTypeId] bigint NULL, [LoggedByName] nvarchar (255) NULL, [Visited] datetime NULL);"); execSQL("CREATE INDEX [trackableid_idx] ON [TbLogs] ([TrackableId] ASC);"); execSQL("CREATE INDEX [trackablecacheid_idx] ON [TBLOGS] ([CacheId] ASC);"); } if (lastDatabaseSchemeVersion < 1018) { execSQL("ALTER TABLE [SdfExport] ADD [MapPacks] nvarchar(512) NULL;"); } if (lastDatabaseSchemeVersion < 1019) { // neue Felder fr die erweiterten Attribute einfgen execSQL("ALTER TABLE [CACHES] ADD [AttributesPositiveHigh] bigint NULL default 0"); execSQL("ALTER TABLE [CACHES] ADD [AttributesNegativeHigh] bigint NULL default 0"); // Die Nummerierung der Attribute stimmte nicht mit der von // Groundspeak berein. Bei 16 und 45 wurde jeweils eine // Nummber bersprungen try { SQLiteGdxDatabaseCursor reader = rawQuery( "select Id, AttributesPositive, AttributesNegative from Caches", new String[] {}); reader.moveToFirst(); while (!reader.isAfterLast()) { long id = reader.getLong(0); long attributesPositive = reader.getLong(1); long attributesNegative = reader.getLong(2); attributesPositive = convertAttribute(attributesPositive); attributesNegative = convertAttribute(attributesNegative); Parameters val = new Parameters(); val.put("AttributesPositive", attributesPositive); val.put("AttributesNegative", attributesNegative); String whereClause = "[Id]=" + id; update("Caches", val, whereClause, null); reader.moveToNext(); } reader.close(); } catch (Exception e) { // no attributes stored } } if (lastDatabaseSchemeVersion < 1020) { // for long Settings execSQL("ALTER TABLE [Config] ADD [LongString] ntext NULL;"); } if (lastDatabaseSchemeVersion < 1021) { // Image Table execSQL("CREATE TABLE [Images] ([Id] integer not null primary key autoincrement, [CacheId] bigint NULL, [GcCode] nvarchar (12) NULL, [Description] ntext, [Name] nvarchar (255) NULL, [ImageUrl] nvarchar (255) NULL, [IsCacheImage] bit NULL);"); execSQL("CREATE INDEX [images_cacheid_idx] ON [Images] ([CacheId] ASC);"); execSQL("CREATE INDEX [images_gccode_idx] ON [Images] ([GcCode] ASC);"); execSQL("CREATE INDEX [images_iscacheimage_idx] ON [Images] ([IsCacheImage] ASC);"); execSQL("CREATE UNIQUE INDEX [images_imageurl_idx] ON [Images] ([ImageUrl] ASC);"); } if (lastDatabaseSchemeVersion < 1022) { execSQL("ALTER TABLE [Caches] ALTER COLUMN [GcCode] nvarchar(15) NOT NULL; "); execSQL("ALTER TABLE [Waypoint] DROP CONSTRAINT Waypoint_PK "); execSQL("ALTER TABLE [Waypoint] ALTER COLUMN [GcCode] nvarchar(15) NOT NULL; "); execSQL("ALTER TABLE [Waypoint] ADD CONSTRAINT [Waypoint_PK] PRIMARY KEY ([GcCode]); "); execSQL("ALTER TABLE [Replication] ALTER COLUMN [WpGcCode] nvarchar(15) NOT NULL; "); execSQL("ALTER TABLE [Trackable] ALTER COLUMN [GcCode] nvarchar(15) NOT NULL; "); execSQL("ALTER TABLE [TbLogs] ALTER COLUMN [GcCode] nvarchar(15) NOT NULL; "); execSQL("ALTER TABLE [Images] ALTER COLUMN [GcCode] nvarchar(15) NOT NULL; "); } if (lastDatabaseSchemeVersion < 1024) { execSQL("ALTER TABLE [Waypoint] ADD COLUMN [IsStart] BOOLEAN DEFAULT 'false' NULL"); } if (lastDatabaseSchemeVersion < 1025) { // nicht mehr bentigt execSQL("ALTER TABLE [Waypoint] ADD COLUMN [UserNote] ntext NULL"); } if (lastDatabaseSchemeVersion < 1026) { // add one column for short description // [ShortDescription] ntext NULL execSQL("ALTER TABLE [Caches] ADD [ShortDescription] ntext NULL;"); } setTransactionSuccessful(); } catch (Exception exc) { log.error("AlterDatabase", exc); } finally { endTransaction(); } break; case FieldNotes: beginTransaction(); try { if (lastDatabaseSchemeVersion <= 0) { // First Initialization of the Database // FieldNotes Table execSQL("CREATE TABLE [FieldNotes] ([Id] integer not null primary key autoincrement, [CacheId] bigint NULL, [GcCode] nvarchar (12) NULL, [GcId] nvarchar (255) NULL, [Name] nchar (255) NULL, [CacheType] smallint NULL, [Url] nchar (255) NULL, [Timestamp] datetime NULL, [Type] smallint NULL, [FoundNumber] int NULL, [Comment] ntext NULL);"); // Config Table execSQL("CREATE TABLE [Config] ([Key] nvarchar (30) NOT NULL, [Value] nvarchar (255) NULL);"); execSQL("CREATE INDEX [Key_idx] ON [Config] ([Key] ASC);"); } if (lastDatabaseSchemeVersion < 1002) { execSQL("ALTER TABLE [FieldNotes] ADD COLUMN [Uploaded] BOOLEAN DEFAULT 'false' NULL"); } if (lastDatabaseSchemeVersion < 1003) { execSQL("ALTER TABLE [FieldNotes] ADD COLUMN [GC_Vote] integer default 0"); } if (lastDatabaseSchemeVersion < 1004) { execSQL("CREATE TABLE [Trackable] ([Id] integer not null primary key autoincrement, [Archived] bit NULL, [GcCode] nvarchar (15) NULL, [CacheId] bigint NULL, [CurrentGoal] ntext, [CurrentOwnerName] nvarchar (255) NULL, [DateCreated] datetime NULL, [Description] ntext, [IconUrl] nvarchar (255) NULL, [ImageUrl] nvarchar (255) NULL, [path] nvarchar (255) NULL, [OwnerName] nvarchar (255), [Url] nvarchar (255) NULL);"); execSQL("CREATE INDEX [cacheid_idx] ON [Trackable] ([CacheId] ASC);"); execSQL("CREATE TABLE [TbLogs] ([Id] integer not null primary key autoincrement, [TrackableId] integer not NULL, [CacheID] bigint NULL, [GcCode] nvarchar (15) NULL, [LogIsEncoded] bit NULL DEFAULT 0, [LogText] ntext, [LogTypeId] bigint NULL, [LoggedByName] nvarchar (255) NULL, [Visited] datetime NULL);"); execSQL("CREATE INDEX [trackableid_idx] ON [TbLogs] ([TrackableId] ASC);"); execSQL("CREATE INDEX [trackablecacheid_idx] ON [TBLOGS] ([CacheId] ASC);"); } if (lastDatabaseSchemeVersion < 1005) { execSQL("ALTER TABLE [Trackable] ADD COLUMN [TypeName] ntext NULL"); execSQL("ALTER TABLE [Trackable] ADD COLUMN [LastVisit] datetime NULL"); execSQL("ALTER TABLE [Trackable] ADD COLUMN [Home] ntext NULL"); execSQL("ALTER TABLE [Trackable] ADD COLUMN [TravelDistance] integer default 0"); } if (lastDatabaseSchemeVersion < 1006) { execSQL("ALTER TABLE [FieldNotes] ADD COLUMN [TbFieldNote] BOOLEAN DEFAULT 'false' NULL"); execSQL("ALTER TABLE [FieldNotes] ADD COLUMN [TbName] nvarchar (255) NULL"); execSQL("ALTER TABLE [FieldNotes] ADD COLUMN [TbIconUrl] nvarchar (255) NULL"); execSQL("ALTER TABLE [FieldNotes] ADD COLUMN [TravelBugCode] nvarchar (15) NULL"); execSQL("ALTER TABLE [FieldNotes] ADD COLUMN [TrackingNumber] nvarchar (15) NULL"); } if (lastDatabaseSchemeVersion < 1007) { execSQL("ALTER TABLE [FieldNotes] ADD COLUMN [directLog] BOOLEAN DEFAULT 'false' NULL"); } setTransactionSuccessful(); } catch (Exception exc) { log.error("AlterDatabase", exc); } finally { endTransaction(); } break; case Settings: beginTransaction(); try { if (lastDatabaseSchemeVersion <= 0) { // First Initialization of the Database execSQL("CREATE TABLE [Config] ([Key] nvarchar (30) NOT NULL, [Value] nvarchar (255) NULL);"); execSQL("CREATE INDEX [Key_idx] ON [Config] ([Key] ASC);"); } if (lastDatabaseSchemeVersion < 1002) { // Long Text Field for long Strings execSQL("ALTER TABLE [Config] ADD [LongString] ntext NULL;"); } setTransactionSuccessful(); } catch (Exception exc) { log.error("AlterDatabase", exc); } finally { endTransaction(); } break; } }