(2021-03-27, 09:43)schapplm Wrote: (2021-03-25, 22:11)samsquanche Wrote: Actually for this the API for mysql and mariadb will be identical and type issues I faced would also happen with mysql as the types used for id's when using sqlite are integers but you use text with a full sql database. That's why I was suggesting the correction.
I still do not get the MySQL / MariaDB issue. The SQL code you posted is identical to the queries I use in the source code to initially create the database. According to your statement, "that did it". So no need to change anything?
In your first post, you replace the types timestamp, tinyint, smallint and int with INTEGER.
Does this mean some versions of MariaDB/MySQL do not support these types? My intention when writing the code was that timestamp is much easier to read in manually inspection (e.g. using phpMyAdmin).
Using tinyint and smallint eventually reduces the space of the database (although perhaps not significantly). Generally, I would take the type with the least size that is able to contain the data.
Hi again,
Sorry for the delay, very busy with school.
So when exporting the SQLite database with SQLite database browser it will prepare for another SQLite database with this at the beginning of the sql script:
SQL:
BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS "movie_watched" (
"idMovieImdb" INTEGER,
"playCount" INTEGER,
"lastChange" INTEGER,
"lastPlayed" INTEGER,
"title" TEXT,
PRIMARY KEY("idMovieImdb")
);
CREATE TABLE IF NOT EXISTS "episode_watched" (
"idShow" INTEGER,
"season" INTEGER,
"episode" INTEGER,
"playCount" INTEGER,
"lastChange" INTEGER,
"lastPlayed" INTEGER,
PRIMARY KEY("idShow","season","episode")
);
CREATE TABLE IF NOT EXISTS "tvshows" (
"idShow" INTEGER,
"title" TEXT,
PRIMARY KEY("idShow")
);
...
Which I changed to the following after having issues and looking at your source on GitHub:
SQL:
CREATE TABLE IF NOT EXISTS `movie_watched` (
`idMovieImdb` int unsigned NOT NULL,
`playCount` tinyint unsigned DEFAULT NULL,
`lastChange` timestamp NULL DEFAULT NULL,
`lastPlayed` timestamp NULL DEFAULT NULL,
`title` text,
PRIMARY KEY (`idMovieImdb`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `episode_watched` (
`idShow` int unsigned NOT NULL DEFAULT '0',
`season` smallint unsigned NOT NULL DEFAULT '0',
`episode` smallint unsigned NOT NULL DEFAULT '0',
`playCount` tinyint unsigned DEFAULT NULL,
`lastChange` timestamp NULL DEFAULT NULL,
`lastPlayed` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`idShow`,`season`,`episode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `tvshows` (
`idShow` int unsigned NOT NULL,
`title` text,
PRIMARY KEY (`idShow`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
There is no issues with your add-on, just the
Conversion from SQLite database to mysql database documentation.
Cheers
