Bitcoin Pump'n'Dump Song by Peter Thiel

I know one song by Peter Thiel called Bitcoin, it goes like this:

Buy, pump, hype, dump
buy, pump, hype, dump
now I am rich
and you are dumb.
I tricked you again
now go vote for Trump.

Work on this site in progress

After about a year offline, the site was just re-born from the former platform.
The current solution still has a few annoyances, but I'm fixing it :)

The most important thing is so sort out the pages that I brought from the former site.
That needs me to implement auto-generated directory indexes in the JBake platform this uses.
Until then, the blog below will contain all pages.

Another thing is the duplicated titles. That will go away soon.

Texy! is sexxy!

Texy! is text-to-HTML formatter and converter library. It allows you to write structured documents without knowledge or using of HTML language. You write documents in humane easy-to-read plain text format and Texy! converts it to structurally and valid (X)HTML code.

Texy! is one of the most complex formatters. Its possibilities covers images, links (anchors), nested lists, tables and has full support for CSS.

Paragraphs

Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Curabitur turpis enim, placerat tincidunt, tincidunt ac, fringilla et, mauris. still the same paragraph
still the same paragraph, but wrapped with element <br>
and second wrapped line

Centered by modifier

Colored by modifier

Code

function reImage($matches) {
  $content = $matches[1];
  $align = $matches[5];
  $href = $matches[6];
}

Syntax can be highlighted by third party module

Images

with on-mouse-over event

List

• Red
• Green
• Blue

or

• Red
• Green
• Blue

ordered

1. Bird
2. McHale
3. Parish

1. Bird
2. McHale
3. Parish

1. Bird
2. McHale
3. Parish

1. Bird
2. McHale
3. Parish

nested

1. Bird
   1. Bird
      • Red
      • Green
      • Blue
   2. McHale
   3. Parish
2. McHale
3. Parish
   1. Bird
   2. McHale
   3. Parish

Typography

• very long words division (with respect for language rules). Example: antidisestablishmen­tarianism
• clickable emails and URL www.davidgrudl.com, david@grudl.com (emails are obfuscated against spambots)
• „national“ ‚typographic‘ quotes
• divider vs. dash: 10–15 vs. north-west
• en-dash: one – two
• dimension sign 10 × 20
• nonbreakable spaces in phone numbers +420 776 552 046
• acronym NATO
• abbr et al.
• quickCorrects like this™ this® or ©this
• arrows ← and → and ↔ ;
• ellipsis…
• preserve HTML entities
• and many others :-)

Tables

Copyright

Texy! © David Grudl (dgx), 2003–2004

Hello World

This is a test texy page. With a right-aligned image: 

And a list:

• one
• two

That's all, folks.

Tento soubor nenĂ aktuálnĂ!

Texy! is sexy!

Texy! je pĹ™evaděč textu do formátovanĂ©ho HTML kĂłdu. DĂky nÄ›mu mĹŻĹľete psát strukturovanĂ© dokumenty i bez znalosti nebo pouĹľitĂ jazyka HTML. Dokument napĂšete v pĹ™ehlednĂ©, ÄŤistÄ› textovĂ©, formÄ› a Texy! jej automaticky pĹ™evede do validnĂho (X)HTML kĂłdu.

Texy! je jednĂm z nejkomplexnÄ›jšĂch formátovaÄŤĹŻ. UmĂ zpracovávat obrázky, odkazy, vnoĹ™enĂ© seznamy, tabulky a má takĂ© plnou podporu CSS.

Titulky

Titulek nižšà úrovně

Odstavce

Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Curabitur turpis enim, placerat tincidunt, tincidunt ac, fringilla et, mauris. stále ten stejný odstavec
stále ten stejnĂ˝ odstavec, ale řádek je oddÄ›len elementem <br>
a druhý oddělený řádek

Vycentrováno modifikátorem

Obarveno modifikátorem

Horizontálnà linky

ÄŤtyĹ™i linky, ÄŤtyĹ™i zpĹŻsoby zápisu

KĂłd

function reImage($matches) {
  $content = $matches[1];
  $align = $matches[5];
  $href = $matches[6];
}

Syntax-highlighting lze zprovoznit pouĹľitĂm pĹ™ĂdanĂ©ho modulu

VypnutĂ Texy

Div

vnoĹ™enĂ

Citace

This is a blockquote with two paragraphs. Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Aliquam hendrerit mi posuere lectus. Vestibulum enim wisi, viverra nec, fringilla in, laoreet vitae, risus.

640 K should be enough for everyone http://www.microsoft.com

vnoĹ™enĂ

Titulek

Vnořená citace

a zpět do prvnà úrovně

Odkazy

• Look at homepage.
• Do you know La Trine?
• This picture is clickable

uĹľitĂm referencĂ

• Look at Texy!
• My address is me@example.com
• What about this site?

Obrázky

v odstavĂch

Left-aligned image. Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Curabitur turpis enim, placerat tincidunt, tincidunt ac, fringilla et, mauris.

Right-aligned image. Curabitur quam justo, hendrerit a, mattis ut, ultrices at, dui. In mollis. Ut pellentesque erat vehicula nunc. Ut ultricies. Nulla nunc velit, consequat vel, laoreet in, blandit et, eros.

s událostĂ on-mouse-over

s popiskou

Toto je popiska pod obrázkem / fotkou

uĹľitĂm referencĂ

What a beautiful logo :-)

s modifikátorem

Fráze

Ukázka: zdůrazněné, silně zdůrazněné and nejsilněji zdůrazněné

Krátký citát: Lorem Ipsum Dolores

Zdroj, citovaná osoba: Jak ~~Frank Borland~~ Ĺ™Ăkal, …

A dalšĂ: superscript m² vs. subscript O₂

++pĹ™idanĂ˝ text++ vs. –odstranÄ›nĂ˝ text--

KĂłd: <br />

s modifikátory

silnĂ˝ a zelenĂ˝ jako Hulk

PĹ™ĂmĂ© HTML tagy

This is strong text.

Seznamy

• Red
• Green
• Blue

or

• Red
• Green
• Blue

ÄŤĂslovanĂ©

1. Bird
2. McHale
3. Parish

1. Bird
2. McHale
3. Parish

1. Bird
2. McHale
3. Parish

1. Bird
2. McHale
3. Parish

1. Bird
2. McHale
3. Parish

vnořené

1. Bird
   1. Bird
      • Red
      • Green
      • Blue
   2. McHale
   3. Parish
2. McHale
3. Parish
   1. Bird
   2. McHale
   3. Parish

definiÄŤnĂ seznam

Koncert DivokĂ˝ Bill

termĂn: 9. 12. 2004

mĂsto: Hala Vodová, Brno

Cena: 260 KÄŤ

a s modifikátory:

triangl

trojĂşhelnĂk

neladěný bicà hudebnà nástroj

tringulačnà věž

Modifikátory

Lze pouĹľĂt tyto druhy modifikátorĹŻ:

• (titulek) popisnĂ©, pĹ™idajĂ objektu titulek (nebo alternativnĂ text obrázkĹŻm)
• [class1 class2 #id] urÄŤujĂcĂ tĹ™Ădu a/nebo ID
• {class:blue} pĹ™ĂmĂ˝ zápis stylu
• horizontálnĂ zarovnánĂ:
  • doleva <
  • doprava >
  • vycentrovanĂ˝ <>
  • do bloku =
• vertikálnĂ zarovnánĂ: (jen u tabulek)
  • nahoru ^
  • na stĹ™ed –
  • dolĹŻ _

Typografie

• rozdÄ›lenĂ velmi dlouhĂ˝ch slov podle ÄŤeskĂ˝ch pravidel. PĹ™Ăklad: nejneobhospodaĹ™ováva­telnÄ›jšĂmi
• klikatelnĂ© adresy www.davidgrudl.com, david@grudl.com (emaily jsou odolnĂ© vĹŻÄŤi spambotĹŻm)
• „ÄŤeskĂ©“ ‚typografickĂ©‘ uvozovky
• pomlÄŤka vs. spojovnĂk: 10–15 vs. ÄŤesko-slovenskĂ˝
• pomlÄŤka: jedna – dvÄ›
• typografickĂ˝ kĹ™ĂĹľek u rozmÄ›rĹŻ 10 × 20
• nedÄ›litelnĂ© mezery u telefonnĂch ÄŤĂsel +420 776 552 046
• vkládánĂ nezalomitelnĂ˝ch mezer za jednopĂsmennĂ© pĹ™edloĹľky
• acronym NATO
• abbr et al.
• náhrady™ nebo® za pĹ™ĂslušnĂ© entity(C)
• šipky ← a → a ↔ ;
• tĹ™i teÄŤky…
• zachovánĂ HTML entit &
• a spousta dalšĂho :-)

Tabulky

s hlaviÄŤkou

nebo pouĹľitĂm *

slouÄŤenĂ sloupcĹŻ

všiměte si zdvojeného ||

sloučenà řádků

modifikátory

Copyright

Texy! © David Grudl (dgx), 2004–2008

Ubytování – Jižní Čechy – Třeboňsko

Nabízíme ubytování v chalupě v Jižních Čechách na třeboňsku.

Rekonstruovaný bývalý statek se třemi komfortními velkými místnostmi:

• Vybavená kuchyně s kamny
• Vybavený obývací pokoj (28 m²) se dvěma postelemi
  • Možnost rozložení gauče na třetí širokou postel
  • Krb
  • Televize + video
  • Kukačky :-)
• Další místnost (28 m²) s jedním dvojlůžkem a možností přistýlky
• Koupelna s vanou
• …a další místnosti (spíž, předsíň, denní pokoj, kuřárna)

Co ještě chalupa nabízí?

• Auto můžete schovat před letními kroupami do garáže.
• Vaše děti mohou dovádět se psem na veliké udržované zahradě.
• Na jihu se můžete kochat krásným výhledem na Novohradské hory.
• Navečer si můžete připravit posezení u grilu (uhli k dispozici) nebo opékání špekáčků u ohýnku (dřevo k dispozici).

Chaloupka se nachází na konci malé klidné vesnice.

Co najdete v okolí:

• Na severu můžete jít na procházku do rozlehlého udržovaného borového lesa s bohatou úrodou borůvek, brusinek a hub
• Na jihu je Národní přírodní rezervace Červené blato
• Na východě Suchdol nad Lužnicí a nedaleko něj vyhlášené přírodní koupaliště Suchdolské pískovny
• Dále na severovýchodě je CHKO Třeboňsko (další stránky o CHKO v angličtině).
• Ideální krajina pro cyklistiku – klidné silnice, spíše roviny a mírné kopečky

Kapacita

Oficiálně nabízíme tři místa. Pro tři osoby nabízí chalupa komfortní bydlení – pro každého jednu postel a dostatek teplé vody. Pokud by vás bylo více, další dvě osoby se po dohodě mohou ubytovat v podobné chalupě hned vedle – zařídíme, domluvíme, kontakt předáme. V případě zájmu se mohou ubytovat i větší skupiny – místa je dost.

Kontakt:

Telefon: 608 903 585, nebo viz stránka kontakty.

Nabídka: byt 3+1 v Českých Budějovicích

Nabízím byt 3+1 v panelovém domě v klidné části českých Budějovic – sídliště Vltava.

Základní informace

• dům po celkové rekonstrukci – střecha, zateplení, výměna oken a stoupaček.
• 7. patro
• 3 pokoje orientované na sever, jeden na jih
• balkon
• družstevní forma vlastnictví

Poloha bytu

Byt se nachází na severním konci sídliště vltava. V okolí:

• prodejna Globus
• zastávka Sídliště Vltava – MHD č. 9, 14, 15, 19
• chráněná přírodní rezervace Vrbenské Rybníky
• v domě je přípojka kabelové televize a několik společností zde nabízí vysokorychlostní internet.

Družstevní vlastnictví

Družstevní vlastnictví někteří lidé iracionálně považují za cosi méněcenného a špatného. Oproti osobnímu vlastnictví má ale mnoho výhod:

• Souhlas s nastěhováním musí udělit družstvo. S tím obvykle není problém, ale navíc to ochrání dům před nastěhováním problémových nájemníků.
• Celkové lepší fungování domu – lidé se lépe domluví na nutných opravách, společných rekonstrukcích.
• Chod domu si obvykle některý z obyvatel vezme „na starost“ – rychleji se tak vyřeší běžné provozní poruchy (poruchy výtahu, prasklá voda, atd.).
• Menší anonymita – pravidelné schůze družstva (pochopitelně s dobrovolnou účastí) umožní nájemníkům se poznat lépe než přes zdi domu.
• Pro sdružení vlastníků banky často neposkytují hypotéky – viz níže.

Jinak je družstevní vlastnicví stejně cenné jako osobní – byt není „někoho jiného“. Veškeré peníze, které platíte, jdou do oprav domu či investic do domu.

Někdo má dojem, že družstvo jej může „nutit“ platit na rekonstrukci, o kterou nemá zájem, kdežto s bytem v osobním vlastnictví si může dělat, co chce. To ale není pravda: V domech s více než 5 byty je ze zákona povinnost ustanovit sdružení vlastníků bytových jednotek, které bude spravovat dům podobně jako družstvo, a nájemníci jsou povinni se podřídit hlasování sdružení vlastníků.

Navíc pro společenství vlastníků banky obvykle neposkytují hypotéky:

Pro sdružení/spole­čenství vlastníků bytových jednotek hypoteční úvěr neposkytujeme. Je však možné na obchodním místě banky individuálně projednat tzv. investiční úvěr poskytovaný pro oblast SME (úvěry pro společnosti a osoby samostatně výdělečně činné). Tento typ úvěru je ale skutečně nutné individuálně projednat, a to včetně podmínek. Marek Richter, vedoucí týmu hypotečních úvěrů Raiffeisenbank

Při koupi bytu v osobním vlastnictví v polorozpadlém paneláku tedy kupujete časovanou bombu a je pravděpodobné, že rekonstrukce domu neproběhne v dohledné době.

Každopádně, tento byt je v rekonstruovaném domě, žádné další společné investice se nechystají, a výše příspěvků do fondu oprav jsou pevně nastavené. Spolupráce s družstvem je dlouhodobě dobrá a k plné spokojenosti obyvatel.

Ostatní

Prodej bytu nijak nespěchá, proto dokud vidíte tuto nabídku, je platná.

Využijte možnosti koupit byt bez zbytečných poplatků realitní kanceláři.

Pište na některý z kontaktů na stránce kontakty.

XULRunner tutorial

I've been playing recently with a time-keeping tool, written in XUL, open in a popup window. The application is pretty simple, just allowing me to enter a list of tasks, and keep track of how long I've had any task selected. Being in a popup allows me to quickly change tasks and keep a much more accurate log of my time.

I was having a problem though, because clicking links from emails or IMs would open up into my popup window, and not a new tab in Firefox, making me lose my data. Nathan suggested XULRunner, and after losing my data 3 more times, I broke down and figured out how to set it up. XULRunner is a command-line app to run XUL applications, essentially starting another gecko instance and running your code in that. That way Outlook won't think that's a good place to load windows, and if Firefox crashes, my XULRunner instance is still fine and dandy.

Documentation on XULRunner is a bit scant, but googling revealed a xulmine sample, and from that I was able to reverse engineer what the hell XULRunner wanted me to do. And, now that I review some of these links, I see Darin has more information about XULRunner than I thought.

As a convention, emphasized words are variables, and should be replaced with a value that makes sense for your application.

Setting up your folder structure

Set up your application like so:

/applicationName
   /chrome
      /applicationName
         your app files
      chrome.manifest
   /defaults
      /preferences
         prefs.js
   application.ini

appName=myXulApp
mkdir -p $appName/chrome/$appName
   touch $appName/chrome/chrome.manifest
mkdir -p $appName/defaults/preferences
   touch $appName/defaults/preferences/prefs.js
   touch $appName/application.ini

There are more complicated ways to do it, but this seems to be the bare bones. Now let's set up all the configuration fi­les.

Setting up your application.ini

XULRunner works by reading an application.ini file, and then loading things up by looking at specific directories. Here's a sample application.ini:

[App]
Vendor=company
Name=applicationName
Version=0.1
BuildID=20050506

[Gecko]
MinVersion=1.8
MaxVersion=1.8

The important bit is the [Gecko] section. The [App] section is nice, but doesn't seem to serve any essential function. The [Gecko] part tells XULRunner what versions of Gecko can be used with your app, and XULRunner will fail if those lines aren't there.

Setting up your prefs.js

This is a preferences file, where you set initial values of things, either gecko engine settings or application specific settings. All we need here is:

pref("toolkit.defaultChromeURI", "chrome://<em>applicationName</em>/content/<em>startPage</em>.xul");

The startPage is what XUL page you want to view initially.

Setting up your chrome.manifest

This file performs the mapping between a chrome:// url and your application files:

content <em>applicationName</em> file:<em>applicationName</em>/

That effectively tells XULRunner to look at applicationName/ in the chrome directory when it encounters a chrome://<em>applicationName</em>/content/ URI.

Most XUL apps I've seen are distributed as a .jar file, rather than a subfolder of chrome/ with all the files scattered about.
Having your application unpacked is much easier for development, but when you do want to deploy it as a jar:

1. Zip up your /chrome/appli­cationName folder, put the applicationNa­me.zip in the /chrome directory.
2. Rename applicationNa­me.zip to applicationNa­me.jar
3. Change the chrome.manifest to point to applicationNa­me.jar:

   content <em>applicationName</em> jar:<em>applicationName</em>.jar!/

   The jar: protocol tells XULRunner that this is a jar file, the the ! after the filename tells it to look inside the jar file.

Running your app with XULRunner

Now all the prep work has been done, and your application is ready to be run. After you download XULRunner, this is just a matter of a command line:

xulrunner application.ini

Your startPage.xul should open up in a window, like any other desktop app.

Conclusion

To get a XUL app working with XUL Runner, you need 3 config files in 3 different formats. That's kinda messed up. To be fair, the chrome.manifest format is something used for Firefox extensions, so it makes sense to re-use that functionality, but needing to specify your start page in prefs.js seems arbitrary. There are some proposals for a different method of XUL Application Packaging, but that's about a week old right now, and there's no code to support it.

A XULRunner-based app seems like a good solution when you want to avoid Firefox. I want to avoid Firefox because of outlook, but in general I don't see much of an advantage over just making an extension, its just a series of trade offs.

Pros:

• Runs in its own process
• Unaffected by other Firefox extensions, skins, configuration.
• Doesn't require Firefox

Cons:

• Doesn't auto-update with Firefox, auto-update code would need to be written
• Unaffected by other Firefox extensions, skins, configuration.
• Install package would be larger download to include the XULRunner binaries (13MB more)

The big internal app we're writing will likely remain as a Firefox extension, but going through this process has certainly taught me a lot more about how to organize XUL apps. I wonder if you could munge XULRunner config files to point to a Firefox extension? Then you could auto-update with Firefox, and still get the benefits of XULRunner.

Friday, May 06, 2005 3:29 PM

nainstaluj openvpn, to novsi (2008..)

(zip)

toto je cfg, to rozbal do programfiles/o­penvpn

uprav si etc/hosts ($win / system32 / drivers /etc) pridej tam:

192.168.77.1 hosting.vpn.rozsnyo.com
192.168.77.1 sql.vpn.rozsnyo.com

muzes zkusit ssh na tu ip/host, port 20202 ondra/on­drazizka a zmen heslo (passwd i smbpasswd)

zkus \\hosting.vpn­.rozsnyo.com\on­dra
ondra/ a heslo ze smbpasswd
a na http://sql.vpn.rozsnyo.com bys mel videt php my admin

nasmeruj tu tvou domenu na 77.93.215.16

toto je jen 2jadro (P4@3.4, 4G ram, 2× 74G/10k v mirroru)

DB: winlyricscom / winlyricscom , heslo vygeneruj (choze: poslu SMSkou)

Na linuxu:

nainstalujes si openvpn a na tom sdileni je ta slozka .vpn, v ni jsou konfiguraky a certifikaty pak to nejak prinutis at to pouzije.. (openvpn vyzaduje v jadru povolit „tun/tap“ device, treba jako modul) a pak do etc/fstab jede neco takovehoto:

hosting.vpn.rozsnyo.com:/home/daniel    /home/daniel/hosting  nfs  noatime,noauto,user  0 0

WinLyrics – WinAmp lyrics plugin

Nový hosting?

Máme nabídku nového hostingu… na Gigawebu to občas vypadne (mysql i web), a už není nijak moc pružný…

přístup zde.

TODO list

Hodnocení lyrics

Dokumentace zde.

• Do interface přidat možnost ohodnotit lyrics.
• Stupnice:
  • Bez chyby, perfektní
  • Dobré
  • Použitelné
  • Nesmysly, hodně chyb
  • Totální odpad
• Vliv na funkci:
  • Vyhledané lyrics se budou řadit také podle hodnocení daných lyrics. Jinak žádný.
• Na server se pošle: ID lyrics, ID hodnotícího uživatele (případně session ID), a hodnocení.

Přihlašování

Dokumentace zde.

• Lyrics se budou stahovat přes login a heslo.
• Zamezí to crackům
• Provedení: Dvě možnosti. Jednodušší a složitější.
  • S každým požadavkem posílat i jméno a heslo.
    • Heslo může být md5
    • Jenže jakmile někdo uvidí cizí request, může se za něj vydávat → šíření user name a hesla, a jsme kde jsme byli.
  • Druhá možnost: Autentizace a session numbers. Jak?
    • Klient se bude chtít přihlásit
    • Server pošle náhodný řetězec (challenge)
    • Klient jej spojí se svým heslem a pošle md5( challenge + md5(heslo) )
    • Na serveru bude uchované md5() hesla, provede se to samé
    • Pokud budou stejné, klientovi se pošle session ID v cookie. Od té doby s ním může po nějakou dobu pracovat.
    • Tímhle způsobem se nebude nikdo moci vymlouvat, že se přes jeho ID připojuje někdo jiný.
    • Pokud se tedy ID začne šířit, má problém :-)
    • Resp. povolíme použití jednoho ID jen z jedné IP adresy v rámci jedné session.

Autorství lyrics (credits)

• Lidi mají tendenci do Lyrics dávat svůj podpis.
• Tomu předejdeme tak, že jim umožníme tento údaj oddělit.
• Při přidávání se také odešle jejich username, případně se použije session ID.
• Na serveru se k lyrics přidá záznam o tom, kdo jej poslal.

User Songs Rating

To save user's rating, call rate.php:

rate.php?user=Trapper852@Adelphia.net&song_id=1500&rating=10
http://d02-0330a.kn.vutbr.cz/winlyrics/real/rate.php?user=Trapper852@Adelphia.net&song_id=1500&rating=10

Params:

@param user     string         Identifier - e.g. e-mail - of the user who rates the song.
@param song_id  unsigned int   ID of the rated song.  UNSIGNED INT
@param rating   signed byte    Rating of the song.

Returns:

one of OK, BAD_PARAM, DB_ERROR, SQL_ERROR, SESSION_ERROR.

May be followed by a space and additional data, specifying the error details.

• OK – everything ok, rating saved.
• BAD_PARAM – bad parameters (see above) – may also mean non-existent user, song, etc.
  • May be followed by the name of the bad param.
• DB_ERROR – database error, e.g. could not connect to DB.
• SQL_ERROR – error occured when performing SQL query. Details in /logs/log.txt .
• SESSION_ERROR – given session ID is not valid, need to re-authentize.

Session-based rating

1. Authentize using authentize.php
2. Use the session to rate:

>> `http://localhost:81/winlyrics/real/rate_sess.php?sess_id=f8675g1390s7dngloohqitsd74&song_id=15&rating=10`
<< `OK`

Params:

@param sess_id  string         Session ID, previously acquired using authentize.php
@param song_id  unsigned int   ID of the rated song.  UNSIGNED INT
@param rating   signed byte    Rating of the song.

Returns: Same as above.

SQL code

Tests

Creates a user-song-rating triplet for user Trapper852@Adelphia.net and song 1500, rated -10:

CALL wl2_SetRating( (SELECT id FROM wl2_users WHERE email = 'Trapper852@Adelphia.net'), 1500, -10 );

Changes that rating to 50:

CALL wl2_SetRating( (SELECT id FROM wl2_users WHERE email = 'Trapper852@Adelphia.net'), 1500, 50 );

Creates rating for other song and other user:

CALL wl2_SetRating( 20, 1700, 40 );

Does nothing (invalid user)

CALL wl2_SetRating( (SELECT id FROM wl2_users WHERE email = 'non@existent@user'), 200, 30 );

Removes the rating for given user-song tuple:

CALL wl2_SetRating( (SELECT id FROM wl2_users WHERE email = 'Trapper852@Adelphia.net'), 1500, NULL );

Tables

CREATE TABLE  `winlyricscom`.`wl2_users` SELECT * FROM users;
ALTER TABLE `winlyricscom`.`wl2_users`
  ADD COLUMN `pass_md5` char(32) character set cp1250 collate cp1250_bin NOT NULL,
  MODIFY `email` `email` varchar(128) character set cp1250 NOT NULL,
  KEY `email` (`email`);

-- evt. --
INSERT INTO wl2_users
  SELECT id, '' AS pass, jmeno, adresa, mesto, stat_kod, postal, email FROM reg_users;

CREATE TABLE  `winlyricscom`.`wl2_songs_rating` (
  `id_song` int(10) unsigned NOT NULL auto_increment,
  `rating_sum` int(10) unsigned NOT NULL,
  `rating_cnt` int(10) unsigned NOT NULL,
  `rating` float NOT NULL,
  PRIMARY KEY  (`id_song`)
) ENGINE=MyISAM DEFAULT CHARSET=utf-8;

CREATE TABLE  `winlyricscom`.`wl2_users_ratings` (
  `id_user` int(10) unsigned NOT NULL,
  `id_lyrics` int(10) unsigned NOT NULL,
  `rating` tinyint(4) NOT NULL,
  PRIMARY KEY  (`id_user`,`id_lyrics`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Procedure wl2_SetRating

Creates / updates rating of given song by the given user. Repeated rating from one user changes his previous rating. Stores the data in wl2_users_ratings.

DELIMITER $$

DROP PROCEDURE IF EXISTS `winlyricscom`.`wl2_SetRating`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE  `winlyricscom`.`wl2_SetRating`(idUser INT UNSIGNED, idSong INT UNSIGNED, iRating TINYINT)
BEGIN


  SELECT rating INTO @iRating FROM wl2_users_ratings WHERE id_user = idUser AND id_song = idSong;

  IF @iRating = 0 THEN
    INSERT INTO wl2_users_ratings SET id_user = idUser, id_song = idSong, rating = iRating;
  ELSE
    UPDATE wl2_users_ratings SET rating_sum = iRating WHERE id_user = idUser AND id_song = idSong;
  END IF;


END $$

DELIMITER ;

Procedure wl2_RebuildRating

Rebuilds the average ratings in wl2_songs_rating based on data in wl2_users_ratings.

DELIMITER $$

DROP PROCEDURE IF EXISTS `winlyricscom`.`wl2_RebuildRating`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE  `winlyricscom`.`wl2_RebuildRating`()
BEGIN

  TRUNCATE wl2_songs_rating;

  INSERT INTO wl2_songs_rating
  SELECT id_song,
      SUM(rating) AS rating_sum, COUNT(*) AS rating_cnt,
      SUM(rating) / COUNT(*) AS rating
    FROM wl2_users_ratings GROUP BY id_song;



END $$

DELIMITER ;

Přihlašování – login

Identifikace

User bude identifikovaný e-mailem.
U toho je jeden problém – celkem dost uživatelů má ve WinLyrics více účtů s jedním mailem, patrně pro více počítačů. Viz tento dotaz:

SELECT email, COUNT(*) AS cnt FROM wl2_users GROUP BY email HAVING cnt > 1 ORDER BY cnt DESC;

Tak těm bychom asi vytvořili jeden účet, který by mogli používat kdekoliv, ne?

Heslo

Heslo bude uložené jako MD5().

To je zatím vše, co je „jisté“, ostatní záleží na chozém. Já navrhuju to udělat přes challenge – response metodu.

Challenge – response autentization

Pro testování… – nastavíme někomu heslo…

UPDATE wl2_users SET pass_md5 = MD5('') WHERE email='winlyrics@dark-sun.org';

>> http://localhost:81/winlyrics/real/authentize.php?action=create_session

<< SESSION_ID f8675g1390s7dngloohqitsd74

sess_id == "f8675g1390s7dngloohqitsd74"
user pass == md5('') == "d41d8cd98f00b204e9800998ecf8427e"
hash = md5( "f8675g1390s7dngloohqitsd74" . "d41d8cd98f00b204e9800998ecf8427e" ) == "989bd8a84f9e42b314b3358bb263794e"

>> http://localhost:81/winlyrics/real/authentize.php?action=verify&sess_id=f8675g1390s7dngloohqitsd74&user=winlyrics@dark-sun.org&hash=989bd8a84f9e42b314b3358bb263794e

<< AUTH_OK

SELECT id, MD5( CONCAT('f8675g1390s7dngloohqitsd74', pass_md5)) = '989bd8a84f9e42b314b3358bb263794e' FROM wl2_users WHERE email = 'winlyrics@dark-sun.org';

>> http://localhost:81/winlyrics/real/authentize.php?action=get_state&sess_id=f8675g1390s7dngloohqitsd74

<< VERIFIED

print_r( $_SESSION );

Array (
    [valid] => 1
    [verified] => 1
    [user] => winlyrics@dark-sun.org
    [user_id] => 1
)

authentize.php

Autenthicates the user.

1) User requests a session ID.
New session is created and ID is sent to user.
2) User sends user name and MD5( sess_id . MD5( user_pass ) ).
Server constructs the same string and checks their equality.
If they match, a „valid“ flag is set to the session and from that moment,
session ID can be used to perform user actions.

Actions:

create_session == true:

Creates a session.
Returns: "SESSION_ERROR" on error or "SESSION_ID <<sess_id>>".

verify == true:

Verifies the user for this session.
//Params:// sess_id, hash.

• „sess_id“ is the ID returned by „create_session“ or „get_state“.
• „user“ is the user identifier (e-mail)
• „hash“ is MD5( sess_id . MD5( <<user pass>> ) ).

Returns one of DB_ERROR, SQL_ERROR, AUTH_FAILED or AUTH_OK.

get_state == true:

//Params:// sess_id.
//Returns://

• "VERIFIED" if the current session is verified (authentization passed).
• "NOT_VERIFIED" if the current session is not verified.
• "BAD_PARAM sess_id" when the given sess_id is not valid session.

Session variables:

• valid – set to true when this session was created using the „create_session“ action.
• verified – set to true after the client succeeded in „verify“ action.
• user – after successful verification, contains user identifier.
• user_id – -"-, contains user ID.

WebTTD – A Browser-based Transport Tycoon Derivate

I've a fixed idea of creating a web version of Transport Tycoon.

Imagine how wonderful it would be if you could build your company for several weeks or months, on a vast landscape. No hush-hush game like classical TTD, but rather slow progress, more business cooperation amongst players…

Game concept

Web game concepts are different from those of desktop games. If you ever have played Travian, you know how such web game works.

1) First, the game progress must be veeery sloooow. Why? Because web games are played occassionally – during a boring meetings, during launch break, before bed… People want to spend few minutes daily on such game. What can you do in that time? Build a track or two, buy a train and set their orders. Then you leave it until the next day. The train should do no more than few turns in that time.

2) Second, there are many many players, which join the game at different times. By the time you have been playing for several weeks, someone other joins the game; so you have your logistic imperium with dozens of tracks, and the other player has nothing but a loan. I've been thinking about this a lot, and came up with a solution similar to Travian's one.

3) Third, web games are not fun because of fancy graphics; But even not because of complicated game logic. The best part of MMO games are – the other players! Yes, it's the social part of the game which keeps people playing: The feel of being a part of gang.

This aspect would have to be carefully applied in the game design. Again, I have ideas about how to achieve that: In classical TTD, you always found point A, point B and built a track between them. Sometimes you found out that it's impossible because some area is being blocked by other player.

Well, in MMO-TTD, this would inevitably happen all the time, so it's not a good concept at all. Better would be to let people deal at which price can one player use other's track.

There are other things to think about, but these three are the main to focus at.

Technology

It would be programmed using technologies:

* HTML + CSS, eventually Canvas
* JavaScript for client logic
* J2EE backend with JBoss Cache,
* or perhaps thin PHP layer with stored procedures in PostgreSQL or MySQL

So far, I gave it about two days of work and have some poor proofs-of-concept:

1) TTD-like dialogs made in HTML. Currently only layout + some DoJo code to make them move.
This part would need a lot of work: many dialog types + data binding.
See the demo .
(Note that the dialogs are not images, but only HTML+CSS – see the page code.)

2) Isometric landscape drawing and editing, plus client/server communication (AJAX).
I'll add URL of this drawing as soon as I put the database on the server.
Works quite fine, but there's one big problem: When you click somewhere,
it's almost impossible to compute which cell has been clicked, so it's needed to store it somewhere.
And since current technologies, including canvas, do not provide such functionality,
the only solution seems to use only flat landscape, which is kinda boring…

Or, the other option – create the client in .NET or Java or Flash.
Since I don't like Flash and .NET is bound to Microsoft, it would be Java's WebStart.

Next I'd move to drawing of vehicles. That should be quite easy, provided we had waypoints of their movement together with their times.

These data have to be computed on the server side. So next stage would be J2EE or PL/SQL programming (I'm SQL fan, so it's likely to be PL/SQL :) //Decision changed:// Due to performance requirements, reading everything to and from the DB is not a good idea. Let it be Java.

Then the data are needed to be send to the client somehow, so some AJAX-Push would be necessary.

Of course the client will not load all the updates, only those which currently affect him – that means:

• Only cells in his viewport
• Only messages he configured to get
• Vehicles positions are only send when it departs onto a track
• No metrics of stations, towns, industries, companies – that's only on-demand in a dialog.

Estimated data transfer amounts is hundreds of bytes per second, at most.

Current code structure

Client side

• Xcom – the main class / namespace (yeah, originally it should have been UFO ;)
  • Xcom.Land – the class which manages the map data.
  • Xcom.data: {
    • cities: []
    • industries: []
    • roadTracks: []
    • roadVehicles: []
    • companies: []
    • stations: []
  • }
• eMap – manages the DHTML representation of the land.
  • eMap.TransformFieldXYtoCoords( x, y, level ) – computes mapbox coords of the given cell, taking the map offset into account.
  • eMap.TransformCoordsToFieldXY( x, y ) – computes position of the cell on the given coordinates, taking the map offset into account.
  • eMap.DrawArea( x1, y1, x2, y2 );
• z-indexes:
  • #map:
  • #mapbox:
  • #overlay:
  • individual cells:

Server side

• Game game
  • TtdGrid land – auto-expanding grid of cells.
    • Currently organized to tiles with 10×10 cells – for optimization.
    • Cell get( x, y )
    • init(), run(), pause(), stop()
    • persist() – persists the game (to a DB?)
  • TtdData data – holds all game data other than the land.
  • GameTickerThread tickerThread – runs a ticker in regular interval (1 s?).
    • GameTicker ticker – modifies the world:
      • Waiting transportables in stations
      • Towns growth
      • Industry production adjustments
      • Failures, disasters…
      • Subsidies
• TtdDataJsonServlet – provides data to the client.
  • request: area info (x1,y1,x2,y2), changedSince [timestamp]
  • JSON response: all information about the given area which changed since changedSince
    • cells
    • stations
    • …

OpenTTD Save/Load

http://svn.openttd.org/trunk/

src/saveload/map_sl.cpp

src/saveload/saveload.cpp

/**
 * Save/Load an array.
 * @param array The array being manipulated
 * @param length The length of the array in elements
 * @param conv VarType type of the atomic array (int, byte, uint64, etc.)
 */
void SlArray(void *array, size_t length, VarType conv){ ... }

src/map.cpp

uint _map_log_x;     ///< 2^_map_log_x == _map_size_x
uint _map_log_y;     ///< 2^_map_log_y == _map_size_y
uint _map_size_x;    ///< Size of the map along the X
uint _map_size_y;    ///< Size of the map along the Y
uint _map_size;      ///< The number of tiles on the map
uint _map_tile_mask; ///< _map_size - 1 (to mask the mapsize)

Tile *_m = NULL;          ///< Tiles of the map
TileExtended *_me = NULL; ///< Extended Tiles of the map

src/map_type.h

/**
 * Data that is stored per tile. Also used TileExtended for this.
 * Look at docs/landscape.html for the exact meaning of the members.
 */
struct Tile {
        byte   type_height; ///< The type (bits 4..7) and height of the northern corner
        byte   m1;          ///< Primarily used for ownership information
        uint16 m2;          ///< Primarily used for indices to towns, industries and stations
        byte   m3;          ///< General purpose
        byte   m4;          ///< General purpose
        byte   m5;          ///< General purpose
        byte   m6;          ///< Primarily used for bridges and rainforest/desert
};

http://svn.openttd.org/…ndscape.html
http://209.85.135.132/search?…
http://209.85.135.132/search?…

OpenTTD's docs: landscape OpenTTD's docs: landscape_exter­nals OpenTTD's docs: landscape_grid

OpenTTD save file transformer

TODOs

• Construction toolbars
• Track and plan editation
• Building functions
  • First, road construction
• AJAX push
• Complete basic subset of tables to make the game run
• Data model in Java
• Vehicle path movement animation
  • Will follow the „track“
  • Track consists of arcs – parts of the track with the same direction.
  • Each direction change is a waypoint of the animation
  • Y axis movement slower than X
• Get pictures from the OpenTTD project
• City growth algorithm

Links

Simutrans history

Tycoonez

Geogre Zernebok (Very interesting page :)

OpenTTD forums – link to my request for OpenTTD map export

A bit of realism

But of course, for one man, this is work for years.

For now, it will just be my humble dream :)

MSIE 8.1 drops JavaScript support in favor of VBScript

Happy Fools' day :)

JavaScript isometric libraries and „game engines“

When you seach for „JavaScript isometric“, you get few links, most of them useless. Let's see what we get:

IsoGame

Just a sprite moving over a generated grid. Somewhat buggy movement algorithm. PHP used to draw the tile sprite. Doesn't work with PHP 5.2. Here's the fixed version: IsoGame.zip

URL: http://code.google.com/p/isogame/

Verdict: Not useful.

Clint's isometric game example, now with Yahoo!

Another sprite moving, this time, only over a texture; grid is „virtual“ (only computed). The movement is done quite nicely; However, this is not a library, only some poorly coded proof-of concept.

URL: http://www.rickherron.com/…IsoTest.html

Verdict: Not useful.

„the snowman that learned to program“'s Ja­vaScript Isometric Game Engine

„Example of an isometric tile environment with depth sorting and path finding“, as the page says.

* Uses <canvas> to render the image.
* „Manually“ drawn sprites, no rendering engine.
* Uses jQuery.
* A* pathfinding algorithm, Heap and Collection implementations.

URL: The code is available at GitHub.

Demo: http://greenmayo.com/…CarterHenry/

Verdict: A bit useful.

FreeVolution

The „first full fledged DHTML isometric tile engine on the internet“. As you scroll around, tiles are fetched and destroyed trough AJAX. PHP backend for AJAX calls.

* Uses really a LOT of <img> elements, all with in-line CSS. No wonder it's so slow (read, „unusably“ slow).
* JavaScript (or, client side) Written in a PHP way, that means, no consistent JavaScript library.
* However, the math behind it could be quite good for inspiration.

Demo: http://www.pc-gamers.com/…s_coords.php

Projec page not found.

Verdict: Could be useful; have to study it more (really pity it's not coded better).

Oasis – Flash Isometric Map Maker

For Flash… not useful. Perhaps the math, will have a look at the sources.

URL: http://komirad.com/…-maker-oasis

Isometric ‚n‘ Hexagonal Maps (an article)

URL: http://www.gamedev.net/…ticle747.asp

c# to javascript, actionscript

Really nice and functional isometric rendering. One of versions has even an attempt of interactivity, though doesn't work for me (FF3).

The only, but fatal, drawback is that the JS code is generated from C#, and quite unreadable.

URL: http://zproxy.wordpress.com/…ript-tycoon/

Demo: http://jsc.sourceforge.net/…atureBoy.htm

Verdict: Would be useful if it was written in JavaScript.

Axono.me

A library, not studied yet.

„axono.me has been designed to render axonometric projections, though it works fine for any kind of grid. The library has been tested using Safari 4 and Firefox.“

URL: http://www.creativeapplications.net.php5-3.dfw1-2.websitetestlink.com/…-me-scripts/

GitHub: http://github.com/…nkey/axonome

Rhodesy Isometric

Written in JavaScript. Seems to be fast, precise, and contains algorithm for distributing terrain level shift from one point.

URL: http://forums.tigsource.com/index.php?…

Demo: http://rhodesy.acsv.net/isometric/

Verdict: Not studied yet, looks very good.

Isometric drawings with jQuery

URL: http://www.tdupree.com/…with-jquery/

Demo: http://www.themaninblue.com/…cape/new.php

Verdict: Not studied yet, looks good.

Other links

50 Useful JavaScript Tools

Isometric camera in javascript for Unity – I don't understand this much. Somehow linked to „Unity3D Dialog Engine“, whatever that is.

Web, JavaScript, AJAX, and Canvas

JavaScript isometric game rendering libraries

I've made up a list of JavaScript libs for isometric rendering.

WebTTD – browser-based Transport Tycoon clone idea.

canvas element selection buffer

English, česky

canvas element selection buffer

I wonder whether canvas element can do any kind of „selection buffer“ functionality (the name taken from OpenGL, I'm sure you know it).

I consider making a Populous-like isometric game in JavaScript, or better said, using web technologies. So it would be isometric, and the cells can be at different „floors“ and they will carry different objects (high buildings, etc). Thus, I can not determine the selected cell/object from the position of the mouse using just some math – I need to store what each pixel or DOM object represents.

• OpenGL has per-pixel selection buffer.
• HTML offers <img> + <map> layer over my game rendered in a <div>.
• SVG offers events on the respective DOM objects.

Does <canvas> offer something? I haven't found anything in the docs at Mozilla and the fact that "":http://blog.nihilogic.dk also uses keyboard selection in Javascript 3D chess also hints that he hasn't found such thing.

Thanks for any tips (ondra@dynawest.cz).

Update – Jacob Seidelin

Napsal jsem Jacobovi Seidelinovi, který si s <canvas>em opravdu vyhraje. (Omlouvám se za redundantnost, ale nemám čas to moc editovat.)

Hello Jacob,

I wonder whether canvas element can do any kind of „selection buffer“ functionality (the name taken from OpenGL, I'm sure you know it).

I consider making a Populous-like isometric game in JavaScript, or better said, using web technologies. So it would be isometric, and the cells can be at different „floors“ and they will carry different objects (high buildings, etc). Thus, I can not determine the selected cell/object from the position of the mouse using just some math – I need to store what each pixel or DOM object represents.

OpenGL has per-pixel selection buffer. HTML offers <img> +

Hey Ondra,

No, there's no such thing for that I'm aware of, unfortunately. I guess you'll have to cook your own thing for that purpose. And yes, the chess keyboard controls were a response to exactly that :-)

Although, if you can describe your areas as polygons or paths, I think there's a isPointInPath() method you could use, although I haven't touched it yet myself. I imagine you'd have to go through all objects, set up a path and check at every mousemove „frame“, though.

Cheers, Jacob

Takže zatím asi nic. Zvažuji podání feature requestu, jestli již někdo něco takového nenavrhnul či se dokonce nechystá.

canvas element selection buffer

I wonder whether canvas element can do any kind of „selection buffer“ functionality (the name taken from OpenGL, I'm sure you know it).

I consider making a Populous-like isometric game in JavaScript, or better said, using web technologies. So it would be isometric, and the cells can be at different „floors“ and they will carry different objects (high buildings, etc). Thus, I can not determine the selected cell/object from the position of the mouse using just some math – I need to store what each pixel or DOM object represents.

• OpenGL has per-pixel selection buffer.
• HTML offers <img> + <map> layer over my game rendered in a <div>.
• SVG offers events on the respective DOM objects.

Does <canvas> offer something? I haven't found anything in the docs at Mozilla and the fact that "":http://blog.nihilogic.dk also uses keyboard selection in Javascript 3D chess also hints that he hasn't found such thing.

Thanks for any tips (ondra@dynawest.cz).

Update – Jacob Seidelin's mail reply

I wrote Jacob Seidelin, who literally plays with <canvas>. (Sorry for redundancy of information – not much time to edit.)

Hello Jacob,

I wonder whether canvas element can do any kind of „selection buffer“ functionality (the name taken from OpenGL, I'm sure you know it).

I consider making a Populous-like isometric game in JavaScript, or better said, using web technologies. So it would be isometric, and the cells can be at different „floors“ and they will carry different objects (high buildings, etc). Thus, I can not determine the selected cell/object from the position of the mouse using just some math – I need to store what each pixel or DOM object represents.

OpenGL has per-pixel selection buffer. HTML offers <img> + <map> layer over my game rendered in a <div>. SVG offers events on the respective DOM objects. Does <canvas> offer something? I haven't found anything in the docs at Mozilla and your chess keyboard selection also hint's that you haven't found such thing.

Thanks for any help, Ondra

Hey Ondra,

No, there's no such thing for <canvas> that I'm aware of, unfortunately. I guess you'll have to cook your own thing for that purpose. And yes, the chess keyboard controls were a response to exactly that :-)

Although, if you can describe your areas as polygons or paths, I think there's a isPointInPath() method you could use, although I haven't touched it yet myself. I imagine you'd have to go through all objects, set up a path and check at every mousemove „frame“, though.

Cheers, Jacob

So much for the selection support of the <canvas> element. I will consider creating a feature request, if I will not find any existing suggestion of such kind.

Thanks also belongs to Martin Hassman for his reply and his blogposts, 2 about canvas (in Czech), where I found the link to Jacob's work.

Suggestion at WHATWG mailing list

Hi,

I've been looking for something similar to OpenGL's selection buffer – that is, you can get some object ID for the given coordinates.

E.g., Jacob Seidelin's chess game http://blog.nihilogic.dk/…/label/chess could use it, but instead, keyboard control had to be used.

isPointInPath() does not solve the problem effectively if the path would be too complex – e.g. pixel-based sprites in several layers.

For an example of what I want to implement, see e.g. http://www.openttd.org/screens.php?… . Mathematical computation of the object is principially impossible (or too complex in best case).

So, my suggestion is to add functionality similar to OpenGL's selection buffer: The canvas element would keep a 2D array with an integer ID for each pixel. When turned on, these values would be set by every operation that changes the pixel, seting it to the current context's value. Pseudo-code example:

function DrawCell( iCellId ){

  canvas.selectionBuffer.trackChanges( ON );
  canvas.selectionBuffer.setFillValue( iCellId );

  canvas.drawRasterImage( ..., cellImage );

  canvas.selectionBuffer.trackChanges( OFF );

}

Then, upon user's mouse click on the canvas, you could determine which object was clicked:

canvas.onclick = function( e ){
  id = canvas.selectionBuffer.getIdAt( e.x, e.y );
  // eventually:
  id = e.selectionBufferID;
}

Such feature would allow interactive application with isometric or 3D graphic. Is something like this planned or already suggested? I haven't found.

Regards, Ondra Zizka

Vybírám skriptovací jazyk pro systémové skripty

Postupem času jsem přešel na Linux. Co mě zde ale nepřestává štvát, je Bash.

Bash je jazyk hrůzy. Ne tak hrozný, jako Caché ObjectScript, ale pořád dost hrozný:

# Bohužel jsem momentálně nenašel dostatečně odstrašující příklad :)
# V praxi ale narážím na naprosto nečitelný kód,
# Využívající těch nejškaredějších syntaktických konstrukcí,
# kde už člověk ztrácí přehled o tom, co je syntaxe find-u a kde je do ní vložen bash.

Jasně, chápu… je to jazyk určený hlavně pro práci v prostředí linuxu, a tedy pro práci převážně s GNU programy, které se vyvíjely v symbióze s Bashem. V interaktivním režimu je stále dobře použitelný pro jednořádkové příkazy. Pokud ale mám louskat několikastránkový skript, kde jsou takových jednořádkových příkazů desítky a každý je taková malá hra „hádej kolik syntaxí je zde použito“, házím flintu do žita.

Doba pokročila, a skoro začíná být důležitější podpora novodobých technologií, jako je Java, XML, webové služby, a v neposlední řadě je velmi příjemné, když lze využít výhod IDE – doplňování, refactoring atd.

Jak je patrné z těchto stránek, jsem momentálně fanda Javy – a to ačkoliv jsem si vědom (někdy až bolestně) všech jejích nevýhod. A jako každý (programátor), občas chci napsat stručný skript, který provede pár operací podle několika zadaných parametrů. Jít na takovou záležitost s Javou je přeci jen poněkud nevhodné – napsat všechnu omáčku, zkompilovat, evt. zabalit do jarka a spouštět stylem java -jar Skript.jar, a to ještě dělat při ladění, to přeci jen není ono.

Takže jsem začal hledat jazyk, který byl vhodný právě pro takové skripty, případně snad i na konzoli. Měl by tedy splňovat tato kritéria:

* Stručný – jít bez okolků na jádro řešení úlohy (jako bash).
* Jednoduchá syntaxe bez desítek výjimek, různých módů nahrazování, a speciálních významů kombinací hieroglyfů typu $? atd.
* Přes jednoduchou syntaxi by měl mít dostatek prostředků pro stručný zápis nejběžnějších operací, např. práce se seznamy.
* Podpora Javy – ideálně by měl umět pracovat s jakoukoliv třídou Javy, včetně takových jako Thread, Throwable atd.
* Pochopitelně by měl umět OOP, výjimky, kontrolu typů.
* Měl by být konzistentní v konceptech.

Jako bonus potom započítávám tyto vlastnosti:

* Nativní podpora XML – nejlépe jako součást nativní podpory práce se stromovými strukturami.
* Bez nutnosti kompilace.
* Různé vychytávky typu „vše je objekt“, uzávěry (closures), yield.
* Možnost uplatnit změny v kódu za běhu programu.

Kandidáti

Nyní proberme jazyky a technologie, které připadají v úvahu:

* PHP rovnou diskvalifikuji z Těchto důvodů
* Python, resp. Jython
* Groovy
* Scala
* JavaScript
* JavaFX
* …navrhněte: ondra@dynawest.cz

Konkurz

Tímto zahajuji výběrové řízení a začínám se poohlížet po vlastnostech jednotlivých jazyků. Pokud bude čas, rozepíšu se o tom, proč který jazyk ano či ne.

Transport schedule connection search

As a part of my master's thesis, I've written an SQL stored procedure that searches for connections in the transport schedules, using the breadth-first search paradigm.

Here is brief overview how it works.

Persistent tables

CREATE TABLE `mhd_jizdy` (
  `id` smallint(5) unsigned NOT NULL default '0',
  `nazev` varchar(140) NOT NULL,
  `id_trasa` smallint(5) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `id_trasa` (`id_trasa`),
  CONSTRAINT `fk_jizdy_id_trasa` FOREIGN KEY (`id_trasa`) REFERENCES `mhd_trasy` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB ROW_FORMAT=FIXED COMMENT='Jizdy - seskupení jednotlivých zastávek na dané trase pr';

CREATE TABLE `mhd_jizdy_stani` (
  `id_jizda` smallint(5) unsigned NOT NULL,
  `poradi` smallint(5) NOT NULL,
  `cas1` time default NULL,
  `cas2` time default NULL,
  `id_trasa_uzel` int(10) unsigned default NULL,
  PRIMARY KEY  USING BTREE (`id_jizda`,`poradi`),
  KEY `id_trasa_uzel` (`id_trasa_uzel`),
  CONSTRAINT `fk_jizdy_stani_id_jizda` FOREIGN KEY (`id_jizda`) REFERENCES `mhd_jizdy` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_jizdy_stani_id_uzel` FOREIGN KEY (`id_trasa_uzel`) REFERENCES `mhd_trasy_uzly` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=cp1250 ROW_FORMAT=FIXED COMMENT='Jednotlivé zastávky jízd';

CREATE TABLE `mhd_linky` (
  `id` smallint(5) unsigned NOT NULL auto_increment,
  `nazev` varchar(60) NOT NULL,
  `cislo` tinyint(3) unsigned default NULL,
  PRIMARY KEY  (`id`),
  KEY `cislo` (`cislo`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=cp1250;

CREATE TABLE `mhd_trasy` (
  `id` smallint(5) unsigned NOT NULL auto_increment,
  `id_linka` smallint(5) unsigned default NULL,
  `popis` char(96) NOT NULL default '',
  `id_zast_prvni` int(10) unsigned NOT NULL,
  `id_zast_posledni` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `id_linka` (`id_linka`),
  CONSTRAINT `fk_trasy_id_linka` FOREIGN KEY (`id_linka`) REFERENCES `mhd_linky` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=158 DEFAULT CHARSET=cp1250 COMMENT='Jednotlive verze linek';

CREATE TABLE `mhd_trasy_uzly` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `id_trasa` smallint(5) unsigned NOT NULL default '0',
  `poradi` smallint(5) NOT NULL default '0',
  `id_zast` smallint(5) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `id_trasa_poradi` USING BTREE (`id_trasa`,`poradi`),
  KEY `id_zast` (`id_zast`),
  CONSTRAINT `fk_trasy_uzly_id_trasa` FOREIGN KEY (`id_trasa`) REFERENCES `mhd_trasy` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_trasy_uzly_id_zast` FOREIGN KEY (`id_zast`) REFERENCES `mhd_zast` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=12555 DEFAULT CHARSET=cp1250 ROW_FORMAT=FIXED COMMENT='Trasy linek (pro kazdou verzi linky a pro kazdy smer)';

CREATE TABLE `mhd_zast` (
  `id` smallint(5) unsigned NOT NULL auto_increment,
  `nazev` varchar(60) NOT NULL default '',
  `nazev_plny` varchar(60) NOT NULL,
  `lat` double default NULL,
  `lon` double default NULL,
  `lat_norm` double default NULL,
  `lon_norm` double default NULL,
  `gps_loc` varchar(60) NOT NULL,
  `prezdivka` varchar(60) NOT NULL default '',
  `id_skup` smallint(5) unsigned default NULL,
  `obec` varchar(45) NOT NULL,
  `obec_cast` varchar(45) NOT NULL,
  `pozn` varchar(45) NOT NULL,
  `id_nn` int(10) unsigned default NULL,
  PRIMARY KEY  (`id`),
  KEY `id_skup` (`id_skup`),
  CONSTRAINT `fk_id_skup` FOREIGN KEY (`id_skup`) REFERENCES `mhd_zast_skup` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=540 DEFAULT CHARSET=cp1250 ROW_FORMAT=DYNAMIC COMMENT='Zastavky';

Procedures

mhd_VyhledejSpoje – searching the connection

Usage:

You ask for the fastest connection from the station #115 to the station #465 from now on, using at the most 2 changes (3 transport vehicles).

CALL mhd_VyhledejSpoje(115, 465, NOW(), 3);

Result:

Source code:

CREATE DEFINER=`root`@`localhost` PROCEDURE `mhd_VyhledejSpoje`(
  idZastFrom INT UNSIGNED,
  idZastTo INT UNSIGNED,
  whn DATETIME,
  iMaxRounds TINYINT UNSIGNED
)
BEGIN

  /**
    Usage:
    CALL mhd_VyhledejSpoje(115, 465, NOW(), 3);
  */

-- snipped --


END $$

Vyhledávání spojů v jízdních řádech

Persistent tables

CREATE TABLE `mhd_jizdy` (
  `id` smallint(5) unsigned NOT NULL default '0',
  `nazev` varchar(140) NOT NULL,
  `id_trasa` smallint(5) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `id_trasa` (`id_trasa`),
  CONSTRAINT `fk_jizdy_id_trasa` FOREIGN KEY (`id_trasa`) REFERENCES `mhd_trasy` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB ROW_FORMAT=FIXED COMMENT='Jizdy - seskupení jednotlivých zastávek na dané trase pr';

CREATE TABLE `mhd_jizdy_stani` (
  `id_jizda` smallint(5) unsigned NOT NULL,
  `poradi` smallint(5) NOT NULL,
  `cas1` time default NULL,
  `cas2` time default NULL,
  `id_trasa_uzel` int(10) unsigned default NULL,
  PRIMARY KEY  USING BTREE (`id_jizda`,`poradi`),
  KEY `id_trasa_uzel` (`id_trasa_uzel`),
  CONSTRAINT `fk_jizdy_stani_id_jizda` FOREIGN KEY (`id_jizda`) REFERENCES `mhd_jizdy` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_jizdy_stani_id_uzel` FOREIGN KEY (`id_trasa_uzel`) REFERENCES `mhd_trasy_uzly` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=cp1250 ROW_FORMAT=FIXED COMMENT='Jednotlivé zastávky jízd';

CREATE TABLE `mhd_linky` (
  `id` smallint(5) unsigned NOT NULL auto_increment,
  `nazev` varchar(60) NOT NULL,
  `cislo` tinyint(3) unsigned default NULL,
  PRIMARY KEY  (`id`),
  KEY `cislo` (`cislo`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=cp1250;

CREATE TABLE `mhd_trasy` (
  `id` smallint(5) unsigned NOT NULL auto_increment,
  `id_linka` smallint(5) unsigned default NULL,
  `popis` char(96) NOT NULL default '',
  `id_zast_prvni` int(10) unsigned NOT NULL,
  `id_zast_posledni` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `id_linka` (`id_linka`),
  CONSTRAINT `fk_trasy_id_linka` FOREIGN KEY (`id_linka`) REFERENCES `mhd_linky` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=158 DEFAULT CHARSET=cp1250 COMMENT='Jednotlive verze linek';

CREATE TABLE `mhd_trasy_uzly` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `id_trasa` smallint(5) unsigned NOT NULL default '0',
  `poradi` smallint(5) NOT NULL default '0',
  `id_zast` smallint(5) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `id_trasa_poradi` USING BTREE (`id_trasa`,`poradi`),
  KEY `id_zast` (`id_zast`),
  CONSTRAINT `fk_trasy_uzly_id_trasa` FOREIGN KEY (`id_trasa`) REFERENCES `mhd_trasy` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_trasy_uzly_id_zast` FOREIGN KEY (`id_zast`) REFERENCES `mhd_zast` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=12555 DEFAULT CHARSET=cp1250 ROW_FORMAT=FIXED COMMENT='Trasy linek (pro kazdou verzi linky a pro kazdy smer)';

CREATE TABLE `mhd_zast` (
  `id` smallint(5) unsigned NOT NULL auto_increment,
  `nazev` varchar(60) NOT NULL default '',
  `nazev_plny` varchar(60) NOT NULL,
  `lat` double default NULL,
  `lon` double default NULL,
  `lat_norm` double default NULL,
  `lon_norm` double default NULL,
  `gps_loc` varchar(60) NOT NULL,
  `prezdivka` varchar(60) NOT NULL default '',
  `id_skup` smallint(5) unsigned default NULL,
  `obec` varchar(45) NOT NULL,
  `obec_cast` varchar(45) NOT NULL,
  `pozn` varchar(45) NOT NULL,
  `id_nn` int(10) unsigned default NULL,
  PRIMARY KEY  (`id`),
  KEY `id_skup` (`id_skup`),
  CONSTRAINT `fk_id_skup` FOREIGN KEY (`id_skup`) REFERENCES `mhd_zast_skup` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=540 DEFAULT CHARSET=cp1250 ROW_FORMAT=DYNAMIC COMMENT='Zastavky';

Procedures

mhd_VyhledejSpoje – searching the connection

Usage:

CALL mhd_VyhledejSpoje(115, 465, NOW(), 3);

Result:

CREATE DEFINER=`root`@`localhost` PROCEDURE `mhd_VyhledejSpoje`(
  idZastFrom INT UNSIGNED,
  idZastTo INT UNSIGNED,
  whn DATETIME,
  iMaxRounds TINYINT UNSIGNED
)
BEGIN

  /**
    Usage:
    CALL mhd_VyhledejSpoje(115, 465, NOW(), 3);
  */

-- snipped --


END $$

„Ternary boolean logic“ in SQL – making NULL useful

Typically, SQL's NULL is used as „unknown value“ or „Not Applicable“.

For example, you have a table of users who may provide their e-mail, but if they don't, you store NULL to the e-mail column. That's „uknown“.

Other usage example is a column with last user's forum post. But before his first post, you store NULL there to mark there's no post yet.

That's the basic NULL usage. But it can provide you much more – mainly for complex SELECTs used for searching or for reports.

NULL features and performance

On most enterprise-ready RDBMS's, NULL can be indexed.

On some, it also has some special features like MySQL's PRIMARY KEY AUTO_INCREMENT or TIMESTAMP's auto-updating feature.

Storage space for a NULLable column is usually one bit, but these bits are grouped in a byte-aligned field per row, which means, 1 to 8 null columns will occupy 1 byte per row, 9–16 will take 2 bytes, etc.

SELECTing the rows (not) having a NULL value is usually very quick, because this information is part of the index; more, the database system does not have to sort them internally in a tree index, like it has with concrete values (e.g. numeric ID's).

Basic NULL constructs in SQL

• SELECT a IS NULL
• SELECT a IS NOT NULL
• SELECT ISNULL(a)
• SELECT IFNULL(a, 'default') returns 'default' if a is NULL.
• SELECT COALESCE(a, b, c) returns the first non-NULL value.

NULL semantics

Null has these characteristics:

Most operations with null result in a NULL.

• '' + NULL ⇒ NULL
• 0 + NULL ⇒ NULL
• NOT NULL ⇒ NULL

Comparison

Comparison always returns NULL, even for NULL = NULL. You have to use IS NULL.

• '' = NULL ⇒ NULL
• 0 = NULL ⇒ NULL
• NULL = NULL ⇒ NULL

There's also a null-safe comparison operator, <=>, which behaves like this:

• 1 <=> 1 ⇒ 1
• NULL <=> NULL ⇒ 1
• 1 <=> NULL ⇒ 0

There are some NULL-related functions, which provide you a convenient way when you need to work with multiple nullable values:

• COALESCE()
• IFNULL()
• NULLIF()

Boolean logic

Boolean logic is specific. Here, NULL is considered as meaning „Unknown value“.

NULL AND 1 ⇒ NULL NULL AND 0 ⇒ 0 NULL OR 1 ⇒ 1 NULL OR 0 ⇒ NULL NULL XOR 1 ⇒ NULL NULL XOR 0 ⇒ NULL

This makes sence, because with AND, you can tell the expression will be false if one of operands is false. However, if one operand is true, it's the other one which determines the result; so if it's unknown, the result is unknown as well.
Similarly it works with OR. For XOR, it's always NULL, as both operands are always needed.

Using NULL features to your advantage

Without learning the basics above, the NULL behavior might seem strange to you and can lead to an unexpected behavior. But if you keep that in mind, you can make your complex SELECTs less complex by using some of the following principles.

To be written later.

Stored Procedure call stack in INFORMATION_SCHEMA

This is a MySQL feature request submitted as bug report #36106.

Having a call stack available in stored procedures would make debugging much much easier. It could be a table like this:

----------------------------------------------
depth INT  routine_name VARCHAR   routine_type ENUM  started TIMESTAMP ...
1          nn_TeachXOR            PROCEDURE          128458937
2          nn_ComputeNet          PROCEDURE          128458938
3          nn_ComputeLevel        PROCEDURE          128458938
...

With such table, one could do profiling,

SELECT UNIX_TIMESTAMP() - started AS duration
  FROM INFORMATION_SCHEMA.CALLSTACK
  ORDER BY depth DESC LIMIT 1;

structured logging, e.g.:

INSERT INTO log
  SELECT CONCAT( REPEAT('  ', ), '+--  ', routine_name) AS str
  ORDER BY depth ASC;

and more…

MySQL: Result set in a @variable

A solution of passing a result set between stored routines is frequently asked question on the official MySQL's Stored Procedures forum (see e.g. Calling PROCEDURE on SELECT output, Use resultset from sproc in select statement or in another sproc, and others).

Since MySQL does not support passing result sets in stored procedures, common practice is to use TEMPORARY TABLES when working with larger data sets.

CREATE PROCEDURE `lib_Explode`(sSepar VARCHAR(255), saVal TEXT)
body: BEGIN

  DROP TEMPORARY TABLE IF EXISTS lib_Explode;
  CREATE TEMPORARY TABLE lib_Explode(
    `pos` int unsigned NOT NULL PRIMARY KEY auto_increment,
    `val` VARCHAR(255) NOT NULL
  ) ENGINE=Memory COMMENT='Explode() results.';

  SET @saTail = saVal, @iSeparLen = LENGTH( sSepar );

  create_layers:
  WHILE @saTail != '' DO
    # Get the next value
    SET @sHead = SUBSTRING_INDEX(@saTail, sSepar, 1);
    SET @saTail = SUBSTRING( @saTail, LENGTH(@sHead)+1+@iSeparLen );
    INSERT INTO lib_Explode SET val = @sHead;
  END WHILE;

END $$

Although, this approach introduces a drawback – you have to care about table names. There are three options:

• Keep track of the table names used. Leads to total chaos in table names.
• Use dynamic prepared statements. Leads to awful code.
• Use some naming conventions. Leads to many table renamings.

(I personally prefer the last option.)

The much more ellegant solution would be if a variable could hold a result set. Let's see what would be possible with such feature.

Storing a result set into a @variable

First, let's create a 3 × 2 table:

CREATE TABLE table1 ( a INT, b INT );
INSERT INTO table1 VALUES (1,2), (3,4), (5,6);
SELECT * FROM table1;

The table now looks like this:

Currently, we can select a single value into a variable:

SELECT a FROM table1 INTO @var LIMIT 1;

But what much more cool would be if we could store a result set:

##  Create the result set and store it in a variable.
SELECT * FROM table1 INTO @resultset;
  -- or --
SET @resultset = (SELECT * FROM table1);

MySQL would recognize that we are assigning a result set. After executing, @resultset would be of special type RESULTSET. Then, we could pass it to a stored procedure.

Passing a result set as a parameter of a stored procedure

CREATE PROCEDURE StoredProcedure( rsData RESULTSET ) BEGIN
  SELECT a + b FROM rsData;
END $$

##  Pass the result set into a stored procedure.
CALL StoredProcedure( @resultset );

In the stored procedure, a RESULTSET param could act exactly as a TEMPORARY table.

Retrieving a result set from a stored procedure

##  Retrieving a result set from a stored procedure
CALL StoredProcedure() INTO @result;

-- or

SET @result = (CALL StoredProcedure());

After executing, @result would be of special type RESULTSET and contain the result of the first SELECT performed in a procedure.

Retrieving multiple result sets from a stored procedure

Multiple results from a CALL could also be fetched:

CREATE PROCEDURE StoredProcedure( rsData RESULTSET ) BEGIN
  SELECT a + b FROM rsData;
  SELECT a * b FROM rsData;
END $$

##  Retrieving multiple result sets from a stored procedure
CALL StoredProcedure() INTO @result1, @result2;

So. This is my suggestion of how to deal with result sets in stored procedures. Hopefully I will see it in MySQL some day – it would reduce the code of my procedures near to half.

Java objects in Stored Procedures (sci-fi)

The notice of having possibility to call Java from stored procedures made me like looking forward to great future :-) The best thing I can imagine now is that I could have Java object references in user variables, something like:

SET @jDate = java::new('java.util.Date');
SET @sDate = @jDate.toString();

Or, letting my imagination fly far away:

SET @jWebService = java::jndi('java:comp/env/ws/weather');
SET @jResults = @jWebService.getWeatherIn('Brno');
SET @jResIter = @jResults.getIterator();
WHILE @jResIter.hasNext() DO
  SET @jRes = @jResIter.next();
  ...
END WHILE;

Concerning web services, there already is a solution: UDF (user defined functions). There are some already working, hopefully their authors will release their source some day.

Feature request discussion: Result sets stored in @variables

Hello,

I got over several weeks of stored routines developement, and the most missed feature was a seamless mechanism for
- calling routines with a resultset in a parameter,
- retrieving a result set coming stored procedure,
- and eventually, for keeping a resultset somewhere without need to create a table.

All of this would be possible if we could store a resultset in a variable. So I have stated a feature request, not yet submitted to a buglist. First I would like to know others' oppinions and suggestions, because I am not much familiar with other DBMS.

I guess that such functionality would not be easy to implement and is not likely to appear soon. But it would be a giant leap for MySQL's stored procedures programming.

Here is a brief overview:

Create the result set and store it in a variable.

SELECT * FROM table1 INTO @resultset;
– or – SET @resultset = (SELECT * FROM table1);

Pass the result set into a stored procedure.

CALL StoredProcedure( @resultset );

Retrieving a result set from a stored procedure

CALL StoredProcedure() INTO @result; – or – SET @result = (CALL StoredProcedure());

More detailed formatted draft is here: http://ondra.zizka.cz/…ariable.texy

Now – what do you think? Would this ease your SP developement? Is there some better concept?

Thanks for comments.

Stored Procedure call stack in INFORMATION_SCHEMA

This is a MySQL feature request submitted as bug report #36106.

Having a call stack available in stored procedures would make debugging much much easier. It could be a table like this:

----------------------------------------------
depth INT  routine_name VARCHAR   routine_type ENUM  started TIMESTAMP ...
1          nn_TeachXOR            PROCEDURE          128458937
2          nn_ComputeNet          PROCEDURE          128458938
3          nn_ComputeLevel        PROCEDURE          128458938
...

With such table, one could do profiling,

SELECT UNIX_TIMESTAMP() - started AS duration
  FROM INFORMATION_SCHEMA.CALLSTACK
  ORDER BY depth DESC LIMIT 1;

structured logging, e.g.:

INSERT INTO log
  SELECT CONCAT( REPEAT('  ', ), '+--  ', routine_name) AS str
  ORDER BY depth ASC;

and more…

SQL koutek

Jednou z mých oblíbených technologií jsou relační databáze a jazyk SQL. Líbí se mi, jak elegantně se dají některé typy problémů pomocí SQL řešít – zejména grafové úlohy. Například výpočet neuronové sítě v tradičních programovacích jazycích sestává obvykle ze tří vnořených cyklů, v SQL je to cyklus pouze jeden, zbytek vyjádříte v SELECTech. Ale o tom až dále.

Mojí oblíbenou databází je MySQL 5.0. Schválně uvádím verzi, protože jsem si ji oblíl až na základě možnosti psát tzv. Stored procedures.

V průběhu doby jsem při řešení různých projektů vytvořil užitečné procedury a funkce, které usnadňují vývoj procedur pro MySQL a mohly by se kdekomu hodit.

Omlouvám se těm, co nehovoří anglicky, ale zbytek SQL sekce bude většinou právě v angličtině.

• MySQL Stored Procedures
• Implementation of neural network in SQL – perceptron and backpropagation

Krom toho střádám i své touhy, co by se mohlo (a mělo) v MySQL objevit:

• Java objects in Stored Procedures (sci-fi)

ALTER TABLE … RENAME [OVERWRITE] [TO] to overwrite existing table

This is a MySQL feature request submitted as bug report #36271.

Since MySQL does not support passing result sets in stored procedures, common practice is to use TEMPORARY TABES when working with larger data sets. (From what did I see, this is the most frequently discussed topic at Stored Procedures forum.

To create generally usable API, one needs to define the names of temp tables with input and output data. Thus, passing temp tables between different procedures needs to rename them. To make it safely, one has to try the drop before altering:

CALL PrepareSomeData(...);  -- This creates TEMP TABLE prepared_data
DROP TEMPORARY TABLE IF EXISTS input_data;
ALTER TABLE prepared_data RENAME TO input_data;
CALL ProcessData(...);      -- This procedure looks for the input_data table.

And this has to be done many times.

My suggestion is to introduce the OVERWRITE option to the ALTER TABLE statement:

CALL PrepareSomeData(...);  -- This creates TEMP TABLE prepared_data
ALTER TABLE prepared_data RENAME OVERWRITE TO input_data;
CALL ProcessData(...);      -- This procedure looks for the input_data table.

The alter with OVERWRITE would check whether a table with such name exists, and if so, it would drop it first.

(Of course, better solution of the problem itself would be a possibility to pass result sets in variables, but as I got to know, that is planned in far future.)

MySQL Stored Procedures Library

Solution of common elementary tasks that MySQL does not solve natively.

What's & Why's

During a developement of custom projects I've written some useful procedures and functions, which ease the developement of procedures for MySQL and somebody could find it handy. So I publish them here.

The goal of this library is not to force MySQL to be as much Perl-ish as possible (nothing against Perl itself and nothing against Giuseppe Maxia's MySQL General Purpose Stored Routines Library). That is, by the way, a reason why do I not implement any „array“ (means hash) stuff

• the best array available in MySQL is a – (moment of surprise) – table.

The goal of this library is to provide the simplest and fastest possible solutions for common tasks, for which MySQL itself does not have a solution of it's own (yet).

That is:

• sequence generators
• many string functions
• geographical transformations (MySQL's current GIS support must be a joke)
• checking the metadata about temporary tables (which are invisible both in SHOW TABLES and in INFORMATION_SCHEMA)
• logging (MySQL does not support direct logging from Stored Procedures)
• RaiseError() to raise custom error in stored procedure

There are some others which are not addressed by this library yet, because I didn't need to solve them since I develop for MySQL 5.0.

The Principles

First, let's say something about the principles how these procedures work.

I try to KISS. Why should anyone bother with another vast non-standard API? Thus, anything that could be done easily with existing MySQL's tools is left outside the scope of this library.

I do not use the support of UDFs (not that I would not like to). Most hosting providers are not likely compile/install custom C code into their production MySQL server.

On the other side, I develop the library for the latest GA version of MySQL. That is, as soon as anything is available in GA MySQL, it can be (mis)used in this library. Like with the BENCHMARK() „function“: It is not a real function in MySQL 5.0, so you can not pass the number of repeats as an expression. But in MySQL 5.1, this is fixed; and I have already working procedure lib_GenerateSequence(), which is about 30% faster thanks to it.

Most often, their output is a table. And because MySQL does not give any support for direct passing the result set between procedures, I've got used to a custom of storing the result into a TEMPORARY TABLE named exactly like the procedure name. E.g:

CALL lib_Explode(',', 'coma, separated, data, 3.14');
SELECT * FROM lib_Explode;
DROP TEMPORARY TABLE IF EXISTS lib_Explode; -- Optional, read below.

Result:

You don't have to care much about releasing (DROPping), unless you work with enormous data volumes – the procedure overwrites the table next time called, and the TEMPORARY tables are released automatically upon the thread end (i.e. after the client disconnects).

<strike>Procedures which return a table, should return it every time; when an error occurs, the table is empty.</strike> (This wasn't a good idea.)

About the code below

For brevity, I do not „prefix“ the code with DELIMITER, but if you do not use the complete script, be sure to use the code this way:

DELIMITER $$
...
END; $$

The code may be cluttered with some debug calls commented out.
Generally, procedures should be bug-free, as I use them quite much.

Stored procedures library categories

Currently the stored procedures available divide into these categories:

• Logging – a set of procedures that dump info to a log table lib_logg. Inspired a bit with log4j.
• Strings – functions that work with text.
  • lib_Expode() – similar to PHP's expode().
  • F1() - F6() – „sprintf()“ with 1 to 6 parameters.
• NULL-safe_functions
  • NS() – NULL-safe
  • NSB() – NULL-safe boolean
  • NSQ() – NULL-safe quoted
• Utilities – custom useful procedures (sequence generator, temp table check).
  • lib_TemporaryTableHasColumns( sTable, saColumns, OUT BOOL bHas ) – TEMP TABLE checking
  • lib_GenerateSe­quence( iFrom INTEGER, iTo INTEGER, iStep INTEGER ) – linear number sequence generator
• Geographic functions and procedures – convert coordinates between geographic coordinate systems, compute distances between places on earth. (Not available yet).

Logging

CREATE TABLE lib_logg (
  `whn` datetime NOT NULL,
  `thread` int(10) unsigned NOT NULL,
  `level` enum('info','warn','error') NOT NULL default 'info',
  `str` text NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

DELIMITER $$
CREATE PROCEDURE  Logg( sStr TEXT ) BEGIN
  INSERT DELAYED INTO lib_logg SET str = sStr, whn = NOW(), thread = CONNECTION_ID();
END; $$

CREATE PROCEDURE  Logg_warn( sStr TEXT ) BEGIN
  INSERT DELAYED INTO lib_logg SET level = 'warn', str = sStr, whn = NOW(), thread = CONNECTION_ID();
END; $$

CREATE PROCEDURE  Logg_error( sStr TEXT ) BEGIN
  INSERT DELAYED INTO lib_logg SET level = 'error', str = sStr, whn = NOW(), thread = CONNECTION_ID();
END; $$

CREATE PROCEDURE LoggP( sRout VARCHAR(255), sStr TEXT ) BEGIN
  INSERT DELAYED INTO lib_logg SET rout = sRout, str = sStr, whn = NOW(), thread = CONNECTION_ID();
END $$

CREATE PROCEDURE LoggEnter(sRout VARCHAR(255)) BEGIN
  INSERT INTO lib_logg SET rout = SUBSTRING_INDEX(sRout,'(',1),
    level='enter', str = sRout, whn = NOW(), thread = CONNECTION_ID();
END $$

CREATE PROCEDURE LoggLeave(sRout VARCHAR(255)) BEGIN
 INSERT INTO lib_logg SET rout = sRout, level='leave', str = NULL, whn = NOW(), thread = CONNECTION_ID();
END $$

String functions

lib_Explode() – similar to PHP's Explode()

Usage:

CALL lib_Explode( ',' ,  'Ahoj, jak se máte, 212' );

Result:

Source code:

CREATE PROCEDURE lib_Explode( sSepar VARCHAR(255), saVal TEXT )
body:
BEGIN

  DROP TEMPORARY TABLE IF EXISTS lib_Explode;
  CREATE TEMPORARY TABLE lib_Explode(
    `pos` int unsigned NOT NULL auto_increment,
    `val` VARCHAR(255) NOT NULL,
    PRIMARY KEY  (`pos`)
  ) ENGINE=Memory COMMENT='Explode() results.';

  IF sSepar IS NULL OR saVal IS NULL THEN LEAVE body; END IF;

  SET @saTail = saVal;
  SET @iSeparLen = LENGTH( sSepar );

  create_layers:
  WHILE @saTail != '' DO

    # Get the next value
    SET @sHead = SUBSTRING_INDEX(@saTail, sSepar, 1);
    SET @saTail = SUBSTRING( @saTail, LENGTH(@sHead) + 1 + @iSeparLen );
    INSERT INTO lib_Explode SET val = @sHead;

  END WHILE;

END; $$

Note: If you are looking for something like lib_Implode(), that is, PHP's explode() equivalent, see MySQL's „aggregate functioni“ GROUP_CONCAT():

CALL lib_GenerateSequence( 5, 15, 2 );
SELECT GROUP_CONCAT(i SEPARATOR ' a ') AS vysledek FROM lib_GenerateSequence;

Výsledek:

F1() - F6() – "sprintf()" with 1 to 6 parameters

In the F1 function the %s string is replaced. Other functions replace strings {1} to {6}.

Usage:

SELECT F2( 'Ve městě {1} je {2} stupňů.', 'Praha', 31.2 ) FROM DUAL;

Result:

Source code:

CREATE FUNCTION  F1 ( sFormat TEXT, sPar1 TEXT )
    RETURNS text CHARSET utf8 BEGIN
  RETURN REPLACE(sFormat, '%s', sPar1);
END; $$

CREATE FUNCTION  F2 ( sFormat TEXT, sPar1 TEXT, sPar2 TEXT )
    RETURNS text CHARSET utf8 BEGIN
  RETURN REPLACE( REPLACE(sFormat, '{1}', sPar1) , '{2}', sPar2);
END; $$

CREATE FUNCTION  F3 ( sFormat TEXT, sPar1 TEXT, sPar2 TEXT, sPar3 TEXT )
    RETURNS text CHARSET utf8 BEGIN
  RETURN REPLACE( REPLACE( REPLACE(sFormat, '{3}', sPar3), '{1}', sPar1), '{2}', sPar2);
END; $$

CREATE FUNCTION  F4 ( sFormat TEXT, sPar1 TEXT, sPar2 TEXT, sPar3 TEXT,
       sPar4 TEXT ) RETURNS text CHARSET utf8 BEGIN
  RETURN REPLACE( REPLACE( REPLACE( REPLACE( sFormat,
    '{1}', sPar1), '{2}', sPar2), '{3}', sPar3), '{4}', sPar4);
END; $$

CREATE FUNCTION  F5 ( sFormat TEXT, sPar1 TEXT, sPar2 TEXT,  sPar3 TEXT,
       sPar4 TEXT, sPar5 TEXT  ) RETURNS text CHARSET utf8 BEGIN
  RETURN REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( sFormat,
    '{1}', sPar1), '{2}', sPar2), '{3}', sPar3), '{4}', sPar4), '{5}', sPar5);
END; $$

CREATE FUNCTION  F6 ( sFormat TEXT, sPar1 TEXT, sPar2 TEXT,  sPar3 TEXT,
       sPar4 TEXT, sPar5 TEXT, sPar6 TEXT  ) RETURNS text CHARSET utf8 BEGIN
  RETURN REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( sFormat,
    '{1}', sPar1), '{2}', sPar2), '{3}', sPar3), '{4}', sPar4), '{5}', sPar5), '{6}', sPar6);
END; $$

NULL-safe functions

Somethimes you need to ensure that no parameter of a function is NULL, e.g. in case of calling CONCAT(), which returns NULL if any of parameters is NULL. Writing IFNULL(@param,'') every time can be tedious; so here are some convenience functions. Mainly for debugging purposes.

Funkce hlavně pro potřeby logování.

NS() – NULL-safe

NSB() – NULL-safe boolean

NSQ() – NULL-safe quoted

If the only argument is NULL, returns the string „NULL“. Otherwise returns:

• NS(): argument.
• NSB(): String „TRUE“, if the argument evaluates as true, or „FALSE“ in the opposite case.
• NSQ(): argument surrounded by quotes.

Usage:

CALL Logg( F1('Computing level %s.', NS(@iLevel)) )

Source code:

CREATE FUNCTION NS( s VARCHAR(255) )
RETURNS varchar(255) CHARSET utf8 BEGIN
  RETURN IF( s IS NULL, 'NULL', CONCAT('"',s,'"') );
END $$

CREATE FUNCTION NSB(b BOOLEAN) RETURNS char(5)
CHARSET utf8 NO SQL DETERMINISTIC BEGIN
  RETURN IF( b IS NULL, 'NULL', IF(b,'TRUE','FALSE') );
END $$

CREATE FUNCTION NSQ(s VARCHAR(255)) RETURNS varchar(255)
CHARSET utf8 NO SQL DETERMINISTIC BEGIN
  RETURN IF( s IS NULL, 'NULL', CONCAT('"',s,'"') );
END  $$

Utilities

lib_GenerateSe­quence – linear sequence generator

Usage:

CALL lib_GenerateSequence( 5, 15, 2 )

Result:

Source code:

DELIMITER $$

CREATE PROCEDURE lib_GenerateSequence(
  iFrom INTEGER, iTo INTEGER, iStep INTEGER )
body:
BEGIN

  DROP TEMPORARY TABLE IF EXISTS lib_GenerateSequence;
  CREATE TEMPORARY TABLE lib_GenerateSequence (i INTEGER NOT NULL);

  ##  Exit if one of arguments is NULL.
  IF iFrom IS NULL OR iTo IS NULL OR iStep IS NULL
    THEN LEAVE body; END IF;


  SET @iMin = iFrom;
  SET @iMax = iTo;

  InsertLoop: LOOP
    IF @iMin > @iMax THEN LEAVE InsertLoop; END IF;
    INSERT INTO lib_GenerateSequence SET i = @iMin;
    SET @iMin = @iMin + iStep;
  END LOOP;

END; $$

lib_Temporary­TableHasColum­ns( sTable, saColumns, OUT BOOL bHas ) – TEMP TABLE checking

Checks whether a TEMP TABLE exists and has the given columns.

Usage:

CALL lib_TemporaryTableHasColumns('table', 'col1,col2,col3', @bHas);
SELECT @bHas;

Source code:

CREATE lib_TemporaryTableHasColumns (
  IN sTable VARCHAR(255), IN saColumns VARCHAR(255), OUT bHas BOOLEAN)
BEGIN


  # Unknown column 'col1' in 'where clause'
  DECLARE EXIT HANDLER FOR 1054 SET bHas = FALSE;

  # Table 'table' doesn't exist
  DECLARE EXIT HANDLER FOR 1146 SET bHas = FALSE;

  # You have an error in your SQL syntax;  ')' at line 1
  # Happens when the columns definition is wrong (e.g. "a,b,")
  -- With this, we could have nice message:
  -- "Unknown column 'lib_TemporaryTableHasColumns(): Bad columns definition [a,b,].' in 'field list'."
  -- But it throws 1054 and is handled by previously declared handler -> no message.
  #DECLARE EXIT HANDLER FOR 1064 CALL ExecuteQuery(F1('SELECT 1+`lib_TemporaryTableHasColumns(): Bad columns definition [%s].` FROM (SELECT 1) AS x', saColumns));
  DECLARE EXIT HANDLER FOR 1064 BEGIN
    SET bHas = FALSE;  ## Known issue: Does not change the out value. (MySQL bug?)
    CALL ExecuteQuery(F1('SELECT 1 FROM `lib_TemporaryTableHasColumns(): Bad columns definition [%s].`', saColumns));
  END;



  #CALL ExecuteQuery(F2('SELECT TRUE INTO @lib_TTHasColumns_foo FROM {1} WHERE TRUE OR FALSE IN ({2}) LIMIT 1', sTable, saColumns));
  #SET @sSQL = F2('SELECT TRUE INTO @lib_TTHasColumns_foo FROM {1} WHERE TRUE OR FALSE IN ({2}) LIMIT 1', sTable, saColumns);
  SET @sSQL = F2('SELECT {2} FROM {1} LIMIT 0', sTable, saColumns);
  PREPARE stmt_name FROM @sSQL;
  DEALLOCATE PREPARE stmt_name;
  SET bHas = TRUE;

END $$

lib_RaiseError() – Similar to RaiseError() in other DBs

Raises an error („throws an exception“) with a message containing the given string.

Usage:

CALL lib_RaiseError('Disaster!');
##  Says:   PROCEDURE neural_network.error: Disaster! You're doomed, solution does not exist

Source code:

CREATE DEFINER=`root`@`localhost` PROCEDURE `lib_RaiseError`( sError VARCHAR(255) )
BEGIN
  ## PROCEDURE neural_network.Ahoj lidi does not exist
  CALL ExecuteQuery(F1('CALL `error: %s   You\'re doomed, solution`', sError));

  ##  Usage:

END $$