I realised that a lot of my old postings were not tagged by awtags because I hadn't been through them to categorise them and, worst still, I couldn't be bothered. This meant that the postings weren't indexed unless someone went way back through the blog history.
I decided to create a new tag for them called untagged. I wrote a python script to look for awtags with no tags assigned and to assign them to this new tag. I used python because I am far more confident in it than I am in php. There is not much point in making this a module or anything because it only needs doing once if I am methodical about giving tags to new postings. I could also have done this in raw SQL but the version of MySQL on Site5 does not support nested selects.
Once the 'untagged' tag is in place the only chore is to remove this tag from postings that I generate new tags for using my search facility. This can be done through the awtags administration interface (e.g. search for tag 'whatever' and remove tag 'untagged'). Also, now I can easily list the untagged articles, it is much easier to see what tags need adding.
1
2
3
4 import MySQLdb
5 import DBTable
6
7 o = MySQLdb.Connect( 'localhost', '<mysql user name', '<password>', '<mysql database name>')
8
9 oAwNodeDB = DBTable.DBTable( o, 'awtags_node')
10
11 oAwNodeDB.Select()
12
13 oTaggedNodes = {}
14
15 while 1:
16 oRow = oAwNodeDB.FetchOne()
17 if not oRow:
18 break
19 oTaggedNodes[oRow['nid']] = 1
20
21 oNodeDB = DBTable.DBTable( o, 'node')
22
23 oNodeDB.Select( "SELECT * FROM node WHERE node.type = 'blog'")
24
25 oNodes = {}
26
27 while 1:
28 oRow = oNodeDB.FetchOne()
29 if not oRow:
30 break
31 oNodes[oRow['nid']] = 1
32
33 for nNid in oNodes.keys():
34 if not oTaggedNodes.has_key( nNid):
35 oDict = { 'nid': nNid,
36 'tid': 84}
37 oAwNodeDB.Insert( oDict)
This assumes the 'untagged' tag has a tid of 84: you should create your own tag and see what number it is.
This uses the DBTable module I wrote a while back. I discovered to my delight that the python odbc and MySQLdb modules had virtually identical interfaces so this module worked largely unchanged. I had to tweek it a bit because the field types were recorded as numbers instead of strings. Here is the modified version. It should work with odbc as well.
1
2
3
4 class DBTable:
5 """
6 Wrapper for database table
7 """
8 FIELD_TYPE = 0
9
10 def __init__( self, oConnection, strTable):
11 self.oConnection = oConnection
12 self.strTable = strTable
13
14 oCursor = oConnection.cursor()
15 oCursor.execute( "SELECT * FROM %s" % strTable)
16
17 self.oFields = [ oField[0] for oField in oCursor.description]
18 self.oFieldDescription = dict( [ (oField[0],
19 oField[1:]) for oField in oCursor.description])
20
21 def Select( self, strQuery = None):
22 """
23 Select records from query
24
25 Takes either SQL of select statement or a dictionary containing
26 field names and values to find.
27 """
28 self.oCursor = self.oConnection.cursor()
29 if strQuery == None:
30 self.oCursor.execute( "SELECT * FROM %s WHERE 1" % (self.strTable))
31 elif type( strQuery) == type(""):
32 self.oCursor.execute( strQuery)
33 else:
34
35
36
37 self.oCursor.execute( "SELECT * FROM %s WHERE %s" % (self.strTable,
38 self.DictToWhere( strQuery)))
39
40 def FetchOne( self):
41 """
42 Get next row of results
43 Returns a dictionary holding field names and values.
44 """
45 oRow = self.oCursor.fetchone()
46 if oRow:
47 """
48 Build a dictionary to map field name->value
49 """
50 return dict([(self.oFields[i], oRow[i]) for i in range(len(oRow))])
51 else:
52 return None
53
54 def Insert( self, oDict):
55 """
56 Insert a row in the database
57 Takes a dictionary holding field names and values.
58 """
59 strFields = oDict.keys()
60 strValues = []
61 for strField in strFields:
62 strValue = oDict[strField]
63 strType = self.oFieldDescription[strField][DBTable.FIELD_TYPE]
64
65 strValues.append( self.FormatField( strField, strValue))
66
67 strSQL = "INSERT INTO %s ( %s) VALUES(%s);" % ( self.strTable,
68 ", ".join( strFields),
69 ", ".join( strValues))
70 print strSQL
71 oCursor = self.oConnection.cursor()
72 oCursor.execute( strSQL)
73 self.oConnection.commit()
74
75 def Update( self, oDictWhere, oDictNew):
76 """
77 Update a row in the database
78 Takes a dictionary holding field names and values to find
79 and dictionary to replace it with.
80 """
81 strFields = oDictNew.keys()
82 strValues = []
83 for strField in strFields:
84 strValue = oDictNew[strField]
85 strType = self.oFieldDescription[strField][DBTable.FIELD_TYPE]
86
87 strValues.append( "%s = %s" % (strField, self.FormatField( strField, strValue)))
88
89 strSQL = "UPDATE %s SET %s WHERE %s;" % ( self.strTable,
90 ", ".join( strValues),
91 self.DictToWhere( oDictWhere))
92 print strSQL
93 oCursor = self.oConnection.cursor()
94 oCursor.execute( strSQL)
95 self.oConnection.commit()
96
97 def InsertOrUpdate( self, oDictWhere, oDictNew):
98 """
99 Seek record in database, add it if not found, update it if found.
100 """
101 self.Select( oDictWhere)
102 if self.FetchOne():
103 self.Update( oDictWhere, oDictNew)
104 else:
105 self.Insert( oDictNew)
106
107 def Delete( self, oDict):
108 """
109 Delete row based on dictionary contents
110 Takes a dictionary holding field names and values.
111 """
112 strSQL = "DELETE FROM %s WHERE %s;" % ( self.strTable, self.DictToWhere( oDict))
113
114 oCursor = self.oConnection.cursor()
115 oCursor.execute( strSQL)
116 self.oConnection.commit()
117
118 def DictToWhere( self, oDict):
119 """
120 Convert dictionary to WHERE clause.
121 """
122 strFields = oDict.keys()
123 strExpressions = []
124
125 for strField in strFields:
126 strValue = oDict[strField]
127 strType = self.oFieldDescription[strField][DBTable.FIELD_TYPE]
128
129 strValue = self.FormatField( strField, strValue)
130
131 strExpressions.append( '%s = %s' % (strField, strValue))
132
133 return " AND ".join( strExpressions)
134
135 def FormatField( self, strField, strValue):
136 """
137 Format a field for an sql statement.
138 """
139 strType = self.oFieldDescription[strField][DBTable.FIELD_TYPE]
140 if strType == 'STRING':
141 return "'%s'" % str(strValue).replace( "'", "''")
142 elif strType == 'NUMBER' or strType == 3:
143 return '%d' % int( strValue)
144 else:
145
146
147
148 return "'%s'" % str(strValue).replace( "'", "''")
149
so I changed the sql in the function awTagsAPI_GetNodesForTag in awTags.inc to make it consider the sticky bit as follows:
Peter's Moblog
