Updating Folder_Path in Windows 10 Photos App

classic Classic list List threaded Threaded
17 messages Options
Reply | Threaded
Open this post in threaded view
|

Updating Folder_Path in Windows 10 Photos App

Carlo capaldo
Dear Users,

Windows 10 Photos App is no longer able to find my files when opening the app.
I discovered that the App uses Sqlite and using DB Browser for SQLite I was able to display the Table folder which shows the many different folder paths I believe the App is referencing (see attached).

The original folder paths have all changed and the photos etc have been consolidated into a single directory (E:\Photos).

I tried using the following sql commands to change the folder references for each entry. The sequence appears to execute correctly but I can’t find a way to update or write the database.

select folder.Folder_Path,
replace(Folder_Path,'C:\Users\carlo\Pictures','E:\Photos')
from Folder
where folder.Folder_Id = 1

Could someone kindly educate me on how to update the folder references?

Kind regards,
Carlo.




_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Updating Folder_Path in Windows 10 Photos App

R Smith-2
Hi Carlo,

Attachments are stripped on this forum - could you use a file service or
upload it somewhere?

Also be clear about the which paths all changed and to what - AND make a
backup of the file before fixing it - but there most certainly is some
SQL that will fix what you described.


Cheers,
Ryan

On 2018/11/29 9:32 PM, Carlo capaldo wrote:

> Dear Users,
>
> Windows 10 Photos App is no longer able to find my files when opening the app.
> I discovered that the App uses Sqlite and using DB Browser for SQLite I was able to display the Table folder which shows the many different folder paths I believe the App is referencing (see attached).
>
> The original folder paths have all changed and the photos etc have been consolidated into a single directory (E:\Photos).
>
> I tried using the following sql commands to change the folder references for each entry. The sequence appears to execute correctly but I can’t find a way to update or write the database.
>
> select folder.Folder_Path,
> replace(Folder_Path,'C:\Users\carlo\Pictures','E:\Photos')
> from Folder
> where folder.Folder_Id = 1
>
> Could someone kindly educate me on how to update the folder references?
>
> Kind regards,
> Carlo.
>
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Updating Folder_Path in Windows 10 Photos App

Simon Slavin-3
In reply to this post by Carlo capaldo
On 29 Nov 2018, at 7:32pm, Carlo capaldo <[hidden email]> wrote:

> select folder.Folder_Path,
> replace(Folder_Path,'C:\Users\carlo\Pictures','E:\Photos')
> from Folder
> where folder.Folder_Id = 1
>
> Could someone kindly educate me on how to update the folder references?

My guess is that you actually want

UPDATE folder
    SET Folder_Path = 'E:\Photos'
    WHERE Folder_Path = 'C:\Users\carlo\Pictures' AND folder_Id = 1

Please take a backup copy of your database file before trying this.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Updating Folder_Path in Windows 10 Photos App

Carlo capaldo
Hi Simon,

Sorry for the late reply and thank you so much for this suggestion which I’ve tried and the sequence executes successfully updating one of the folder paths. Unfortunately due to the tangled mess my daughter got into in creating the video in the first place she ended up with 17 different folder paths all pointing to different directories. My aim (since August!) has been trying to redirect all of these folders to a single path (‘E\Photos’) where all photos and videos are now stored.

Since testing your suggestion I’ve been reading the SQL syntax and examples to try to work out how to modify or extend your sequence below to change the remaining 16 Folder Paths however when I try to change each one in turn I receive the following error:

UNIQUE constraint failed: Folder.Folder_Path: UPDATE folder
SET Folder_Path = 'E:\Photos'
WHERE Folder_Path = 'C:\Users\carlo\Pictures' AND folder_Id = 1

Kind regards,
Carlo.

From: Simon Slavin<mailto:[hidden email]>
Sent: 30 November 2018 14:43
Subject: Re: [sqlite] Updating Folder_Path in Windows 10 Photos App

On 29 Nov 2018, at 7:32pm, Carlo capaldo <[hidden email]> wrote:

> select folder.Folder_Path,
> replace(Folder_Path,'C:\Users\carlo\Pictures','E:\Photos')
> from Folder
> where folder.Folder_Id = 1
>
> Could someone kindly educate me on how to update the folder references?

My guess is that you actually want

UPDATE folder
    SET Folder_Path = 'E:\Photos'
    WHERE Folder_Path = 'C:\Users\carlo\Pictures' AND folder_Id = 1

Please take a backup copy of your database file before trying this.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Updating Folder_Path in Windows 10 Photos App

Carlo capaldo
In reply to this post by R Smith-2
Hi Ryan,



Sorry for the late reply and fully understood on the need to strip attachments for obvious reasons.



While I’ve been working out which file service I can upload the file to one of your users kindly provided an excellent suggestion which I’ve been studying and reading up on to try and modify so as to try and fix all of the incorrect directory references (but so far failed to achieve!).



Kind regards,

Carlo.



________________________________
From: sqlite-users <[hidden email]> on behalf of R Smith <[hidden email]>
Sent: Friday, November 30, 2018 1:43:49 PM
To: [hidden email]
Subject: Re: [sqlite] Updating Folder_Path in Windows 10 Photos App

Hi Carlo,

Attachments are stripped on this forum - could you use a file service or
upload it somewhere?

Also be clear about the which paths all changed and to what - AND make a
backup of the file before fixing it - but there most certainly is some
SQL that will fix what you described.


Cheers,
Ryan

On 2018/11/29 9:32 PM, Carlo capaldo wrote:

> Dear Users,
>
> Windows 10 Photos App is no longer able to find my files when opening the app.
> I discovered that the App uses Sqlite and using DB Browser for SQLite I was able to display the Table folder which shows the many different folder paths I believe the App is referencing (see attached).
>
> The original folder paths have all changed and the photos etc have been consolidated into a single directory (E:\Photos).
>
> I tried using the following sql commands to change the folder references for each entry. The sequence appears to execute correctly but I can’t find a way to update or write the database.
>
> select folder.Folder_Path,
> replace(Folder_Path,'C:\Users\carlo\Pictures','E:\Photos')
> from Folder
> where folder.Folder_Id = 1
>
> Could someone kindly educate me on how to update the folder references?
>
> Kind regards,
> Carlo.
>
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Updating Folder_Path in Windows 10 Photos App

Simon Slavin-3
In reply to this post by Carlo capaldo
On 6 Dec 2018, at 9:01pm, Carlo capaldo <[hidden email]> wrote:

> UNIQUE constraint failed: Folder.Folder_Path:

You seem to have two rows in the table: one with the correct Folder_Path and one with the incorrect Folder_Path.  So instead of an UPDATE you just need a DELETE command like

DELETE FROM folder
    WHERE Folder_Path = 'C:\Users\carlo\Pictures'
    AND folder_Id = 1

However, please check by eye to make sure I've understood the situation correctly, before you execute the above DELETE command.

If it's not obvious whether this is the right thing to do, then using the SQLite CLI tool, execute the command ".schema" and paste the resulting text into a reply to this thread.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Updating Folder_Path in Windows 10 Photos App

Carlo capaldo
Simon, thank you for your reply.



Unfortunately as a novice I’m still struggling to find a construct that will do what I want.

Reading the on-line SQLite guide to the UPDATE statement:



“If the UPDATE statement does not have a WHERE clause, all rows in the table are modified by the UPDATE.”



I was hoping that the command:



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.



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’).



I have now managed to upload the file to my Google drive and it’s accessible via the following link:



https://drive.google.com/open?id=191LBKrl7hA7OR2HUUFbFnU71lF9X9uoo



Examining ‘Table -> Folder’ shows the 16 rows containing the incorrect folder paths all of which now need to point to ‘E:\Photos’



Kind regards,

Carlo.







________________________________
From: sqlite-users <[hidden email]> on behalf of Simon Slavin <[hidden email]>
Sent: Thursday, December 6, 2018 9:22:35 PM
To: SQLite mailing list
Subject: Re: [sqlite] Updating Folder_Path in Windows 10 Photos App

On 6 Dec 2018, at 9:01pm, Carlo capaldo <[hidden email]> wrote:

> UNIQUE constraint failed: Folder.Folder_Path:

You seem to have two rows in the table: one with the correct Folder_Path and one with the incorrect Folder_Path.  So instead of an UPDATE you just need a DELETE command like

DELETE FROM folder
    WHERE Folder_Path = 'C:\Users\carlo\Pictures'
    AND folder_Id = 1

However, please check by eye to make sure I've understood the situation correctly, before you execute the above DELETE command.

If it's not obvious whether this is the right thing to do, then using the SQLite CLI tool, execute the command ".schema" and paste the resulting text into a reply to this thread.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Updating Folder_Path in Windows 10 Photos App

Simon Slavin-3
On 13 Dec 2018, at 2:57pm, Carlo capaldo <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Updating Folder_Path in Windows 10 Photos App

Carlo capaldo
Simon,

Thank you again for the time you've spent on this and the recommendations below, it's really appreciated.

Kind regards,
Carlo.

________________________________
From: sqlite-users <[hidden email]> on behalf of Simon Slavin <[hidden email]>
Sent: 13 December 2018 17:26
To: SQLite mailing list
Subject: Re: [sqlite] Updating Folder_Path in Windows 10 Photos App

On 13 Dec 2018, at 2:57pm, Carlo capaldo <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Updating Folder_Path in Windows 10 Photos App

Luuk
In reply to this post by Simon Slavin-3

On 13-12-2018 18:26, Simon Slavin wrote:

> On 13 Dec 2018, at 2:57pm, Carlo capaldo <[hidden email]> 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;


Just because i was interested in the structure of tis SQLite db, i was
looking at the version on my computer.

sqlite> select Folder_Path, Folder_DisplayName from Folder;
Folder_Path              Folder_DisplayName
-----------------------  ------------------
C:\Users\Luuk2\Pictures  Pictures
C:\Users\Luuk2\Pictures  Camera Roll
C:\Users\Luuk2\OneDrive  Afbeeldingen
C:\Users\Luuk2\Pictures  Saved Pictures
C:\Users\Luuk2\OneDrive  Schermopnamen
C:\Users\Luuk2\Pictures  Screenshots

OK so far, no problems, but why do i get 'no collation sequence' with
this statement:

sqlite> select Folder_Path from Folder;
Error: no such collation sequence: NoCaseUnicode
sqlite>



For completenes the CREATE TABLE for this table (without indexes an
triggers):

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);





_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Updating Folder_Path in Windows 10 Photos App

Luuk

On 14-12-2018 11:15, Luuk wrote:

>
> On 13-12-2018 18:26, Simon Slavin wrote:
>> On 13 Dec 2018, at 2:57pm, Carlo capaldo <[hidden email]>
>> 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;
>
>
> Just because i was interested in the structure of tis SQLite db, i was
> looking at the version on my computer.
>
> sqlite> select Folder_Path, Folder_DisplayName from Folder;
> Folder_Path              Folder_DisplayName
> -----------------------  ------------------
> C:\Users\Luuk2\Pictures  Pictures
> C:\Users\Luuk2\Pictures  Camera Roll
> C:\Users\Luuk2\OneDrive  Afbeeldingen
> C:\Users\Luuk2\Pictures  Saved Pictures
> C:\Users\Luuk2\OneDrive  Schermopnamen
> C:\Users\Luuk2\Pictures  Screenshots
>
> OK so far, no problems, but why do i get 'no collation sequence' with
> this statement:
>
> sqlite> select Folder_Path from Folder;
> Error: no such collation sequence: NoCaseUnicode
> sqlite>
>
>
>
> For completenes the CREATE TABLE for this table (without indexes an
> triggers):
>
> 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);
>
>
>
sqlite> .version
SQLite 3.21.0 2017-10-24 18:55:49
1a584e499906b5c87ec7d43d4abce641fdf017c42125b083109bc77c4de48827
sqlite>
>
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Updating Folder_Path in Windows 10 Photos App

Simon Slavin-3
In reply to this post by Luuk
On 14 Dec 2018, at 10:15am, Luuk <[hidden email]> wrote:

> why do i get 'no collation sequence' with this statement:
>
> sqlite> select Folder_Path from Folder;
> Error: no such collation sequence: NoCaseUnicode

There's a UNIQUE index for Folder_Path COLLATE NoCaseUnicode.  Iterating through that index would involve processing less data than reading the entire table.  So SQLite decides that using that index to complete your SELECT command is a good strategy.  But that collation is not included in the default compilation of SQLite so SQLite realises it can't use that index.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Updating Folder_Path in Windows 10 Photos App

Luuk

On 14-12-2018 11:22, Simon Slavin wrote:
> On 14 Dec 2018, at 10:15am, Luuk <[hidden email]> wrote:
>
>> why do i get 'no collation sequence' with this statement:
>>
>> sqlite> select Folder_Path from Folder;
>> Error: no such collation sequence: NoCaseUnicode
> There's a UNIQUE index for Folder_Path COLLATE NoCaseUnicode.  Iterating through that index would involve processing less data than reading the entire table.  So SQLite decides that using that index to complete your SELECT command is a good strategy.  But that collation is not included in the default compilation of SQLite so SQLite realises it can't use that index.
>
> Simon.

OK, but a slightly different statement, which needs to scan the complete
table, also returns the error:

sqlite> explain query plan select Folder_Path from Folder order by
upper(Folder_Path);
Error: no such collation sequence: NoCaseUnicode
sqlite> explain query plan select upper(Folder_Path) from Folder order
by upper(Folder_Path);
Error: no such collation sequence: NoCaseUnicode

My conclusion would be that this is a bug (in the definition of the
table, no in SQLite).

Or, but i double it will make sence,

When SQLite realises it can't use that index, should it revert to a full
table scan?

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Updating Folder_Path in Windows 10 Photos App

Simon Slavin-3
On 14 Dec 2018, at 10:41am, Luuk <[hidden email]> wrote:

> When SQLite realises it can't use that index, should it revert to a full table scan?

That's the bigger question.  My theory is that SQLite doesn't want to touch that database unless it has all the collations it will need.  So it errors out the first time it notes the lack.

After all, you might make a few changes to the database on tables that don't use that collation, then be trying to change another table in sync with your first changes, and only then realise you shouldn't have changed anything.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Updating Folder_Path in Windows 10 Photos App

Keith Medcalf
In reply to this post by Luuk

You get the message about missing collating sequences because the database is using collating sequences that you don't have (ie, are missing) in your version of SQLite3.


---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.


>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of Luuk
>Sent: Friday, 14 December, 2018 03:18
>To: SQLite mailing list
>Subject: Re: [sqlite] Updating Folder_Path in Windows 10 Photos App
>
>
>On 14-12-2018 11:15, Luuk wrote:
>>
>> On 13-12-2018 18:26, Simon Slavin wrote:
>>> On 13 Dec 2018, at 2:57pm, Carlo capaldo
><[hidden email]>
>>> 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;
>>
>>
>> Just because i was interested in the structure of tis SQLite db, i
>was
>> looking at the version on my computer.
>>
>> sqlite> select Folder_Path, Folder_DisplayName from Folder;
>> Folder_Path              Folder_DisplayName
>> -----------------------  ------------------
>> C:\Users\Luuk2\Pictures  Pictures
>> C:\Users\Luuk2\Pictures  Camera Roll
>> C:\Users\Luuk2\OneDrive  Afbeeldingen
>> C:\Users\Luuk2\Pictures  Saved Pictures
>> C:\Users\Luuk2\OneDrive  Schermopnamen
>> C:\Users\Luuk2\Pictures  Screenshots
>>
>> OK so far, no problems, but why do i get 'no collation sequence'
>with
>> this statement:
>>
>> sqlite> select Folder_Path from Folder;
>> Error: no such collation sequence: NoCaseUnicode
>> sqlite>
>>
>>
>>
>> For completenes the CREATE TABLE for this table (without indexes an
>> triggers):
>>
>> 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);
>>
>>
>>
>sqlite> .version
>SQLite 3.21.0 2017-10-24 18:55:49
>1a584e499906b5c87ec7d43d4abce641fdf017c42125b083109bc77c4de48827
>sqlite>
>>
>>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Updating Folder_Path in Windows 10 Photos App

Luuk

On 14-12-2018 16:54, Keith Medcalf wrote:
> You get the message about missing collating sequences because the database is using collating sequences that you don't have (ie, are missing) in your version of SQLite3.
>
>
>
Extensioon in SQLite, thats somewhere on my TODO list ;)

Is there any documentation on where to find these extensions for Windows?

Trying to compile something meself (on windows) is a too deep hole right
now ;)




_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Updating Folder_Path in Windows 10 Photos App

Keith Medcalf

On Friday, 14 December, 2018 10:09, Luuk <[hidden email]> wrote:
>On 14-12-2018 16:54, Keith Medcalf wrote:
>> You get the message about missing collating sequences because the
>> database is using collating sequences that you don't have (ie, are
>> missing) in your version of SQLite3.

>Extensioon in SQLite, thats somewhere on my TODO list ;)

>Is there any documentation on where to find these extensions for
>Windows?

Well, the author of the extension makes them available.  In the case of this collation sequence one would presume that Microsoft is the author.  If they can provide the code, then compiling it in is not difficult.

>Trying to compile something meself (on windows) is a too deep hole
>right now ;)

It is the same as any other platform.  Unless of course you use a Microsoft compiler.  If you use GCC then it is the same GCC as you would use anywhere else GCC runs ...

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.




_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users