Prosíme přihlašte se nebo zaregistrujte.

Přihlašte se svým uživatelským jménem a heslem.
Vaše pomoc je stále potřeba!

Autor Téma: Amarok, MySQL, UTF-8, UBUNTU HH  (Přečteno 3991 krát)

cybgate

  • Aktivní člen
  • *
  • Příspěvků: 134
    • CybGate World
Amarok, MySQL, UTF-8, UBUNTU HH
« kdy: 08 Července 2008, 13:25:08 »


Tak jsem si hrál s Amarokem, a na odkaze:
http://amarok.kde.org/wiki/MySQL_HowTo#Amarok_1.4.8_and_MySQL_5.0.45

Je script, který je ale chybný, pro češtinu nefunguje. :)

Tady je script pro vytvoření struktury databáze pro češtinu - UTF-8 a české třízení:
Kód: [Vybrat]

/*

MySQL Data Transfer

Source Host: localhost

Source Database: amarok

Target Host: localhost

Target Database: amarok

Date: 8.7.2008 13:03:05

*/



SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------

-- Table structure for admin

-- ----------------------------

DROP TABLE IF EXISTS `admin`;

CREATE TABLE `admin` (
  `noption` varchar(255) collate utf8_czech_ci default NULL,
  `value` varchar(255) collate utf8_czech_ci default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;



-- ----------------------------

-- Table structure for album

-- ----------------------------

DROP TABLE IF EXISTS `album`;

CREATE TABLE `album` (
  `id` int(11) NOT NULL,
  `name` varchar(255) collate utf8_czech_ci default NULL,
  PRIMARY KEY  (`id`),
  KEY `album_idx` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;



-- ----------------------------

-- Table structure for amazon

-- ----------------------------

DROP TABLE IF EXISTS `amazon`;

CREATE TABLE `amazon` (
  `asin` varchar(20) collate utf8_czech_ci default NULL,
  `locale` varchar(2) collate utf8_czech_ci default NULL,
  `filename` varchar(33) collate utf8_czech_ci default NULL,
  `refetchdate` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;



-- ----------------------------

-- Table structure for artist

-- ----------------------------

DROP TABLE IF EXISTS `artist`;

CREATE TABLE `artist` (
  `id` int(11) NOT NULL,
  `name` varchar(255) collate utf8_czech_ci default NULL,
  PRIMARY KEY  (`id`),
  KEY `artist_idx` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;



-- ----------------------------

-- Table structure for composer

-- ----------------------------

DROP TABLE IF EXISTS `composer`;

CREATE TABLE `composer` (
  `id` int(11) NOT NULL,
  `name` varchar(255) collate utf8_czech_ci default NULL,
  PRIMARY KEY  (`id`),
  KEY `composer_idx` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;



-- ----------------------------

-- Table structure for devices

-- ----------------------------

DROP TABLE IF EXISTS `devices`;

CREATE TABLE `devices` (
  `id` int(11) NOT NULL,
  `type` varchar(255) collate utf8_czech_ci default NULL,
  `label` varchar(255) collate utf8_czech_ci default NULL,
  `lastmountpoint` varchar(255) collate utf8_czech_ci default NULL,
  `uuid` varchar(255) collate utf8_czech_ci default NULL,
  `servername` varchar(255) collate utf8_czech_ci default NULL,
  `sharename` varchar(255) collate utf8_czech_ci default NULL,
  PRIMARY KEY  (`id`),
  KEY `devices_type` (`type`),
  KEY `devices_uuid` (`uuid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;



-- ----------------------------

-- Table structure for directories

-- ----------------------------

DROP TABLE IF EXISTS `directories`;

CREATE TABLE `directories` (
  `dir` varchar(1024) collate utf8_czech_ci default NULL,
  `deviceid` int(11) default NULL,
  `changedate` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;



-- ----------------------------

-- Table structure for embed

-- ----------------------------

DROP TABLE IF EXISTS `embed`;

CREATE TABLE `embed` (
  `url` varchar(1024) collate utf8_czech_ci default NULL,
  `deviceid` int(11) default NULL,
  `hash` varchar(1024) collate utf8_czech_ci default NULL,
  `description` varchar(255) collate utf8_czech_ci default NULL,
  KEY `embed_hash` (`hash`(333)),
  KEY `embed_deviceid_index` (`deviceid`),
  KEY `embed_url_index` (`url`(333))
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;



-- ----------------------------

-- Table structure for genre

-- ----------------------------

DROP TABLE IF EXISTS `genre`;

CREATE TABLE `genre` (
  `id` int(11) NOT NULL,
  `name` varchar(255) collate utf8_czech_ci default NULL,
  PRIMARY KEY  (`id`),
  KEY `genre_idx` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;



-- ----------------------------

-- Table structure for images

-- ----------------------------

DROP TABLE IF EXISTS `images`;

CREATE TABLE `images` (
  `path` varchar(1024) collate utf8_czech_ci default NULL,
  `deviceid` int(11) default NULL,
  `artist` varchar(255) collate utf8_czech_ci default NULL,
  `album` varchar(255) collate utf8_czech_ci default NULL,
  KEY `images_album` (`album`),
  KEY `images_artist` (`artist`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;



-- ----------------------------

-- Table structure for labels

-- ----------------------------

DROP TABLE IF EXISTS `labels`;

CREATE TABLE `labels` (
  `id` int(11) NOT NULL,
  `name` varchar(255) collate utf8_czech_ci default NULL,
  `type` int(11) default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `labels_name` (`name`,`type`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;



-- ----------------------------

-- Table structure for lyrics

-- ----------------------------

DROP TABLE IF EXISTS `lyrics`;

CREATE TABLE `lyrics` (
  `url` varchar(1024) collate utf8_czech_ci default NULL,
  `deviceid` int(11) default NULL,
  `lyrics` text character set latin1,
  `uniqueid` varchar(32) collate utf8_czech_ci default NULL,
  KEY `lyrics_uniqueid` (`uniqueid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;



-- ----------------------------

-- Table structure for playlists

-- ----------------------------

DROP TABLE IF EXISTS `playlists`;

CREATE TABLE `playlists` (
  `playlist` varchar(255) collate utf8_czech_ci default NULL,
  `url` varchar(1024) collate utf8_czech_ci default NULL,
  `tracknum` int(11) default NULL,
  KEY `playlist_playlists` (`playlist`),
  KEY `url_playlists` (`url`(333))
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;



-- ----------------------------

-- Table structure for podcastchannels

-- ----------------------------

DROP TABLE IF EXISTS `podcastchannels`;

CREATE TABLE `podcastchannels` (
  `url` varchar(1024) collate utf8_czech_ci default NULL,
  `title` varchar(255) collate utf8_czech_ci default NULL,
  `weblink` varchar(1024) collate utf8_czech_ci default NULL,
  `image` varchar(1024) collate utf8_czech_ci default NULL,
  `comment` text character set latin1,
  `copyright` varchar(255) collate utf8_czech_ci default NULL,
  `parent` int(11) default NULL,
  `directory` varchar(255) collate utf8_czech_ci default NULL,
  `autoscan` tinyint(1) default NULL,
  `fetchtype` int(11) default NULL,
  `autotransfer` tinyint(1) default NULL,
  `haspurge` tinyint(1) default NULL,
  `purgecount` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;



-- ----------------------------

-- Table structure for podcastepisodes

-- ----------------------------

DROP TABLE IF EXISTS `podcastepisodes`;

CREATE TABLE `podcastepisodes` (
  `id` int(11) NOT NULL,
  `url` varchar(1024) collate utf8_czech_ci default NULL,
  `localurl` varchar(1024) collate utf8_czech_ci default NULL,
  `parent` varchar(1024) collate utf8_czech_ci default NULL,
  `guid` varchar(1024) collate utf8_czech_ci default NULL,
  `title` varchar(255) collate utf8_czech_ci default NULL,
  `subtitle` varchar(255) collate utf8_czech_ci default NULL,
  `composer` varchar(255) collate utf8_czech_ci default NULL,
  `comment` text collate utf8_czech_ci,
  `filetype` varchar(255) character set latin1 default NULL,
  `createdate` varchar(255) character set latin1 default NULL,
  `length` int(11) default NULL,
  `size` int(11) default NULL,
  `isNew` tinyint(1) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;



-- ----------------------------

-- Table structure for podcastfolders

-- ----------------------------

DROP TABLE IF EXISTS `podcastfolders`;

CREATE TABLE `podcastfolders` (
  `id` int(11) NOT NULL,
  `name` varchar(255) collate utf8_czech_ci default NULL,
  `parent` int(11) default NULL,
  `isOpen` tinyint(1) default NULL,
  PRIMARY KEY  (`id`),
  KEY `url_podfolder` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;



-- ----------------------------

-- Table structure for related_artists

-- ----------------------------

DROP TABLE IF EXISTS `related_artists`;

CREATE TABLE `related_artists` (
  `artist` varchar(255) collate utf8_czech_ci default NULL,
  `suggestion` varchar(255) collate utf8_czech_ci default NULL,
  `changedate` int(11) default NULL,
  KEY `related_artists_artist` (`artist`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;



-- ----------------------------

-- Table structure for statistics

-- ----------------------------

DROP TABLE IF EXISTS `statistics`;

CREATE TABLE `statistics` (
  `url` varchar(1024) collate utf8_czech_ci NOT NULL default '',
  `deviceid` int(11) NOT NULL default '0',
  `createdate` int(11) default NULL,
  `accessdate` int(11) default NULL,
  `percentage` float default NULL,
  `rating` int(11) default '0',
  `playcounter` int(11) default NULL,
  `uniqueid` varchar(32) collate utf8_czech_ci default NULL,
  `deleted` tinyint(1) default '0',
  UNIQUE KEY `uniqueid` (`uniqueid`),
  KEY `percentage_stats` (`percentage`),
  KEY `rating_stats` (`rating`),
  KEY `playcounter_stats` (`playcounter`),
  KEY `uniqueid_stats` (`uniqueid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;



-- ----------------------------

-- Table structure for tags

-- ----------------------------

DROP TABLE IF EXISTS `tags`;

CREATE TABLE `tags` (
  `url` varchar(1024) collate utf8_czech_ci default NULL,
  `dir` varchar(1024) collate utf8_czech_ci default NULL,
  `createdate` int(11) default NULL,
  `modifydate` int(11) default NULL,
  `album` int(11) default NULL,
  `artist` int(11) default NULL,
  `composer` int(11) default NULL,
  `genre` int(11) default NULL,
  `title` varchar(255) collate utf8_czech_ci default NULL,
  `year` int(11) default NULL,
  `comment` text character set latin1,
  `track` decimal(4,0) default NULL,
  `discnumber` int(11) default NULL,
  `bitrate` int(11) default NULL,
  `length` int(11) default NULL,
  `samplerate` int(11) default NULL,
  `filesize` int(11) default NULL,
  `filetype` int(11) default NULL,
  `sampler` tinyint(1) default NULL,
  `bpm` float default NULL,
  `deviceid` int(11) default NULL,
  KEY `album_tag` (`album`),
  KEY `artist_tag` (`artist`),
  KEY `composer_tag` (`composer`),
  KEY `genre_tag` (`genre`),
  KEY `year_tag` (`year`),
  KEY `sampler_tag` (`sampler`),
  KEY `tags_artist_index` (`artist`),
  KEY `tags_album_index` (`album`),
  KEY `tags_deviceid_index` (`deviceid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;



-- ----------------------------

-- Table structure for tags_labels

-- ----------------------------

DROP TABLE IF EXISTS `tags_labels`;

CREATE TABLE `tags_labels` (
  `deviceid` int(11) default NULL,
  `url` varchar(1024) collate utf8_czech_ci default NULL,
  `uniqueid` varchar(32) character set latin1 default NULL,
  `labelid` int(11) default NULL,
  KEY `tags_labels_uniqueid` (`uniqueid`),
  KEY `tags_labels_labelid` (`labelid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;



-- ----------------------------

-- Table structure for uniqueid

-- ----------------------------

DROP TABLE IF EXISTS `uniqueid`;

CREATE TABLE `uniqueid` (
  `url` varchar(1024) collate utf8_czech_ci default NULL,
  `deviceid` int(11) default NULL,
  `uniqueid` varchar(32) collate utf8_czech_ci default NULL,
  `dir` varchar(1024) collate utf8_czech_ci default NULL,
  UNIQUE KEY `uniqueid` (`uniqueid`),
  KEY `uniqueid_uniqueid` (`uniqueid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;



-- ----------------------------

-- Table structure for year

-- ----------------------------

DROP TABLE IF EXISTS `year`;

CREATE TABLE `year` (
  `id` int(11) NOT NULL,
  `name` varchar(255) collate utf8_czech_ci default NULL,
  PRIMARY KEY  (`id`),
  KEY `year_idx` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;




Šupex

  • Návštěvník
  • Příspěvků: 55
Re: Amarok, MySQL, UTF-8, UBUNTU HH
« Odpověď #1 kdy: 05 Listopadu 2008, 17:41:01 »
Vím, že to vytahuju z mrtvých, nicméně je to pro mě nutnost.

Mám Ubuntu 8.1 a používám Amarok. Dáke mám taky MySQL, phpmyadmin, vlastně celý LAMP.

Celý včerejšek jsem nad tím dumal a na nic jsem nepřišel.

Zkoušel jsem různé nastavení v Amaroku, pak jsem nechal provést skript, který je zde. Sice bylo vypsáno, že skript proběhl úspěšně, nicméně paznaky v názvech písniček jsou pořád. Skript jsem prováděl přes phpmyadmina, v otevřené db Amaroku na kartě SQL. Dělám teda něco špatně? Už si nevím rady.

V podstatě by mi to ani nevadilo, ale ono to v tomhle spraseném tvaru posílá i na Last.fm.

Kdyby mi někdo pomohl byl bych neskonale vděčen ;)

cybgate

  • Aktivní člen
  • *
  • Příspěvků: 134
    • CybGate World
Re: Amarok, MySQL, UTF-8, UBUNTU HH
« Odpověď #2 kdy: 05 Listopadu 2008, 22:52:23 »
Dělal jsem to na HH 8.04.

Použil jsem grafické front-end aplikace od MySQL.
PHPadmina na lokále nepoužívám.


Šupex

  • Návštěvník
  • Příspěvků: 55
Re: Amarok, MySQL, UTF-8, UBUNTU HH
« Odpověď #3 kdy: 06 Listopadu 2008, 16:36:03 »
Tak se mi to teda podařilo přes nějakou desktopovou aplikaci, ale divím se, že přes phpmyadmina to nešlo. Každopádně jsem konečně rád, že to jede tak má. Pouze v samotném zobrazení knihovny se to pořád takto zobrazuje, ale to už je jenom piha na celé kráse Amaroku.

 

Provoz zaštiťuje spolek OpenAlt.