[sqlite] 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.

[R Smith]

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
> sqlite-***@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[Carlo capaldo]

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 <sqlite-users-***@mailinglists.sqlite.org> on behalf of R Smith <***@gmail.com>
Sent: Friday, November 30, 2018 1:43:49 PM
To: sqlite-***@mailinglists.sqlite.org
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
> sqlite-***@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
sqlite-***@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[Simon Slavin]

On 29 Nov 2018, at 7:32pm, Carlo capaldo <***@hotmail.com> 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.

[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:***@bigfraud.org>
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 <***@hotmail.com> 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
sqlite-***@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[Simon Slavin]

On 6 Dec 2018, at 9:01pm, Carlo capaldo <***@hotmail.com> 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.