Fórum Ubuntu CZ/SK
Ostatní => Tipy a triky pro Linux => Téma založeno: cybgate 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í:
/*
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;
-
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 ;)
-
Dělal jsem to na HH 8.04.
Použil jsem grafické front-end aplikace od MySQL.
PHPadmina na lokále nepoužívám.
-
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.