blognote-info.com

--
-- récupération des noeuds
--
drop table if exists `node`;
CREATE TABLE `node` (
  `nid` int(10) unsigned NOT NULL auto_increment,
  `vid` int(10) unsigned NOT NULL default '0',
  `type` varchar(32) NOT NULL default '',
  `language` varchar(12) NOT NULL default '',
  `title` varchar(255) NOT NULL default '',
  `uid` int(11) NOT NULL default '0',
  `status` int(11) NOT NULL default '1',
  `created` int(11) NOT NULL default '0',
  `changed` int(11) NOT NULL default '0',
  `comment` int(11) NOT NULL default '0',
  `promote` int(11) NOT NULL default '0',
  `moderate` int(11) NOT NULL default '0',
  `sticky` int(11) NOT NULL default '0',
  `tnid` int(10) unsigned NOT NULL default '0',
  `translate` int(11) NOT NULL default '0',
  PRIMARY KEY  (`nid`),
  UNIQUE KEY `vid` (`vid`),
  KEY `node_changed` (`changed`),
  KEY `node_created` (`created`),
  KEY `node_moderate` (`moderate`),
  KEY `node_promote_status` (`promote`,`status`),
  KEY `node_status_type` (`status`,`type`,`nid`),
  KEY `node_title_type` (`title`,`type`(4)),
  KEY `node_type` (`type`(4)),
  KEY `uid` (`uid`),
  KEY `tnid` (`tnid`),
  KEY `translate` (`translate`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
 
-- autoincrement dynamique
set @autoincrement := 0;
select @autoincrement := max(post_id) from dc_post;
SET @s = CONCAT("alter table node auto_increment=",@autoincrement); 
PREPARE stmt FROM @s; 
EXECUTE stmt; 
DEALLOCATE PREPARE stmt;
 
set @revision := 0;
insert into node
select post_id as nid, @revision:=@revision+1 as vid, 'story' as type, 'fr' as language, post_titre as title, 1 as uid, 1 as status, 
  UNIX_TIMESTAMP(post_creadt) as created, UNIX_TIMESTAMP(post_upddt) as changed, 1 as comment, 1 as promote, 0 as moderate, 0 as sticky, 0 as tnid, 0 as translate
from dc_post
order by post_id;
 
--
-- récupération des urls d'accès aux noeuds
--
drop table if exists `url_alias`;
CREATE TABLE `url_alias` (
  `pid` int(10) unsigned NOT NULL auto_increment,
  `src` varchar(128) NOT NULL default '',
  `dst` varchar(128) NOT NULL default '',
  `language` varchar(12) NOT NULL default '',
  PRIMARY KEY  (`pid`),
  UNIQUE KEY `dst_language_pid` (`dst`,`language`,`pid`),
  KEY `src_language_pid` (`src`,`language`,`pid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
insert into url_alias (src, dst, language)
select concat('node/', post_id) as src, concat('fr/n', post_id, '/', post_titre_url) as dst, 'fr' as language
from dc_post
order by post_id;
 
--
-- récupération du contenu associé aux noeuds
--
drop table if exists `node_revisions`;
CREATE TABLE `node_revisions` (
  `nid` int(10) unsigned NOT NULL default '0',
  `vid` int(10) unsigned NOT NULL auto_increment,
  `uid` int(11) NOT NULL default '0',
  `title` varchar(255) NOT NULL default '',
  `body` longtext NOT NULL,
  `teaser` longtext NOT NULL,
  `log` longtext NOT NULL,
  `timestamp` int(11) NOT NULL default '0',
  `format` int(11) NOT NULL default '0',
  PRIMARY KEY  (`vid`),
  KEY `nid` (`nid`),
  KEY `uid` (`uid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
 
set @autoincrement := 0;
select @autoincrement := count(*) from dc_post;
SET @s = CONCAT("alter table node_revisions auto_increment=",@autoincrement); 
PREPARE stmt FROM @s; 
EXECUTE stmt; 
DEALLOCATE PREPARE stmt;
 
set @revision := 0;
insert into node_revisions
select post_id as nid, @revision:=@revision+1 as vid, 1 as uid, post_titre as title, post_content as body, post_chapo as teaser, '' as log, UNIX_TIMESTAMP(post_dt) as timestamp, 2 as format
from dc_post
order by post_id;
 
--
-- récupération des commentaires
--
drop table if exists comments;
CREATE TABLE `comments` (
  `cid` int(11) NOT NULL auto_increment,
  `pid` int(11) NOT NULL default '0',
  `nid` int(11) NOT NULL default '0',
  `uid` int(11) NOT NULL default '0',
  `subject` varchar(64) NOT NULL default '',
  `comment` longtext NOT NULL,
  `hostname` varchar(128) NOT NULL default '',
  `timestamp` int(11) NOT NULL default '0',
  `status` tinyint(3) unsigned NOT NULL default '0',
  `format` smallint(6) NOT NULL default '0',
  `thread` varchar(255) NOT NULL,
  `name` varchar(60) default NULL,
  `mail` varchar(64) default NULL,
  `homepage` varchar(255) default NULL,
  PRIMARY KEY  (`cid`),
  KEY `pid` (`pid`),
  KEY `nid` (`nid`),
  KEY `status` (`status`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
 
set @autoincrement := 0;
select @autoincrement := max(comment_id) from dc_comment;
SET @s = CONCAT("alter table comments auto_increment=",@autoincrement); 
PREPARE stmt FROM @s; 
EXECUTE stmt; 
DEALLOCATE PREPARE stmt;
 
insert into comments
select comment_id as cid, 0 as pid, post_id as nid, 0 as uid, '' as subject, comment_content as comment, comment_ip as hostname, UNIX_TIMESTAMP(comment_upddt) as timestamp, 0 as status, 1 as format, '01/' as thread, comment_auteur as name, comment_email as mail, comment_site as homepage
from dc_comment
order by comment_id;
 
--
-- récupération des catégories
--
drop table if exists term_data;
CREATE TABLE `term_data` (
  `tid` int(10) unsigned NOT NULL auto_increment,
  `vid` int(10) unsigned NOT NULL default '0',
  `name` varchar(255) NOT NULL default '',
  `description` longtext,
  `weight` tinyint(4) NOT NULL default '0',
  PRIMARY KEY  (`tid`),
  KEY `taxonomy_tree` (`vid`,`weight`,`name`),
  KEY `vid_name` (`vid`,`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
 
set @autoincrement := 0;
select @autoincrement := max(cat_id) from dc_categorie;
SET @s = CONCAT("alter table term_data auto_increment=",@autoincrement); 
PREPARE stmt FROM @s; 
EXECUTE stmt; 
DEALLOCATE PREPARE stmt;
 
insert into `term_data`
select cat_id as tid, 1 as vid, cat_libelle as name, '' as description, 0 as weight
from dc_categorie
order by cat_id;
 
drop table if exists term_hierarchy;
CREATE TABLE `term_hierarchy` (
  `tid` int(10) unsigned NOT NULL default '0',
  `parent` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`tid`,`parent`),
  KEY `parent` (`parent`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
 
insert into term_hierarchy
select tid, 0
from term_data;
 
--
-- liens catégories <-> noeuds
--
drop table if exists term_node;
CREATE TABLE `term_node` (
  `nid` int(10) unsigned NOT NULL default '0',
  `vid` int(10) unsigned NOT NULL default '0',
  `tid` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`tid`,`vid`),
  KEY `vid` (`vid`),
  KEY `nid` (`nid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
 
set @revision := 0;
insert into term_node
select post_id as nid, @revision:=@revision+1 as vid, cat_id as tid
from dc_post
order by post_id;

RewriteCond %{QUERY_STRING} ^[0-9]{4}/[0-9]{2}/[0-9]{2}/([0-9]+)-(.*).*$
RewriteRule .* /fr/n%1/%2? [L,R]

<?php
$vid = 1;
$items = array();
$terms = taxonomy_get_tree($vid);
foreach ( $terms as $term ) {  
    $count = db_result(db_query("SELECT COUNT(nid) FROM {term_node} WHERE tid = %d", $term->tid));  
    $items[] = l($term->name, "taxonomy/term/$term->tid") . " ($count)";
}
if ( count($items) ) {  print theme('item_list', $items);}
?>