From cec681f1b4551e3d8588cf2d22e4eae3a8c08b66 Mon Sep 17 00:00:00 2001 From: Evgeny Fadeev Date: Tue, 26 Feb 2013 19:38:21 -0300 Subject: allowed search by email address in the user search bar --- .../migrations/0164_update_postgres_user_search.py | 399 +++++++++++++++++++++ .../postgresql/user_profile_search_02262013.plsql | 102 ++++++ 2 files changed, 501 insertions(+) create mode 100644 askbot/migrations/0164_update_postgres_user_search.py create mode 100644 askbot/search/postgresql/user_profile_search_02262013.plsql diff --git a/askbot/migrations/0164_update_postgres_user_search.py b/askbot/migrations/0164_update_postgres_user_search.py new file mode 100644 index 00000000..d65c2aa0 --- /dev/null +++ b/askbot/migrations/0164_update_postgres_user_search.py @@ -0,0 +1,399 @@ +# -*- coding: utf-8 -*- +import askbot +import datetime +import os.path +from south.db import db +from south.v2 import DataMigration +from django.db import models +from askbot.search import postgresql + +class Migration(DataMigration): + + def forwards(self, orm): + "Write your forwards methods here." + db_engine_name = askbot.get_database_engine_name() + if 'postgresql_psycopg2' in db_engine_name: + script_path = os.path.join( + askbot.get_install_directory(), + 'search', + 'postgresql', + 'user_profile_search_02262013.plsql' + ) + postgresql.setup_full_text_search(script_path) + + def backwards(self, orm): + "Write your backwards methods here." + + + 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'}) + }, + '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(2013, 2, 26, 14, 41, 36, 405005)'}), + '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(2013, 2, 26, 14, 41, 36, 404683)'}), + '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'] + symmetrical = True diff --git a/askbot/search/postgresql/user_profile_search_02262013.plsql b/askbot/search/postgresql/user_profile_search_02262013.plsql new file mode 100644 index 00000000..fc4e7509 --- /dev/null +++ b/askbot/search/postgresql/user_profile_search_02262013.plsql @@ -0,0 +1,102 @@ +/* +Script depends on functions defined for general askbot full text search. +to_tsvector(), add_tsvector_column() + +calculates text search vector for the user profile +the searched fields are: +1) user name +2) user profile +3) group names - for groups to which user belongs +*/ +CREATE OR REPLACE FUNCTION get_auth_user_tsv(user_id integer) +RETURNS tsvector AS +$$ +DECLARE + group_query text; + user_query text; + onerow record; + tsv tsvector; +BEGIN + group_query = + 'SELECT user_group.name as group_name ' || + 'FROM auth_group AS user_group ' || + 'INNER JOIN auth_user_groups AS gm ' || + 'ON gm.user_id= ' || user_id || ' AND gm.group_id=user_group.id'; + + tsv = to_tsvector(''); + FOR onerow in EXECUTE group_query LOOP + tsv = tsv || to_tsvector(onerow.group_name); + END LOOP; + + user_query = 'SELECT about, username, real_name, email FROM auth_user WHERE id=' || user_id; + FOR onerow in EXECUTE user_query LOOP + tsv = tsv || + to_tsvector(onerow.username) || + to_tsvector(onerow.real_name) || + to_tsvector(onerow.email) || + to_tsvector(onerow.about); + END LOOP; + RETURN tsv; +END; +$$ LANGUAGE plpgsql; + +/* create tsvector columns in the content tables */ +SELECT add_tsvector_column('text_search_vector', 'auth_user'); + +/* populate tsvectors with data */ +UPDATE auth_user SET text_search_vector = get_auth_user_tsv(id); + +/* one trigger per table for tsv updates */ + +/* set up auth_user triggers */ +CREATE OR REPLACE FUNCTION auth_user_tsv_update_handler() +RETURNS trigger AS +$$ +BEGIN + new.text_search_vector = get_auth_user_tsv(new.id); + RETURN new; +END; +$$ LANGUAGE plpgsql; +DROP TRIGGER IF EXISTS auth_user_tsv_update_trigger ON auth_user; + +CREATE TRIGGER auth_user_tsv_update_trigger +BEFORE INSERT OR UPDATE ON auth_user +FOR EACH ROW EXECUTE PROCEDURE auth_user_tsv_update_handler(); + +/* group membership trigger - reindex users when group membership + * changes */ +CREATE OR REPLACE FUNCTION group_membership_tsv_update_handler() +RETURNS trigger AS +$$ +DECLARE + tsv tsvector; + user_query text; +BEGIN + IF (TG_OP = 'INSERT') THEN + user_query = 'UPDATE auth_user SET username=username WHERE ' || + 'id=' || new.user_id; + ELSE + user_query = 'UPDATE auth_user SET username=username WHERE ' || + 'id=' || old.user_id; + END IF; + /* just trigger the tsv update on user */ + EXECUTE user_query; + RETURN NULL; +END; +$$ LANGUAGE plpgsql; + +DROP TRIGGER IF EXISTS group_membership_tsv_update_trigger +ON auth_user_groups; + +CREATE TRIGGER group_membership_tsv_update_trigger +AFTER INSERT OR DELETE +ON auth_user_groups +FOR EACH ROW EXECUTE PROCEDURE group_membership_tsv_update_handler(); + +/* todo: whenever group name changes - also + * reindex users belonging to the group */ + +DROP INDEX IF EXISTS auth_user_search_idx; + +CREATE INDEX auth_user_search_idx ON auth_user +USING gin(text_search_vector); -- cgit v1.2.3-1-g7c22