Comments (3)
How to generate a random number in every DB engine:
- SQLite:
SELECT random()
- PostgreSQL:
SELECT random()
- MySQL/MariaDB:
SELECT RAND()
- MS SQL:
SELECT RAND()
These are not supported by DQL by default, so we may need to add a custom function that can be used with all of them https://www.doctrine-project.org/projects/doctrine-orm/en/3.0/cookbook/dql-user-defined-functions.html
This library can be used for inspiration, as it implements a bunch of them: https://github.com/beberlei/DoctrineExtensions
from shlink.
This should allow supporting RAND()
in DQL, in a way that it gets translated to the right platform function.
use Doctrine\DBAL\Platforms\PostgreSQLPlatform;
use Doctrine\DBAL\Platforms\SQLitePlatform;
use Doctrine\ORM\Query\AST\Functions\FunctionNode;
use Doctrine\ORM\Query\Parser;
use Doctrine\ORM\Query\SqlWalker;
use Doctrine\ORM\Query\TokenType;
class Rand extends FunctionNode
{
public function getSql(SqlWalker $sqlWalker): string
{
return match ($sqlWalker->getConnection()->getDatabasePlatform()::class) {
PostgreSQLPlatform::class, SQLitePlatform::class => 'random()',
default => 'RAND()',
};
}
public function parse(Parser $parser): void
{
$parser->match(TokenType::T_IDENTIFIER);
$parser->match(TokenType::T_OPEN_PARENTHESIS);
$parser->match(TokenType::T_CLOSE_PARENTHESIS);
}
}
Registered as
$entityManager->getConfiguration()->addCustomNumericFunction('RAND', Rand::class);
However, considering the insertion may happen at an entity level, it might make sense to generate the random
number in PHP code, via rand(1, 100)
.
EDIT
Actually, the kind of query needed for this to be fully efficient will require a native query, not DQL or entities, so the above is probably not needed.
INSERT INTO short_url_visits_counts(short_url_id, potential_bot, slot_id, count)
VALUES (123, false, RAND() * 100, 1)
ON DUPLICATE KEY UPDATE count = count + 1;
from shlink.
A listener to set/update counts every time a Visit is persisted:
use Doctrine\Common\EventSubscriber;
use Doctrine\DBAL\Connection;
use Doctrine\DBAL\Platforms\PostgreSQLPlatform;
use Doctrine\DBAL\Platforms\SQLitePlatform;
use Doctrine\DBAL\Platforms\SQLServerPlatform;
use Doctrine\ORM\EntityManagerInterface;
use Doctrine\ORM\Event\PreFlushEventArgs;
use Shlinkio\Shlink\Core\Visit\Entity\Visit;
$em->getEventManager()->addEventSubscriber(new class implements EventSubscriber {
public function getSubscribedEvents(): array
{
return ['preFlush'];
}
public function preFlush(PreFlushEventArgs $args): void
{
$em = $args->getObjectManager();
$entitiesToBeCreated = $em->getUnitOfWork()->getScheduledEntityInsertions();
foreach ($entitiesToBeCreated as $entity) {
$this->trackVisitCount($em, $entity);
}
}
private function trackVisitCount(EntityManagerInterface $em, object $entity): void
{
// This is not a non-orphan visit
if (!$entity instanceof Visit || $entity->shortUrl === null) {
return;
}
$visit = $entity;
// The short URL is not persisted yet
$shortUrlId = $visit->shortUrl->getId();
if ($shortUrlId === null || $shortUrlId === '') {
return;
}
$isBot = $visit->potentialBot;
$conn = $em->getConnection();
$platformClass = $conn->getDatabasePlatform();
match ($platformClass::class) {
PostgreSQLPlatform::class => $this->incrementForPostgres($conn, $shortUrlId, $isBot),
SQLitePlatform::class, SQLServerPlatform::class => $this->incrementForOthers($conn, $shortUrlId, $isBot),
default => $this->incrementForMySQL($conn, $shortUrlId, $isBot),
};
}
private function incrementForMySQL(Connection $conn, string $shortUrlId, bool $potentialBot): void
{
$this->incrementWithPreparedStatement($conn, $shortUrlId, $potentialBot, <<<QUERY
INSERT INTO short_url_visits_counts (short_url_id, potential_bot, slot_id, count)
VALUES (:short_url_id, :potential_bot, RAND() * 100, 1)
ON DUPLICATE KEY UPDATE count = count + 1;
QUERY);
}
private function incrementForPostgres(Connection $conn, string $shortUrlId, bool $potentialBot): void
{
$this->incrementWithPreparedStatement($conn, $shortUrlId, $potentialBot, <<<QUERY
INSERT INTO short_url_visits_counts (short_url_id, potential_bot, slot_id, count)
VALUES (:short_url_id, :potential_bot, random() * 100, 1)
ON CONFLICT (short_url_id, potential_bot, slot_id) DO UPDATE
SET count = count + 1;
QUERY);
}
private function incrementWithPreparedStatement(
Connection $conn,
string $shortUrlId,
bool $potentialBot,
string $query,
): void {
$statement = $conn->prepare($query);
$statement->bindValue('short_url_id', $shortUrlId);
$statement->bindValue('potential_bot', $potentialBot);
$statement->executeStatement();
}
private function incrementForOthers(Connection $conn, string $shortUrlId, bool $potentialBot): void
{
$slotId = rand(1, 100);
// For engines without a specific UPSERT syntax, do a regular locked select followed by an insert or update
$qb = $conn->createQueryBuilder();
$qb->select('id')
->from('short_url_visits_counts')
->where($qb->expr()->and(
$qb->expr()->eq('short_url_id', ':short_url_id'),
$qb->expr()->eq('potential_bot', ':potential_bot'),
$qb->expr()->eq('slot_id', ':slot_id'),
))
->setParameter('short_url_id', $shortUrlId)
->setParameter('potential_bot', $potentialBot)
->setParameter('slot_id', $slotId)
->forUpdate()
->setMaxResults(1);
$resultSet = $qb->executeQuery()->fetchOne();
$writeQb = ! $resultSet
? $conn->createQueryBuilder()
->insert('short_url_visits_counts')
->values([
'short_url_id' => ':short_url_id',
'potential_bot' => ':potential_bot',
'slot_id' => ':slot_id',
])
: $conn->createQueryBuilder()
->update('short_url_visits_counts')
->set('count', 'count + 1')
->where($qb->expr()->and(
$qb->expr()->eq('short_url_id', ':short_url_id'),
$qb->expr()->eq('potential_bot', ':potential_bot'),
$qb->expr()->eq('slot_id', ':slot_id'),
));
$writeQb->setParameter('short_url_id', $shortUrlId)
->setParameter('potential_bot', $potentialBot)
->setParameter('slot_id', $slotId)
->executeStatement();
}
});
from shlink.
Related Issues (20)
- Improve GeoLite2 file downloads when using RoadRunner HOT 1
- Auto generate a url HOT 2
- Add another shlink instances
- Support Japanese charsets HOT 9
- Improve performance when using findIfExists HOT 12
- cpanel
- A + after the short url shows the destination HOT 3
- Application error: a client-side exception has occurred (see the browser console for more information). HOT 4
- Unable to connect app.shlink.io HOT 1
- Flag to disable shared slug HOT 1
- HTTP status code per short URL HOT 1
- Allow "&" and "+" characters in custom slugs again. HOT 6
- Upgrade from Docker Image Shlink 3 to 4 failed HOT 2
- Upgrade shlink from 4.0 to 4.1 failed HOT 1
- Add support for PHP 8.4
- Add console command to edit short URLs
- Upgrade from 3.7.4 to 4.0.3: Column "api_key_id" has invalid type HOT 3
- Can't install from docker-compose.ylm : resolve : lstat /root/data: no such file or directory HOT 3
- shlink with gtm (Google Tag Manager)
- [Feature Request] Supporting IPinfo's free IP to Country ASN/ISP database HOT 2
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from shlink.