Kvnbbg.fr

Who loves creating apps.

Tag: database

python & sqlite / Enhancing Library Databases !

24/05/2024 / / 0 Comments

Quels sont les effets personnels auxquels vous tenez le plus ?

Les effets personnels auxquels je tiens le plus sont généralement ceux qui ont une valeur sentimentale ou une importance particulière dans ma vie quotidienne.

  1. Photographies et souvenirs : Les photos de famille, d’amis et des moments importants de ma vie sont précieuses car elles capturent des souvenirs irremplaçables.
  2. Objets hérités : Des objets transmis de génération en génération, comme des bijoux, des montres, ou des meubles, ont souvent une grande valeur sentimentale.
  3. Livres et documents personnels : Les journaux intimes, les lettres et les livres qui ont marqué ma vie ont une signification personnelle profonde.
  4. Appareils électroniques : Mon téléphone, mon ordinateur portable, et d’autres appareils électroniques sont essentiels pour mon travail et ma communication quotidienne.
  5. Objets de passe-temps : Les instruments de musique, les équipements sportifs ou les jeux vidéo qui correspondent à mes loisirs et passions sont également très importants pour moi.

Ces objets, bien qu’ils aient une valeur matérielle, sont souvent chéris pour les souvenirs et les émotions qu’ils évoquent.

Using Python and SQLite to manage a library database, including realistic data and tracking updates, helps streamline the organization and retrieval of important personal collections, much like preserving and valuing sentimental personal effects.

The Importance of Realistic and Diverse Data

Click here to display content from YouTube.
Learn more in YouTube’s privacy policy.

Open "UML Diagrams Full Course (Unified Modeling Language)" directly

In the digital age, libraries have evolved from mere repositories of physical books to sophisticated hubs of information, accessible at the click of a button. One of the critical aspects of managing a modern library database is ensuring that the data it contains is both realistic and diverse. This not only enhances the usability of the database but also improves the overall user experience.

The Need for Realistic and Diverse Data

Realistic data provides users with a more accurate representation of the library’s contents, which is essential for efficient search and retrieval. Diverse data, on the other hand, ensures that the library caters to a wide range of interests and scholarly needs, promoting inclusivity and broadening the scope of knowledge accessible to users.

A library database with well-structured and comprehensive data can support various functions, from basic cataloging to advanced analytics. This data helps librarians make informed decisions about acquisitions, track book popularity, and even predict future trends.

Practical Example: Creating a Realistic Library Database

Consider a library database that stores information about books and authors. A simplistic dataset might suffice for basic operations, but it often falls short in real-world applications. To illustrate this, let’s look at an SQL script designed to create and populate a realistic and diverse library database.

— Create the Authors table
CREATE TABLE IF NOT EXISTS Authors (
id INTEGER PRIMARY KEY, — Unique identifier for each author
name TEXT — Name of the author
);

— Create the Books table
CREATE TABLE IF NOT EXISTS Books (
id INTEGER PRIMARY KEY, — Unique identifier for each book
name TEXT, — Name of the book
year INTEGER, — Year the book was published
author_id INTEGER, — ID of the author from the Authors table
FOREIGN KEY (author_id) REFERENCES Authors(id) — Establishes the foreign key relationship
);

— Insert sample data into the Authors table
INSERT INTO Authors (id, name) VALUES
(1, ‘Jane Austen’),
(2, ‘Charles Dickens’),
(3, ‘Mark Twain’),
(4, ‘Virginia Woolf’),
(5, ‘George Orwell’),
(6, ‘Agatha Christie’),
(7, ‘J.K. Rowling’);

— Insert sample data into the Books table
INSERT INTO Books (id, name, year, author_id) VALUES
(1, ‘Pride and Prejudice’, 1813, 1),
(2, ‘Sense and Sensibility’, 1811, 1),
(3, ‘Great Expectations’, 1861, 2),
(4, ‘A Tale of Two Cities’, 1859, 2),
(5, ‘Adventures of Huckleberry Finn’, 1884, 3),
(6, ‘The Adventures of Tom Sawyer’, 1876, 3),
(7, ‘Mrs Dalloway’, 1925, 4),
(8, ‘To the Lighthouse’, 1927, 4),
(9, ‘1984’, 1949, 5),
(10, ‘Animal Farm’, 1945, 5),
(11, ‘Murder on the Orient Express’, 1934, 6),
(12, ‘The Murder of Roger Ackroyd’, 1926, 6),
(13, ‘Harry Potter and the Philosopher\’s Stone’, 1997, 7),
(14, ‘Harry Potter and the Chamber of Secrets’, 1998, 7);

— Select the book name, year, and author’s name for all books,
— ordering the results first by the author’s name alphabetically,
— and then by the year of publication in ascending order.
SELECT
Books.name AS book_name,
Books.year,
Authors.name AS author
FROM
Books
JOIN
Authors ON Books.author_id = Authors.id
ORDER BY
Authors.name ASC,
Books.year ASC;

Benefits of a Realistic Dataset in Python !

# In python

import sqlite3
from datetime import datetime

# Function to initialize the database and create tables
def initialize_database():
conn = sqlite3.connect(‘library.db’)
cursor = conn.cursor()

cursor.execute(”’
CREATE TABLE IF NOT EXISTS Authors (
id INTEGER PRIMARY KEY,
name TEXT
)
”’)

cursor.execute(”’
CREATE TABLE IF NOT EXISTS Books (
id INTEGER PRIMARY KEY,
name TEXT,
year INTEGER,
author_id INTEGER,
FOREIGN KEY (author_id) REFERENCES Authors(id)
)
”’)

cursor.execute(”’
CREATE TABLE IF NOT EXISTS Updates (
id INTEGER PRIMARY KEY AUTOINCREMENT,
timestamp TEXT,
description TEXT
)
”’)

conn.commit()
conn.close()

# Function to insert sample data
def insert_sample_data():
conn = sqlite3.connect(‘library.db’)
cursor = conn.cursor()

authors = [
(1, ‘Jane Austen’),
(2, ‘Charles Dickens’),
(3, ‘Mark Twain’),
(4, ‘Virginia Woolf’),
(5, ‘George Orwell’),
(6, ‘Agatha Christie’),
(7, ‘J.K. Rowling’)
]

books = [
(1, ‘Pride and Prejudice’, 1813, 1),
(2, ‘Sense and Sensibility’, 1811, 1),
(3, ‘Great Expectations’, 1861, 2),
(4, ‘A Tale of Two Cities’, 1859, 2),
(5, ‘Adventures of Huckleberry Finn’, 1884, 3),
(6, ‘The Adventures of Tom Sawyer’, 1876, 3),
(7, ‘Mrs Dalloway’, 1925, 4),
(8, ‘To the Lighthouse’, 1927, 4),
(9, ‘1984’, 1949, 5),
(10, ‘Animal Farm’, 1945, 5),
(11, ‘Murder on the Orient Express’, 1934, 6),
(12, ‘The Murder of Roger Ackroyd’, 1926, 6),
(13, ‘Harry Potter and the Philosopher\’s Stone’, 1997, 7),
(14, ‘Harry Potter and the Chamber of Secrets’, 1998, 7)
]

cursor.executemany(‘INSERT OR IGNORE INTO Authors (id, name) VALUES (?, ?)’, authors)
cursor.executemany(‘INSERT OR IGNORE INTO Books (id, name, year, author_id) VALUES (?, ?, ?, ?)’, books)

log_update(cursor, “Inserted sample data into Authors and Books tables.”)

conn.commit()
conn.close()

# Function to log updates
def log_update(cursor, description):
timestamp = datetime.now().strftime(‘%Y-%m-%d %H:%M:%S’)
cursor.execute(‘INSERT INTO Updates (timestamp, description) VALUES (?, ?)’, (timestamp, description))

# Function to fetch updates
def fetch_updates():
conn = sqlite3.connect(‘library.db’)
cursor = conn.cursor()

cursor.execute(‘SELECT * FROM Updates ORDER BY timestamp DESC’)
updates = cursor.fetchall()

conn.close()
return updates

# Initialize the database and insert sample data
initialize_database()
insert_sample_data()

# Fetch and print the updates
updates = fetch_updates()
print(“Database Updates:”)
for update in updates:
print(f”ID: {update[0]}, Timestamp: {update[1]}, Description: {update[2]}”)

By including a diverse range of authors and books, the database becomes a more accurate reflection of what one might find in a real library. This not only helps in better cataloging and retrieval but also enhances user engagement. Users are more likely to find what they are looking for when the database mirrors the diversity of real-world literature.

black flat screen computer monitor

the integration of backend technologies like PHP and MariaDB with front-end libraries such as Three.js

28/03/2024 / / 0 Comments

Hosting a PHP and Three.js Application on Fly.io

1. Introduction to Fly.io Deployment

Fly.io is a modern platform that enables developers to run their applications close to users worldwide, leveraging the benefits of containers. It supports a wide array of languages and frameworks, including PHP, making it an excellent choice for deploying web applications that require global distribution.

2. Containerization with Docker

The first step involves containerizing your PHP application using Docker. This process ensures your application runs consistently across different environments. Here’s a basic Dockerfile for setting up a PHP environment:

FROM php:8.0-apache
WORKDIR /var/www/html
COPY . .
EXPOSE 80
CMD ["apache2-foreground"]

3. Deploying on Fly.io

Once your application is containerized, deploying it on Fly.io involves a few commands using the Fly.io CLI. Ensure you’ve logged in (fly auth login) and created an app (fly apps create). Then, deploy your application using fly deploy. The CLI tool handles the rest, ensuring your app is deployed globally.

Building the Educational App

Algorithm Overview

The core of our educational app revolves around explaining database technologies through interactive examples and tutorials. Users can select a topic (MariaDB vs. MySQL), and the app dynamically presents relevant information, tutorials, and visualizations using Three.js.

Sample Algorithm Block: Display Database Comparison

<?php
// Sample PHP code to fetch and display database information
function fetchDatabaseInfo($dbType) {
    // Placeholder for fetching data. Assume $databaseInfo is fetched here.
    $databaseInfo = [
        'description' => 'Description of ' . $dbType,
        'links' => [
            'official' => 'https://officialsite.com/' . $dbType,
            'tutorial' => 'https://tutorialsite.com/' . $dbType,
        ],
        'primaryKeys' => 'Explanation of primary keys in ' . $dbType,
        'robustness' => 'How ' . $dbType . ' ensures robustness',
        'commonBugs' => 'Common bugs found in ' . $dbType . ' and how to avoid them',
    ];
    return $databaseInfo;
}

// Example usage
$dbType = 'MariaDB';
$databaseInfo = fetchDatabaseInfo($dbType);
echo json_encode($databaseInfo);

Integrating Three.js

Three.js is utilized to create interactive 3D visualizations for each database concept. For instance, showing a 3D model of database tables and relationships can help explain primary keys and data integrity visually.

<!DOCTYPE html>
<html>
<head>
    <title>3D Database Concepts</title>
    <script src="https://threejs.org/build/three.js"></script>
</head>
<body>
    <script>
        // Three.js script to visualize database concepts
        // Placeholder for Three.js code to render 3D models
    </script>
</body>
</html>

Enriching the App with Resources

Educational Content and Links

  • MariaDB: Explore the features and advantages of MariaDB, a fork of MySQL, offering enhanced performance, security, and open-source freedom. MariaDB Official
  • Transact-SQL: Learn about Microsoft’s extension to SQL, Transact-SQL (T-SQL), used in SQL Server. T-SQL adds procedural programming, local variables, and support for error handling. T-SQL Documentation
  • Oracle Database: Dive into the world of Oracle Database, a multi-model database management system known for its scalability, reliability, and comprehensive features. Oracle Documentation
  • NoSQL: Understand the principles behind NoSQL databases, designed for specific data models and have flexible schemas for building modern applications. NoSQL Database Explained
  • MySQL: Get started with MySQL, the world’s most popular open-source database, known for its reliability, ease of use, and performance. MySQL Official
  • SQL Server: Explore SQL Server, Microsoft’s enterprise database solution, offering robust data management and business intelligence capabilities. SQL Server Documentation
  • Complex SQL Queries: Enhance your skills in understanding complex SQL queries with step-by-step explanations and examples. Complex SQL Tutorial
  • Primary Keys (Clé Primaire): Learn about the importance of primary keys in database design and how they ensure data uniqueness and integrity. Primary Keys Explained
  • Building Robust Applications: Tips and best practices for developing robust database applications that are secure, efficient, and reliable. Database Design Best Practices
  • Common Database Bugs: Identify common bugs in database applications and learn how to avoid them, enhancing the stability and performance of your applications. Common Database Errors

Conclusion

Creating an application that marries PHP and Three.js for educational purposes serves as a potent tool for junior developers eager to learn about database technologies. Hosting it on Fly.io ensures global reach and performance.

web development, php, java

Angular for the frontend, Python and Flask for the backend, and a SQL database to anchor our data.

25/03/2024 / / 0 Comments

Setting Up the Project 🛠️

Angular Setup

ng new task-app
cd task-app
ng serve

Python & Flask Backend

python -m venv venv
source venv/bin/activate

With our environment consecrated, Flask stands ready to breathe life into our server-side logic.

Integrating SQL Database 🗃️

The heart of our application—a SQL database—is next. Here, Flask SQLAlchemy weaves together our data model:

from flask import Flask, request, jsonify
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///tasks.db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy(app)

class Task(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(100), nullable=False)

@app.before_first_request
def create_tables():
    db.create_all()

@app.route('/task', methods=['POST'])
def add_task():
    data = request.get_json()
    new_task = Task(title=data['title'])
    db.session.add(new_task)
    db.session.commit()
    return jsonify({'message': 'Task created successfully'}), 201

@app.route('/tasks', methods=['GET'])
def get_tasks():
    tasks_query = Task.query.all()
    tasks = [{"id": task.id, "title": task.title} for task in tasks_query]
    return jsonify({'tasks': tasks})

@app.route('/task/<int:id>', methods=['PUT'])
def update_task(id):
    data = request.get_json()
    task = Task.query.filter_by(id=id).first()
    if not task:
        return jsonify({'message': 'Task not found'}), 404
    task.title = data['title']
    db.session.commit()
    return jsonify({'message': 'Task updated successfully'})

@app.route('/task/<int:id>', methods=['DELETE'])
def delete_task(id):
    task = Task.query.filter_by(id=id).first()
    if not task:
        return jsonify({'message': 'Task not found'}), 404
    db.session.delete(task)
    db.session.commit()
    return jsonify({'message': 'Task deleted successfully'})

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

CSRF Protection with Angular

Angular, with its built-in CSRF defenses, ensures our application is fortified against cross-site request forgery:

import { HttpClient } from '@angular/common/http';
import { Injectable } from '@angular/core';

@Injectable({
  providedIn: 'root'
})
export class TaskService {
  constructor(private http: HttpClient) {}

  addTask(taskData: any) {
    return this.http.post('/api/tasks', taskData);
  }
}

This service acts as a conduit between our Angular frontend and Flask backend, ensuring tasks are managed with grace and efficiency.

CSRF Configuration in Flask

Ensuring Flask is prepared to parry CSRF attempts is paramount. Flask-WTF lends its strength to our defenses:

import os
from flask import Flask, render_template_string
from flask_wtf.csrf import CSRFProtect

app = Flask(__name__)
app.config['SECRET_KEY'] = os.getenv('SECRET_KEY', os.urandom(24))

csrf = CSRFProtect(app)

@app.route('/', methods=['GET', 'POST'])
def index():
    return render_template_string("...")

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

With CSRFProtect invoked, our application is shielded, allowing us to focus on crafting user experiences without fear.

Database Management

22/03/2024 / / 0 Comments

At its core, a database is a systematic collection of data that supports the storage, manipulation, and retrieval of information. Databases can be relational (SQL) or non-relational (NoSQL), each serving different needs based on the structure and scalability requirements of your application.

from Sololearn

Best Practices :

  • Data Sanitization : Always sanitize user inputs to prevent SQL injection attacks. This involves escaping potentially harmful characters before they’re processed by the database.
  • Privilege : Operate your database under the principle of least privilege, meaning users and applications should have only the minimum permissions necessary to perform their tasks.

Secure Requests and Authorization

HTTPS : Use HTTPS (Hypertext Transfer Protocol Secure) for all communications between the client and server. HTTPS encrypts data in transit, preventing attackers from intercepting sensitive information.

Authorization Tokens : Implement token-based authorization, such as JWT (JSON Web Tokens), to manage user sessions. Tokens should be securely stored (in HTTP-only cookies) and validated with each request to verify a user’s identity and permissions.

Click here to display content from YouTube.
Learn more in YouTube’s privacy policy.

Open "Does Storing JWT’s In HTTP Only Cookies Stop XSS Attacks" directly

Safeguarding Your Environment

Application and server configurations play a significant role in security. A misconfigured server or application can serve as an entry point for attackers.

Secure Configuration Practices:

  • Update Regularly: Keep your server software and dependencies up to date to protect against known vulnerabilities.
  • Minimal Exposure: Disable unnecessary services and features on your server to reduce potential attack surfaces.
  • Environment Variables: Store sensitive configuration options such as API keys and database credentials in environment variables, not in your codebase.

A Shield Against Cross-Site Request Forgery

Cross-Site Request Forgery (CSRF) is an attack that tricks the victim into submitting a malicious request. It exploits the trust that a site has in a user’s browser.

How CSRF Protection Works :

  1. Token Generation : The server generates a unique, unpredictable token and sends it to the client’s browser as part of a form or an AJAX request.
  2. Token Validation : When the client submits the form or makes a request, it must include this token. The server then validates the token before processing the request.
  3. Token Invalidation : Tokens are invalidated after being used or after a certain period, requiring new tokens for subsequent requests.

Implementing CSRF tokens in forms and AJAX requests is a standard practice in modern web frameworks. This mechanism ensures that every state-changing request originates from your application, not an attacker.

Without Ajax (simplified)

Conclusion : Keeping Data Secure

Remember, security isn’t a one-time task but a continuous process of learning, implementing, and evolving with the digital landscape.

Docker simplifies the complexities of the deployment process

22/03/2024 / / 0 Comments

Why Docker ?

  • Consistency and Isolation.
  • Microservices Architecture : Easily manage parts of an application independently.
  • Scalability.

Unit Testing

Unit testing is crucial for verifying the functionality of individual parts of an application. It helps in detecting early bugs and ensuring code quality.

  • unittest : Python’s built-in library unittest offers a way to automate testing for Python applications.
  • PHPUnit : PHPUnit is a programmer-oriented testing framework for PHP.

MySQL Workbench

MySQL Workbench is a unified visual tool for developers. It provides data modeling, SQL development, and comprehensive administration tools for server configuration, user administration, and much more.

Why MySQL Workbench ?

  • Simplify database design and maintenance.
  • Optimize database performance.
  • Manage your database server efficiently.

Web Hosting

Choosing the right web hosting service is crucial for the success of any web application. Always Data or back4app offer robust hosting solutions with support for Docker containers, making them an excellent choice for modern web applications.

Managing Databases with phpMyAdmin

phpMyAdmin is a free software tool written in PHP, intended to handle the administration of MySQL. It offers a convenient visual interface for database management, making tasks like creating databases, tables, and executing SQL statements easier.

Practical Scenario: Project Management System

Imagine developing a web-based project management system with features to manage projects and users. Here’s how the discussed tools come into play:

  • Development: Use Docker to containerize the application, ensuring it can be easily moved from development to production.
  • Testing: Implement unit tests in Python or PHP to ensure each functionality works as expected.
  • Database: Design your database schema using MySQL Workbench. The schema includes two main tables: projects and users.
    • Projects Table: Stores information about various projects.
    • Users Table: Contains user information, including roles that determine access levels within the system.
  • Deployment: Choose a web hosting service like Always Data or Back4app that supports Docker to deploy your application.
  • Database Management: Use phpMyAdmin for database administration tasks, such as managing user permissions and querying data.

Selection in Database: Example Scenario

To retrieve information about a specific project and its assigned users, you would execute a SQL query, possibly through phpMyAdmin. Here’s an example:

Conclusion

The ecosystem surrounding web application development, from Docker containers and unit testing to database management and web hosting, provides developers with a powerful toolkit for building, testing, and deploying robust applications.

© 2024 Kvnbbg.fr

Theme by Anders Norén — Up ↑

Verified by MonsterInsights