-- $Id: freetag.sql,v 1.2 2006/05/29 17:11:44 brian Exp brian $
--
-- MySQL dump 9.11
-- Freetag Structure v2.02
--
-- Table structure for table `freetags`
--

DROP TABLE IF EXISTS freetags;
CREATE TABLE freetags (
  id int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  tag varchar(30) NOT NULL DEFAULT '',
  raw_tag varchar(50) NOT NULL DEFAULT '',
  PRIMARY KEY  (id)
) TYPE=MyISAM;

--
-- Table structure for table `freetagged_objects`
--

DROP TABLE IF EXISTS freetagged_objects;
CREATE TABLE freetagged_objects (
  tag_id int(10) UNSIGNED NOT NULL DEFAULT '0',
  tagger_id int(10) UNSIGNED NOT NULL DEFAULT '0',
  object_id int(10) UNSIGNED NOT NULL DEFAULT '0',
  tagged_on datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY  (`tag_id`,`tagger_id`,`object_id`),
  KEY `tag_id_index` (`tag_id`),
  KEY `tagger_id_index` (`tagger_id`),
  KEY `object_id_index` (`object_id`)
) TYPE=MyISAM;

--
-- Table structure for table `freetag_bookmarks`
--

DROP TABLE IF EXISTS freetag_bookmarks;
CREATE TABLE freetag_bookmarks (
    bookmark_id int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    title varchar(255) NOT NULL DEFAULT '',
    uri varchar(255) NOT NULL,
    description varchar(255) DEFAULT NULL,
    wikka_id varchar(75) NOT NULL,
    private tinyint(1) NOT NULL DEFAULT '0',
    PRIMARY KEY (`bookmark_id`),
    KEY `bookmark_id_index` (`bookmark_id`)
) TYPE=MyISAM;

--
-- Table structure for table `freetag_wikka_id_map`
--

DROP TABLE IF EXISTS freetag_wikka_id_map;
CREATE TABLE freetag_wikka_id_map (
    tagger_id int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    wikka_id varchar(75) NOT NULL DEFAULT '',
    PRIMARY KEY(`tagger_id`)
) TYPE=MyISAM;

<?php
    /**
    * Bookmark manager with tagging
    *
    * Allows users (registered or not) to tag objects such as
    * bookmarks and display linked tags in the spirit of
    * {@link http://de.licio.us} and {@link http://de.lirio.us}.
    *
    * Usage: {{bookmarks}}
    *
    * Credits:
    * Gordon Luk's Freetag for PHP4 {@link http://www.getluky.net/freetag}
    *
    * This program is free software; you can redistribute it and/or
    * modify it under the terms of the GNU General Public License as
    * published by the Free Software Foundation; either version 2 of
    * the License, or (at your option) any later version.
    *
    * This program is distributed in the hope that it will be useful,
    * but WITHOUT ANY WARRANTY; without even the implied warranty of
    * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
    * GNU General Public Licence for more details:
    *
    *            http://www.gnu.org/copyleft/gpl.html
    *
    *
    * @author        {@link http://wikkawiki.org/BrianKoontz Brian Koontz} <brian@pongonova.net>
    * @copyright    Copyright (c) 2006, Brian Koontz <brian@pongonova.net>
    * @name        BookmarkManager
    * @package        Actions
    * @filesource
    * @license        http://www.gnu.org/copyleft/gpl.html
    * @since        Wikka 1.1.6.1
    * @version        $Id: bookmarks.php,v 1.8 2006/08/10 04:03:38 brian Exp brian $
    *
    */

    // Check for freetag class and helper libraries
    include_once('libs/Wikka.freetag.class.php');
    include_once('libs/Wikka.freetag.lib.php');

    /********************************************************************/
    /* DB connection params                                                */
    /********************************************************************/
    $freetag_options = array(
        'db_user' => $this->config['mysql_user'],
        'db_pass' => $this->config['mysql_password'],
        'db_host' => $this->config['mysql_host'],
        'db_name' => 'freetag',
        'PCONNECT' => true,
    );
    $freetag = new wikka_freetag($freetag_options);

    $wikka_id = $this->GetUserName();
    $tagger_id = wikka_id_to_tagger_id($wikka_id, $this, $freetag_options);
    $all_obj_count = count_objects(NULL, $this, $freetag_options);
    $your_obj_count = count_objects($wikka_id, $this, $freetag_options);
    $search_type = "";
    if(isset($_REQUEST['action'])) {
        if($_REQUEST['action']=="list_yours") {
            $search_type = "search_yours";
        } else {
            $search_type = "search_all";
        }
    }

    echo "<a href=\"".$this->href()."&action=list_all"."\">List all</a> (".$all_obj_count.") | ".
        "<a href=\"".$this->href()."&action=list_yours"."\">List yours</a> (".$your_obj_count.") | ".
        "<a href=\"".$this->href()."&action=add"."\">Add</a>".
        $this->FormOpen().
        "<input type=\"hidden\" name=\"action\" value=\"".$search_type."\" />".
        "Search: ".
            "<input name=\"search\" size=\"20\" />".
            "<input type=\"radio\" name=\"search_type\" value=\"tags\" checked>Tags".
            "<input type=\"radio\" name=\"search_type\" value=\"title_desc\">Title/Desc".
        $this->FormClose();

    if(!isset($_REQUEST['action'])) {
        // Display all tags at start of session
        $this->Redirect($this->href()."&action=list_all");
    }

    // Search by tags, titles, or descriptions
    // Action name: search
    // Search field name: search
    // Radio buttons: search_type: "tags" or "title_desc"
    if(isset($_REQUEST['action']) && ($_REQUEST['action']=="search_yours") || ($_REQUEST['action']=="search_all")) {
        if(isset($_REQUEST['search_type']) &&
          ($_REQUEST['search_type']=="tags") &&
          isset($_REQUEST['search'])) {
              if($_REQUEST['action'] == "search_yours") {
                  $this->Redirect($this->href()."&search_term=".$_REQUEST['search']."&action=list_yours");
              } else {
                  $this->Redirect($this->href()."&search_term=".$_REQUEST['search']."&action=list_all");
              }
        }
    }

    // Add new entry to DB
    if(isset($_REQUEST['action']) && ($_REQUEST['action']=="add")) {
        $values = array();
        // Check for request from a bookmarklet...check to see if user
        // is logged in...if not, redirect to login page and come back
        // here when done
        if(isset($_REQUEST['wikka_bookmarklet'])) {
            $values['uri'] = mysql_real_escape_string($_REQUEST['uri']);
            if(isset($_REQUEST['title'])) {
                $values['title'] = mysql_real_escape_string($_REQUEST['title']);
            }
            $values['referrer'] = $_SERVER['HTTP_REFERER'];
            $values['when_done'] = "add_link";
            save_to_current_session($values);
            //session_write_close();
            if(!$this->GetUser()) {
                $_SESSION['go_back'] = $this->Href(null, $this->MiniHref()."&action=add");
                //session_write_close();
                $this->Redirect($this->config['base_url']."UserSettings");
            }
        }
        // The next clause checks to see if we're returning from a
        // login request while trying to execute a bookmarklet
        if(isset($_SESSION['when_done']) && ($_SESSION['when_done']=="add_link")) {
            $values['uri'] = $_SESSION['uri'];
            $values['title'] = $_SESSION['title'];
            $_SESSION['when_done'] = "return";
            //session_write_close();
        }

        if(trim($_REQUEST['tags']) != "") {
            // Private bookmark?
            $private = 0;
            if(strpos(trim($_REQUEST['tags']), "@private") !== false)
                $private = 1;
           
            // Converts numeric tag "123" to "123_" to facilitate
            // alphanumeric sorting (otherwise, PHP converts string to
            // true integer).
            $tags = preg_split('/\s+/', $_REQUEST['tags'], -1, PREG_SPLIT_NO_EMPTY);
            $tags = preg_replace('/^([0-9]+)$/', "$1_", $tags);
            $tags = implode(" ", $tags);

            $this->Query("insert ".$freetag_options['db_name'].".freetag_bookmarks set ".
                "title = '".mysql_real_escape_string($_REQUEST['title'])."', ".
                "uri = '".mysql_real_escape_string($this->cleanUrl($_REQUEST['uri']))."', ".
                "wikka_id = '".mysql_real_escape_string($wikka_id)."', ".
                "private = '".$private."', ".
                "description = '".mysql_real_escape_string($_REQUEST['desc'])."';");
            $last_id = $this->LoadSingle("select last_insert_id();");
            $freetag->tag_object($tagger_id, $last_id['last_insert_id()'], $tags);
            // Check to see if it's time to return to the page from which
            // the bookmarklet was called
            if(isset($_SESSION['when_done']) && ($_SESSION['when_done']=="return")) {
                if(isset($_SESSION['referrer'])) {
                    $referrer = $_SESSION['referrer'];
                    unset($_SESSION['go_back']);
                    unset($_SESSION['uri']);
                    unset($_SESSION['title']);
                    unset($_SESSION['referrer']);
                    unset($_SESSION['when_done']);
                    $this->Redirect($referrer);
                }
            }
            // Otherwise, go back to tag/link list view
            $this->Redirect($this->href()."&action=list_yours");
        } else {
            // Display add form
            print($this->FormOpen());
            ?>
            <input type="hidden" name="action" value="add" />
            <table>
                <tr>
                    <td align="right">Title:</td>
                    <td><input name="title" size="40" value="<?php echo $values['title']; ?>" /></td>
                </tr>
                <tr>
                    <td align="right">URI:</td> <td><input name="uri" size="40" value="<?php echo $values['uri']; ?>" /></td> </tr>
                <tr>
                    <td align="right">Description:</td>
                    <td><input name="desc" size="40"/></td>
                </tr>
                <tr>
                    <td align="right"></td>
                    <td><?php echo $this->Format("Use blank space between tags, include \"@private\" for private bookmark"); ?></td>
                </tr>
                <tr>
                    <td align="right">Tags:</td>
                    <td><input name="tags" size="40"/></td>
                </tr>
                <tr>
                    <td></td>
                    <td><input type="submit" value="Add" size="40" /></td>
                </tr>
            </table>
        <?php
            print($this->FormClose());
        }
    }

    // Edit entry
    if(isset($_REQUEST['action']) && ($_REQUEST['action']=="edit") && (isset($_REQUEST['object_id']))) {
        $object_id = mysql_real_escape_string($_REQUEST['object_id']);
        $obj = $this->LoadSingle("select bookmark_id,title,uri,description,wikka_id from ".$freetag_options['db_name'].".freetag_bookmarks where bookmark_id = ".$object_id.";");
        // Is the logged-in user the owner?
        if($wikka_id != $obj['wikka_id']) {
            $this->Redirect($this->href()."&action=list_all");
        }
        if(isset($_REQUEST['modify']) && trim($_REQUEST['tags']) != "") {
            // Delete all tags, then re-tag
            $freetag->delete_all_object_tags_for_user($tagger_id,$object_id);
            // Private bookmark?
            $private = 0;
            if(strpos(trim($_REQUEST['tags']), "@private") !== false)
                $private = 1;
            $this->Query("update ".$freetag_options['db_name'].".freetag_bookmarks set ".
                "title = '".mysql_real_escape_string($_REQUEST['title'])."', ".
                "uri = '".mysql_real_escape_string($this->cleanUrl($_REQUEST['uri']))."', ".
                "wikka_id = '".mysql_real_escape_string($wikka_id)."', ".
                "private = '".$private."', ".
                "description = '".mysql_real_escape_string($_REQUEST['desc'])."' ".
                "where bookmark_id = ".mysql_real_escape_string($object_id)." ".
                "limit 1;");
            $freetag->tag_object($tagger_id, $obj['bookmark_id'], $_REQUEST['tags']);
            $this->Redirect($this->href()."&action=list_yours");
        }

        // Display add form
        print($this->FormOpen());
        ?>
        <input type="hidden" name="action" value="edit" />
        <input type="hidden" name="modify" value="yes" />
        <input type="hidden" name="object_id" value="<?php print $object_id ?>" />
        <table>
            <tr>
                <td align="right">Title:</td>
                <td><input name="title" size="40" value="<?php print $obj['title']; ?>"/></td>
            </tr>
            <tr>
                <td align="right">URI:</td>
                <td><input name="uri" size="40" value="<?php print $obj['uri']; ?>"/></td>
            </tr>
            <tr>
                <td align="right">Description:</td>
                <td><input name="desc" size="40" value="<?php print $obj['description']; ?>"/></td>
            </tr>
            <tr>
                <td align="right"></td>
                <td><?php echo $this->Format("Use blank space between tags, include \"@private\" for private bookmark"); ?></td>
            </tr>
            <tr>
                <td align="right">Tags:</td>
                <?php
                    $tags = $freetag->get_tags_on_object($object_id);
                    $taglist = "";
                    foreach($tags as $idx=>$res) {
                        $taglist .= trim($res['raw_tag'])." ";
                    }
                ?>
                <td><input name="tags" size="40" value="<?php print $taglist?>"/></td>
            </tr>
            <tr>
                <td></td>
                <td><input type="submit" value="Edit" size="40" /></td>
            </tr>
        </table>
        <?php
        print($this->FormClose());
    }

    // Delete entry
    if(isset($_REQUEST['action']) && $_REQUEST['action']=="delete") {
        $object_id = mysql_real_escape_string($_REQUEST['object_id']);
        $obj = $this->LoadSingle("select bookmark_id,wikka_id from ".$freetag_options['db_name'].".freetag_bookmarks where bookmark_id = ".mysql_real_escape_string($object_id).";");
        // Is the logged-in user the owner?
        if($wikka_id != $obj['wikka_id']) {
            $this->Redirect($this->href()."&action=list_all");
        }
        // Delete all tags first...
        $freetag->delete_all_object_tags_for_user($tagger_id,$object_id);

        // ...then delete object
        $this->Query("delete from ".$freetag_options['db_name'].".freetag_bookmarks where bookmark_id = ".mysql_real_escape_string($object_id)." limit 1;");
        $this->Redirect($this->href()."&action=list_yours");
    }

    // List all bookmarks (ordered by most recent)
    if(isset($_REQUEST['action']) && ($_REQUEST['action']=="list_all")) {
        $offset = 0;
        if(isset($_REQUEST['offset'])) {
            $offset = mysql_real_escape_string($_REQUEST['offset']);
        }
        $limit = 25;
        if(isset($_REQUEST['limit'])) {
            $limit = mysql_real_escape_string($_REQUEST['limit']);
        }
        $tag_offset = 0;
        if(isset($_REQUEST['tag_offset'])) {
            $tag_offset = mysql_real_escape_string($_REQUEST['tag_offset']);
        }
        $tag_limit = 100;
        if(isset($_REQUEST['tag_limit'])) {
            $tag_limit = mysql_real_escape_string($_REQUEST['tag_limit']);
        }

        // Output tag cloud
        $url_opts = $this->href()."&action=list_all";
        $search_term = NULL;
        $header = "All tags";
        if(isset($_REQUEST['search_term'])) {
            $search_term = mysql_real_escape_string($_REQUEST['search_term']);
            $header = "All tags by search";
        }
        output_tag_cloud($freetag,$url_opts,NULL,$header,$tag_offset,$tag_limit,$search_term);

        $ids = "";
        $tag = "";
        if(isset($_REQUEST['tag']) && $_REQUEST['tag'] != "") {
            $ids = $freetag->get_most_recent_objects(NULL,$_REQUEST['tag'],$offset,$limit);
            $tag = mysql_real_escape_string($_REQUEST['tag']);
        } else {
            $ids = $freetag->get_most_recent_objects(NULL,NULL,$offset,$limit);
        }

        foreach($ids as $key=>$val) {
            $tags = $freetag->get_tags_on_object($val['object_id']);
            $obj = $this->LoadSingle("select title,uri,description,wikka_id from ".$freetag_options['db_name'].".freetag_bookmarks where bookmark_id = ".$val['object_id']." and private = 0;");
            if(!isset($obj)) {
                continue;
            }
            print "<div class=\"bookmark_block\">\n";
            print "<div class=\"bookmark_title\"><a href=\"".$obj['uri']."\">".$obj['title']."</a></div>\n";
            print "<div class=\"bookmark_box\"><div class=\"bookmark_desc\">".($obj['description'] ?  $obj['description'] : " ")."</div>\n";
            $taglist = "";
            $link = $url_opts."&tag_offset=".$tag_offset."&tag_limit=".$tag_limit;
            foreach($tags as $idx=>$res) {
                $temp_tag = trim($res['tag']);
                $taglist .= "<a href=\"".$link."&tag=".$temp_tag."\">".$temp_tag."</a> ";
            }
            print $taglist."by ".$obj['wikka_id']." (created: ".$val['tagged_on'].")\n";
            //print "</div><div class=\"clear\"> </div><hr/>";
            print "</div></div>\n";
        }

        // Display pagination links
        $obj_count = 0;
        $tag_url = "";
        if($tag != "") {
            $obj_count = count_tagged_objects(NULL, $tag, $this, $freetag_options, $freetag);
            $tag_url = "&tag=".$tag;
        } else {
            $obj_count = count_objects(NULL, $this, $freetag_options);
        }
        $prev_offset = $offset - $limit;
        $prev_url = "";
        if($prev_offset < 0) {
            $prev_offset = 0;
        }
        if($offset > 0) {
            $prev_url = "<a href=\"".$this->href()."&action=list_all&offset=".$prev_offset."&limit=".$limit.$tag_url."\"><<</a>";
        }
        $next_offset = $offset + $limit;
        $next_url = "";
        if($next_offset < $obj_count - 1) {
            $next_url = "<a href=\"".$this->href()."&action=list_all&offset=".$next_offset."&limit=".$limit.$tag_url."\">>></a>";
        }
        print "<div class=\"pagination\">";
        print $prev_url."  ".$next_url;
        print "</div>";
    }

    // List user bookmarks (ordered by most recent)
    if(isset($_REQUEST['action']) && ($_REQUEST['action']=="list_yours")) {
        $offset = 0;
        if(isset($_REQUEST['offset'])) {
            $offset = mysql_real_escape_string($_REQUEST['offset']);
        }
        $limit = 25;
        if(isset($_REQUEST['limit'])) {
            $limit = mysql_real_escape_string($_REQUEST['limit']);
        }
        $tag_offset = 0;
        if(isset($_REQUEST['tag_offset'])) {
            $tag_offset = mysql_real_escape_string($_REQUEST['tag_offset']);
        }
        $tag_limit = 100;
        if(isset($_REQUEST['tag_limit'])) {
            $tag_limit = mysql_real_escape_string($_REQUEST['tag_limit']);
        }

        // Output tag cloud
        $url_opts = $this->href()."&action=list_yours";
        $search_term = NULL;
        $header = "Your tags";
        if(isset($_REQUEST['search_term'])) {
            $search_term = mysql_real_escape_string($_REQUEST['search_term']);
            $header = "Your tags by search";
        }

        output_tag_cloud($freetag,$url_opts,$tagger_id,$header,$tag_offset,$tag_limit,$search_term);

        $ids = "";
        $tag = "";
        if(isset($_REQUEST['tag']) && $_REQUEST['tag'] != "") {
            $ids = $freetag->get_most_recent_objects($tagger_id, $_REQUEST['tag'], $offset, $limit);
            $tag = mysql_real_escape_string($_REQUEST['tag']);
        } else {
            $ids = $freetag->get_most_recent_objects($tagger_id, NULL, $offset, $limit);
        }

        foreach($ids as $key=>$val) {
            $tags = $freetag->get_tags_on_object($val['object_id']);
            $obj = $this->LoadSingle("select title,uri,description,wikka_id from ".$freetag_options['db_name'].".freetag_bookmarks where bookmark_id = ".$val['object_id'].";");
            print "<div class=\"bookmark_block\">\n";
            print "<div class=\"bookmark_title\"><a href=\"".$obj['uri']."\">".$obj['title']."</a></div>\n";
            print "<div class=\"bookmark_box\"><div class=\"bookmark_desc\">".($obj['description'] ?  $obj['description'] : " ")."</div>\n";
            print " <a href=\"".$this->href()."&action=edit&object_id=".$val['object_id']."\">edit</a>";
            print "|";
            print "<a href=\"".$this->href()."&action=delete&object_id=".$val['object_id']."\">delete</a><br/>";
            $taglist = "";
            $link = $url_opts."&tag_offset=".$tag_offset."&tag_limit=".$tag_limit;
            foreach($tags as $idx=>$res) {
                $normtag = trim($res['tag']);
                $rawtag = trim($res['raw_tag']);
                $taglist .= "<a href=\"".$link."&tag=".$normtag."\">".$rawtag."</a> ";
            }
            print $taglist."by ".$obj['wikka_id']." (created: ".$val['tagged_on'].")\n";
            //print "<div class=\"clear\"> </div><hr/>";
            print "</div></div>\n";
        }

        // Display pagination links
        $obj_count = 0;
        $tag_url = "";
        if($tag != "") {
            $obj_count = count_tagged_objects($wikka_id, $tag, $this, $freetag_options, $freetag);
            $tag_url = "&tag=".$tag;
        } else {
            $obj_count = count_objects($wikka_id, $this, $freetag_options);
        }
        $prev_offset = $offset - $limit;
        $prev_url = "";
        if($prev_offset < 0) {
            $prev_offset = 0;
        }
        if($offset > 0) {
            $prev_url = "<a href=\"".$this->href()."&action=list_yours&offset=".$prev_offset."&limit=".$limit.$tag_url."\"><<</a>";
        }
        $next_offset = $offset + $limit;
        $next_url = "";
        if($next_offset < $obj_count - 1) {
            $next_url = "<a href=\"".$this->href()."&action=list_yours&offset=".$next_offset."&limit=".$limit.$tag_url."\">>></a>";
        }
        print "<div class=\"center\">";
        print $prev_url."  ".$next_url;
        print "</div>";
    }
?>