PlanetMysql.ru — информация о СУБД MySQL

Background Knowledge

The progress of a task in TaskFreak! is shown as a percentage value and is not exactly visually appealing to quickly spot the progress. With a few minor alterations we can show the percentage completed bar that fills as the task progresses and a gradient bar indicating the progress along with the percentage value.

This solution was posted by Searcher at Re: Taskfreak Customizing Status.

Solution

1. Edit at line #268 as shown below.
   

   Cod Before

   268	<th width="<?php echo FRK_STATUS_LEVELS * 2; ?>%" onclick="freak_sort('statusKey')" colspan="< ?php echo FRK_STATUS_LEVELS ?>" class="sortable">< ?php echo (FRK_STATUS_LEVELS == 1)?'X':$langForm['status']; ?></th>

   Code After

   268	<th width="80" onclick="freak_sort('statusKey')" class="sortable">< ?php echo (FRK_STATUS_LEVELS == 1)?'X':$langForm['status']; ?></th>

2. Edit at line #382
   

   Code Before

   < ?php
                       $s = $objItem->itemStatus->statusKey;
                       for ($i = 0; $i < FRK_STATUS_LEVELS; $i++) {
                           $j = ($i < $s)?(FRK_STATUS_LEVELS - $i):0;
                   ?>
                           <td id="est<?php echo ($i+1).$objItem->id; ?>" class="sts< ?php echo $j; ?>"< ?php
                           if ($objUser->checkLevel(14) || $objItem->checkRights($objUser->id,8,true))  {
                               echo ' onclick="freak_sts('.$objItem->id.','.($i+1).')" style="cursor:pointer"';
                           }
                       ?>>&nbsp;</td>
                   < ?php
                       }
                   ?>

   Code After

   <!-- status bar update -->
             <td>
               <table width="100%" cellpadding="0" cellspacing="0">
                 <tr>
                   < ?php
                   $s = $objItem->itemStatus->statusKey;
                   for ($i = 0; $i < FRK_STATUS_LEVELS; $i++) {
                     $j = ($i < $s)?(FRK_STATUS_LEVELS - $i):0;
                     ?>
                     <td width="10" onmouseover="this.style.cursor='pointer'" id="est<?php echo ($i+1).$objItem->id; ?>" class="sts< ?php echo $j; ?>"< ?php if ($objUser->checkLevel(14) || $objItem->checkRights($objUser->id,8,true))  { echo ' onclick="freak_sts('.$objItem->id.','.($i+1).')" style="cursor:pointer"'; } ?>>&nbsp;</td>
                     < ?php } ?>
                     <td style="border:0"></td>
                     <td id="status_perc_<?php echo $objItem->id; ?>" style="border:0; font-size:8px; color:#000" align="right">&nbsp;< ?php echo ($s*20)."%"; ?></td>
                   </tr>
                   <tr><td style="border:0; height:2px"></td></tr>
                   <tr>
                     <td colspan="7" style="border:0"><img id="status_bar_<?php echo $objItem-/>id; ?>" src="skins/status.jpg" height="5" width="< ?php echo ($s*16); ?>" /></td>
                 </tr>
               </table>
             </td>
   <!-- status bar update -->

3. Download the status gradient bar image file from “http://demofreak.dracon.biz/skins/status.jpg” and the copy image file to the /taskfreak/skins/ directory.

Source: Taskfreak Customizing Status

Background Knowledge

The progress of a task in TaskFreak! is shown as a percentage value and is not exactly visually appealing to quickly spot the progress. With a few minor alterations we can show the percentage completed bar that fills as the task progresses and a gradient bar indicating the progress along with the percentage value.

This solution was posted by Searcher at Re: Taskfreak Customizing Status.

Solution

1. Edit at line #268 as shown below.
   

   Cod Before

   268	<th width="<?php echo FRK_STATUS_LEVELS * 2; ?>%" onclick="freak_sort('statusKey')" colspan="< ?php echo FRK_STATUS_LEVELS ?>" class="sortable">< ?php echo (FRK_STATUS_LEVELS == 1)?'X':$langForm['status']; ?></th>

   Code After

   268	<th width="80" onclick="freak_sort('statusKey')" class="sortable">< ?php echo (FRK_STATUS_LEVELS == 1)?'X':$langForm['status']; ?></th>

2. Edit at line #382
   

   Code Before

   < ?php
                       $s = $objItem->itemStatus->statusKey;
                       for ($i = 0; $i < FRK_STATUS_LEVELS; $i++) {
                           $j = ($i < $s)?(FRK_STATUS_LEVELS - $i):0;
                   ?>
                           <td id="est<?php echo ($i+1).$objItem->id; ?>" class="sts< ?php echo $j; ?>"< ?php
                           if ($objUser->checkLevel(14) || $objItem->checkRights($objUser->id,8,true))  {
                               echo ' onclick="freak_sts('.$objItem->id.','.($i+1).')" style="cursor:pointer"';
                           }
                       ?>>&nbsp;</td>
                   < ?php
                       }
                   ?>

   Code After

   <!-- status bar update -->
             <td>
               <table width="100%" cellpadding="0" cellspacing="0">
                 <tr>
                   < ?php
                   $s = $objItem->itemStatus->statusKey;
                   for ($i = 0; $i < FRK_STATUS_LEVELS; $i++) {
                     $j = ($i < $s)?(FRK_STATUS_LEVELS - $i):0;
                     ?>
                     <td width="10" onmouseover="this.style.cursor='pointer'" id="est<?php echo ($i+1).$objItem->id; ?>" class="sts< ?php echo $j; ?>"< ?php if ($objUser->checkLevel(14) || $objItem->checkRights($objUser->id,8,true))  { echo ' onclick="freak_sts('.$objItem->id.','.($i+1).')" style="cursor:pointer"'; } ?>>&nbsp;</td>
                     < ?php } ?>
                     <td style="border:0"></td>
                     <td id="status_perc_<?php echo $objItem->id; ?>" style="border:0; font-size:8px; color:#000" align="right">&nbsp;< ?php echo ($s*20)."%"; ?></td>
                   </tr>
                   <tr><td style="border:0; height:2px"></td></tr>
                   <tr>
                     <td colspan="7" style="border:0"><img id="status_bar_<?php echo $objItem-/>id; ?>" src="skins/status.jpg" height="5" width="< ?php echo ($s*16); ?>" /></td>
                 </tr>
               </table>
             </td>
   <!-- status bar update -->

3. Download the status gradient bar image file from “http://demofreak.dracon.biz/skins/status.jpg” and the copy image file to the /taskfreak/skins/ directory.

Source: Taskfreak Customizing Status

This post explains how to debug MySQL Cluster 7.1, installed using the RPM packages, using gdb on a Linux box (Red Hat, Oracle Enterprise Linux, CentOS, ..).

When a data node crashes lots of information goes into the error log, trace files and out log. However, it makes sometimes sense when you can repeat the crash, to run the data node in debug mode, or using gdb.

First, using RPMs and a Linux distribution, make sure you have the ‘debuginfo’ package installed. For example, for Red Hat or Oracle Enterprise Linux on a 64-bit machine, this package would be called: MySQL-Cluster-gpl-debuginfo-7.1.15-1.rhel5.x86_64.rpm .

Create a file with the following commands, we will name it ‘ndbd.gdb’:

set pagination off
set logging overwrite on
set logging file ndbd_gdb_backtrace.txt
set logging on
run --foreground -c <YourMGM:1186> --ndb-nodeid=<YourID>
thread apply all bt
set logging off

Note line 5: pass the options to ‘run’ which you usually pass when starting ndbd, but leave the –foreground option.
Note line 3: you can save of course the logging file wherever you want to.

Then all you need to do is run gdb with the commands file and the ndbd binary you just created:

shell> gdb /usr/sbin/ndbd -x ndbd.gdb

A full backtrace of threads will be available in the logging file when ndbd crashes. This you can then upload when reporting bugs.

There are probably more options and things you can do, please post them in the comments!

This is a reply to the blog post Poor man’s MySQL replication monitoring. Haidong Ji had a few problems using MySQLdb (could use the ‘dict’ cursor) and apparently he doesn’t want to much dependencies. I agree that using the mysql client tool is a nice alternative if you don’t want to use any 3rd party Python modules. And the MySQL client tools are usually and should be installed with the server.

However, since MySQL Connector/Python only needs itself and Python, dependencies are reduced to a minimum. Here you’ll find a refactored version of Haidong’s version (can of course be made much more sophisticated) using the connector:

import sys
from socket import gethostname
import smtplib
import mysql.connector

emailSubject = "Replication problem on slave %s"
emailTo = "recipient@example.com"
emailFrom = "monitor-tool@example.com"

def runCmd(cmd):
    cnx = mysql.connector.connect(user='root',
                                  unix_socket='/path/to/mysql.sock')
    cur = cnx.cursor(buffered=True)
    cur.execute(cmd)
    columns = tuple( [d[0].decode('utf8') for d in cur.description] )
    row = cur.fetchone()
    if row is None:
        raise StandardError("MySQL Server not configured as Slave")
    result = dict(zip(columns, row))
    cur.close()
    cnx.close()
    return result

try:
    slave_status = runCmd("SHOW SLAVE STATUS")
except mysql.connector.Error, e:
    print >> sys.stderr, "There was a MySQL error:", e
    sys.exit(1)
except StandardError, e:
    print >> sys.stderr, "There was an error:", e
    sys.exit(1)

if (slave_status['Slave_IO_Running'] == 'Yes' and
    slave_status['Slave_SQL_Running'] == 'Yes' and
    slave_status['Last_Errno'] == 0):
    print "Cool"
else:
    emailBody = [
        "From: %s" % emailFrom,
        "To: %s" % emailTo,
        "Subject: %s" % (emailSubject %  gethostname()),
        "",
        '\n'.join([ k + ' : ' + str(v) for k,v in slave_status.iteritems()]),
        "\r\n",
        ]
    server = smtplib.SMTP("localhost")
    server.sendmail(emailFrom, [emailTo], '\r\n'.join(emailBody))
    server.quit()

Setting client flags with MySQL Connector/Python works a bit differently than the other MySQL Python drivers. This blog post describes how to set and unset flags, like the CLIENT_FOUND_ROWS.

The default client flags for the MySQL Client/Server protocol can be retrieved using the constants.ClientFlag class:

>>> from mysql.connector.constants import ClientFlag
>>> defaults = ClientFlag.get_default()
>>> print ClientFlag.get_bit_info(defaults)
['SECURE_CONNECTION', 'TRANSACTIONS', 'CONNECT_WITH_DB',
 'PROTOCOL_41', 'LONG_FLAG', 'MULTI_RESULTS',
 'MULTI_STATEMENTS', 'LONG_PASSWD']

To set an extra flag when connecting to MySQL you use the client_flags argument of connect()-method. For example, you’d like to have the CLIENT_FOUND_ROWS set:

import mysql.connector
from mysql.connector.constants import ClientFlag
extra_flags = [ClientFlag.FOUND_ROWS]
cnx = mysql.connector.connect(client_flags=extra_flags)

Similar, you can unset a flag passing a list of negative values, or all at the same time. For example, you’d like the CLIENT_FOUND_ROWS set, but you don’t want CLIENT_MULTI_STATEMENTS:

import mysql.connector
from mysql.connector.constants import ClientFlag
extra_flags = [ClientFlag.FOUND_ROWS, -ClientFlag.MULTI_STATEMENTS]
cnx = mysql.connector.connect(client_flags=extra_flags)

It is also possible to pass the client_flags an integer, but you need to get first the defaults, and do bitwise operations to set/unset flags yourself. At the moment of writing, there is a bug about this, see lp:695514, but I recommend using the list-method.

This blog post shows how to create a cursor class for MySQL Connector/Python which will allow you to cache queries. It will hold the query itself and the result in a global variable.

Note: this is a proof of concept and is only meant as a demonstration on how to extend MySQL Connector/Python.

Why query caching?

You are doing lots of queries that have the same result. It would be expensive to always run the same exact query. MySQL has already a query cache, and there is also memcached. But you like MySQL Connector/Python so much you’d like to do it yourself.

A cursor caching queries and their result

To demonstrate a simple implementation of a query cache, we inherit from an existing class: MySQLCursorBuffered. It will save the executed operation with their results in a ‘global’ variable. We call this cursor MySQLCursorQueryCache.

We take the buffered cursor because we’d like to save the result right away. Below you see we only changed two methods for MySQLCursorBuffered:

• .execute(): it will now first check using an md5 checksum whether we executed the query before. If we did, we set the make the cached result active. If not, we simply executed.
• ._handle_resultset(): called when .execute() did an operation which has a result set. The result we know save in the QUERY_CACHE global dict.

from hashlib import md5
import mysql.connector

QUERY_CACHE = dict()

class MySQLCursorQueryCache(mysql.connector.cursor.MySQLCursorBuffered):
    def execute(self, operation, params=None):
        self._qmd5 = md5(operation).digest()
        if QUERY_CACHE.has_key(self._qmd5):
            (self._rows, eof) = QUERY_CACHE[self._qmd5]
            self.rowcount = len(self._rows)
            self._handle_eof(eof)
            self._next_row = 0
        else:
            super(MySQLCursorQCache, self).execute(operation, params)

    def _handle_resultset(self):
        (self._rows, eof) = self.db().protocol.get_rows()
        self.rowcount = len(self._rows)
        self._handle_eof(eof)
        self._next_row = 0
        QUERY_CACHE[self._qmd5] = (self._rows, eof)
        try:
            self.db().unread_result = False
        except:
            pass
        self._qmd5 = None

The above code is a proof of concept, there is lots of room for improvement. For example, you need something to invalidate entries in the query cache.

How to use MySQLCursorQueryCache

def main():

    cnx = mysql.connector.connect(database='test')
    cur = cnx.cursor(cursor_class=MySQLCursorQueryCache)

    cur.execute("SELECT NOW()")
    print cur.fetchone()
    time.sleep(2)
    cur.execute("SELECT NOW()")
    print cur.fetchone()

    cur.close()
    cnx.close()

When you would use the default cursor, both executed SQL statements would produce a different result. The above produces the following output:

(datetime.datetime(2010, 11, 22, 21, 20, 4),)
(datetime.datetime(2010, 11, 22, 21, 20, 4),)

When you’d like to have some statements cached, and some not, just create a second cursor cursor_class=MySQLCursorBuffered (see Buffering results with MySQL Connector/Python).

MySQL Connector/Python doesn’t buffer results by default. This means you have to fetch the rows when you issued a SELECT. This post describes how you can change this behavior.

Why buffering result sets?

Buffering or storing the result set on the client side is handy when you, for example, would like to use multiple cursors per connection and you’de like to traverse each one interleaved.

Keep in mind that with bigger result sets, the client side will use more memory. You just need to find out for yourself what’s best. When you know result sets are mostly small, you might opt to buffer.

MySQLdb by default buffers results and you need to use a different cursor to disable it. oursql does not buffer by default. This is good to know when you start using a different MySQL DB API for Python.

Use case: 1 connection, fetch from 2 cursors

You want to fetch data from two tables and process the data using one connection. If you do it without buffering, you would run into the following:

>>> cnx = mysql.connector.connect(database='test')
>>> cur1 = cnx.cursor()
>>> cur2 = cnx.cursor()
>>> cur1.execute("SELECT c1 FROM t1")
-1
>>> cur2.execute("SELECT c1 FROM t1")
..
mysql.connector.errors.InternalError: Unread result found.

MySQL Connector/Python offers two ways to turn buffering on or off. Either per connection or per cursor using the buffered argument set to True

Set buffering per connection

If you want all the cursors for a particular connection to be buffered, you can turn it on when connecting to MySQL setting the buffered-argument to True.

>>> import mysql.connector
>>> cnx = mysql.connector.connect(database='test',buffered=True)
>>> cur1 = cnx.cursor()
>>> cur1.__class__

>>> cur2 = cnx.cursor()
>>> cur1.execute("SELECT c1 FROM t1")
3
>>> cur2.execute("SELECT c1 FROM t1")
3
>>> cur1.fetchone()
(u'Geert',)
>>> cur2.fetchone()

Set buffering per cursor

You can request a buffering cursor buffering from the connection object. Do this use the buffered-argument set to True:

>>> import mysql.connector
>>> cnx = mysql.connector.connect(database='test')
>>> cur1 = cnx.cursor(buffered=True)
>>> cur1.__class__

Note: the above should work with MySQL Connector/Python v0.2 or greater

This post describes how to make a custom cursor returning rows as dictionaries using MySQL Connctor/Python v0.2 (or later).

Problem: you want to fetch rows from the database and return them as a dictionary with keys being the column names.

First, lets check how you would do it without any custom cursor.

cnx = mysql.connector.connect(host='localhost',database='test')
cur = cnx.cursor()
cur.execute("SELECT c1, c2 FROM t1")
result = []
columns = tuple( [d[0].decode('utf8') for d in cur.description] )
for row in cur:
  result.append(dict(zip(columns, row)))
pprint(result)
cur.close()
cnx.close()

The above results in an output like this:

[{u'c1': datetime.datetime(2010, 10, 13, 8, 55, 35), u'c2': u'ham'},
 {u'c1': datetime.datetime(2010, 10, 13, 8, 55, 38), u'c2': u'spam'}]

Alternatively, you can code your own cursor which will return a row as a dictionary. Here is an example on how to do this:

import mysql.connector

class MySQLCursorDict(mysql.connector.cursor.MySQLCursor):

  def fetchone(self):
    row = self._fetch_row()
      if row:
        return dict(zip(self.column_names, self._row_to_python(row)))
      return None

What we did is subclassing MySQLCursor and overload the fetchone()-method. To use the above, you can do following:

cnx = mysql.connector.connect(host='localhost',database='test')
cur = cnx.cursor(cursor_class=MySQLCursorDict)
cur.execute("SELECT c1, c2 FROM t1")
rows = cur.fetchall()
pprint(rows)
cur.close()
cnx.close()

The trick is to pass the cursor_class-argument when creating the cursor. (At the time of writing, I realize that the cursor_class should be part of the connection arguments to set a default for all requested cursors. We’ll change that.)

It’s a bit more elegant coding and possibilities are endless. Maybe you’d like a cursor which returns a row in XML for example.

MySQL Connector/Python is (or should be) compliant with the Python DB-API 2.0 specification. This means that you can use DBUtils' PooledDB module to implement database connection pooling.

Here below you'll find an example which will output the connection ID of each connection requested through the pooling mechanism.

from DBUtils.PooledDB import PooledDB
import mysql.connector

def main():
    pool_size = 3
    pool = PooledDB(mysql.connector, pool_size,
        database='test', user='root', host='127.0.0.1')
    
    cnx = [None,] * pool_size
    for i in xrange(0,pool_size):
        cnx[i] = pool.connection()
        cur = cnx[i].cursor()
        cur.execute("SELECT CONNECTION_ID()")
        print "Cnx %d has ID %d" % (i+1,cur.fetchone()[0])
        cur.close()
    
    for c in cnx:
        c.close()

The output will be something like this:

Cnx 1 has ID 42
Cnx 2 has ID 41
Cnx 3 has ID 40

Background Knowledge

The Search Plugin for TaskFreak! created by DaDaemon and xdu v0.0.1 (March 26, 2007) was designed to create a simple, quick search capability of the tasks title and description. As well it only searched through he current task view (tasks visible at the time) and tasks that are not completed. For some this was not what was desired and would rather have the Search Plugin search through all tasks weather completed or not and as well search through the comments of tasks along with the title and description. I’ll show you how this is done using Searcher, bchristie and davidlmansfield instructions posted on the TaskFreak! Forums.

Solution – Add the Ability to Search All Tasks

Edit the “index.php” located in the root of TaskFreak! as follows. We will be working in the “Load Tasks” section to just above the “Task Order” section. This solution was posted by Searcher at Re: Quick ‘n’ Dirty Search Plugin.

Code Before

39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198

$arrFilters = array();
 
// --- filter: project ---
if ($pProject) {
    // load tasks for specific project
	$sqlFilter = 'ii.projectId = \''.$pProject.'\'';
    $pLink=Tzn::concatUrl($pLink,'sProject='.$pProject);
} else if (!$objUser->checkLevel(6)) {
    // user can only access his own projects
    if ($objUserProjectList->rMore()) {
        $arrProject = array();
        while($objTmp = $objUserProjectList->rNext()) {
            $arrProject[] = $objTmp->id;
        }
        if ($objUser->checkLevel(13)) {
        	$arrProject[] = '0';
        }
        $sqlFilter = 'ii.projectId IN ('.implode(',',$arrProject).')';
 
        unset($arrProject);
        $objUserProjectList->rReset();
    }
}
 
$objItemList->addWhere($sqlFilter);
 
// --- filter: user ---
$pUser = intval($_REQUEST['sUser']);
if (!isset($_REQUEST['sUser']) && @constant('FRK_DEFAULT_VIEW_OWN_TASKS')) {
	// default view is own tasks only
	$pUser = $objUser->id;
}
 
if($_REQUEST['sUser'] == 'myprojects' && $_SESSION['selUser']){
   $objItemList->addWhere('ii.authorId='.$_SESSION['selUser']);
}
elseif ($pUser) {
	$objItemList->addWhere('ii.memberId = \''.$pUser.'\'');
    $_SESSION['selUser'] = $pUser;
    $pDefaultUserId = $pUser;
} else {
    unset($_SESSION['selUser']);
    session_unregister('selUser');
    $pDefaultUserId = $objUser->id;
    // by default, show own tasks
    /*
    if (!$objUser->checkLevel(1)) { // admin can see all
	    $objItemList->addWhere('(ii.memberId='.$objUser->id
    		.' OR ii.authorId='.$objUser->id.')');
    }
    */
}
 
$pLink=Tzn::concatUrl($pLink,'sUser='.$pUser);
 
// --- private tasks --------------------------------------------------------
 
$arrFilter[] = 'showPrivate=0';
 
if ($objUser->checkLevel(12)) {
	// show internal tasks
	$arrFilter[] = 'showPrivate=1';
}
 
$arrFilter[] = '(showPrivate=2 AND (ii.memberId='.$objUser->id
	.' OR ii.authorId='.$objUser->id.'))';
 
$objItemList->addWhere('('.implode(' OR ',$arrFilter).')');
 
// --- filter: context ---
 
if ($_REQUEST['sContext']) {
	$pContext = Tzn::getHttp($_REQUEST['sContext'],true);
	$objItemList->addWhere('context = \''.$pContext.'\'');
    $pLink=Tzn::concatUrl($pLink,'sContext='.$pContext);
}
 
$sqlFilter = '';
$pShow = ($_REQUEST['show'])?$_REQUEST['show']:'today';
$pLink=Tzn::concatUrl($pLink,'show='.$pShow);
 
$pKeepNoDead = intval(@constant('FRK_NO_DEADLINE_KEEP') -1) * 86400;
 
// --- Filter per date -----------------------------------------------------
 
switch ($pShow) {
	case 'all':
		break;
	case 'future':
		// show coming tasks and late tasks (undone only)
		$sqlFilter = '((deadlineDate >= \''
			.strftime(TZN_DATE_SQL,PRJ_DTE_NOW).'\' AND statusKey < '
			.FRK_STATUS_LEVELS.')'.' OR statusKey < '.FRK_STATUS_LEVELS.')';
        // show uncompleted tasks with no deadline
		$sqlFilter .= ' OR (deadlineDate = \'9999-00-00\' AND statusKey < '
			.FRK_STATUS_LEVELS.')';
		break;
	case 'past':
		// show past tasks and already done
		$sqlFilter = '(deadlineDate < \''
			.strftime(TZN_DATE_SQL,PRJ_DTE_NOW).'\' OR statusKey = '
			.FRK_STATUS_LEVELS.')';
		break;
	case 'today':
		// show all future tasks (done + undone) and late tasks
		$pKeepNoDead = intval(@constant('FRK_NO_DEADLINE_KEEP') -1) * 86400;
		$sqlFilter = '(statusKey = '.FRK_STATUS_LEVELS.' AND statusDate > \''
			.gmdate('Y-m-d 00:00:00',time()-$pKeepNoDead).'\') ';
 
		// hide far future tasks ?
		$tmpFilter = '';
		if (@constant('FRK_DEFAULT_FAR_FUTURE_HIDE')) {
			$tmp = intval(FRK_DEFAULT_FAR_FUTURE_HIDE) * 86400;
			$tmpFilter .= 'deadlineDate < \''
				.gmdate('Y-m-d 00:00:00',time()+$tmp).'\'';
		}
 
		// show tasks with no deadline ?
		if (@constant('FRK_NO_DEADLINE_TOO')) {
			// yes
			if ($tmpFilter) {
				$sqlFilter .= 'OR ('.$tmpFilter
					.' OR deadlineDate = \'9999-00-00\')'
					. ' AND statusKey < '.FRK_STATUS_LEVELS;
			} else {
				$sqlFilter .= ' OR statusKey < '.FRK_STATUS_LEVELS;
			}
		} else {
			// don't show uncompleted non planned tasks
			if ($tmpFilter) {
				$sqlFilter .= ' OR ('.$tmpFilter.' AND statusKey < '
    	        	.FRK_STATUS_LEVELS.')';
			} else {
	            $sqlFilter .= ' OR (deadlineDate <> \'9999-00-00\' AND statusKey < '
    	        	.FRK_STATUS_LEVELS.')';
			}
		}
 
        if (@constant('FRK_DEFAULT_CURRENT_TASKS')) {
            $objItemList->setPagination(FRK_DEFAULT_CURRENT_TASKS);
        }
		break;
	default:
		break;
}
 
// -TODO- Add filter current project only (no completed, no cancelled)
 
// echo '<p>&</p><p>-</p><p>-</p>'.$sqlFilter;
 
if ($sqlFilter) {
	$objItemList->addDateFilter($sqlFilter);
}
 
// search filter
	$pSearch = ($_REQUEST['search']);
	if ($pSearch) {
		$sqlFilter = '(ii.title LIKE \'%'.$pSearch.'%\' OR ii.description LIKE \'%'.$pSearch.'%\')';
		$objItemList->addWhere($sqlFilter);
	}

Code After

39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199

$pSearch = ($_REQUEST['search']);
if ($pSearch) {
	$sqlFilter = '(ii.title LIKE \'%'.$pSearch.'%\' OR ii.description LIKE \'%'.$pSearch.'%\')';
	$objItemList->addWhere($sqlFilter);
}
else
{
	$arrFilters = array();
 
	// --- filter: project ---
	if ($pProject) {
	    // load tasks for specific project
		$sqlFilter = 'ii.projectId = \''.$pProject.'\'';
	    $pLink=Tzn::concatUrl($pLink,'sProject='.$pProject);
	} else if (!$objUser->checkLevel(6)) {
	    // user can only access his own projects
	    if ($objUserProjectList->rMore()) {
	        $arrProject = array();
	        while($objTmp = $objUserProjectList->rNext()) {
	            $arrProject[] = $objTmp->id;
	        }
	        if ($objUser->checkLevel(13)) {
	        	$arrProject[] = '0';
	        }
	        $sqlFilter = 'ii.projectId IN ('.implode(',',$arrProject).')';
 
	        unset($arrProject);
	        $objUserProjectList->rReset();
	    }
	}
 
	$objItemList->addWhere($sqlFilter);
 
	// --- filter: user ---
	$pUser = intval($_REQUEST['sUser']);
	if (!isset($_REQUEST['sUser']) && @constant('FRK_DEFAULT_VIEW_OWN_TASKS')) {
		// default view is own tasks only
		$pUser = $objUser->id;
	}
 
	if($_REQUEST['sUser'] == 'myprojects' && $_SESSION['selUser']){
	   $objItemList->addWhere('ii.authorId='.$_SESSION['selUser']);
	}
	elseif ($pUser) {
		$objItemList->addWhere('ii.memberId = \''.$pUser.'\'');
	    $_SESSION['selUser'] = $pUser;
	    $pDefaultUserId = $pUser;
	} else {
	    unset($_SESSION['selUser']);
	    session_unregister('selUser');
	    $pDefaultUserId = $objUser->id;
	    // by default, show own tasks
	    /*
	    if (!$objUser->checkLevel(1)) { // admin can see all
		    $objItemList->addWhere('(ii.memberId='.$objUser->id
	    		.' OR ii.authorId='.$objUser->id.')');
	    }
	    */
	}
 
	$pLink=Tzn::concatUrl($pLink,'sUser='.$pUser);
 
	// --- private tasks --------------------------------------------------------
 
	$arrFilter[] = 'showPrivate=0';
 
	if ($objUser->checkLevel(12)) {
		// show internal tasks
		$arrFilter[] = 'showPrivate=1';
	}
 
	$arrFilter[] = '(showPrivate=2 AND (ii.memberId='.$objUser->id
		.' OR ii.authorId='.$objUser->id.'))';
 
	$objItemList->addWhere('('.implode(' OR ',$arrFilter).')');
 
	// --- filter: context ---
 
	if ($_REQUEST['sContext']) {
		$pContext = Tzn::getHttp($_REQUEST['sContext'],true);
		$objItemList->addWhere('context = \''.$pContext.'\'');
	    $pLink=Tzn::concatUrl($pLink,'sContext='.$pContext);
	}
 
	$sqlFilter = '';
	$pShow = ($_REQUEST['show'])?$_REQUEST['show']:'today';
	$pLink=Tzn::concatUrl($pLink,'show='.$pShow);
 
	$pKeepNoDead = intval(@constant('FRK_NO_DEADLINE_KEEP') -1) * 86400;
 
	// --- Filter per date -----------------------------------------------------
 
	switch ($pShow) {
		case 'all':
			break;
		case 'future':
			// show coming tasks and late tasks (undone only)
			$sqlFilter = '((deadlineDate >= \''
				.strftime(TZN_DATE_SQL,PRJ_DTE_NOW).'\' AND statusKey < '
				.FRK_STATUS_LEVELS.')'.' OR statusKey < '.FRK_STATUS_LEVELS.')';
	        // show uncompleted tasks with no deadline
			$sqlFilter .= ' OR (deadlineDate = \'9999-00-00\' AND statusKey < '
				.FRK_STATUS_LEVELS.')';
			break;
		case 'past':
			// show past tasks and already done
			$sqlFilter = '(deadlineDate < \''
				.strftime(TZN_DATE_SQL,PRJ_DTE_NOW).'\' OR statusKey = '
				.FRK_STATUS_LEVELS.')';
			break;
		case 'today':
			// show all future tasks (done + undone) and late tasks
			$pKeepNoDead = intval(@constant('FRK_NO_DEADLINE_KEEP') -1) * 86400;
			$sqlFilter = '(statusKey = '.FRK_STATUS_LEVELS.' AND statusDate > \''
				.gmdate('Y-m-d 00:00:00',time()-$pKeepNoDead).'\') ';
 
			// hide far future tasks ?
			$tmpFilter = '';
			if (@constant('FRK_DEFAULT_FAR_FUTURE_HIDE')) {
				$tmp = intval(FRK_DEFAULT_FAR_FUTURE_HIDE) * 86400;
				$tmpFilter .= 'deadlineDate < \''
					.gmdate('Y-m-d 00:00:00',time()+$tmp).'\'';
			}
 
			// show tasks with no deadline ?
			if (@constant('FRK_NO_DEADLINE_TOO')) {
				// yes
				if ($tmpFilter) {
					$sqlFilter .= 'OR ('.$tmpFilter
						.' OR deadlineDate = \'9999-00-00\')'
						. ' AND statusKey < '.FRK_STATUS_LEVELS;
				} else {
					$sqlFilter .= ' OR statusKey < '.FRK_STATUS_LEVELS;
				}
			} else {
				// don't show uncompleted non planned tasks
				if ($tmpFilter) {
					$sqlFilter .= ' OR ('.$tmpFilter.' AND statusKey < '
	    	        	.FRK_STATUS_LEVELS.')';
				} else {
		            $sqlFilter .= ' OR (deadlineDate <> \'9999-00-00\' AND statusKey < '
	    	        	.FRK_STATUS_LEVELS.')';
				}
			}
 
	        if (@constant('FRK_DEFAULT_CURRENT_TASKS')) {
	            $objItemList->setPagination(FRK_DEFAULT_CURRENT_TASKS);
	        }
			break;
		default:
			break;
	}
 
	// -TODO- Add filter current project only (no completed, no cancelled)
 
	// echo '<p>&</p><p>-</p><p>-</p>'.$sqlFilter;
 
	if ($sqlFilter) {
		$objItemList->addDateFilter($sqlFilter);
	}
}

Solution – Add Ability to Also Search within Task Comments

Edit the “index.php” located in the root of TaskFreak! as follows. We will be working in just below the heading section of the “Load Tasks”. This solution was posted by davidlmansfield at [PATCH] [Search Plugin] include tasks matching in comment fields.

Code Before

39
40
41
42
43
44

// search filter
$pSearch = ($_REQUEST['search']);
if ($pSearch) {
	$sqlFilter = '(ii.title LIKE \'%'.$pSearch.'%\' OR ii.description LIKE \'%'.$pSearch.'%\')';
	$objItemList->addWhere($sqlFilter);
}

Code After

39
40
41
42
43
44

// search filter
$pSearch = ($_REQUEST['search']);
if ($pSearch) {
	$sqlFilter = '(ii.title LIKE \'%'.$pSearch.'%\' OR ii.description LIKE \'%'.$pSearch.'%\' OR ii.itemId in (select itemId from '.$objItemList->gTable('itemComment').' where body LIKE \'%'.$pSearch.'%\'))';
	$objItemList->addWhere($sqlFilter);
}

Solution – Stop SQL Injections

Edit the “index.php” located in the root of TaskFreak! as follows. We will be working in just below the heading section of the “Load Tasks”. This solution was posted by bchristie at Re: Quick ‘n’ Dirty Search Plugin.

Code Before

39
40

// search filter
$pSearch = ($_REQUEST['search']);

Code After

39
40

// search filter
$pSearch = isset($_REQUEST['search'])?mysql_real_escape_string($_REQUEST['search']):false;