Hello all. This is a set of aliases and database queries that emulate Achaea's nmark command. I found it very useful in Achaea, and I am posting this, hoping that someone else might find it useful. I use the xpertmud client on a linux box with perl scripting, so it is unlikely that you will use this verbatim. One note on this, is that I don't believe it could be ported to zMud, but even if it could, I wouldn't know how to do it. On Mushclient it might be easier. So let's start:
One word for the interface. We will use the nmark command as follows:
nmark add
nmark list
nmark remove|all
nmark read
I think that the syntax is straitforward. For instance if I wanted to mark the Public post #1 I would use:
1000h, 1000m, 1000e, 0p ex- nmark add public 1 Estarra's first post.
Using nmark list I would get then:
1000h, 1000m, 1000e, 0p ex-nmark list
****************************************************************
--------------------------------------------------------------------------------
1 public 1 Estarra's first post
********************************************************************************
1000h, 1000m, 1000e, 0p ex-
Lets start by creating the tables first. I use mysql, for the database.
CREATE DATABASE lusternia_data;
CREATE TABLE characters
(
  cid TINYINT UNSIGNED NOT NULL,
    name VARCHAR(20) NOT NULL,
    PRIMARY KEY(cid)
) ENGINE=INNODB;
CREATE TABLE newsposts Â
(
  cid TINYINT UNSIGNED NOT NULL,
    id INT UNSIGNED NOT NULL,
    board VARCHAR(50) NOT NULL,
    postnum INT UNSIGNED NOT NULL,
    description VARCHAR(50),
    PRIMARY KEY(cid, id),
    FOREIGN KEY(cid) REFERENCES characters(id) ON DELETE CASCADE
) ENGINE=INNODB;
We create two tables, one that holds the characters that we have and one for the newsposts. We want to keep seperate the marked posts of each alt, because we don't want to have them all show up when we call "nmark list".
No let's take a look at the nmark alias:
#Use database support.
use DBI;
delAlias(qr/News_nmark/o);
addAlias("News_nmark",qr/^\\s*nmark\\s+(add\\s+\\w+\\s+\\d+(?:\\s.*)?|remove\\s+(?:\\d+|all)|read\\s+\\d+|list)\\s*$/o,
  sub
  {
#Split arguments at whitespaces.
      my $args = $1;
      @arglst = split(/\\s+/,$args);
#connect to the db
      my $dsn = 'dbi:mysql:lusternia_data:localhost:3306';
      my $user = '';
      my $pass = '';
      my $dbh = DBI->connect($dsn, $user, $pass);
      if(not $dbh)
      {
        showError("cannot connect to db: $DBI::errstr\\n");
        return undef;
      }
#Check the args.
#We are adding a post.
      if($arglst eq "add")
      {
#Set the id of the next post to 0, if we don't have a setting that indicates it
        if(not defined $settings{'nmark add'})
        {
            $settings{'nmark add'} = 0;
        }
#the default value for a comment if the user doesn't provide one
        my $comment = '-';
#But if he provides it will have been splited. Rejoin it.
        if(defined $arglst)
        {
            $comment = "".join " ", @arglst;
        }
#Set the next post id to the previous +1.
        $settings{'nmark add'}++;
#Setup the insert query and try to execute it.
        my $query = "INSERT INTO newsposts VALUES ((SELECT id FROM characters WHERE name='$settings{'character name'}'), '$settings{'nmark add'}', '$arglst', '$arglst', '$comment')";
        my $action = $dbh->prepare($query);
        if(not $action->execute)
        {
            showError("$query failed: $DBI::errstr\\n");
            return undef;
        }
        showMessage("added bookmark $settings{'nmark add'} for $arglst post #$arglst");
      }
#We are removing one or all the posts.
      elsif($arglst eq "remove")
      {
        my $query = "";
#If the user requested all its markers to be removed setup the following query that removes all posts of this character.
        if($arglst eq "all")
        {
            $query = "DELETE FROM newsposts WHERE cid=(SELECT id FROM characters WHERE name='$settings{'character name'}')";
        }
#Otherwise delete only the post of this character that he requested.
        else
        {
            $query = "DELETE FROM newsposts WHERE cid=(SELECT id FROM characters WHERE name='$settings{'character name'}') AND id='$arglst'";
        }
        my $action = $dbh->prepare($query);
#Try to execute the query
        if(not $action->execute)
        {
            showError("$query failed: $DBI::errstr\\n");
            return undef;
        }
        showMessage("removed bookmark $arglst");
#If we have removed the last marker, decrease by one the next id, so as not to leave a hole at the end of the list.
        if($arglst == $settings{'nmark add'})
        {
            $settings{'nmark add'}--;
        }
      }
#We are reading a marked post.
      elsif($arglst eq "read")
      {
        showMessage("reading post number $arglst");
#We only need the board and the number of the post for reading it. Set up the query accordingly
        my $query = "SELECT board, postnum FROM newsposts WHERE cid=(SELECT id FROM characters WHERE name='$settings{'character name'}') AND id='$arglst'";
        my $action = $dbh->prepare($query);
        if(not $action->execute)
        {
            showError("$query failed: $DBI::errstr\\n");
            return undef;
        }
        my @row = $action->fetchrow_array;
#check that we got a meaningful result...
        if(not @row)
        {
            showMessage("no such mark");
        }
#...and if so, send to Lusternia the command "readnews "
        else
        {
            logPrintSend("readnews @row");
        }
      }
#We are listing the markers.
      elsif($arglst eq "list")
      {
        showMessage("listing posts in database");
#We must get the id the board, the number of the post and the description from the newsposts table for our character. Setup the query.
        my $query = "SELECT id, board, postnum, description FROM newsposts WHERE cid=(SELECT id FROM characters WHERE name='$settings{'character name'}') ORDER BY id";
        my $action = $dbh->prepare($query);
        if(not $action->execute)
        {
            showError("$query failed: $DBI::errstr\\n");
            return undef;
        }
#At this point the query has been executed successfully. Begin printing.
        $winmain->print(ansiStr("cyan","black","*"x32.""."*"x32));
        $winmain->print("\\n");
        $winmain->print("          \\n");
        $winmain->print(ansiStr("cyan","black","-"x80));
        $winmain->print("\\n");
        my @row;
#While we have more results, get the next and print it formated at the main window.
        while(@row = $action->fetchrow_array)
        {
            $winmain->print(sprintf("%-5d%-15s%-8d%-55s\\n",@row));
        }
        $winmain->print(ansiStr("cyan","black","*"x80));
        $winmain->print("\\n");
      }
     Â
      return undef;
  }
,1);
There are some minor issues remaining. For instance, the output of nmark list is not logged in a log file. Also one could think a more clever algorithm for finding the next id for the posts, that doesn't leave holes in the middle of the list. Finally, the above doesn't handle presenting long lists very well. You will have to scroll up.
I hope it will give you ideas or you will find it useful, and I didn't waste your time by posting this.
One word for the interface. We will use the nmark command as follows:
CODE
nmark add
nmark list
nmark remove
nmark read
I think that the syntax is straitforward. For instance if I wanted to mark the Public post #1 I would use:
CODE
1000h, 1000m, 1000e, 0p ex- nmark add public 1 Estarra's first post.
Using nmark list I would get then:
QUOTE
1000h, 1000m, 1000e, 0p ex-nmark list
****************************************************************
--------------------------------------------------------------------------------
1 public 1 Estarra's first post
********************************************************************************
1000h, 1000m, 1000e, 0p ex-
Lets start by creating the tables first. I use mysql, for the database.
CODE
CREATE DATABASE lusternia_data;
CREATE TABLE characters
(
  cid TINYINT UNSIGNED NOT NULL,
    name VARCHAR(20) NOT NULL,
    PRIMARY KEY(cid)
) ENGINE=INNODB;
CREATE TABLE newsposts Â
(
  cid TINYINT UNSIGNED NOT NULL,
    id INT UNSIGNED NOT NULL,
    board VARCHAR(50) NOT NULL,
    postnum INT UNSIGNED NOT NULL,
    description VARCHAR(50),
    PRIMARY KEY(cid, id),
    FOREIGN KEY(cid) REFERENCES characters(id) ON DELETE CASCADE
) ENGINE=INNODB;
We create two tables, one that holds the characters that we have and one for the newsposts. We want to keep seperate the marked posts of each alt, because we don't want to have them all show up when we call "nmark list".
No let's take a look at the nmark alias:
CODE
#Use database support.
use DBI;
delAlias(qr/News_nmark/o);
addAlias("News_nmark",qr/^\\s*nmark\\s+(add\\s+\\w+\\s+\\d+(?:\\s.*)?|remove\\s+(?:\\d+|all)|read\\s+\\d+|list)\\s*$/o,
  sub
  {
#Split arguments at whitespaces.
      my $args = $1;
      @arglst = split(/\\s+/,$args);
#connect to the db
      my $dsn = 'dbi:mysql:lusternia_data:localhost:3306';
      my $user = '
      my $pass = '
      my $dbh = DBI->connect($dsn, $user, $pass);
      if(not $dbh)
      {
        showError("cannot connect to db: $DBI::errstr\\n");
        return undef;
      }
#Check the args.
#We are adding a post.
      if($arglst eq "add")
      {
#Set the id of the next post to 0, if we don't have a setting that indicates it
        if(not defined $settings{'nmark add'})
        {
            $settings{'nmark add'} = 0;
        }
#the default value for a comment if the user doesn't provide one
        my $comment = '-';
#But if he provides it will have been splited. Rejoin it.
        if(defined $arglst)
        {
            $comment = "".join " ", @arglst;
        }
#Set the next post id to the previous +1.
        $settings{'nmark add'}++;
#Setup the insert query and try to execute it.
        my $query = "INSERT INTO newsposts VALUES ((SELECT id FROM characters WHERE name='$settings{'character name'}'), '$settings{'nmark add'}', '$arglst', '$arglst', '$comment')";
        my $action = $dbh->prepare($query);
        if(not $action->execute)
        {
            showError("$query failed: $DBI::errstr\\n");
            return undef;
        }
        showMessage("added bookmark $settings{'nmark add'} for $arglst post #$arglst");
      }
#We are removing one or all the posts.
      elsif($arglst eq "remove")
      {
        my $query = "";
#If the user requested all its markers to be removed setup the following query that removes all posts of this character.
        if($arglst eq "all")
        {
            $query = "DELETE FROM newsposts WHERE cid=(SELECT id FROM characters WHERE name='$settings{'character name'}')";
        }
#Otherwise delete only the post of this character that he requested.
        else
        {
            $query = "DELETE FROM newsposts WHERE cid=(SELECT id FROM characters WHERE name='$settings{'character name'}') AND id='$arglst'";
        }
        my $action = $dbh->prepare($query);
#Try to execute the query
        if(not $action->execute)
        {
            showError("$query failed: $DBI::errstr\\n");
            return undef;
        }
        showMessage("removed bookmark $arglst");
#If we have removed the last marker, decrease by one the next id, so as not to leave a hole at the end of the list.
        if($arglst == $settings{'nmark add'})
        {
            $settings{'nmark add'}--;
        }
      }
#We are reading a marked post.
      elsif($arglst eq "read")
      {
        showMessage("reading post number $arglst");
#We only need the board and the number of the post for reading it. Set up the query accordingly
        my $query = "SELECT board, postnum FROM newsposts WHERE cid=(SELECT id FROM characters WHERE name='$settings{'character name'}') AND id='$arglst'";
        my $action = $dbh->prepare($query);
        if(not $action->execute)
        {
            showError("$query failed: $DBI::errstr\\n");
            return undef;
        }
        my @row = $action->fetchrow_array;
#check that we got a meaningful result...
        if(not @row)
        {
            showMessage("no such mark");
        }
#...and if so, send to Lusternia the command "readnews
        else
        {
            logPrintSend("readnews @row");
        }
      }
#We are listing the markers.
      elsif($arglst eq "list")
      {
        showMessage("listing posts in database");
#We must get the id the board, the number of the post and the description from the newsposts table for our character. Setup the query.
        my $query = "SELECT id, board, postnum, description FROM newsposts WHERE cid=(SELECT id FROM characters WHERE name='$settings{'character name'}') ORDER BY id";
        my $action = $dbh->prepare($query);
        if(not $action->execute)
        {
            showError("$query failed: $DBI::errstr\\n");
            return undef;
        }
#At this point the query has been executed successfully. Begin printing.
        $winmain->print(ansiStr("cyan","black","*"x32.""."*"x32));
        $winmain->print("\\n");
        $winmain->print("          \\n");
        $winmain->print(ansiStr("cyan","black","-"x80));
        $winmain->print("\\n");
        my @row;
#While we have more results, get the next and print it formated at the main window.
        while(@row = $action->fetchrow_array)
        {
            $winmain->print(sprintf("%-5d%-15s%-8d%-55s\\n",@row));
        }
        $winmain->print(ansiStr("cyan","black","*"x80));
        $winmain->print("\\n");
      }
     Â
      return undef;
  }
,1);
There are some minor issues remaining. For instance, the output of nmark list is not logged in a log file. Also one could think a more clever algorithm for finding the next id for the posts, that doesn't leave holes in the middle of the list. Finally, the above doesn't handle presenting long lists very well. You will have to scroll up.
I hope it will give you ideas or you will find it useful, and I didn't waste your time by posting this.
Drathys. Much love. I have no clue how to manipulate database variables/formatting like that. I'll have to try and work it out from that, now. 
