SQL for dummies

[Einar Saukas]

I used the original script to build it in SQLiteStudio last time. Only thing is it couldn't cope with running the whole thing so I had to break it into separate bits and run them individually. In fact most table INSERTs I had to break up into loads of separate inserts - the whole process took me about a week, so I'm not doing that again..

If you prefer SQLiteStudio, that's fine! I suggest you keep using SQLiteStudio, but install HeidiSQL too.

Whenever you need to run a larger SQL script, then simply start HeidiSQL, open "File / Run SQL file", select the SQL script, afterwards close HeidiSQL and go back to SQLiteStudio.

This way, updating ZXDB will take 30 seconds instead of 1 week.

I guess I could wade through the SQL update script files to see what's been added/changed, and at some point I'll probably hit one that's already reflected in my copy. Then I can just run them from there. But there didn't seem to be anything in the files (comments etc.) indicating which update it is, or what date (I'm assuming they are incrementally in date order).

Yes, the ZXDB incremental scripts are numbered in date order.

The ZXDB release dates are listed here:

https://github.com/zxdb/ZXDB/commits/master

These release dates correspond to the file dates of the incremental scripts.

[Morkin]

Thanks Einar. There may be a few steps missing I think (remember, this thread is for dummies)...

So far I've:

- Downloaded HeidiSQL (portable) and unzipped
- Run heidisql.exe

I couldn't open ZXDB_mysql.sql at this point, but I:

- Created a new connection
- Selected Network type SQLIte
- Specified the database/path to the existing ...\ZXDB.db file that I'm currently using
- Clicked 'Open'

This then showed me a bunch of ZXDB tables on the left and records on the right so I guess I'm connected OK at this point. Then from the menu, I did this:

- File > Run SQL file...
- Select ZXDB_mysql.sql (latest version) and Open

I get this:

[Einar Saukas]

My apologies! I have never SQLiteStudio and I thought it supported the ZXDB script syntax, because you previously wrote that you were able to execute it after breaking down INSERTs. But it seems I misunderstood what you meant, sorry!

Luckily @AndyC already implemented a Python script to convert ZXDB from MySQL to SQLite. You can download it from here. Just put this script and file "ZXDB_mysql.sql" in the same folder, then run this script using Python. It will generate a new file "ZXDB_sqlite.sql" that you should be able to run directly using SQLiteStudio or HeidiSQL. If you have any problems, let me know and I will try to help. Good luck!

[Morkin]

Oh, thanks Einar - I've just remembered that I ran a Python script to generate the SQLite.sql file first time round, but deleted it afterwards once it was done, and forgot all about it...

Am still having problems with constraints right at the start:

Code: Select all

SQL Error (19): UNIQUE constraint failed: aliases.entry_id, aliases.release_seq, aliases.language_id, aliases.title

I think I got round this last time by running:

Code: Select all

PRAGMA foreign_keys = 0;

...within SQLite Studio. I tried running the same in Heidi, along with:

Code: Select all

PRAGMA ignore_check_constraints = true;

(not 100% sure what this actually does though)

But I still get the error/warning.

In the .sql file there are lines like:

Code: Select all

/*!40000 ALTER TABLE aliases DISABLE KEYS */;

Do I have to do something with those?

[AndyC]

The lines between /* and */ are comments and should be ignored (MySQL embeds some internal configuration in these but it shouldn't break things)

SQLite is a very slimmed down implementation of SQL (to the point of barely being SQL at all) and I seem to recall I had to special case a bunch of tables to get the conversion working properly.

If you plan on doing a lot with ZXDB and aren't comfortable updating the conversion script yourself, I'd definitely lean more towards installing a MySQL or MariaDB instance and just working of the normal files. The conversion scripts were mainly for trying things out and probably haven't been stress tested enough through the various iterations of ZXDB

[Morkin]

The lines between /* and */ are comments and should be ignored (MySQL embeds some internal configuration in these but it shouldn't break things)

SQLite is a very slimmed down implementation of SQL (to the point of barely being SQL at all) and I seem to recall I had to special case a bunch of tables to get the conversion working properly.

If you plan on doing a lot with ZXDB and aren't comfortable updating the conversion script yourself, I'd definitely lean more towards installing a MySQL or MariaDB instance and just working of the normal files. The conversion scripts were mainly for trying things out and probably haven't been stress tested enough through the various iterations of ZXDB

No worries, I appreciate the effort already put in as I'm fully aware I haven't paid for professional tech support..

I noticed those comments before each table update in the SQL file. I was just wondering whether the commented-out bits were there so they could be used to enable the bypassing of constraints or something.

One issue I have is doing anything at all with the massive .sql file, it seems to crash or hang just about any app I use to view it in

Oddly enough I've been learning a bit of Python and regular expressions (both about as good as my SQL at the moment). I could probably have a play with the script, see what happens... (Then when I fail miserably I can have a look at the MySQL/MariaDB option).

[Einar Saukas]

Am still having problems with constraints right at the start:

Code: Select all

SQL Error (19): UNIQUE constraint failed: aliases.entry_id, aliases.release_seq, aliases.language_id, aliases.title

Are you running this script into an already populated database? You need to create a new empty database before running this script, otherwise you will try to insert data that's already in the database.

I think I got round this last time by running:

Code: Select all

PRAGMA foreign_keys = 0;

Please report here everything you have to modify in the generated script, so I can improve the converter.

Code: Select all

PRAGMA ignore_check_constraints = true;

(not 100% sure what this actually does though)

This is probably not needed, but I'm not sure.

[Morkin]

Are you running this script into an already populated database? You need to create a new empty database before running this script, otherwise you will try to insert data that's already in the database.

...There it is.. I knew it'd be something simple..

I (probably stupidly) assumed that the SQL script would drop any existing tables that might already be in the DB, I didn't know I had to empty it myself.

Anyway all sorted now, thanks for your help guys..! I don't think I had to do anything extra with the script, it seems to have worked OK - I checked and can see it's picked up the last incremental logged changes.

[Einar Saukas]

Awesome!

Did you really have to add "PRAGMA foreign_keys = 0"?

[Morkin]

Awesome!

Did you really have to add "PRAGMA foreign_keys = 0"?

I don't think it was needed when the DB was empty - I'll double-check when I try it again for the next update.

[Einar Saukas]

OK! If you find out it's needed, let me know so I can update the converter.

[marenja]

quick import into SQLite db

Code: Select all

echo.|sqlite3.exe -init ZXDB_sqlite.sql any_file_name.db3

sqlite3.exe is a command-line shell program that can be downloaded from https://sqlite.org/download.html Look for "A bundle of command-line tools for managing ..." at download page. Documentation https://sqlite.org/cli.html

details below

I guess that may be a more efficient way to remove/replace the DB then? I know about as much Powershell as I do SQL, but if it does it fairly quickly, I'm all in...

I guess you perform your SQL updates with multiple copy and paste manually to editor. This is wrong way as 94mb script is too much for any editor.

I use sqlite3.exe console util to import db from scratch (windows). Your need 3 files in same dir, sqlite3.exe + ZXDB_mysql.sql + ZXDB_to_SQLite.py

1st step - convert ZXDB_mysql.sql to ZXDB_sqlite.sql (you have done it so now there are 4 files in same dir)
2nd step - import ZXDB_sqlite.sql into any_file_name.db3 with following command

Code: Select all

echo.|sqlite3.exe -init ZXDB_sqlite.sql any_file_name.db3

To do step #2 you need to run console (not powershell but cmd.exe) and enter "echo.|sqlite3.exe -init ..." Yout can insert the string above into console. Pay attention it should be no spaces between "echo" and "sqlite" otherwize you can get into sqlite cli-tool. If you opened sqlite-cli then type ".quit" and press enter.

All time sqlite needed to complete 2nd step was less than 12 seconds on SSD drive.

Just delete old file any_file_name.db3 next time you will rebuild DB with latest changes. This method DOES NOT save updates to DB you have entered yourself.

You are in sqlite-cli when you see this message and every line will start from "sqlite>" after that.

Code: Select all

sqlite3 -init ZXDB_sqlite.sql zxdb.db3
-- Loading resources from ZXDB_sqlite.sql
SQLite version 3.37.1 2021-12-30 15:30:28
Enter ".help" for usage hints.
sqlite>

[Morkin]

Thanks @marenja, I missed that when I was browsing the SQLite docs. Not sure how

As I've got HeidiSQL set up I've been using that and it's been working OK so far - takes < 1 minute and worked fine with the latest update - but good to know that there are non-Heidi alternatives.

The scripts I've used have been generating ZXDB.db. I had to look up .db3 - I guess it's just an SQLite database version?

[Vampyre]

Thanks for the post @marenja. I need to get a more recent version of ZXDB into SQLite so I'll give that a bash as my current workaround is a bit of a slog.

I guess you perform your SQL updates with multiple copy and paste manually to editor. This is wrong way as 94mb script is too much for any editor.

This is true to a certain extent but there are some very good free text editors out there that will cope with massive files. I speak from experience as I once had to fix a massive DriveTime .sql text file that had several million syntax errors which the provider wouldn't fix. It was nearly 10GB in size.

Sublime Text (https://www.sublimetext.com/) copes with files pretty well up to around 250Mb. It's shareware (it'll nag to upgrade but work forever) and is IMO the best text editor around.

For massive files, and for that DriveTime file I mentioned, I had to use Large File Editor (https://www.liquid-technologies.com/large-file-editor). It's really quite magical how well, and fast considering, it works. At least I think it was this editor - the name rings a bell. I know it was a free version anyway and it coped magnificently. It was a few years ago and I think I just triggered some PTSD writing this

Could I suggest to the admins that we get some sort of sticky thread with instructions for importing ZXDB into different database engines? I think it could be quite useful. Particularly something like SQLite where the user doesn't have to install some big database engine installation that's constantly consuming resources.

[marenja]

The scripts I've used have been generating ZXDB.db. I had to look up .db3 - I guess it's just an SQLite database version?

That is just an extension, can be any. May be SQLite hints that it is 3rd version but I have only seen only 3rd major version, not 2nd. SQLite uses .db, or .db3, or .sqlite for file extension - but it can be any or it can be none at all. The only meaning is if you have windows file association (double-click to open with some program, e.g. if HeidiSQL has done it during installation). And fancy icon can be displayed if association was done.

As I've got HeidiSQL set up I've been using that and it's been working OK so far - takes < 1 minute and worked fine with the latest update - but good to know that there are non-Heidi alternatives.

I'm almost sure HeidiSQL runs the same SQLite at backend and performs similar operations. The main idea was that correct way is using sql dump files as "scripts" but not as text in interactive sql-windows. So the option in your SQL tool should be like "import" / "bulk import" or similar.

[Morkin]

That is just an extension, can be any. May be SQLite hints that it is 3rd version but I have only seen only 3rd major version, not 2nd. SQLite uses .db, or .db3, or .sqlite for file extension - but it can be any or it can be none at all. The only meaning is if you have windows file association (double-click to open with some program, e.g. if HeidiSQL has done it during installation). And fancy icon can be displayed if association was done.

I'm almost sure HeidiSQL runs the same SQLite at backend and performs similar operations. The main idea was that correct way is using sql dump files as "scripts" but not as text in interactive sql-windows. So the option in your SQL tool should be like "import" / "bulk import" or similar.

OK - I will try the SQLite command line build for the next ZXDB update.

[marenja]

OK - I will try the SQLite command line build for the next ZXDB update.

Do it in case you are curious about shell. Otherwise it is redundant in case you use HeidiSQL as main tool. Because heidi does import fast enough and ...

I'm almost sure HeidiSQL runs the same SQLite at backend and performs similar operations.

[Sol_HSA]

Slightly on a tangent, but here's a sqlite-based interactive SQL tutorial I wrote at one point: http://sol.gfxile.net/g3/

[Morkin]

Slightly on a tangent, but here's a sqlite-based interactive SQL tutorial I wrote at one point: http://sol.gfxile.net/g3/

Looks interesting...

[Morkin]

I use sqlite3.exe console util to import db from scratch (windows). Your need 3 files in same dir, sqlite3.exe + ZXDB_mysql.sql + ZXDB_to_SQLite.py

1st step - convert ZXDB_mysql.sql to ZXDB_sqlite.sql (you have done it so now there are 4 files in same dir)
2nd step - import ZXDB_sqlite.sql into any_file_name.db3 with following command

Code: Select all

echo.|sqlite3.exe -init ZXDB_sqlite.sql any_file_name.db3

Bump..!

Just a quick update to say that I finally got round to updating my copy of ZXDB. These instructions for sqlite3.exe worked a treat, took me about 60 seconds to update, and only needed typing in two command line instructions.

So thanks @marenja..! And also @Einar Saukas & others for the pointers.