Installing ZXDB on your local computer

This is the place for general discussion and updates about the ZXDB Database. This forum is not specific to Spectrum Computing.

Moderator: druellan

Post Reply
User avatar
Vampyre
Manic Miner
Posts: 833
Joined: Wed Nov 15, 2017 2:51 pm
Contact:

Re: Installing ZXDB on your local computer

Post by Vampyre »

Hi Everyone,

I thought it might be an idea to post some instructions on how I got ZXDB into SQL Server, so the following is how to create the database from scratch. It's assuming that you have a relatively new version of SQL Server Engine and SQL Server Management Studio (SSMS) installed and that you have a bit of know-how with Management Studio. One of the requirements of the following instructions is that SSMS must be able to run a query in SQLCMD Mode - you can test this by creating a New Query in SSMS, select Query from the main menu items and seeing if that contains a SQLCMD Mode option. If so, all good, otherwise you'll have to find another way of running each .sql file that the Powershell script creates.

- First download ZXDB from GitHub - https://github.com/zxdb/ZXDB. Click the green "Clone or Download" button and select Download Zip, which will download the file to your Downloads folder.

- Locate this zip file (called ZXDB-master.zip) and extract the files to C:\zxdb (or which ever folder you like; the instructions are going to assume C:\zxdb). At this point all of the files are in C:\zxdb\ZXDB-master\ZXDB-master. Take all of the files from the final ZXDB-master folder and paste to C:\zxdb. Then delete the ZXDB-master folder to tidy up.

- Open C:\zxdb\scripts\ZXDB_to_SQLServer.ps1 in your favoured text editor and overwrite with the contents of AndyC's amended script above. Save and exit.

- Copy C:\zxdb\scripts\ZXDB_to_SQLServer.ps1 to C:\zxdb (so it's in the same location as ZXDB_mysql.sql)

Next we have to set up Windows Powershell so that it's able to run Powershell scripts. By default it will be disabled so we're going to enable it and when completed disable it again.

- Find Windows Powershell (not Powershell ISE) and Run As Administrator. At the command prompt type (without the quotes): "set-executionpolicy unrestricted". Answer Y to the question. Leave Powershell open for the time being.

- Find Windows Powershell ISE and Run As Administrator.

- At the Powershell ISE command prompt type (without quotes): "cd C:\zxdb" - this sets the working directory.

- In Powershell ISE: File > Open C:\zxdb\ZXDB_to_SQLServer.ps1

- Press F5 to run the script. Powershell ISE takes a few minutes to create the converted .sql files - there's a progress bar so you'll know when it's complete. Once complete close Powershell ISE.

- Go back to the Powershell command prompt we opened earlier and type (without quotes): "set-executionpolicy restricted". Answer Y to the question. Close Powershell.

At this point, all of the .sql files are present but there are some minor errors that will stop them executing correctly. So make the following amends in your favourite text editor:

C:\zxdb\Output\Tables\Table_search_by_titles.sql - Amend "CHARACTER SET utf8 COLLATE utf8_unicode_ci" to "COLLATE DATABASE_DEFAULT"
C:\zxdb\Output\Tables\Table_search_by_names.sql - Amend "CHARACTER SET utf8 COLLATE utf8_unicode_ci" to "COLLATE DATABASE_DEFAULT"
C:\zxdb\Output\Tables\Table_aliases.sql - Amend "COLLATE Latin1_General_CS" to "COLLATE DATABASE_DEFAULT"
C:\zxdb\Output\Tables\Table_availabletypes.sql - Amend "COLLATE Latin1_General_CS" to "COLLATE DATABASE_DEFAULT"
C:\zxdb\Output\Tables\Table_downloads.sql - Amend "COLLATE Latin1_General_CS" to "COLLATE DATABASE_DEFAULT"
C:\zxdb\Output\Tables\Table_entries.sql - Amend "COLLATE Latin1_General_CS" to "COLLATE DATABASE_DEFAULT"
C:\zxdb\Output\Tables\Table_features.sql - Amend "COLLATE Latin1_General_CS" to "COLLATE DATABASE_DEFAULT"
C:\zxdb\Output\Tables\Table_labels.sql - Amend "COLLATE Latin1_General_CS" to "COLLATE DATABASE_DEFAULT"
C:\zxdb\Output\Tables\Table_magazines.sql - Amend "COLLATE Latin1_General_CS" to "COLLATE DATABASE_DEFAULT"
C:\zxdb\Output\Tables\Table_origintypes.sql - Amend "COLLATE Latin1_General_CS" to "COLLATE DATABASE_DEFAULT" (Warning: there are two instances in this file)
C:\zxdb\Output\Tables\Table_tools.sql - Amend "COLLATE Latin1_General_CS" to "COLLATE DATABASE_DEFAULT"

That's all of the amends to the files.

- Open Management Studio and create a new database. What's vitally important here is that the COLLATION must be Case Sensitive - otherwise when the data is imported it will create key violations and fail to import. When creating the database, click the Options menu item on the left of the window that opens. Whilst we're here, set the Recovery Model to Simple unless you really need SQL to record every single amend made to the database. Just above Recovery Model is Collation and this is probably set to <default>. Unless you've changed this in the past then the default collation is Case Insensitive. Click on the Collation drop down find and select SQL_Latin1_General_CP1_CS_AS. The _CS_ means Case Sensitive (and _CI_ means Case Insensitive). Click OK to create your database.

- In Management Studio click New Query and make sure the targeted database is your newly created one. Paste the following into the New Query window:

Code: Select all

:setvar path "C:\zxdb\Output\Tables\"
:r $(path)\Table_aliases.sql
:r $(path)\Table_authors.sql
:r $(path)\Table_availabletypes.sql
:r $(path)\Table_booktypeins.sql
:r $(path)\Table_compilations.sql
:r $(path)\Table_countries.sql
:r $(path)\Table_downloads.sql
:r $(path)\Table_entries.sql
:r $(path)\Table_extras.sql
:r $(path)\Table_features.sql
:r $(path)\Table_filetypes.sql
:r $(path)\Table_formattypes.sql
:r $(path)\Table_frameworks.sql
:r $(path)\Table_genretypes.sql
:r $(path)\Table_groups.sql
:r $(path)\Table_grouptypes.sql
:r $(path)\Table_hosts.sql
:r $(path)\Table_idioms.sql
:r $(path)\Table_interviews.sql
:r $(path)\Table_issues.sql
:r $(path)\Table_labelfiles.sql
:r $(path)\Table_labels.sql
:r $(path)\Table_labeltypes.sql
:r $(path)\Table_licenses.sql
:r $(path)\Table_licensetypes.sql
:r $(path)\Table_licensors.sql
:r $(path)\Table_machinetypes.sql
:r $(path)\Table_magazines.sql
:r $(path)\Table_magfiles.sql
:r $(path)\Table_magrefs.sql
:r $(path)\Table_members.sql
:r $(path)\Table_nvgs.sql
:r $(path)\Table_origintypes.sql
:r $(path)\Table_permissions.sql
:r $(path)\Table_permissiontypes.sql
:r $(path)\Table_platforms.sql
:r $(path)\Table_ports.sql
:r $(path)\Table_publicationtypes.sql
:r $(path)\Table_publishers.sql
:r $(path)\Table_referencetypes.sql
:r $(path)\Table_relatedlinks.sql
:r $(path)\Table_releases.sql
:r $(path)\Table_remakes.sql
:r $(path)\Table_roles.sql
:r $(path)\Table_roletypes.sql
:r $(path)\Table_schemetypes.sql
:r $(path)\Table_scores.sql
:r $(path)\Table_search_by_authors.sql
:r $(path)\Table_search_by_names.sql
:r $(path)\Table_search_by_publishers.sql
:r $(path)\Table_search_by_titles.sql
:r $(path)\Table_spex_authors.sql
:r $(path)\Table_spex_companies.sql
:r $(path)\Table_spex_entries.sql
:r $(path)\Table_spex_magazines.sql
:r $(path)\Table_sscgc_authors.sql
:r $(path)\Table_toolfiles.sql
:r $(path)\Table_tools.sql
:r $(path)\Table_topics.sql
:r $(path)\Table_topictypes.sql
:r $(path)\Table_variationtypes.sql
:r $(path)\Table_websites.sql
- In the top menu, select Query then SQLCMD Mode. Press F5 to execute. It will complete in about a second.

- Now to import the data. In the New Query panel, overwrite what we've just ran with:

Code: Select all

:setvar path "C:\zxdb\Output\Inserts\"
:r $(path)\Insert_aliases.sql
:r $(path)\Insert_authors.sql
:r $(path)\Insert_availabletypes.sql
:r $(path)\Insert_booktypeins.sql
:r $(path)\Insert_compilations.sql
:r $(path)\Insert_countries.sql
:r $(path)\Insert_downloads.sql
:r $(path)\Insert_entries.sql
:r $(path)\Insert_extras.sql
:r $(path)\Insert_features.sql
:r $(path)\Insert_filetypes.sql
:r $(path)\Insert_formattypes.sql
:r $(path)\Insert_frameworks.sql
:r $(path)\Insert_genretypes.sql
:r $(path)\Insert_groups.sql
:r $(path)\Insert_grouptypes.sql
:r $(path)\Insert_hosts.sql
:r $(path)\Insert_idioms.sql
:r $(path)\Insert_interviews.sql
:r $(path)\Insert_issues.sql
:r $(path)\Insert_labelfiles.sql
:r $(path)\Insert_labels.sql
:r $(path)\Insert_labeltypes.sql
:r $(path)\Insert_licenses.sql
:r $(path)\Insert_licensetypes.sql
:r $(path)\Insert_licensors.sql
:r $(path)\Insert_machinetypes.sql
:r $(path)\Insert_magazines.sql
:r $(path)\Insert_magfiles.sql
:r $(path)\Insert_magrefs.sql
:r $(path)\Insert_members.sql
:r $(path)\Insert_nvgs.sql
:r $(path)\Insert_origintypes.sql
:r $(path)\Insert_permissions.sql
:r $(path)\Insert_permissiontypes.sql
:r $(path)\Insert_platforms.sql
:r $(path)\Insert_ports.sql
:r $(path)\Insert_publicationtypes.sql
:r $(path)\Insert_publishers.sql
:r $(path)\Insert_referencetypes.sql
:r $(path)\Insert_relatedlinks.sql
:r $(path)\Insert_releases.sql
:r $(path)\Insert_remakes.sql
:r $(path)\Insert_roles.sql
:r $(path)\Insert_roletypes.sql
:r $(path)\Insert_schemetypes.sql
:r $(path)\Insert_scores.sql
:r $(path)\Insert_search_by_authors.sql
:r $(path)\Insert_search_by_names.sql
:r $(path)\Insert_search_by_publishers.sql
:r $(path)\Insert_search_by_titles.sql
:r $(path)\Insert_spex_authors.sql
:r $(path)\Insert_spex_companies.sql
:r $(path)\Insert_spex_entries.sql
:r $(path)\Insert_spex_magazines.sql
:r $(path)\Insert_sscgc_authors.sql
:r $(path)\Insert_toolfiles.sql
:r $(path)\Insert_tools.sql
:r $(path)\Insert_topics.sql
:r $(path)\Insert_topictypes.sql
:r $(path)\Insert_variationtypes.sql
:r $(path)\Insert_websites.sql
- Press F5 to execute. This will take a couple of minutes to complete at least.

- Finally run the fixups.sql file to create all of the Foreign Keys. In the New Query window, overwrite with:

Code: Select all

:setvar path "C:\zxdb\Output\"
:r $(path)\fixups.sql
- And press F5 to execute.

When that's complete (takes a few seconds) you should have a fully imported ZXDB database sitting in SQL Server. Enjoy!!
ZX Spectrum Reviews REST API: http://zxspectrumreviews.co.uk/
User avatar
Morkin
Bugaboo
Posts: 3251
Joined: Mon Nov 13, 2017 8:50 am
Location: Bristol, UK

Re: Installing ZXDB on your local computer

Post by Morkin »

SQL noob here..! I thought I'd have a go at this, but have run into a snag or two.

I'm using SQLiteStudio and have just about worked out how to download the right things from GitHub ;)

- I've run the Python script to convert ZXDB into SQLite compatible SQL
- I've opened Einar's gargantuan SQL file in the SQL editor in SQLite

I've successfully run snippets of the SQL to create the tables, so I think it sort of works. But whenever I try one of the mahoosive INSERT commands (like INSERT INTO entries;), it hangs.

I copied a segment of it and successfully INSERTed about 700 rows, but when I try with more (e.g. 5000), it seems to hang, and after about 10 minutes the application just shuts down. I tried with a few thousand and got "Error while executing SQL query on database 'ZXDB': FOREIGN KEY constraint failed".

Is there a better/another way of getting the entries in than using the editor? I don't really want to have to do a few hundred at a time...
My Speccy site: thirdharmoniser.com
User avatar
Morkin
Bugaboo
Posts: 3251
Joined: Mon Nov 13, 2017 8:50 am
Location: Bristol, UK

Re: Installing ZXDB on your local computer

Post by Morkin »

OK, so unless I've missed something (probably) it looks as if SQLite doesn't like this statement

Code: Select all

ALTER TABLE tablename DISABLE KEYS;
Before the INSERT

I tried:

Code: Select all

PRAGMA foreign_keys = 0;
And it let me insert the rows.
My Speccy site: thirdharmoniser.com
Post Reply