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
Ralf
Rick Dangerous
Posts: 2279
Joined: Mon Nov 13, 2017 11:59 am
Location: Poland

Installing ZXDB on your local computer

Post by Ralf »

It's something that I wanted to do since ZXDB exists but never found enough determination to learn all the steps myself.

So Einar publishes from time to time new version of ZXDB. As I understand it is some big SQL script creating database, tables and populating them with data. I also understand it's MySql variant of SQL.

So to install it you'll need to have MySql server first. Then you can use a tool like SqlYog to connect to the database, execute sql queries and see the results

Image

My question is - how do you install MySql server on your personal computer? Is it possible on some regular PC that you turn on and off, that has standard Windows 7, that doesn't have any fixed IP address? It may be absolutely local for me, visible only on my computer. I googled a bit but it all seemed quite complicated. Any tutorial/help how to do it in an easy way would be much appreciated.

I have experience with SQL but I'm a type of a programmer and not an admin, better at writing code than configuring ;)
User avatar
kolbeck
Manic Miner
Posts: 309
Joined: Mon Nov 13, 2017 9:04 pm

Re: Installing ZXDB on your local computer

Post by kolbeck »

Hi

I’ve made a short tutorial on how to get quickly started with ZXDB using MariaDB in a container using Docker. Find it here https://github.com/thomasheckmann/zxinfo-db

Just realized that latest Docker requires Windows 10..

/thomas
Last edited by kolbeck on Fri Jan 19, 2018 11:00 pm, edited 2 times in total.
https://api.zxinfo.dk/v3/ - ZXDB API for developers
zxinfo-file-browser - Cross platform app to manage your files
https://zxinfo.dk - another ZXDB frontend
User avatar
Mike Davies
Microbot
Posts: 137
Joined: Mon Nov 13, 2017 10:11 am

Re: Installing ZXDB on your local computer

Post by Mike Davies »

You should be able to install either MySQL or MariaDB (which might be a better option) on Windows. It will run as a service on your Windows machine, and your SQL Client (SqlYog) should be able to connect to the server via localhost -- so no need for any physical network.

Back when I was using Windows, I had WAMP running, which gives me a full-stack Apache webserver + PHP + MySQL -- that sounds like overkill for your needs.
User avatar
4thRock
Manic Miner
Posts: 415
Joined: Thu Nov 09, 2017 9:35 am
Location: Portugal

Re: Installing ZXDB on your local computer

Post by 4thRock »

I use XAMPP and that will get you a local webserver with PHP + MySql.
It comes with PhpMyAdmin to manage the SQL database.

Installation is simple, and it doesn't mess your system. You just run it when needed.
User avatar
PeterJ
Site Admin
Posts: 6852
Joined: Thu Nov 09, 2017 7:19 pm
Location: Surrey, UK

Re: Installing ZXDB on your local computer

Post by PeterJ »

I use XAMPP on Windows too.

The file which Einar provides is a MySQL or MariaDB export file. If you use PHPMyAdmin as we do on our virtual server it's really easy to import the database.

Once you get XAMPP up and running I'm happy to talk you through it. I'm sure we can give you some simple SQL to get you started as well.

Note I have not watched this video so can't vouch for it's quality. Just off out but found it on YouTube and thought it may be useful.

[youtube]https://youtu.be/N6ENnaRotmo [/youtube]
Ralf
Rick Dangerous
Posts: 2279
Joined: Mon Nov 13, 2017 11:59 am
Location: Poland

Re: Installing ZXDB on your local computer

Post by Ralf »

Thanks guys! I succeeded in installing MySql Server and restoring ZXDB.

For anybody who would like to follow my path:

1) I decided for XAMPP. The instalation is really easy and the linked Youtube video is helpful. Mostly you click "OK" and "Continue". I probably installed much too much stuff for my needs but who cares? ;)

There was a tricky moment when it started showing me warnings that I have firewall on, antivirus program on and it may make MySql server run incorrectly. I ignored it, continued and it was a good move as everything just works.

2. When you ready with installation some configuration panel appears. I just clicked "Start" for MySql and it well... started:
Image

3. XAMPP already enables you some tool for working with SQL called phpMyAdmin but I found it unfriendly. Instead I decided for SqlYog. I had previous experience with it and find it nice. Type "sqlyog community" in Google to download a free version.

So I installed SqlYog and when run for the 1st time it detected my database automatically:
Image

4. Now I had to restore ZXDB on sql server and it was the hardest moment

So I downloaded the script from Einar's site and opened it in SqlYog. It's a huge script, 50 MB big but it worked. But when I executed it it made SqlYog hang :( Maybe if I waited for 2 hours or more it would complete. But I wasn't sure if it is working or hanging so I aborted it.

Eventually I succeeded in restoring the database from command line but it still lasted 10-15 minutes. Actually it was like loading a Spectrum game from a tape - you start something and later sit and say your prayers that everything works ;) But it worked, my computer is quite old and maybe that was the problem.

The command was:
C:\xampp\mysql\bin>mysql -u root -p test < e:\zxdb_latest_mysql.sql

You may have different paths but I hope you get the idea.

And that's all. Now I can play with ZXDB and am quite happy with it :)
User avatar
PeterJ
Site Admin
Posts: 6852
Joined: Thu Nov 09, 2017 7:19 pm
Location: Surrey, UK

Re: Installing ZXDB on your local computer

Post by PeterJ »

Well done!
User avatar
R-Tape
Site Admin
Posts: 6353
Joined: Thu Nov 09, 2017 11:46 am

Re: Installing ZXDB on your local computer

Post by R-Tape »

Note to self - ZXDB is more than just a spreadsheet :mrgreen:
User avatar
RMartins
Manic Miner
Posts: 776
Joined: Thu Nov 16, 2017 3:26 pm

Re: Installing ZXDB on your local computer

Post by RMartins »

R-Tape wrote: Sat Jan 20, 2018 5:48 pm Note to self - ZXDB is more than just a spreadsheet :mrgreen:
A lot more :lol:
User avatar
Seven.FFF
Manic Miner
Posts: 735
Joined: Sat Nov 25, 2017 10:50 pm
Location: USA

Re: Installing ZXDB on your local computer

Post by Seven.FFF »

AndyC wrote some nice scripts to convert to SQL Server, too. That's what I used at first, but I soon switched to native MySql on win10. MySqlWorkBench is not a bad IDE. It connects directly to the MySql dbs on my website hosting too, which is nice.
Robin Verhagen-Guest
SevenFFF / Threetwosevensixseven / colonel32
NXtel NXTP ESP Update ESP Reset CSpect Plugins
User avatar
PeterJ
Site Admin
Posts: 6852
Joined: Thu Nov 09, 2017 7:19 pm
Location: Surrey, UK

Re: Installing ZXDB on your local computer

Post by PeterJ »

I had a day off work today and managed to get a LAMP stack installed on my Raspberry Pi 3, along with PHPMyAdmin. After spending a couple of hours working out how to extend the time-out because the database import kept timing out half way through, I managed to import ZXDB. Its all now linking up nicely with dbForge Studio for MySQL Express on my Windows PC.

Is there any interest in me writing this up?

Image
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 »

Seven.FFF wrote: Sun Jan 21, 2018 12:02 am AndyC wrote some nice scripts to convert to SQL Server, too.
Has anyone else got this script to work? I can start the script off in Powershell, it creates an Output folder with a fixups.sql file. There are also an Inserts and Tables folders but these are empty. It's definitely doing something as Powershell takes a good couple of minutes to process.

Just wondered if I'm missing something obvious.
ZX Spectrum Reviews REST API: http://zxspectrumreviews.co.uk/
User avatar
Seven.FFF
Manic Miner
Posts: 735
Joined: Sat Nov 25, 2017 10:50 pm
Location: USA

Re: Installing ZXDB on your local computer

Post by Seven.FFF »

I had to mod it slightly to get it to pick the right directories. I’ll see if I can find what I did.

Sounds like the same issue. It is creating everything, but some of it is in completely the wrong place.
Robin Verhagen-Guest
SevenFFF / Threetwosevensixseven / colonel32
NXtel NXTP ESP Update ESP Reset CSpect Plugins
User avatar
Seven.FFF
Manic Miner
Posts: 735
Joined: Sat Nov 25, 2017 10:50 pm
Location: USA

Re: Installing ZXDB on your local computer

Post by Seven.FFF »

I suspect it depends on your Perl install, so Andy didn’t pick it up.

I have to say it’s a pretty slow process. After running the scripts a few times I just sucked it up and installed MySQL.
Robin Verhagen-Guest
SevenFFF / Threetwosevensixseven / colonel32
NXtel NXTP ESP Update ESP Reset CSpect Plugins
AndyC
Dynamite Dan
Posts: 1386
Joined: Mon Nov 13, 2017 5:12 am

Re: Installing ZXDB on your local computer

Post by AndyC »

It shouldn't need Perl, but I think (skimming over the source code on GitHub) that it expects your PowerShell working directory to be set to the same directory that the ZXDB_mysql.sql file is in. I should probably have put better error checking in, but it was more intended as a proof of concept and a chance to play around with ZXDB in an RDBMS I was familiar with.
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 »

Tried the Working Directory to no avail I'm afraid. I'm going to have a bash at the conversion through .NET.
ZX Spectrum Reviews REST API: http://zxspectrumreviews.co.uk/
User avatar
Seven.FFF
Manic Miner
Posts: 735
Joined: Sat Nov 25, 2017 10:50 pm
Location: USA

Re: Installing ZXDB on your local computer

Post by Seven.FFF »

Sorry Andy, don’t know where I git Perl from. I meant power shell. It’s been a while, and I didn’t find my modded files yet.

I think what I did is figure out one of the missing file names, and search for it on the entire disk. Then I googled the directory they had ended up in, found the part of the script that was referencing that directory, and changed it so it was putting it in the same place as one of the other files that wasn’t missing.
Robin Verhagen-Guest
SevenFFF / Threetwosevensixseven / colonel32
NXtel NXTP ESP Update ESP Reset CSpect Plugins
AndyC
Dynamite Dan
Posts: 1386
Joined: Mon Nov 13, 2017 5:12 am

Re: Installing ZXDB on your local computer

Post by AndyC »

I think it's because the .NET working directory isn't necessarily the same as the PowerShell one.

Here's an updated version which is a bit more explicit about the files it creates, so should guarantee everything ends up in the right place. And displays where it plans to create files just in case. ;-)

Code: Select all

$ErrorActionPreference = "Stop";

#Get working directory
$root = $pwd.Path

$tableFolder = Join-Path $root 'Output\Tables'
$insertFolder = Join-Path $root 'Output\Inserts'
$sourceFile = Join-Path $root 'ZXDB_mysql.sql'
$currentOutputFile = Join-Path $root 'Output\preamble.sql'
$postfixFile = Join-Path $root 'Output\fixups.sql'

Write-Host "Source: $($sourceFile)"
Write-Host "Tables: $($tableFolder)"
Write-Host "Inserts: $($insertFolder)"

if (!(Test-Path $sourceFile)) {Write-Error 'Source fille missing';return}

# Create output folders
if (!(Test-Path $tableFolder)) {mkdir $tableFolder}
if (!(Test-Path $insertFolder)) {mkdir $insertFolder}

@"
Use ZXDB
Go

"@ | Out-File $postfixFile -Encoding utf8

write-progress -id 1 -Activity 'Rewriting files' -status "loading source data" -PercentComplete -1

$source = Get-Content $sourceFile -Encoding utf8
$totalRows = $source.count

$sw = new-object System.IO.Streamwriter($currentOutputfile, $false, [System.Text.Encoding]::UTF8)

for ($rownum =0; $rownum -lt $totalRows; $rownum++)
{
    $progress = ($rownum / $totalRows) * 100
    write-progress -id 1 -Activity 'Rewriting files' -status "% complete: $('{0:N2}' -f $progress)" -PercentComplete $progress

    $text = $source[$rownum]

    $text = $text -replace '`text`','[text]' # escape columns with reserved word names
    $text = $text -Replace '`','' # Remove backticks
    $text = $text -Replace "\\'","''" # Change escaped quotes

    # Change file for table create
    If ($text -match '^Create Table If Not Exists (\w+)') {
        $tableName = $Matches[1]

        $text = @"
Use ZXDB
Go

If Object_ID('$($tableName)') Is Not Null Drop Table $TableName
Create Table $tableName (
"@

        $currentOutputfile = Join-Path $tableFolder "Table_$($tableName).sql"

        $sw.Close()        
        $sw = new-object System.IO.Streamwriter($currentOutputfile, $false, [System.Text.Encoding]::UTF8)
       
        $rowlimit = 0 # Can have as many rows as we like in Create Table file
        $processingTable = $true 
    }

    # Change file for insert into
    If ($text -match '^Insert Into (\w+)') {
        $tableName = $Matches[1]
        $count = 0
        $lastfile = $currentOutputFile
        $currentOutputfile = Join-Path $insertFolder "Insert_$($tableName).sql"

        # don't recreate the file if it's a continuation (because we just hit a secondary INSERT statement)
        if ($lastfile -ne $currentOutputFile)
        {
            $sw.Close()        
            $sw = new-object System.IO.Streamwriter($currentOutputfile, $false, [System.Text.Encoding]::UTF8)
        }
        $rowlimit = 1000
        $processingTable = $false #Avoid possibility of table fixups corrupting data
        $idfix = @"
Use ZXDB
Go

If Exists(Select * From sys.columns Where is_identity = 1 And Object_Name(Object_Id) = '$TableName')
Begin
    Set Identity_Insert $tableName On
End
`n
"@ 
        $sw.WriteLine($idfix)
        $insertPreamble = $idfix + $text
    }

    if ($processingTable) {

        # Remove weird MySql column width sizes from int types and remove unsigned suffix since SQL Server doesn't have unsigned types
        $text = $text -replace '(?<name>\w+)\s+(?<type>\w+)?int\((?<size>\w+)\)\s*(?<sign>unsigned)?\s+','${name} ${type}int '

        # Rewrite mediumtext to varchar(max)
        $text = $text -replace 'mediumtext','varchar(max)'

        #Rewrite tinyint to smallint, since SQL Server doesn't allow negatives in tinyint columns
        $text = $text -replace 'tinyint','smallint'
    
        #Remove Default Null constraints, they don't make much sense
        $text = $text -replace 'DEFAULT NULL',''

        # Fix up identity values
        $text = $text -replace 'ENGINE=InnoDB AUTO_INCREMENT=(?<autoincrement>\d+)? DEFAULT CHARSET=utf8 COLLATE=utf8_bin;',"`n dbcc checkident($tablename, RESEED, `${autoincrement})"

        # For auto increment columns, make them identity columns (fix up values with dbcc later)
        $text = $text -replace 'AUTO_INCREMENT','Identity(1,1)'

        # Clean up mysql table options
        $text = $text -replace 'ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin',''

        #Rewrite Unique Constraints, make sure they're unqiue a MySql names aren't
        $text = $text -replace 'UNIQUE KEY (\[)?(?<keyname>\w+)(\])? \((?<cols>[\[|\]|\w|\s|,]+)\)',"Constraint UQ_$($TableName)_`${keyname} Unique (`${cols})"

        #Name primary keys, so it's easier to patch them up later if need be
        $text = $text -replace 'PRIMARY KEY \((?<cols>[\[|\]|\w|\s|,]+)\)',"Constraint PK_$($tableName) Primary Key Clustered (`${cols})"

        #Deal with inline indexes by writing them to a Post Fix file
        if ($text -match '^\s+KEY (?<keyname>\w+) \((?<cols>[\[|\]|\w|\s|,]+)\)')
        {
            "Create Nonclustered Index $($Matches['Keyname']) On $($TableName)($($Matches['cols']))" | Out-File $postfixFile -Encoding utf8 -Append -NoClobber
            $text = '--' + $text
        }

        # Move foreign key constraint into Post Fix file, so table order deployment doesn't have to matter
        if ($text -match '^\s+Constraint\s+(?<name>\w+)\s+Foreign Key \((?<cols>[\[|\]|\w|\s|,]+)\) References (?<foreignTable>\w+) \((?<foreignCols>[\[|\]|\w|\s|,]+)\)')
        {
            "Alter Table $TableName Add Constraint $($Matches['Name']) Foreign Key ($($Matches['cols'])) References $($Matches['foreignTable']) ($($Matches['foreignCols']))" | 
                Out-File $postfixFile -Encoding utf8 -Append -NoClobber
            $text = '--' + $text
        }

        # Fix collations
        $text = $text -replace 'COLLATE utf8_bin','COLLATE DATABASE_DEFAULT'

        # Change columns that currently need case sensitive behaviour
        if (($tableName -eq 'aliases' -and $text -match '^\s*title')`
            -or ($tableName -eq 'availabletypes' -and $text -match '^\s*id')`
            -or ($tableName -eq 'labels' -and $text -match '^\s*name')`
            -or ($tableName -eq 'magazines' -and $text -match '^\s*name')`
            -or ($tableName -eq 'origintypes' -and $text -match '^\s*id')`
            -or ($tableName -eq 'origintypes' -and $text -match '^\s*\[text\]')`
            -or ($tableName -eq 'tools' -and $text -match '^\s*title')`
            -or ($tableName -eq 'downloads' -and $text -match '^\s*origintype_id')`
            -or ($tableName -eq 'entries' -and $text -match '^\s*availabletype_id')`
            -or ($tableName -eq 'features' -and $text -match '^\s*name')`
        )
        {
            
            $text = $text -replace 'DATABASE_DEFAULT','Latin1_General_CS_AS'
        }

        # Remove unique constraints on tables that seem to have non-unique data
        if (($tableName -eq 'magrefs' -and $text -match '^\s*Constraint UQ_magrefs_uk_magref_entry')`
            -or ($tableName -eq 'magrefs' -and $text -match '^\s*Constraint UQ_magrefs_uk_magref_label')`
            -or ($tableName -eq 'magrefs' -and $text -match '^\s*Constraint UQ_magrefs_uk_magref_topic')`
            #-or ($tableName -eq 'features' -and $text -match '^\s*Constraint UQ_features_uk_feature')`
        )
        {
            $text = '--' + $text + ' -- broken constraint'
        }

    } else {
        $count++
        # Remove trailing comma from last row of insert
        if ($count -eq $rowlimit)
        {
            if ($text -match '(?<line>\s*\(.*\)),')
            {
                $text = $Matches['line'] +  '; -- reached insert limit'
            }
        }

        # Can only insert 1000 values per insert statement, so adjust and write to a new file cause SSMS copes better
        if ($rowlimit -ne 0) {
            if ($count -gt $rowlimit) {
                $count = 1

                $sw.WriteLine("Go`n")
                $sw.WriteLine($insertPreamble)
        
            }
        }
    }

    $sw.WriteLine($text)
}
$sw.Close()
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 »

[mention]AndyC[/mention] You sir, are a genius and a gentleman! Thank you so much! Other than a few tweaks I had to make, that I mention below, I go it to work perfectly. I can now work on reverse-engineering ZXDB into ZXSR's database and get rid of a load of crap data I've had for years.

The only amends required were to the Table_ files:

[search_by_titles] has an invalid CHARACTER reference. Altered "CHARACTER SET utf8 COLLATE utf8_unicode_ci" to "COLLATE DATABASE_DEFAULT"
Ditto [search_by_names]

The following tables have the wrong collation (COLLATE LATIN...) and will cause errors when the Foreign Keys are created when running the fixups.sql. I have simply renamed the COLLATE LATIN... to COLLATE DATABASE_DEFAULT:

Table_tools
Table_origintypes
Table_magazines
Table_labels
Table_features
Table_entries
Table_downloads
Table_availabletypes
Table_aliases

Cheers again. Vampyre.
ZX Spectrum Reviews REST API: http://zxspectrumreviews.co.uk/
AndyC
Dynamite Dan
Posts: 1386
Joined: Mon Nov 13, 2017 5:12 am

Re: Installing ZXDB on your local computer

Post by AndyC »

No worries. When I'm somewhere with better broadband I'll have a look at adding fixes for those into the main script (and submit it to the github site).
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: 3250
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: 3250
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