Generování čísla objednávky

BayerMeister

Zdar!

Jak by se s Nette/Database(/Table) dal vložit nový záznam s ID, který vychází z posledního předchozího záznamu?
Chtěl bych totiž číslovat objednávky takto: (int) YY####, kde YY je 17 pro tento rok.
S čistým MySQL bych to udělal na 2 dotazy s pomocnou proměnnou, uzavřené do transakce. Takto:

start transaction;
select @nextId := if(isnull(max(id)), 170000, max(id)) + 1 from objednavka where id > 170000 && id < 180000;
insert into objednavka (id, ...další sloupce) values(@nextId, ...další hodnoty);
commit;

A následně bych to ještě přes @nextId selectnul.
Zkusil jsem to pomocí context->query(…). Sice to vloží, ale je to špinavý (nejen MySQL specific) a po transakci se mi nedaří získat poslední vložené ID. Zde je kód:

$sql = "start transaction;
select @nextId := if(isnull(max(id)), 170000, max(id)) + 1 from objednavka where id > 170000 && id < 180000;
insert into objednavka (id, data) values(@nextId, 1234);
commit;";
$result = $this->database->query($sql);
$id = $this->database->getInsertId('objednavka');
\Tracy\Debugger::barDump($result);
\Tracy\Debugger::barDump($id);

V $resultu je ResultSet [… result ⇒ NULL, resultKey => –1, results ⇒ NULL …], v $id je „0“

Vůbec se nechci držet tohoto postupu. Důležitý je jen cíl: mít klíče v uvedeném tvaru.
Přemýšlel jsem i o tom, že bych měl PK normální AUTO_INCREMENT, ale mechanizmus který by objednávkám pak přiděloval hezké číslo do jiného unique sloupce by vyžadoval stejnou atomičnost.

Možnost ošklivá je risknout vložit bez synchronizace a chytat nějakou SQL Exception. Dokud lítá Exception, úsilí opakovat (tj. znov zjistit nejvyšší dosavadní klíč…).
To je ale asi tak hezký, jako psát v Nette čisté MySQL ručně.

Předem díky! Richard

P.S.: to je mi jasný, že to někde na fóru už bude, ale… nenašel jsem to :-)

PetrHH

Nepomohlo by toto? https://api.nette.org/…Builder.html

GEpic

Co takhle si nastavit auto_increment na 170000? Pak můžeš číslování úplně vypustit a MySQL se ti o něj postará samo a každá další objednávka bude aktuální auto_increment + 1 (to si řídí samo MySQL)

Editoval GEpic (21. 2. 2017 16:40)

fizzy

Najdes poslednu objednavku s vs >= 170000, ak neexistuje, ulozis 170000, inak vs podlednej objednavky + 1

Martk

Další varianta

$count = $this->database->query("SELECT COUNT(id) WHERE id >= ?", date('y') . '0000')
$id = date('y') . sprintf("%04d", $count + 1);

CZechBoY

@Martk Tak právě přijdeš o tu atomičnost.

Martk

@CZechBoY U nastavení auto_increment musíš zase ohlídat začátkem roku, aby se změnil auto_increment o +1 v místě YY nebo na tohle existuje něco v mysql?

CZechBoY

@Martk Je to tak – musí se pohlídat začátek roku. Vyřešit se to dá třeba cronem na 31.12. 23:59:59 a změnit autoincrement hodnotu… nebo nějakej trigger, kterej podle času vygeneruje další hodnotu.

Editoval CZechBoY (21. 2. 2017 21:31)

fizzy

atomickost mozes osetrit nejakym mutexom :D

este je moznost spravit si zvlast tabulku kde budes ukladat posledny pouzity vs a pouzijes pesimistic locking

GEpic

Přesně, cron může přenastavit auto_increment každý rok, dávej akorát pozor aby si neměl za rok více než 10000 objednávek s tímto řešením, byl by problém.

Editoval GEpic (22. 2. 2017 0:38)

greeny

Tohle bych řešil nějakým sloupečkem identifier, zneužívat na tohle auto increment nebo doufat že nebudu mít 1e5 objednávek je trochu hack. Ten identifier pak můžeš generovat třeba i po insertu nějakým cronem.

BayerMeister

Lidi díky za reakce. Nějak mi nedošla žádná notifikace na mail…

Nastavovat auto_incremet z jiného skriptu, který se spustí na CRON (nikdy 2× současně), by mělo nevýhodu, že se Vážu na hosting dalším nastavením (ale s tím se lze smířit).

Bohužel jsem zatajil další drobnost, která se v tomto návrhu ukazuje podstatnou:
Formát by obsahoval další dvojčíslí závislé na jiné okolnosti, řekněme, předmětu nákupu: YYXX####, takže by paralelně vznikaly objednávky 1701#### a 1702####
Po nějakou dobu bude ale existovat pouze předmět „01“, takže lze dočasně postup s CRONem použít.

Takže dočasně dobrý, ale chtělo by to nějaký best-practice na tenhle typ atomických getmax-increment-insert-select.

BayerMeister

Nechápal a tak testoval jsem, jak se vlastně ->query() chová, když se jí podá více příkazů oddělených středníkem.
Třeba 2 selecty: do DB se odešlou oba, návratová hodnota ale zohledňuje první dotaz a ne poslední, jak jsem čekal.

No a proměnné @MySQL mají platnost pro úřipojení… tak mi došlo, že to celé, co tady prasím do jednoho příkazu, je zbytečné, že ta proměnná mi přežije i do dalšího volání ->query()

A tak mám:

$this->database->query('start transaction');
$this->database->query('select @next_id := if(isnull(max(id)), 170000, max(id)) + 1 from objednavka where id > 170000 && id < 180000');
$this->database->query('insert into objednavka (id, data) values(@next_id, 1234)');
$this->database->query('commit');
$result = $this->database->query('select @next_id as next_id');

…a $result vrací, co jsem potřeboval – vložené ID.

Další rozšíření o různé řady objednávek je jednoduché – mění se konstanty 170000/180000 (dno/strop) na 17010000/17020000.

Pořád prasárna, ale už jsme blíž :-)