Question about sqlite

[pclausen 42]

What's the trick to opening the library.db database file?  I have the latest version of SQLite and it won't open that file.

 

I want to create a report that contains data elements not currently available in the Report to Excel export utility within the Reports system.

 

Thanks

[Luke 40063]

shutdown the server.

[pclausen 42]

duh!  Thanks Luke.  That did the trick.  

 

Is there a data diagram or something to reference?  The ItemIds all look like binary values in SQLite, making it very hard to determine the linking between tables.

 

Without using the AncestorIds or ChildrenIds tables, the below is the best I have been able to come up with for spitting out a list of my movies with the IMDB.  I'm still trying to figure out what table contains the actual Movie title value.

 

EDIT: looks like the TypedBaseItems is what I'm looking for.

select distinct	IM.Path
		, MS.Codec
		, MS.Height
		, MS.Width
		, UDK.UserDataKey IMDB
from ItemValues IV
join Images IM on IM.ItemId = IV.ItemId
join mediastreams MS on MS.ItemId = IV.ItemId
join UserDataKeys UDK on UDK.ItemId = IV.ItemId
where IM.ImageType = 0 and MS.Height is not null and UDK.Priority = 1
order by IM.Path

Edited July 24, 2016 by pclausen

[ebr 15662]

Have fun .

 

Just realize that the internals of our database implementation could change at any time...

[andersonrs45 3]

I want to restore user data to the new installation. But I have doubts about library.db. I already have the SQL browser program. How do I attach the new library.db file using the Attach option?

 

[andersonrs45 3]

To be more specific in my doubt. In "EMBY BACKUP", the tutorial says that I should replace the old library.db file. Can I do it manually in the folder, or do I need this program?

[Happy2Play 9441]

I personally used export and import as I didn't understand this process.

You'll need to open the old library.db file, then attach the new library.db file. You can then migrate the data using: REPLACE INTO NewDB.userdata SELECT * FROM userdata

[andersonrs45 3]

How did you export and import?

[Happy2Play 9441]

How did you export and import?

 

Here are the steps I used.

1) Open the old/backed up library.db and export userdata table to csv file

2) Open the new library.db from new installation and deleted the userdata table (reason for delete, some have reported column mismatch)

3) Click the execute sql tab and run this

4) CREATE TABLE userdata (key nvarchar, userId GUID, rating float null, played bit, playCount int, isFavorite bit, playbackPositionTicks bigint, lastPlayedDate datetime null, AudioStreamIndex int NULL, SubtitleStreamIndex int NULL)

​5) Then Import your csv into new library.db

But the Server Configurations Backup plugin is the easiest way to save and restore "userdata".

Edited September 22, 2017 by Happy2Play

[andersonrs45 3]

I was able to continue to step 4, but step 5 is having difficulties. Should I import csv from the old database?  I imported the old database into the CSV. If this is the case, I went to the "Import CSV" option, but it is displaying this error message saying that a table with the same name already exists.

 

[Happy2Play 9441]

I was able to continue to step 4, but step 5 is having difficulties. Should I import csv from the old database?  I imported the old database into the CSV. If this is the case, I went to the "Import CSV" option, but it is displaying this error message saying that a table with the same name already exists.

 

 

I would guess you didn't do step 2,

[Happy2Play 9441]

I was able to continue to step 4, but step 5 is having difficulties. Should I import csv from the old database?  I imported the old database into the CSV. If this is the case, I went to the "Import CSV" option, but it is displaying this error message saying that a table with the same name already exists.

 

 

 

 

You should have 2 databases.  The backed up original library.db and the new library.db that was created when you rebuild the server.  Export from original and import into new  Follow the steps above..

[andersonrs45 3]

When you say to open the new bank, is it to open the library.db file from the new installation directory?

[Happy2Play 9441]

When you say to open the new bank, is it to open the library.db file from the new installation directory?

 

Yes the new database should be in \Emby-Server\data, and the backup is where ever you saved it.

[andersonrs45 3]

Below are the pictures of the instructions you sent from the second step:

 

 

1st Image: New open database and deleted user data table

 

In the second image, I clicked the "execute sql" tab and entered the following command: CREATE TABLE userdata (key nvarchar, userId GUID, rating float null, played bit, playCount int, isFavorite bit, playbackPositionTicks bigint, lastPlayedDate datetime null, AudioStreamIndex int NULL, SubtitleStreamIndex int NULL)

 

 

 

In the last image, highlighted in red is the new table created in the new database.

 

 

And, lastly, should I import csv from the old database to this file?

[Happy2Play 9441]

And, lastly, should I import csv from the old database to this file?

 

Yes that is what I did.

[andersonrs45 3]

After all this process is completed, should I insert the file into the installation directory? In what format?

[Happy2Play 9441]

After all this process is completed, should I insert the file into the installation directory? In what format?

 

After you import the csv into the new library.db you are done, unless you moved the library.db during this process..  Start server and perform another library scan, all your userdata should be restored.

Edited September 22, 2017 by Happy2Play

[andersonrs45 3]

This software I'm using, just saved in SQL file format, not DB. I've already imported the CSV file, and now I do not know what else to do  

 

[Happy2Play 9441]

There is nothing else to do.   If you followed the steps when you close DB Browser it will ask you to save the db and you are done.  I am confuse on what you are trying to do.

 

 

 

 

I also figured out this process also.

You'll need to open the old library.db file, then attach the new library.db file. You can then migrate the data using: REPLACE INTO NewDB.userdata SELECT * FROM userdata

"NewDB" is actually the what ever name you attach the new library.db as.  So if you attach the db with a different name, that is what you will use in this command.

[andersonrs45 3]

Could you explain me with step-by-step images so that I can follow the procedures in the most correct way?

[Happy2Play 9441]

 

This software I'm using, just saved in SQL file format, not DB. I've already imported the CSV file, and now I do not know what else to do  

 

 

 

You have already provided all the screen shots needed.  All you should have to do is click "Close Database" on that screen (not export), 

[andersonrs45 3]

Oh, yes, thank you.