There is a free viewer program (for both Mac and PC) for the SQL Lite database DVDPedia uses.
This program is not only a viewer, but also supports full editing.
The structure of the DVDPedia database isn't too terribly difficult to figure out ... most everything is in the ZENTRY table.
I was faced with a dilemma: I had used the ZCUSTOM1 field as the Location of over 3,400 movies, and would like to update the ZLOCATION field to this value. Re-typing 3,400+ entries isn't appealing at all.
Voila! Using the SQL Lite Viewer and a very simple SQL command, I moved all values from ZCUSTOM1 into ZLOCATION, and deleted all values in ZCUSTOM1. It worked like a charm.
I naturally backed up my current database in several locations before trying this. I actually changed just one or two entries by hand to make sure this program didn't "smash" the DVDPedia database.
If you're familiar with databases and SQL commands for global changes (SELECT, UPDATE, etc. are just what you'd expect), or even to update field data by hand, it's quite easy to do with this program.
I absolutely do not recommend adding or deleting records, or changing or adding new fields. But, for editing what's there ... it's almost a snap.
Not for everyone ... BUT!
Thank you for the tip. For those familiar with Terminal the same SQL statements can be run using the sqlite3 command. A replace statement requires a newer version of sqlite3 3.1.3 that comes with Tiger.
As mentioned before backup Database.pediadata; you can easily delete, destroy or render the data unreadable by DVDpedia with an SQL command.
As mentioned before backup Database.pediadata; you can easily delete, destroy or render the data unreadable by DVDpedia with an SQL command.
Name of the SQL viewer
Hello drjohncmac:
could you tell us the precise name of that program (SQL viewer)?
Thank you.
Stefano
could you tell us the precise name of that program (SQL viewer)?
Thank you.
Stefano
I think it's this one.
-
drjohncmac
- Captain
- Posts: 22
- Joined: Fri Feb 02, 2007 8:55 pm
Sorry ... been away for a while.
Yes, you're correct. There isn't a replace button.
There is a tab for writing SQL Queries ... This is where you can update records, do replaces (no deletes ... please
).
Look at the documentation for SQL Lite Browser. There is a rudimentary syntax to use, e.g.,
UPDATE Zentry
SET Location = Custom1
Updates the location field in the Zentry table with the contents of the Custom1 field. Then
Update Zentry
SET Custom1 = NULL
Updates the Custom1 field in the Zentry table with NULL values.
As I mentioned in the initial post ... DO NO CHANGE STRUCTURE. DO NOT DELETE RECORDS. DO NOT ADD RECORDS.
MAKE A BACK-UP. MAKE A BACK-UP. MAKE A BACK-UP.
Although not a full-featured SQL syntax, SQL Lite can perform many laborous functions in an instant. I have a 3700+ record DB, so changing Location to the contents of Custom1 would have been very time-consuming.
I occasionally to a large re-cataloging of part of the collection (Moving contents into DVD Carousels, etc.) The SQL Lite Querying capability will make this much less tiresome and time-consuming.
Yes, you're correct. There isn't a replace button.
There is a tab for writing SQL Queries ... This is where you can update records, do replaces (no deletes ... please
).Look at the documentation for SQL Lite Browser. There is a rudimentary syntax to use, e.g.,
UPDATE Zentry
SET Location = Custom1
Updates the location field in the Zentry table with the contents of the Custom1 field. Then
Update Zentry
SET Custom1 = NULL
Updates the Custom1 field in the Zentry table with NULL values.
As I mentioned in the initial post ... DO NO CHANGE STRUCTURE. DO NOT DELETE RECORDS. DO NOT ADD RECORDS.
MAKE A BACK-UP. MAKE A BACK-UP. MAKE A BACK-UP.
Although not a full-featured SQL syntax, SQL Lite can perform many laborous functions in an instant. I have a 3700+ record DB, so changing Location to the contents of Custom1 would have been very time-consuming.
I occasionally to a large re-cataloging of part of the collection (Moving contents into DVD Carousels, etc.) The SQL Lite Querying capability will make this much less tiresome and time-consuming.
-
drjohncmac
- Captain
- Posts: 22
- Joined: Fri Feb 02, 2007 8:55 pm
Sorry ... been away for a while.
Yes, you're correct. There isn't a replace button.
There is a tab for writing SQL Queries ... This is where you can update records, do replaces (no deletes ... please).
Look at the documentation for SQL Lite Browser. There is a rudimentary syntax to use, e.g.,
UPDATE Zentry
SET Location = Custom1
Updates the location field in the Zentry table with the contents of the Custom1 field. Then
Update Zentry
SET Custom1 = NULL
Updates the Custom1 field in the Zentry table with NULL values.
As I mentioned in the initial post ... DO NO CHANGE STRUCTURE. DO NOT DELETE RECORDS. DO NOT ADD RECORDS.
MAKE A BACK-UP. MAKE A BACK-UP. MAKE A BACK-UP.
Although not a full-featured SQL syntax, SQL Lite can perform many laborous functions in an instant. I have a 3700+ record DB, so changing Location to the contents of Custom1 would have been very time-consuming.
I occasionally to a large re-cataloging of part of the collection (Moving contents into DVD Carousels, etc.) The SQL Lite Querying capability will make this much less tiresome and time-consuming.
Yes, you're correct. There isn't a replace button.
There is a tab for writing SQL Queries ... This is where you can update records, do replaces (no deletes ... please
).Look at the documentation for SQL Lite Browser. There is a rudimentary syntax to use, e.g.,
UPDATE Zentry
SET Location = Custom1
Updates the location field in the Zentry table with the contents of the Custom1 field. Then
Update Zentry
SET Custom1 = NULL
Updates the Custom1 field in the Zentry table with NULL values.
As I mentioned in the initial post ... DO NO CHANGE STRUCTURE. DO NOT DELETE RECORDS. DO NOT ADD RECORDS.
MAKE A BACK-UP. MAKE A BACK-UP. MAKE A BACK-UP.
Although not a full-featured SQL syntax, SQL Lite can perform many laborous functions in an instant. I have a 3700+ record DB, so changing Location to the contents of Custom1 would have been very time-consuming.
I occasionally to a large re-cataloging of part of the collection (Moving contents into DVD Carousels, etc.) The SQL Lite Querying capability will make this much less tiresome and time-consuming.