Add fulltext search support

9 files changed, 35 insertions, 21 deletions

diff --git a/app/models.py b/app/models.py
index e9cd65c..c9b9f5a 100644
--- a/app/models.py
+++ b/app/models.py
@@ -15,18 +15,29 @@
 # along with this program.  If not, see <https://www.gnu.org/licenses/>.
 
 
+import enum, datetime
+
+from app import app, gravatar
+from urllib.parse import urlparse
+
 from flask import Flask, url_for
-from flask_sqlalchemy import SQLAlchemy
+from flask_sqlalchemy import SQLAlchemy, BaseQuery
 from flask_migrate import Migrate
-from urllib.parse import urlparse
-from app import app, gravatar
-from sqlalchemy.orm import validates
 from flask_user import login_required, UserManager, UserMixin, SQLAlchemyAdapter
-import enum, datetime
+from sqlalchemy.orm import validates
+from sqlalchemy_searchable import SearchQueryMixin
+from sqlalchemy_utils.types import TSVectorType
+from sqlalchemy_searchable import make_searchable
+
 
 # Initialise database
 db = SQLAlchemy(app)
 migrate = Migrate(app, db)
+make_searchable(db.metadata)
+
+
+class ArticleQuery(BaseQuery, SearchQueryMixin):
+    pass
 
 
 class UserRank(enum.Enum):
@@ -246,7 +257,7 @@ class PackageType(enum.Enum):
 class PackagePropertyKey(enum.Enum):
 	name          = "Name"
 	title         = "Title"
-	shortDesc     = "Short Description"
+	short_desc     = "Short Description"
 	desc          = "Description"
 	type          = "Type"
 	license       = "License"
@@ -343,19 +354,22 @@ class Dependency(db.Model):
 		return retval
 
 
-
 class Package(db.Model):
+	query_class  = ArticleQuery
+
 	id           = db.Column(db.Integer, primary_key=True)
 
 	# Basic details
 	author_id    = db.Column(db.Integer, db.ForeignKey("user.id"))
 	name         = db.Column(db.String(100), nullable=False)
-	title        = db.Column(db.String(100), nullable=False)
-	shortDesc    = db.Column(db.String(200), nullable=False)
-	desc         = db.Column(db.Text, nullable=True)
+	title        = db.Column(db.Unicode(100), nullable=False)
+	short_desc   = db.Column(db.Unicode(200), nullable=False)
+	desc         = db.Column(db.UnicodeText, nullable=True)
 	type         = db.Column(db.Enum(PackageType))
 	created_at   = db.Column(db.DateTime, nullable=False, default=datetime.datetime.utcnow)
 
+	search_vector = db.Column(TSVectorType("title", "short_desc", "desc"))
+
 	license_id   = db.Column(db.Integer, db.ForeignKey("license.id"), nullable=False, default=1)
 	license      = db.relationship("License", foreign_keys=[license_id])
 	media_license_id = db.Column(db.Integer, db.ForeignKey("license.id"), nullable=False, default=1)
@@ -409,7 +423,7 @@ class Package(db.Model):
 			"name": self.name,
 			"title": self.title,
 			"author": self.author.display_name,
-			"short_description": self.shortDesc,
+			"short_description": self.short_desc,
 			"type": self.type.toName(),
 			"release": self.getDownloadRelease(protonum).id if self.getDownloadRelease(protonum) is not None else None,
 			"thumbnail": (base_url + tnurl) if tnurl is not None else None,
@@ -422,7 +436,7 @@ class Package(db.Model):
 			"author": self.author.display_name,
 			"name": self.name,
 			"title": self.title,
-			"short_description": self.shortDesc,
+			"short_description": self.short_desc,
 			"desc": self.desc,
 			"type": self.type.toName(),
 			"created_at": self.created_at,
diff --git a/app/public/static/package_create.js b/app/public/static/package_create.js
index a115b14..a278953 100644
--- a/app/public/static/package_create.js
+++ b/app/public/static/package_create.js
@@ -35,10 +35,10 @@ $(function() {
 				setField("#repo", result.repo || repoURL);
 				setField("#issueTracker", result.issueTracker);
 				setField("#desc", result.description);
-				setField("#shortDesc", result.short_description);
+				setField("#short_desc", result.short_description);
 				setField("#harddep_str", result.depends);
 				setField("#softdep_str", result.optional_depends);
-				setField("#shortDesc", result.short_description);
+				setField("#short_desc", result.short_description);
 				setField("#forums", result.forumId);
 				if (result.type && result.type.length > 2) {
 					$("#type").val(result.type);
diff --git a/app/public/static/package_edit.js b/app/public/static/package_edit.js
index b997b83..dfd9de0 100644
--- a/app/public/static/package_edit.js
+++ b/app/public/static/package_edit.js
@@ -41,7 +41,7 @@ $(function() {
 		It's obvious that this adds something to Minetest,
 		there's no need to use phrases such as \"adds X to the game\".`
 
-	$("#shortDesc").on("change paste keyup", function() {
+	$("#short_desc").on("change paste keyup", function() {
 		var val = $(this).val().toLowerCase();
 		if (val.indexOf("minetest") >= 0 || val.indexOf("mod") >= 0 ||
 				val.indexOf("modpack") >= 0 || val.indexOf("mod pack") >= 0) {
diff --git a/app/querybuilder.py b/app/querybuilder.py
index e524bde..408d95d 100644
--- a/app/querybuilder.py
+++ b/app/querybuilder.py
@@ -40,7 +40,7 @@ class QueryBuilder:
 			query = query.filter(Package.type.in_(self.types))
 
 		if self.search:
-			query = query.filter(Package.title.ilike('%' + self.search + '%'))
+			query = query.search(self.search)
 
 		if self.random:
 			query = query.order_by(func.random())
diff --git a/app/templates/macros/packagegridtile.html b/app/templates/macros/packagegridtile.html
index a0f21f4..9f70c1c 100644
--- a/app/templates/macros/packagegridtile.html
+++ b/app/templates/macros/packagegridtile.html
@@ -12,7 +12,7 @@
 			</h3>
 
 			<p>
-				{{ package.shortDesc }}
+				{{ package.short_desc }}
 			</p>
 
 
diff --git a/app/templates/packages/create_edit.html b/app/templates/packages/create_edit.html
index d0e8174..9a11531 100644
--- a/app/templates/packages/create_edit.html
+++ b/app/templates/packages/create_edit.html
@@ -49,7 +49,7 @@
 			{{ render_field(form.title, class_="pkg_meta col-sm-7") }}
 			{{ render_field(form.name, class_="pkg_meta col-sm-3") }}
 			</div>
-			{{ render_field(form.shortDesc, class_="pkg_meta") }}
+			{{ render_field(form.short_desc, class_="pkg_meta") }}
 			{{ render_multiselect_field(form.tags, class_="pkg_meta") }}
 			<div class="pkg_meta row">
 				{{ render_field(form.license, class_="not_txp col-sm-6") }}
diff --git a/app/templates/packages/editrequest_create_edit.html b/app/templates/packages/editrequest_create_edit.html
index c83bade..7a9052c 100644
--- a/app/templates/packages/editrequest_create_edit.html
+++ b/app/templates/packages/editrequest_create_edit.html
@@ -17,7 +17,7 @@
 		{{ render_field(form.type) }}
 		{{ render_field(form.name) }}
 		{{ render_field(form.title) }}
-		{{ render_field(form.shortDesc) }}
+		{{ render_field(form.short_desc) }}
 		{{ render_field(form.desc) }}
 		{{ render_multiselect_field(form.tags) }}
 
diff --git a/app/templates/packages/view.html b/app/templates/packages/view.html
index f4ef811..b725dbe 100644
--- a/app/templates/packages/view.html
+++ b/app/templates/packages/view.html
@@ -19,7 +19,7 @@
 			</h1>
 
 			<p class="lead">
-				{{ package.shortDesc }}
+				{{ package.short_desc }}
 			</p>
 
 			<div class="row" style="margin-top: 2rem;">
diff --git a/app/views/packages/packages.py b/app/views/packages/packages.py
index 0fe4dc1..80c5a97 100644
--- a/app/views/packages/packages.py
+++ b/app/views/packages/packages.py
@@ -171,7 +171,7 @@ def package_download_page(package):
 class PackageForm(FlaskForm):
 	name          = StringField("Name (Technical)", [InputRequired(), Length(1, 20), Regexp("^[a-z0-9_]", 0, "Lower case letters (a-z), digits (0-9), and underscores (_) only")])
 	title         = StringField("Title (Human-readable)", [InputRequired(), Length(3, 50)])
-	shortDesc     = StringField("Short Description (Plaintext)", [InputRequired(), Length(1,200)])
+	short_desc     = StringField("Short Description (Plaintext)", [InputRequired(), Length(1,200)])
 	desc          = TextAreaField("Long Description (Markdown)", [Optional(), Length(0,10000)])
 	type          = SelectField("Type", [InputRequired()], choices=PackageType.choices(), coerce=PackageType.coerce, default=PackageType.MOD)
 	license       = QuerySelectField("License", [InputRequired()], query_factory=lambda: License.query.order_by(db.asc(License.name)), get_pk=lambda a: a.id, get_label=lambda a: a.name)