Connexion
"Rendering Doom in a database is obviously a bad idea," Lukas Vogel writes in a lengthy blog post explaining how exactly he managed to render Doom using an SQL database.
OK, that's not entirely accurate. The SQLDoom project uses a small Python client to handle input and output, drive the game's timing, and display each frame to the screen. Behind that, a series of CedarDB tables tracks the game geometry and state, while about 1,300 lines of SQL queries spread across 89 common table expressions implement the game logic and generate 35 bitmap framebuffers per second.
In this, SQLDoom is a major improvement over Vogel's previous DoomQL project, which last year set out to build "a multiplayer Doom-like shooter entirely in SQL." Unfortunately, that effort ended up with raycasting-based, grayscale ASCII graphics that were more akin to the simplistic 90-degree-angled maps of Wolfenstein 3D. The newer SQLDoom, on the other hand, generates full-color 640x480 frames that look like they could have come from the original Doom executable.
SQL Server on Azure Local is generally available. Review disconnected deployment requirements, separate licensing, and recovery planning before adopting it.
The post Microsoft Makes SQL Server on Azure Local Generally Available appeared first on TechRepublic.
Attackers are exploiting CVE-2026-48842 against unpatched Roundcube servers months after a fix was released, raising urgency for organizations to update.
The post Attackers Target Unpatched Roundcube Servers With CVE-2026-48842 appeared first on TechRepublic.
Vous le savez, je suis un grand défenseur des flux RSS . C'est d'ailleurs pour ça que le mien est complet !! Je dois être le dernier à faire ça, alors, abonnez-vous (c'est gratuit) car ça me permet de continuer à faire ce que j'aime et ce que vous aimez aussi apparemment, puisque vous êtes de plus en plus nombreux à venir ici !
Mais bon, bref, si je vous parle de ça aujourd'hui, c'est parce que j'ai découvert RSSMonster, un lecteur de flux RSS que vous installez sur votre machine, et qui a la particularité de rapprocher tout seul les articles qui parlent du même truc et qui leur met une note.
J'ai de la lecture...
La qualité d'écriture compte pour moitié, le ton pour un quart, et le dernier quart mesure à quel point le texte est promotionnel, si bien qu'un publireportage descendra tout seul. J'ai trouvé ça plutôt malin. Et les articles qui racontent le même "fait" sont regroupés dans un Event, donc vous lisez l'actu qu'une fois sans vous fader tous les copycats.
Et le plus cool, c'est que ce tri tourne sur votre ordi, sans avoir besoin de la moindre clé d'API. Chez Miniflux ou FreshRSS, la liste officielle des fonctionnalités ne mentionne rien de tel. Quant à NewsBlur, celui-ci apprend à masquer ou surligner les articles selon ce que vous lui dites aimer, ce qui n'est pas la même chose que rapprocher deux actus pour les dédupliquer. Dans le même genre, je vous avais quand même déjà parlé d' un lecteur RSS qui s'y met aussi.
RSSMonster est livré avec deux fichiers Docker Compose. Le premier monte l'application et un collecteur de flux au-dessus de SQLite, sans un seul modèle (c'est celui de la procédure express du README). Le second ajoute MySQL, un worker dédié à l'IA et le service d'inférence, et c'est lui qui allume tout ça.
En fait, si vous choisissiez SQLite, la concurrence des traitements optionnels sera plafonnée à un, donc, ce sera un peu plus lent. Et surtout, le profil léger tel qu'il est livré ne démarre pas le worker IA et coupe l'inférence, donc pas de regroupement ni de notes tant que vous ne les activez pas à la main. Ça ira donc pour une install perso, mais si vous voulez mettre ça sur un serveur pour le partager à tous vos potes ou dans votre entreprise, je vous conseille vraiment de choisir MySQL directement. Parce qu'après, la bascule de l'un vers l'autre a l'air plus hasardeuse.
Enfin, rassurez-vous, il y aura toujours la possibilité d'exporter un fichier OPML avec tous vos abonnements et les catégories. Par contre, ça ne transporte que votre liste de flux, pas les articles déjà collectés ni ce que vous avez lu.
C'est celui que je vous conseille pour commencer. Côté prérequis : git, Docker et Docker Compose. On commence par cloner le dépôt :
Lateo.net - Flux RSS en pagaille (pour en ajouter : @ moi) 
git clone https://github.com/pietheinstrengholt/rssmonster.git
cd rssmonster
Ensuite, faut générer deux secrets. Lancez cette commande deux fois, les résultats doivent différer :
openssl rand -hex 32
Créez un .env à la racine, lisible par vous seul :
touch .env && chmod 600 .env
JWT_SECRET=votre-premier-secret
FEVER_CREDENTIAL_SECRET=votre-second-secret
Ajoutez-y RSSMONSTER_BIND_ADDRESS=0.0.0.0 tout de suite pour y accéder depuis une autre machine, le port n'écoutant que sur la boucle locale. Et ne sautez pas les secrets, car le Compose refuse de démarrer si l'un des deux manque :
docker compose up -d
Ouvrez http://localhost:3000. Pas de compte admin par défaut, c'est le premier que vous créez qui devient l'administrateur, et notez bien vos identifiants. Ensuite, allez dans réglages puis flux, et importez ensuite votre OPML.
L'écran de connexion au premier démarrage
Si vous aviez déjà lancé le profil léger, coupez-le d'abord avec docker compose down, qui arrête les conteneurs sans toucher à vos données. N'y ajoutez surtout pas -v, car ça supprimera le volume et la base. Et MySQL démarrera vide et vous devrez réimporter votre OPML comme sur une install neuve.
Ensuite, ajoutez ces quatre valeurs dans le même .env, à côté de vos deux secrets :
DB_DATABASE=rssmonster
DB_USERNAME=rssmonster
DB_PASSWORD=un-mot-de-passe-solide
MYSQL_ROOT_PASSWORD=un-autre-mot-de-passe-solide
Et lancez l'autre fichier :
docker compose -f docker-compose.mysql.yml up -d --build
Ne paniquez pas si localhost:3000 ne répond pas dans la foulée. L'application attend que MySQL et l'inférence soient dispo, et celle-ci commence par télécharger ses trois modèles donc ça prend quelques minutes. Le contrôle de santé de l'outil lui laisse dix minutes, donc allez vous faire un ti café (dans sa couche ? nooon).
L'ajout d'un flux, sa catégorie et sa fenêtre de rattrapage
Voilà, amusez-vous bien.
Source : le dépôt GitHub de RSSMonster
JFrog, une société spécialisée dans la sécurité de la chaîne logicielle, a passé au crible les 55 vulnérabilités déposées par un seul compte GitHub. Cinquante-quatre étaient entièrement fabriquées, une seule décrivait un vrai bug.
Six d'entre elles visaient SQLite, la petite base de données embarquée qu'on retrouve dans à peu près tous les téléphones et navigateurs de la planète, avec des scores de gravité affichés jusqu'à 9,8 sur 10. Les quarante-neuf autres s'en prenaient à libraw, une bibliothèque de traitement d'images, et à un module audio pour cartes ESP32.
Les rapports ne résistent pas à une vérification. L'un s'appuie sur une fonction qui n'existe pas dans la version de SQLite qu'il prétend attaquer. Un autre cite les lignes 3555 et 3575 d'un fichier qui n'en compte que 2706.
Ces failles n'ont été bloquées à aucune étape. Elles ont atterri dans le NVD, la base de référence américaine des vulnérabilités, avec un enrichissement fourni par la CISA, l'agence fédérale de cybersécurité, qui a validé les scores critiques au passage. Red Hat a dû redescendre l'une d'elles de 10 sur 10 à 7,6.
Le formulaire public par lequel on déclare une faille ne vérifie pas sérieusement l'identité du déclarant. Aucune étape du processus n'exige de preuve de concept ni la moindre reproduction du bug. Un texte plausible suffit.
Le reste est automatique. La fiche descend dans les bases dérivées, puis dans les scanners que les entreprises font tourner sur leur propre code, et une équipe finit par chercher un correctif à un problème qui n'a jamais existé. MITRE, l'organisme qui attribue ces identifiants, a rejeté le lot le 1er août.
Le NIST, chargé d'analyser ces fiches, avait déjà plus de 27 000 vulnérabilités en attente fin 2025, et un rapport officiel de mai dernier lui reprochait un manque de planification et de décision.
Les mainteneurs de logiciels libres décrochent. Le projet curl a fermé son programme de primes début 2026, après sept ans, son taux de rapports confirmés étant passé de 15 % à moins de 5 % sous le déluge de textes générés par IA.
Daniel Stenberg, qui le maintient, a ensuite fermé le guichet aux signalements du 1er juillet au 3 août. Bref, ce qui faisait tenir le système, c'est que fabriquer un faux rapport crédible demandait du temps à quelqu'un.
Source et visuel : The Register et JFROG
Microsoft's 2026 lifecycle schedule includes major support deadlines for Windows, Office, SQL Server, SharePoint, and Azure services.
The post Microsoft’s 2026 Support Deadlines Explained: Everything You Need to Know appeared first on TechRepublic.
Un chercheur en sécurité nommé Ian Carroll s'est amusé à lâcher Claude Opus sur la billetterie de Live Nation, afin d'y trouver des failles de sécurité, et l'IA lui a carrément écrit toute la chaîne d'exploitation sans aucune aide. Lui n'a eu qu'à le lancer...
Tout démarre avec une session de fuzzing sur l'API des terminaux, fgtapi.frontgatetickets.com. Carroll repère un truc... chaque endpoint qui contient le mot "device" réclame un paramètre deviceUID, et ce paramètre ne demande aucune authentification. Il colle un simple guillemet à la fin, la requête se met à ramer, et là, signe classique, le paramètre file direct dans une requête SQL sans le moindre échappement.
Une injection SQL bien à l'ancienne (si vous voulez voir à quoi ça ressemble, j'avais déjà décortiqué le principe il y a un bail).
Sauf qu'un WAF AWS est planté devant pour bloquer ce genre de payload. Et c'est là que Claude entre en scène. L'IA pige toute seule que le pare-feu n'inspecte que la couche extérieure de la requête, et qu'il suffit de planquer l'injection dans une sous-requête imbriquée pour passer sous le radar.
Ensuite elle se fabrique un oracle booléen aveugle qui fait que selon que la condition testée est vraie ou fausse, le serveur renvoie deux réponses différentes, "MC70-023" pour vrai, "Intellitix Upload" pour faux. Vous enchaînez ensuite les questions oui/non, et vous reconstituez la base entière, caractère par caractère.
Et la base, elle est bien garnie. Plus de 500 tables dans un ensemble baptisé fgs avec dedans les emails et mots de passe du personnel, ceux des clients, les tokens de reset, les tokens d'API et les jetons OAuth encore actifs. Avec ça, Carroll précise qu'il aurait pu émettre autant de billets gratuits qu'il voulait, pour n'importe quel événement.
Mais c'est une personne pleine de sagesse (et qui ne veut pas aller en prison) alors il ne l'a pas fait. Et surtout, il a tout remonté à Live Nation. Le lendemain où il les a contactés, la boîte confirmait le déploiement d'un correctif.
Ce qui est intéressant ici, c'est que le contournement du WAF par sous-requête, et la construction de l'oracle, tout ça a été proposé par Claude, et ne vient pas d'une demande du chercheur. On avait certes, déjà vu l'IA d'Anthropic dénicher des failles dans Firefox ou éplucher du code Apple II vieux de 40 ans mais là, c'est un sacré cran plus loin, je trouve.
Merci à Ian Carroll pour le writeup détaillé .
Source : CyberSecurityNews
Learning to program in C on an online platform can provide structured learning and a certification to show along with your resume.
The post The 5 Best Online C Programming Courses for 2026 appeared first on TechRepublic.
A security flaw in the Ally WordPress plugin used on more than 400,000 sites could allow attackers to extract sensitive data without logging in.
The post Security Flaw in WordPress Plugin Puts 400,000 Websites at Risk appeared first on TechRepublic.
Microsoft’s March Patch Tuesday fixes 78 vulnerabilities, including Office preview pane flaws, an Excel Copilot data leak risk, and an AI-discovered 9.8 severity bug.
The post Patch Alert: Microsoft Fixes Nearly 80 Bugs, Including Critical Office Flaws appeared first on TechRepublic.
March 2026 TIOBE Index stays largely steady, with SQL and R swapping spots, as Paul Jansen explains why the index still relies on search engine hits.
The post TIOBE Index for March 2026: SQL and R Swap Spots as Jansen Defends Search-Based Rankings appeared first on TechRepublic.
How do I change MySQL root password under Linux, FreeBSD, OpenBSD and UNIX-like like operating system over the ssh session?
Love this? sudo share_on: Twitter - Facebook - LinkedIn - Whatsapp - Reddit
The post MySQL Change root Password Command appeared first on nixCraft.
Here is a simple shell script to notify secondary read-only replica (slave) MariaDB server health status failure via email and push notification. Master-slave data replication allows you to copy databases to multiple MariaDB servers. It is useful for backup, data recovery, load balancing, and much more. See how to configure SSL-protected MariaDB replication between a master and slave server.
Love this? sudo share_on: Twitter - Facebook - LinkedIn - Whatsapp - Reddit
The post Shell script to monitor MariaDB replication and send email alert about server health status appeared first on nixCraft.
MySQL version 8.0 is a free and open-source database system used by most web applications and sites on the Internet. Typically MySQL is part of the LAMP (Linux, Apache, MySQL, Perl/Python/PHP) stack. MySQL heavily uses popular open-source software such as WordPress, MediaWiki, and others as a database storage engine. Let us see how to install MySQL server version 8.x on Ubuntu 22.04 LTS Linux server, including settings up a new database, users and fine tuning server config.
Love this? sudo share_on: Twitter - Facebook - LinkedIn - Whatsapp - Reddit
The post How to install MySQL server on Ubuntu 22.04 LTS Linux appeared first on nixCraft.
When you have some generic functionality like anything commentable, likable, or upvotable, it’s common to use Generic Relations in Django. The problem with Generic Relations is that they create the relationships at the application level instead of the database level, and that requires a lot of database queries if you want to aggregate content that shares the generic functionality. There is another way that I will show you in this article.
I learned this technique at my first job in 2002 and then rediscovered it again with Django a few years ago. The trick is to have a generic Item model where every other autonomous model has a one-to-one relation to the Item. Moreover, the Item model has an item_type field, allowing you to recognize the backward one-to-one relationship.
Then whenever you need to have some generic categories, you link them to the Item. Whenever you create generic functionality like media gallery, comments, likes, or upvotes, you attach them to the Item. Whenever you need to work with permissions, publishing status, or workflows, you deal with the Item. Whenever you need to create a global search or trash bin, you work with the Item instances.
Let’s have a look at some code.
First, I'll create the items app with two models: the previously mentioned Item and the abstract model ItemBase with the one-to-one relation for various models to inherit:
# items/models.py
import sys
from django.db import models
from django.apps import apps
if "makemigrations" in sys.argv:
from django.utils.translation import gettext_noop as _
else:
from django.utils.translation import gettext_lazy as _
class Item(models.Model):
"""
A generic model for all autonomous models to link to.
Currently these autonomous models are available:
- content.Post
- companies.Company
- accounts.User
"""
ITEM_TYPE_CHOICES = (
("content.Post", _("Post")),
("companies.Company", _("Company")),
("accounts.User", _("User")),
)
item_type = models.CharField(
max_length=200, choices=ITEM_TYPE_CHOICES, editable=False, db_index=True
)
class Meta:
verbose_name = _("Item")
verbose_name_plural = _("Items")
def __str__(self):
content_object_title = (
str(self.content_object) if self.content_object else "BROKEN REFERENCE"
)
return (
f"{content_object_title} ({self.get_item_type_display()})"
)
@property
def content_object(self):
app_label, model_name = self.item_type.split(".")
model = apps.get_model(app_label, model_name)
return model.objects.filter(item=self).first()
class ItemBase(models.Model):
"""
An abstract model for the autonomous models that will link to the Item.
"""
item = models.OneToOneField(
Item,
verbose_name=_("Item"),
editable=False,
blank=True,
null=True,
on_delete=models.CASCADE,
related_name="%(app_label)s_%(class)s",
)
class Meta:
abstract = True
def save(self, *args, **kwargs):
if not self.item:
model = type(self)
item = Item.objects.create(
item_type=f"{model._meta.app_label}.{model.__name__}"
)
self.item = item
super().save()
def delete(self, *args, **kwargs):
if self.item:
self.item.delete()
super().delete(*args, **kwargs)Then let's create some autonomous models that will have one-to-one relations with the Item. By "autonomous models," I mean those which are enough by themselves, such as posts, companies, or accounts. Models like types, categories, tags, or likes, wouldn't be autonomous.
Second, I create the content app with the Post model. This model extends ItemBase which will create the one-to-one relation on save, and will define the item_type as content.Post:
# content/models.py
import sys
from django.contrib.auth.base_user import BaseUserManager
from django.db import models
from django.contrib.auth.models import AbstractUser
if "makemigrations" in sys.argv:
from django.utils.translation import gettext_noop as _
else:
from django.utils.translation import gettext_lazy as _
from items.models import ItemBase
class Post(ItemBase):
title = models.CharField(_("Title"), max_length=255)
slug = models.SlugField(_("Slug"), max_length=255)
content = models.TextField(_("Content"))
class Meta:
verbose_name = _("Post")
verbose_name_plural = _("Posts")
Third, I create the companies app with the Company model. This model also extends ItemBase which will create the one-to-one relation on save, and will define the item_type as companies.Company:
# companies/models.py
import sys
from django.contrib.auth.base_user import BaseUserManager
from django.db import models
from django.contrib.auth.models import AbstractUser
if "makemigrations" in sys.argv:
from django.utils.translation import gettext_noop as _
else:
from django.utils.translation import gettext_lazy as _
from items.models import ItemBase
class Company(ItemBase):
name = models.CharField(_("Name"), max_length=255)
slug = models.SlugField(_("Slug"), max_length=255)
description = models.TextField(_("Description"))
class Meta:
verbose_name = _("Company")
verbose_name_plural = _("Companies")
Fourth, I'll have a more extensive example with the accounts app containing the User model. This model extends AbstractUser from django.contrib.auth as well as ItemBase for the one-to-one relation. The item_type set at the Item model will be accounts.User:
# accounts/models.py
import sys
from django.db import models
from django.contrib.auth.base_user import BaseUserManager
from django.contrib.auth.models import AbstractUser
if "makemigrations" in sys.argv:
from django.utils.translation import gettext_noop as _
else:
from django.utils.translation import gettext_lazy as _
from items.models import ItemBase
class UserManager(BaseUserManager):
def create_user(self, username="", email="", password="", **extra_fields):
if not email:
raise ValueError("Enter an email address")
email = self.normalize_email(email)
user = self.model(username=username, email=email, **extra_fields)
user.set_password(password)
user.save(using=self._db)
return user
def create_superuser(self, username="", email="", password=""):
user = self.create_user(email=email, password=password, username=username)
user.is_superuser = True
user.is_staff = True
user.save(using=self._db)
return user
class User(AbstractUser, ItemBase):
# change username to non-editable non-required field
username = models.CharField(
_("Username"), max_length=150, editable=False, blank=True
)
# change email to unique and required field
email = models.EmailField(_("Email address"), unique=True)
bio = models.TextField(_("Bio"))
USERNAME_FIELD = "email"
REQUIRED_FIELDS = []
objects = UserManager()
I will use the Django shell to create several autonomous model instances and the related Items too:
>>> from content.models import Post
>>> from companies.models import Company
>>> from accounts.models import User
>>> from items.models import Item
>>> post = Post.objects.create(
... title="Hello, World!",
... slug="hello-world",
... content="Lorem ipsum…",
... )
>>> company = Company.objects.create(
... name="Aidas & Co",
... slug="aidas-co",
... description="Lorem ipsum…",
... )
>>> user = User.objects.create_user(
... username="aidas",
... email="aidas@example.com",
... password="jdf234oha&6sfhasdfh",
... )
>>> Item.objects.count()
3Lastly, here is an example of having posts, companies, and users in a single view. For that, we will use the Item queryset with annotations:
from django import forms
from django.db import models
from django.shortcuts import render
from django.utils.translation import gettext, gettext_lazy as _
from .models import Item
class SearchForm(forms.Form):
q = forms.CharField(label=_("Search"), required=False)
def all_items(request):
qs = Item.objects.annotate(
title=models.Case(
models.When(
item_type="content.Post",
then="content_post__title",
),
models.When(
item_type="companies.Company",
then="companies_company__name",
),
models.When(
item_type="accounts.User",
then="accounts_user__email",
),
default=models.Value(gettext("<Untitled>")),
),
description=models.Case(
models.When(
item_type="content.Post",
then="content_post__content",
),
models.When(
item_type="companies.Company",
then="companies_company__description",
),
models.When(
item_type="accounts.User",
then="accounts_user__bio",
),
default=models.Value(""),
),
)
form = SearchForm(data=request.GET, prefix="search")
if form.is_valid():
query = form.cleaned_data["q"]
if query:
qs = qs.annotate(
search=SearchVector(
"title",
"description",
)
).filter(search=query)
context = {
"queryset": qs,
"search_form": form,
}
return render(request, "items/all_items.html", context)
You can have generic functionality and still avoid multiple hits to the database by using the Item one-to-one approach instead of generic relations.
The name of the Item model can be different, and you can even have multiple such models for various purposes, for example, TaggedItem for tags only.
Do you use anything similar in your projects?
Do you see how this approach could be improved?
Let me know in the comments!
Cover picture by Pixabay
So bizarrely, I forgot my WordPress admin password, including my login name. I did not add my login details to my password manager either. The container image that I built lacking outgoing email support (SMTP) too. I got stuck. Fortunately, I have full root access to my MySQL/MariaDB server hosted at AWS EC2, and here is how to reset a forgotten WordPress password using MySQL command-line.
The post How to reset WordPress password using MySQL CLI appeared first on nixCraft.
How do I create an admin user in MySQL? How can I set up and create a superuser account in a MySQL server running on Linux or Unix-like systems? Can you provide a quick guide about making a superuser with root-like access to the MySQL or MariaDB databases?
The post How to create MySQL admin user (superuser) account appeared first on nixCraft.
How do I install MySQL server 8.0 on CentOS 8 Linux server running on Linode and AWS cloud? How do I add and set up a new MySQL user and database account on the newly created CentOS server?
The post How to install MySQL server on CentOS 8 Linux appeared first on nixCraft.
How can I install MySQL server 8.0 on Debian 10 LTS Linux server? How do I add a new MySQL user and database on the newly set up Debian server running on AWS EC2 server?
The post How to install MySQL server on Debian 10 Linux appeared first on nixCraft.
How do I install MySQL server 8.0 on Ubuntu 20.04 LTS Linux server? How can I add a new MySQL user and database on the newly set up Ubuntu server?
The post How to install MySQL server on Ubuntu 20.04 LTS Linux appeared first on nixCraft.
How do I create a new MySQL user and grant permissions in AWS RDS cloud service from the Linux command line?
The post How to create MySQL user and grant permissions in AWS RDS appeared first on nixCraft.
How do I install Adminer on Ubuntu 20.04 LTS for database management over the web?
The post How to install Adminer on Ubuntu 20.04 LTS appeared first on nixCraft.
Django ORM (Object-relational mapping) makes querying the database so intuitive, that at some point you might forget that SQL is being used in the background.
This year at the DjangoCon Europe Katie McLaughlin was giving a talk and mentioned one thing that affects the SQL query generated by Django ORM, depending on how you call the QuerySet or manager methods. This particularity is especially relevant when you are creating your QuerySets dynamically. Here it is. When you have a many-to-many relationship, and you try to filter objects by the fields of the related model, every new filter() method of a QuerySet creates a new INNER JOIN clause. I won't discuss whether that's a Django bug or a feature, but these are my observations about it.
Let's create an app with books and authors, where each book can be written by multiple authors.
# -*- coding: UTF-8 -*-
from __future__ import unicode_literals
from django.db import models
from django.utils.translation import ugettext_lazy as _
from django.utils.encoding import python_2_unicode_compatible
@python_2_unicode_compatible
class Author(models.Model):
first_name = models.CharField(_("First name"), max_length=200)
last_name = models.CharField(_("Last name"), max_length=200)
author_name = models.CharField(_("Author name"), max_length=200)
class Meta:
verbose_name = _("Author")
verbose_name_plural = _("Authors")
ordering = ("author_name",)
def __str__(self):
return self.author_name
@python_2_unicode_compatible
class Book(models.Model):
title = models.CharField(_("Title"), max_length=200)
authors = models.ManyToManyField(Author, verbose_name=_("Authors"))
publishing_date = models.DateField(_("Publishing date"), blank=True, null=True)
class Meta:
verbose_name = _("Book")
verbose_name_plural = _("Books")
ordering = ("title",)
def __str__(self):
return self.title
The similar app with sample data can be found in this repository.
With the above models, you could define the following QuerySet to select books which author is me, Aidas Bendoraitis.
queryset = Book.objects.filter(
authors__first_name='Aidas',
).filter(
authors__last_name='Bendoraitis',
)We can check what SQL query it would generate with str(queryset.query) (or queryset.query.__str__()).
The output would be something like this:
SELECT `libraryapp_book`.`id`, `libraryapp_book`.`title`, `libraryapp_book`.`publishing_date`
FROM `libraryapp_book`
INNER JOIN `libraryapp_book_authors` ON ( `libraryapp_book`.`id` = `libraryapp_book_authors`.`book_id` )
INNER JOIN `libraryapp_author` ON ( `libraryapp_book_authors`.`author_id` = `libraryapp_author`.`id` )
INNER JOIN `libraryapp_book_authors` T4 ON ( `libraryapp_book`.`id` = T4.`book_id` )
INNER JOIN `libraryapp_author` T5 ON ( T4.`author_id` = T5.`id` )
WHERE (`libraryapp_author`.`first_name` = 'Aidas' AND T5.`last_name` = 'Bendoraitis')
ORDER BY `libraryapp_book`.`title` ASC;Did you notice, that the database table libraryapp_author was attached through the libraryapp_book_authors table to the libraryapp_book table TWICE where just ONCE would be enough?
On the other hand, if you are defining query expressions in the same filter() method like this:
queryset = Book.objects.filter(
authors__first_name='Aidas',
authors__last_name='Bendoraitis',
)The generated SQL query will be much shorter and (theoretically) would perform faster:
SELECT `libraryapp_book`.`id`, `libraryapp_book`.`title`, `libraryapp_book`.`publishing_date`
FROM `libraryapp_book`
INNER JOIN `libraryapp_book_authors` ON ( `libraryapp_book`.`id` = `libraryapp_book_authors`.`book_id` )
INNER JOIN `libraryapp_author` ON ( `libraryapp_book_authors`.`author_id` = `libraryapp_author`.`id` )
WHERE (`libraryapp_author`.`first_name` = 'Aidas' AND `libraryapp_author`.`last_name` = 'Bendoraitis')
ORDER BY `libraryapp_book`.`title` ASC;The same SQL query can be achieved using the Q() objects:
queryset = Book.objects.filter(
models.Q(authors__first_name='Aidas') &
models.Q(authors__last_name='Bendoraitis')
)The Q() objects add a lot of flexibility to filters allowing to OR, AND, and negate query expressions.
So to have faster performance, when creating QuerySets dynamically, DON'T use filter() multiple times:
queryset = Book.objects.all()
if first_name:
queryset = queryset.filter(
authors__first_name=first_name,
)
if last_name:
queryset = queryset.filter(
authors__last_name=last_name,
)DO this instead:
filters = models.Q()
if first_name:
filters &= models.Q(
authors__first_name=first_name,
)
if last_name:
filters &= models.Q(
authors__last_name=last_name,
)
queryset = Book.objects.filter(filters)Here the empty Q() doesn't have any impact for the generated SQL query, so you don't need the complexity of creating a list of filters and then joining all of them with the bitwise AND operator, like this:
import operator
from django.utils.six.moves import reduce
filters = []
if first_name:
filters.append(models.Q(
authors__first_name=first_name,
))
if last_name:
filters.append(models.Q(
authors__last_name=last_name,
))
queryset = Book.objects.filter(reduce(operator.iand, filters))In DEBUG mode, you can check how long the previously executed SQL queries took by checking django.db.connection.queries:
>>> from django.db import connection
>>> connection.queries
[{'sql': 'SELECT …', 'time': '0.001'}, {'sql': 'SELECT …', 'time': '0.004'}]filter() methods, make use of Q() objects instead.str(queryset.query).django.db.connection.queries.Cover photo by Tobias Fischer.
In this article I will guide you through the steps I had to take to migrate Django projects from MySQL to PostgreSQL.
MySQL database has proven to be a good start for small and medium scale projects. It is known and used widely and has good documentation. Also there are great clients for easy management, like phpMyAdmin (web), HeidiSQL (windows), or Sequel Pro (macOS). However, in my professional life there were unfortunate moments, when databases from different projects crashed because of large queries or file system errors. Also I had database integrity errors which appeared in the MySQL databases throughout years because of different bugs at the application level.
When one thinks about scaling a project, they have to choose something more suitable. It should be something that is fast, reliable, and well supports ANSI standards of relational databases. Something that most top Django developers use. And the database of choice for most professionals happens to be PostgreSQL. PostgreSQL enables using several vendor-specific features that were not possible with MySQL, e.g. multidimensional arrays, JSON fields, key-value pair fields, special case-insensitive text fields, range fields, special indexes, full-text search, etc. For a newcomer, the best client that I know - pgAdmin (macOS, linux, windows) - might seem too complex at first, compared with MySQL clients, but as you have Django administration and handy ORM, you probably won't need to inspect the database in raw format too often.
So what does it take to migrate from MySQL to PostgreSQL? We will do that in a few steps and we will be using pgloader to help us with data migration. I learned about this tool from Louise Grandjonc, who was giving a presentation about PostgreSQL query optimization at DjangoCon Europe 2017.
One prerequisite for the migration are passing tests. You need to have functional tests to check if all pages are functioning correctly and unit tests to check at least the most critical or complex classes, methods, and functions.
Make sure that your production MySQL database migration state is up to date:
(env)$ python manage.py migrate --settings=settings.productionThen create a local copy of your production MySQL database. We are going to use it for the migration.
As I mentioned before, for the database migration we will use a tool called pgloader (version 3.4.1 or higher). This tool was programmed by Dimitri Fontaine and is available as an open source project on GitHub. You can compile the required version from the source. Or if you are using macOS, you can install it with Homebrew:
$ brew update
$ brew install pgloaderNote that PostgreSQL will also be installed as a dependency.
Unlike with MySQL, creating new database users and databases with PostgreSQL usually happen in the shell rather than in the database client.
Let's create a user and database with the same name myproject.
$ createuser --createdb --password myproject
$ createdb --username=myproject myprojectThe --createdb parameter will enable privilege to create databases. The --password parameter will offer to enter a password. The --username parameter will set the owner of the created database.
Link the project to this new PostgreSQL database in the settings, for example:
DATABASES = {
'postgresql': {
'ENGINE': 'django.db.backends.postgresql_psycopg2',
'NAME': get_secret("DATABASE_NAME"),
'USER': get_secret("DATABASE_USER"),
'PASSWORD': get_secret("DATABASE_PASSWORD"),
},
}
DATABASES['default'] = DATABASES['postgresql']Here the custom get_secret() function returns sensitive information from environment variables or a text file that is not tracked under version control. Its implementation is up to you.
Run the migrations to create tables and foreign key constraints in the new PostgreSQL database:
(env)$ python manage.py migrate --settings=settings.localThe pgloader uses configuration files with the settings defining how to deal with migrations. Let's create the configuration file myproject.load with the following content:
LOAD DATABASE
FROM mysql://mysql_username:mysql_password@localhost/mysql_dbname
INTO postgresql:///myproject
WITH truncate, data only, disable triggers, preserve index names, include no drop, reset sequences
ALTER SCHEMA 'mysql_dbname' RENAME TO 'public'
;Now it's time to copy the data:
$ pgloader myproject.loadTypically you will get a bunch of warnings about type conversions. These can probably be ignored, because the script will take its best guess how to convert data when importing. If in addition you get errors about duplicated data or tables with foreign keys to missing entries, you will need to fix the issues at MySQL database and then repeat the process. In that case, clean up the MySQL database, update your local copy, recreate PostgreSQL database with dropdb and createdb commands, run Django migrations to create the database schema, and copy the data again.
When the database is successfully migrated, we should run Django project tests and fix all PostgreSQL-specific problems in the project's code. The code running Django ORM will run smoothly, but very likely there will be issues with raw SQL, QuerySet's extra() method, and type conversions.
Typically, these are the differences that you might have to keep in mind:
String values in PostgreSQL queries are always quoted with 'single quotes'.
PostgreSQL doesn't convert types when comparing values automatically as MySQL does. If you use any raw SQL, you will need to do some casting before comparison like CAST(blog_post.id AS text) = likes_like.object_id or blog_post.id::text = likes_like.object_id. The latter double-colon syntax is not understood by MySQL, so if you want to support both databases, you will need to write vendor-specific cases for each database management system.
PostgreSQL is case-sensitive for string comparisons, so in the QuerySet filters you will need to use *__iexact lookup instead *__exact and *__icontains lookup instead of *__contains.
When ordering, convert the column to lowercase with the Lower() function:
from django.db import models
posts = Post.objects.order_by(models.Lower('title'))When using *__in lookup, make sure that the type of the listed elements matches the type of the model field. For example, you may have a Like model with generic relation, i.e. content_type and object_id fields that together combine a generic foreign key to any model instance. The object_id field is usually of a string type, but the IDs of related models might be integers, strings, or UUIDs. If you then want to get the liked Posts which primary keys are integers, you would need to convert the object_id values to integers before assigning them to the pk__in lookup in the filter:
liked_ids = map(int, Like.objects.filter(
user=request.user,
content_type=ContentType.objects.get_for_model(Post)
).values("object_id", flat=True))
liked_posts = Post.objects.filter(pk__in=liked_ids)When you are sure that the migration process is fluent and all Django tests pass, you can take your production website down, repeat the migration process locally with the latest production data, copy the migrated local database to production server, update the production code, install new dependencies, and take the website back online.
To create a database dump you can use command:
$ pg_dump --format=c --compress=9 --file=myproject.backup myprojectTo restore or create the database from dump use commands:
$ dropdb --username=pgsql myproject
$ createdb --username=myproject myproject
$ pg_restore --dbname=myproject --role=myproject --schema=public myproject.backupI might probably miss some points and there are some ways to automate the upgrade process for production, but you got the idea.
PostgreSQL is more restrictive than MySQL, but it provides greater performance, more stability, and better compliance with standards. In addition, in PostgreSQL there is a bunch of features that were not available in MySQL. If you are lucky, you can switch your project from MySQL to PostgreSQL in one day.
Cover photo by Casey Allen
How do I install Linux, nginx, MySQL, PHP (LEMP) stack on Amazon Linux AMI running on AWS?
The post Amazon Linux AMI Install Linux, nginx, MySQL, PHP (LEMP) appeared first on nixCraft.
How do I install mysql command for management Amazon RDS or Managed Databases for Amazon Lightsail on Amazon Linux AMI?
The post Amazon Linux AMI install mysql client programs for RDS appeared first on nixCraft.
How do I install a LAMP stack on Debian 10 (Buster)?
The post How to install LAMP on Debian 10 (Buster) appeared first on nixCraft.
How do I install MariaDB database servers on OpenBSD Unix operating systems?
The post OpenBSD install MariaDB database server appeared first on nixCraft.
How do I set up a LAMP stack on RHEL 8 cloud server or VPS or bare metal server? How can I install Linux, Apache, MySQL/MariaDB, PHP (LAMP) stack On RHEL (Red Hat Enterprise Linux) 8?
The post How To Install Linux, Apache, MySQL, PHP (LAMP) stack On RHEL 8 appeared first on nixCraft.
How do I install PostgreSQL relational database management on RHEL 8 using the command line? How do I install and setup PostgreSQL on RHEL 8 server using application streams?
The post How to install and setup PostgreSQL on RHEL 8 appeared first on nixCraft.
