====== scuttle2sitebar bookmark import ======

This is a simple script that will import bookmarks from a [[http://www.sitebar.org|Sitebar]] database into a [[http://sourceforge.net/projects/scuttle/|Scuttle]] database.

Features:

  * Honours private bookmarks
  * Uses folder names as tags

Current Limitations:

  * missing error handling - it's just a quick hack
  * compatibility with new database structure is an even bigger hack by someone clueless...
  * Only one user is converted at a time, identified by his email address
  * The user must exist in both databases and have the same email address
  * Scuttle doesn't use UTF-8 -> everything is converted to latin1
  * maybe others

<code php>
<?
// sitebar
$sb_name='sitebar';
$sb_host='localhost';
$sb_user='sitebar';
$sb_pass='sbpass';
$sb_pre='sitebar_';

// scuttle
$sc_name='scuttle';
$sc_host='localhost';
$sc_user='scuttle';
$sc_pass='scpass';

// email of user to convert
$email = 'user@example.com';

/* -------- no editing below needed -------------------------------------- */
 
if (!extension_loaded('mysql')){
    dl('mysql.so');
}
 
$sb = mysql_connect($sb_host, $sb_user, $sb_pass);
mysql_select_db($sb_name,$sb);
 
$sc = mysql_connect($sc_host, $sc_user, $sc_pass);
mysql_select_db($sc_name,$sc);
 
$sql = "SELECT nid
          FROM ${sb_pre}root A, ${sb_pre}user B
         WHERE A.uid = B.uid
           AND B.email = '".addslashes($email)."'";
$res = mysql_query($sql,$sb);
$row = mysql_fetch_array($res, MYSQL_ASSOC);
$nid = $row['nid'];

$sql = "SELECT uId FROM ${sc_pre}users
         WHERE email = '".addslashes($email)."'";
$res = mysql_query($sql,$sc);
$row = mysql_fetch_array($res, MYSQL_ASSOC);
$SC_UID = $row['uId'];
 
print "NID: $nid -> UID: $SC_UID\n";
 
list_links($nid,'',0);
 
function check_privacy($nid,$private){
  global $sb;
  global $sb_pre;
  global $sc_pre;
  $sql = "SELECT A.allow_select
            FROM ${sb_pre}acl A, ${sb_pre}group B
           WHERE B.name='Everyone'
             AND A.nid = $nid";

  $res = mysql_query($sql,$sb);
  if(mysql_affected_rows($sb)){
    $line = mysql_fetch_array($res, MYSQL_ASSOC);
    if($line['allow_select'] == 1){
      $private=0;
    }else{
      $private=1;
    }
  }
  mysql_free_result($res);
  return $private;
}
 
function list_links($nid,$names="",$private){
  global $sb;
  global $sc;
  global $sb_pre;
  global $sc_pre;
  global $SC_UID;
 
  // update privacy
  $private = check_privacy($nid,$private);
 
  // get all links
  $sql  = "SELECT url, name, private, comment, added, changed
             FROM ${sb_pre}link
            WHERE nid = $nid";

  $res = mysql_query($sql,$sb);
  while ($row = mysql_fetch_array($res, MYSQL_ASSOC)) {
 
    // determine link's privacy
    if($private || $row['private']){
      $stat = 2;
    }else{
      $stat = 0;
    }
    print "$stat ".$row['name']."\n";
 
    // build tags
    $names = preg_replace('/[^\w@\-_]+/',',',$names);
    $names = preg_replace('/,+/',',',$names);
    $tags  = explode(',',strtolower(trim($names,',')));
 
    $row = array_map('utf8_decode',$row);
 
    // insert into scuttle
    $sql = "INSERT IGNORE INTO ${sc_pre}bookmarks
               SET bAddress     = '".addslashes($row['url'])."',
                   bTitle       = '".addslashes($row['name'])."',
                   bStatus      = ".$stat.",
                   bDescription = '".addslashes($row['comment'])."',
                   bDatetime    = '".$row['added']."',
                   bModified    = '".$row['changed']."',
                   uId          = $SC_UID";

   mysql_query($sql,$sc);
 
    // add tags
    $ins = mysql_insert_id($sc);
    if($ins){
      foreach($tags as $tag){
        if ($tag != '') {
          $sql = "INSERT INTO ${sc_pre}tags
                     SET bId = $ins,
                         tag = '".addslashes($tag)."'";

         mysql_query($sql,$sc);
        }
      }
    }
  }
  mysql_free_result($res);
 
  // get all the subtrees
  $sql = "SELECT nid, name FROM ${sb_pre}node WHERE nid_parent = $nid";
  $res = mysql_query($sql,$sb);
  while ($line = mysql_fetch_array($res, MYSQL_ASSOC)) {
    $line['name'] = utf8_decode($line['name']);
    list_links($line['nid'], $names.','.$line['name'],$private);
  }
  mysql_free_result($res);
 
}
 
?>
</code>