Converting SQLite to MySQL

[Andrew Turner]

Dominik,

The only other thing I can think of for the actual database structure
is the use of AUTO_INCREMENT in MySQL for PRIMARY KEY "id" columns on
quite a lot of tables, although I'm not sure it matters.

e.g.
SQLite:
CREATE TABLE artist ( id INTEGER PRIMARY KEY , name VARCHAR( 255 ));

MySQL:
CREATE TABLE artist ( id INTEGER PRIMARY KEY AUTO_INCREMENT, name
VARCHAR( 255 ));

Andrew

On 12/11/06, Dominik Karall <dominik.karall at gmx.net> wrote:
> Hi Andrew,
>
> thanks for you reply! Do you know if there are any other differences
> between MySQL and SQLite regarding to the usage with Amarok? Because
> if so, it would be great if I could include them as well to have the
> MySQL DB correctly built for Amarok to avoid errors in future.
>
> If everything runs well I would for sure update the wiki too.
>
> Thanks,
> dominik
>
> On Sunday, 12. November 2006 15:29, Andrew Turner wrote:
> > Dominik,
> >
> > Amarok does have quite a lot of DB-specific code, including the
> > types for the "url" column. For MySQL we use VARBINARY( 255 ) to
> > combat both the problems you've described - case insensitivity and
> > MySQL4's stupid maximum column length (for indices and hence
> > primary keys, I believe)
> >
> > If you change VARCHAR(1024) to VARBINARY( 255 ) it might work for
> > you, but as the wiki mentions, this whole procedure is unsupported.
> > Unless you really really value your statistics, it might be worth
> > starting with a clean MySQL DB, or you just stick with SQLite.
> >
> > If you do decide to try it and it works, it would be great if you
> > could let us know and either you or one of us could update the
> > wiki.
> >
> > Andrew
> >
> > On 12/11/06, Dominik Karall <dominik.karall at gmx.net> wrote:
> > > hi,
> > >
> > > I tried to convert my sqlite database to mysql and followed the
> > > instructions on:
> > > http://amarok.kde.org/wiki/MySQL_HowTo
> > >
> > > following errors occured:
> > > -
> > > ERROR 1071 (42000) at line 23843: Specified key was too long; max
> > > key length is 999 bytes
> > >
> > > line 23843: CREATE TABLE statistics (url VARCHAR(1024),deviceid
> > > INTEGER,createdate INTEGER,accessdate INTEGER,percentage
> > > FLOAT,rating INTEGER DEFAULT 0,playcounter INTEGER,uniqueid
> > > VARCHAR(32) UNIQUE,deleted BOOL DEFAULT 0,PRIMARY KEY(url,
> > > deviceid) ); -
> > > there were more such errors, that were caused by UTF-8 which
> > > needs 3byte per character, so 1024 must be changed to something
> > > about 300.
> > >
> > > another annoying bug:
> > > mysql seems to be case insensitive on inserts, so all my songs
> > > which are unique on my hdd (e.g. FOD.mp3 and fod.mp3) are treated
> > > as duplicate in mysql.
> > >
> > > Conclusion:
> > > IMHO there is no easy way to convert sqlite dbs to mysql. maybe
> > > these errors should be mentioned on the wiki too.
> > > but I'm asking myself, if these create table statements and
> > > inserts don't work with mysql correctly, how do they work native
> > > in amarok? because the case insensitivity could cause some
> > > problems with uniqueness.
> > >
> > > cheers,
> > > dominik
> > > _______________________________________________
> > > Amarok mailing list
> > > Amarok at kde.org
> > > https://mail.kde.org/mailman/listinfo/amarok
>