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