diff options
author | Evgeny Fadeev <evgeny.fadeev@gmail.com> | 2013-02-01 14:56:33 -0300 |
---|---|---|
committer | Evgeny Fadeev <evgeny.fadeev@gmail.com> | 2013-02-01 14:56:33 -0300 |
commit | f50e8b949ae18e0bfad197a67cb2dd70ff3b2077 (patch) | |
tree | 33997db30743cefe3c01418273e22ed008642a7f | |
parent | 739c59b6d7b756b06a6d50a832e748c1c6c73a6a (diff) | |
download | askbot-f50e8b949ae18e0bfad197a67cb2dd70ff3b2077.tar.gz askbot-f50e8b949ae18e0bfad197a67cb2dd70ff3b2077.tar.bz2 askbot-f50e8b949ae18e0bfad197a67cb2dd70ff3b2077.zip |
some work towards multilingual search
-rw-r--r-- | askbot/migrations/0162_auto__add_field_post_language_code__del_field_thread_junk.py | 391 | ||||
-rw-r--r-- | askbot/models/post.py | 2 | ||||
-rw-r--r-- | askbot/search/postgresql/__init__.py | 32 | ||||
-rw-r--r-- | askbot/search/postgresql/thread_and_post_models_01022013.plsql | 289 |
4 files changed, 710 insertions, 4 deletions
diff --git a/askbot/migrations/0162_auto__add_field_post_language_code__del_field_thread_junk.py b/askbot/migrations/0162_auto__add_field_post_language_code__del_field_thread_junk.py new file mode 100644 index 00000000..9733b817 --- /dev/null +++ b/askbot/migrations/0162_auto__add_field_post_language_code__del_field_thread_junk.py @@ -0,0 +1,391 @@ +# encoding: utf-8 +import datetime +from south.db import db +from south.v2 import SchemaMigration +from django.db import models + +class Migration(SchemaMigration): + + def forwards(self, orm): + + # Adding field 'Post.language_code' + db.add_column('askbot_post', 'language_code', self.gf('django.db.models.fields.CharField')(default='en', max_length=16), keep_default=False) + + + def backwards(self, orm): + + # Deleting field 'Post.language_code' + db.delete_column('askbot_post', 'language_code') + + + models = { + 'askbot.activity': { + 'Meta': {'object_name': 'Activity', 'db_table': "u'activity'"}, + 'active_at': ('django.db.models.fields.DateTimeField', [], {'default': 'datetime.datetime.now'}), + 'activity_type': ('django.db.models.fields.SmallIntegerField', [], {}), + 'content_type': ('django.db.models.fields.related.ForeignKey', [], {'to': "orm['contenttypes.ContentType']"}), + 'id': ('django.db.models.fields.AutoField', [], {'primary_key': 'True'}), + 'is_auditted': ('django.db.models.fields.BooleanField', [], {'default': 'False'}), + 'object_id': ('django.db.models.fields.PositiveIntegerField', [], {}), + 'question': ('django.db.models.fields.related.ForeignKey', [], {'to': "orm['askbot.Post']", 'null': 'True'}), + 'receiving_users': ('django.db.models.fields.related.ManyToManyField', [], {'related_name': "'received_activity'", 'symmetrical': 'False', 'to': "orm['auth.User']"}), + 'recipients': ('django.db.models.fields.related.ManyToManyField', [], {'related_name': "'incoming_activity'", 'symmetrical': 'False', 'through': "orm['askbot.ActivityAuditStatus']", 'to': "orm['auth.User']"}), + 'summary': ('django.db.models.fields.TextField', [], {'default': "''"}), + 'user': ('django.db.models.fields.related.ForeignKey', [], {'to': "orm['auth.User']"}) + }, + 'askbot.activityauditstatus': { + 'Meta': {'unique_together': "(('user', 'activity'),)", 'object_name': 'ActivityAuditStatus'}, + 'activity': ('django.db.models.fields.related.ForeignKey', [], {'to': "orm['askbot.Activity']"}), + 'id': ('django.db.models.fields.AutoField', [], {'primary_key': 'True'}), + 'status': ('django.db.models.fields.SmallIntegerField', [], {'default': '0'}), + 'user': ('django.db.models.fields.related.ForeignKey', [], {'to': "orm['auth.User']"}) + }, + 'askbot.anonymousanswer': { + 'Meta': {'object_name': 'AnonymousAnswer'}, + 'added_at': ('django.db.models.fields.DateTimeField', [], {'default': 'datetime.datetime.now'}), + 'author': ('django.db.models.fields.related.ForeignKey', [], {'to': "orm['auth.User']", 'null': 'True'}), + 'id': ('django.db.models.fields.AutoField', [], {'primary_key': 'True'}), + 'ip_addr': ('django.db.models.fields.IPAddressField', [], {'max_length': '15'}), + 'question': ('django.db.models.fields.related.ForeignKey', [], {'related_name': "'anonymous_answers'", 'to': "orm['askbot.Post']"}), + 'session_key': ('django.db.models.fields.CharField', [], {'max_length': '40'}), + 'text': ('django.db.models.fields.TextField', [], {}), + 'wiki': ('django.db.models.fields.BooleanField', [], {'default': 'False'}) + }, + 'askbot.anonymousquestion': { + 'Meta': {'object_name': 'AnonymousQuestion'}, + 'added_at': ('django.db.models.fields.DateTimeField', [], {'default': 'datetime.datetime.now'}), + 'author': ('django.db.models.fields.related.ForeignKey', [], {'to': "orm['auth.User']", 'null': 'True'}), + 'id': ('django.db.models.fields.AutoField', [], {'primary_key': 'True'}), + 'ip_addr': ('django.db.models.fields.IPAddressField', [], {'max_length': '15'}), + 'is_anonymous': ('django.db.models.fields.BooleanField', [], {'default': 'False'}), + 'session_key': ('django.db.models.fields.CharField', [], {'max_length': '40'}), + 'tagnames': ('django.db.models.fields.CharField', [], {'max_length': '125'}), + 'text': ('django.db.models.fields.TextField', [], {}), + 'title': ('django.db.models.fields.CharField', [], {'max_length': '300'}), + 'wiki': ('django.db.models.fields.BooleanField', [], {'default': 'False'}) + }, + 'askbot.askwidget': { + 'Meta': {'object_name': 'AskWidget'}, + 'group': ('django.db.models.fields.related.ForeignKey', [], {'to': "orm['askbot.Group']", 'null': 'True', 'blank': 'True'}), + 'id': ('django.db.models.fields.AutoField', [], {'primary_key': 'True'}), + 'include_text_field': ('django.db.models.fields.BooleanField', [], {'default': 'False'}), + 'inner_style': ('django.db.models.fields.TextField', [], {'blank': 'True'}), + 'outer_style': ('django.db.models.fields.TextField', [], {'blank': 'True'}), + 'tag': ('django.db.models.fields.related.ForeignKey', [], {'to': "orm['askbot.Tag']", 'null': 'True', 'blank': 'True'}), + 'title': ('django.db.models.fields.CharField', [], {'max_length': '100'}) + }, + 'askbot.award': { + 'Meta': {'object_name': 'Award', 'db_table': "u'award'"}, + 'awarded_at': ('django.db.models.fields.DateTimeField', [], {'default': 'datetime.datetime.now'}), + 'badge': ('django.db.models.fields.related.ForeignKey', [], {'related_name': "'award_badge'", 'to': "orm['askbot.BadgeData']"}), + 'content_type': ('django.db.models.fields.related.ForeignKey', [], {'to': "orm['contenttypes.ContentType']"}), + 'id': ('django.db.models.fields.AutoField', [], {'primary_key': 'True'}), + 'notified': ('django.db.models.fields.BooleanField', [], {'default': 'False'}), + 'object_id': ('django.db.models.fields.PositiveIntegerField', [], {}), + 'user': ('django.db.models.fields.related.ForeignKey', [], {'related_name': "'award_user'", 'to': "orm['auth.User']"}) + }, + 'askbot.badgedata': { + 'Meta': {'ordering': "('slug',)", 'object_name': 'BadgeData'}, + 'awarded_count': ('django.db.models.fields.PositiveIntegerField', [], {'default': '0'}), + 'awarded_to': ('django.db.models.fields.related.ManyToManyField', [], {'related_name': "'badges'", 'symmetrical': 'False', 'through': "orm['askbot.Award']", 'to': "orm['auth.User']"}), + 'id': ('django.db.models.fields.AutoField', [], {'primary_key': 'True'}), + 'slug': ('django.db.models.fields.SlugField', [], {'unique': 'True', 'max_length': '50', 'db_index': 'True'}) + }, + 'askbot.bulktagsubscription': { + 'Meta': {'ordering': "['-date_added']", 'object_name': 'BulkTagSubscription'}, + 'date_added': ('django.db.models.fields.DateField', [], {'auto_now_add': 'True', 'blank': 'True'}), + 'groups': ('django.db.models.fields.related.ManyToManyField', [], {'to': "orm['askbot.Group']", 'symmetrical': 'False'}), + 'id': ('django.db.models.fields.AutoField', [], {'primary_key': 'True'}), + 'tags': ('django.db.models.fields.related.ManyToManyField', [], {'to': "orm['askbot.Tag']", 'symmetrical': 'False'}), + 'users': ('django.db.models.fields.related.ManyToManyField', [], {'to': "orm['auth.User']", 'symmetrical': 'False'}) + }, + 'askbot.draftanswer': { + 'Meta': {'object_name': 'DraftAnswer'}, + 'author': ('django.db.models.fields.related.ForeignKey', [], {'related_name': "'draft_answers'", 'to': "orm['auth.User']"}), + 'id': ('django.db.models.fields.AutoField', [], {'primary_key': 'True'}), + 'text': ('django.db.models.fields.TextField', [], {'null': 'True'}), + 'thread': ('django.db.models.fields.related.ForeignKey', [], {'related_name': "'draft_answers'", 'to': "orm['askbot.Thread']"}) + }, + 'askbot.draftquestion': { + 'Meta': {'object_name': 'DraftQuestion'}, + 'author': ('django.db.models.fields.related.ForeignKey', [], {'to': "orm['auth.User']"}), + 'id': ('django.db.models.fields.AutoField', [], {'primary_key': 'True'}), + 'tagnames': ('django.db.models.fields.CharField', [], {'max_length': '125', 'null': 'True'}), + 'text': ('django.db.models.fields.TextField', [], {'null': 'True'}), + 'title': ('django.db.models.fields.CharField', [], {'max_length': '300', 'null': 'True'}) + }, + 'askbot.emailfeedsetting': { + 'Meta': {'unique_together': "(('subscriber', 'feed_type'),)", 'object_name': 'EmailFeedSetting'}, + 'added_at': ('django.db.models.fields.DateTimeField', [], {'auto_now_add': 'True', 'blank': 'True'}), + 'feed_type': ('django.db.models.fields.CharField', [], {'max_length': '16'}), + 'frequency': ('django.db.models.fields.CharField', [], {'default': "'n'", 'max_length': '8'}), + 'id': ('django.db.models.fields.AutoField', [], {'primary_key': 'True'}), + 'reported_at': ('django.db.models.fields.DateTimeField', [], {'null': 'True'}), + 'subscriber': ('django.db.models.fields.related.ForeignKey', [], {'related_name': "'notification_subscriptions'", 'to': "orm['auth.User']"}) + }, + 'askbot.favoritequestion': { + 'Meta': {'object_name': 'FavoriteQuestion', 'db_table': "u'favorite_question'"}, + 'added_at': ('django.db.models.fields.DateTimeField', [], {'default': 'datetime.datetime.now'}), + 'id': ('django.db.models.fields.AutoField', [], {'primary_key': 'True'}), + 'thread': ('django.db.models.fields.related.ForeignKey', [], {'to': "orm['askbot.Thread']"}), + 'user': ('django.db.models.fields.related.ForeignKey', [], {'related_name': "'user_favorite_questions'", 'to': "orm['auth.User']"}) + }, + 'askbot.group': { + 'Meta': {'object_name': 'Group', '_ormbases': ['auth.Group']}, + 'description': ('django.db.models.fields.related.OneToOneField', [], {'blank': 'True', 'related_name': "'described_group'", 'unique': 'True', 'null': 'True', 'to': "orm['askbot.Post']"}), + 'group_ptr': ('django.db.models.fields.related.OneToOneField', [], {'to': "orm['auth.Group']", 'unique': 'True', 'primary_key': 'True'}), + 'is_vip': ('django.db.models.fields.BooleanField', [], {'default': 'False'}), + 'logo_url': ('django.db.models.fields.URLField', [], {'max_length': '200', 'null': 'True'}), + 'moderate_answers_to_enquirers': ('django.db.models.fields.BooleanField', [], {'default': 'False'}), + 'moderate_email': ('django.db.models.fields.BooleanField', [], {'default': 'True'}), + 'openness': ('django.db.models.fields.SmallIntegerField', [], {'default': '2'}), + 'preapproved_email_domains': ('django.db.models.fields.TextField', [], {'default': "''", 'null': 'True', 'blank': 'True'}), + 'preapproved_emails': ('django.db.models.fields.TextField', [], {'default': "''", 'null': 'True', 'blank': 'True'}) + }, + 'askbot.groupmembership': { + 'Meta': {'object_name': 'GroupMembership', '_ormbases': ['auth.AuthUserGroups']}, + 'authusergroups_ptr': ('django.db.models.fields.related.OneToOneField', [], {'to': "orm['auth.AuthUserGroups']", 'unique': 'True', 'primary_key': 'True'}), + 'level': ('django.db.models.fields.SmallIntegerField', [], {'default': '1'}) + }, + 'askbot.markedtag': { + 'Meta': {'object_name': 'MarkedTag'}, + 'id': ('django.db.models.fields.AutoField', [], {'primary_key': 'True'}), + 'reason': ('django.db.models.fields.CharField', [], {'max_length': '16'}), + 'tag': ('django.db.models.fields.related.ForeignKey', [], {'related_name': "'user_selections'", 'to': "orm['askbot.Tag']"}), + 'user': ('django.db.models.fields.related.ForeignKey', [], {'related_name': "'tag_selections'", 'to': "orm['auth.User']"}) + }, + 'askbot.post': { + 'Meta': {'object_name': 'Post'}, + 'added_at': ('django.db.models.fields.DateTimeField', [], {'default': 'datetime.datetime.now'}), + 'approved': ('django.db.models.fields.BooleanField', [], {'default': 'True', 'db_index': 'True'}), + 'author': ('django.db.models.fields.related.ForeignKey', [], {'related_name': "'posts'", 'to': "orm['auth.User']"}), + 'comment_count': ('django.db.models.fields.PositiveIntegerField', [], {'default': '0'}), + 'deleted': ('django.db.models.fields.BooleanField', [], {'default': 'False', 'db_index': 'True'}), + 'deleted_at': ('django.db.models.fields.DateTimeField', [], {'null': 'True', 'blank': 'True'}), + 'deleted_by': ('django.db.models.fields.related.ForeignKey', [], {'blank': 'True', 'related_name': "'deleted_posts'", 'null': 'True', 'to': "orm['auth.User']"}), + 'groups': ('django.db.models.fields.related.ManyToManyField', [], {'related_name': "'group_posts'", 'symmetrical': 'False', 'through': "orm['askbot.PostToGroup']", 'to': "orm['askbot.Group']"}), + 'html': ('django.db.models.fields.TextField', [], {'null': 'True'}), + 'id': ('django.db.models.fields.AutoField', [], {'primary_key': 'True'}), + 'is_anonymous': ('django.db.models.fields.BooleanField', [], {'default': 'False'}), + 'language_code': ('django.db.models.fields.CharField', [], {'default': "'en'", 'max_length': '16'}), + 'last_edited_at': ('django.db.models.fields.DateTimeField', [], {'null': 'True', 'blank': 'True'}), + 'last_edited_by': ('django.db.models.fields.related.ForeignKey', [], {'blank': 'True', 'related_name': "'last_edited_posts'", 'null': 'True', 'to': "orm['auth.User']"}), + 'locked': ('django.db.models.fields.BooleanField', [], {'default': 'False'}), + 'locked_at': ('django.db.models.fields.DateTimeField', [], {'null': 'True', 'blank': 'True'}), + 'locked_by': ('django.db.models.fields.related.ForeignKey', [], {'blank': 'True', 'related_name': "'locked_posts'", 'null': 'True', 'to': "orm['auth.User']"}), + 'offensive_flag_count': ('django.db.models.fields.SmallIntegerField', [], {'default': '0'}), + 'old_answer_id': ('django.db.models.fields.PositiveIntegerField', [], {'default': 'None', 'unique': 'True', 'null': 'True', 'blank': 'True'}), + 'old_comment_id': ('django.db.models.fields.PositiveIntegerField', [], {'default': 'None', 'unique': 'True', 'null': 'True', 'blank': 'True'}), + 'old_question_id': ('django.db.models.fields.PositiveIntegerField', [], {'default': 'None', 'unique': 'True', 'null': 'True', 'blank': 'True'}), + 'parent': ('django.db.models.fields.related.ForeignKey', [], {'blank': 'True', 'related_name': "'comments'", 'null': 'True', 'to': "orm['askbot.Post']"}), + 'points': ('django.db.models.fields.IntegerField', [], {'default': '0', 'db_column': "'score'"}), + 'post_type': ('django.db.models.fields.CharField', [], {'max_length': '255', 'db_index': 'True'}), + 'summary': ('django.db.models.fields.TextField', [], {'null': 'True'}), + 'text': ('django.db.models.fields.TextField', [], {'null': 'True'}), + 'thread': ('django.db.models.fields.related.ForeignKey', [], {'default': 'None', 'related_name': "'posts'", 'null': 'True', 'blank': 'True', 'to': "orm['askbot.Thread']"}), + 'vote_down_count': ('django.db.models.fields.IntegerField', [], {'default': '0'}), + 'vote_up_count': ('django.db.models.fields.IntegerField', [], {'default': '0'}), + 'wiki': ('django.db.models.fields.BooleanField', [], {'default': 'False'}), + 'wikified_at': ('django.db.models.fields.DateTimeField', [], {'null': 'True', 'blank': 'True'}) + }, + 'askbot.postflagreason': { + 'Meta': {'object_name': 'PostFlagReason'}, + 'added_at': ('django.db.models.fields.DateTimeField', [], {}), + 'author': ('django.db.models.fields.related.ForeignKey', [], {'to': "orm['auth.User']"}), + 'details': ('django.db.models.fields.related.ForeignKey', [], {'related_name': "'post_reject_reasons'", 'to': "orm['askbot.Post']"}), + 'id': ('django.db.models.fields.AutoField', [], {'primary_key': 'True'}), + 'title': ('django.db.models.fields.CharField', [], {'max_length': '128'}) + }, + 'askbot.postrevision': { + 'Meta': {'ordering': "('-revision',)", 'unique_together': "(('post', 'revision'),)", 'object_name': 'PostRevision'}, + 'approved': ('django.db.models.fields.BooleanField', [], {'default': 'False', 'db_index': 'True'}), + 'approved_at': ('django.db.models.fields.DateTimeField', [], {'null': 'True', 'blank': 'True'}), + 'approved_by': ('django.db.models.fields.related.ForeignKey', [], {'to': "orm['auth.User']", 'null': 'True', 'blank': 'True'}), + 'author': ('django.db.models.fields.related.ForeignKey', [], {'related_name': "'postrevisions'", 'to': "orm['auth.User']"}), + 'by_email': ('django.db.models.fields.BooleanField', [], {'default': 'False'}), + 'email_address': ('django.db.models.fields.EmailField', [], {'max_length': '75', 'null': 'True', 'blank': 'True'}), + 'id': ('django.db.models.fields.AutoField', [], {'primary_key': 'True'}), + 'is_anonymous': ('django.db.models.fields.BooleanField', [], {'default': 'False'}), + 'post': ('django.db.models.fields.related.ForeignKey', [], {'blank': 'True', 'related_name': "'revisions'", 'null': 'True', 'to': "orm['askbot.Post']"}), + 'revised_at': ('django.db.models.fields.DateTimeField', [], {}), + 'revision': ('django.db.models.fields.PositiveIntegerField', [], {}), + 'summary': ('django.db.models.fields.CharField', [], {'max_length': '300', 'blank': 'True'}), + 'tagnames': ('django.db.models.fields.CharField', [], {'default': "''", 'max_length': '125', 'blank': 'True'}), + 'text': ('django.db.models.fields.TextField', [], {}), + 'title': ('django.db.models.fields.CharField', [], {'default': "''", 'max_length': '300', 'blank': 'True'}) + }, + 'askbot.posttogroup': { + 'Meta': {'unique_together': "(('post', 'group'),)", 'object_name': 'PostToGroup', 'db_table': "'askbot_post_groups'"}, + 'group': ('django.db.models.fields.related.ForeignKey', [], {'to': "orm['askbot.Group']"}), + 'id': ('django.db.models.fields.AutoField', [], {'primary_key': 'True'}), + 'post': ('django.db.models.fields.related.ForeignKey', [], {'to': "orm['askbot.Post']"}) + }, + 'askbot.questionview': { + 'Meta': {'object_name': 'QuestionView'}, + 'id': ('django.db.models.fields.AutoField', [], {'primary_key': 'True'}), + 'question': ('django.db.models.fields.related.ForeignKey', [], {'related_name': "'viewed'", 'to': "orm['askbot.Post']"}), + 'when': ('django.db.models.fields.DateTimeField', [], {}), + 'who': ('django.db.models.fields.related.ForeignKey', [], {'related_name': "'question_views'", 'to': "orm['auth.User']"}) + }, + 'askbot.questionwidget': { + 'Meta': {'object_name': 'QuestionWidget'}, + 'group': ('django.db.models.fields.related.ForeignKey', [], {'to': "orm['askbot.Group']", 'null': 'True', 'blank': 'True'}), + 'id': ('django.db.models.fields.AutoField', [], {'primary_key': 'True'}), + 'order_by': ('django.db.models.fields.CharField', [], {'default': "'-added_at'", 'max_length': '18'}), + 'question_number': ('django.db.models.fields.PositiveIntegerField', [], {'default': '7'}), + 'search_query': ('django.db.models.fields.CharField', [], {'default': "''", 'max_length': '50', 'null': 'True', 'blank': 'True'}), + 'style': ('django.db.models.fields.TextField', [], {'default': '"\\n@import url(\'http://fonts.googleapis.com/css?family=Yanone+Kaffeesatz:300,400,700\');\\nbody {\\n overflow: hidden;\\n}\\n\\n#container {\\n width: 200px;\\n height: 350px;\\n}\\nul {\\n list-style: none;\\n padding: 5px;\\n margin: 5px;\\n}\\nli {\\n border-bottom: #CCC 1px solid;\\n padding-bottom: 5px;\\n padding-top: 5px;\\n}\\nli:last-child {\\n border: none;\\n}\\na {\\n text-decoration: none;\\n color: #464646;\\n font-family: \'Yanone Kaffeesatz\', sans-serif;\\n font-size: 15px;\\n}\\n"', 'blank': 'True'}), + 'tagnames': ('django.db.models.fields.CharField', [], {'max_length': '50'}), + 'title': ('django.db.models.fields.CharField', [], {'max_length': '100'}) + }, + 'askbot.replyaddress': { + 'Meta': {'object_name': 'ReplyAddress'}, + 'address': ('django.db.models.fields.CharField', [], {'unique': 'True', 'max_length': '25'}), + 'allowed_from_email': ('django.db.models.fields.EmailField', [], {'max_length': '150'}), + 'id': ('django.db.models.fields.AutoField', [], {'primary_key': 'True'}), + 'post': ('django.db.models.fields.related.ForeignKey', [], {'related_name': "'reply_addresses'", 'null': 'True', 'to': "orm['askbot.Post']"}), + 'reply_action': ('django.db.models.fields.CharField', [], {'default': "'auto_answer_or_comment'", 'max_length': '32'}), + 'response_post': ('django.db.models.fields.related.ForeignKey', [], {'related_name': "'edit_addresses'", 'null': 'True', 'to': "orm['askbot.Post']"}), + 'used_at': ('django.db.models.fields.DateTimeField', [], {'default': 'None', 'null': 'True'}), + 'user': ('django.db.models.fields.related.ForeignKey', [], {'to': "orm['auth.User']"}) + }, + 'askbot.repute': { + 'Meta': {'object_name': 'Repute', 'db_table': "u'repute'"}, + 'comment': ('django.db.models.fields.CharField', [], {'max_length': '128', 'null': 'True'}), + 'id': ('django.db.models.fields.AutoField', [], {'primary_key': 'True'}), + 'negative': ('django.db.models.fields.SmallIntegerField', [], {'default': '0'}), + 'positive': ('django.db.models.fields.SmallIntegerField', [], {'default': '0'}), + 'question': ('django.db.models.fields.related.ForeignKey', [], {'to': "orm['askbot.Post']", 'null': 'True', 'blank': 'True'}), + 'reputation': ('django.db.models.fields.IntegerField', [], {'default': '1'}), + 'reputation_type': ('django.db.models.fields.SmallIntegerField', [], {}), + 'reputed_at': ('django.db.models.fields.DateTimeField', [], {'default': 'datetime.datetime.now'}), + 'user': ('django.db.models.fields.related.ForeignKey', [], {'to': "orm['auth.User']"}) + }, + 'askbot.tag': { + 'Meta': {'ordering': "('-used_count', 'name')", 'object_name': 'Tag', 'db_table': "u'tag'"}, + 'created_by': ('django.db.models.fields.related.ForeignKey', [], {'related_name': "'created_tags'", 'to': "orm['auth.User']"}), + 'deleted': ('django.db.models.fields.BooleanField', [], {'default': 'False'}), + 'deleted_at': ('django.db.models.fields.DateTimeField', [], {'null': 'True', 'blank': 'True'}), + 'deleted_by': ('django.db.models.fields.related.ForeignKey', [], {'blank': 'True', 'related_name': "'deleted_tags'", 'null': 'True', 'to': "orm['auth.User']"}), + 'id': ('django.db.models.fields.AutoField', [], {'primary_key': 'True'}), + 'name': ('django.db.models.fields.CharField', [], {'unique': 'True', 'max_length': '255'}), + 'status': ('django.db.models.fields.SmallIntegerField', [], {'default': '1'}), + 'suggested_by': ('django.db.models.fields.related.ManyToManyField', [], {'related_name': "'suggested_tags'", 'symmetrical': 'False', 'to': "orm['auth.User']"}), + 'tag_wiki': ('django.db.models.fields.related.OneToOneField', [], {'related_name': "'described_tag'", 'unique': 'True', 'null': 'True', 'to': "orm['askbot.Post']"}), + 'used_count': ('django.db.models.fields.PositiveIntegerField', [], {'default': '0'}) + }, + 'askbot.thread': { + 'Meta': {'object_name': 'Thread'}, + 'accepted_answer': ('django.db.models.fields.related.ForeignKey', [], {'blank': 'True', 'related_name': "'+'", 'null': 'True', 'to': "orm['askbot.Post']"}), + 'added_at': ('django.db.models.fields.DateTimeField', [], {'default': 'datetime.datetime.now'}), + 'answer_accepted_at': ('django.db.models.fields.DateTimeField', [], {'null': 'True', 'blank': 'True'}), + 'answer_count': ('django.db.models.fields.PositiveIntegerField', [], {'default': '0'}), + 'approved': ('django.db.models.fields.BooleanField', [], {'default': 'True', 'db_index': 'True'}), + 'close_reason': ('django.db.models.fields.SmallIntegerField', [], {'null': 'True', 'blank': 'True'}), + 'closed': ('django.db.models.fields.BooleanField', [], {'default': 'False'}), + 'closed_at': ('django.db.models.fields.DateTimeField', [], {'null': 'True', 'blank': 'True'}), + 'closed_by': ('django.db.models.fields.related.ForeignKey', [], {'to': "orm['auth.User']", 'null': 'True', 'blank': 'True'}), + 'favorited_by': ('django.db.models.fields.related.ManyToManyField', [], {'related_name': "'unused_favorite_threads'", 'symmetrical': 'False', 'through': "orm['askbot.FavoriteQuestion']", 'to': "orm['auth.User']"}), + 'favourite_count': ('django.db.models.fields.PositiveIntegerField', [], {'default': '0'}), + 'followed_by': ('django.db.models.fields.related.ManyToManyField', [], {'related_name': "'followed_threads'", 'symmetrical': 'False', 'to': "orm['auth.User']"}), + 'groups': ('django.db.models.fields.related.ManyToManyField', [], {'related_name': "'group_threads'", 'symmetrical': 'False', 'through': "orm['askbot.ThreadToGroup']", 'to': "orm['askbot.Group']"}), + 'id': ('django.db.models.fields.AutoField', [], {'primary_key': 'True'}), + 'language_code': ('django.db.models.fields.CharField', [], {'default': "'en'", 'max_length': '16'}), + 'last_activity_at': ('django.db.models.fields.DateTimeField', [], {'default': 'datetime.datetime.now'}), + 'last_activity_by': ('django.db.models.fields.related.ForeignKey', [], {'related_name': "'unused_last_active_in_threads'", 'to': "orm['auth.User']"}), + 'points': ('django.db.models.fields.IntegerField', [], {'default': '0', 'db_column': "'score'"}), + 'tagnames': ('django.db.models.fields.CharField', [], {'max_length': '125'}), + 'tags': ('django.db.models.fields.related.ManyToManyField', [], {'related_name': "'threads'", 'symmetrical': 'False', 'to': "orm['askbot.Tag']"}), + 'title': ('django.db.models.fields.CharField', [], {'max_length': '300'}), + 'view_count': ('django.db.models.fields.PositiveIntegerField', [], {'default': '0'}) + }, + 'askbot.threadtogroup': { + 'Meta': {'unique_together': "(('thread', 'group'),)", 'object_name': 'ThreadToGroup', 'db_table': "'askbot_thread_groups'"}, + 'group': ('django.db.models.fields.related.ForeignKey', [], {'to': "orm['askbot.Group']"}), + 'id': ('django.db.models.fields.AutoField', [], {'primary_key': 'True'}), + 'thread': ('django.db.models.fields.related.ForeignKey', [], {'to': "orm['askbot.Thread']"}), + 'visibility': ('django.db.models.fields.SmallIntegerField', [], {'default': '1'}) + }, + 'askbot.vote': { + 'Meta': {'unique_together': "(('user', 'voted_post'),)", 'object_name': 'Vote', 'db_table': "u'vote'"}, + 'id': ('django.db.models.fields.AutoField', [], {'primary_key': 'True'}), + 'user': ('django.db.models.fields.related.ForeignKey', [], {'related_name': "'votes'", 'to': "orm['auth.User']"}), + 'vote': ('django.db.models.fields.SmallIntegerField', [], {}), + 'voted_at': ('django.db.models.fields.DateTimeField', [], {'default': 'datetime.datetime.now'}), + 'voted_post': ('django.db.models.fields.related.ForeignKey', [], {'related_name': "'votes'", 'to': "orm['askbot.Post']"}) + }, + 'auth.authusergroups': { + 'Meta': {'unique_together': "(('group', 'user'),)", 'object_name': 'AuthUserGroups', 'db_table': "'auth_user_groups'", 'managed': 'False'}, + 'group': ('django.db.models.fields.related.ForeignKey', [], {'to': "orm['auth.Group']"}), + 'id': ('django.db.models.fields.AutoField', [], {'primary_key': 'True'}), + 'user': ('django.db.models.fields.related.ForeignKey', [], {'to': "orm['auth.User']"}) + }, + 'auth.group': { + 'Meta': {'object_name': 'Group'}, + 'id': ('django.db.models.fields.AutoField', [], {'primary_key': 'True'}), + 'name': ('django.db.models.fields.CharField', [], {'unique': 'True', 'max_length': '80'}), + 'permissions': ('django.db.models.fields.related.ManyToManyField', [], {'to': "orm['auth.Permission']", 'symmetrical': 'False', 'blank': 'True'}) + }, + 'auth.permission': { + 'Meta': {'ordering': "('content_type__app_label', 'content_type__model', 'codename')", 'unique_together': "(('content_type', 'codename'),)", 'object_name': 'Permission'}, + 'codename': ('django.db.models.fields.CharField', [], {'max_length': '100'}), + 'content_type': ('django.db.models.fields.related.ForeignKey', [], {'to': "orm['contenttypes.ContentType']"}), + 'id': ('django.db.models.fields.AutoField', [], {'primary_key': 'True'}), + 'name': ('django.db.models.fields.CharField', [], {'max_length': '50'}) + }, + 'auth.user': { + 'Meta': {'object_name': 'User'}, + 'about': ('django.db.models.fields.TextField', [], {'blank': 'True'}), + 'avatar_type': ('django.db.models.fields.CharField', [], {'default': "'n'", 'max_length': '1'}), + 'bronze': ('django.db.models.fields.SmallIntegerField', [], {'default': '0'}), + 'consecutive_days_visit_count': ('django.db.models.fields.IntegerField', [], {'default': '0'}), + 'country': ('django_countries.fields.CountryField', [], {'max_length': '2', 'blank': 'True'}), + 'date_joined': ('django.db.models.fields.DateTimeField', [], {'default': 'datetime.datetime.now'}), + 'date_of_birth': ('django.db.models.fields.DateField', [], {'null': 'True', 'blank': 'True'}), + 'display_tag_filter_strategy': ('django.db.models.fields.SmallIntegerField', [], {'default': '0'}), + 'email': ('django.db.models.fields.EmailField', [], {'max_length': '75', 'blank': 'True'}), + 'email_isvalid': ('django.db.models.fields.BooleanField', [], {'default': 'False'}), + 'email_key': ('django.db.models.fields.CharField', [], {'max_length': '32', 'null': 'True'}), + 'email_signature': ('django.db.models.fields.TextField', [], {'blank': 'True'}), + 'email_tag_filter_strategy': ('django.db.models.fields.SmallIntegerField', [], {'default': '1'}), + 'first_name': ('django.db.models.fields.CharField', [], {'max_length': '30', 'blank': 'True'}), + 'gold': ('django.db.models.fields.SmallIntegerField', [], {'default': '0'}), + 'gravatar': ('django.db.models.fields.CharField', [], {'max_length': '32'}), + 'groups': ('django.db.models.fields.related.ManyToManyField', [], {'to': "orm['auth.Group']", 'symmetrical': 'False', 'blank': 'True'}), + 'id': ('django.db.models.fields.AutoField', [], {'primary_key': 'True'}), + 'ignored_tags': ('django.db.models.fields.TextField', [], {'blank': 'True'}), + 'interesting_tags': ('django.db.models.fields.TextField', [], {'blank': 'True'}), + 'is_active': ('django.db.models.fields.BooleanField', [], {'default': 'True'}), + 'is_fake': ('django.db.models.fields.BooleanField', [], {'default': 'False'}), + 'is_staff': ('django.db.models.fields.BooleanField', [], {'default': 'False'}), + 'is_superuser': ('django.db.models.fields.BooleanField', [], {'default': 'False'}), + 'languages': ('django.db.models.fields.CharField', [], {'default': "'en'", 'max_length': '128'}), + 'last_login': ('django.db.models.fields.DateTimeField', [], {'default': 'datetime.datetime.now'}), + 'last_name': ('django.db.models.fields.CharField', [], {'max_length': '30', 'blank': 'True'}), + 'last_seen': ('django.db.models.fields.DateTimeField', [], {'default': 'datetime.datetime.now'}), + 'location': ('django.db.models.fields.CharField', [], {'max_length': '100', 'blank': 'True'}), + 'new_response_count': ('django.db.models.fields.IntegerField', [], {'default': '0'}), + 'password': ('django.db.models.fields.CharField', [], {'max_length': '128'}), + 'questions_per_page': ('django.db.models.fields.SmallIntegerField', [], {'default': '10'}), + 'real_name': ('django.db.models.fields.CharField', [], {'max_length': '100', 'blank': 'True'}), + 'reputation': ('django.db.models.fields.PositiveIntegerField', [], {'default': '1'}), + 'seen_response_count': ('django.db.models.fields.IntegerField', [], {'default': '0'}), + 'show_country': ('django.db.models.fields.BooleanField', [], {'default': 'False'}), + 'show_marked_tags': ('django.db.models.fields.BooleanField', [], {'default': 'True'}), + 'silver': ('django.db.models.fields.SmallIntegerField', [], {'default': '0'}), + 'status': ('django.db.models.fields.CharField', [], {'default': "'w'", 'max_length': '2'}), + 'subscribed_tags': ('django.db.models.fields.TextField', [], {'blank': 'True'}), + 'user_permissions': ('django.db.models.fields.related.ManyToManyField', [], {'to': "orm['auth.Permission']", 'symmetrical': 'False', 'blank': 'True'}), + 'username': ('django.db.models.fields.CharField', [], {'unique': 'True', 'max_length': '255'}), + 'website': ('django.db.models.fields.URLField', [], {'max_length': '200', 'blank': 'True'}) + }, + 'contenttypes.contenttype': { + 'Meta': {'ordering': "('name',)", 'unique_together': "(('app_label', 'model'),)", 'object_name': 'ContentType', 'db_table': "'django_content_type'"}, + 'app_label': ('django.db.models.fields.CharField', [], {'max_length': '100'}), + 'id': ('django.db.models.fields.AutoField', [], {'primary_key': 'True'}), + 'model': ('django.db.models.fields.CharField', [], {'max_length': '100'}), + 'name': ('django.db.models.fields.CharField', [], {'max_length': '100'}) + } + } + + complete_apps = ['askbot'] diff --git a/askbot/models/post.py b/askbot/models/post.py index 2b616718..a029143a 100644 --- a/askbot/models/post.py +++ b/askbot/models/post.py @@ -214,6 +214,7 @@ class PostManager(BaseQuerySetManager): added_at = added_at, wiki = wiki, text = text, + language_code = thread.language_code #.html field is denormalized by the save() call ) @@ -371,6 +372,7 @@ class Post(models.Model): html = models.TextField(null=True)#html rendition of the latest revision text = models.TextField(null=True)#denormalized copy of latest revision + language_code = models.CharField(max_length=16, default=django_settings.LANGUAGE_CODE) # Denormalised data summary = models.TextField(null=True) diff --git a/askbot/search/postgresql/__init__.py b/askbot/search/postgresql/__init__.py index 4e359cc1..3edef65d 100644 --- a/askbot/search/postgresql/__init__.py +++ b/askbot/search/postgresql/__init__.py @@ -3,6 +3,26 @@ from django.db import connection from django.conf import settings as django_settings from django.utils.translation import get_language +#mapping of "django" language names to postgres +LANGUAGE_NAMES = { + 'da': 'danish', + 'nl': 'dutch', + 'en': 'english', + 'fi': 'finnish', + 'fr': 'french', + 'de': 'german', + 'hu': 'hungarian', + 'it': 'italian', + 'ja': 'japanese', + 'nb': 'norwegian', + 'pt': 'portugese', + 'ro': 'romanian', + 'ru': 'russian', + 'es': 'spanish', + 'sv': 'swedish', + 'tr': 'turkish' +} + def setup_full_text_search(script_path): """using postgresql database connection, installs the plsql language, if necessary @@ -37,17 +57,21 @@ def run_full_text_search(query_set, query_text, text_search_vector_name): rank_clause = 'ts_rank(' + table_name + \ '.' + text_search_vector_name + \ - ', plainto_tsquery(%s))' + ', plainto_tsquery(%s, %s))' where_clause = table_name + '.' + \ text_search_vector_name + \ - ' @@ plainto_tsquery(%s)' + ' @@ plainto_tsquery(%s, %s)' + + language_code = get_language() if getattr(django_settings, 'ASKBOT_MULTILINGUAL', True): - where_clause += " AND language_code='" + get_language() + "'" + where_clause += " AND " + table_name + \ + '.' + "language_code='" + language_code + "'" search_query = '&'.join(query_text.split())#apply "AND" operator - extra_params = (search_query,) + language_name = LANGUAGE_NAMES.get(language_code, 'english') + extra_params = (language_name, search_query,) extra_kwargs = { 'select': {'relevance': rank_clause}, 'where': [where_clause,], diff --git a/askbot/search/postgresql/thread_and_post_models_01022013.plsql b/askbot/search/postgresql/thread_and_post_models_01022013.plsql new file mode 100644 index 00000000..8e57ed1b --- /dev/null +++ b/askbot/search/postgresql/thread_and_post_models_01022013.plsql @@ -0,0 +1,289 @@ +/* function testing for existence of a column in a table + if table does not exists, function will return "false" */ +CREATE OR REPLACE FUNCTION column_exists(colname text, tablename text) +RETURNS boolean AS +$$ +DECLARE + q text; + onerow record; +BEGIN + + q = 'SELECT attname FROM pg_attribute WHERE attrelid = ( SELECT oid FROM pg_class WHERE relname = '''||tablename||''') AND attname = '''||colname||''''; + + FOR onerow IN EXECUTE q LOOP + RETURN true; + END LOOP; + + RETURN false; +END; +$$ LANGUAGE plpgsql; + +/* function adding tsvector column to table if it does not exists */ +CREATE OR REPLACE FUNCTION add_tsvector_column(colname text, tablename text) +RETURNS boolean AS +$$ +DECLARE + q text; +BEGIN + IF NOT column_exists(colname, tablename) THEN + q = 'ALTER TABLE ' || tablename || ' ADD COLUMN ' || colname || ' tsvector'; + EXECUTE q; + RETURN true; + ELSE + q = 'UPDATE ' || tablename || ' SET ' || colname || '=NULL'; + EXECUTE q; + RETURN false; + END IF; +END; +$$ LANGUAGE plpgsql; + +/* aggregate function that concatenates tsvectors */ +CREATE OR REPLACE FUNCTION tsv_add(tsv1 tsvector, tsv2 tsvector) +RETURNS tsvector AS +$$ +BEGIN + RETURN tsv1 || tsv2; +END; +$$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION setup_aggregates() RETURNS boolean AS +$$ +DECLARE + onerow record; +BEGIN + FOR onerow IN SELECT * FROM pg_proc WHERE proname = 'concat_tsvectors' AND proisagg LOOP + DROP AGGREGATE concat_tsvectors(tsvector); + END LOOP; + CREATE AGGREGATE concat_tsvectors ( + BASETYPE = tsvector, + SFUNC = tsv_add, + STYPE = tsvector, + INITCOND = '' + ); + RETURN true; +END; +$$ LANGUAGE plpgsql; + +SELECT setup_aggregates(); + +/* language name resolution function. todo: move languace conv to python code */ +DROP FUNCTION IF EXISTS get_language_name_from_code(lang_code text); +CREATE OR REPLACE FUNCTION get_language_name_from_code(lang_code text) +RETURNS regconfig AS +$$ +BEGIN + IF lang_code = 'en' THEN + RETURN 'english'; + ELSIF lang_code = 'da' THEN + RETURN 'danish'; + ELSIF lang_code = 'nl' THEN + RETURN 'danish'; + ELSIF lang_code = 'fi' THEN + RETURN 'finnish'; + ELSIF lang_code = 'fr' THEN + RETURN 'french'; + ELSIF lang_code = 'de' THEN + RETURN 'german'; + ELSIF lang_code = 'hu' THEN + RETURN 'hungarian'; + ELSIF lang_code = 'it' THEN + RETURN 'italian'; + ELSIF lang_code = 'nb' THEN + RETURN 'norwegian'; + ELSIF lang_code = 'pt' THEN + RETURN 'portugese'; + ELSIF lang_code = 'ro' THEN + RETURN 'romanian'; + ELSIF lang_code = 'ru' THEN + RETURN 'russian'; + ELSIF lang_code = 'es' THEN + RETURN 'spanish'; + ELSIF lang_code = 'sv' THEN + RETURN 'swedish'; + ELSIF lang_code = 'tr' THEN + RETURN 'turkish'; + ELSIF lang_code = 'ja' THEN + RETURN 'japanese'; + END IF; + RETURN 'english'; +END; +$$ LANGUAGE plpgsql; + +/* calculates text search vector for the individual thread row +DOES not include question body post, answers or comments */ +DROP FUNCTION IF EXISTS get_thread_tsv(title text, tagnames text); +CREATE OR REPLACE FUNCTION get_thread_tsv(title text, tagnames text, lang_code text) +RETURNS tsvector AS +$$ +DECLARE + lang_name regconfig; +BEGIN + lang_name = get_language_name_from_code(lang_code); + /* todo add weight depending on votes */ + RETURN setweight(to_tsvector(lang_name, coalesce(title, '')), 'A') || + setweight(to_tsvector(lang_name, coalesce(tagnames, '')), 'A'); +END; +$$ LANGUAGE plpgsql; + +/* calculates text seanch vector for the individual question row */ +DROP FUNCTION IF EXISTS get_post_tsv(text text, post_type text); +CREATE OR REPLACE FUNCTION get_post_tsv(text text, post_type text, lang_code text) +RETURNS tsvector AS +$$ +DECLARE + lang_name regconfig; +BEGIN + /* todo adjust weights to reflect votes */ + lang_name = get_language_name_from_code(lang_code); + IF post_type='question' THEN + RETURN setweight(to_tsvector(lang_name, coalesce(text, '')), 'B'); + ELSIF post_type='answer' THEN + /* todo reflect whether the answer acepted or has many points */ + RETURN setweight(to_tsvector(lang_name, coalesce(text, '')), 'C'); + ELSIF post_type='comment' THEN + RETURN setweight(to_tsvector(lang_name, coalesce(text, '')), 'D'); + ELSE + RETURN to_tsvector(''); + END IF; +END; +$$ LANGUAGE plpgsql; + +/* calculates text search vector for the question body part by thread id +here we extract question title and the text by thread_id and then +calculate the text search vector. In the future question +title will be moved to the askbot_thread table and this function +will be simpler. +*/ +CREATE OR REPLACE FUNCTION get_thread_question_tsv(thread_id integer) +RETURNS tsvector AS +$$ +DECLARE + query text; + onerow record; +BEGIN + query = 'SELECT text, language_code FROM askbot_post WHERE thread_id=' || thread_id || + ' AND post_type=''question'' AND deleted=false'; + FOR onerow in EXECUTE query LOOP + RETURN get_post_tsv(onerow.text, 'question', onerow.language_code); + END LOOP; + RETURN to_tsvector(''); +END; +$$ LANGUAGE plpgsql; + +DROP FUNCTION IF EXISTS get_dependent_comments_tsv(object_id integer, tablename text); +CREATE OR REPLACE FUNCTION get_dependent_comments_tsv(parent_id integer) +RETURNS tsvector AS +$$ +DECLARE + query text; + onerow record; +BEGIN + query = 'SELECT concat_tsvectors(text_search_vector) FROM askbot_post' || + ' WHERE parent_id=' || parent_id || + ' AND post_type=''comment'' AND deleted=false'; + FOR onerow IN EXECUTE query LOOP + RETURN onerow.concat_tsvectors; + END LOOP; + RETURN to_tsvector(''); +END; +$$ LANGUAGE plpgsql; + +DROP FUNCTION IF EXISTS get_dependent_answers_tsv(question_id integer); +CREATE OR REPLACE FUNCTION get_dependent_answers_tsv(thread_id integer) +RETURNS tsvector AS +$$ +DECLARE + query text; + onerow record; +BEGIN + query = 'SELECT concat_tsvectors(text_search_vector) ' || + 'FROM askbot_post WHERE thread_id = ' || thread_id || + ' AND deleted=false'; + FOR onerow IN EXECUTE query LOOP + RETURN onerow.concat_tsvectors; + END LOOP; + RETURN to_tsvector(''); +END; +$$ LANGUAGE plpgsql; + +DROP TRIGGER IF EXISTS thread_search_vector_update_trigger on askbot_thread; +DROP TRIGGER IF EXISTS thread_search_vector_insert_trigger on askbot_thread; +DROP TRIGGER IF EXISTS post_search_vector_update_trigger on askbot_post; + +/* create tsvector columns in the content tables + need to isolate these into own transactions, b/c of a weird mix + of triggers/update and alter table statements +*/ +SELECT add_tsvector_column('text_search_vector', 'askbot_thread'); +COMMIT; +BEGIN; +SELECT add_tsvector_column('text_search_vector', 'askbot_post'); +COMMIT; +BEGIN; +SELECT add_tsvector_column('title_search_vector', 'askbot_thread'); +COMMIT; +BEGIN; + +/* one trigger per table for tsv updates */ + +/* set up update triggers */ +CREATE OR REPLACE FUNCTION thread_update_trigger() RETURNS trigger AS +$$ +DECLARE + title_tsv tsvector; +BEGIN + title_tsv = get_thread_tsv(new.title, new.tagnames, new.language_code); + new.title_search_vector = title_tsv; + new.text_search_vector = title_tsv || + get_thread_question_tsv(new.id) || + get_dependent_answers_tsv(new.id); + RETURN new; +END; +$$ LANGUAGE plpgsql; +CREATE TRIGGER thread_search_vector_update_trigger +BEFORE UPDATE ON askbot_thread FOR EACH ROW EXECUTE PROCEDURE thread_update_trigger(); + +CREATE OR REPLACE FUNCTION thread_insert_trigger() RETURNS trigger AS +$$ +BEGIN + new.text_search_vector = get_thread_tsv(new.title, new.tagnames, new.language_code); + RETURN new; +END; +$$ LANGUAGE plpgsql; +CREATE TRIGGER thread_search_vector_insert_trigger +BEFORE INSERT ON askbot_thread FOR EACH ROW EXECUTE PROCEDURE thread_insert_trigger(); + +/* post trigger */ +CREATE OR REPLACE FUNCTION post_trigger() RETURNS trigger AS +$$ +BEGIN + IF new.post_type = 'question' THEN + new.text_search_vector = get_post_tsv(new.text, 'question', new.language_code) || + get_dependent_comments_tsv(new.id); + ELSIF new.post_type = 'answer' THEN + new.text_search_vector = get_post_tsv(new.text, 'answer', new.language_code) || + get_dependent_comments_tsv(new.id); + ELSIF new.post_type = 'comment' THEN + new.text_search_vector = get_post_tsv(new.text, 'comment', new.language_code); + END IF; + UPDATE askbot_thread SET id=new.thread_id WHERE id=new.thread_id; + return new; +END; +$$ LANGUAGE plpgsql; +CREATE TRIGGER post_search_vector_update_trigger +BEFORE INSERT OR UPDATE ON askbot_post FOR EACH ROW EXECUTE PROCEDURE post_trigger(); + +/* populate tsvectors with data */ +-- post tsvectors +UPDATE askbot_post set text_search_vector = get_post_tsv(text, post_type, language_code) WHERE post_type IN ('answer', 'comment', 'question'); +UPDATE askbot_post as q SET text_search_vector = text_search_vector || + get_dependent_comments_tsv(q.id) WHERE post_type IN ('question', 'answer'); + +--thread tsvector +UPDATE askbot_thread SET text_search_vector = get_thread_tsv(title, tagnames, language_code); +UPDATE askbot_thread as t SET text_search_vector = text_search_vector || + get_dependent_answers_tsv(t.id) || + get_thread_question_tsv(t.id); +UPDATE askbot_thread SET title_search_vector = get_thread_tsv(title, tagnames, language_code); +DROP INDEX IF EXISTS askbot_search_idx; +CREATE INDEX askbot_search_idx ON askbot_thread USING gin(text_search_vector); |