Creating a comprehensive database structure and Flask application for a Search Engine project involves designing a robust database schema, implementing advanced search functionality, and creating a user-friendly interface using Bootstrap 5. Below is a detailed breakdown of the project.

Database Structure

The database will include the following tables:

  • Users (for user authentication and search history)
  • SearchQueries (for storing search queries)
  • SearchResults (for storing search results)
  • WebPages (for storing web page metadata)
  • Keywords (for storing keywords extracted from web pages)
  • Links (for storing links between web pages)

SQL Schema


CREATE TABLE Users (
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) UNIQUE NOT NULL,
password_hash VARCHAR(255) NOT NULL,
search_history TEXT
);
CREATE TABLE SearchQueries (
id INT PRIMARY KEY AUTO_INCREMENT,
query TEXT NOT NULL,
user_id INT,
FOREIGN KEY (user_id) REFERENCES Users(id)
);
CREATE TABLE SearchResults (
id INT PRIMARY KEY AUTO_INCREMENT,
query_id INT,
web_page_id INT,
rank INT,
FOREIGN KEY (query_id) REFERENCES SearchQueries(id),
FOREIGN KEY (web_page_id) REFERENCES WebPages(id)
);
CREATE TABLE WebPages (
id INT PRIMARY KEY AUTO_INCREMENT,
url VARCHAR(255) UNIQUE NOT NULL,
title VARCHAR(255),
content TEXT,
keywords TEXT
);
CREATE TABLE Keywords (
id INT PRIMARY KEY AUTO_INCREMENT,
keyword VARCHAR(50),
web_page_id INT,
FOREIGN KEY (web_page_id) REFERENCES WebPages(id)
);
CREATE TABLE Links (
id INT PRIMARY KEY AUTO_INCREMENT,
source_web_page_id INT,
target_web_page_id INT,
FOREIGN KEY (source_web_page_id) REFERENCES WebPages(id),
FOREIGN KEY (target_web_page_id) REFERENCES WebPages(id)
);

Flask Application Structure

The Flask application will have the following structure:


search_engine/

├── app/
│ ├── __init__.py
│ ├── models.py
│ ├── routes/
│ │ ├── auth.py
│ │ ├── search.py
│ │ ├── results.py
│ │ └── web_pages.py
│ ├── templates/
│ │ ├── base.html
│ │ ├── auth/
│ │ │ ├── login.html
│ │ │ └── register.html
│ │ ├── search/
│ │ │ ├── search.html
│ │ │ └── results.html
│ │ └── web_pages/
│ │ ├── web_page.html
│ │ └── links.html
│ └── static/
│ ├── css/
│ │ └── styles.css
│ └── js/
│ └── scripts.js

├── config.py
├── requirements.txt
└── run.py

Flask Code

1. app/__init__.py


from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from flask_login import LoginManager
from flask_bootstrap import Bootstrap5
db = SQLAlchemy()
login_manager = LoginManager()
def create_app():
app = Flask(__name__)
app.config.from_pyfile('config.py')
db.init_app(app)
login_manager.init_app(app)
from .routes.auth import auth_bp
from .routes.search import search_bp
from .routes.results import results_bp
from .routes.web_pages import web_pages_bp
app.register_blueprint(auth_bp)
app.register_blueprint(search_bp)
app.register_blueprint(results_bp)
app.register_blueprint(web_pages_bp)
return app

2. app/config.py


import os
class Config:
SECRET_KEY = os.environ.get('SECRET_KEY') or 'a_secret_key'
SQLALCHEMY_DATABASE_URI = os.environ.get('DATABASE_URL') or 'sqlite:///search_engine.db'
SQLALCHEMY_TRACK_MODIFICATIONS = False

3. run.py


from app import create_app
app = create_app()
if __name__ == '__main__':
app.run(debug=True)

4. Flask Routes

app/routes/auth.py


from flask import Blueprint, render_template, redirect, url_for, flash, request
from flask_login import login_user, logout_user, login_required
from .models import User
from . import db
auth_bp = Blueprint('auth', __name__)
@auth_bp.route('/login', methods=['GET', 'POST'])
def login():
if request.method == 'POST':
username = request.form['username']
password = request.form['password']
user = User.query.filter_by(username=username).first()
if user and user.verify_password(password):
login_user(user)
return redirect(url_for('search.search'))
flash('Invalid username or password')
return render_template('auth/login.html')
@auth_bp.route('/logout')
@login_required
def logout():
logout_user()
return redirect(url_for('auth.login'))
@auth_bp.route('/register', methods=['GET', 'POST'])
def register():
if request.method == 'POST':
username = request.form['username']
password = request.form['password']
new_user = User(username=username)
new_user.set_password(password)
db.session.add(new_user)
db.session.commit()
flash('Your account has been created! You can now log in')
return redirect(url_for('auth.login'))
return render_template('auth/register.html')

4. app/routes/search.py


from flask import Blueprint, render_template, request
from .models import SearchQuery, WebPage
from . import db
search_bp = Blueprint('search', __name__)
@search_bp.route('/search', methods=['GET', 'POST'])
def search():
if request.method == 'POST':
query = request.form['query']
new_query = SearchQuery(query=query)
db.session.add(new_query)
db.session.commit()
results = WebPage.query.filter(WebPage.content.contains(query)).all()
return render_template('search/results.html', results=results, query=query)
return render_template('search/search.html')

5. app/routes/results.py


from flask import Blueprint, render_template
from .models import SearchResult
results_bp = Blueprint('results', __name__)
@results_bp.route('/results/<int:query_id>')
def results(query_id):
results = SearchResult.query.filter_by(query_id=query_id).all()
return render_template('search/results.html', results=results)

6. app/routes/web_pages.py


from flask import Blueprint, render_template
from .models import WebPage
web_pages_bp = Blueprint('web_pages', __name__)
@web_pages_bp.route('/web_page/<int:web_page_id>')
def web_page(web_page_id):
page = WebPage.query.get_or_404(web_page_id)
return render_template('web_pages/web_page.html', page=page)

7. app/templates/base.html


<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css">
<link rel="stylesheet" href="{{ url_for('static', filename='css/styles.css') }}">
<title>{% block title %}Search Engine{% endblock %}</title>
</head>
<body>
<nav class="navbar navbar-expand-lg navbar-light bg-light">
<a class="navbar-brand" href="{{ url_for('search.search') }}">Search Engine</a>
<div class="collapse navbar-collapse">
<ul class="navbar-nav ml-auto">
{% if current_user.is_authenticated %}
<li class="nav-item">
<a class="nav-link" href="{{ url_for('auth.logout') }}">Logout</a>
</li>
{% else %}
<li class="nav-item">
<a class="nav-link" href="{{ url_for('auth.login') }}">Login</a>
</li>
<li class="nav-item">
<a class="nav-link" href="{{ url_for('auth.register') }}">Register</a>
</li>
{% endif %}
</ul>
</div>
</nav>
<div class="container mt-4">
{% with messages = get_flashed_messages() %}
{% if messages %}
<div class="alert alert-warning">
{% for message in messages %}
<p>{{ message }}</p>
{% endfor %}
</div>
{% endif %}
{% endwith %}
{% block content %}{% endblock %}
</div>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/js/bootstrap.bundle.min.js"></script>
</body>
</html>

8. app/templates/auth/login.html


{% extends 'base.html' %}
{% block title %}Login{% endblock %}
{% block content %}
<h2>Login</h2>
<form method="POST">
<div class="mb-3">
<label for="username" class="form-label">Username</label>
<input type="text" class="form-control" id="username" name="username" required>
</div>
<div class="mb-3">
<label for="password" class="form-label">Password</label>
<input type="password" class="form-control" id="password" name="password" required>
</div>
<button type="submit" class="btn btn-primary">Login</button>
</form>
{% endblock %}

9. app/templates/auth/register.html


{% extends 'base.html' %}
{% block title %}Register{% endblock %}
{% block content %}
<h2>Register</h2>
<form method="POST">
<div class="mb-3">
<label for="username" class="form-label">Username</label>
<input type="text" class="form-control" id="username" name="username" required>
</div>
<div class="mb-3">
<label for="email" class="form-label">Email</label>
<input type="email" class="form-control" id="email" name="email" required>
</div>
<div class="mb-3">
<label for="password" class="form-label">Password</label>
<input type="password" class="form-control" id="password" name="password" required>
</div>
<button type="submit" class="btn btn-primary">Register</button>
</form>
{% endblock %}

10. app/templates/search/search.html


{% extends 'base.html' %}
{% block title %}Search{% endblock %}
{% block content %}
<h2>Search</h2>
<form method="POST">
<div class="mb-3">
<label for="query" class="form-label">Search Query</label>
<input type="text" class="form-control" id="query" name="query" required>
</div>
<button type="submit" class="btn btn-primary">Search</button>
</form>
{% endblock %}

11. app/templates/search/results.html


{% extends 'base.html' %}
{% block title %}Search Results{% endblock %}
{% block content %}
<h2>Results for "{{ query }}"</h2>
<ul class="list-group">
{% for result in results %}
<li class="list-group-item">
<a href="{{ result.url }}">{{ result.title }}</a>
<p>{{ result.content[:150] }}...</p>
</li>
{% else %}
<li class="list-group-item">No results found.</li>
{% endfor %}
</ul>
{% endblock %}

12. app/templates/web_pages/web_page.html


{% extends 'base.html' %}
{% block title %}{{ page.title }}{% endblock %}
{% block content %}
<h2>{{ page.title }}</h2>
<p><a href="{{ page.url }}">{{ page.url }}</a></p>
<p>{{ page.content }}</p>
{% endblock %}

13. app/templates/web_pages/links.html


{% extends 'base.html' %}
{% block title %}Links{% endblock %}
{% block content %}
<h2>Links</h2>
<ul class="list-group">
{% for link in page.links %}
<li class="list-group-item">
<a href="{{ link.target_web_page.url }}">{{ link.target_web_page.title }}</a>
</li>
{% endfor %}
</ul>
{% endblock %}

14. app/templates/base.html


import os
class Config:
SECRET_KEY = os.environ.get('SECRET_KEY') or 'a_default_secret_key'
SQLALCHEMY_DATABASE_URI = os.environ.get('DATABASE_URL') or 'sqlite:///search_engine.db'
SQLALCHEMY_TRACK_MODIFICATIONS = False

15. requirements.txt


Flask==2.0.2
Flask-SQLAlchemy==2.5.1
Flask-Login==0.5.0
Flask-Bootstrap==3.3.7.1

16. run.py


from app import create_app
app = create_app()
if __name__ == '__main__':
app.run(debug=True)

Conclusion

This structure provides a solid foundation for an advanced search engine project using Flask and Bootstrap 5. You can expand upon this by adding features such as pagination, advanced search filters, user profiles, and more. Make sure to test each component thoroughly and consider implementing error handling and logging for a production-ready application.