La couche d'abstraction de base de données

Dépendances

web2py est fourni avec une couche d'abstraction à la base de données (DAL), une API qui mappe les objets Python avec des objets de base de données tels que des requêtes, des tables ou des enregistrements. La DAL génère dynamiquement le SQL en temps réel en utilisant le dialecte spécifié pour la base, afin que vous n'ayez pas à écrire de code SQL ou à apprendre différents dialectes SQL (le terme SQL est utilisé de façon générique), et l'application sera portable sur un grand nombre de types de bases de données. Une liste partielle des bases supportées est indiqué dans le tableau ci-après. Vérifiez sur le site web web2py et sur la mailing list pour des adaptations plus récentes. Google NoSQL est traité comme un cas particulier dans le chapitre 13.

La section Pièges à la fin de ce chapitre ont également des informations sur les bases de données spécifiques.

La distribution binaire Windows fonctionne directement avec QSLite et MySQL. La distribution binaire MAC fonctionne avec SQLite. Pour utiliser tout autre système de base de données, lancez la distribution depuis les sources et installez les pilotes appropriés pour le système souhaité.

Une fois le bon pilote installé, démarrez web2py depuis les sources, et il trouvera le pilote. Voici une liste des pilotes :

sqlite3, pymysql, pg8000, et imaplib disponibles avec web2py. Le support de MongoDB est expérimental. L'option IMAP permet d'utiliser la DAL pour accéder à IMAP.

web2py définit les classes suivantes qui construisent la DAL :

L'objet DAL représente une connexion à la base de données. Par exemple :

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

Table représente une table de la base de données. Vous n'instanciez pas directement Table ; DAL.define_table l'instancie à la place.

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

Les plus importantes méthodes d'une Table sont :

.insert, .truncate, .drop, et .import_from_csv_file.

Field représente un champ de base de données. Il peut être instancié et passé comme argument à DAL.define_table.

DAL Rows

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

Row contient des valeurs de champ.

for row in rows:
    print row.myfield

Query est un objet qui représente une clause "where" SQL :

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

Set est un objet qui représente un ensemble d'enregistrements. Ses méthodes les plus importantes sont count, select, update, et delete. Par exemple :

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

Expression est quelque chose comme une expression orderby ou groupby. La classe Field est dérivée de Expression. Voici un exemple.

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

Chaînes de connection

Une connexion avec la base de données est établie en créant une instance d'objet de la DAL :

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

db n'est pas un mot-clé ; c'est une variable locale qui stocke l'objet de connexion à la DAL. Vous êtes libre de lui donner un nom différent. Le constructeur de la DAL nécessite un seul argument, la chaîne de connexion. La chaîne de connexion est le seul code web2py qui dépend d'un système de base de données spécifique. Voici quelques exemples de chaînes de connexion pour des types spécifiques de systèmes de bases de données supportés (dans tous les cas, nous supposons que la base fonctionne en local sur son port par défaut et est nommée "test") :

Notez qu'en SQLite, la base de données consiste en un simple fichier. S'il n'existe pas, il est créé. Ce fichier est verrouilé à chaque fois qu'il est utilisé. Dans le cas de MySQL, PostgreSQL, MSSQL, FireBird, Oracle, DB2, Ingres et Informix la base "test" doit être créée en dehors de web2py. Une fois la connexion établie, web2py crééra, modifiera et supprimera les tables en fonction.

Il est aussi possible de définir la chaîne de connexion à None. Dans ce cas, la DAL ne se connectera à aucun système de base de données, mais l'API peut toujours être accessible pour des tests. Des exemples seront montrés au chapitre 7.

Parfois, vous pouvez avoir besoin de générer du SQL comme si vous aviez une connection mais sans vraiment vous connecter à la base. Ceci peut être fait avec

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

Dans ce cas vous serez capable d'appeler _select, _insert, _update, et _delete pour générer du SQL mais vous ne pourrez pas appeler select, insert, update, et delete. Dans la plupart des cas, vous pouvez utiliser do_connect=False même sans avoir les pilotes nécessaire à la connexion.

Notez que par défaut, web2py utilise l'encodage utf8 pour les bases de données. Si vous travaillez avec des bases existantes qui en nécessitent un autre, vous devez le changer avec le paramètre optionnel db_codec comme

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

autrement vous obtiendrez des tickets UnicodeDecodeError.

Pool de connexion

Le second argument du constructeur de DAL est le pool_size ; il est par défaut à zéro.

Comme il est plutôt lent d'établir une connexion à une nouvelle base de données pour chaque requête, web2py implémente un mécanisme pour le pool de connexions. Une fois une connexion établie, que la page a été servie et la transaction complétée, la connexion n'est pas fermée mais envoyée vers un pool. Lorsque la requête http suivante arrive, web2py essaie de recycler une connexion du pool et de l'utiliser pour la nouvelle transaction. S'il n'y en a pas de disponible dans le pool, une nouvelle connexion est alors établie.

Lorsque web2py démarre, le pool est toujours vide. Le pool grandit jusqu'au minimum entre les valeurs du pool_size et le nombre maximum de requêtes concurrentes. Ceci signifie que si pool_size=10 mais que notre server ne reçoit jamais plus de 5 requêtes concurrentes, alors la taille actuelle du pool grandira uniquement jusqu'à 5. Si pool_size=0 alors le pool de connexion n'est pas utilisé.

Les connexions dans les pools sont partagés séquentiellement entre les threads, dans le sens où elles peuvent être utilisées par deux threads différents mais pas en simultané. Il n'y a qu'un pool pour chaque process web2py.

Le paramètre pool_size est ignoré par SQLite et Google App Engine. Le pool de connexion est ignoré pour SQLite, puisqu'il n'en tirerait aucun profit.

Erreurs de connexion

Si web2py échoue lors de la connexion à la base, il attend une seconde et essaie à nouveau jusqu'à 5 fois avant de déclarer l'échec. Dans le cas d'un pooling de connexion il est possible qu'une connexion dans le pool reste ouverte mais inutilisée pendant un laps de temps et soit fermée par le système de base de données. Grâce au mécanisme de réouverture, web2py essaie de ré-établir ces connexions abandonnées.

Bases de données répliquées

Le premier argument de DAL(...) peut être une liste d'URIs. Dans ce cas, web2py essaie de se connecter à chacun d'entre eux. Le but principal pour cela est de traiter plusieurs serveurs de base de données et distribuer la charge de travail entre eux. Voici un exemple typique de cas d'usage :

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

Dans ce cas, la DAL essaie de se connecter tout d'abord au premier, et en cas d'échec, il essaiera le second puis le troisième. Ceci peut égalemnet être utilisé pour distribuer la charge dans une configuration de base maître/esclave. Nous présenterons ceci plus en détail dans le chapitre 13 dans le contexte de la scalabilité.

Mots-clés réservés

check_reserved est encore un autre argument qui peut être passé au constructeur de la DAL. Il lui indique de vérifier les noms de table et les noms de colonnes vis-à-vis des mots-clés SQL réservés par les bases de données ciblées. check_reserved est par défaut à None.

Ceci est une liste des chaînes qui contiennent les noms des adapteurs des systèmes de bases de données.

Le nom de l'adapteur est le même que celui utilisé dans la chaîne de connexion à la DAL. Donc si vous souhaitez vérifier pour PostgreSQL et MSSQL, alors votre chaîne de connexion devrait ressembler à :

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

La DAL va scanner les mots-clés dans le même ordre que la liste.

Il y a deux options supplémentaires "all" et "common". Si vous spécifiez "all", il effectuera la vérification pour tous les mots-clés connus de bases. Si vous spécifiez "common", il vérifiera uniquement les mots-clés communs tels que SELECT, INSERT, UPDATE, etc.

Pour les systèmes supportés vous pouvez aussi spécifier si vous souhaitez effectuer la vérification des mots-clés SQL non réservés. Dans ce cas vous ajouteriez _nonreserved au nom. Par exemple :

check_reserved=['postgres', 'postgres_nonreserved']

Les systèmes de bases de données suivant supportent la vérification des mots réservés.

DAL, Table, Field

Vous pouvez tester l'API de la DAL en utilisant le shell web2py.

Commencez par créer une connexion. Pour un exemple, vous pouvez utiliser SQLite. Rien de ce qui est décrit ici ne change lorsque vous changez de moteur de base de données.

Constructeur de DAL

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

La base de données est maintenant connectée est la connexion est stockée dans la variable globale db.

A n'importe quel moment vous pouvez retrouver la chaîne de connexion.

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

et le nom de la base de données :

>>> print db._dbname
sqlite

La chaîne de connexion est appelée une _uri puisque c'est une instance de Uniform Resource Identifier.

La DAL permet de multiples connexions avec la même base de données ou avec différentes bases, même si ce sont des bases de différents types. Pour le moment, nous supposons la présence d'une simple base puisque c'est la situation la plus courante.

Constructeur de table

La méthode la plus importante d'une DAL est define_table :

>>> db.define_table('person', Field('name'))

Il définit, stocke et retourne un objet Table appelé "person" contenant un champ (colonne) "name". Cet objet peut aussi être atteint via db.person, donc vous n'avez pas besoin de récupérer la valeur de retour.

Ne déclarez pas un champ appelé "id", car un est créé automatiquement par web2py quoi qu'il en soit. Toutes les tables ont un champ appelé "id" par défaut. C'est un champ entier auto-incrémental (démarrant à 1) utilisé pour les références croisées et pour rendre chaque enregistrement unique, donc "id" est une clé primaire. (Note : le fait que l'id commence à 1 est spécifique au SGBD. Par exemple, ceci ne s'applique pas à Google App Engine NoSQL.)

Optionnellemnet, vous pouvez définir un champ de type='id' et web2py utilisera ce champ comme champ id auto-incrémental. Ceci n'est pas recommandé à moins de vouloir accéder à des tables de bases legacy. Avec quelques limitations, vous pouvez aussi utiliser des clés primaires différentes et ceci est présenté dans la section "Bases de données legacy et tables clés".

Les tables peuvent être définies uniquement une fois mais vous pouvez forcer web2py à redéfinir une table existante :

db.define_table('person', Field('name'))
db.define_table('person', Field('name'), redefine=True)

La redéfinition peut déclencher une migration is le contenu du champ est différent.

Lazy Tables, un boost de performance majeur

Les modèles web2py sont exécutés avant les contrôleurs, afin que toutes les tables soient bien définies à chaque requête. Ce ne sont pas toutes les tables qui nécessitent de gérer chaque requête, il est donc possible qu'un peu de temps soit perdu en définissant les tables. Les modèles conditionnels (modèles conditionnels, chapitre 4) peuvent aider, mais web2py offre un gros boost de performance avec les lazy_tables. Cette fonctionnalité signifie que la création des tables est repoussée tant que la table est déjà référencée. L'activation des lazy tables nécessite de définir le paramètre DAL(...,lazy_tables=True). C'est l'un des boost de performance les plus significatifs de web2py en temps de réponse.

Ajouter des attributs aux champs et aux tables

Si vous avez besoin d'ajouter des atributs personnalisés aux champs, vous pouvez simplement faire ceci :

db.table.field.extra = {}

"extra" n'est pas un mot-clé ; c'est un attribut personnalisé maintenant ataché à l'objet field. Vous pouvez le faire avec les tables également mais elles nécessitent d'être précédées par un underscore pour éviter les conflits de nom avec les champs :

db.table._extra = {} 

Representation d'un enregistrement

C'est optionnel mais recommandé de spécifier un format de représentation pour les enregistrements :

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

ou

>>> db.define_table('person', Field('name'), format='%(name)s %(id)s')

ou même encore des plus complexex en utilisant une fonction :

>>> db.define_table('person', Field('name'),
       format=lambda r: r.name or 'anonymous')

L'attribut de format sera utilisé pour deux raisons :

• Représenter des enregistrement référencés dans des listes déroulantes select/option.
• Définir l'attribut db.othertable.person.represent pour tous les champs référençant cette tables. Ceci signifie que SQLTABLE ne montrera pas les références par id mais utilisera la représentation de format préférée à la place.

Constructeur de Field

Voici les valeurs par défaut d'un constructeur de Field :

Field(name, '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=os.path.join(request.folder,'uploads'),
      uploadseparate=None,uploadfs=None)

Tous ne sont pas nécessaire pour tous les champs. "length" est nécessaire uniquement pour les champs de type "string". "uploadfield" et "authorize" sont nécessaires uniquement pour les champs de type "upload". "ondelete" est nécessaire uniquement pour les champs de type "reference" et "upload".

• length définit la longueur maximale d'un champ "string", "password" ou "upload". Si length n'est pas spécifié, une valeur par défaut est utilisée mais la valeur par défaut n'est pas garantie d'être rétro-compatible. Pour éviter des migrations non souhaitées lors de mises à jour, nous recommandons de toujours spécifier la longueur d'un champ string, password et upload.
• default définit la valeur par défaut pour le champ. La valeur par défaut est utilisée lorsqu'un insert est effectué si une valeur n'est pas spécifiée explicitement. Il est également possible de pré-peupler les formulaires construits depuis une table en utilisant SQLFORM. Notez que plutôt que d'être une valeur fixée, le défaut peut être une fonction (incluant une fonction lambda) qui retourne une valeur du type approprié pour le champ. Dans ce cas, la fonction est appelée une fois pour chaque enregistrement inséré, même si de multiples enregistrements sont insérés dans une simple transaction.
• required indique à la DAL qu'aucun insert ne devrait être accepté sur cette table si une valeur pour ce champ n'est pas spécifiée explicitement.
• requires est un validateur ou une liste de validateurs. Ce n'est pas utilisé par la DAL, mais par SQLFORM. Les validateurs par défaut pour les types données sont présentés dans le tableau suivant :

Types de champs

Decimal nécessite et retourne des valeurs comme objets Decimal, comme défini dans le module Python decimal. SQLite ne gère pas le type decimal donc de façon interne nous le traitons comme un double. Les (n,m) sont les nombres de de chiffres au total et le nombre de chiffres après la virgule.

Le bid-id et, big-reference sont seulement supportés par certains des moteurs de bases de données et sont expérimentaux. Ils ne sont normalement pas utilisés comme des types de champs sauf pour les tables legacy, cependant, le constructeur DAL a un argument bingint_id qui lorsqu'il est défini à True rend les champs id et reference respectivement big-id et big-reference.

Les champs list:<type> sont speciaux car ils sont destinés à proditer de certaines fonctionnalités de dénormalisation sur NoSQL (dans le cas de Google App Engine NoSQL, les types de champ ListProperty et StringListProperty) et les reportent sur toutes les autres bases de données relationnelles supportées. Sur les bases de données relationnelles les listes sont stockées comme champ text. Les objets sont séparés par un | et chaque | dans un objet string est échappé comme ||. Ceci est présenté dans leur propre section.

Le type de champ json est assez clair. Il peut stocker n'importe quel objet json sérialisable. Il est destiné à fonctionner particulièrement pour MongoDB et adapté à tous les autres adapteurs de base de données pour la portabilité.

Notez que requires=... est forcé au niveau des formulaires, required=True est forcé au niveau de la DAL (insert), alors que notnull, unique et ondelete sont forcés au niveau de la base de données. Bien qu'ils puissent parfois sembler redondants, il est important de maintenir la distinction lorsque l'on développe avec la DAL.

• ondelete traduit en déclaration SQL "ON DELETE". Par défaut, défini à "CASCADE". Ceci indique à la base de données que lorqu'un enregistrement est supprimé, il faut également supprimer toutes les références à cet enregistrement. Pour désactiver cette fonctionnalité, il faut définir ondelete à "NO ACTION" ou "SET NULL".
• notnull=True traduit en déclaration SQL "NOT NULL". Il évite à la base de données d'insérer des valeurs null pour le champ.
• unique=True traduit en déclaration SQL "UNIQUE" et s'assure que les valeurs de ce champ soient uniques dans la table. Ceci est renforcé au niveau de la base de données.
• uploadfield s'applique uniquement aux champs de type "upload". Un champ de type "upload" stocke le nom d'un fichier sauvegardé quelque part ailleurs, par défaut sur le filesystem dans le dossier "uploads/" de l'application. Si uploadfield est défini à True, alors le fichier est stocké dans un champ blob dans la même table et la valeur de uploadfield est le nom du champ blob. Ceci est présenté plus en détails dans le contexte de SQLFORM.
• uploadfolder est par défaut le dossier "uploads/" de l'application. S'il est défini vers un autre chemin, les fichiers seront stockés dans un dossier différent. Par exemple,

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

va envoyer les fichiers vers le dossier "web2py/applications/myapp/static/temp".

• uploadseparate si défini à True va uploader les fichiers sous différents sous-dossiers du dossier uploadfolder. Ceci est optimisé pour éviter d'avoir trop de fichiers sous le même répertoire folder/subfolder. ATTENTION : Vous ne pouvez pas changer la valeur de uploadseparate de True à False sans casser les liens d'upload existants. web2py utilise les sous-dossiers ou non. Changer le comportement après que les fichiers aient été envoyés empêchera web2py de retrouver ces fichiers. Si cela arrive, il est possible de déplacer les fichiers et de corriger le problème mais la procédure n'est pas décrite ici.
• uploadfs vous permet de spécifier un système de fichiers différent où envoyer les fichiers, incluant le stockage Amazon S3 ou un serveur distant SFTP. Cette option nécessite que PyFileSystem soit installé. uploadfs doit pointer sur PyFileSystem.
  PyFileSystem
  uploadfs
• widget doit être l'un des objets widget disponibles, incluant les widgets personnalisés, par exemple : SQLFORM.widgets.string.widget. Une liste deswidgets disponibles sera présentée plus tard. Chaque champ a un widget par défaut.
• label est une chaîne (ou un helper ou quelque chose qui puisse être sérialisé en chaîne) qui contient le label à utiliser pour ce champ dans des formulaires auto-générés.
• comment est une chaîne (ou un helper ou quelque chose qui puisse être sérialisé en chaîne) qui contient un commentaire associé à ce champ, et qui sera affichée à droite du champ input des formulaires auto-générés.
• writable déclare si un champ est modifiable dans les formulaires.
• readable déclare si un champ est visible dans les formulaires. Si un champ n'est ni lisible, ni modifiable, il ne sera pas affiché dans les formulaires de création et de mise à jour.
• update contient la valeur par défaut pour ce champ lorsque l'enregistrement est mis à jour.
• compute est une fonction optionnelle. Si un enregistrement est inséré ou mis à jour, la fonction compute sera exécutée et le champ sera peuplé avec le résultat de la fonction. L'enregistrement est envoyé à la fonctoin compute comme un dict, et le dict n'incluera pas sa valeur courante, ou tout autre champ compute.
• authorize peut être utilisé pour nécessiter un contrôle d'accès sur le champ correspondant, pour les champs "upload" seulement. Cette fonction sera présentée plus en détail dans le contexte de Authentication et Authorization.
• autodelete détermine si le fichier uploadé correspondant devrait être supprimé lorsque la référence fichier de l'enregistrement est supprimée. Pour les champs "upload" uniquement.
• represent peut être None ou peut pointer sur une fonction qui prend un champ valeur et retourne une représentation alternée pour le champ. Exemples :

db.mytable.name.represent = lambda name,row: name.capitalize()
db.mytable.other_id.represent = lambda id,row: row.myfield
db.mytable.some_uploadfield.represent = lambda value,row:     A('get it', _href=URL('download', args=value))

Les champs "blob" sont spéciaux également. Par défaut, les données binaires sont encodées en base64 avant d'être stockées dans le champ de la base actuelle, et décodé lorsqu'extraites. Ceci a pour effet négatif d'utiliser 25% de stockage supplémentaire que nécessaire dans les champs blob, mais a deux avantages. En moyenne, cela réduit le montant de données communiquantes entre web2py et le serveur de bases de données, et il rend la communication indépendante des conventions d'échappement spécifiques aux systèmes.

Champ d'exécution et modification de table

La plupart des champs d'attributs et de tables peuvent être modifiés après qu'ils aient été définis :

db.define_table('person',Field('name',default=''),format='%(name)s')
db.person._format = '%(name)s/%(id)s'
db.person.name.default = 'anonymous'

(notez que les attributs de tables sont habituellement préfixés par un underscore pour éviter les conflits avec des noms de champ).

Vous pouvez lister les tables qui ont été définies pour une connexion à la base de données donnée :

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

Vous pouvez aussi lister les champs qui ont été définis pour une table donnée :

>>> print db.person.fields
['id', 'name']

Vous pouvez demander le type d'une table :

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

et vous pouvez accéder à une table depuis la connexion à la DAL en utilisant :

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

De même, vous pouvez accéder aux champs depuis leur nom de plusieurs façons :

>>> print type(db.person.name)
<class 'pydal.objects.Field'>
>>> print type(db.person['name'])
<class 'pydal.objects.Field'>
>>> print type(db['person']['name'])
<class 'pydal.objects.Field'>

Etant donné un champ, vous pouvez accéder aux attributs définis dans leur définition :

>>> print db.person.name.type
string
>>> print db.person.name.unique
False
>>> print db.person.name.notnull
False
>>> print db.person.name.length
32

en incluant la table parent, le tablename, et la connexion parent :

>>> db.person.name._table == db.person
True
>>> db.person.name._tablename == 'person'
True
>>> db.person.name._db == db
True

Un champ a également des méthodes. Certaines d'entre elles sont utilisées pour construire des requêtes et seront présentées plus tard. Une méthode spéciale de l'objet de champ est validate et appelle les validateurs pour le champ.

print db.person.name.validate('John')

qui retourne un tuple (value, error). error est None si l'entrée passe la validation.

Migrations

define_table vérifie si la table correspondante existe ou non. Si elle n'existe pas, le code SQL pour la créer est généré et exécuter. Si la table existe mais diffère de celle initialement définie, le code SQL pour la modifier est générer et exécuter. Si un champ a changé de type mais pas de nom, il essaiera de convertir les données (si vous ne souhaitez pas cela, vous devez redéfinir la table deux fois, la première fois en laissant web2py dropper le champ en le supprimant, et la seconde fois en ajoutant le nouveau champ défini afin que web2py puisse le créer.). Si la table existe et correspond à la définition courante, il la laissera telle quelle. Dans tous les cas, il crééra l'objet db.person qui représente la table.

Nous appelons ce comportement une "migration". web2py enregistre toutes les migrations et les tentatives de migration dans le fichier "databases/sql.log".

Le premier argument de define_table est toujours le nom de table. Les autres arguments non nommés sont les champs (Field). La fonction prend également un argument mot-clé optionnel appelé "migrate" :

>>> db.define_table('person', Field('name'), migrate='person.table')

La valeur de migrate est le nom de fichier (dans le dossier "databases" pour l'application) où web2py stocke les informations internes de migration pour cette table. Ces fichiers sont très importants et ne devraient jamais être supprimés tant que la table correspondante existe. Dans le cas où une table a été effacée et le fichier correspondant existe encore, il peut être supprimé manuellement. Par défaut, migrate est défini à True. Ceci mène web2py à générer le nom de fichier depuis un hash de la chaîne de connexion. Si migrate est défini à False, la migration n'est pas effectuée, et web2py suppose que la table existe dans le datastore et contient (au moins) les champs listés dans define_table. La bonne pratique est de donner un nom explicit à la table migrée.

Il ne peut pas y avoir deux tables dans la même application avec le même nom de fichier de migration.

La classe DAL prend également un argument "migrate", qui détermine la valeur par défaut de migrate pour les appels à define_table. Par exemple,

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

va définir la valeur par défaut de migrate à False à chaque fois que db.define_table est appelé sans un argument migrate.

Notez que web2py migre uniquement les nouvelles colonnes, les colonnes supprimées, et les changements de type de colonne (sauf en sqlite). web2py ne migre pas les changements dans les attributs tel que les changements de valeurs de default, unique, notnull et ondelete.

Les migrations peuvent être désactivées pour toutes les tables en une fois :

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

C'est le comportement recommandé lorsque deux application partagent la même base de données. Seulement l'une des deux applications devrait effectuer les migrations, l'autre devrait les désactiver.

Corriger les migrations cassées

Il y a deux problèmes communs avec les migrations et il y a des moyens de s'en sortir.

Un problème est spécifique à SQLite. SQLite ne force pas les types de colonne et ne peut pas dropper les colonnes. Cela signifie que si vous avez une colonne de type chaîne et que vous la supprimez, elle n'est pas réellement supprimée. Si vous ajoutez la colonne à nouveau avec un type différent (par exemple datetime) vous finirez avec une colonnes datetime qui contient les chaînes (junk pour des raisons pratiques). web2py ne s'en plaint pas puisqu'il n'a pas connaissance de ce qu'il y a dans la base, jusqu'à ce qu'il essaie de récupérer un enregistrement et échoue.

Si web2py retourne une erreur dans la fonction gluon.sql.parse en sélectionnant des enregistrement, voici le problème : les données corrompues dans une colonne telles que présenté dans le cas ci-dessus.

La solution consiste à mettre à jour tous les enregistrements de la table et mettre à jours les valeurs dans la colonne en question avec None.

L'autre problème est plus générique mais typique avec MySQL. MySQL n'autorise pas plus d'un ALTER TABLE dans une transaction. Cela signifie que web2py doit séparer les transactions complexes en de plus petites transactions (un ALTER TABLE à la fois) et effectuer les commit un à un. Il est aussi possible qu'une partie de la transaction complexe soit commitée et qu'une autre partie échoue, laissant web2py dans un état corrompu. Pourquoi une partie de transaction échouerait ? Car, par exemple, cela entraîne la modification d'une table et la conversion d'une colonne chaîne en une colonne datetime, web2py essaie de convertir les données, mais les données ne peuvent pas être converties. Qu'arrive-t-il à web2py ? Il devient confus sur la réelle structure de la table stockée dans la base de données.

La solution consiste à désactiver les migrations pour toutes les tables et activer de fausses migrations :

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

Ceci va reconstruire les méta-données web2py de la table en fonction de la définition de la table. Essayer plusieurs définitions de table pour voir laquelle fonctionne (celle avant la migration et celle après la migration échouée). Une fois réussi, supprimez le paramètre fake_migrate=True.

Avant d'essayer de corriger les problèmes de migration, il est prudent de faire une copie des fichiers "applications/yourapp/databases/*.table".

Les problèmes de migration peuvent aussi être corrigés pour toutes les tables en une fois :

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

Ceci échoue également si le modèle décrit des tables qui n'existent pas dans la base de données, mais peut aider à se rapprocher du problème.

Migration control summary

La logique des arguments variables de migration sont résumés dans ce pseudo-code :

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

insert

Etant donnée une table, vous pouvez insérer des enregistrements

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

Insert retourne la valeur unique "id" de chaque enregistrement inséré.

Vous pouvez tronquer la table, i.e., supprimer tous les enregistrements et remettre à zéro le compteur d'id.

>>> db.person.truncate()

Maintenant, si vous insérez un enregistrement à nouveau, le compteur redémarre à 1 (ceci est spécifique au back-end et ne s'applique pas à Google NoSQL) :

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

Notez que vous pouvez passer les paramètres à truncate, par exemple vous pouvez indiquer à SQLITE de redémarrer le compteur d'id.

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

L'argument est en SQL brut et donc spécifique au moteur.

web2py fournit également une méthode bulk_insert

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

Il prend une liste de dictionnaires de champs à insérer et effectue plusieurs insertions à la fois. Il retourne les IDs des enregistrements insérés. Sur les bases de données supportées il n'y a pas d'avantage à utiliser cette fonction plutôt que de boucler et effectuer les insertions individuelles sauf sur Google App Engine NoSQL, il y a un avantage majeur de performance.

commit and rollback

Aucune opération create, drop, insert, truncate, delete ou update n'est validée avant que vous ayez utilisé la commande commit

>>> db.commit()

Pour vérifier, insérons un nouvel enregistrement :

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

et revenons en arrière, i.e., ignorant toutes les opérations depuis le dernier commit :

>>> db.rollback()

Si vous insérez maintenant à nouveau l'enregistrement, le compteur sera à nouveau défini à 2, puisque l'enregistrement précédent a été annulé.

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

Le code des modèles, vues et contrôleurs est formé en code web2py qui ressemble à cela :

try:
     execute models, controller function and view
except:
     rollback all connections
     log the traceback
     send a ticket to the visitor
else:
     commit all connections
     save cookies, sessions and return the page

Il n'y a pas besoin d'appeler commit ou rollback explicitement dans web2py à moins que l'on nécessite d'un contrôle très précis.

SQL brut

Timing queries

Toutes les requêtes sont automatiquement chronométrées par web2py. La variable db._timings est une liste de tuples. Chaque tuple contient la requête SQL brute telle que passée au driver de base de données et le temps d'exécution en secondes. Cette variable peut être affichée dans les vues en utilisant la barre d'outils :

{{=response.toolbar()}}

executesql

La DAL vous permet de créer explicitement des déclarations SQL.

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

Dans ce cas, les valeurs retournées ne sont pas parsées ou transformées par la DAL, et le format dépend du driver spécifique à la base de données. Cet usage avec les selects n'est normalement pas nécessaire, mais c'est plus commun avec des index. executesql prend 4 arguments optionnels : placeholders, as_dict, fields et colnames. placeholders est une séquence optionnelle de valeurs qui doivent être substituées ou, si supporté par le driver de la base, un dictionnaire avec les clés correspondant aux placeholders nommés dans votre SQL.

Si as_dict est défini à True, le curseur des résultats retournée par le driver de la base sera converti en une séquence de dictionnaires classés avec les noms des champs de la base. Les résultats retournés avec as_dict = True sont les mêmes que ceux retournés lorsque l'on applique .as_list() à un select normal.

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

L'argument fields est une liste d'objets Field de la DAL qui correspondent aux champs retournés par la base. Les objets Field devraient faire partie de l'un ou plus des objets Table définis dans l'objet DAL. La liste fields peut inclure un ou plusieurs objets Table de la DAL en plus ou en remplacement d'objets Field, ou il peut simplement être une simple table (et non dans une liste). Dans ce cas, les objets Field seront extraits de la(les) table(s).

Au lieu de spécifier l'arguments fields, l'argument colnames peut être spécifié comme une liste de noms de champ dans le format tablename.fieldname. Encore une fois, ils devraient représenter les tables et champs définis sur l'objet DAL.

Il est également possible de spécifier fields et le colnames associé. Dans ce cas, fields peut aussi inclure des objets Expression de la DAL en plus des objets Field. Pour les objets Field dans "fields", le colnames associé doit encore être dans le format tablename.fieldname. Pour les objets Expression dans fields, le colnames associé peut être n'importe quel label arbitraire.

Notez que les objets Table de la DAL référés aux fields ou colnames peuvent être des fausses tables et ne pas représenter de tables réelles dans la base de données. Aussi, notez que fields et colnames doivent être dans le même ordre que les champs dans le curseur de résultat retourné par la base de données.

_lastsql

Que le code SQL soit exécuté manuellement en utilisant executesql ou du SQL généré par la DAL, vous pouvez toujours trouver le code SQL dans db._lastsql. Ceci est utile en cas de déboguage :

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

web2py ne génère jamais de requête en utilisant l'opérateur "*". web2py est toujours explicite quant aux champs selectionnés.

drop

Finalement, vous pouvez exécuter un drop sur des tables et toutes les données seront perdues :

>>> db.person.drop()

Note pour sqlite : web2py ne re-crééra pas la table droppée sauf si vous naviguez dans le système de fichiers jusqu'au répertoire databases de votre application, et supprimez le fichier associé à la table supprimée.

Indexes

Pour le moment, l'API de la DAL ne fournit pas de commande pour créer des index sur les tables, mais ceci peut être fait en utilisant la commande executesql. Ceci est dû au fait que l'existance d'index peut rendre les migrations complexes, et il est meilleur de les gérer explicitement. Les index peuvent être nécessaires pour ces champs qui sont utilisés dans des requêtes récurrentes.

Voici un exemple de comment créer un index en utilisant SQL dans SQLite :

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

Les autres dialectes de base de données ont des syntaxes très similaires mais peuvent ne pas supporter la directive optionnelle "IF NOT EXISTS".

Bases de données legacy et tables indexées

web2py peut se connecter aux bases de données legacy sous certaines conditions.

Le meilleur moyen est lorsque ces conditions sont respectées :

• Chaque table doit avoir un champ entier unique auto-incrémental appelé "id"
• Les enregistrements doivent être référencés exclusivement en utilisant le champ "id".

Lors de l'accès à une table existante, i.e., une table non créée par web2py dans l'application courante, toujours définir migrate=False.

Si la table legacy a un champ entier auto-incrémental mais n'est pas appelé "id", web2py peut encore y accéder mais la définition de la table doit contenir explicitement un Field('....','id') où ... est le nom du champ entier auto-incrémenté.

Finalement, si la table legacy utilise une clé primaire qui n'est pas un champ auto-incrémental il est possible d'utiliser une "table indexée", par exemple :

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

• primarykey est une liste de noms de champ qui créent la clé primaire.
• Tous les champs primarykey ont un ensemble NOT NULL même si non spécifié.
• Les tables indexées peuvent seulement référencer d'autres tables indexées.
• Le référencement des champs doit utiliser le format reference tablename.fieldname.
• La fonction update_record n'est pas disponible pour les Rows ou les tables indexées.

Pour le moment, les tables indexées ne sont supportées que pour DB2, MS-SQL et Informix, mais les autres moteurs seront ajoutés.

Au moment où ceci est écrit, nous ne pouvons pas garantir que l'attribut primarykey fonctionne avec toutes les tables legacy et tous les systèmes de base de données supportées. Par simplicité, nous recommandons, si possible, de créer une vue de base de données qui a un champ id auto-incrémental.

Transaction distribuée

Au moment où ceci est écrit, cette fonctionnalité est uniquement supportée par PostgreSQL, MySQL et Firebird, puisqu'ils exposent l'API pour le commit bi-phasé.

Supposons que vous avez deux (ou plus) connexions à des bases PostgreSQL distinctes, par exemple :

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

Dans vos modèles et contrôleurs, vous pouvez effectuer les commits de manière concurrentielle avec :

DAL.distributed_transaction_commit(db_a, db_b)

En cas d'échec, cette fonction reviens en arrière et lève une Exception.

Dans les contrôleurs, lorsqu'une action retourne, si vous avez deux connexions distinctes et que vous n'appelez pas la fonction ci-dessus, web2py effectue les commits séparément. Ceci signifie qu'il y a une possibilité que l'un des commits réussisse et que l'autre échoue. La transaction distribuée empêche que cela arrive.

Plus sur les uploads

Considérons le modèle suivant :

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

Dans le cas d'un champ 'upload', la valeur par défaut peut optionnellement être définie à un chemin (un chemin absolu ou un chemin relatif au dossier courant de l'application) et l'image par défaut sera définie à une copie du fichier à ce chemin. Une nouvelle copie est faite pour chaque nouvel enregistrement qui ne spécifie pas une image.

Normalement une insertion est gérée automatiquement via un SQLFORM ou un formulaire crud (qui est un SQLFORM) mais occasionnellement vous avez déjà le fichier sur le filesystem et vous voulez l'uploader de manière scriptée. Ceci peut être fait avec :

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

Il est également possible d'insérer un fichier d'un moyen plus simple et d'avoir la méthode d'appel d'insertion qui le stocke automatiquement :

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

Dans ce cas, le nom de fichier est obtenu par l'objet stream si disponible. Il utilise le nom de fichier pour déterminer l'extension (type) du fichier, créé un nouveau nom temporaire pour le fichier (selon le mécanisme d'upload de web2py) et charge le contenu du fichier dans ce nouveau fichier temporaire (dans le répertoire uploads à moins que spécifié autrement). Il retourne le nouveau nom temporaire, qui est alors stocké dans le champ image de la table db.myfile.

Notez, si le fichier doit être stocké dans un champ blob associé plutôt que sur le système de fichier, la méthode store() n'insérera pas le fichier dans le champ blob (car store() est appelé avant l'insertion), donc le fichier doit être explicitement inséré dans le champ blob :

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

L'opposé de .store est .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

Considérons encore la table définie (et supprimée) précédemment et insérons trois enregistrements :

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

Vous pouvez stocker la table dans une variable. Par exemple, avec la variable person, vous pourriez faire :

>>> person = db.person

Vous pouvez également stocker un champ dans une variable telle que name. Par exemple, vous pourriez aussi faire :

>>> name = person.name

Vous pouvez aussi construire une requête (en utilisant les opérateurs tels que ==, !=, <, >, <=, >=, like, belongs) et stocker la requête dans une variable q tel que dans :

>>> q = name=='Alex'

Lorsque vous appelez db avec une requête, vous définissez un ensemble d'enregistrements. Vous pouvez le stocker dans une variable s et écrire :

>>> s = db(q)

Notez qu'aucune requête à la base de données n'a été effectuée jusqu'ici. DAL + Query définissement seulement un ensemble d'enregistrements dans cette base de données qui correspondent à la requête. web2py détermine depuis la requête quelle table (ou quelles tables) sont impliquées, et, en fait, il n'y a aucun besoin de le spécifier.

select

Etant donné un Set, s, vous pouvez rassembler les enregistrements avec la commande select :

>>> rows = s.select()

Cela retourne un objet itérable de la classe pydal.objects.Rows dont les éléments sont des objets Row. Les objets pydal.objects.Row agissent comme des dictionnaires, mais leurs éléments peuvent aussi être accédés comme attributs, comme gluon.storage.Storage. La forme diffère de ce qui est vu avant car ses valeurs sont en lecture seule.

L'objet Rows permet de boucler sur les résultats du select et d'afficher les valeurs des champs sélectionnés pour chaque ligne :

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

Vous pouvez faire toutes les étapes en une déclaration :

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

La commande select peut prendre des arguments. Tous les arguments non nommés sont interprétés comme les noms des champs que vous voulez récupérer. Par exemple, vous pouvez être explicite sur la récupération du champ "id" et du champ "name" :

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

L'attribut de table ALL vous permet de spécifier tous les champs :

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

Notez qu'il n'y a pas de chaîne de requête passée à la base de données. web2py comprend que vous voulez tous les champs de la table person sans information additionnelle donc vous souhaitez tous les enregistrements de la table person.

Une syntaxe alternative équivalente est la suivante :

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

et web2py comprend que si vous demandez tous les enregistrements de la table person (id > 0) sans information additionnelle, alors vous souhaitez tous les champs de la table person.

Etant donnée une ligne

row = rows[0]

vous pouvez extraire ses valeurs en utilisant des expressions multiples équivalentes :

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

La dernière syntaxe est particulièrement pratique lors de la sélection d'une expression au lieu d'une colonne. Nous verrons ça plus tard.

Vous pouvez aussi faire

rows.compact = False

pour désactiver la notation

row[i].name

et activer, à la place, la notation moins compacte :

row[i].person.name

Oui, c'est inhabituel et très rarement nécessaire.

Afficher les lignes en utilisant la représentation

Vous pouvez souhaiter ré-écrire les lignes retournées par select pour profiter de l'information de formatage contenue dans le paramètre de représentation des champs.

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

Si vous ne spécifiez pas d'index, vous obtenez un générateur pour itérer sur toutes les lignes :

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

Peut aussi être appliqué par tranches :

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

Si vous voulez juste transformer les champs sélectionnés via leur attribut "represent", vous pouvez les lister dans l'argument "fields" :

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

Notez que cela retourne une copie transformée de la Row originale, donc il n'y a pas d'update_record (que vous ne voudriez pas de toute façon) ou de delete_record.

Raccourcis

La DAL supporte divers raccourcis pour simplifier le code. En particulier :

myrecord = db.mytable[id]

retourne l'enregistrement avec l'id donné s'il existe. Si l'id n'existe pas, il retourne None. La déclaration ci-dessus est équivalente à

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

Vous pouvez supprimer les enregistrements par id :

del db.mytable[id]

et c'est équivalent à

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

et supprime l'enregistrement avec l'id donné, s'il existe.

Note : Cette syntaxe de raccourci delete ne fonctionne pas pour le moment si versioning est activé

Vous pouvez insérer des enregistrements :

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

C'est équivalent à

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

et cela créé un nouvel enregistrement avec les valeurs de champs spécifiées par le dictionnaire sur la partie droite.

Vous pouvez mettre à jours des enregistrements :

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

ce qui est équivalent à

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

et cela met à jour un enregistrement existant avec les valeurs de champ spécifiées par le dictionnaire sur la partie droite.

Récupérer une Row

Une autre syntaxe pratique est la suivante :

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

Apparemment similaire à db.mytable[id] la syntaxe ci-dessus est plus flexible et plus sûre. Tout d'abord elle vérifie que l'id soit un int (ou str(id) soit un entier) et retourne None sinon (ne lève jamais d'exception). Elle permet également de spécifier de multiples conditions que l'enregistrement doit accepter. Si elles ne le sont pas, il retourne également None.

selects récursifs

Considérons la table précédente person et une nouvelle table "thing" référençant une "person" :

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

et un simple select de cette table :

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

qui est équivalent à

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

où ._id est une référence à la clé primaire de la table. Normalement db.thing._id est la même chose que db.thing.id et nous le supposerons dans la plupart de ce livre.

Pour chaque Row de thing il est possible de récupérer non pas juste les champs de la table sélectionnée (thing) mais aussi les tables liées (récursivement) :

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

Ici thing.owner_id.name nécessite un select à la base de données pour chaque objet dans things et c'est assez inefficace. Nous suggérons l'utilisation de jointures dès que possible au lieu de selects récursifs, néanmoins c'est pratique et utile lorsque l'on accède à des enregistrements individuels.

Vous pouvez aussi le faire différemment, en sélectionnant les objets référencés par une personne :

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

Dans cette dernière expression person.thing est un raccourci pour

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

i.e. le Set de thing est référencé par la person courante. Cette syntaxe se casse si la table référente a plusieurs références à la table référencée. Dans ce cas, il est nécessaire d'être plus explicite et d'utiliser une Query complète.

Sérialiser les Rows dans les vues

Etant donnée l'action suivante contenant une requête

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

Le résultat d'un select peut être affiché dans une vue avec la syntaxe suivante :

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

Ce qui est équivalent à :

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

SQLTABLE convertit les lignes en table HTML avec un en-tête contenant les noms de colonne et une ligne par enregistrement. Les lignes sont marquées comme classe alternant "even" et classe "odd". En arrière-plan, Rows est d'abord converti en objet SQLTABLE (à ne pas confondre avec Table) et ensuite sérialisé. Les valeurs extraites de la base sont aussi formatées par les validateurs associés au champ et ensuite échappés.

Il est maintenant possible et parfois pratique d'appeler SQLTABLE explicitement.

Le constructeur SQLTABLE prend les arguments optionnels suivants :

• la fonction lambda linkto ou une action à utiliser pour lier les champs de référence (par défaut à None).

Si vous lui assignez une chaîne avec le nom d'une action, il génèrera un lien vers cette fonction en lui passant, comme arguments, le nom de la table et l'id de chaque enregistrement (dans cet ordre). Par exemple :

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

Si vous voulez qu'un lien différent soit généré, vous pouvez spécifier un lambda, qui recevra comme paramètres, la valeur de l'id, le type de l'objet (e.g. table), et le nom de l'objet. Par exemple, si vous voulez recevoir les arguments en ordre inverse :

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

• upload l'URL de l'action de téléchargement pour autoriser le téléchargement de fichiers uploadés (par défaut à None)
• headers un dictionnaire mappant les noms de champ à leurs labels pour être utilisés comme en-têtes (par défaut à {}).Peut également être une instruction. Pour le moment, headers='fieldname:capitalize' est supporté.
• truncate le nombre de caractères pour tronquer les longues valeurs dans la table (défaut à 16)
• columns La liste des fieldnames à être montrés comme colonnes (dans le format tablename.fieldname). Ceux non listés ne sont pas affichés (par défaut à all).
• **attributes attribut helper générique devant être passé à l'objet TABLE le plus externe.

Voici un exemple :

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

SQLTABLE est utile mais il y a des fois où l'on a besoin de plus. SQLFORM.grid est une extension de SQLTABLE qui créé une table avec les fonctionnalités de recherche et de pagination, ainsi que la possibilité d'ouvrir des enregistrements détaillés, créer, éditer et supprimer des enregistrements. SQLFORM.smartgrid est une généralisation plus profonde qui autorise tout ce qui a été vu ci-dessus mais créé également des boutons pour accéders aux enregistrements de référencement.

Voici un exemple d'usage de SQLFORM.grid :

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

et la vue correspondante :

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

Pour travailler avec de multiples lignes, SQLFORM.grid et SQLFORM.smartgrid sont préférés à SQLTABLE car ils sont bien plus puissants. Voyez le chapitre 7.

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

La commande select prend de nombreux arguments optionnels.

orderby

Vous pouvez rassembler les enregistrements triés par nom :

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

Vous pouvez rassembler les enregistrement triés par nom en ordre inverse (notez le tilde) :

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

Vous pouvez obtenir les enregistrements en ordre aléatoire :

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

L'utilisation de orderby='<random>' n'est pas supportée sur Google NoSQL. Cependant, dans cette situation et comme dans beaucoup d'autres où les pré-packagés ne sont pas suffisants, les imports peuvent être utilisés :

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

Vous pouvez trier les enregistrements selon les multiples champs en les concaténant avec un "|" :

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

groupby, having

En utilisant groupby ensemble avec orderby, vous pouvez regrouper les enregistrements avec la même valeur pour le champ spécifié (ceci est spécifique au système de SGBD utilisé, et n'est pas disponible sur Google NoSQL) :

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

Vous pouvez utiliser having en conjonction avec groupby pour faire des regroupements conditionnels (seulement ceux avec la condition having sont groupés).

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

Notez que query1 filtre les enregistrements à afficher, query2 filtre les enregistrements à regrouper.

distinct

Avec l'argument distinct=True, vous pouvez spécifier si vous voulez seulement sélectionner des enregistrements distincts. Ceci a le même effet que grouping en utilisant tous les champs spécifiés sauf qu'il ne nécessite pas de tri. Lors de l'utilisation de distinct, il est important de ne pas sélectionner TOUS les champs, et en particulire de ne pas sélectionner le champ "id", sinon tous les enregistrements seront toujours distincts.

Voici un exemple :

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

Notez que distinct peut aussi être une expression, par exemple :

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

limitby

Avec limitby=(min, max), vous pouvez sélectionner un sous-ensemble d'enregistrements de offset=min jusqu'à offset=max exclus (dans ce cas, les deux premiers en commençant à zéro) :

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

orderby_on_limitby

Notez que la DAL ajoute par défaut implicitement une clause orderby lorsque limitby est utilisé. Ceci permet de s'assurer que la même requête retourne les mêmes résultats chaque fois, important pour la pagination. Mais ceci peut engendrer des problèmes de performance. Utilisez orderby_on_limitby = False pour changer cela (par défaut à True).

left

Présenté plus bas dans la section sur les jointures.

cache, cacheable

Un exemple d'usage qui donne plus de rapidité aux selects est :

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

Voir la discussion sur 'caching selects', ci-après, pour comprendre le fonctionnement.

Opérateurs logiques

Les requêtes peuvent être combinées en utilisant l'opérateur binaire AND "&" :

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

et l'opérateur binaire OR "|" :

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

Vous pouvez inverser une requête (ou sous-requête) avec l'opérateur binaire "!=" :

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

ou en exprimant la négation explicitement avec l'opérateur unitaire "~" :

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

Selon les restrictions Python sur la surcharge des opérateurs "and" et "or", ils ne peuvent pas être utilisés lors de la formation de requêtes. Les opérateurs binaires "&" et "|" doivent être utilisés à la place. Notez que ces opérateurs (contrairement à "and" et "or") on une plus haute priorité par rapport aux opérateurs de comparaison, donc les parenthèses "extra" dans l'exemple ci-dessus sont obligatoires. De la même manière, l'opérateur unitaire "~" a une plus haute priorité que les opérateurs de comparaison, donc les comparaisons inversées ~ doivent également être mises entre parenthèses.

Il est également possible de construire des requêtes en utilisant les opérateurs logiques en place :

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

count, isempty, delete, update

Vous pouvez compter les enregistrements dans un ensemble :

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

Notez que count prend un argument optionnel distinct qui est par défaut à False, et cela fonctionne exactement pareil que pour le même argument pour select. count a également un argument cache qui fonctionne pareil que son équivalent pour la méthode select.

Parfois vous pouvez avoir besoin de vérifier si une table est vide. Un moyen plus efficace que compter est d'utiliser la méthode isempty :

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

ou son équivalent :

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

Vous pouvez supprimer les enregistrements dans un ensemble :

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

Et vous pouvez mettre à jour tous les enregistrements dans un ensemble en passant les arguments nommés correspondant aux champs qui ont besoin d'être mis à jour :

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

Expressions

La valeur assignée à une déclaration de mise à jour peut être une expression. Par exemple, considérons ce modèle :

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

Les valeurs utilisées dans les requêtes peuvent aussi être des expressions

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

Une expression peut contenir une clause case, par exemple :

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

update_record

web2py permet également la mise à jour d'un seul enregistrement qui est déjà en mémoire en utilisant update_record

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

update_record ne devrait pas être confondu avec

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

car pour une simple ligne, la méthode update met à jour l'objet de la ligne mais pas l'enregistrement dans la base de données, comme dans le cas de update_record.

Il est également possible de changer les attributs d'une ligne (une par une) et ensuite appeler update_record() sans arguments pour sauver les changements :

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

La méthode update_record est disponible seulement si le champ id de la table est inclus dans le select, et cacheable n'est pas défini à True.

Insertion et mise à jour deuis un dictionnaire

Une erreur commune consiste à avoir besoin d'insérer ou mettre à jour des enregistrements dans une table où le nom de la table, le champ à mettre à jour, et la valeur pour le champ sont stockés dans des variables. Par exemple : tablename, fieldname, et value.

L'insert peut être fait en utilisant la syntaxe suivante :

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

La mise à jour des enregistrements avec l'id donné peut être fait avec :

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

Notez que nous avons utilisé table._id au lieu de table.id. Dans ce cas la requête fonctionne même pour les tables avec un champ de type "id" qui a un nom autre que "id".

first et last

Etant donné un objet Rows contenant les enregistrements :

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

sont équivalents à

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

as_dict et as_list

Un objet Row peut être sérialisé en dictionnaire régulier en utilisant la méthode as_dict() et un objet Rows peut être sérialisé en une liste de dictionnaires en utilisant la méthode as_list(). Voici quelques exemples :

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

Ces méthodes sont pratiques pour passer des Rows à des vues génériques ou pour stocker des Rows dans les sessions (tant que les objets Rows eux-mêmes ne peuvent pas être sérialisés tant qu'ils contiennent une référence vers une connexion ouverte à la DB) :

>>> rows = db(query).select()
>>> session.rows = rows # not allowed!
>>> session.rows = rows.as_list() # allowed!

Combiner les lignes

Les objets Rows peuvent être combinés au niveau Python. Voici ce que l'on suppose :

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

Vous pouvez faire une intersection d'enregistrements dans deux ensembles de lignes :

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

Vous pouvez faire un union d'enregistrements en supprimant les dupliqués :

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

find, exclude, sort

Parfois vous avez besoin d'exécuter deux selects et l'un contient un sous-ensemble du select précédent. Dans ce cas, il est inutile de ré-accéder à la base de données à nouveau. Les objets find, exclude et sort vous permettent de manipuler des objets Rows et d'en générer un autre sans accéder à la base. Plus précisément :

• find retourne un nouvel ensemble de Rows filtrés par une condition et laisse l'original inchangé.
• exclude retourne un nouvel ensemble de Rows filtrés par une condition et les supprime du Rows original.
• sort retourne un nouvel ensemble de Rows trié par une condition et laisse l'original inchangé.

Toutes ces méthodes prennent un simple argument, une fonction qui agit sur chaque ligne individuellement.

Voici un exemple d'usage :

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

Ils peuvent être combinés :

>>> 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 prend un argument optionnel reverse=True avec une action évidente.

La méthode find a un argument limitby optionnel avec la même syntaxe et fonctionnalité que la méthode select d'un Set.

Autres méthodes

update_or_insert

Parfois vous avez besoin d'exécuter une insertion seulement s'il n'y a pas d'enregistrement avec les mêmes valeurs que celles qui ont été insérées. Ceci peut être fait avec :

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

L'enregistrement sera inséré seulement s'il n'y a pas d'autre utilisateur appelé John né à Chicago.

Vous pouvez spécifier quelles valeurs à utiliser comme clé pour déterminer si un enregistrement existe. Par exemple :

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

et s'il y a John, son lieu de naissance sera mis à jour sinon un nouvel enregistrement sera créé.

Le critère de selection dans l'exemple précédent est un simple champ. Il peut aussi être une requête, telle que

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

La fonction

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

fonctionne exactement pareille que

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

sauf qu'elle appelle les validateurs pour les champs avant d'effecture l'insertion et s'arrête si la validation échoue. Si la validation ne réussit pas, les erreurs peuvent être trouvées dans ret.error. Si elle réussit, l'id du nouvel enregistrement est dans ret.id. Pensez que normalement la validation est faite par la logique d'exécution du formulaire donc cette fonction est rarement nécessaire.

De la même façon

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

fonctionne quasiment pareil que

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

sauf qu'elle appelle les validateurs pour les champs avant d'effecture la mise à jour. Notez que cela fonctionne uniquement si la requête implique une seule table. Le nombre d'enregistrements mis à jour peut être trouvé dans res.updated et les erreurs seront ret.errors.

smart_query (expérimental)

Il y a des fois où l'on a besoin de parser une requête en utilisant le langage naturel tel que

name contain m and age greater than 18

La DAL fournit une méthode pour parser ce type de requêtes :

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

Le premier argument doit être une liste de tables ou de champs qui devraient être autorisés dans la recherche. Une exception RuntimeError est levée si la chaîne de recherche est invalide. Cette fonctionnalité peut être utilisée pour construire des interfaces RESTful (voir chapitre 10) et est utilisé en interne par SQLFORM.grid et SQLFORM.smartgrid.

Dans la chaîne de recherche smartquery, un champ peut être identifié par le fieldname seulement ou par un tablename.fieldname. Les chaînes peuvent être délimitées par des doubles quotes si elles contiennent des espaces.

Champs calculés

Les champs de la DAL peuvent avoir un attribut compute. Ceci doit être une fonction (ou lambda) qui prend un objet Row et retourne une valeur pour le champ. Lorsqu'un nouvel enregistrement est modifié, incluant les insertions et les mises à jours, si une valeur pour le champ n'est pas fournie, web2py essaie de la calculer depuis les valeurs des autres champs en utilisant la fonction compute. Voici un exemple :

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

Notez que la valeur calculée est stockée dans la base et n'est pas calculée lors de la récupération, comme dans le cas d'un champ virtuel, décrit juste après. Deux applications typiques de champs calculés sont :

• dans les applications de wiki, pour stocker l'entrée texte demandée du wiki en HTML, pour éviter de re-procéder à son traitement à chaque requête
• pour rechercher, pour calculer les valeurs normalisées pour un champ, à utiliser pour les recherches.

Les champs calculés sont évalués dans l'ordre dans lequel ils sont définis dans la définition de la table. Un champ calculé peut se référer aux champs définis précédemment (nouveau après la version 2.5.1)

Champs virtuels

Les champs virtuels sont aussi des champs calculés (comme dans la sous-section précédente) mais diffèrent d'eux car ils sont virtual dans le sens où ils ne sont pas stockés dans la base de données et ils sont calculés à chaque fois que les enregistrements sont extraits de la base. Ils peuvent être utilisés pour simplifier le code utilisateur sans utiliser de stockage additionnel mais ils ne peuvent pas être utilisés pour la recherche.

Nouveau style de champs virtuels

web2py fournit un moyen nouveau et plus simple de définir des champs virtuels et des champs virtuels simplistes. Cette section est marquée comme expérimentale car leurs API peuvent encore changer un peu de ce qui est décrit ici.

Nous considèrerons ici le même exemple que dans la sous-section précédente. En particulier nous considérons le modèle suivant :

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

On peut définir un champ virtuel total_price comme

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

i.e. en définissant simplement un nouveau champ total_price pour être un Field.Virtual. Le seul argument du constructeur est une fonction qui prend une ligne et retourne les valeurs calculées.

Un champ virtuel défini comme celle au-dessus est automatiquement calculé pour tous les enregistrements lorsque les enregistrement sont sélectionnés :

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

Il est également possible de définir des champs de méthode qui sont calculés à la demande, lorsqu'ils sont appelés. Par exemple :

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

Dans ce cas, row.discounted_total n'est pas une valeur mais une fonction. La fonction prend les mêmes arguments que la fonction passée au constructeur Method sauf pour row qui est implicite (considéré comme self pour les objets rows).

Le champ simpliste dans l'exemple ci-dessus autorise le calcul du prix total pour chaque item :

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

Et il permet également de passer un pourcentage optionnel discount (15%) : >>> for row in db(db.item).select(): print row.discounted_total(15)

Les champs Virtual et Method peuvent aussi être définis lorsqu'une table est définie :

>>> 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: ...)) :code ------ Considérez que les champs virtuels n'ont pas les mêmes attributs que les autres champs (default, readable, requires, etc...). Dans de plus anciennes versions de web2py ils n'apparaissent pas dans la liste des db.table.fields et ils nécessitent une approche spéciale pour afficher SQLFORM.grid et SQLFORM.smartgrid. Voir la discussion sur les grids et champs virtuels dans le chapitre sur les formulaires. ------ #### Ancien style de champs virtuels Afin de définir un champ virtuel ou plus, vous pouvez aussi définir une classe container, l'instancier et la lier à une table ou à un select. Par exemple, considérer la table suivante : >>> db.define_table('item', Field('unit_price','double'), Field('quantity','integer'), :code On peut définir un champ virtuel total_price comme >>> class MyVirtualFields(object): def total_price(self): return self.item.unit_price*self.item.quantity >>> db.item.virtualfields.append(MyVirtualFields()) :code Notez que chaque méthode de la classe qui prend un simple argument (self) est un nouveau champ virtuel. self réfère à chaque ligne du select. Les valeurs du champ sont référées par un chemin complet tel que dans self.item.unit_price. La table est liée à des champs virtuels en ajoutant une instance de la classe à l'attribut virtualfields de la table. Les champs virtuels peuvent aussi accéder récursivement comme dans >>> 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()) :code Notez accès récursif au champ self.order_item.item.unit_price où self est l'enregistrement de boucle. Ils peuvent aussi agir sur le résultat d'un JOIN >>> 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 :code Notez comment dans ce cas, la syntaxe est différente. Le champ virtuel accède à self.item.unit_price et self.order_item.quantity qui appartiennent au select join. Le champ virtuel est attaché aux lignes de la table en utilisant la méthode setvirtualfields de l'objet rows. Cette méthode prend un nombre arbitraire d'arguments nommés et peut être utilisé pour définir de multiples champs virtuels, définis dans des classes multiples, et les attacher à de multiples tables : >>> 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

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

>>> class MyVirtualFields(object): def lazy_total_price(self): return lambda self=self: self.item.unit_price * self.item.quantity :code ### Relation un à plusieursone to many:inxx Pour illustrer comment implémenter des relations un à plusieurs avec la DAL web2py, définissez une autre table "thing" qui se réfère à la table "person" que nous redéfinissons ici : >>> db.define_table('person', Field('name'), format='%(name)s') >>> db.define_table('thing', Field('name'), Field('owner_id', 'reference person'), format='%(name)s')

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

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

>>> db.thing.insert(name='Boat', owner_id=1) 1 >>> db.thing.insert(name='Chair', owner_id=1) 2 >>> db.thing.insert(name='Shoes', owner_id=2) 3

>>> for row in db(db.thing.owner_id==1).select(): print row.name Boat Chair :code Vu qu'un objet a une référence vers une personne, une personne peut avoir de nombreux objets, donc un enregistrement de la table personne acquiert maintenant un nouvel attribut thing, qui est un Set, qui définit les objets de cette personne. Ceci permet de boucler sur les personnes et de récupérer leurs objets facilement : referencing:inxx >>> for person in db().select(db.person.ALL): print person.name for thing in person.thing.select(): print ' ', thing.name Alex Boat Chair Bob Shoes Carl :code #### Jointures internes Un autre moyen d'obtenir un résultat similaire est d'utiliser une jointure, spécialement un INNER JOIN. web2py effectue des jointures automatiquement et de manière transparente lorsque les requêtes lient deux tables ou plus comme dans l'exemple suivant : Rows:inxx inner join:inxx join:inxx >>> rows = db(db.person.id==db.thing.owner_id).select() >>> for row in rows: print row.person.name, 'has', row.thing.name Alex has Boat Alex has Chair Bob has Shoes

row.name

row.person.name

row.thing.name

>>> rows = db(db.person).select(join=db.thing.on(db.person.id==db.thing.owner_id)) >>> for row in rows: print row.person.name, 'has', row.thing.name Alex has Boat Alex has Chair Bob has Shoes

>>> 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)]) La valeur d'un join peut être une liste de db.table.on(...) à joindre. #### Jointure externe gauche Notez que Carl n'est pas apparu dans la liste ci-dessus car il n'a pas d'objets. Si vous prévoyez de sélectionner sur la table personnes (qu'ils aient des objets ou non) et leurs objets (s'ils en ont), alors vous avez besoin d'effectuer un LEFT OUTER JOIN. Ceci est fait en utilisant l'argument "left" de la commande select. Voici un exemple : Rows:inxx left outer join:inxx outer join:inxx >>> 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 has Boat Alex has Chair Bob has Shoes Carl has None

left = db.thing.on(...) :code effectue la requête de jointure gauche. Ici l'argument de db.thing.on est la condition requise pour la jointure (la même qu'utilisée au-dessus pour la jointure interne). Dans le cas d'une jointure à gauche, il est nécessaire d'êtr eexplicite sur les champs que l'on souhaite sélectionner. De multiples jointures à gauche peuvent être combinées en passant une liste de tuples de db.mytable.on(...) à l'attribut left. #### gourping et counting Lorsque l'on fait des jointures, parfois vous souhaitez pouvoir grouper des lignes en fonction de certains critères et les compter. Par exemple, compter le nombre d'objets possédés par toutes les personnes. web2py permet ceci également. Tout d'abord, vous avez besoin d'un opérateur de comptage. Ensuite, vous voulez joindre la table person avec la table thing par possesseur. Troisièmement, vous voulez sélectionner toutes les lignes (personne + objet), les grouper par personne, et les compter en les groupant : grouping:inxx >>> 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 :code Notez que l'opếrateur count (qui est pré-construit) est utilisé comme un champ. Le seul problème ici est sur la façon de récupérer l'information. Chaque ligne contient clairement une personnes et le compteur, mais le compteur n'est pas un champ de person ni une table. Donc où vas-t-il ? Il va dans un objet de stockage représentant l'enregistrement avec une clé valant l'expression de la requête elle-même. La méthode count de l'objet Field a un argument optionnel distinct. Lorsqu'il est défini à True il spécifie que seules les valeurs disctinctes de l'objet en question doivent être comptées. ### Many to manymany-to-many:inxx Dans les exemples précédents, nous avons autorisé un objet à avoir un possesseur mais une personne pouvait avoir de multiples objets. Que se passe-t-il si un Boat est possédé par Alex et Curt ? Ceci nécessite une relation many-to-many, et ceci est réalisé via une table intermédiaire qui lie une personne à un objet via une relation de possession. Voici comment le faire : >>> db.define_table('person', Field('name')) >>> db.define_table('thing', Field('name')) >>> db.define_table('ownership', Field('person', 'reference person'), Field('thing', 'reference thing'))

>>> db.ownership.insert(person=1, thing=1) # Alex owns Boat >>> db.ownership.insert(person=1, thing=2) # Alex owns Chair >>> db.ownership.insert(person=2, thing=3) # Bob owns Shoes

>>> db.ownership.insert(person=3, thing=1) # Curt owns Boat too

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

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

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

>>> for row in persons_and_things(db.thing.name=='Boat').select(): print row.person.name Alex Curt :code Une alternative plus légère de relations many-to-many est le tagging. Le tagging est présenté dans le contexte du validateur IS_IN_DB. Le tagging fonctionne même sur les systèmes de bases de données qui ne supportent pas les JOINs comme le Google App Engine NoSQL. ### list:<type> et contains

>>> 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 :code #### like, regexp, startswith, contains, upper, lower

db.mytable.myfield.like('value',case_sensitive=True)

db.mytable.myfield.startswith('value') db.mytable.myfield.contains('value')

db.mytable.myfield.like('value%') db.mytable.myfield.like('%value%') :code Notez que contains a une signification spéciale pour les champs list:<type> et a été présenté dans la section précédente. La méthode contains peut aussi recevoir une liste de valeurs et un argument optionnel booléen all pour rechercher les enregistrement qui contiennent toutes les valeurs : db.mytable.myfield.contains(['value1','value2'], all=True)

ou toute valeur de la liste 

db.mytable.myfield.contains(['value1','value2'], all=false) Il y a également une méthode regexp qui fonctionne comme la méthode like mais qui autorise la syntaxe d'expression régulière pour la recherche d'expression. Elle est uniquement supportée par PostgreSQL et SQLite. Les méthodes upper et lower vous autorisent à convertir la valeur du champ en majuscule ou minuscule, et vous pouvez aussi les combiner avec l'opérateur like : upper:inxx lower:inxx >>> for row in db(db.log.event.upper().like('PORT%')).select(): print row.event port scan :code #### year, month, day, hour, minutes, seconds

L'opérateur SQL IN est réalisé via la méthode belongs qui retourne true lorsque la valeur du champ appartient à l'ensemble spécifié (liste de tuples) :

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() :code Dans ce cas, il est évident que le select suivant nécessite untiquement le champ référencé par le champ db?thing.owner_id afin que l'on n'ait pas besoin de la notation plus verbeuse _select. nested_select:inxx Un select de ce type peut aussi être utilisé comme valeur d'insert/update mais dans le cas, la syntaxe est différente : lazy = db(db.person.name=='Jonathan').nested_select(db.person.id) db(db.thing.id==1).update(owner_id = lazy) :code Dans ce cas lazy est une expression qui calcule l'id de la personne "Jonathan". Les deux lignes résultent en une simple requête SQL. #### sum, avg, min, max and len

db(db.thing).select(distinct = db.thing.name[:3]) :code #### Valeurs par défaut avec coalesce et coalesce_zero Il y a des fois où vous avez besoin de récupérer une valeur de la base de données mais nécessitez aussi une valeur par défaut si la valeur pour l'enregistrement est définie à NULL. En SQL il y a le mot-clé COALESCE pour cela. web2py a une méthode équivalente 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

D'autres fois vous avez besoin de calculer une expression mathématique mais certains champs ont une valeur définie à None lorsque ce devrait être zéro.

### Générer du SQL brut

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

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

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

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

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

TABLE tablename field1, field2, field3, ... :code Deux tables sont séparées \r\n\r\n. Le fichier termine avec la ligne END

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

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") :code ------- Notez que dans les définitions de table ci-dessus, la valeur par défaut pour les deux champs uuid est définie en une fonction lambda, qui retourne un UUID (converti en string). La fonction lambda est appelée une fois pour chaque enregistrement inséré, s'assurant que chaque enregistrement obtient un unique UUID, même si de multiples enregistrements sont insérés dans une seule transaction. ------- Créez une action contrôleur pour exporter la base de données : def export(): s = StringIO.StringIO() db.export_to_csv_file(s) response.headers['Content-Type'] = 'text/csv' return s.getvalue()

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)

:code Eventuellement, vous pourrez créer un index manuellement pour rendre la recherche par uuid plus rapide. XML-RPC:inxx Autrement, vous pouvez utiliser XML-RPC pour exporter/importer le fichier. Si les enregistrements référencent les fichiers uploadés, vous avez aussi besoin d'exporter/importer le contenu du dossier uploads. Notez que les fichiers à l'intérieur sont déjà labelisés par UUIDs donc vous n'avez pas à vous inquiéter des conflits de noms et de références. #### HTML et XML (une Table à la fois) Rows objects:inxx Les objets Rows ont également une méthode xml (comme les helpers) qui les sérialise en XML/HTML : HTML:inxx >>> 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> :code Rows custom tags:inxx Si vous avez besoin de sérialiser les Rows dans n'importe quel autre format XML avec des tags personnalisés, vous pouvez facilement le faire en utilisant le helper universel TAG et la notation * :XML:inxx >>> 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> :code #### Représentation des données export_to_csv_file:inxx La fonction export_to_csv_file accepte un argument mot-clé nommé represent. Lorsque True il utilisera les colonnes de la fonction represent lors de l'export des données au lieu des données brutes. colnames:inxx La fonction accepte également un argument mot-clé nommé colnames qui devrait contenir une liste de noms de colonnes que l'on souhaite exporter. Par défaut, il prend toutes les colonnes. Aussi bien export_to_csv_file et import_from_csv_file acceptent les arguments mots-clé qui indiquent au parser CSV le format des fichiers à sauver/charger : - delimiter: délimiteur pour séparer les valeurs (par défaut ',') - quotechar: caractère à utiliser pour quoter les valeurs chaîne (par défaut, les double quotes) - quoting: système de quote (par défaut csv.QUOTE_MINIMAL) Voici un exemple d'usage : >>> import csv >>> rows = db(query).select() >>> rows.export_to_csv_file(open('/tmp/test.txt', 'w'), delimiter='|', quotechar='"', quoting=csv.QUOTE_NONNUMERIC)

"hello"|35|"this is the text description"|"2013-03-03" :code Pour plus d'informations, consulter la documentation officielle Python quoteall:cite ### Mise en cache des selects La méthode select prend également un argument cache, qui est par défaut à None. Pour des raisons de mise en cache, il devrait être défini à un tuple où le premier élément est le modèle de cache (cache.ram, cache.disk, etc...), et le second élément est la durée d'expiration en secondes. Dans l'exemple suivant, vous voyez un contrôleur qui met en cache un select sur la table précédemment définie db.log. Le select actuel récupère les données depuis le système de base de données pas plus souvent que toutes les 60 secondes et stocke le résultat dans cache.ram. Si l'appel suivant à ce contrôleur survient moins de 60 secondes depuis le dernier IO de base de données, il va simplement récupérer les précédentes données depuis cache.ram. cache select:inxx def cache_db_select(): logs = db().select(db.log.ALL, cache=(cache.ram, 60)) return dict(logs=logs) :code cacheable:inxx La méthode select a un argument optionnel cacheable, normalement défini à False. Lorsque cacheable=True le Rows résultant est sérialisable mais les Rowss manquent de méthode update_record et delete_record. Si vous n'avez pas besoin de ces méthodes, vous pouvez optimiser les selects en définissant l'attribut cacheable : rows = db(query).select(cacheable=True) :code Lorsque l'argument cache est défini mais cacheable=False (défaut) seuls les résultats de la base de données sont mis en cache, pas l'objet actuel Rows. Lorsque l'argument cache est utilisé conjointement avec cacheable=True l'objet entier Rows est mis en cache et ceci résulte en une mise en cache bien plus rapide : rows = db(query).select(cache=(cache.ram,3600),cacheable=True) :code ### Auto-Reference et aliases self reference:inxxalias:inxx Il est possible de définir des tables avec des champs qui se réfèrent eux-mêmes, voici un exemple :reference table:inxx db.define_table('person', Field('name'), Field('father_id', 'reference person'), Field('mother_id', 'reference person')) :code Notez que la notation alternative de l'utilisation d'un objet table comme type champ va échouer dans ce cas, car il utilise une variable db.person avant de l'avoir définie : db.define_table('person', Field('name'), Field('father_id', db.person), # wrong! Field('mother_id', db.person)) # wrong! :code En général, db.tablename et "reference tablename" sont des types de champ équivalents, mais le dernier est le seul autorisé pour les self.references. with_alias:inxx Si la table se réfère à elle-même, alors il n'est pas possible d'effectuer un JOIN pour sélectionner une personne et ses parents sans utiliser le mot-clé SQL "AS". Ceci est effectué dans web2py en utilisant with_alias. Voici un exemple : >>> 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

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 :code Mais il est important d'avoir une distinction claire afin de construire des requêtes correctes. ### Fonctionnalités avancées #### Héritage de Tableinheritance:inxx Il est possible de créer une table qui contient tous les champs d'une autre table. C'est suffisant pour passer l'autre table à la place d'un champ pour define_table. Par exemple db.define_table('person', Field('name')) db.define_table('doctor', db.person, Field('specialization')) :code dummy table:inxx Il est également possible de définir une fausse table qui n'est pas stockée dans la base de données afin de la réutiliser dans de multiples autres cas. Par exemple : 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) :code Cet exemple suppose que l'authentificaiton standard web2py est activée. Notez que si vous utilisez Auth web2py créé déjà une telle table pour vous : auth = Auth(db) db.define_table('payment', Field('amount', 'double'), auth.signature) Lorsque vous utilisez l'héritage de table, si vous voulez que la table héritante hérite des validateurs, assurez-vous de définir les validateurs de la table parent avant de définir la table à hériter. #### filter_in and filter_out

>>> 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)) :code Ici f est un dictionnaire de champs passés à insert ou update, id est l'id de l'enregistrement nouvellement inséré, s est l'objet Set utilisé pour la mise à jour ou la suppression. >>> 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)>,) :code Les valeurs de retour de ces callback devraient être None ou False. Si n'importe quel callback _before_* retourne une valeur True il abandonnera l'opération actuelle insert/update/delete. update_naive:inxx Parfois un callback peut avoir besoin d'effectuer une mise à jour dans la même table ou une table différente et voudrait éviter les callbacks de s'appeler eux-mêmes récursivement. Pour cela, il y a des objets Set qui ont une méthode update_naive qui fonctionne comme update mais ignore les callbacks avant et après. [[versioning]] #### Versioning d'enregistrement_enable_record_versioning:inxx Il est possible de demander à web2py de sauver toute copie d'un enregistrement lorsque l'enregistrement est individuellement modifié. Il y a différents moyens de le faire et ce peut être fait pour toutes les tables en une fois en utilisant la syntaxe : auth.enable_record_versioning(db)

db.define_table('stored_item', Field('name'), Field('quantity','integer'), Field('is_active','boolean', writable=False,readable=False,default=True)) :code Notez le champs caché booléen appelé is_active et défini par défaut à True. Nous pouvons indiquer à web2py de créer une nouvelle table (dans la même base ou dans une différente) et stocker toutes les versions précédentes de chaque enregistrement dans la table, dès qu'il y a modification. Ceci est fait de la façon suivante : db.stored_item._enable_record_versioning()

db.stored_item._enable_record_versioning( archive_db = db, archive_name = 'stored_item_archive', current_record = 'current_record', is_active = 'is_active') Le archive_db=True indique à web2py de stocker la table archite dans la même base que la table stored_item. Le archive_name définit le nom de pour la table archive. La table archive a les mêmes champs que la table originale stored_item sauf que les champs unique ne sont plus uniques (puisqu'il est nécessaire de stocker plusieurs versions) et et a un champ supplémentaire dont le nom est spécifié par current_record et qui est une référence à l'enregistrement courant dans la table stored_item. Lorsque les enregistrements sont supprimé, ils ne sont pas réellement supprimés. Un enregistrement supprimé est copié dans la table stored_item_archive (comme lorsqu'il est modifié) et le champ is_active est défini à False. En activant le versioning d'enregistrements, web2py définir un custom_filter sur cette table qui cache tous les enregistrements dans la table stored_item où le champ is_active est défini à False. Le paramètre is_active dans la méthode _enable_record_versioning permet de spécifier le nom du champ utilisé par le custom_filter pour déterminer si le champ a été supprimé ou non. custom_filters sont ignorés par l'interface appadmin. #### Champs communs et multi-locationcommon fields:inxxmulti tenancy:inxx db._common_fields est une liste de champs qui devraient appartenir à toutes les tables. Cette liste peut aussi contenir des tables et est comprise par tous les champs de la table. Par exemple, vous pouvez parfois vous retrouver à avoir besoin d'ajouter une signature à toutes vos tables sauf les tables auth. Dans ce cas, après db.define_tables() mais avant que vous définissiez toute autre table, insérez db._common_fields.append(auth.signature)

Un champ est spécial : "request_tenant".
Ce champ n'existe pas mais vous pouvez le créer et l'ajouter à n'importe laquelle de vos tables (ou toutes) :

db._common_fields.append(Field('request_tenant', default=request.env.http_host,writable=False)) Pour toute table avec un champ appelé db._request_tenant, tous les champs pour toutes les requêtes sont toujours automatiquement filtrés par : db.table.request_tenant == db.table.request_tenant.default

default = request.env.http_host

i.e. nous avons choisi de demander à notre application de filtrer toutes les tables dans toutes les requêtes avec 

db.table.request_tenant == request.env.http_host Cette simple astuce nous permet de rendre n'importe quelle application multi-tenante. i.e. même si nous démarrons une instance de l'application et que nous utilisons une seule base de données, si l'application est accessible via deux domaines ou plus (dans l'exemple où le nom de domaine est récupéré depuis request.env.http_post) les visiteurs verront des données différentes selon le domaine. Imaginez démarrer de multiples stockages web sous différents domaines avec une seule application et une seule base de données. Vous pouvez éteindre les filtres de multi-tenancy en utilisant : ignore_common_filters:inxx rows = db(query, ignore_common_filters=True).select()

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 )

Tout select, delete ou update dans cette table, va inclure seulement les posts publics du blog. L'attribut peut aussi être changé dans les contrôleurs :

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

Il sert dans les deux cas à éviter la répétition de la phrase "db.blog_post.is_public==True" dans chaque recherche de post de blog, et aussi comme amélioration de sécurité, qui vous évite d'oublier désactiver le visionnage des posts non publics.

Dans le cas où vous voulez annuler des objets avec le filtre commun (par exemple, autoriser l'admin à voir ces posts non publics), vous pouvez aussi supprimer le filtre :

db.blog_post._common_filter = None

ou l'ignorer :

db(query, ignore_common_filters=True).select(...) #### Types de Field personnalisés (expérimental) SQLCustomType:inxx En plus d'utiliser filter_in et filter_out, il est possible de définir des types de champ nouveaux/personnalisés. Par exemple, nous considérons ici un champ qui contient des données binaires sous forme compressée : 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)) :code SQLCustomType est un champ type de construction. Son argument type peut être l'un des types standard web2py. Il indique à web2py comment traiter les valeurs de champs au niveau de web2py. native est le type du champ tant que la base de données est concernée. Les noms autorisés dépendent du système de base de données. encoder est une fonction de transformation optionnelle appliquée lorsque les données sont stockées et decoder est une fonction de transformation optionnelle inverse. La fonctionnalité est marquée comme expérimentale. En pratique, elle est intégrée à web2py depuis longtemps et fonctionne mais peut rendre le code non portable, par exemple lorsque le type natif est spécifique à la base de données. Cela ne fonctionne pas sur Google App Engine NoSQL. #### Utiliser la DAL sans définir de tables La DAL peut être utilisée depuis n'importe quel programme Python simplement en faisant : from gluon import DAL, Field db = DAL('sqlite://storage.sqlite',folder='path/to/app/databases') :code i.e. importer la DAL, Field, se connecter et spécifier le dossier qui contient les fichier .table (le dossier app/databases). i.e. import the DAL, Field, connect and specify the folder which contains the .table files (the app/databases folder). Pour accéder aux données et ses attributs, nous devons encore définir toutes les tables que nous allons utiliser avec db.define_tables(...). Si nous avons juste besoin d'accéder aux données mais pas aux attributs de la table web2py, nous passons outre sans redéfinir les tables mais en demandant simplement à web2py de lire les informations nécessaires depuis les méta-données dans les fichiers .table : from gluon import DAL, Field db = DAL('sqlite://storage.sqlite',folder='path/to/app/databases', auto_import=True)) :code Ceci nous permet d'accéder à tout db.table sans devoir le redéfinir. #### PostGIS, SpatiaLite, et MS Geo (expérimental) PostGIS:inxx StatiaLite:inxx Geo Extensions:inxxgeometry:inxx geoPoint:inxx geoLine:inxx geoPolygon:inxx La DAL support les APIs géographiques en utilisant PostGIS (pour PostgreSQL), spatialite (pour SQLite), et MSSQL et les extensions Spatial. C'est une fonctionnalité qui a été sponsorisé par le projet Sahana et implémenté par Denes Lengyel. La DAL fournit des types de champs géométriques et géographiques et les fonctions suivantes : st_asgeojson:inxx st_astext:inxx st_contains:inxxst_distance:inxx st_equals:inxx st_intersects:inxx st_overlaps:inxxst_simplify:inxx st_touches:inxx st_within:inxx st_asgeojson (PostGIS only) st_astext st_contains st_distance st_equals st_intersects st_overlaps st_simplify (PostGIS only) st_touches st_within st_x st_y

Voici quelques exemples :

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

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

rows = db(sp.id>0).select() :code retourne toujours les données géométriques sérialisées en texte. Vous pouvez aussi faire la mêmes chose plus explicitement en utilisant st_astext() : 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))" :code Vous pouvez demander la représentation native en utilisant st_asgeojson() (sous PostGIS seulement): 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, 3, [0, 0], [150, 0], [150, 150], [0, 150], [0,]

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

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

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

et vous souhaitez déplacer vers une autre base en utilisant une chaîne de connexion différente :

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

Avant de basculer, vous voulez déplacer les données et reconstruire les méta-données pour la nouvelle base. Nous supposons que la nouvelle base existe mais nous pourrions également supposer qu'elle est vide.

Web2py fournit un script qui fait cela pour vous :

cd web2py python scripts/cpdb.py -f applications/app/databases -y 'sqlite://storage.sqlite' -Y 'postgres://username:password@localhost/mydb'

Après avoir lancé le script vous pouvez simplement basculer la chaîne de connexion dans le modèle et tout devrait fonctionner directement. Les nouvelles données devraient être là.

Ce script fournit des lignes de commandes variées qui vous permettent de déplacer les données d'une application à une autre, déplacer toutes les tables ou seulement quelques tables, effacer les données dans les tables. Pour plus d'information, essayez :

python scripts/cpdb.py -h

#### Note sur les nouvelles DAL et adaptateurs

Le code source de la DAL a été complètement ré-écrit en 2010. Tant qu'il reste rétro-compatible, la ré-écriture l'a rendue plus modulaire et plus facile à étendre. Nous expliquons ici la logique principale.

Le fichier "gluon/dal.py" définit, parmi tant d'autres, les classes suivantes.

ConnectionPool BaseAdapter extends ConnectionPool Row DAL Reference Table Expression Field Query Set Rows Leur usage a été expliqué dans les sections précédentes sauf pour BaseAdapter. Lorsque les méthodes d'un objet Table ou Set ont besoin de communiquer avec la base de données, ils délèguent aux méthodes de l'adaptateur la tâche de générer le code SQL et/ou l'appel de fonction. Par exemple : db.mytable.insert(myfield='myvalue')

appelle

Table.insert(myfield='myvalue')

qui délègue à l'adaptateur de retourner :

db._adapter.insert(db.mytable,db.mytable._listify(dict(myfield='myvalue'))) Ici db.mytable._listify convertit le dictionnaire d'arguments en une liste de (field,value) et appelle la méthode insert de l'adapter. db._adapter fait plus ou moins ce qui suit : query = db._adapter._insert(db.mytable,list_of_fields) db._adapter.execute(query)

où la première ligne construit la requête et la seconde l'exécute.

specify a diver to use driver = globals().get('pymysql',None)

map web2py types into database types types = { 'boolean': 'CHAR(1)', 'string': 'VARCHAR(%(length)s)', 'text': 'LONGTEXT', ... }

connect to the database using driver def __init__(self,db,uri,pool_size=0,folder=None,db_codec ='UTF-8', credential_decoder=lambda x:x, driver_args={}, adapter_args={}):

parse uri string and store parameters in driver_args ...

define a connection function def connect(driver_args=driver_args): return self.driver.connect(**driver_args)

place it in the pool self.pool_connection(connect)

set optional parameters (after connection) self.execute('SET FOREIGN_KEY_CHECKS=1;') self.execute("SET sql_mode='NO_BACKSLASH_ESCAPES';")

override BaseAdapter methods as needed def lastrowid(self,table): self.execute('select last_insert_id();') return int(self.cursor.fetchone()[0])

:code En regardant les divers adaptateurs en exemple, il devrait être facile d'en écrire de nouveaux. Lorsque l'instance db est créée : db = DAL('mysql://...')

le préfixe dans la chaîne URI définit l'adaptateur. Le mapping est défini dans le dictionnaire suivant également dans "gluon/dal.py" :

ADAPTERS = { 'sqlite': SQLiteAdapter, 'sqlite:memory': SQLiteAdapter, 'mysql': MySQLAdapter, 'postgres': PostgreSQLAdapter, 'oracle': OracleAdapter, 'mssql': MSSQLAdapter, 'mssql2': MSSQL2Adapter, 'db2': DB2Adapter, 'teradata': TeradataAdapter, 'informix': InformixAdapter, '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, # discouraged, for backward compatibility 'google:datastore': GoogleDatastoreAdapter, 'google:sql': GoogleSQLAdapter, 'couchdb': CouchDBAdapter, 'mongodb': MongoDBAdapter, 'imap': IMAPAdapter }

import MySQLdb as mysqldb from gluon.dal import MySQLAdapter MySQLAdapter.driver = mysqldb i.e. mysqldb doit être ''that module'' avec une méthode .connect(). Vous pouvez spécifier des arguments optionnels pour le driver et pour l'adaptateur : db =DAL(..., driver_args={}, adapter_args={}) ### Pièges #### SQLite SQLite ne supporte pas le dropping ou la modification de colonnes. Cela signifie que les migration web2py fonctionneront jusqu'à un point. Si vous supprimez un champ d'une table, la colonne restera dans la base de données mais sera invisible pour web2py. Si vous décidez de ré-instancier la colonne, web2py la re-crééra et échouera. Dans ce cas, vous devez définir fake_migrate=True pour que les méta-données soient reconstruites sans essayer d'ajouter la colonne à nouveau. Aussi, pour la même raison, **SQLite** n'est pas conscient des changements de type de colonne. Si vous insérez un chiffre dans un champ chaîne, il sera stocké comme chaîne. Si plus tard vous modifiez le modèle, et que vous remplacez le type "string" par le type "integer", SQLite continuera à conserver le chiffre comme chaîne et cela peut poser des problèmes lorsque vous essaierez d'extraire les données. SQLite n'a pas de type booléen. web2py mappe de façon interne les booléens à une chaîne de 1 caractère, avec 'T' et 'F' représentant True et False. La DAL gère cela entièrement ; l'abstraction d'un vrai booléen fonctionne bien. Mais si vous mettez à jour la table SQLite avec du SQL directement, soyez conscient de l'implémentation web2py, et évitez d'utiliser les valeurs 0 et 1. #### MySQL MySQL ne supporte pas les multiples ALTER TABLE dans une simple transaction. Cela signifie que tout process de migration est séparé en plusieurs commits. Si quelque chose survient en causant un échec, il est possible de casser une migration (les méta-données web2py ne sont plus en synchronisation avec la structure de la table actuelle dans la base de donnée). C'est dommage mais peut être évité (migrer une table à la fois) ou peut être corrigé à posteriori (en inversant le modèle web2py à ce qui correspond à la structure de la table dans la base de données. Définir fake_migrate=True et une fois les méta-données reconstruites, définir fake_migrate=False et migrer la table à nouveau). #### Google SQL Google SQL a les mêmes problèmes que MySQL et même plus. En particulier, les méta-données de table elles-mêmes doivent être stockées dans la base de données dans une table qui n'est pas migrée par web2py. Ceci car Google App Engine a un système de fichiers en lecture seule. Les migration web2py dans Google:SQL combinées avec le problème MySQL décrit ci-dessus peut résulter en une corruption des méta-données. Encore une fois, ceci peut être évité (en migrant la table une première fois et en définissant ensuite migrate=False afin que la table des méta-données ne soit plus accédée) ou peut être corrigé à posteriori (en accédant à la base de données en utilisant le dashboard Google et en supprimant toute entrée corrompue de la table appelée web2py_filesystem. #### MSSQL (Microsoft SQL Server)limitby:inxx MSSQL ne supporte pas le mot-clé SQL OFFSET. Donc la base de données ne peut pas faire de pagination. Lorsque l'on fait un limitby=(a,b) web2py va récupérer les premières lignes b et annuler les premières a. Ceci peut résulter en une surcharge considérable lors de comparaison avec d'autres moteurs de bases de données. #### Oracle Oracle ne supporte pas non plus la pagination. Il ne supporte ni les mots-clés OFFSET ni LIMIT. Web2py effectue la pagination en traduisant un db(...).select(limitby=(a,b)) en un select complexe tiers (comme sugéré par la documentation officielle Oracle). Cela fonctionne pour de simple select mais peut se casser pour des selects complexes impliquant des champs aliasés ou des jointures. #### MSSQL MSSQL a des problèmes avec les références circulaires dans les tables qui ont ONDELETE CASCADE. C'est un bug MSSQL et le contournement est de définir l'attribut ondelete pour tous les champs de référence à "NO ACTION". Vous pouvez aussi le faire une seule fois avant de définir les tables : db = DAL('mssql://....') for key in ['reference','reference FK']: db._adapter.types[key]=db._adapter.types[key].replace( '%(on_delete_action)s','NO ACTION')

db(query).select(distinct=True)

cela ne fonctionne pas

db(query).select(distinct=db.mytable.myfield) #### Google NoSQL (Datastore) Google NoSQL (Datastore) ne permet pas les jointures, les jointures externes gauches, les aggrégats, les expressions, OR impliquant plus qu'une table, l'opérateur de recherche 'like' dans les champs "text". Les transactions sont limitées et ne sont pas fournies automatiquement par web2py (vous avez besoin d'utiliser les API Google run_in_transaction que vous pouvez retrouver dans la documentation en ligne de Google App Engine). Google limite également le nombre d'enregistrements que vous pouvez récupérer dans chaque requête (1000 à la fois en écriture). Sur le Google datastore, les IDs d'enregistrement sont des entiers mais ils ne sont pas séquentiels. Alors que sur SQL le type "list:string" est mappé en type "text", sur le Google Datastore il est mappé en ListStringProperty``. De la même manière "list:integer" et "list:reference" sont mappés en "ListProperty". Ceci rend les recherches pour du contenu dans ces types de champs plus efficaces sur Google NoSQL que sur les bases de données SQL.