ZXDB and Microsoft SQL

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
PeterJ
Site Admin
Posts: 6854
Joined: Thu Nov 09, 2017 7:19 pm
Location: Surrey, UK

ZXDB and Microsoft SQL

Post by PeterJ »

Rather a specialist question, but thought I would give it a go.

I have downloaded ZXDB from the GitHub page:

https://github.com/zxdb/ZXDB

I'm converting from MariaDB to Microsoft SQL. I have run the PowerShell script and now have two folders in the Output directory. One called 'Inserts', and one called 'Tables'.

I'm pretty new to SQL Server Management Studio, and wondered how I go about importing the tables to re-build the data? I think [mention]Vampyre[/mention] is a MS-SQL man.
Ralf
Rick Dangerous
Posts: 2279
Joined: Mon Nov 13, 2017 11:59 am
Location: Poland

Re: ZXDB and Microsoft SQL

Post by Ralf »

I guess "Tables" folder contains scripts for creating... well, tables while "Inserts" contains actual data inserted into the tables by INSERT commands. So you first create tables, then populate them with data.

I have experience with SSMS and T-SQL, feel free to ask any specific questions.
User avatar
PeterJ
Site Admin
Posts: 6854
Joined: Thu Nov 09, 2017 7:19 pm
Location: Surrey, UK

Re: ZXDB and Microsoft SQL

Post by PeterJ »

Ralf wrote: Mon Jul 01, 2019 9:13 pm I guess "Tables" folder contains scripts for creating... well, tables while "Inserts" contains actual data inserted into the tables by INSERT commands. So you first create tables, then populate them with data.

I have experience with SSMS and T-SQL, feel free to ask any specific questions.
Thanks [mention]Ralf[/mention]. I sort of expected that, but as there is a script for each table, I assume there is a way of doing them in a batch?
User avatar
Vampyre
Manic Miner
Posts: 833
Joined: Wed Nov 15, 2017 2:51 pm
Contact:

Re: ZXDB and Microsoft SQL

Post by Vampyre »

PeterJ wrote: Mon Jul 01, 2019 9:03 pm Rather a specialist question, but thought I would give it a go.

I have downloaded ZXDB from the GitHub page:

https://github.com/zxdb/ZXDB

I'm converting from MariaDB to Microsoft SQL. I have run the PowerShell script and now have two folders in the Output directory. One called 'Inserts', and one called 'Tables'.

I'm pretty new to SQL Server Management Studio, and wondered how I go about importing the tables to re-build the data? I think @Vampyre is a MS-SQL man.
Peter, try the instructions in viewtopic.php?f=32&t=328&p=17143#p17143. I managed to get everything into MS-SQL, it's a bit of a faff with Powerscript involved though, but it does it all in three batches and only takes a few minutes. If there are any new tables you will have to include them in the scripts. There may also be some COLLATE errors in the new table Inserts as well which will have to be manually fixed.
ZX Spectrum Reviews REST API: http://zxspectrumreviews.co.uk/
User avatar
PeterJ
Site Admin
Posts: 6854
Joined: Thu Nov 09, 2017 7:19 pm
Location: Surrey, UK

Re: ZXDB and Microsoft SQL

Post by PeterJ »

Thanks [mention]Vampyre[/mention], and sorry I missed your original post.

I'm having to learn Reporting Services for a new role I'm starting so thought I would use ZXDB for as I know the database structure.

PowerBI next!

Cheers again.
User avatar
PeterJ
Site Admin
Posts: 6854
Joined: Thu Nov 09, 2017 7:19 pm
Location: Surrey, UK

Re: ZXDB and Microsoft SQL

Post by PeterJ »

All working now [mention]Vampyre[/mention]. Cheers.

This is chart to show original ZX Spectrum releases by calendar year.

Image

Code: Select all

SELECT        count(entries.id), releases.release_year
FROM            entries INNER JOIN
                         releases ON entries.id = releases.entry_id
WHERE        (releases.release_seq = 0) and entries.machinetype_id in (1,2,3,4,5,6,7,8,9,10) and releases.release_year is not null
Group By releases.release_year
Order by releases.release_year
User avatar
Juan F. Ramirez
Bugaboo
Posts: 5101
Joined: Tue Nov 14, 2017 6:55 am
Location: Málaga, Spain

Re: ZXDB and Microsoft SQL

Post by Juan F. Ramirez »

Curious the year 1984 is the peak, not 86 or 87 (as I thought)
Post Reply