pclausen 41 Posted July 24, 2016 Share Posted July 24, 2016 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 More sharing options...
Luke 36999 Posted July 24, 2016 Share Posted July 24, 2016 shutdown the server. Link to comment Share on other sites More sharing options...
pclausen 41 Posted July 24, 2016 Author Share Posted July 24, 2016 (edited) 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 Link to comment Share on other sites More sharing options...
ebr 14902 Posted July 24, 2016 Share Posted July 24, 2016 Have fun . Just realize that the internals of our database implementation could change at any time... Link to comment Share on other sites More sharing options...
andersonrs45 3 Posted September 21, 2017 Share Posted September 21, 2017 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? Link to comment Share on other sites More sharing options...
andersonrs45 3 Posted September 21, 2017 Share Posted September 21, 2017 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 More sharing options...
Happy2Play 8239 Posted September 21, 2017 Share Posted September 21, 2017 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 More sharing options...
andersonrs45 3 Posted September 21, 2017 Share Posted September 21, 2017 How did you export and import? Link to comment Share on other sites More sharing options...
Happy2Play 8239 Posted September 21, 2017 Share Posted September 21, 2017 (edited) 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 Link to comment Share on other sites More sharing options...
andersonrs45 3 Posted September 21, 2017 Share Posted September 21, 2017 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. Link to comment Share on other sites More sharing options...
Happy2Play 8239 Posted September 22, 2017 Share Posted September 22, 2017 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, Link to comment Share on other sites More sharing options...
Happy2Play 8239 Posted September 22, 2017 Share Posted September 22, 2017 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 More sharing options...
andersonrs45 3 Posted September 22, 2017 Share Posted September 22, 2017 When you say to open the new bank, is it to open the library.db file from the new installation directory? Link to comment Share on other sites More sharing options...
Happy2Play 8239 Posted September 22, 2017 Share Posted September 22, 2017 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 More sharing options...
andersonrs45 3 Posted September 22, 2017 Share Posted September 22, 2017 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? Link to comment Share on other sites More sharing options...
Happy2Play 8239 Posted September 22, 2017 Share Posted September 22, 2017 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 More sharing options...
andersonrs45 3 Posted September 22, 2017 Share Posted September 22, 2017 After all this process is completed, should I insert the file into the installation directory? In what format? Link to comment Share on other sites More sharing options...
Happy2Play 8239 Posted September 22, 2017 Share Posted September 22, 2017 (edited) 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 Link to comment Share on other sites More sharing options...
andersonrs45 3 Posted September 22, 2017 Share Posted September 22, 2017 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 Link to comment Share on other sites More sharing options...
Happy2Play 8239 Posted September 22, 2017 Share Posted September 22, 2017 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 More sharing options...
andersonrs45 3 Posted September 22, 2017 Share Posted September 22, 2017 Could you explain me with step-by-step images so that I can follow the procedures in the most correct way? Link to comment Share on other sites More sharing options...
Happy2Play 8239 Posted September 22, 2017 Share Posted September 22, 2017 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), Link to comment Share on other sites More sharing options...
andersonrs45 3 Posted September 22, 2017 Share Posted September 22, 2017 Oh, yes, thank you. Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now