Chapter 6: The database abstraction layer

Databázová abstrakční vrstva

DAL

Ú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).

database drivers

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ů:

DAL
SQLite
MySQL
PostgresSQL
Oracle
MSSQL
FireBird
DB2
Informix
Sybase
Teradata
MongoDB
CouchDB
SAPDB
Cubrid

databázeovladač
SQLitesqlite3 nebo pysqlite2 nebo zxJDBC [zxjdbc] (pro Jython)
PostgreSQLpsycopg2 [psycopg2] nebo pg8000 [pg8000] nebo zxJDBC [zxjdbc] (pro Jython)
MySQLpymysql [pymysql] nebo MySQLdb [mysqldb]
Oraclecx_Oracle [cxoracle]
MSSQLpyodbc [pyodbc] nebo pypyodbc [pypyodbc]
FireBirdkinterbasdb [kinterbasdb] nebo fdb nebo pyodbc
DB2pyodbc [pyodbc]
Informixinformixdb [informixdb]
Ingresingresdbi [ingresdbi]
Cubridcubriddb [cubridb]
SybaseSybase [Sybase]
Teradatapyodbc [Teradata]
SAPDBsapdb [SAPDB]
MongoDBpymongo [pymongo]
IMAPimaplib [IMAP]

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:

DAL
sqlite

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

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

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

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

insert
truncate
drop
import_from_csv_file

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

Field

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

Rows
Row

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ů):

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

Row obsahuje jednotlivá pole (jejich hodnoty).

1
2
for row in rows:
    print row.myfield
Query

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

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

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:

1
2
3
4
myset = db(myquery)
rows = myset.select()
myset.update(myfield='nejakahodnota')
myset.delete()
Expression

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

1
2
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

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

DAL konstruktor

Základní použití:

1
>>> 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í

_uri
1
2
3
                                                                                                                                                                                                                                          
>>> print db._uri
sqlite://storage.db

a jméno databáze

_dbname
1
2
>>> 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)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
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)

connection strings

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

1
>>> 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"):

ndb
SQLitesqlite://test.db
MySQLmysql://username:password@localhost/test
PostgreSQLpostgres://username:password@localhost/test
MSSQL(původní)mssql://username:password@localhost/test
MSSQL(>=2005)mssql3://username:password@localhost/test
MSSQL(>=2012)mssql4://username:password@localhost/test
FireBirdfirebird://username:password@localhost/test
Oracleoracle://username/password@test
DB2db2://username:password@test
Ingresingres://username:password@localhost/test
Sybasesybase://username:password@localhost/test
Informixinformix://username:password@test
Teradatateradata://DSN=dsn;UID=user;PWD=pass;DATABASE=test
Cubridcubrid://username:password@localhost/test
SAPDBsapdb://username:password@localhost/test
IMAPimap://user:password@server:port
MongoDBmongodb://username:password@localhost/test
Google/SQLgoogle:sql://project:instance/database
Google/NoSQLgoogle:datastore
Google/NoSQL/NDBgoogle:datastore+ndb

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:

1
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:

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

Jinak dostanete chyby UnicodeDecodeError.

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

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:

1
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

reserved Keywords

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:

1
2
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ř.:

1
check_reserved=['postgres', 'postgres_nonreserved']

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

PostgreSQLpostgres(_nonreserved)
MySQLmysql
FireBirdfirebird(_nonreserved)
MSSQLmssql
Oracleoracle

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.

ignore_field_case = Trueentity_quoting = True

Příklad:

1
2
3
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:

1
>>> 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:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
>>> 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.)

named id field

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:

1
2
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):

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

or

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

nebo i složitěji pomocí funkce:

1
2
>>> 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:

1
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:

1
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:

1
2
3
4
5
 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ř.

1
db.nejakatabulka.nejakepole == nejaka_hodnota
, a to by způsobilo, že tabulka nejakatabulka se připojí předčasně. Právě takovou situaci může zachránit (zlepšit) on_define.

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

lazy tables

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:

1
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:

1
db.table._extra = {} 

Field constructor

Field constructor

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

1
2
3
4
5
6
7
8
9
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
  • 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:
1
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:
1
2
3
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

field types
typ poledefaultní validátor
stringIS_LENGTH(length) default length je 512
textIS_LENGTH(65536)
blobNone
booleanNone
integerIS_INT_IN_RANGE(-1e100, 1e100)
doubleIS_FLOAT_IN_RANGE(-1e100, 1e100)
decimal(n,m)IS_DECIMAL_IN_RANGE(-1e100, 1e100)
dateIS_DATE()
timeIS_TIME()
datetimeIS_DATETIME()
passwordNone
uploadNone
reference <tabulka>IS_IN_DB(db, tabulka.pole, format)
list:stringNone
list:integerNone
list:reference <tabulka>IS_IN_DB(db, tabulka.pole, format, multiple=True)
jsonIS_JSON()
bigintNone
big-idNone
big-referenceNone

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

"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í:

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

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

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

Typ tabulky - instance třídy Table:

Table
1
2
>>> print type(db.person)
<class 'gluon.sql.Table'>

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

1
2
>>> print type(db['osoba'])
<class 'gluon.sql.Table'>

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

1
2
3
4
5
6
>>> print type(db.osoba.jmeno)
<class 'gluon.sql.Field'>
>>> print type(db.osoba['jmeno'])
<class 'gluon.sql.Field'>
>>> print type(db['osoba']['jmeno'])
<class 'gluon.sql.Field'>

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

1
2
3
4
5
6
7
8
>>> 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ří:

1
2
3
4
5
6
>>> 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

migrations

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":

1
>>> 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:

1
>>> 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í

fake_migrate

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í:

1
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:

1
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:

1
2
3
4
5
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:

insert
1
2
3
4
>>> 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:

truncate
1
>>> 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):

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

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

1
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.

bulk_insert

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

1
2
>>> 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ě:

commit
1
>>> db.commit()

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

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

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

rollback
1
>>> db.rollback()

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

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

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

1
2
3
4
5
6
7
8
9
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).

executesql
1
2
>>> 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.

1
[{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í:

_lastdb
1
2
3
>>> 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:

drop
1
>>> 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:

1
2
3
>>> 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.

keyed table

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:

1
2
3
4
5
6
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:

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

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

1
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:

1
2
>>> 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:

1
2
>>> 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:

1
2
>>> 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ě:

1
2
3
4
5
6
>>> 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:

1
2
3
4
5
6
7
>>> 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:

Table
1
>>> 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:

Field
1
>>> 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:

Query
1
>>> 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:

Set
1
>>> 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
select

1
>>> rows = s.select()
Row

Dostaneme iterovatelný objekt třídy gluon.sql.Rows, jehož položkami (prvky) jsou objekty Row. gluon.sql.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:

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

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

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

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":

1
2
3
4
5
>>> 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:

1
2
3
4
5
>>> 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:

1
2
3
4
5
>>> 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í.

1
2
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:

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

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

1
2
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":

1
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 shortcuts

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

1
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:

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

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

1
del db.mytable[id]

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

1
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:

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

což je ekvivalent

1
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:

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

což je ekvivalent úplné syntaxe

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

Získání Row

Další praktická syntaxe je tato:

1
2
3
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

recursive selects

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

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

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

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

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

1
>>> 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.

_id

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ě):

1
>>> 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):

1
2
3
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

1
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ů).

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

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

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

To je ekvivalentní zápisu:

1
2
3
{{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:

1
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í:

1
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:

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

SQLFORM.grid
SQLFORM.smartgrid

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:

1
2
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:

orderby
groupby
having

1
2
3
4
5
6
>>> 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)):

1
2
3
4
5
6
>>> 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í:

1
2
3
4
5
6
>>> 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:
1
2
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 "|":

1
2
3
4
5
6
>>> 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):

1
2
3
4
5
6
7
>>> 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í.

1
>>> 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
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:

1
2
3
4
5
>>> 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ř.:

1
2
3
4
5
>>> 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
limitby
1
2
3
4
>>> for row in db().select(db.person.ALL, limitby=(0, 2)):
        print row.name
Alex
Bob
orderby_on_limitby
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:

1
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 "&":

and
or
not

1
2
3
>>> 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 "|":

1
2
3
>>> 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 "!=":

1
2
3
4
>>> 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:

1
2
3
4
>>> 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():

count
isempty

1
2
>>> 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:

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

ekvivalentní, ale kratší, je:

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

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

delete
1
>>> 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í:

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

Výrazy

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

1
2
3
4
5
>>> 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:

1
2
3
4
5
>>> 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ř.:

1
2
3
4
5
6
7
>>> 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

update_record

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

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

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

1
>>> 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:

1
2
3
>>> 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:

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

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

_id

1
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

first
last

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

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

což je ekvivalent zápisu:

1
2
>>> 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

as_list
as_dict

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():

1
2
3
>>> 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í):

1
2
3
>>> 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):

1
2
3
4
>>> rows3 = rows1 & rows2
>>> print rows3
name
Tim

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

1
2
3
4
5
6
>>> rows3 = rows1 | rows2
>>> print rows3
name
Max
Tim
John

find, exclude, sort

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:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
>>> 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:

1
2
3
4
5
6
7
8
>>> 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

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:

1
2
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:

1
2
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:

1
2
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

validate_and_insert
validate_and_update

Funkce

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

pracuje podobně jako

1
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ě

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

pracuje podobně jako

1
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)

compute

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:

1
2
3
4
5
6
7
8
>>> 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

virtual fields

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:

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

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

1
2
3
>>> 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:

1
>>> 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:

1
2
3
4
5
>>> 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:

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

Definujeme virtuální pole total_price takto

1
2
3
4
>>> 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:

1
2
3
4
5
6
7
8
9
>>> 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:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
>>> 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:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
>>> 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:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
>>> 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:

1
2
3
>>> 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)

one to many

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

1
2
3
4
5
6
7
>>> 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:

1
2
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:

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

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

1
2
3
4
5
6
>>> 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:

1
2
3
4
>>> 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:

referencing
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
>>> 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
inner join
join

1
2
3
4
5
6
>>> 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:

1
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:

1
row.person.name

nebo:

1
row.thing.name

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

1
2
3
4
5
6
>>> 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
left outer join
outer join

1
2
3
4
5
6
7
8
9
>>> 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:

1
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:

grouping
1
2
3
4
5
6
>>> 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

many-to-many

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:

1
2
3
4
5
6
7
>>> 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:

1
2
3
>>> 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:

1
>>> 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:

1
2
>>> 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í:

1
2
3
4
5
6
>>> 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:

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

nebo všechny vlastníky člunu:

1
2
3
4
>>> 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

list:string
list:integer
list:reference
contains
multiple
tags

Web2py poskytuje tyto speciální typy polí:

1
2
3
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:

1
2
3
4
5
6
7
8
9
>>> 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:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
>>> 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):

1
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.

date
datetime
time

1
2
3
>>> 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.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
>>> 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

like
ilike
startswith
endswith
regexp
contains
upper
lower

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

1
2
3
>>> 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í:

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

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

1
2
3
db.mojetabulka.mojepole.startswith('value')
db.mojetabulka.mojepole.endswith('value')
db.mojetabulka.mojepole.contains('value')

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

1
2
3
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:

upper
lower

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

year, month, day, hour, minutes, seconds

hour
minutes
seconds
day
month
year

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:

1
2
3
4
5
>>> 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)):

belongs
1
2
3
4
>>> 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:

nested select
1
2
3
4
5
6
>>> 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:

1
2
3
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.

nested_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:

1
2
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
avg
min
max
Dříve jsme si popsali, jak použít operátor count ke spočítání záznamů. Podobně můžete použít operátor sum k sečtení hodnot číselného pole pro skupinu záznamů. Podobně jako u count získáme výsledek z Row objektu (což je, připomeňme si, vlastně slovník (dictionary) s rozšířenými vlastnostmi), kde klíč bude proměnná (sum):

1
2
3
>>> 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:

1
2
3
>>> 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:

1
2
>>> 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:

1
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í

raw SQL

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

_insert

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

nebo _count

_count

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

nebo _select

_select

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

nebo _delete

_delete

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

a nakonec _update

_update

1
2
>>> 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

export
import

CSV (jednotlivá tabulka)

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

csv
1
2
3
4
5
6
>>> 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

Tabulku tedy můžete exportovat do CSV souboru "test.csv" takto:

1
>>> open('test.csv', 'wb').write(str(db(db.person.id).select()))

a to je ekvivalent tohoto postupu:

1
2
>>> rows = db(db.person.id).select()
>>> rows.export_to_csv_file(open('test.csv', 'wb'))

CSV soubor můžete načíst zpět takto:

1
>>> db.person.import_from_csv_file(open('test.csv', 'r'))

Během importu Web2py hledá pole podle jmen polí v CSV hlavičce. V našem případě najde dva sloupce: "person.id" a "person.name". Bude ignorovat prefix "person." a pole "id". Všechny záznamy pak budou vloženy do tabulky databáze a tím získají nové id. Popsané operace můžete provádět přímo ve webovém appadmin rozhraní.

CSV (všechny tabulky společně)

Ve Web2py můžete zálohovat/obnovit (backup/restore) celou databázi pomocí příkazů:

Pro export:

1
>>> db.export_to_csv_file(open('somefile.csv', 'wb'))

Pro import:

1
>>> db.import_from_csv_file(open('somefile.csv', 'rb'))

Tento mechanismus může být použit také když jednotlivé databáze jsou různých typů (různé databázové stroje). Data se exportují do "somefile.csv" jako CSV soubor, kde každá tabulka začíná vždy dvěma řádky: na prvním je uvedeno jméno tabulky a následující řádek obsahuje jména polí:

1
2
TABLE tablename
field1, field2, field3, ...

Tabulky jsou vzájemně odděleny dvěma prázdnými řádky \r\n\r\n a soubor končí řádkem:

1
END

Soubor neobsahuje uploadované soubory, pokud nebyly uloženy přímo do databáze. Je však snadné zvlášť zazipovat adresář "uploads".

Když importujete a databáze není prázdná, nové záznamy budou do tabulek přidány. Záznamy obecně nebudou mít totéž id v databázi jako měly v csv souboru. Nicméně Web2py obnoví odkazy (cizí klíče), takže vazby (relace) mezi záznamy nebudou porušeny, bez ohledu na změny id.

Obsahuje-li tabulka pole se jménem "uuid", bude použito pro určení duplicit. Jestliže má importovaný záznam stejné "uuid" jako existující, bude ((?? předchozí ??)) záznam aktualizován.

CSV synchronizace vzdálených databází

Uvažujme následuující model:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
db = DAL('sqlite:memory:')
db.define_table('person',
    Field('name'),
    format='%(name)s')
db.define_table('thing',
    Field('owner_id', 'reference person'),
    Field('name'),
    format='%(name)s')

if not db(db.person).count():
    id = db.person.insert(name="Massimo")
    db.thing.insert(owner_id=id, name="Chair")

Každý záznam je identifikován pomocí ID a záznamy jiných tabulek na něj mohou pomocí tohoto ID odkazovat. Jestliže máte dvě kopie databáze, které používají dvě různé instalace Web2py, je ID unikátní jen uvnitř jednotlivé databáze a nikoli mezi oběma databázemi. Pak vzniká problém při slučování (merging) záznamů z různých databází.

Abychom záznam změnili na unikátně identifikovatelný mezi více databázemi, musí:

  • mít unikátní id (UUID),
  • mít event_time (abychom se mohli rozhodnout, která kopie je mezi více kopiemi nejaktuálnější),
  • odkazovat na UUID místo na id.

Tento režim lze zavést bez úpravy Web2py frameworku, a sice takto:

Změňte předešlý model:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
db.define_table('person',
    Field('uuid', length=64, default=lambda:str(uuid.uuid4())),
    Field('modified_on', 'datetime', default=request.now),
    Field('name'),
    format='%(name)s')

db.define_table('thing',
    Field('uuid', length=64, default=lambda:str(uuid.uuid4())),
    Field('modified_on', 'datetime', default=request.now),
    Field('owner_id', length=64),
    Field('name'),
    format='%(name)s')

db.thing.owner_id.requires = IS_IN_DB(db, 'person.uuid', '%(name)s')

if not db(db.person.id).count():
    id = uuid.uuid4()
    db.person.insert(name="Massimo", uuid=id)
    db.thing.insert(owner_id=id, name="Chair")
Všimněte si, že v předchozích definicích tabulek je defaultní hodnota pro obě uuid pole zadána pomocí lambda funkce, která vrátí UUID, zkonvertované na řetězec. lambda funkce se zavolá jedenkrát pro kterýkoli vložený záznam a zajistí, že každý záznam dostane unikátní UUID.

Vytvořme akci (funkci) controlleru pro export databáze:

1
2
3
4
5
def export():
    s = StringIO.StringIO()
    db.export_to_csv_file(s)
    response.headers['Content-Type'] = 'text/csv'
    return s.getvalue()

Vytvořme akci controlleru pro import uložené kopie druhé databáze a synchronizaci záznamů:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
def import_and_sync():
    form = FORM(INPUT(_type='file', _name='data'), INPUT(_type='submit'))
    if form.process().accepted:
        db.import_from_csv_file(form.vars.data.file,unique=False)
        # for every table
        for table in db.tables:
            # for every uuid, delete all but the latest
            items = db(db[table]).select(db[table].id,
                       db[table].uuid,
                       orderby=db[table].modified_on,
                       groupby=db[table].uuid)
            for item in items:
                db((db[table].uuid==item.uuid)&                   (db[table].id!=item.id)).delete()
    return dict(form=form)

Eventuálně je vhodné ručně vytvořit index podle uuid, aby hledání uuid probíhalo rychleji.

XML-RPC

Pro export/import souboru můžete také použít XML-RPC.

Jestliže záznamy odkazují na uploadované soubory, potřebujete ještě exportovat/importovat obsah adresáře uploads. Soubory v uploads adresáři jsou už označeny pomocí UUID, takže se nemusíte obávat konfliktů a poruch odkazů při slučování databází.

HTML a XML (jednotlivá tabulka)

Rows objects

Rows objekty mají také metodu xml (stejně jako HTML helpers), která je serializuje na XML/HTML:

HTML
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
>>> rows = db(db.person.id > 0).select()
>>> print rows.xml()
<table>
  <thead>
    <tr>
      <th>person.id</th>
      <th>person.name</th>
      <th>thing.id</th>
      <th>thing.name</th>
      <th>thing.owner_id</th>
    </tr>
  </thead>
  <tbody>
    <tr class="even">
      <td>1</td>
      <td>Alex</td>
      <td>1</td>
      <td>Boat</td>
      <td>1</td>
    </tr>
    ...
  </tbody>
</table>
Rows custom tags

Pokud potřebujete serializovat Rows do jakéhokoli jiného XML formátu s proprietárními tagy, můžete využít univerzální helper TAG a notaci pro sekvenci s *:

XML
1
2
3
4
5
6
7
8
9
>>> rows = db(db.person.id > 0).select()
>>> print TAG.result(*[TAG.row(*[TAG.field(r[f], _name=f)           for f in db.person.fields]) for r in rows])
<result>
  <row>
    <field name="id">1</field>
    <field name="name">Alex</field>
  </row>
  ...
</result>

Representace exportovaných dat

export_to_csv_file

Funkce export_to_csv_file má pojmenovaný parametr represent. Nastavíte-li jej na True, použije se výsledek represent funkce příslušných polí místo údaje přímo z databáze.

colnames

Pomocí pojmenovaného parametru colnames lze také zadat výčet polí, které si přejeme exportovat. Defaultně se exportují všechny sloupce.

Obě funkce export_to_csv_file a import_from_csv_file mají další parametry, kterými lze ovlivnit strukturu csv souboru:

  • delimiter: oddělovač jednotlivých údajů (defaultně ',')
  • quotechar: omezovač řetězců (defaultně "..")
  • quoting: systém použití omezovačů (default csv.QUOTE_MINIMAL)

Tady je příklad:

1
2
3
4
5
6
>>> import csv
>>> rows = db(query).select()
>>> rows.export_to_csv_file(open('/tmp/test.txt', 'w'),
        delimiter='|',
        quotechar='"',
        quoting=csv.QUOTE_NONNUMERIC)

Výsledek bude vypadat nějak takto

1
"nazdar"|35|"nějký podrobný popis"|"2013-03-03"

Pro podrobnější informace se podívejte do oficiální dokumentace Pythonu. [quoteall]

Kešování selectu (caching)

Metoda select má také parametr cache, s defaultní hodnotou None. Chceme-li výsledky cacheovat (opakovat nedávný výsledek bez dalšího dotazu do databáze), nastavíme tento argument na vektor (tuple), jehož prvním prvkem bude kešovací mechanismus (cache.ram, cache.disk, apod.) a druhým prvkem doba platnosti keše v sekundách.

V následujícím příkladu vidíte controller, který kešuje výběr záznamů z výše definované tabulky db.log. Skutečné výsledky se z databáze přenášejí nejčastěji jednou za 60 sekund, při dřívějším dalším dotazu se poskytnou z cache.ram.

cache select
1
2
3
def cache_db_select():
    logs = db().select(db.log.ALL, cache=(cache.ram, 60))
    return dict(logs=logs)
cacheable

Metoda select má volitelný parametr cacheable, který je normálně nastaven na False. Nastavíte-li cacheable=True, bude výsledný Rows objekt normálně serializovatelný, ale Row objekty nebudou mít metodu update_record ani delete_record.

Jestliže tyto metody v daném provozním scénáři nepotřebujete, můžete získat vyšší výkonnost nastavením atributu cacheable:

1
rows = db(query).select(cacheable=True)

Když je nastaven cache argument, ale cacheable=False (defaultně) jsou kešovány výsledky z databáze, ale ne samotný Rows objekt. Spojíme-li argument cache a cacheable=True, je kešován i výsledný Rows objekt a výsledkem jsou mnohem rychlejší doby přístupu:

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

Odkaz na záznamy stejné tabulky (self-reference) a aliasy

self reference
alias

Můžete definovat tabulky s poli (cizími klíči), které odkazují na stejnou tabulku. Tady je příklad:

reference table
1
2
3
4
db.define_table('person',
    Field('name'),
    Field('father_id', 'reference person'),
    Field('mother_id', 'reference person'))

Ovšem alternativní zápis pomocí table objektu (db.person) jako typu pole, by zde selhal, protože použije proměnnou db.person ještě dříve, než je definována:

1
2
3
4
db.define_table('person',
    Field('name'),
    Field('father_id', db.person), # wrong!
    Field('mother_id', db.person)) # wrong!

Ačkoli obecně vzato jsou db.tablename a "reference tablename" ekvivalentní typy pole, jen druhý zápis se dá použít pro případ, kdy definice odkazované tabulky ještě není hotova.

with_alias

Pokud tabulka odkazuje sama na sebe (rozuměj: její záznamy na jiné záznamy té samé tabulky, např. osoba a její rodiče), není možné použít JOIN, aniž by SQL příkaz obsahoval frázi "AS". Ve Web2py tuto druhou kopii stejné tabulky zpřístupníme metodou with_alias. Tady je příklad:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
>>> Father = db.person.with_alias('father')
>>> Mother = db.person.with_alias('mother')
>>> db.person.insert(name='Massimo')
1
>>> db.person.insert(name='Claudia')
2
>>> db.person.insert(name='Marco', father_id=1, mother_id=2)
3
>>> rows = db().select(db.person.name, Father.name, Mother.name,
      left=(Father.on(Father.id==db.person.father_id),
            Mother.on(Mother.id==db.person.mother_id)))
>>> for row in rows:
        print row.person.name, row.father.name, row.mother.name
Massimo None None
Claudia None None
Marco Massimo Claudia

Upřesněme si význam:

  • "father_id": je jméno pole v tabulce "person";
  • "father": je alias, kterým zpřístupňujeme tabulku "person" v situaci, kdy se na její záznamy odkazujeme pomocí předchozího pole - toto je v SQL dotazu předáno i do databáze
  • "Father": je proměnná Web2py, kterou se na tento alias odkazujeme.

Odlišnosti jsou malé, a tak byste ani nic zásadního nepokazili, kdybyste všechny tři pojmenovali stejně:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
db.define_table('person',
    Field('name'),
    Field('father', 'reference person'),
    Field('mother', 'reference person'))
>>> father = db.person.with_alias('father')
>>> mother = db.person.with_alias('mother')
>>> db.person.insert(name='Massimo')
1
>>> db.person.insert(name='Claudia')
2
>>> db.person.insert(name='Marco', father=1, mother=2)
3
>>> rows = db().select(db.person.name, father.name, mother.name,
      left=(father.on(father.id==db.person.father),
            mother.on(mother.id==db.person.mother)))
>>> for row in rows:
        print row.person.name, row.father.name, row.mother.name
Massimo None None
Claudia None None
Marco Massimo Claudia

Je však vhodné rozdílům rozumět, zejména při (odlišném pojmenování a) sestavování dotazů.

Pokročilé vlastnosti

Dědičnost tabulek

inheritance

Je možné definovat tabulku, která převezme definice všech polí z jiné tabulky. V tom případě stačí uvést takovou tabulku na místě argumentu Field ve volání metody define_table. Například:

1
2
db.define_table('person', Field('name'))
db.define_table('doctor', db.person, Field('specialization'))
dummy table

Je také možné definovat tabulku, která fyzicky nebude existovat, jen se použije opakovaně pro definování odvozených tabulek. Příklad je zde:

1
2
3
4
5
6
7
signature = db.Table(db, 'signature',
    Field('created_on', 'datetime', default=request.now),
    Field('created_by', db.auth_user, default=auth.user_id),
    Field('updated_on', 'datetime', update=request.now),
    Field('updated_by', db.auth_user, update=auth.user_id))

db.define_table('payment', Field('amount', 'double'), signature)

Příklad předpokládá, že je ve Web2py aplikaci zapnutá standardní autentikace.

Když se používá Auth objekt, Web2py už pro vás jednu takovou fiktivní tabulku vytvoří a můžete ji využít:

auth = Auth(db)
db.define_table('payment', Field('amount', 'double'), auth.signature)

Jestliže jste se rozhodli použít dědičnost tabulek a chcete, aby se převzaly i validátory polí, pamatujte, že validátory musí být definovány dříve, než definujete odvozenou tabulku.

filter_in a filter_out

filter_in
filter_out

Je možné definovat filtr na každé pole, který se zavolá před vložením hodnoty do databáze a poté, co se hodnota z databáze získá.er a value is retrieved from the database.

Např. byste chtěli zajistit, aby se nějaká serializovatelná datová struktura Pythonu ukládala do databáze v json formátu. Můžete si to zařídit takto:

1
2
3
4
5
6
7
8
9
>>> from simplejson import loads, dumps
>>> db.define_table('anyobj', Field('name'), Field('data','text'))
>>> db.anyobj.data.filter_in = lambda obj, dumps=dumps: dumps(obj)
>>> db.anyobj.data.filter_out = lambda txt, loads=loads: loads(txt)
>>> myobj = ['hello', 'world', 1, {2: 3}]
>>> id = db.anyobj.insert(name='myobjname', data=myobj)
>>> row = db.anyobj(id)
>>> row.data
['hello', 'world', 1, {2: 3}]

Jiný způsob je definovat pole typu SQLCustomType, což probereme později.

callbacky (funkce volané před a po akci) při insert, delete a update

_before_insert
_after_insert
_before_update
_after_update
_before_delete
_after_delete

Web2py má mechanismus registrování callbacků, funkcí, které se provedou před resp. po insert, update nebo delete.

Každá tabulka má 6 seznamů (list) callback funkcí:

1
2
3
4
5
6
db.mytable._before_insert
db.mytable._after_insert
db.mytable._before_update
db.mytable._after_update
db.mytable._before_delete
db.mytable._after_delete

Požadavek vykonávání funkce můžete zaregistrovat přidáním (append) funkce do některého z těchto seznamů. Určitá obtíž je v tom, že podle použití se liší signatura funkce (parametry).

Nejsnáze si to ukážeme na příkladech.

1
2
3
4
5
6
7
8
>>> db.define_table('person', Field('name'))
>>> def pprint(*args): print args
>>> db.person._before_insert.append(lambda f: pprint(f))
>>> db.person._after_insert.append(lambda f,id: pprint(f,id))
>>> db.person._before_update.append(lambda s,f: pprint(s,f))
>>> db.person._after_update.append(lambda s,f: pprint(s,f))
>>> db.person._before_delete.append(lambda s: pprint(s))
>>> db.person._after_delete.append(lambda s: pprint(s))

f je slovník (dictionary) polí, předaný do insert nebo update, id je id nově založeného záznamu, s je Set objekt, který určuje záznamy, ovlivněné updatem nebo deletem.

1
2
3
4
5
6
7
8
9
>>> db.person.insert(name='John')
({'name': 'John'},)
({'name': 'John'}, 1)
>>> db(db.person.id==1).update(name='Tim')
(<Set (person.id = 1)>, {'name': 'Tim'})
(<Set (person.id = 1)>, {'name': 'Tim'})
>>> db(db.person.id==1).delete()
(<Set (person.id = 1)>,)
(<Set (person.id = 1)>,)

Hodnota, vrácená z callbacků, má být None nebo False. Jestliže některý z _before_* callbacků vrátí True, zabrání tím aktuální insert/update/delete operaci.

update_naive

Může nastat případ, kdy callback má provést update v té samé tabulce nebo v jiné tabulce, a my chceme zabránit, aby se callbacky v tomto případě vyvolávaly rekurzivně.

Z toho důvodu má Set objekt ještě metodu update_naive, která pracuje stejně jako update, ale už neaktivuje žádné z právě definovaných callbacků.

Databázová podpora kaskády

Databázové schéma může definovat vazby mezi tabulkami, kdy zrušení záznamu vyvolá (triggers) zrušení závislých záznamů, což je označováno jako kaskáda. DAL nedostane žádnou informaci, když je záznam kaskádně zrušen v databázi. Proto není on_delete trigger volán během kaskádního rušení.

Verzování záznamů

_enable_record_versioning

Je možné požadovat po Web2py, aby ukládalo všechny stavy záznamů během historie jejich postupné modifikace. Je více možností, jak to udělat, a je možné to zajistit naráz pro všechny tabulky za pomoci této syntaxe:

1
auth.enable_record_versioning(db)

vyžaduje to používat Auth objekt a více se o tom píše v kapitole o autentikaci. Také je možné tuto vlastnost aktivovat jen pro jednotlivé tabulky, což popíšeme zde.

Představte si následující tabulku:

1
2
3
4
5
db.define_table('stored_item',
    Field('name'),
    Field('quantity', 'integer'),
    Field('is_active', 'boolean',
          writable=False, readable=False, default=True))

Všimněte si skrytého boolean pole is_active, které má defaultní hodnotu True.

Můžeme Web2py říci, aby vytvořilo novou tabulku (ve stejné nebo jiné databázi) a uložit do ní všechny starší verze záznamů, jakmile byly změněny.

Provede se to takto:

1
db.stored_item._enable_record_versioning()

případně výřečnější syntaxe:

db.stored_item._enable_record_versioning(
    archive_db = db,
    archive_name = 'stored_item_archive',
    current_record = 'current_record',
    is_active = 'is_active')

archive_db=db řekne, ve které databázi se má archivní tabulka udržovat, v tomto případě ve stejné jako "živá" tabulka stored_item. archive_name zadává jméno pro archivní tabulku. Archivní tabulka má stejná pole jako "živá" tabulka, ale s tím rozdílem, že unikátní pole už nadále nejsou unique (protože uložíme více verzí stejného záznamu) a má jedno pole navíc, jehož jméno zadáváme argumentem current_record - v tomto poli bude reference na aktuální záznam v "živé" tabulce (stored_item).

Při rušení záznamů se záznamy neruší fyzicky. Poslední obsah záznamu se zkopíruje do archivní tabulky stored_item_archive (stejně jako při modifikaci záznamu) a pole is_active se nastaví na False. Povolením verzování záznamů Web2py zapne filtr custom_filter nad tabulkou stored_item a skryje záznamy, kde is_active je False. Parametr is_active metody _enable_record_versioning vám případně umožňuje použít jinak pojmenované pole pro zjišťování, zda je záznam zrušený nebo platný.

custom_filter jsou ignorovány v appadmin rozhraní, kde tedy vidíte a můžete pracovat i se smazanými záznamy.

Společná pole a rozdělené vlastnictví tabulky

common fields
multi tenancy

db._common_fields je seznam polí, které chceme mít ve všech tabulkách. Pokud by místo pole byla uvedena tabulka, zařadí se všechna pole z tabulky. Dejme tomu, že chcete sledovat, kdo upravil záznam, a to ve všech tabulkách kromě tabulek auth (autentikace). Pak mezi inicializaci auth a před definici dalších tabulek vložíme:

db._common_fields.append(auth.signature)

Existuje speciální pole: "request_tenant". Normálně neexistuje, ale v kterékoli tabulce (nebo třeba ve všech) si jej můžete vytvořit:

db._common_fields.append(Field('request_tenant',
    default=request.env.http_host, writable=False))

Má-li tabulka pole db._request_tenant ('request_tenant'), její záznamy, získané z kteréhokoli dotazu (query), budou autoaticky zafiltrovány podle defaultní hodnoty pole 'request_tenant':

1
db.table.request_tenant == db.table.request_tenant.default

V každém přidaném záznamu bude toto pole nastaveno na (právě tuto) defaultní hodnotu. V předchozím příkladě jsme zadali

default = request.env.http_host

neboli rozhodli jsme se, že chceme, aby naše aplikace filtrovala všechny záznamy ze všech dotazů filtrem

db.table.request_tenant == request.env.http_host

Tímto jednoduchým trikem můžeme ze kterékoli aplikace udělat aplikaci paralelně používanou více vlastníky, neboli - ačkoli provozujeme jedinou instanci aplikace nad jedinou databází - jestliže budou uživatelé (bude uživatel) aplikaci používat pod různými doménami (doména je obsažena v request.env.http_host), uvidí data jen pro svoji konkrétní doménu. Můžeme tedy třeba provozovat více obchodů pod různými doménami - s jedinou aplikací a jedinou databází.

Filtr rozděleného vlastnictví záznamů můžete vypnout pomocí

ignore_common_filters
1
rows = db(query, ignore_common_filters=True).select()

Trvalé filtry

Trvalý filtr je generalizací výše popsané myšlenky rozděleného vlastnictví. Umožňuje se vyhnout neustálému opakování stejného dotazu. Např. předpokládejme tuto definici:

db.define_table('blog_post',
    Field('subject'),
    Field('post_text', 'text'),
    Field('is_public', 'boolean'),
    common_filter = lambda query: db.blog_post.is_public==True
)

Jakýkoli select, delete nebo update v této tabulce se bude týkat jen veřejných blogových příspěvků. Atribut můžete nastavit také v controlleru:

db.blog_post._common_filter = lambda query: db.blog_post.is_public == True

Jednak tím zabráníte neustálému opakování fráze "db.blog_post.is_public==True", jednak se na toto můžeme dívat i jako na bezpečnostní vylepšení, které brání tomu, abyste někdy nechtíc (díky opomenutí připsání filtru) zobrazili neveřejný příspěvek.

V případech, kdy chcete obejít trvalý filtr (např. když chcete administrátorovi dát možnost prohlížet neveřejné příspěvky), můžete trvalý filtr vypnout:

db.blog_post._common_filter = None

nebo jej během DAL příkazu ignorovat:

db(query, ignore_common_filters=True).select(...)

Uživatelsky definované typy polí (experimentální)

SQLCustomType

Kromě možnosti použít filter_in a filter_out, můžete použít uživatelsky definované typy polí. Např. si vytvoříme pole, které bude mít uložena binární data v komprimované formě:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
from gluon.dal import SQLCustomType
import zlib

compressed = SQLCustomType(
     type ='text',
     native='text',
     encoder =(lambda x: zlib.compress(x or '')),
     decoder = (lambda x: zlib.decompress(x))
)

db.define_table('example', Field('data', type=compressed))

SQLCustomType je předloha pro typy polí. Jejím type argumentem musí být jeden ze standardních typů Web2py frameworku. Říká Web2py, jak má s polem zacházet na úrovni Web2py. native je typ pole z hlediska databáze. Povolené hodnoty tedy záleží na konkrétním databázovém stroji. encoder je (nepovinná) transformační funkce, která se provede při ukládání do databáze a decoder je inverzní transformační funkce (použitá při zjišťování hodnoty z databáze).

Tato vlastnost je označena jako experimentální. Ve skutečnosti je ve Web2py již dlouho a lze ji používat, ale může potlačit přenositelnost kódu nejčastěji tím, je-li native typ pole databázově specifický. Tato vlastnost nepracuje s Google App Engine NoSQL.

Použití DAL bez definice tabulek

DAL může být používána z kteréhokoli programu, napsaného v Pythonu, stačí udělat např. toto:

1
2
from gluon import DAL, Field
db = DAL('sqlite://storage.sqlite', folder='path/to/app/databases')

tedy importovat DAL a Field, připojit se do databáze, k čemuž je v případě používání mimo prostředí Web2py aplikace potřeba uvést adresář, kde se budou ukládat metadata o tabulkách (soubory .table) a v případě SQLite i samotný soubor databáze.

Chceme-li přistupovat k datům a k Web2py atributům, které popisují tabulky a data, musíme definovat všechny tabulky, které chceme používat, pomocí db.define_tables(...).

Jestliže ale chceme jen přistupovat k datům a nechceme využívat atributy tabulek a dat, můžeme místo provedení definic tabulek říci Web2py, aby jen načetlo potřebné informace z aktuálních metadat v souborech .table:

1
2
3
from gluon import DAL, Field
db = DAL('sqlite://storage.sqlite', folder='path/to/app/databases',
         auto_import=True)

To nám umožní přistupovat ke kterékoli tabulce db.table, aniž bychom ji definovali.

PostGIS, SpatiaLite a MS Geo (experimentální)

PostGIS
StatiaLite
Geo Extensions
geometry
geoPoint
geoLine
geoPolygon

DAL podporuje geografická API pomocí PostGIS (pro PostgreSQL), spatialite (pro SQLite) a MSSQL se Spatial Extensions. Tato vlastnost byla sponzorována projektem Sahana a implementována Denesem Lengyelem.

DAL poskytuje geometry a geography typy polí a následující funkce:

st_asgeojson
st_astext
st_contains
st_distance
st_equals
st_intersects
st_overlaps
st_simplify
st_touches
st_within

st_asgeojson (jen PostGIS)
st_astext
st_contains
st_distance
st_equals
st_intersects
st_overlaps
st_simplify (jen PostGIS)
st_touches
st_within
st_x
st_y

Zde je několik příkladů:

1
2
3
from gluon.dal import DAL, Field, geoPoint, geoLine, geoPolygon
db = DAL("mssql://user:pass@host:db")
sp = db.define_table('sp', Field('loc', 'geometry()'))  # sp=spatial

Vložíme bod (uzel), čáru, a plochu (polygon, mnohoúhelník):

1
2
3
sp.insert(loc=geoPoint(1,1))
sp.insert(loc=geoLine((100,100),(20,180),(180,180)))
sp.insert(loc=geoPolygon((0,0),(150,0),(150,150),(0,150),(0,0)))

Poznamenejme, že

1
rows = db(sp.id>0).select()

vrací vždy data geometrie serializovaná jako text. Totéž můžete zadat více explicitně pomocí st_astext():

1
2
3
4
5
print db(sp.id>0).select(sp.id, sp.loc.st_astext())
spatial.id, spatial.loc.STAsText()
1, "POINT (1 2)"
2, "LINESTRING (100 100, 20 180, 180 180)"
3, "POLYGON ((0 0, 150 0, 150 150, 0 150, 0 0))"

Můžete data přečíst jako nativní reprezentaci pomocí st_asgeojson() (jen PostGIS):

1
2
3
4
5
print db(sp.id>0).select(sp.id, sp.loc.st_asgeojson().with_alias('loc'))
spatial.id, loc
1, [1, 2]
2, [[100, 100], [20 180], [180, 180]]
3, [[[0, 0], [150, 0], [150, 150], [0, 150], [0, 0]]]

(všimněme si, že pole (array) znamená bod (uzel), pole polí (array of arrays) čáru, a pole, jehož obsahem je pole polí, znamená polygon.

Zde jsou příklady, jak používat geografické funkce:

1
2
3
4
5
6
query = sp.loc.st_intersects(geoLine((20,120),(60,160)))
query = sp.loc.st_overlaps(geoPolygon((1,1),(11,1),(11,11),(11,1),(1,1)))
query = sp.loc.st_contains(geoPoint(1,1))
print db(query).select(sp.id, sp.loc)
spatial.id, spatial.loc
3,"POLYGON ((0 0, 150 0, 150 150, 0 150, 0 0))"

Vypočtené vzdálenosti můžeme také získat jako floating čísla:

1
2
3
4
5
6
dist = sp.loc.st_distance(geoPoint(-1,2)).with_alias('dist')
print db(sp.id>0).select(sp.id, dist)
spatial.id, dist
1 2.0
2 140.714249456
3 1.0

Kopírování dat mezi dvěma db

Dejme tomu, že používáte následujíc databázi:

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

a chcete přejít na jinou a změnit řetězec připojení (connection string):

db = DAL('postgres://username:password@localhost/mydb')

Než přejdete, chcete přesunout data a přebudovat metadata (soubory .table) pro novou databázi. Předpokládáme, že novou databázi máme vytvořenou a že je prázdná.

Web2py poskytuje skript, který toto provede za vás:

1
2
3
4
5
6
cd web2py
python scripts/cpdb.py \
   -f applications/app/databases \
   -y 'sqlite://storage.sqlite' \
   -Y 'postgres://username:password@localhost/mydb' \
   -d ../gluon

Po provedení skriptu můžete jednoduše connection string v modelu zaměnit za nový a vše by mělo pracovat, s původními daty v nové databázi.

Uvedený skript nabízí command line přepínače pro přesun dat mezi aplikacemi, přesun všech nebo jen vybraných tabulek, smazání dat v tabulkách, apod. Pro více informací napište:

python scripts/cpdb.py -h

Poznámka k "nové" verzi DAL a adaptérům (driverům)

Zdrojový kód DAL byl zcela přepsán v roce 2010, a to jako zpětně kompatibilní, ale více modulární a snadněji rozšiřovatelný. Zde vysvětlíme hlavní logiku.

Soubor "gluon/dal.py" definuje, mimo jiné, následující třídy.

ConnectionPool
BaseAdapter extends (děděno z) ConnectionPool
Row
DAL
Reference
Table
Expression
Field
Query
Set
Rows

Řekli jsme si o nich v předchozích oddílech, s výjimkou BaseAdapter. Jestliže metoda Table nebo Set objektu chce komunikovat s databází, deleguje metodám adaptéru samotné sestavení SQL příkazu nebo zavolání funkce.

Např.:

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

zavolá:

Table.insert(myfield='myvalue')

a ta to deleguje aktuálnímu konkrétnímu adaptéru takto:

db._adapter.insert(db.mytable, db.mytable._listify(dict(myfield='myvalue')))

db.mytable._listify zkonvertuje slovník (dictionary) argumentů na seznam (list) párů (field,value) a zavolá insert metodu adaptéru. db._adapter udělá více nebo méně toto:

query = db._adapter._insert(db.mytable, list_of_fields)
db._adapter.execute(query)

kde první řádek sestaví dotaz a druhý řádek jej provede.

Pozn. překladatele: V předchozím textu překladu není možná vždy důsledně rozlišován adaptér a driver (ovladač). Zde se tedy snažím být přesnější. Driverem (ovladačem) se myslí modul Pythonu pro používání konkrétního databázového stroje, adaptérem se myslí kód Web2py, který přizpůsobuje absatrakční jazyk DAL konkrétnímu databázovému stroji, z větší části právě pomocí jeho driveru (ovladače).

BaseAdapter definuje společné rozhraní všech adaptérů.

"gluon/dal.py" v době psaní tohoto oddílu obsahuje následující adaptéry:

SQLiteAdapter extends BaseAdapter
JDBCSQLiteAdapter extends SQLiteAdapter
MySQLAdapter extends BaseAdapter
PostgreSQLAdapter extends BaseAdapter
JDBCPostgreSQLAdapter extends PostgreSQLAdapter
OracleAdapter extends BaseAdapter
MSSQLAdapter extends BaseAdapter
MSSQL2Adapter extends MSSQLAdapter
MSSQL3Adapter extends MSSQLAdapter
MSSQL4Adapter extends MSSQLAdapter
FireBirdAdapter extends BaseAdapter
FireBirdEmbeddedAdapter extends FireBirdAdapter
InformixAdapter extends BaseAdapter
DB2Adapter extends BaseAdapter
IngresAdapter extends BaseAdapter
IngresUnicodeAdapter extends IngresAdapter
GoogleSQLAdapter extends MySQLAdapter
NoSQLAdapter extends BaseAdapter
GoogleDatastoreAdapter extends NoSQLAdapter
CubridAdapter extends MySQLAdapter (experimentální)
TeradataAdapter extends DB2Adapter (experimentální)
SAPDBAdapter extends BaseAdapter (experimentální)
CouchDBAdapter extends NoSQLAdapter (experimentální)
IMAPAdapter extends NoSQLAdapter (experimentální)
MongoDBAdapter extends NoSQLAdapter (experimentální)
VerticaAdapter extends MSSQLAdapter (experimentální)
SybaseAdapter extends MSSQLAdapter (experimentální)

které přepisují nebo upřesňují chování ovladače BaseAdapter.

Každý adaptér má přibližně tuto strukturu:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
class MySQLAdapter(BaseAdapter):

    # driver, který se má použít
    driver = globals().get('pymysql', None)

    # mapování Web2py typů na typy databázového stroje
    types = {
        'boolean': 'CHAR(1)',
        'string': 'VARCHAR(%(length)s)',
        'text': 'LONGTEXT',
        ...
        }

    # připojení do databáze za pomoci driveru
    def __init__(self, db,uri, pool_size=0, folder=None, db_codec ='UTF-8',
                credential_decoder=lambda x:x, driver_args={},
                adapter_args={}):
        # analyzuj (parse) řetězec připojení (uri string) a parametry ulož jako driver_args
        ...
        # definuj funkci pro připojení
        def connect(driver_args=driver_args):
            return self.driver.connect(**driver_args)
        # vlož ji do zásobníku (poolu)
        self.pool_connection(connect)
        # nastav volitelné parametry (po připojení)
        self.execute('SET FOREIGN_KEY_CHECKS=1;')
        self.execute("SET sql_mode='NO_BACKSLASH_ESCAPES';")

   # přepiš metody základní třídy BaseAdapter podle potřeby
   def lastrowid(self, table):
        self.execute('select last_insert_id();')
        return int(self.cursor.fetchone()[0])

Podíváme-li se na různé existující adaptéry, nebude těžké napsat případný adaptér nový.

Když vytváříme instanci db:

db = DAL('mysql://...')

tak prefix uri řetězce (řetězce připojení) určuje použitý adaptér. Mapování určuje tento slovník (dictionary), který je rovněž součástí "gluon/dal.py":

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
ADAPTERS = {
    'sqlite': SQLiteAdapter,
    'spatialite': SpatiaLiteAdapter,
    'sqlite:memory': SQLiteAdapter,
    'spatialite:memory': SpatiaLiteAdapter,
    'mysql': MySQLAdapter,
    'postgres': PostgreSQLAdapter,
    'postgres:psycopg2': PostgreSQLAdapter,
    'postgres:pg8000': PostgreSQLAdapter,
    'postgres2:psycopg2': NewPostgreSQLAdapter,
    'postgres2:pg8000': NewPostgreSQLAdapter,
    'oracle': OracleAdapter,
    'mssql': MSSQLAdapter,
    'mssql2': MSSQL2Adapter,
    'mssql3': MSSQL3Adapter,
    'mssql4' : MSSQL4Adapter,
    'vertica': VerticaAdapter,
    'sybase': SybaseAdapter,
    'db2': DB2Adapter,
    'teradata': TeradataAdapter,
    'informix': InformixAdapter,
    'informix-se': InformixSEAdapter,
    'firebird': FireBirdAdapter,
    'firebird_embedded': FireBirdAdapter,
    'ingres': IngresAdapter,
    'ingresu': IngresUnicodeAdapter,
    'sapdb': SAPDBAdapter,
    'cubrid': CubridAdapter,
    'jdbc:sqlite': JDBCSQLiteAdapter,
    'jdbc:sqlite:memory': JDBCSQLiteAdapter,
    'jdbc:postgres': JDBCPostgreSQLAdapter,
    'gae': GoogleDatastoreAdapter,   # zastaralé, pro zpětnou kompatibilitu
    'google:datastore': GoogleDatastoreAdapter,
    'google:datastore+ndb': GoogleDatastoreAdapter,
    'google:sql': GoogleSQLAdapter,
    'couchdb': CouchDBAdapter,
    'mongodb': MongoDBAdapter,
    'imap': IMAPAdapter
}

Uri řetězec (řetězec připojení) je pak podorbněji parsován (analyzován) samotným použitým adaptérem.

Pro kterýkoli adaptér můžete nahradit ovladač jiným ovladačem:

import MySQLdb as mysqldb
from gluon.dal import MySQLAdapter
MySQLAdapter.driver = mysqldb

takže mysqldb nyní bude modulem, který poskytuje metodu .connect(). Můžete zadat další parametry ovladače (driveru) a adaptéru:

db =DAL(..., driver_args={}, adapter_args={})

Specifické odchylky a problémy různých databázových strojů

SQLite

SQLite nepodporuje zrušení a změny sloupců. Znamená to, že Web2py migrace nebudou pracovat ideálně. Když zrušíte pole (sloupec) tabulky, sloupec zůstane v databázi, ale nebude nadále viditelný pro Web2py. Jestliže se později rozhodnete stejný sloupec znovu přidat, Web2py se o to pokusí a havaruje. V tomto případě musíte nastavit fake_migrate=True, aby se metadata aktualizovala bez pokusu sloupec skutečně fyzicky přidat. Ze stejného důvodu SQLite dělají potíže změny typu sloupců. Vložíte-li číslo do pole typu string (řetězec), uloží se jako řetězec. Když model později změníte a nahradíte typ "string" typem "integer", v SQLite zůstanou i nadále uloženy řetězce a to způsobí problém při získávání dat z databáze.

SQLite nezná typ boolean. Web2py interně mapuje boolean na jednoznakový řetězec 'T' nebo 'F' (True/False). To kompletně zajišťuje DAL a tato abstrakce dobře pracuje. Ale v případě, že aktualizujete SQLite tabulky SQL příkazy přímo, pamatujte na tuto Web2py implementaci a nepoužívejte hodnoty 0 nebo 1.

MySQL

MySQL nepodporuje více příkazů ALTER TABLE v jedné transakci. Znamená to, že každý (složitější) migrační proces musí být rozdělen do více commitů. Jestliže nastane nějaká chyba, může dojít k poškození mechanismu migrace (Web2py metadata přestanou odpovídat aktuální struktuře tabulky v databázi). Tomuto problému zabráníte nejlépe tak, že budete migrovat vždy jednu tabulku v jednotlivém kroku. Nebo je možné opravit problém dodatečně, pokud vznikne (vrátit Web2py model do stavu, který odpovídá skutečné struktuře tabulek v databázi, nastavit fake_migrate=True, spustit aplikaci (nebo appadmin) pro přebudování metadat, vrátit na fake_migrate=False a opakovat migraci).

Google SQL

Google SQL má stejný problém jako MySQL a další navíc. Konkrétně samotná metadata musí být uložena v databázi, do tabulky, kterou Web2py nemigruje. To proto, že Google App Engine má read-only souborový systém. Web2py migrace nad Google:SQL, kombinovaná s problémem, popsaným právě u MySQL, může vést k poškození metadat. Znova: nejlépe tomu zabráníte migrací postupně po jednotlivých tabulkách a nakonec nastavením migrate=False, čímž nebudou metadata už nadále používána. Nebo je možné opravit problém dodatečně (přístupem k databázi z Google dashboard a smazáním poškozených položek z tabulky se jménem web2py_filesystem.

MSSQL (Microsoft SQL Server)

limitby

MSSQL < 2012 nepodporuje SQL frázi OFFSET. Databáze tedy nemůže správně stránkovat. Když zadáte limitby=(a,b), Web2py v skutečnosti získá prních b záznamů a pak z nich prvních a záznamů zahodí. Může to způsobit značné zpomalení ve srovnání s provozem jiných databázových strojů. Používáte-li MSSQL >= 2005, doporučený prefix je mssql3://, který poskytuje patch metody pro načtení resultsetu tak, aby nebyla načtena všechna nestránkovaná data. Používáte-li MSSQL >= 2012, použijte mssql4://, který generuje konstrukci OFFSET ... ROWS ... FETCH NEXT ... ROWS ONLY pro efektivní nativní stránkování. mssql:// uri také z historických důvodů upřednostňuje používání polí typu text, které byly v pozdějších verzích (počínaje 2005) nahrazeny varchar(max). Opět je lépe použít mssql3:// nebo mssql4://, pokud nepotřebujete z nějakého důvodu používat - oficiálně zastaralý - typ sloupce text.

MSSQL má problém při cirkulárních odkazech mezi tabulkami, kde klíče mají nastaveno ONDELETE CASCADE. Je to bug MSSQL a pokud na něj narazíte, zabráníte mu nastavením ondelete atributu všech cizích klíčů na "NO ACTION". Můžete to také udělat najednou ještě předtím, než definujete tabulky:

1
2
3
4
db = DAL('mssql://....')
for key in ['reference', 'reference FK']:
    db._adapter.types[key] = db._adapter.types[key].replace(
        '%(on_delete_action)s','NO ACTION')

MSSQL má problém i s argumentem, předaným do fráze DISTINCT, a tak zatímco toto pracuje správně:

db(query).select(distinct=True)

toto nelze použít:

db(query).select(distinct=db.mytable.myfield)

Oracle

Ani Oracle nepodporuje stránkování. Nezná nejen frázi OFFSET, ale ani LIMIT. Web2py dosáhne stránkování přeložením db(...).select(limitby=(a,b)) na složitý třícestný nested select (výběr s podvýběrem), jak radí oficiální dokumentace Oracle. To uspokojivě pracuje pro jednodušší selecty, ale může havarovat pro složité selecty s aliasovanými poli nebo joiny.

Google NoSQL (Datastore)

Google NoSQL (Datastore) nepodporuje joiny, left joiny, agregace, výrazy, zahrnutí více než jedné tabulky, ‘like’ operátor nepracuje nad "text" poli.

Transakce jsou omezené a nejsou automaticky řízeny z Web2py (místo toho potřebujete explicitně volat Google API run_in_transaction, podrobnosti najdete v Google App Engine dokumentaci online).

Google také omezuje počet záznamů, které vrátí jednotlivý dotaz (v době psaní textu platil limit 1000 záznamů). ID záznamů jsou sice integer, ale nejsou přidělována ve vzestupné sekvenci. Zatímco na SQL je typ "list:string" mapován na "text" typ, na Google Datastore je mapován na ListStringProperty. Podobně "list:integer" a "list:reference" jsou mapovány na "ListProperty". Tím je prohledávání obsahu těchto polí na Google NoSQL efektivnější než na SQL databázích.

 top