On 13 Dec 2018, at 2:57pm, Carlo capaldo <carlocapa...@hotmail.com> wrote:
> UPDATE folder
>
> SET Folder_Path = 'E:\Photos'
>
> would change the directory references in all existing 16 rows currently
> containing Folder_Path references to the wrong locations to the correct
> location ‘E:\Photos’ however this also generates a “UNIQUE constraint failed”
> error.
You are correct about what the command does. However, the schema of the
database (its structure, including how all its tables, indexes and views are
defined) includes the following:
CREATE UNIQUE INDEX Folder_Path ON Folder(Folder_Path);
This means that no two rows in the Folder TABLE may have the same values for
Folder_Path . I'm sure that makes sense to the person who defined the schema,
so let's look at the current values:
sqlite> .mode column
sqlite> .width 60 20
sqlite> SELECT Folder_Path,Folder_DisplayName FROM Folder;
C:\Users\carlo\Pictures Pictures
C:\Users\carlo\Pictures\Saved Pictures Saved Pictures
C:\Users\carlo\Pictures\Camera Roll Camera Roll
OneDrive
Documents
Attachments
C:\Users\carlo\Pictures\Exported videos Exported videos
C:\Users\carlo\Pictures\2018-09 2018-09
C:\Users\carlo\Pictures\2018-08 2018-08
C:\Users\carlo\Pictures\GoPro GoPro
C:\Users\carlo\OneDrive\Pictures Pictures
Pictures
Camera imports
2018-09-14
C:\Users\carlo\OneDrive\Pictures\Camera imports Camera imports
C:\Users\carlo\OneDrive\Pictures\Camera imports\2018-09-14 2018-09-14
E:\Photos Photos
But they obviously shouldn't all be the same folder. Each row in the table
needs to refer to a different folder.
So I think you're changing data from the wrong table. If you really have moved
all your pictures into the new E:\Photos folder then you need to find the rows
in other tables referring to Folder_Id and change them all to the new Folder_Id:
sqlite> .width 5 50
sqlite> SELECT Folder_Id,Folder_Path FROM Folder;
1 C:\Users\carlo\Pictures
2 C:\Users\carlo\Pictures\Saved Pictures
3 C:\Users\carlo\Pictures\Camera Roll
4
5
6
7 C:\Users\carlo\Pictures\Exported videos
13 C:\Users\carlo\Pictures\2018-09
14 C:\Users\carlo\Pictures\2018-08
16 C:\Users\carlo\Pictures\GoPro
17 C:\Users\carlo\OneDrive\Pictures
20
21
22
23 C:\Users\carlo\OneDrive\Pictures\Camera imports
24 C:\Users\carlo\OneDrive\Pictures\Camera imports\20
25 E:\Photos
Unfortunately there are a lot of tables in the database and with all the
TRIGGERs I can see I don't really understand the consequences of making changes
like this. You might want to seek help from a forum which understands the
software which uses this database.
> I also downloaded the SQLite CLI tool and tried to execute the command
> ".schema" but received the error “unable to open database” when trying to
> open the file. (The file opens without problems using the application ‘DB
> Browser for SQLite’).
Works fine for me:
178:Desktop simon$ sqlite3 /Users/simon/Desktop/MediaDb.v1.sqlite
SQLite version 3.24.0 2018-06-04 14:10:15
Enter ".help" for usage hints.
sqlite> .schema folder
CREATE TABLE Folder(Folder_Id INTEGER PRIMARY KEY,Folder_ParentFolderId INTEGER
REFERENCES Folder ON DELETE CASCADE,Folder_LibraryRelationship
INTEGER,Folder_Source INTEGER,Folder_SourceId INTEGER REFERENCES Source ON
DELETE CASCADE,Folder_Path TEXT COLLATE NoCaseUnicode,Folder_DisplayName TEXT
COLLATE NoCaseLinguistic,Folder_DateCreated INTEGER,Folder_DateModified
INTEGER,Folder_KnownFolderType INTEGER,Folder_SyncWith
INTEGER,Folder_StorageProviderFileId TEXT,Folder_InOneDrivePicturesScope
INTEGER,Folder_ItemCount INTEGER);
CREATE INDEX Folder_ParentFolderId ON Folder(Folder_ParentFolderId);
CREATE INDEX Folder_Source ON Folder(Folder_Source);
CREATE INDEX Folder_SourceId ON Folder(Folder_SourceId);
CREATE UNIQUE INDEX Folder_Path ON Folder(Folder_Path);
[etc.]
Simon.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
