User:Herzbube/DatabaseQueries
This page contains useful database queries. This page on my personal wiki has details how to set up a personal ISFDB website on a Mac. This page on the ISFDB wiki has much simpler instructions how to set up only the database without the website.
Find out your user ID
Knowing only your username, the following query spits out your user ID, which you can then use for all sorts of queries that involve your verifications.
SELECT user_id FROM mw_user WHERE lower(user_name) = 'herzbube'
Setting your password
If you want to make modifications to your local ISFDB instance, the ISFDB software requires you to login first - without logging in you are not allowed to make any changes! If you don't care which user you will perform the modifications as, then the Python script scripts/create_user.py
, which is part of the ISFDB source code, will insert a completely new user account including a password into the database for you. However, if you want to work as the same user that you are usually logged in to the real ISFDB (e.g. because you want to see your verifications), then you need to modify an existing user account in the database - your user account - and the Python script won't help you with that.
The following query sets the password for any given account.
-- Set the desired password and the name of your account SELECT @password := '123456'; SELECT @userName = 'herzbube'; -- Find out the user ID that corresponds to the user name SELECT @userID := user_id FROM mw_user WHERE lower(user_name) = @userName; -- Calculate password hashes SELECT @passwordHash := md5(@password); SELECT @passwordRoundTwo := CONCAT(@userID, '-', @passwordHash); SELECT @passwordRoundTwoHash := md5(@passwordRoundTwo); -- Write the final password hash to the database UPDATE mw_user SET user_password = @passwordRoundTwoHash WHERE user_id = @userID; -- Display the updated account SELECT * FROM mw_user WHERE user_id = @userID;
Find all books that you have primary verified that contain a specific story
Maybe you are preparing a shopping list, or you have other reasons, but sometimes you just want to know if a given title (can be a novel, or a short story) is in your collection. Your collection is defined as "all the publications that you have primary verified". The query in this section will help you with that.
In preparation you first have to find the title record for the title. Search the database for the title record using the function of your choice, for instance the Advanced Search. When you have found the title record, make sure that it's not a variant title record (unless you really want to restrict the search to only this variant). If it is a variant title record, navigate to its parent title record. Now if you look at the address bar of your web browser you will see the title record ID as the last part of the URL. For instance, if the URL is
http://www.isfdb.org/cgi-bin/title.cgi?55822
then the title record ID is 55822.
Now set the variable in the first line of the following query to the ID of the title record that you have found. Also set your user name. Then run the script. Voilà.
-- Set the title ID and your user name SELECT @titleID := 55822; SELECT @userName := 'herzbube'; -- Find out the user ID that corresponds to the user name SELECT @userID := user_id FROM mw_user WHERE lower(user_name) = @userName; -- Display all primary verified publications that contain the title SELECT * FROM pubs WHERE pub_id IN ( SELECT DISTINCTROW(pub_content.pub_id) FROM pub_content INNER JOIN verification ON pub_content.pub_id = verification.pub_id WHERE verification.user_id = @userID AND verification.ver_status = 1 AND reference_id IN ( SELECT reference_id FROM reference WHERE reference_label LIKE '%primary%' ) AND pub_content.title_id IN ( SELECT title_id FROM titles WHERE title_id = @titleID OR title_parent = @titleID ) )