web2py

Databázová abstrakční vrstva

Úvodem

Web2py obsahuje databázovou abstrakční vrstvu (DAL), což je API rozhraní, které přiřazuje objekty Pythonu databázovým objektům, jako jsou dotazy (queries), tabulky nebo záznamy (věty, records). DAL dynamicky v reálném čase sestavuje dialekt SQL jazyka pro zvolený databázový back-end, takže nemusíte psát SQL kód a učit se odchylky syntaxe SQL dialektů. Aplikace bude přenositelná na různé databázové stroje. Seznam podporovaných databází je v tabulce níže. Podívejte se na Web2py stránku nebo zkuste zjistit ve Web2py Google Group diskuzi, zda existuje další požadovaný adaptér. Google NoSQL propíráme jako speciální případ v Kapitole 13. V oddíle o specifických odchylkách a problémech konkrétních databázových strojů na konci této kapitoly se můžete dočíst více o zvláštnostech jednotlivých databází.

Binární distribuce pro Windows pracuje ihned (out of the box) s SQLite, MSSQL, Postgresql a MySQL. Binární distribuce pro Mac pracuje ihned s SQLite. Chcete-li používat jiný databázový back-end, použijte Web2py ze zdrojových kódů (sources) a pokud není driver přímo k dispozici, doinstalujte jej (jeho Python ovladač, typicky pomocí pip).

Jakmile je nainstalován potřebný driver, spusťte Weby2py (ze zdrojových kódů) a driver se zobrazí jako dostupný. Zde je seznam možných driverů:

sqlite3, pymysql, pg8000 a imaplib jsou k dispozici jako součást Web2py. Podpora MongoDB je experimentální. IMAP umožňuje používat DAL pro přístup k IMAP.

DAL: Rychlá prohlídka

Web2py má následující třídy, které tvoří DAL:

DAL reprezentuje databázové připojení (connection). Například:

db = DAL('sqlite://storage.db')

Table reprezentuje databázovou tabulku. Table neinstanciujete přímo. Místo toho používáte DAL.define_table, která vytvoří instanci Table.

db.define_table('mytable', Field('myfield'))

Nejdůležitější metody třídy Table jsou:

.insert, .truncate, .drop a .import_from_csv_file.

Field reprezentuje databázové pole. Instanciuje se předáním jako argument pro metodu DAL.define_table.

DAL Rows (řádky, záznamy) je objekt, který vrátí výběr z databáze (select). Představme si ho jako seznam Row (jednotlivých řádků, záznamů):

rows = db(db.mytable.myfield!=None).select()

Row obsahuje jednotlivá pole (jejich hodnoty).

for row in rows:
    print row.myfield

Query je objekt, který odpovídá SQL klauzuli "where":

myquery = (db.mytable.myfield != None) | (db.mytable.myfield > 'A')

Set je objekt, který odpovídá vymezuje množinu záznamů (zatímco v Rows objektu jsou již konkrétní vybrané záznamy). Nejdůležitější metody Set objektu jsou count, select, update a delete. Například:

myset = db(myquery)
rows = myset.select()
myset.update(myfield='nejakahodnota')
myset.delete()

Expression znamená něco jako orderby nebo groupby výrazy. Třída Expression je tvořena pomocí Field. Tady je příklad:

myorder = db.mytable.myfield.upper() | db.mytable.id
db().select(db.table.ALL, orderby=myorder)

Použití DAL samostatně (stand-alone)

Web2py DAL (databázová abstrakční vrstva) může být použita z prostředí mimo Web2py

from gluon import DAL, Field
# případně také: from gluon.validators import *

DAL konstruktor

Základní použití:

>>> db = DAL('sqlite://storage.db')

Tím je vytvořeno připojení do databáze a to je uloženo do globální proměnné db.

Kdykoli můžete zjistit řetězec připojení

                                                                                                                                                                                                                                          
>>> print db._uri
sqlite://storage.db

a jméno databáze

>>> print db._dbname
sqlite

Řetězec připojení nazýváme _uri, protože se jedná o instanci Uniform Resource Identifier (jednotný identifikátor zdroje).

DAL umožňuje vytvořit více připojení ke stejné databázi nebo k různým databázím, dokonce současně k databázím různých typů (k různým databázovým strojům). Prozatím předpokládejme jediné připojení k databázi, protože to je obvyklá situace.

Signatura DAL (parametry konstruktoru)

DAL(
    uri='sqlite://dummy.db',
    pool_size=0,
    folder=None,
    db_codec='UTF-8',
    check_reserved=None,
    migrate=True,
    fake_migrate=False,
    migrate_enabled=True,
    fake_migrate_all=False,
    decode_credentials=False,
    driver_args=None,
    adapter_args=None,
    attempts=5,
    auto_import=False,
    bigint_id=False,
    debug=False,
    lazy_tables=False,
    db_uid=None,
    do_connect=True,
    after_connection=None,
    tables=None,
    ignore_field_case=True,
    entity_quoting=False,
    table_hash=None)

Řetězce připojení (connection strings) (uri parametr)

Připojení k databázi je vytvořeno instanciováním DAL třídy:

>>> db = DAL('sqlite://storage.db', pool_size=0)

db není klíčové slovo. Je to obyčejná lokální proměnná pro objekt připojení DAL a můžete ji tedy pojmenovat jakkoli. Konstruktor DAL vyžaduje jeden povinný argument, což je connection string (řetězec připojení). Je to vlastně ve Web2py jediný kód, který závisí na zvoleném databázovém back-endu. Zde jsou příklady řetězců připojení pro jednotlivé databáze (ve všech případech předpokládáme, že databáze běží na localhost na svém standardním portu a má jméno "test"):

Pro Google/NoSQL varianta +ndb zapne NDB. NDB používá Memcache buffer k přednačtení dat, ke kterým se často přistupuje. To je zcela automatické a probíhá na úrovni databáze, nikoli na úrovni Web2py.

V SQLite je databáze tvořena jediným souborem a ten je vytvořen automaticky, jestliže zatím neexistuje. Tento soubor je vždy uzamčen po dobu každého přístupu. V případě MySQL, PostgreSQL, MSSQL, FireBird, Oracle, DB2, Ingres a Informix je potřeba prázdnou databázi "test" mimo Web2py (ve vhodném databázovém manažeru). Jakmile je vytvořeno připojení k databázi, Web2py v ní bude vytvářet nebo rušit tabulky, nebo měnit jejich strukturu.

Je také možné jako řetězec připojení použít None. V tom případě se DAL nepřipojí k žádné databázi, ale API je i tak k dispozici pro testování. Příklady uvádíme v Kapitole 7.

Někdy také může být potřeba sestavovat SQL příkazy jako kdybyste připojení měli, aniž by bylo skutečné připojení vytvořeno. To lze udělat takto:

db = DAL('...', do_connect=False)

Pak můžete volat _select, _insert, _update nebo _delete a tím sestavit SQL příkaz, ale nelze volat select, insert, update nebo delete. Ve většině případů lze použít do_connect=False dokonce bez požadovaného databázového ovladače.

Web2py defaultně používá kódování znaků utf8. Pokud byste pracovali s databází, která ukládá v jiném kódování, použijte volitelný parametr db_codec:

db = DAL('...', db_codec='latin1')

Jinak dostanete chyby UnicodeDecodeError.

Sdílení připojení (Connection pooling)

DAL konstruktor má dále parametr pool_size s defaultní hodnotou: nula.

Vzhledem k tomu, že docela dlouho trvá vytvořit připojení pro každý request (přístup na server), Web2py implementuje mechanismus Sdílení připojení. Poté, co je připojení vytvořeno, stránka je sestavena a předána, a transakce je potvrzena, tak se připojení, místo aby bylo uzavřeno, ponechá v poolu (v zásobě otevřených připojení). Jakmile přijde následující http request, Web2py zkusí vzít volné připojení z poolu a použít jej pro novou transakci. Teprve není-li v zásobě žádné volné připojení, vytvoří se připojení nové.

Po startu serveru je zásobník (pool) prázdný. Pool za provozu roste až na minimum z hodnoty pool_size a ze skutečného nejvyššího počtu souběžných přístupů. Dejme tomu, že pool_size=10, ale na náš server nikdy nepřišlo více než 5 souběžných požadavků - pool poté bude obsahovat 5 připojení. Při nastavení pool_size=0 se sdílení připojení nepoužije.

Připojení v poolu mohou být využita z různých vláken (threads), přesněji řečeno z různých, ale nikoli současně běžících vláken. Jeden Web2py proces má jen jeden zásobník připojení (pokud se používá).

Parametr pool_size je ignorován, pokud pracujeme s SQLite nebo s Google App Engine. V případě SQLite je tomu tak proto, že by uvedený postup neznamenal žádný přínos (úsporu času).

Chyby připojení (parametr: attempts)

Jestliže se Web2py nepodaří připojit k databázi, počká 1 vteřinu a zkusí to znova, postupně pětkrát, než ohlásí chybu. Při sdílení připojení pomocí zásobníku připojení (connection pooling) se může stát, že otevřené, ale nějakou dobu nepoužívané připojení uzavře databázový stroj. Pomocí opakovaných pokusů o připojení může Web2py opravit tuto situaci. Počet pokusů o připojení je zadán parametrem attempts.

Odložené definice tabulek (Lazy Tables)

Nastavení lazy_tables = True umožňuje podstatné zrychlení zpracování. Viz níže: lazy tables Definice se použije odloženě, až v okamžiku, kdy bude potřeba. Tomu se říká lazy definice (doslova: líné). Globálně je můžeme používat pomocí DAL(..., lazy_tables=True). Tabulky (jim odpovídající datové struktury) se skutečně vytvoří jen tehdy a až tehdy, když se do nich bude přistupovat.

Aplikace bez modelu

Použití Web2py adresáře model pro definici schématu databáze je pohodlné a produktivní. Za pomoci odložených definic (lazy tables), je výkonnost většinou přijatelná i pro rozsáhlé aplikace. Mnozí zkušení vývojáři tento režim používají v produkčním prostředí.

Nicméně je také možné definovat DAL tabulky až podle potřeby v akcích (funkcích) kontroléru nebo v modulech. Může to být vhodné, jestliže rozsah složitosti definic tabulek jde až za možnosti lazy tabulek.

Takový režim se nazývá "model-less" development (vývoj bez modelu databáze). Tím se sníží rozsah kódu, spouštěného (pokaždé) z adresáře model. Je to jen pojmenování tohoto stylu. Neznamená to vůbec, že by byl opouštěn koncept modelu, kontrolérů a šablon (views).

Automatické vykonání kódu z adresáře model pro vás zajistí:

1. modely se vykonají automaticky s každým přístupem (request-em)
2. modely použijí globální rozsah platnosti proměnných (global scope)

Modely jsou také užitečné pro interaktivní shell (terminálové) sezení, kdy Web2py spustíme s volbou příkazového řádku (commandline option) -M.

Také zvažte udržovatelnost: Ostatní Web2py vývojáři budou obvykle očekávat, že definici schématu databáze najdou v adresáři model.

Chcete-li použít styl "model-less" vývoje, přebíráte více odpovědnosti za potřebná pomocná řešení. Provedete definice tabulek jen tam, kde je právě potřebujete, ale musíte zajistit přístup do globálního rozsahu platnosti proměnných (global scope) pomocí objektu current. (jak popisuje dokumentace ve 4. kapitole sharing global with the current object )

Například typická model-less aplikace ponechá definici databázového připojení v modelu, ale definice tabulek bude dělat až podle okamžité potřeby ve funckích kontrolérů.

Typickým řešením je přesunout samotné definice tabulek do modulů (python soubory v adresáři modules).

Jestliže se funkce, která definuje skupinu tabulek, nazývá define_employee_tables() a je umístěna v modulu "table_setup.py", tak váš kontrolér, který chce pracovat s nějakou tabulkou z evidence zaměstnanců (employes) třeba editací v SQLFORM formuláři, musí zavolat nejprve funkci define_employee_tables() a až potom začít pracovat s daty. Funkce define_employee_tables() potřebuje přístup objektu připojení do databáze (db), aby v něm definovala tabulky. K tomu potřebujete správně zacházet s objektem current v modulu, který obsahuje funkci define_employee_tables() (více si přečtěte u objektu current).

Replikované databáze

Jako první argument DAL(...) lze uvést i seznam URI (adres). V takovém případě se Web2py pokusí připojit ke každé z nich. Obvyklým účelem je možnost použití více databázových serverů a rozložení zátěže mezi ně. Typický use case (scénář použití) je zde:

db = DAL(['mysql://...1','mysql://...2','mysql://...3'])

V tom případě se DAL pokusí připojit k první databázi, když se to nepovede, tak ke druhé, a nakonec ke třetí databázi. To lze použít k distribuci zátěže v master-slave konfiguraci. Povíme si o tom více v Kapitole 13 při probírání otázek škálovatelnosti (scalability).

Vyhrazená slova

check_reserved je další argument, který můžeme předat DAL konstruktoru. Tím požadujeme, aby se zkontrolovalo, že názvy tabulek a sloupců nekolidují s vyhrazenými SQL jmény v cílových databázových strojích. Defaultní hodnota je None.

Možná hodnota parametru je seznam (list) jmen databázových adaptérů.

Jméno adaptéru je totéž jako řetězec, který se používá v DAL řetězci připojení (connection stringu). Takže chcete-li zkontrolovat kolize vůči vyhraženým jménům PostgreSQL a MSSQL databází, zadáte řetězec takto:

db = DAL('sqlite://storage.db',
         check_reserved=['postgres', 'mssql'])

DAL pak jména zkontroluje v zadaném pořadí.

Navíc jsou k dispozici volby "all" nebo "common". Zadáte-li "all", kontrola se provede proti všem známým rezervovaným jménům všech SQL strojů. Zadáte-li "common", zkontrolují se jen hlavní klíčová slova jako SELECT, INSERT, UPDATE, apod.

Můžete také zkontrolovat kolize nevyhražených jmen. V takovém případě přidejte _nonreserved ke jménu adaptéru. Např.:

check_reserved=['postgres', 'postgres_nonreserved']

Dále uvedené adaptéry podporují kontrolu vyhrazených jmen:

Omezovače SQL eintit (database quoting) a ignorovaná velikost písmen v názvech

Můžete použít explicitní omezovače pro SQL jména na úrovni DAL. Můžete použít stejná jména v Pythonu a v DB schématu.

Příklad:

db = DAL('postgres://...', ..., ignore_field_case=False, entity_quoting=True)
db.define_table('table1', Field('column'), Field('COLUMN'))
print db(db.table1.COLUMN != db.table1.column).select()

Ostatní parametry DAL konstruktoru

Umístění adresáře databases

Jedná se o adresář, kde budou vytvořeny soubory .table (metadata o tabulkách databázového schématu). Uvnitř Web2py aplikace je tento adresář nastaven automaticky. Při práci s DAL mimo Web2py aplikaci zadejte tento adresář explicitně.

Defaultní nastavení pro migraci

Migrace je podrobněji popsána níže u tabulek (Tables) table migrations. Parametry pro nastavení migrace DAL konstruktoru jsou boolean hodnoty, které určují defaultní a globální nastavení.

migrate = True určuje defaultní migrační chování pro tabulky, které nemají migrační chování explicitně nastaveno

fake_migrate = False určuje defaultní fake-migrate chování pro tabulky, které nemají fake-migrate chování explicitně nastaveno

migrate_enabled = True - nastavením False můžeme zcela zakázat všechny migrace (bez ohledu na explicitní nastavení u tabulek)

fake_migrate_all = False - nastavením True vynutíme fake-migraci všech tabulek (bez ohledu na explicitní nastavení u tabulek)

Experiment s interaktivním sezením s Web2py pomocí terminálu

S DAL API (s rozhraním databázové vrstvy) můžete experimentovat pomocí Web2py shellu. Příklad: python web2py.py -a "<heslo>" -M -S "<aplikace>" command line option

Zápisem "db<enter>" zjistěte, zda je vytvořeno spojení, a/nebo jej vytvořte znova, např. do SQLite:

>>> db = DAL('sqlite://storage.db')

Nyní je připojení vytvořeno a uloženo v proměnné db. Z příkazového řádku můžete nyní zkoušet příkazy, popisované v dalším textu.

konstruktor třídy Table (definice tabulky)

define_table

Field

signatura metody define_table

Tabulky se v DAL definují pomocí metody define_table:

>>> db.define_table('person', Field('name'),
    id=id,
    rname=None,
    redefine=True
    common_filter,
    fake_migrate,
    fields,
    format,
    migrate,
    on_define,
    plural,
    polymodel,
    primarykey,
    redefine,
    sequence_name,
    singular,
    table_class,
    trigger_name)
 

Ta definuje, ukládá a vrací objekt třídy Table (Tabulka), v tomto případě zvaný "osoba", což je tabulka s polem (sloupcem) "jmeno". Tento objekt je nadále dostupný i jako db.person, takže obvykle není potřeba si návratovou hodnotu uložit.

id: Notes about the primary key

V seznamu polí neuvádějte "id", protože Web2py vytvoří toto pole vždy automaticky. Každá tabulka bude mít pole "id" defaultně. Je to integer auto-inkrementované pole (obvykle počínaje od 1) pro unikátní identifikaci každého záznamu a pro reference (odkazy) mezi tabulkami datového modelu. Neboli "id" je primární klíč tabulky. (Poznámka: číslování id od 1 závisí na databázovém stroji. Např. tomu tak není na Google App Engine NoSQL.)

Lze také definovat jinak pojmenované pole typu type='id' a Web2py takové pole použije stejně jako auto-increment id. To se nedoporučuje používat, ale můžete to potřebovat, pokud se budete připojovat k tabulkám jiné (ne Web2py) aplikace (legacy tables), která používá jinak pojmenovaný primární klíč. S nějakými omezeními je možné použít i jiné primární klíče pomocí primarykey parametru. primarykey Bude to krátce vysvětleno níže.

plural a singular

Smartgrid objekty mohou potřebovat znát plné jméno tabulky v jednotném (singular) a množném (plural) čísle. Je tedy lepší tato pojmenování (tedy jména tabulky, jak je má vidět uživatel) zadat.

redefine

Tabulky lze definovat jen jednou, avšak je možné re-definovat existující tabulku:

db.define_table('osoba', Field('jmeno'))
db.define_table('osoba', Field('jmeno'), redefine=True)

To může spustit migraci (automatický převod struktury tabulky), pokud se seznam polí proti původnímu změnil.

Reprezentace záznamu

Je nepovinné, ale doporučuje se zadat výraz pro reprezentaci jednotlivého záznamu (tedy určit, jak má být zobrazen uživateli):

>>> db.define_table('osoba', Field('jmeno'), format='%(jmeno)s')

or

>>> db.define_table('osoba', Field('jmeno'), format='%(jmeno)s %(id)s')

nebo i složitěji pomocí funkce:

>>> db.define_table('osoba', Field('jmeno'),
       format=lambda r: r.jmeno or 'anonymní uživatel')

Atribut format se používá ze dvou důvodů:

• Aby reprezentoval odkazované záznamy ve výběrových drop-down prvcích.
• Aby inicioval atribut db.jinatabulka.osoba.represent pro všechny tabulky, které jsou pomocí cizího klíče na tuto tabulku vázány. To znamená, že v tabulkovém zobrazení (např. pomocí SQLTABLE) se nezobrazí odkazovaná id, ale záznam tak, jak jej chcete (nastavením: format=) vidět.

rname

rname nastavuje (případně odlišné) jméno tabulky na databázovém stroji. Pokud jej použijete, stane se z Web2py jména tabulky vlastně alias, kdežto rname je skutečné jméno, které se použije při sestavování dotazu (query) pro backend. Abychom si ukázali nějaký případ, kdy je tento parametr potřeba, uvažujme o plně kvalifikovaných jménech na MSSQL serveru:

rname = 'db1.dbo.table1'

primarykey: Podpora pro tabulky jiných existujících aplikací

primarykey umožňuje pracovat s existujícími primárními klíči, včetně složených z více polí. Více viz Legacy Databases níže.

migrate, fake_migrate

migrate nastaví, zda a jak se bude provádět migrace této tabulky. viz Table Migrations níže.

table_class

Jestliže definujete vlastní Table třídu jako podtřídu, zděděnou z gluon.dal.Table, můžete ji zde zadat. Tím je umožněno rozšiřovat nebo přepisovat metody standardní Table třídy. Příklad:

table_class=MyTable

sequence_name

(volitelně) Jméno sekvence (pokud sekvence podporuje databáze). Může vytvořit SEQUENCE (číslováno od 1 a inkrementováno po +1) nebo použít jiné sekvence při práci s tabulkami jiných aplikací (legacy tables). Jestliže je to potřeba, Web2py si vytvoří sekvence automaticky (číslované od 1).

trigger_name

(volitelně) Souvisí s sequence_name. Má význam pro některé databázové stroje, pokud nepodporují auto-inkrementální integer pole.

polymodel

Pro Google App Engine

on_define

on_define je callback funkce, která se spustí v okamžiku, kdy je instanciována lazy tabulka. Je také zavolána přímo při define_table(), jestliže tabulka není lazy. Umožňuje to provést dynamické změny po připojení tabulky, aniž bychom ztráceli výhodu odložené instanciace.

Příklad:

 db = DAL(lazy_tables=True) 
 db.define_table('person', Field('name'), Field('age','integer'), 
    on_define=lambda table: [ 
            table.name.set_attributes(requires=IS_NOT_EMPTY(),default=''), 
            table.age.set_attributes(requires=IS_INT_IN_RANGE(0,120),default=30), 

Tento příklad ukazuje, jak on_define použít, ale není ve skutečnosti nutný. Jednoduché requires hodnoty u Field definic budou tak jako tak "lazy". Ale když requires bude mít Set objekt jako první argument, jako je tomu u IS_IN_DB validátoru, sestaví dotaz (query) např.

db.nejakatabulka.nejakepole == nejaka_hodnota

Lazy tabulky, zásadní zlepšení výkonu (performance boost)

Web2py modely se vykonají při každém přístupu dříve, než se provede akce (funkce) kontroleru, takže všechny tabulky se definují při každém přístupu (requestu). Ale ne všechny tabulky pro zpracování každého přístupu potřebujeme, takže je možné nějaký čas ušetřit, jestliže se pro aktuálně nepotřebné tabulky část zpracování neprovede. Podmíněné modely (conditional models, kapitola 4) mohou pomoci, ale Web2py získá podstatné zvýšení výkonu používáním lazy tabulek. Znamená to, že vytvoření nebo zpřístupnění tabulky je odloženo až na okamžik, kdy se na tabulku opravdu odkážeme. Povolení lazy definic je zajištěno inicializací v DAL konstruktoru. Vyžaduje nastavit parametr DAL(..., lazy_tables=True). To je jedna z nejdůležitějších optimalizací, které můžete ve Web2py provést.

Přidávání atributů polím a tabulkám

Chcete-li přidat extra atribut polím tabulky (zde trochu předbíháme, ale o polích budeme mluvit vzápětí), můžete udělat jednoduše toto:

db.table.field.extra = {}

"extra" není klíčové slovo; je to nějaký váš atribut, který jste připojili k objektu pole. Totéž můžete dělat pro tabulky, ale aby nedošlo k záměně/konfliktu se jménem pole, musí vámi přidané vlastnosti začínat podtržítkem:

db.table._extra = {} 

Field constructor

Zde jsou defaultní hodnoty parametrů konstruktoru třídy Field:

Field(name, type='string', length=None, default=None,
      required=False, requires='<default>',
      ondelete='CASCADE', notnull=False, unique=False,
      uploadfield=True, widget=None, label=None, comment=None,
      writable=True, readable=True, update=None, authorize=None,
      autodelete=False, represent=None, compute=None,
      uploadfolder=None,
      uploadseparate=None, uploadfs=None,
      rname=None)

Ne všechny argumenty jsou relevantní pro kterýkoli typ pole. Např. "uploadfield" a "authorize" zase jen k polím typu "upload". "ondelete" má význam jen pro pole typů "reference" nebo "upload".

• length určuje maximální délku (počet znaků) "string", "password" nebo "upload" pole. Jestliže length není zadáno, použije se defaultní hodnota, jenže pro ni není zaručována zpětná kompatibilita. Abyste zabránili nechtěným migracím při upgradu verze, doporučujeme zadat length vždy explicitně.
• default nastavuje defaultní hodnotu pole. Ta se použije při INSERTu, jestliže hodnota pole není v příkazu určena explicitně. Také se použije pro předvyplnění formulářů, vytvořených podle tabulky pomocí SQLFORM. Nemusí to být jen pevná hodnota, ale také to může být funkce (typicky lambda funkce), která vrátí hodnotu potřebného typu. V takovém případě se funkce zavolá pro každý jednotlivý záznam, i když je vkládáno více záznamů v jediné transakci.
• required říká DAL vrstvě, že není dovolen INSERT do tabulky, dokud hodnota pole není explicitně určena.
• requires určuje validátor nebo seznam validátorů. Nepoužívá jej DAL, ale používá jej formulář: SQLFORM. Defaultní validátory podle typu pole uvádíme v dalším oddíle.

requires=... je použit na úrovni formulářů, required=True na úrovni DAL (pro insert), kdežto notnull, unique a ondelete na úrovni databáze. Mohou se někdy zdát redundantní, ale je potřeba pamatovat na uvedený rozdíl.

• ondelete se přeloží do SQL příkazu na klauzuli "ON DELETE". Defaultně je nastaven na "CASCADE". Tím je databázi řečeno, aby, když maže záznam, prošla a zrušila i záznamy jiných tabulek, jejichž cizí klíč na rušený záznam odkazuje. Takové chování potlačíme nastavením ondelete na "NO ACTION" nebo "SET NULL".
• notnull=True přidá do SQL příkazu klauzuli "NOT NULL". Blokuje přidávání null hodnot do příslušného pole databáze.
• unique=True se přeloží v SQL příkazu na klauzuli "UNIQUE" a tím způsobí kontrolu, že hodnoty v tomto poli (sloupci) tabulky jsou unikátní. To je rovněž zajištěno na úrovni databáze.
• rname podobně jako u tabulky může přejmenovat jméno pole z pohledu Web2py na jiné (skutečné) jméno, používané v databázi. V tom případě tedy toto jméno bude použito v SQL příkazech, kdežto Web2py jméno se vlastně změní na alias.
• uploadfield má význam jen pro pole typu "upload". Pole typu "upload" ukládá normálně jen jméno souboru, zatímco samotný soubor ukládá jinam, defaultně do adresáře "uploads/" souborového systému. Jestliže je nastaveno uploadfield, pak je soubor uložen do blob pole té samé tabulky a hodnota uploadfield určuje jméno tohoto blob pole. Probereme to podrobněji v souvislosti s SQLFORM.
• uploadfolder je defaultně None nebo "uploads/" adresář aplikace. Pokud se uploadované soubory ukládají jako samostatné soubory (tedy nikoli do blob pole), můžete určit odlišnou cestu, kam soubory ukládat. Například:

Field(...,uploadfolder=os.path.join(request.folder,'static/temp'))

bude soubory ukládat do "web2py/applications/mojeaplikace/static/temp".

• uploadseparate, pokud bude nastaveno na True, způsobí ukládání souborů do separátních podadresářů adresáře pro upload. Tím je optimalizováno ukládání velkého množství souborů. POZOR: Není dovoleno hodnotu uploadseparate změnit dodatečně: rozbili byste tím odkazy na existující uploadované soubory. Pokud by se to stalo, je možné soubory přesunout a tím problém opravit, ale tento postup zde neuvádíme.
• uploadfs umožňuje uvést jiný souborový systém pro ukládání souborů, včetně Amazon S3 úložiště (storage) nebo vzdáleného (remote) SFTP úložiště. K tomu musí být instalován PyFileSystem. uploadfs musí odkazovat na PyFileSystem.
  PyFileSystem
  uploadfs
• widget musí být některý z dostupných widget objektů, včetně uživatelsky přidaných widgetů, například: SQLFORM.widgets.string.widget. Seznam widgetů, které jsou k dispozici, bude probrán později. Každý typ pole má přiřazen svůj defaultní widget.
• label je řetězec (nebo něco, co může být na řetězec serializováno, např. HTML helper), který se použije pro pojmenování pole v automaticky generovaných formulářích.
• comment je řetězec (nebo opět to, co může být na řetězec serializováno, např. HTML helper) s poznámkou nebo komentářem pro toto pole, což se zobrazí v automaticky generovaných formulářích typicky napravo od input prvku.
• writable říká, zda je prvek, zobrazený ve formulářích, přístupný pro změny (zápis).
• readable určuje zobrazení prvku ve formulářích. Pole, které není readable ani writable, se nezobrazí ve formulářích pro přidání záznamu a editaci.
• update znamená defaultní hodnotu, která přepíše obsah pole při ukládání záznamu.
• compute je volitelná funkce, která se vykoná při ukládání (insert nebo update) do záznamu - do pole se uloží výsledek této funkce. Jako argument je funkci předán slovník (dictionary) aktuálního záznamu. V něm ovšem chybí položky s aktuálními hodnotami "compute" polí.
• authorize lze použít k vynucení kontroly přístupu pro jednotlivé pole, a sice pouze u "upload" polí. Podrobněji to probereme u Autentikace a Autorizace.
• autodelete určuje, zda bude při mazání záznamu automaticky smazán i separátně uložený soubor. Vztahuje se pouze k "upload" polím. Ovšem pro záznamy, které smaže samotný databázový stroj navíc na základě CASCADE operace, se ignoruje nastavené autodelete a odpovídající soubor zůstane nesmazaný. Diskuzi o náhradním řešení lze hledat ve Web2py Google Group.
• represent může být None nebo to může být funkce, která hodnotu pole převede na jinou reprezentaci pro uživatele. Příklady:

db.mojetabulka.jmeno.represent = lambda jmeno, row: jmeno.capitalize()
db.mojetabulka.jine_id.represent = lambda id, row: row.myfield
db.mojetabulka.nejake_uploadpole.represent = lambda value, row:     A('stáhnout', _href=URL('download', args=value))

Field types

Decimal vyžaduje a vrací hodnoty stejně jako Decimal objekty z Python modulu decimal. SQLite s nimi neumí pracovat, proto interně je typ decimal zpracován jako double. (n,m) udávají počet číslic celkem a počet číslic za desetinnou tečkou.

big-id a big-reference podporují jen některé databázové stroje a jsou experimentální. Není důvod je normálně použít, vyjma legacy tables (práce s tabulkami jiných aplikací). DAL konstruktor má parametr bigint_id a jestliže mu předáme True, změní pole typů id a reference na big-id a big-reference.

list:<type> pole jsou zvláštní případ. Jsou určena k využití jistých výhod denormalizačních vlastností NoSQL (v případě Google App Engine NoSQL polí typu ListProperty a StringListProperty) a tyto vlastnosti dávají k dispozici i podporovaným relačním databázím. V relačních databázích jsou "list" pole uložena jako pole typu text. Položky v nich jsou odděleny pomocí | s tím, že případný znak | uvnitř položky je escapován na ||. O těchto typech polí pojednáme ve zvláštním oddíle.

json pole může ukládat jakýkoli json serializovatelný objekt. Je speciálně vytvořeno kvůli práci s MongoDB a v rámci přenositelnosti jej mají k dispozici i ostatní databázové adaptéry.

"blob" pole jsou rovněž speciální. Defaultně jsou binární data zakódována do base64 a pak teprve uložena do pole v databázi, a obdobně jsou dekódována při čtení z databáze. Tím se sice zabere o 25% více místa, ale dává to dvě výhody. V průměru to sníží množství dat, přenesených mezi Web2py a databázovým serverem, a komunikace tím nebude závislá na specifických escape konvencích (ošetření problematických znaků) konkrétního back-endu (databázového stroje).

Dodatečné změny údajů o tabulkách a polích

Většina atributů polí může být změněna i dodatečně, později než jsou definována pole:

db.define_table('osoba', Field('jmeno', default=''), format='%(jmeno)s')
db.osoba._format = '%(jmeno)s/%(id)s'
db.osoba.jmeno.default = 'anonym'

(poznamenejme, že atributy tabulek prefixujeme podtržítkem, aby se zabránilo možnému konfliktu se jmény polí).

Můžete získat seznam (list) tabulek, definovaných pro dané databázové připojení:

>>> print db.tables
['osoba']

Podobně můžete získat seznam polí, definovaných pro danou tabulku:

>>> print db.osoba.fields
['id', 'jmeno']

Typ tabulky - instance třídy Table:

>>> print type(db.person)
<class 'pydal.objects.Table'>

jiný způsob, jak použít tabulku:

>>> print type(db['osoba'])
<class 'pydal.objects.Table'>

Podobně můžete přistupovat k polím více způsoby:

>>> print type(db.osoba.jmeno)
<class 'pydal.objects.Field'>
>>> print type(db.osoba['jmeno'])
<class 'pydal.objects.Field'>
>>> print type(db['osoba']['jmeno'])
<class 'pydal.objects.Field'>

Pro konkrétní pole můžete získat aktuální nastavení jeho atributů:

>>> print db.osoba.jmeno.type
string
>>> print db.osoba.jmeno.unique
False
>>> print db.osoba.jmeno.notnull
False
>>> print db.osoba.jmeno.length
32

a to včetně tabulky, do které patří (jako odkaz nebo jako řetězec) a včetně připojení, k němuž definice patří:

>>> db.osoba.jmeno._table == db.osoba
True
>>> db.osoba.jmeno._tablename == 'osoba'
True
>>> db.osoba.jmeno._db == db
True

Pole má také metody. Některé se používají pro sestavení dotazů (queries) a seznámíme se s nimi je později. Speciální metodou objektu pole je validate, které provede validátor(y) pole.

print db.osoba.jmeno.validate('Honza')

což vrátí vektor (tuple) (value, error). error je None, jestliže validace prošla bez chyb.

Migrace

define_table kontroluje, zda příslušná tabulka existuje nebo ne. Pokud ne, sestaví SQL příkaz pro její vytvoření a provede jej. Jestliže tabulka už existuje, ale liší se od zadané definice, sestaví se SQL příkaz pro úpravu struktury tabulky a provede se. Pokud pole nezměnilo název, ale změnilo typ, dojde k pokusu převést data. (Pokud tomuto chcete zabránit, je potřeba redefinovat strukturu tabulky postupně dvakrát, napoprvé odstraněním pole (a uložených dat v něm) a napodruhé jeho opětovným nadefinováním, takže bude vytvořeno jako prázdné.) Pokud tabulka existuje ve správné struktuře, neprovede se nic, ale ve všech případech se vytvoří objekt db.person jako reprezentace databázové tabulky.

Toto chování označujeme jako "migrace". Web2py loguje všechny migrace a pokusy o ni do souboru "databases/sql.log".

Prvním argumentem define_table je vždy jméno tabulky. Následující další nepojmenované argumenty jsou definice polí (Field). Funkce má také nepovinný pojmenovaný parametr "migrate":

>>> db.define_table('osoba', Field('jmeno'), migrate='osoba.table')

Jméno, uvedené v "migrate", je jméno souboru (který vznikne ve složce "databases" aplikace) a v němž si Web2py udržuje své interní informace o této tabulce. Tyto soubory jsou velice důležité a nikdy je neodstraňujte, dokud v databázi existují odpovídající tabulky. Jen v případě, že jste tabulku už zrušili (drop) lze smazat i soubor s interními informacemi a jménem zrušené tabulky (pokud by zůstal). Defaultně je "migrate" nastaveno na True. Web2py pak sestaví jméno souboru s interními informacemi z hashe připojení a ze jména tabulky. Nastavíme-li migrate=False, není migrace povolena a Web2py předpokládá, že tabulka existuje v databázi a že má správnou strukturu, a sice alespoň ta pole, která jsou uvedena v define_table.

Je doporučeno migrační tabulky explicitně pojmenovat. Migrační tabulky dvou různých tabulek v aplikaci pochopitelně nesmějímít stejná jména.

Také DAL třída má parametr "migrate", který určuje defaultní hodnotu pro "migrate" při volání define_table. Například:

>>> db = DAL('sqlite://storage.db', migrate=False)

nastaví defaultní hodnotu "migrate" na False při každém volání db.define_table, v němž chybí argument "migrate". Kontrola existence a struktury tabulky je tím potlačena.

Poznamenáváme, že Web2py migruje jen nové sloupce, odstraněné sloupce a změny v typu sloupce (to poslední s výjimkou SQLite). Web2py nemigruje změněné atributy jako default, unique, notnull, nebo ondelete.

Migrace lze striktně zakázat všem tabulkám najednou:

db = DAL(..., migrate_enabled=False)

To je doporučené chování a nastavení, jestliže dvě aplikace sdílejí stejnou databázi. Jen jedna z nich by měla mít povoleny migrace a provádět je, druhá (nebo ostatní) by měly mít migrace zakázané.

Oprava poškozených migrací

Existují dva běžné problémy s migracemi a k nim vhodné postupy, jak je řešit.

Jeden problém je specifický pro SQLite. SQLite nevynucuje typy sloupců a neumí fyzicky odstranit sloupec. Znamená to, že máte-li pole (sloupec) typu string a odstraníte jej, z databáze fyzicky odstraněn nebude, pouze se nepoužívá. Jestliže stejně pojmenovaný sloupec přidáte znovu s jiným typem, dejme tomu datetime (nebo to celé provedete v jediném migračním kroku), skončíte s datetime sloupcem, který ve své části obsahuje řetězce (string). Web2py neví, co z databáze obdrží, takže zkusí načíst záznamy a havaruje.

Jestliže Web2py vrátí chybu v gluon.sql.parse funkci při načtení (select) záznamů, jedná se o tento problém: poškozená data ve sloupci v důsledku uvedeného problematického chování.

Řešením je aktualizovat v opětovně přidaném (nebo v jediném kroku přetypovaném) sloupci data hodnotou správného typu. Např. můžeme přetypovaný sloupec ve všech záznamech aktualizovat hodnotou None.

Jiný problém je obecnější, ale typický pro MySQL. MySQL nedovoluje více než jeden ALTER TABLE v transakci. Znamená to, že Web2py musí rozdělit složitější transakce na jednoduché transakční kroky (s nějvýše jedním ALTER TABLE) a každý z nich commitovat individuálně. Může tak dojít k tomu, že zatímco část celého postupu projde a je commitována, následující část selže a zanechá tak Web2py v poškozeném stavu. Z jakého důvodu může dílčí transakce havarovat? Protože například zahrnuje změnu typu string na datetime a spolu s ní se Web2py pokusí data převést a havaruje při převodu. Co to pro Web2py bude dále znamenat? Bude zmatené v tom, jaká přesně struktura tabulky zůstala v databázi.

Opravu lze provést povolením falešných (fake) migrací:

db.define_table(...., migrate=True, fake_migrate=True)

Tím se přebudují Web2py metadata (migrační tabulka) podle definice tabulky. Vyzkoušejte různé definice a zjistěte, která pracuje správně (definici před migrací a definici po migraci). Jakmile budete úspěšní, odstraňte zase fake_migrate=True atribut.

Před pokusem o opravu poškozené migrace je vhodné si zálohovat soubory "applications/yourapp/databases/*.table".

Migrační problémy lze také opravit pro všechny tabulky najednou:

db = DAL(..., fake_migrate_all=True)

To havaruje, jestliže model obsahuje tabulky, které v databázi právě neexistují, ale může to při řešení problému pomoci.

Řízení migrace - shrnutí

Logiku různých argumentů migrace pomůže pochopit tento pseudo-kód:

if DAL.migrate_enabled and table.migrate:
   if DAL.fake_migrate_all or table.fake_migrate:
       perform fake migration
   else:
       perform migration

insert

Do určené tabulky můžete vkládat záznamy:

>>> db.person.insert(name="Alex")
1
>>> db.person.insert(name="Bob")
2

Insert vrátí unikátní "id" záznamu, který byl právě vytvořen.

Můžete vymazat obsah tabulky neboli zrušit všechny záznamy a resetovat čítač přidělovaných id:

>>> db.person.truncate()

Když nyní zopakujete přidání, čítač začne znova přidělovat id od 1 (což je back-end specifické a neplatí na Google NoSQL):

>>> db.person.insert(name="Alex")
1

Poznamenejme, že pro truncate lze zadat argumenty, např. říci SQLite, že má restartovat čítač id.

db.person.truncate('RESTART IDENTITY CASCADE')

Argumenty jsou v cílovém SQL a tím pádem specifické pro konkrétní databázový stroj.

Web2py má také metodu pro hromadný import (bulk_insert)

>>> db.person.bulk_insert([{'name':'Alex'}, {'name':'John'}, {'name':'Tim'}])
[3,4,5]

Argumentem je seznam (list) slovníků (dictionary). Pro každý slovník ze seznamu se importuje jeden záznam. Jako seznam (list) jsou vrácena id přidaných záznamů. Na podporovaných relačních databázích není tento import žádným přínosem ve srovnání s cyklem a prováděním jednotlivých importů. Ale na Google App Engine NoSQL dosáhnete podstatného zvýšení rychlosti.

commit a rollback

create, drop, insert, truncate, delete, nebo update operace nejsou potvrzeny, dokud nepoužijete příkaz commit. V modelu, kontrolérech i šablonách (view) se o to Web2py postará za vás. Při změnách, provedených z modulů, to musíte udělat explicitně:

>>> db.commit()

Ověřte si to tak, že přidáte záznam:

>>> db.person.insert(name="Bob")
2

a revertujete, neboli budete ignorovat všechny operace, provedené od předchozího commitu:

>>> db.rollback()

Když nyní zopakujete insert, čítač znova vrátí 2, protože předchozí insert byl odvolán.

>>> db.person.insert(name="Bob")
2

Kód v modelech, controllerech a view je obalen Web2py kódem, který vypadá zhruba takto:

try:
     provést všechny modely, funkci controlleru a view
except:
     rollback všech připojení
     zaznamenat stack volání do logu
     vystavit chybový ticket uživateli
else:
     commit všech připojení
     uložit cookies, sessiony a vrátit sestavenou stránku

Z toho důvodu není potřebné explicitně ve Web2py po provedení modelu, kontroléru a view, volat commit nebo rollback, leda tehdy, když chcete potvrzovat změny dat po menších krocích. Nicméně, pokud jste nějaké změny provedly z modulů, zavolejte .commit() explicitně.

Hrubé SQL příkazy (Raw SQL)

Měření času provedení příkazů

Web2py automaticky měří čas vykonání SQL příkazů. Proměnná db._timings je seznam (list) vektorů (tuple). Každý vektor obsahuje cílový SQL příkaz, jak byl předán databázovému driveru, a čas, který zabralo jeho vykonání. Z diagnostických důvodů si toto můžete zobrazit ve view pomocí toolbaru:

{{=response.toolbar()}}

executesql

DAL umožňuje vykonávat i explicitní SQL příkazy (tedy nejen automaticky sestavené SQL příkazy).

>>> print db.executesql('SELECT * FROM person;')
[(1, u'Massimo'), (2, u'Massimo')]

V takovém případě DAL neparsuje a nepřevádí vrácené hodnoty a formát tak závisí na konkrétním databázovém ovladači. Takové použití obvykle není potřeba pro SELECTy, ale je typické např. pro SQL příkazy pro práci s indexy. executesql má 4 volitelné argumenty: placeholders, as_dict, fields a colnames. placeholders je volitelná sekvence hodnot pro náhradu zástupných symbolů (placeholders) ve vašem SQL příkazu nebo (pokud to databázový stroj podporuje) slovník (dictionary) s klíči, které odpovídají zástupným symbolům v SQL příkaze.

Jestliže as_dict je nastaveno na True, výsledný kurzor, jak jej vrátí DB driver, bude zkonvertován na seznam (list) slovníků (dictionary), jejichž klíči jsou jména db polí. Výsledky, vrácené při as_dict = True jsou stejné, jako když aplikujete .as_list() na výsledky normálního DAL selectu.

[{field1: value1, field2: value2}, {field1: value1b, field2: value2b}]

V opačném případě (defaultně) získáte seznam (list) vektorů (tuple).

Argument fields je seznam (list) DAL Field objektů, které odpovídají polím, vráceným z databáze. Field objekty mají být použity v definici jedné nebo více DAL tabulek. fields seznam může také obsahovat jako položku DAL tabulku nebo tabulky, a to místo polí nebo spolu s dalšími poli. Chceme-li uvést jedinou tabulku, nemusí ani být parametrem seznam, ale přímo objekt tabulky. Tam, kde je použit objekt tabulky, získají se Field objekty z definice tabulky.

Místo určení argumentu fields lze použít argument colnames, což je seznam (list) jmen polí ve formátu jmenotabulky.jmenopole. I v tomto případě by měly být použity pole a tabulky, které jsou definovány v DAL objektu připojení (obvykle pojmenovaném: db).

Je i možné zadat obojí současně, fields i příslušné colnames. V takovém případě může kromě Fields objektů argument fields obsahovat DAL Expression objekty (výrazy). K Field objektům ve "fields" odpovídající colnames musí stále být ve formátu jmenotabulky.jmenopole, kdežto pro Expression objekty může být použito libovolné jméno.

fields a colnames musí být ve stejném pořadí, jako jsou pole ve výsledném kurzoru, jak jej vrátí databáze.

Poznamenejme ještě, že DAL Table objekty, na které odkazují fields a colnames, mohou být fiktivní (dummy) tabulky, které nemusí představovat žádnou skutečnou tabulku v databázi.

_lastsql

Ať už byl SQL příkaz zadán manuálně nebo byl sestaven pomocí DAL, vždy můžete zjistit poslední provedený SQL příkaz pomocí db._lastsql. To je užitečné při ladění:

>>> rows = db().select(db.person.ALL)
>>> print db._lastsql
SELECT person.id, person.name FROM person;

Web2py nikdy nesestavuje SQL příkazy s operátorem "*" - místo toho je vždy explicitní při tvorbě seznamu požadovaných polí.

drop

Celou tabulku můžete smazat:

>>> db.person.drop()

Poznámka pro SQLite: Web2py nevytvoří tabulku znova (migrací při příštím přístupu), dokud v databases/ adresáři nezrušíte odpovídající .table soubor (s metadaty zrušené tabulky).

Indexy

DAL API zatím nemá příkaz pro vytváření indexů, takže právě k tomu je vhodné použít příkaz executesql. Existence indexů (zahrnutá do DAL) by totiž mohla příliš zkomplikovat problematiku migrací, takže je lepší s indexy manipulovat explicitně.

Tady je příklad, jak vytvořit index pomocí SQL příkazu v SQLite:

>>> db = DAL('sqlite://storage.db')
>>> db.define_table('person', Field('name'))
>>> db.executesql('CREATE INDEX IF NOT EXISTS myidx ON person (name);')

Jiné databázové dialekty mají velice podobnou syntaxim ale např. nemusí rozumět volitelné direktivě "IF NOT EXISTS".

Databáze jiných aplikací (legacy databases) a "tabulky s klíčem"

Web2py se může připojit k databázím jiných aplikací jen za určitých podmínek.

Nejsnazší je to při splnění těchto podmínek:

• Každá tabulka má unikátní auto-inkrement integer pole, pojmenované "id"
• Záznamy jsou z jiných tabulek odkazovány výhradně pomocí tohoto "id" pole.

Když se připojujete k existující tabulce (k tabulce, kterou nevytvořila tato Web2py aplikace), vždy nastavte migrate=False.

Jestliže cizí tabulka má auto-increment integer pole, ale to se jmenuje jinak než "id", Web2py k ní může i tak přistupovat, ale do definice tabulky musíte explicitně přidat Field('....','id'), kde .... je jméno auto-increment integer pole.

Jestliže cizí tabulka používá primární klíč, tvořený jinak než jako jediné auto-inkrementální integer pole, můžete využít "tabulku s klíčem" ("keyed table"), například:

db.define_table('account',
    Field('accnum','integer'),
    Field('acctype'),
    Field('accdesc'),
    primarykey=['accnum', 'acctype'],
    migrate=False)

• primarykey je seznam jmen polí, které tvoří primární klíč.
• Všechna primarykey pole budou mít nastavení NOT NULL, i když to explicitně nespecifikujete.
• "Tabulky s klíčem" mohou odkazovat pomocí cizích klíčů zase jen na tabulky s klíčem (s argumentem "primarykey").
• Odkazující pole musí používat formát reference tablename.fieldname.
• Funkce update_record není k dispozici pro Rows objekt "tabulek s klíčem".

Tabulky s klíčem jsou podporovány jen pro některé back-endy (DB2, MS-SQL, Ingres, Informix, případně později přidané). Nezaručujeme tedy, že primarykey atribut je použitelný pro jakoukoli cizí tabulku a libovolný db back-end. Řešením v takovém případě může být vytvoření view, které má auto-inkrementální id pole.

Distribuované transakce

distributed transactions

V době psaní je tato vlastnost podporována jen pro PostgreSQL, MySQL a Firebird, protože mají API pro dvoufázové commity.

Předpokládejme, že máte dvě (nebo více) připojení k různým PostgreSQL databázím, například:

db_a = DAL('postgres://...')
db_b = DAL('postgres://...')

V modelu nebo controlleru je můžete commitovat současně, pomocí:

DAL.distributed_transaction_commit(db_a, db_b)

Dojde-li k chybě, tato metoda revertuje vše a vyhodí Exception.

Normálně, po návratu z akce (funkce) controlleru, jsou-li použita dvě připojení současně a nezavoláte tuto funkci, Web2py je obě připojení commituje, jenže separátně. Znamená to, že jeden z commitů může být úspěšný a druhý selhat. Distribuovaná transakce (explicitní zavolání popsané funkce) takové situaci zabrání.

Více o uploadech

Uvažujme následující model:

>>> db.define_table('myfile',
    Field('image', 'upload', default='path/'))

Pro 'upload' pole může být jeho default nastaven na cestu (absolutní nebo relativní k aktuální aplikaci) a defaultní image se vytvoří jako kopie souboru na zadané cestě. Vytvoří se nová samostatná kopie pro každý přidaný záznam, v němž nebude upload (v příkladu pojmenovaný: image) zadán.

Normálně je vložení uploadu zajištěno automaticky pomocí SQLFORM nebo crud formuláře (což je rovněž varianta SQLFORM), ale někdy již máte soubor v souborovém systému a chcete ho uploadovat programově. To je možné provést takto:

>>> stream = open(filename, 'rb')
>>> db.myfile.insert(image=db.myfile.image.store(stream, filename))

Je také možné vložit soubor ještě jednodušším způsobem, kdy insert metoda zavolá store() automaticky:

>>> stream = open(filename, 'rb')
>>> db.myfile.insert(image=stream)

V tomto případě bude jméno souboru zjištěno ze stream objektu.

Metoda store upload pole má dva parametry: stream a jméno souboru. Založí nový soubor, pojmenovaný jako dočasný, a sice v uploads/ adresáři, není-li zadáno jinak, a obsah souboru zkopíruje do tohoto nového dočasného souboru. Vrátí jméno souboru, které je nakonec uloženo do image pole tabulky db.myfile.

Poznamenejme, že jestliže má být soubor uložen ne jako samostatný soubor, ale do blob pole databáze, metoda store() soubor do blob pole neuloží - a to proto, že je zavolána dříve než insert(). Soubor tedy v tomto případě musíme uložit do blob pole explicitně:

>>> db.define_table('myfile',
        Field('image', 'upload', uploadfield='image_file'),
        Field('image_file', 'blob'))
>>> stream = open(filename, 'rb')
>>> db.myfile.insert(image=db.myfile.image.store(stream, filename),
        image_file=stream.read())

Opakem .store je .retrieve:

>>> row = db(db.myfile).select().first()
>>> (filename, stream) = db.myfile.image.retrieve(row.image)
>>> import shutil
>>> shutil.copyfileobj(stream,open(filename,'wb'))

Query, Set, Rows

Uvažujme znova tabulku, definovanou výše, prázdnou (např. po truncate() nebo po drop() následovaném opětovným vytvořením tabulky mechanismem migrace). Vložme do ní tři záznamy:

>>> db.define_table('person', Field('name'))
>>> db.person.insert(name="Alex")
1
>>> db.person.insert(name="Bob")
2
>>> db.person.insert(name="Carl")
3

Odkaz na tabulku můžete pochopitelně uložit do proměnné, dejme tomu pojmenované person:

>>> person = db.person

Stejně si můžete do proměnné uložit odkaz na pole, např. pojmenujme proměnnou opět stejně se jménem pole:

>>> name = person.name

Vytvořme si dotaz (query) pomocí operátorů (jako jsou ==, !=, <, >, <=, >=, like nebo belongs) a tento dotaz můžeme opět uložit do proměnné - pojmenujme ji třeba q:

>>> q = name=='Alex'

Zavoláním db s parametrem <dotaz> definujete objekt Set - sadu (množinu) záznamů (set of records). Zase si ji uložíme do proměnné, pojmenované např. s:

>>> s = db(q)

Poznamenejme, že zatím nebyl vykonán žádný SQL příkaz do databáze. DAL + Query jen připravily objekt pro sadu záznamů, které tomuto dotazu (query) vyhovují. Web2py z dotazu vyhodnotí, do které tabulky (nebo tabulek) dotaz zasahuje. Není potom potřeba tabulky explicitně určit.

select

Se Set objektem, který máme nyní přístupný pod proměnnou s, můžete získat skutečné záznamy z databáze příkazem select:

>>> rows = s.select()

Dostaneme iterovatelný objekt třídy pydal.objects.Rows, jehož položkami (prvky) jsou objekty Row. pydal.objects.Row objekty se chovají jako slovníky (dictionary), ale s jejich prvky můžete také pracovat jako s atributy: row['jmeno'] nebo row.jmeno. Rows objekt (sada záznamů) je read-only, do Row objekt (jednotlivý záznam) lze i měnit.

Rows objekt umožňuje v cyklu procházet jednotlivé záznamy výsledku SELECTu, např. můžete vypsat hodnoty polí pro jednotlivé záznamy:

>>> for row in rows:
        print row.id, row.name
1 Alex

Všechny kroky můžete spojit do jediného příkazu:

>>> for row in db(db.person.name=='Alex').select():
        print row.name
Alex

Metodě select můžete předat argumenty. Všechny nepojmenované argumenty budou chápany jako pole, které chcete z databáze získat (fetch). Např. můžete explicitně chtít pole "id" a "name":

>>> for row in db().select(db.person.id, db.person.name):
        print row.name
Alex
Bob
Carl

Atribut tabulky ALL vám umožňuje požadovat všechna pole tabulky:

>>> for row in db().select(db.person.ALL):
        print row.name
Alex
Bob
Carl

Všimněte si, že jsme nezadali žádný dotaz (query) jako argument db(). Web2py pochopí, že jestliže chcete všechna pole tabulky person bez uvedení nějaké dodatečné informace, tak chcete získat všechny záznamy této tabulky.

Ekvivalentní alternativní syntaxe je tato:

>>> for row in db(db.person.id > 0).select():
        print row.name
Alex
Bob
Carl

V tomto případě Web2py naopak pochopí, že chcete-li všechny záznamy tabulky person (id > 0) a neuvádíte žádnou informaci navíc (o požadovaných polích), má vrátit všechna pole této tabulky.

Vezměme nyní jeden řádek výsledku (záznam, větu tabulky):

row = rows[0]

Můžete získat obsah polí více ekvivalentními způsoby:

>>> row.name
Alex
>>> row['name']
Alex
>>> row('person.name')
Alex

Poslední uvedená syntaxe (s kulatými závorkami) je zvláště praktická, když chcete získat výraz místo pole. Ukážeme si to později.

Můžete nastavit

rows.compact = False

tím zakážete notaci

row.name

a místo ní povolíte méně stručnou notaci:

row.person.name

To je ovšem méně obvyklé a málokdy potřebné.

Prezentace (rendering) záznamů pomocí represent

Je možné přepsat záznamy, vrácené selectem, abychom využili formátovací informace z nastavení represent jednotlivých polí.

rows = db(query).select()  
repr_row = rows.render(0)

Když neuvedete index, dostanete místo toho generátor, který umožní iterovat přes věechna pole:

for row in rows.render():
    print row.myfield

Také to lze aplikovat na řezy (slices):

for row in rows[0:10].render():
    print row.myfield

Chcete-li represent atribut aplikovat jen na vybraná pole, můžete je uvést pomocí argumentu "fields":

repr_row = row.render(0, fields=[db.mytable.myfield])

Ale pozor, dostaneme transformovanou kopii normálního objektu Row, takže tento objekt nebude mít k dispozici metody update_record ani delete_record.

Zkratky (shortcuts)

DAL nabízí různé zkratky ke zjednodušení kódu. Podívejme se na ně:

myrecord = db.mytable[id]

Vrátí záznam (Row objekt) se zadaným id. Pokud id v tabulce neexistuje, vrátí None. Je to ekvivalentní této úplné syntaxi:

myrecord = db(db.mytable.id==id).select().first()

Můžete zrušit záznam pomocí id, takto:

del db.mytable[id]

což je ekvivalent úplného znění:

db(db.mytable.id==id).delete()

Ale pozor, tato zkratka aktuálně nepracuje pro případ, kdy používáte verzování (úplný záznam historie) záznamů.

Záznam můžete vložit i takto:

db.mytable[0] = dict(myfield='somevalue')

což je ekvivalent

db.mytable.insert(myfield='somevalue')

a v obou případech přidá záznam, přičemž v prvním případě předáme hodnoty jako slovník (dictionary).

Můžete aktualizovat záznam:

db.mytable[id] = dict(myfield='somevalue')

což je ekvivalent úplné syntaxe

db(db.mytable.id==id).update(myfield='somevalue')

Získání Row

Další praktická syntaxe je tato:

record = db.mytable(id)
record = db.mytable(db.mytable.id==id)
record = db.mytable(id,myfield='somevalue')

Je poněkud podobná syntaxi db.mytable[id], ale je flexibilnější a bezpečnější. V obou případech může být id zadáno jako integer nebo jako string (1, '1'). Ale chování se liší, pokud není vůbec zadáno číslo: db.mytable['a'] vyvolá výjimku, kdežto db.mytable('a') vrátí None (čili: neexistuje požadovaný záznam). Ve druhé variantě (s kulatými závorkami) tedy výjimka nevznikne nikdy. Druhá varianta také umožňuje zadat více podmínek, které musí být splněny současně. Pokud nejsou, je rovněž vráceno None.

Rekurzivní select

Uvažujme předcházející tabulku "person" a novou tabulku "thing", která se na záznamy z "person" odkazuje:

>>> db.define_table('thing',
        Field('name'),
        Field('owner_id', 'reference person'))

proveďme jednoduchý select() z této tabulky:

>>> things = db(db.thing).select()

což, jak jsme si ukázali, je totéž jako:

>>> things = db(db.thing._id>0).select()

._id znamená primární klíč tabulky. Pokud se nejedná o tabulku jiné aplikace, ale o standardní tabulku Web2py aplikace, tak db.thing._id je totéž jako db.thing.id, což jsme použili ve výkladu dříve a budeme tak uvádět i ve většině ostatních příkladů v této knize.

Pro každý záznam (Row), získaný z tabulky things, je nejen možné získat pole z této tabulky (thing), ale také z relačně navázaných tabulek (rekursivně):

>>> for thing in things: print thing.name, thing.owner_id.name

Nicméně zde thing.owner_id.name vyvolá jeden SQL select pro každý záznam ve things, což je samozřejmě velice neefektivní. Doporučujeme proto používat joiny místo rekurzivních selectů všude, kde je to možné. Přesto tato vlastnost může být pohodlná a praktická, pokud pracujeme jen s jednotlivým záznamem nebo velmi málo záznamy.

Funguje to i zpětně, pro směr relace 1->m, tedy můžete zjistit věci (things), které patří některé osobě (person):

person =  db.person(id)
for thing in person.thing.select(orderby=db.thing.name):
    print person.name, 'owns', thing.name

person.thing je zkratka (shortcut) pro

db(db.thing.owner_id==person.id)

tedy pro sadu (Set) záznamů z thing, které jsou vázány na zadanou osobu person. Tato zkratka ale selže, jestliže odkazovaná tabulka (things) má více klíčů, které propojují záznamy do řídící tabulky (preson). V takovém případě musíte být konkrétnější a použít druhou syntaxi (celý Dotaz).

Serializace Rows do views

Mějme následující akci index(), která na základě dotazu query vytvoří Rows objekt (sekvenci záznamů).

def index():
    return dict(rows = db(query).select())

Výsledek můžete zobrazit ve view touto jednoduchou syntaxí:

{{extend 'layout.html'}}
<h1>Záznamy</h1>
{{=rows}}

To je ekvivalentní zápisu:

{{extend 'layout.html'}}
<h1>Records</h1>
{{=SQLTABLE(rows)}}

SQLTABLE konvertuje Rows objekt na HTML tabulku s hlavičkou se jmény sloupců a s jedním řádkem pro každý záznam. Řádky jsou doplněny střídavě o css class "even" resp. "odd". Interně je Rows objekt nejprve konvertován na SQLTABLE objekt (nazeměňujte s Table) a pak je serializován. Hodnoty z databáze jsou při tom zformátovány pomocí validátorů a escapovány pro HTML výstup.

Je také možné a někdy užitečné zavolat SQLTABLE explicitně.

SQLTABLE konstruktor má následující volitelné argumenty:

• linkto lambda funkce nebo akce pro vytvoření odkazů z polí typu reference (default je None)

Jestliže zadáte jméno akce, sestaví odkaz (link) na tuto akci, kterému jako argumenty předá (v uvedeném pořadí) jméno tabulky a id. Příklad:

linkto = 'pointed_function' # generates something like <a href="pointed_function/table_name/id_value">

Chcete-li sestavit jiný odkaz, použijte lambda funkci. Ta jako parametry dostane id, typ objektu (např. table) a jméno objektu. Například, když chcete předat argumenty v opačném pořadí:

linkto = lambda id, type, name: URL(f='pointed_function', args=[id, name])

• upload URL nebo akce pro umožnění downloadu souborů, které byly uploadovány (default je None)
• headers slovník (dictionary), který mapuje jména polí na hlavičky sloupců (default je {}); případně to může být příkaz/povel (aktuálně podporujeme: headers='fieldname:capitalize')
• truncate počet znaků, na které budou zkráceny dlouhé údaje v tabulce
• columns seznam jmen polí, které se promítnou jako sloupce (ve formátu tablename.fieldname), neuvedené se nezobrazí (default je zobrazit vše)
• **attributes atributy pro TABLE objekt

Příklad:

{{extend 'layout.html'}}
<h1>Záznamy</h1>
{{=SQLTABLE(rows,
     headers='fieldname:capitalize',
     truncate=100,
     upload=URL('download'))
}}

SQLTABLE může být užitečná, ale někdy můžeme potřebovat více. SQLFORM.grid je rozšíření SQLTABLE s vyhledáváním a stránkováním a s možností vytvářet záznamy, editovat je nebo rušit. SQLFORM.smartgrid je další zobecnění, které kromě předchozího nabízí podobně funkční odkazy na záznamy relačně závislých tabulek.

Tady je příklad použití SQLFORM.grid:

def index():
    return dict(grid=SQLFORM.grid(query))

a odpovídající view:

{{extend 'layout.html'}}
{{=grid}}

Pro práci s více záznamy by měly být SQLFORM.grid a SQLFORM.smartgrid preferovány před SQLTABLE, protože jsou výkonnější. Podrobněji je probíráme v kapitole 7.

orderby, groupby, limitby, distinct, having, orderby_on_limitby, left,cache

Příkaz select může mít několik volitelných argumentů.

orderby

Takto můžete získané záznamy setřídit podle sloupce name:

>>> for row in db().select(
        db.person.ALL, orderby=db.person.name):
        print row.name
Alex
Bob
Carl

Můžete změnit pořadí na opačné (všimněte si znaku ~ (tilda)):

>>> for row in db().select(
        db.person.ALL, orderby=~db.person.name):
        print row.name
Carl
Bob
Alex

Můžete použít náhodné pořadí:

>>> for row in db().select(
        db.person.ALL, orderby='<random>'):
        print row.name
Carl
Alex
Bob

Použití orderby='<random>' není podporováno na Google NoSQL. Ale samozřejmě můžete importovat externí modul:

import random
rows=db(...).select().sort(lambda row: random.random())

Můžete setřídit podle dalších sloupců při shodě předchozích, tak, že je uvedete spojené znakem "|":

>>> for row in db().select(
        db.person.ALL, orderby=db.person.name|db.person.id):
        print row.name
Carl
Bob
Alex

groupby, having

Použijete-li spolu s orderby také groupby, můžete sloučit záznamy se stejnou hodnotou zadaného pole (to je back-end specifické a není podporováno na Google NoSQL):

>>> for row in db().select(
        db.person.ALL,
        orderby=db.person.name, groupby=db.person.name):
        print row.name
Alex
Bob
Carl

Můžete použít having spolu s groupby pro podmíněné seskupení.

>>> print db(query1).select(db.person.ALL, groupby=db.person.name, having=query2)

query1 vybírá záznamy, které se mají zobrazit, kdežto query2 záznamy, které se mají seskupit.

distinct

Argumentem distinct=True můžete zadat. že chcete, aby každý vrácený záznam byl unikátní (uvést duplicitní záznamy jen jednou). Je to totéž jako groupovat (seskupit) podle všech výstupních polí, s tím rozdílem, že nemusíte zadat pořadí (orderby). Rozumné použití znamená, že jednak pomocí ALL nevyberete všechna pole, jednak nevyberete pole id explicitně, protože v obou případech by id způsobilo, že každý výstupní záznam by byl unikátní a výsledek by se tedy nelišil bez ohledu na nastavení distinct.

Příklad:

>>> for row in db().select(db.person.name, distinct=True):
        print row.name
Alex
Bob
Carl

Jako distinct můžete uvést také výraz, např.:

>>> for row in db().select(db.person.name, distinct=db.person.name):
        print row.name
Alex
Bob
Carl

Pomocí limitby=(min, max) můžete vybrat část výstupních záznamů s offset od min (včetně) do max (bez něj). Např. zde chceme získat jen první dva záznamy:

limitby

>>> for row in db().select(db.person.ALL, limitby=(0, 2)):
        print row.name
Alex
Bob

orderby_on_limitby

DAL defaultně přidává orderby, kdykoli použijeme limitby. To dává jistotu, že dotaz vrátí pokaždé totéž, což je zásadní pro stránkování. Mohlo by to ale způsobit snížení výkonnosti (performance problems). Ke změně defaultního chování můžete použít orderby_on_limitby = False (default je tedy True).

left

Diskutujeme níže v sekci o joinech.

cache, cacheable

Kešování umožňuje zásadně zrychlit opakované selecty, které nemusí dát naprosto aktuální výsledek:

rows = db(query).select(cache=(cache.ram, 3600), cacheable=True)

Více je popsáno níže v samostatném oddíle o kešování.

Logické oprátory

Dotazy (query) lze kombinovat do složitějších. Binární AND (logický součin, splněný při splnění obou dílčích podmínek) zajišťuje operátor "&":

>>> rows = db((db.person.name=='Alex') & (db.person.id>3)).select()
>>> for row in rows: print row.id, row.name
4 Alex

Binární OR (logický součet, splněný při splnění aspoň jedné z dílčích podmínek) zajišťuje operátor "|":

>>> rows = db((db.person.name=='Alex') | (db.person.id>3)).select()
>>> for row in rows: print row.id, row.name
1 Alex

Můžete negovat dotaz změnou operátoru za opačný, typicky pomocí operátoru "!=":

>>> rows = db((db.person.name!='Alex') | (db.person.id>3)).select()
>>> for row in rows: print row.id, row.name
2 Bob
3 Carl

Negovat dotaz (nebo dílčí podmínku složeného dotazu) můžete také unárním operátorem "~". Ubární operátor se aplikuje na za ním následující výraz:

>>> rows = db(~(db.person.name=='Alex') | (db.person.id>3)).select()
>>> for row in rows: print row.id, row.name
2 Bob
3 Carl

Nelze použít operátory "and" a "or", a to vzhledem k omezením Pythonu z hlediska možnosti je předefinovat. Právě proto je potřeba používat při skládání dotazů operátory "&" a "|". Tyto operátory mají větší přednost než operátory porovnání (na rozdíl od "and" a "or"), takže je nutné používat "extra" závorky navíc kolem vzájemně spojovaných dílčích dotazů. Podobně i operátor "~" má větší přednost než operátory pro porovnání, takže i negovaný dílčí dotaz je potřeba uzavřít do závorky.

Dotazy lze také měnit pomocí in-place logických operátorů:

>>> query = db.person.name!='Alex'
>>> query &= db.person.id>3
>>> query |= db.person.name=='John'

count, isempty, delete, update

Spočítat záznamy můžete metodou count():

>>> print db(db.person.id > 0).count()
3

count má také volitelný argument distinct (defaultně False), který se chová identicky jako u metody select. count má dále argument cache, který se také chová stejně jako u select metody.

Někdy potřebujete zjistit, zda je tabulka (případně sada (set)) prázdná. Efektivnější než počítat záznamy je použít metodu isempty:

>>> print db(db.person.id > 0).isempty()
False

ekvivalentní, ale kratší, je:

>>> print db(db.person).isempty()
False

Můžete smazat záznamy vybrané sady:

>>> db(db.person.id > 3).delete()

Můžete aktualizovat všechny záznamy sady předáním pojmenovaných argumentů metodě update. Jména parametrů musí být identická se jmény aktualizovaných polí:

>>> db(db.person.id > 3).update(name='Ken')

Výrazy

Hodnota, předaná argumentu metody update, může být také výraz. Např.

>>> db.define_table('person',
        Field('name'),
        Field('visits', 'integer', default=0))
>>> db(db.person.name == 'Massimo').update(
        visits = db.person.visits + 1)

Výraz se vyhodnotí pro každý záznam sady zvlášť. Také dotazy mohou obsahovat výrazy:

>>> db.define_table('person',
        Field('name'),
        Field('visits', 'integer', default=0),
        Field('clicks', 'integer', default=0))
>>> db(db.person.visits == db.person.clicks + 1).delete()

case

case

Výraz může obsahovat kluzuli case, např.:

>>> db.define_table('person', Field('name'))
>>> condition = db.person.name.startswith('M')
>>> yes_or_no = condition.case('Yes','No')
>>> for row in db().select(db.person.name, yes_or_no):
...     print row.person.name,  row(yes_or_no)
Max Yes
John No

update_record

Web2py poskytuje také metodu pro aktualizaci jednoho záznamu současně v paměti i v databázi, update_record:

>>> row = db(db.person.id==2).select().first()
>>> row.update_record(name='Curt')

Nezaměňujte update_record s metodou update (aplikovanou na jediný řádek)

>>> row.update(name='Curt')

protože metoda update aktualizuje jen row objekt (kopii záznamu v paměti), ale ne záznam v databázi.

Je však možné měnit atributy row objektu popsaným způsobem pomocí update nebo jednotlivě pomocí přiřazení, a pak zavolat update_record() bez argumentů, což uloží provedené změny:

>>> row = db(db.person.id > 2).select().first()
>>> row.name = 'Curt'
>>> row.update_record() # uloží dříve provedenou změnu

Metoda update_record je k dispozici JEN TEHDY, pokud je v selectu (tedy i v záznamu) pole id a pokud není nastaveno cacheable na True.

Vkládání a aktualizace pomocí slovníku (dictionary)

Občas je potřeba vkládat nebo aktualizovat záznamy v tabulce, kdy jméno tabulky, jména dotčených polí a ukládané hodnoty jsou uloženy v proměnných. Např. tablename (proměnná se jménem tabulky), fieldname (proměnná se jménem pole, kam ukládáme), and value (proměnná s hodnotou, která má být uložena).

Pro vkládání upravíme syntaxi takto:

db[tablename].insert(**{fieldname:value})

Aktualizace záznamu s určeným id:

db(db[tablename]._id==id).update(**{fieldname:value})

Použití table._id místo table.id je obecnější - dotaz pak funguje i pro případ cizích tabulek s polem typu "id", které je ale pojmenováno jinak.

first a last

Z Rows objektu můžeme jako Row objekt získat první nebo poslední záznam:

>>> rows = db(query).select()
>>> first_row = rows.first()
>>> last_row = rows.last()

což je ekvivalent zápisu:

>>> first_row = rows[0] if len(rows)>0 else None
>>> last_row = rows[-1] if len(rows)>0 else None

as_dict a as_list

Row objekt (1 záznam) může být převeden na standardní slovník (dictionary) pomocí metody as_dict(). Rows object (sekvence záznamů) může být převeden na standardní seznam (list) pomocí metody as_list():

>>> rows = db(query).select()
>>> rows_list = rows.as_list()
>>> first_row_dict = rows.first().as_dict()

Tyto metody mohou být užitečné při předávání Rows objektu do generických (obecně použitelných) šablon (views) a chceme-li uložit Rows objekt do sessions (protože Rows objekt nemůže být přímo serializován, obsahuje totiž odkaz na otevřené db připojení):

>>> rows = db(query).select()
>>> session.rows = rows # nelze!
>>> session.rows = rows.as_list() # je možné

Skládání Rows objektů

Rows objekty je možné slučovat na úrovni Pythonu. Dejme tomu, že máme tyto dva Rows objekty:

>>> print rows1
person.name
Max
Tim
>>> print rows2
person.name
John
Tim

Můžeme zjistit průnik množin (společné záznamy):

>>> rows3 = rows1 & rows2
>>> print rows3
name
Tim

Můžeme je spojit do jediného objektu s vyloučením duplicit:

>>> rows3 = rows1 | rows2
>>> print rows3
name
Max
Tim
John

find, exclude, sort

Někdy je potřeba provést dva selecty, přičemž druhý zahrnuje podmnožinu předchozího selectu. V takovém případě není vhodné zatěžovat zbytečně databázi dalším dotazem. Metody find, exclude a sort umožňují pracovat s Rows objektem a vytvořit z jeho (všech nebo některých) záznamů jiný, bez přístupu do databáze:

• find vrátí nový Rows objekt s vybranými záznamy podle podmínky; originál zůstane beze změny.
• exclude vrátí nový Rows objekt s vybranými záznamy podle podmínky; z původního Rows objektu budou vyhovující záznamy odstraněny.
• sort vrátí nový Rows objekt setříděný podle zadaného výrazu; originál zůstane beze změny.

Všechny metody mají shodný parametr: funkci, která se provede nad každým jednotlivým řádkem.

Příklad:

>>> db.define_table('person', Field('name'))
>>> db.person.insert(name='John')
>>> db.person.insert(name='Max')
>>> db.person.insert(name='Alex')
>>> rows = db(db.person).select()
>>> for row in rows.find(lambda row: row.name[0]=='M'):
        print row.name
Max
>>> print len(rows)
3
>>> for row in rows.exclude(lambda row: row.name[0]=='M'):
        print row.name
Max
>>> print len(rows)
2
>>> for row in rows.sort(lambda row: row.name):
        print row.name
Alex
John

Další příklad - volání dvou metod v jednom příkazu:

>>> rows = db(db.person).select()
>>> rows = rows.find(
        lambda row: 'x' in row.name).sort(
            lambda row: row.name)
>>> for row in rows:
        print row.name
Alex
Max

Sort může mít parametr reverse=True pro opačné setřídění.

Metoda find má volitelný argument limitby se stejnou syntaxí a funkcí jako u select metody objektu Set.

Další metody

update_or_insert

Někdy potřebujete vložit záznam jen tehdy, pokud ještě neexistuje záznam s danými hodnotami. To je možné provést takto:

db.define_table('person', Field('name'), Field('birthplace'))
db.person.update_or_insert(name='John', birthplace='Chicago')

Záznam se založí jen když dosud neevidujeme žádného Johna z Chicaga.

Jiná verze může být užitečnější: Jako poziční (nepojmenovaný) první parametr můžeme uvést Dotaz (query), pomocí něhož se určí, zda vhodný záznam existuje nebo ne:

db.person.update_or_insert(db.person.name=='John',
     name='John', birthplace='Chicago')

Jestliže John je v evidenci, aktualizuje se jeho místo narození. V opačném případě se vytvoří nový záznam.

Jiný příklad se složitějším dotazem:

db.person.update_or_insert((db.person.name=='John') & (db.person.birthplace=='Chicago'),
     name='John', birthplace='Chicago', pet='Rover')

validate_and_insert, validate_and_update

Funkce

ret = db.mytable.validate_and_insert(field='value')

pracuje podobně jako

id = db.mytable.insert(field='value')

s tím rozdílem, že nejprve validuje zadaná pole a nevykoná insert(), jestliže validace selže. Jestliže validace selžou, problémy lze nalézt ve vráceném objektu v ret.errors, což je slovník (mapping), kde klíčem ke jméno pole, jehož validace selhala a hodnotou je text validační chyby (podobně jako u form.errors). Jestliže validace projdou a záznam se uloží, přidělené id je v ret.id. Vzhledem k tomu, že validace se provádí automaticky během logiky zpracování formuláře, není tato funkce běžně potřeba.

Obdobně

ret = db(query).validate_and_update(field='value')

pracuje podobně jako

num = db(query).update(field='value')

ale s provedením validace předem. To funguje jedině tehdy, když dotaz (query) pracuje jen s jedinou tabulkou (bez joinů). Počet ovlivněných záznamů lze pak najít v res.updated a chyby v ret.errors.

smart_query (experimentální)

Umožňuje parsovat dotaz v přirozeném jazyce (anglicky)

name contain m and age greater than 18

DAL umí parsovat takovýto dotaz:

search = 'name contain m and age greater than 18'
rows = db.smart_query([db.person], search).select()

První argument musí být seznam tabulek nebo polí, které jsou v dotazu dovoleny. Při chybě v řetězci search je vyvolán RuntimeError. Tato funkcionalita může být použita při tvorbě RESTful interface (více v kapitole 10) a je interně použita u SQLFORM.grid a SQLFORM.smartgrid.

V řetězci pro smart_query může být pole určeno jen jako samotné jméno pole (fieldname) nebo ve formátu tablename.fieldname. Řetězce lze omezit uvozovkami (double quotes), jestliže obsahují mezery.

Vypočtená pole (computed fields)

DAL pole může mít atribut compute. Musí to být funkce (často lambda funkce), která vezme parametr Row objekt a vrátí hodnotu pole. Když je vkládán nový záznam nebo aktualizován existující, pak není-li hodnota pole explicitně uvedena, Web2py ji určí výpočtem pomocí compute funkce:

>>> db.define_table('item',
        Field('unit_price','double'),
        Field('quantity','integer'),
        Field('total_price',
            compute=lambda r: r['unit_price']*r['quantity']))
>>> r = db.item.insert(unit_price=1.99, quantity=5)
>>> print r.total_price
9.95

Poznamenejme, že vypočtená hodnota je uložena v poli v databázi a nepočítá se až při získání (načtení) hodnoty, jako je tomu u virtuálních polí, která popisujeme níže. Dvě typická použití "vypočtených polí" jsou:

• ve wiki aplikacích pro uložení textu ve verzi symbolického jazyka do verze HTML, aby nebylo nutné překládat symbolický jazyk do HTML až později, při zobrazení článku.
• při vyhledávání, k určení normalizované verze hodnoty pro vyhledávání.

Hodnoty vypočtených polí se vyčíslují v pořadí, jak jsou uvedena v definici tabulky. Vypočtené pole může odkazovat i na dříve vypočtené pole (což je nové chování od verze 2.5.1).

Virtuální pole

Virtuální pole jsou rovněž počítaná pole, ale liší se tím, že jsou "virtuální" v tom smyslu, že ve skutečnosti neexistují v databázi. Jsou vypočtena vždy až v okamžiku, kdy je záznam získán z databáze. Mohou být použita ke zjednodušení kódu, který se záznamem pracuje, ale nelze podle nich (z pochopitelných důvodů) vyhledávat.

Nová koncepce virtuálních polí

Novější verze Web2py poskytují nový snadnější způsob definice virtuálních polí a virtuálních polí s odloženým vyčíslením (lazy virtual fields). Tuto sekci považujeme ještě za experimentální, protože API těchto virtuálních polí (příkazy pro práci s nimi) se ještě může změnit vůči popisu, který uvádíme zde.

Zde uvažujme stejný příklad jako v minulém odstavci, konkrétně tento model:

>>> db.define_table('item',
        Field('unit_price','double'),
        Field('quantity','integer'),

Můžeme definovat virtuální pole total_price takto

>>> db.item.total_price = Field.Virtual(
    'total_price',
    lambda row: row.item.unit_price*row.item.quantity)

Jediný argument konstruktoru pro Field.Virtual je funkce, která pro zadaný Row objekt vrátí hodnotu virtuálního pole.

Takto definované virtuální pole je automaticky spočteno pro všechny záznamy poté, co jsou získány select-em:

>>> for row in db(db.item).select(): print row.total_price

Je také možné virtruální pole definovat jako metodu, která spočte hodnotu až když ji budeme opravdu potřebovat. Například:

>>> db.item.discounted_total = Field.Method(lambda row, discount=0.0:        row.item.unit_price*row.item.quantity*(1.0-discount/100))

V tomto případě row.discounted_total není hodnota, ale funkce. Tato funkce má takové parametry, jaké jsme uvedli při definici, ale s výjimkou prvního parametru (row), který se při volání neuvádí a je předán implicitně (představme si jej jako obdobu pythonovského "self" pro row objekt virtuální metody).

Lazy pole (s odloženým vyčíslením) ve výše uvedeném příkladu dovoluje spočítat výslednou cenu každé položky item:

>>> for row in db(db.item).select(): print row.discounted_total()

přičemž současně lze zadat volitelnou slevu jako argument discount, např. 15%:

>>> for row in db(db.item).select(): print row.discounted_total(15)

Virtuální pole Virtual a Method nemusíme definovat až dodatečně, ale můžeme je také definovat přímo jako součást definice tabulky:

>>> db.define_table('item',
        Field('unit_price', 'double'),
        Field('quantity', 'integer'),
        Field.Virtual('total_price', lambda row: ...),
        Field.Method('discounted_total', lambda row, discount=0.0: ...))

Pamatujte, že virtuální pole nemají stejné atributy jako ostatní pole (default, readable, requires, apod.). Ve starších verzích Web2py chyběly v seznamu db.table.fields. Vyžadují zvláštní ošetření při použití v SQLFORM.grid a SQLFORM.smartgrid - více o gridu a virtuálních polích je diskutováno v kapitole o Formulářích.

Stará koncepce virtuálních polí

Pro definování jednoho nebo více virtuálních polí můžete rovněž definovat containerovou třídu, vytvořit její instanci a propojit ji s tabulkou nebo selectem. Například:

>>> db.define_table('item',
        Field('unit_price','double'),
        Field('quantity','integer'),

Definujeme virtuální pole total_price takto

>>> class MyVirtualFields(object):
        def total_price(self):
            return self.item.unit_price*self.item.quantity
>>> db.item.virtualfields.append(MyVirtualFields())

Každá metoda této třídy, která má jediný argument (self) představuje nové virtuální pole. self zde odkazuje na jednotlivý řádek selectu. Obsah polí je zde potřeba odkazovat plným jménem (včetně jména tabulky), např. self.item.unit_price. Tabulku a virtuální pole vzájemně propojíme tak, že do atributu db.jmenotabulky.virtualfields provedeme .append() instance třídy, ve které virtuální pole definujeme.

Virtuální pole mohou přistupovat i k rekurzivním polím jako je tomu v tomto příkladu:

>>> db.define_table('item',
        Field('unit_price','double'))
>>> db.define_table('order_item',
        Field('item','reference item'),
        Field('quantity','integer'))
>>> class MyVirtualFields(object):
        def total_price(self):
            return self.order_item.item.unit_price                 * self.order_item.quantity
>>> db.order_item.virtualfields.append(MyVirtualFields())

Všimněte si přístupu k rekurzivnímu poli self.order_item.item.unit_price, kde self znamená záznam (row) v order_item.

S poněkud pozměněnou syntaxí mohou spolupracovat také s výsledkem JOINu:

>>> db.define_table('item',
        Field('unit_price','double'))
>>> db.define_table('order_item',
        Field('item','reference item'),
        Field('quantity','integer'))
>>> rows = db(db.order_item.item==db.item.id).select()
>>> class MyVirtualFields(object):
        def total_price(self):
            return self.item.unit_price                 * self.order_item.quantity
>>> rows.setvirtualfields(order_item=MyVirtualFields())
>>> for row in rows: print row.order_item.total_price

Všimněte si, v čem se syntaxe liší: Virtuální pole přistupuje k údajům původně z různých tabulek self.item.unit_price a self.order_item.quantity, a sice do výsledného joinovaného selectu. K Rows objektu připojíme virtuální pole voláním jeho metody setvirtualfields. Tato metoda může mít libovolný počet pojmenovaných argumentů, může tím vytvářet více virtuálních polí, definovaných v jedné nebo více třídách a připojovat je k různým tabulkám:

>>> class MyVirtualFields1(object):
        def discounted_unit_price(self):
            return self.item.unit_price*0.90
>>> class MyVirtualFields2(object):
        def total_price(self):
            return self.item.unit_price                 * self.order_item.quantity
        def discounted_total_price(self):
            return self.item.discounted_unit_price                 * self.order_item.quantity
>>> rows.setvirtualfields(
        item=MyVirtualFields1(),
        order_item=MyVirtualFields2())
>>> for row in rows:
        print row.order_item.discounted_total_price

Virtuální pole mohou být lazy (s odloženým výpočtem); k tomu musí vracet funkci a musí se k nim přistupovat zavoláním funkce, jak ukazuje tento příklad:

>>> db.define_table('item',
        Field('unit_price','double'),
        Field('quantity','integer'),
>>> class MyVirtualFields(object):
        def lazy_total_price(self):
            def lazy(self=self):
                return self.item.unit_price                     * self.item.quantity
            return lazy
>>> db.item.virtualfields.append(MyVirtualFields())
>>> for item in db(db.item).select():
        print item.lazy_total_price()

nebo stručněji, když funkci zkrátíme na lambda funkci:

>>> class MyVirtualFields(object):
        def lazy_total_price(self):
            return lambda self=self: self.item.unit_price                 * self.item.quantity

Relace 1:m (jedna ku mnoho)

Abychom si ukázali, jak implementovat 1:m relaci pomocí DAL Web2py frameworku, definujte další tabulku "thing", která bude odkazovat na tabulku "person":

>>> db.define_table('person',
                    Field('name'),
                    format='%(name)s')
>>> db.define_table('thing',
                    Field('name'),
                    Field('owner_id', 'reference person'),
                    format='%(name)s')

Tabulka "thing" má dvě pole, jméno věci a jejího vlastníka. "owner_id" je odkazující pole (cizí klíč). Typ "odkaz" můžeme popsat dvěma rovnocennými způsoby:

Field('owner_id', 'reference person')
Field('owner_id', db.person)

Později uvedená verze je vždy konvertována na první variantu. Druhá varianta je sice ekvivalentní, ale v některých případech ji nelze použít, a sice u lazy tabulek (vytvářených se zpožděním), odkazů klíče na stejnou tabulku (v níž se klíč sám nachází) a případu cyklických odkazů v datovém modelu, kdy nemůžeme zajistit, aby vždy byla cílová tabulka klíče už definovaná. V těchto případech je možné jen použití první varianty.

Jestliže je typem pole jiná tabulka, znamená to, že pole odkazuje na záznam jiné tabulky pomocí id. Můžete si zkusit vypsat typ pole a dostanete:

>>> print db.thing.owner_id.type
reference person

Nyní přidejte 3 věci, dvě do vlastnictví Alexe a jednu Bobovi:

>>> db.thing.insert(name='člun', owner_id=1)
1
>>> db.thing.insert(name='křeslo', owner_id=1)
2
>>> db.thing.insert(name='boty', owner_id=2)
3

Pokud znáte klíč (id) záznamu, můžete vybírat obvyklým způsobem:

>>> for row in db(db.thing.owner_id==1).select():
        print row.name
Boat
Chair

Protože věc/thing má odkaz na osobu/person, osoba může mít více věcí. Row objekt záznamu z tabulky person nyní obsahuje nový atribut "thing", což je instance Set, která definuje věci této osoby. Můžeme tedy volat metody třídy Set, zejména metodu select(), která vybere osobě příslušné věci do objektu Rows:

>>> for person in db().select(db.person.ALL):
        print person.name
        for thing in person.thing.select():
            print '    ', thing.name
Alex
     člun
     křeslo
Bob
     boty
Carl

Inner join (vnitřní join - vzájemné navázání tabulek)

Jiná cesta, jak dosáhneme podobný výsledek, je použitím joinů, např. INNER JOINu. Web2py provede joiny automaticky, jestliže dotaz (query) spojuje dvě nebo více tabulek, jak ukazuje tento příklad:

>>> rows = db(db.person.id==db.thing.owner_id).select()
>>> for row in rows:
        print row.person.name, 'má', row.thing.name
Alex má člun
Alex má křeslo
Bob má boty

Web2py provedlo join, takže výsledný záznam (objekt Row) je nyní sestaven ze záznamů dvou tabulek, spojených do jednoho výsledného. Protože oba spojené záznamy mohou mít pole s konfliktními názvy, je potřeba při přístupu k polím výsledku zadávat i tabulku, z níž údaj pochází. Takže zatímco dřív jste mohli jednoduše napsat jen:

row.name

a bylo jasné (dokud se vybíralo z jediné tabulky), zda se jedná o jméno osoby/person nebo věci/thing, při práci s výsledkem joinu to musíte explicitně určit:

row.person.name

nebo:

row.thing.name

Je tu ještě alternativní syntaxe pro INNER JOINy:

>>> rows = db(db.person).select(join=db.thing.on(db.person.id==db.thing.owner_id))
>>> for row in rows:
    print row.person.name, 'má', row.thing.name
Alex má člun
Alex má křeslo
Bob má boty

Ačkoli výstup je totožný, sestavený SQL příkaz se může mezi oběma případy lišit. Druhá verze syntaxe odstraňuje pochybnosti v případě, že stejné tabulky jsou propojeny (joinovány) 2x a k jejich používání je proto potřeba aliasů (náhradních jmen):

>>> db.define_table('thing',
        Field('name'),
        Field('owner_id1','reference person'),
        Field('owner_id2','reference person'))
>>> rows = db(db.person).select(
    join=[db.person.with_alias('owner_id1').on(db.person.id==db.thing.owner_id1).
          db.person.with_alias('owner_id2').on(db.person.id==db.thing.owner_id2)])

Hodnotou join parametru, jak vidíte, může být i seznam db.table.on(...), tedy joinů, které spojují tabulky v datovém modelu, a tedy i pro náš výběr.

Left outer join (levý vnější join)

Možná jste si ani nevšimli, že Carl se ve výstupu neobjevil. Je to proto, že nemá žádnou věc. Jestliže chcete vybírat osoby a získat je do výsledku bez ohledu na to, zda mají nějakou věc, potřebujete použít LEFT OUTER JOIN. V DAL to umožňuje argument "left" metody select(). Tady je příklad:

>>> rows=db().select(
        db.person.ALL, db.thing.ALL,
        left=db.thing.on(db.person.id==db.thing.owner_id))
>>> for row in rows:
        print row.person.name, 'has', row.thing.name
Alex má člun
Alex má křeslo
Bob má boty
Carl má None

kde:

left = db.thing.on(...)

definuje dotaz (query) pro left join. Argument metody db.thing.on je podmínka joinu (dotaz, query; stejná jako v předchozím případě inner joinu). Na rozdíl od Inner joinu, kde to nebylo potřeba, musíme být u Left joinu explicitní a uvést, která pole chceme vybrat (zde pomocí .jmenotabulky.ALL).

Více Left joinů můžete zkombinovat tak, že do atributu left předáte seznam (list) nebo vektor (tuple) výrazů db.mytable.on(...).

Seskupování a počítání

Když používáte joiny, chcete někdy seskupit záznamy podle určitých kritérií a spočítat je. Např. bychom chtěli spočítat počet věcí, které ta která osoba vlastní. Za prvé, potřebujete count operátor. Za druhé, potřebujete propojit (join) tabulku osob (person) a tabulku věcí (thing). Za třetí, chcete vybrat všechny řádky (jako kombinaci person + thing), seskupit je podle osoby (person), a během seksupování je spočítat:

>>> count = db.person.id.count()
>>> for row in db(db.person.id==db.thing.owner_id).select(
        db.person.name, count, groupby=db.person.name):
        print row.person.name, row[count]
Alex 2
Bob 1

Všimněte si count operátoru, který se zde používá jako pole. Jediná těžkost tady je, jak přistupovat k vypočtenému počtu. Každý řádek totiž obsahuje osobu a jí odpovídající počet, ale count (počet) není pole tabulky person, a ani to není tabulka. Takže kam se výsledný počet uloží? Do Row objektu (což, jak jsme si řekli, je storage objekt, neboli objekt podobný slovníku (dictionary), který obsahuje jeden záznam). Klíčem je proměnná count.

Technicky je do Row objektu vložen další Row objekt s klíčem '_extra'. row[count] funguje jako zkratka, ale zrovna tak se k počtu dostanete i jinak: row[str(count)], row['COUNT(person.id)'], row._extra[count], row._extra[str(count)], row._extra['COUNT(person.id)']. Místo row._extra také lze psát row['_extra'].

Metoda count objektu pole (Field) má nepovinný argument distinct. Je-li True, budou spočítány jen unikátní hodnoty pole.

m:n - mnoho ku mnoho

V předchozím případě jsme umožnili, aby věc měla jediného vlastníka (zatímco vlastník mohl mít více věcí). Co když ve skutečnosti může být vlastnictví společné, např. člun může vlastnit Alex s Curtem dohromady? Budeme potřebovat relaci (vazbu) mnoho:mnoho, kterou vytvoříme pomocí vložené mezitabulky, která propojí osoby (person) a věci (thing), obsahuje odkazy na záznamy obou těchto tabulek, a má tedy význam vlastnictví (ownership).

Uděláme to tedy takto:

>>> db.define_table('person',
                    Field('name'))
>>> db.define_table('thing',
                    Field('name'))
>>> db.define_table('ownership',
                    Field('person', 'reference person'),
                    Field('thing', 'reference thing'))

Zatím existující vlastnictví tedy můžeme do nově přidané vazební tabulky uložit takto:

>>> db.ownership.insert(person=1, thing=1) # Alex vlastní člun
>>> db.ownership.insert(person=1, thing=2) # Alex vlastní křeslo
>>> db.ownership.insert(person=2, thing=3) # Bob vlastní boty

a pak přidáme Curtovo spoluvlastnictví člunu:

>>> db.ownership.insert(person=3, thing=1) # Curt vlastní (tentýž) člun

Můžeme si definovat nový Set, nad nímž budeme provádět operace:

>>> persons_and_things = db(
        (db.person.id==db.ownership.person)         & (db.thing.id==db.ownership.thing))

S pomocí tohoto nového Setu bude snadné vybrat všechny osoby s věcmi, které vlastní:

>>> for row in persons_and_things.select():
        print row.person.name, row.thing.name
Alex Boat
Alex Chair
Bob Shoes
Curt Boat

Nebo můžeme snadno vybrat předměty, které vlastní Alex:

>>> for row in persons_and_things(db.person.name=='Alex').select():
        print row.thing.name
člun
křeslo

nebo všechny vlastníky člunu:

>>> for row in persons_and_things(db.thing.name=='Boat').select():
        print row.person.name
Alex
Curt

Méně náročnou alternativou m:n relace je tagging (značkování). Tagging probereme v souvislosti s IS_IN_DB validátorem. Tagging bude pracovat i nad databázovými back-endy, které nepodporují JOINy, jako je např. Google App Engine NoSQL.

list:<type> a contains

Web2py poskytuje tyto speciální typy polí:

list:string
list:integer
list:reference <table>

Mohou obsahovat seznam (list) řetězců (strings), celých čísel (integers) nebo odkazů (references).

Na Google App Engine NoSQL list:string bude mapován na StringListProperty a ostatní dva na ListProperty(int). Na relačních databázích budou všechna tato pole mapována do pole text a seznam (list) v poli text bude tvořen prvky, oddělenými pomocí |. Například [1,2,3] bude uloženo v poli text jako |1|2|3|.

V případě seznamu řetězců (list of strings) jsou položky escapovány (ošetřeny na výskyt problematických znaků), takže každý znak | v položce je nahrazen pomocí ||. To je ale jen interní reprezentace a obsah pro uživatele zůstává stejný.

Pole typu list:string můžete použít například takto:

>>> db.define_table('product',
        Field('name'),
        Field('colors','list:string'))
>>> db.product.colors.requires = IS_IN_SET(('červená', 'modrá', 'zelená'))
>>> db.product.insert(name='hračka',colors=['červená', 'zelená'])
>>> products = db(db.product.colors.contains('červená')).select()
>>> for item in products:
        print item.name, item.colors
hračka ['červená', 'zelená']

Podobně se chová pole list:integer, ale jeho prvky musí být celá čísla (integers).

Tyto požadavky (requires=..) jsou zajišťovány (validovány) na úrovni formulářů a nikoli na úrovni insert-u.

Pro pole list:<type> operátor contains(value) je mapován na složitější dotaz (query), který hledá seznam, obsahující požadovanou hodnotu value. Operátor contains můžete ale použít i pro normální string a text pole, kde je mapován na SQL frázi LIKE '%value%'.

Pole typu list:reference a operátor contains(value) mohou být prospěšné při denormalizaci m:n (mnoho:mnoho) relace. Tady je příklad:

>>> db.define_table('tag', Field('name'), format='%(name)s')
>>> db.define_table('product',
        Field('name'),
        Field('tags', 'list:reference tag'))
>>> a = db.tag.insert(name='červená')
>>> b = db.tag.insert(name='zelená')
>>> c = db.tag.insert(name='modrá')
>>> db.product.insert(name='hračka', tags=[a, b, c])
>>> products = db(db.product.tags.contains(b)).select()
>>> for item in products:
        print item.name, item.tags
hračka [1, 2, 3]
>>> for item in products:
        print item.name, db.product.tags.represent(item.tags)
hračka červená, zelená, modrá

Všimněme si, že pole list:reference tag dostalo defaultní omezení (constraint):

requires = IS_IN_DB(db, 'tag.id', db.tag._format, multiple=True)

což vytvoří ve formulářích SELECT/OPTION drop-box s možností výběru více položek.

Také si všimněte, že toto pole dostalo defaultní atribut represent, který vytvoří čárkami oddělený seznam formátovaných odkazů. To se použije v readonly (jen pro čtení) výpisech, jaké provádí např. SQLTABLE.

Zatímco list:reference má defaultní validátor a reprezentaci, pole typu list:integer a list:string ji stanovenu nemají. Takže pokud chcete tato pole použít ve formulářích, je potřeba jim pomocí requires=.. přiřadit IS_IN_SET nebo IS_IN_DB validátor.

Další operátory

Web2py má další operátory pro používání obdobných operátorů SQL jazyka. Dejme tomu, že budeme mít jinou tabulku "log" pro zaznamenávání událostí, důležitých z hlediska bezpečnosti, času jejich vzniku (event_time) a závažnosti (severity), kterou ohodnotíme pomocí integer čísla.

>>> db.define_table('log', Field('event'),
                           Field('event_time', 'datetime'),
                           Field('severity', 'integer'))

Stejně jako minule, vložme několik událostí. Pro náš příklad nevadí, když budou mít stejný čas. Dáme jim ale odlišnou závažnost.

>>> import datetime
>>> now = datetime.datetime.now()
>>> print db.log.insert(
        event='port scan', event_time=now, severity=1)
1
>>> print db.log.insert(
        event='xss injection', event_time=now, severity=2)
2
>>> print db.log.insert(
        event='unauthorized login', event_time=now, severity=3)
3

like, ilike, regexp, startswith, endswith, contains, upper, lower

Pole mají operátor like, který lze použít ke zjišťování shody řetězců:

>>> for row in db(db.log.event.like('port%')).select():
        print row.event
port scan

"port%" znamená řetězec, který začíná znaky "port". "%" je zástupný znak (wild-card) s významem jakékoli (jakkoli dlouhé) posloupnosti znaků.

Operátor like je mapován na frázi LIKE v ANSI-SQL. LIKE je ve většině databází case-sensitivní, případně zohledňuje aktuální třídění (collation). Takže like metoda je běžně case-sensitivní, ale to můžete potlačit pomocí:

db.mojetabulka.mojepole.like('hodnota', case_sensitive=False)

Ve Web2py máte také k dispozici tyto zkratky:

db.mojetabulka.mojepole.startswith('value')
db.mojetabulka.mojepole.endswith('value')
db.mojetabulka.mojepole.contains('value')

které jsou přibližně ekvivalentní tomuto zadání:

db.mojetabulka.mojepole.like('value%')
db.mojetabulka.mojepole.like('%value')
db.mojetabulka.mojepole.like('%value%')

Připomeňme si, že contains má speciální význam pro pole typu list:<type>, což jsme probrali v předchozím oddíle.

Metodu contains lze také volat se seznamem (list) hodnot a volitelným argumentem all. Pak najde záznamy, kde pole mojepole obsahuje všechny zadané hodnoty současně:

db.mojetabulka.mojepole.contains(['hodnota1', 'hodnota2'], all=True)

nebo které obsahují kteroukoli hodnotu ze seznamu:

db.mojetabulka.mojepole.contains(['hodnota1', 'hodnota2'], all=False)

K dispozici je také metoda regexp, která pracuje podobně jako like metoda, ale povoluje pro hledaný výraz syntaxi regulárních výrazů. Lze ji použít pouze u PostgreSQL, MySQL, Oracle a u SQLite (s rozdílnou mírou podpory).

Metody upper a lower umožňují převést obsah pole na velká, resp. malá písmena. Můžete je kombinovat s metodou like:

>>> for row in db(db.log.event.upper().like('PORT%')).select():
        print row.event
port scan

year, month, day, hour, minutes, seconds

Pole typů date a datetime mají metody day, month a year, datetime má navíc hour, minutes a seconds (obě poslední poněkud nesystematicky v množném čísle). Příklad je tady:

>>> for row in db(db.log.event_time.year()==2013).select():
        print row.event
port scan
xss injection
unauthorized login

belongs

SQL IN operátor můžeme vyvolat pomocí metody belongs, která vrátí True, jestliže je hodnota pole uvedena v zadané sekvenci (seznamu (list) nebo vektoru (tuple)):

>>> for row in db(db.log.severity.belongs((1, 2))).select():
        print row.event
port scan
xss injection

Jako argument metody belongs DAL umožňuje také vnořený select. Ten ale musí být přejmenován na _select a musí vracet jediné pole, které určí množinu povolených hodnot:

>>> mizerna_doba = db(db.log.severity==3)._select(db.log.event_time)
>>> for row in db(db.log.event_time.belongs(mizerna_doba)).select():
        print row.event
port scan
xss injection
unauthorized login

Výsledek není právě ilustrativní, protože jsme zkušební data naplnili stejným časem události. Vypsali jsme ale všechny události, ke kterým došlo ve stejné vteřině jako k nějaké události se závažností 3.

Tam, kde je potřeba vnořený select a kde pole, v němž hledáme je odkaz do jiné tabulky (reference; cizí klíč), můžeme také jako rgument použít dotaz (query). Například:

db.define_table('person', Field('name'))
db.define_table('thing', Field('name'), Field('owner_id', 'reference thing'))
db(db.thing.owner_id.belongs(db.person.name=='Jonathan')).select()

Zde se využívá toho, že je zřejmé, že vnořený select musí vracet pole odkazované cizím klíčem db.thing.owner_id, takže není potřeba toto zadávat pomocí podrobnější notace se _select.

Vnořený select se dá použít také pro případ vložení nebo aktualizace hodnoty, v takovém případě se ale používá odlišná syntaxe:

lazy = db(db.person.name=='Jonathan').nested_select(db.person.id)
db(db.thing.id==1).update(owner_id = lazy)

V tomto případě je lazy vnořený výraz pro určení id osoby "Jonathan". Obě řádky sestaví dohromady jediný SQL příkaz. (Pojmenování lazy je tedy zvoleno podle odloženého vyhodnocení.)

sum, avg, min, max and len

>>> sum = db.log.severity.sum()
>>> print db().select(sum).first()[sum]
6

db().select(sum) provede agregaci a vrátí ji jako Rows objekt (podobný seznamu). Metoda first() z tohoto seznamu vrátí první (v tomto případě jediný) záznam (Row objekt). V něm je jako prvek row._extra (nebo chcete-li row['_extra']) vnořen další Row objekt se samotným výsledkem agregace. Tento implementační detail ale není důležitý - výsledek sčítání získáte zkratkou row[sum].

Podobně můžete použít avg, min a max k získání průměru, nejmenší a největší hodnoty pole ve výsledných záznamech. Například:

>>> max = db.log.severity.max()
>>> print db().select(max).first()[max]
3

Poznamenejme ještě, že zatímco count vrátí správně 0, sum, avg, min a max mohou vracet None, pokud je nulový počet výsledných záznamů.

Metoda len spočítá délku údaje po jeho převodu na řetězec (str(..)).

Výrazy lze kombinovat do složitějších. Například zde spočítáme délku (počet znaků) všech čísel v poli severity, a chceme-li to např. vědět pro jejich vypsání za sebe, přidáme +1 pro mezeru mezi čísly:

>>> sum = (db.log.severity.len()+1).sum()
>>> print db().select(sum).first()[sum]

Podřetězce

Můžeme psát výrazy, které znamenají podřetězec (substring). Například můžete seskupit věci, jejichž jméno začíná stejnými třemi znaky a vybrat jen jednu takovou věc ze skupiny:

db(db.thing).select(distinct = db.thing.name[:3])

Defaultní hodnoty při coalesce a coalesce_zero

Někdy potřebujete získat hodnotu z databáze, ale zároveň pro ni použít default, je-li v databázi hodnota NULL. SQL má pro tento případ příkaz COALESCE. Web2py podobně nabízí metodu coalesce:

>>> db.define_table('sysuser', Field('username'), Field('fullname'))
>>> db.sysuser.insert(username='max', fullname='Max Power')
>>> db.sysuser.insert(username='tim', fullname=None)
print db(db.sysuser).select(db.sysuser.fullname.coalesce(db.sysuser.username))
"COALESCE(sysuser.fullname,sysuser.username)"
Max Power
tim

Jindy potřebujete spočítat hodnotu matematického výrazu, ale některé údaje vrací z databáze NULL, kdežto vy potřebujete, aby v tom případě byly nulové. Pomůže vám funkce coalesce_zero, která převede NULL/None hodnotu na 0:

>>> db.define_table('sysuser',Field('username'),Field('points'))
>>> db.sysuser.insert(username='max',points=10)
>>> db.sysuser.insert(username='tim',points=None)
>>> print db(db.sysuser).select(db.sysuser.points.coalesce_zero().sum())
"SUM(COALESCE(sysuser.points,0))"
10

Sestavení SQL příkazu bez vykonání

Někdy potřebujete sestavit SQL příkaz, aniž byste ho vykonali. Příkazy Web2py, které provádí přístup do databáze mají ekvivalent, který do databáze nepřistupuje a jen vrací sestavený příkaz, který by se (případně) vykonal. Tyto příkazy mají stejné jméno a signaturu (tj. parametry) jako příkazy, které do databáze přistupují, s tím rozdílem, že začínají podtržítkem:

Např. _insert

>>> print db.person._insert(name='Alex')
INSERT INTO person(name) VALUES ('Alex');

nebo _count

>>> print db(db.person.name=='Alex')._count()
SELECT count(*) FROM person WHERE person.name='Alex';

nebo _select

>>> print db(db.person.name=='Alex')._select()
SELECT person.id, person.name FROM person WHERE person.name='Alex';

nebo _delete

>>> print db(db.person.name=='Alex')._delete()
DELETE FROM person WHERE person.name='Alex';

a nakonec _update

>>> print db(db.person.name=='Alex')._update()
UPDATE person SET  WHERE person.name='Alex';

Kromě toho vždy po skutečném vykonání příkazu můžete pomocí db._lastsql zjistit naposledy provedený SQL příkaz. Jde o naposledy provedený příkaz, ať už vznikl automatickým sestavením pomoví DAL metody (bez podtržítka) nebo jste jej zadali pomocí executesql.

Export a import dat

CSV (jednotlivá tabulka)

Při konverzi Rows objektu na řetězec (string) dojde k automatické serializaci do CSV:

>>> rows = db(db.person.id==db.thing.owner_id).select()
>>> print rows
person.id,person.name,thing.id,thing.name,thing.owner_id
1,Alex,1,Boat,1
1,Alex,2,Chair,1
2,Bob,3,Shoes,2