Jump to content

Question about sqlite


pclausen
 Share

Recommended Posts

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

Link to comment
Share on other sites

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
Link to comment
Share on other sites

Have fun :).

 

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

Link to comment
Share on other sites

  • 1 year later...

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

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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,

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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

 

Yes that is what I did.

Link to comment
Share on other sites

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
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

 

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

Link to comment
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
 Share

×
×
  • Create New...