Jump to content
pclausen

Question about sqlite

Recommended Posts

pclausen

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

Share this post


Link to post
Share on other sites
Luke

shutdown the server.

Share this post


Link to post
Share on other sites
pclausen

duh!  Thanks Luke.  That did the trick.   :rolleyes:

 

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 by pclausen

Share this post


Link to post
Share on other sites
ebr

Have fun :).

 

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

Share this post


Link to post
Share on other sites
andersonrs45

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?

 

59c41b726be72_sadf.jpg

Share this post


Link to post
Share on other sites
andersonrs45

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?

Share this post


Link to post
Share on other sites
Happy2Play

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

Share this post


Link to post
Share on other sites
andersonrs45

How did you export and import?

Share this post


Link to post
Share on other sites
Happy2Play

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 by Happy2Play

Share this post


Link to post
Share on other sites
andersonrs45

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.

 

59c44b0637f42_Semttulo.jpg

Share this post


Link to post
Share on other sites
Happy2Play

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.

 

59c44b0637f42_Semttulo.jpg

 

I would guess you didn't do step 2,

Share this post


Link to post
Share on other sites
Happy2Play

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

Share this post


Link to post
Share on other sites
andersonrs45

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

Share this post


Link to post
Share on other sites
Happy2Play

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.

Share this post


Link to post
Share on other sites
andersonrs45

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

 

 

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

59c45f2814ec0_Tabelauserdatadeletada.jpg

 

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)

 

59c4603da61cd_Passo2.jpg

 

 

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

 

59c461618fd90_nova.jpg

 

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

Share this post


Link to post
Share on other sites
Happy2Play

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

 

Yes that is what I did.

Share this post


Link to post
Share on other sites
andersonrs45

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

Share this post


Link to post
Share on other sites
Happy2Play

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 by Happy2Play

Share this post


Link to post
Share on other sites
andersonrs45

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   :mellow:

 
59c4959781d2c_Screenshot_1.jpg

Share this post


Link to post
Share on other sites
Happy2Play

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.

Share this post


Link to post
Share on other sites
andersonrs45

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

Share this post


Link to post
Share on other sites
Happy2Play

 

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   :mellow:

 
59c4959781d2c_Screenshot_1.jpg

 

 

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

Share this post


Link to post
Share on other sites
andersonrs45

Oh, yes, thank you.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...