← Back to branch summary

~vorlon/ubuntu/saucy/gourmet/trunk

« back to all changes in this revision

Viewing changes to src/lib/backends/db.py

  • Committer: Bazaar Package Importer
  • Author(s): Rolf Leggewie
  • Date: 2009-01-12 23:03:28 UTC
  • mfrom: (2.1.3 sid)
  • Revision ID: james.westby@ubuntu.com-20090112230328-wnuqqte22uk3981b
Tags: 0.14.3-2
http://bugs.debian.org/507382
Conflict with python-pysqlite >= 2.5 which when installed crashes 
gourmet at startup. (Closes: #507382) and python-sqlalchemy >= 0.5.
Both restrictions should eventually be relaxed when upstream has
made the code compatible with the newer libs.

Show diffs side-by-side

added added

removed removed

Lines of Context:
src/lib/backends/db.py
18
18
from gourmet.plugin import DatabasePlugin
19
19
 
20
20
import sqlalchemy, sqlalchemy.orm
21
 
from sqlalchemy import Integer, Binary, String, Float, Boolean, Numeric, Table, Column, ForeignKey
 
21
from sqlalchemy import Integer, Binary, String, Float, Boolean, Numeric, Table, Column, ForeignKey, Text
22
22
from sqlalchemy.sql import and_, or_
23
23
 
24
24
def map_type_to_sqlalchemy (typ):
30
30
        return String(
31
31
            length=int(typ[typ.find('(')+1:typ.find(')')])
32
32
            )
33
 
    if typ=='text': return String(length=None)
 
33
    if typ=='text': return Text()
34
34
    if typ=='bool': return Boolean()
35
35
    if typ=='float': return Float()
36
36
    if typ=='binary': return Binary()
59
59
    for k,v in fix_colnames(criteria,*tables).items():
60
60
        if type(v)==tuple:
61
61
            operator,value = v
 
62
            if type(v)==str:
 
63
                v = unicode(v)
62
64
            if operator=='in':
63
65
                args.append(k.in_(*value))
64
66
            elif hasattr(k,operator):
101
103
    
102
104
class DBObject:
103
105
    pass
104
 
 
105
 
 
106
106
# CHANGES SINCE PREVIOUS VERSIONS...
107
107
# categories_table: id -> recipe_id, category_entry_id -> id
108
108
# ingredients_table: ingredient_id -> id, id -> recipe_id
186
186
            #c = sqlite_connection.cursor()
187
187
            #c.execute('select name from sqlite_master')
188
188
            #sqlite_connection.create_function('instr',2,instr)
 
189
        self.db.commit() # Somehow necessary to prevent "DB Locked" errors 
189
190
        debug('Done initializing DB connection',1)
190
191
 
191
192
    def save (self):
242
243
            pass
243
244
        self._setup_object_for_table(self.info_table, Info)
244
245
        self.plugin_info_table = Table('plugin_info',self.metadata,
245
 
                                       Column('plugin',String(length=None),**{}),
 
246
                                       Column('plugin',Text(),**{}),
246
247
                                       # three part version numbers
247
248
                                       # 2.1.10, etc. 1.0.0 -- these
248
249
                                       # contain the Gourmet version
253
254
                                       Column('version_major',Integer(),**{}),
254
255
                                       Column('version_minor',Integer(),**{}),
255
256
                                       # Stores the last time the plugin was used...
256
 
                                       Column('plugin_version',String(length=None),**{}))
 
257
                                       Column('plugin_version',String(length=32),**{}))
257
258
        class PluginInfo (object):
258
259
            pass
259
260
        self._setup_object_for_table(self.plugin_info_table, PluginInfo)
261
262
    def setup_recipe_table (self):
262
263
        self.recipe_table = Table('recipe',self.metadata,
263
264
                                  Column('id',Integer(),**{'primary_key':True}),
264
 
                                  Column('title',String(length=None),**{}),
265
 
                                  Column('instructions',String(length=None),**{}),
266
 
                                  Column('modifications',String(length=None),**{}),
267
 
                                  Column('cuisine',String(length=None),**{}),
 
265
                                  Column('title',Text(),**{}),
 
266
                                  Column('instructions',Text(),**{}),
 
267
                                  Column('modifications',Text(),**{}),
 
268
                                  Column('cuisine',Text(),**{}),
268
269
                                  Column('rating',Integer(),**{}),
269
 
                                  Column('description',String(length=None),**{}),
270
 
                                  Column('source',String(length=None),**{}),
 
270
                                  Column('description',Text(),**{}),
 
271
                                  Column('source',Text(),**{}),
271
272
                                  Column('preptime',Integer(),**{}),
272
273
                                  Column('cooktime',Integer(),**{}),
273
274
                                  Column('servings',Float(),**{}),
278
279
                                  Column('recipe_hash',String(length=32),**{}),
279
280
                                  # A hash for uniquely identifying a recipe (based on ingredients)
280
281
                                  Column('ingredient_hash',String(length=32),**{}),
281
 
                                  Column('link',String(length=None),**{}), # A field for a URL -- we ought to know about URLs
 
282
                                  Column('link',Text(),**{}), # A field for a URL -- we ought to know about URLs
282
283
                                  Column('last_modified',Integer(),**{}),
283
284
                                  ) # RECIPE_TABLE_DESC
284
285
        class Recipe (object): pass
288
289
        self.categories_table = Table('categories',self.metadata,
289
290
                                    Column('id',Integer(),primary_key=True),
290
291
                                    Column('recipe_id',Integer,ForeignKey('recipe.id'),**{}), #recipe ID
291
 
                                    Column('category',String(length=None),**{}) # Category ID
 
292
                                    Column('category',Text(),**{}) # Category ID
292
293
                                    ) # CATEGORY_TABLE_DESC
293
294
        class Category (object): pass
294
295
        self._setup_object_for_table(self.categories_table,Category)
298
299
                                       Column('id',Integer(),primary_key=True),
299
300
                                       Column('recipe_id',Integer,ForeignKey('recipe.id'),**{}),
300
301
                                       Column('refid',Integer,ForeignKey('recipe.id'),**{}),
301
 
                                       Column('unit',String(length=None),**{}),
 
302
                                       Column('unit',Text(),**{}),
302
303
                                       Column('amount',Float(),**{}),
303
304
                                       Column('rangeamount',Float(),**{}),
304
 
                                       Column('item',String(length=None),**{}),
305
 
                                       Column('ingkey',String(length=None),**{}),
 
305
                                       Column('item',Text(),**{}),
 
306
                                       Column('ingkey',Text(),**{}),
306
307
                                       Column('optional',Boolean(),**{}),
307
308
                                       #Integer so we can distinguish unset from False
308
309
                                       Column('shopoptional',Integer(),**{}), 
309
 
                                       Column('inggroup',String(length=None),**{}),
 
310
                                       Column('inggroup',Text(),**{}),
310
311
                                       Column('position',Integer(),**{}),
311
312
                                       Column('deleted',Boolean(),**{}),
312
313
                                       )
317
318
        # Keylookup table - for speedy keylookup
318
319
        self.keylookup_table = Table('keylookup',self.metadata,
319
320
                                     Column('id',Integer(),primary_key=True),
320
 
                                     Column('word',String(length=None),**{}),
321
 
                                      Column('item',String(length=None),**{}),
322
 
                                      Column('ingkey',String(length=None),**{}),
 
321
                                     Column('word',Text(),**{}),
 
322
                                      Column('item',Text(),**{}),
 
323
                                      Column('ingkey',Text(),**{}),
323
324
                                      Column('count',Integer(),**{})
324
325
                                     ) # INGKEY_LOOKUP_TABLE_DESC
325
326
        class KeyLookup (object): pass
331
332
 
332
333
        # shopcats - Keep track of which shoppin category ingredients are in...
333
334
        self.shopcats_table = Table('shopcats',self.metadata,
334
 
                                    Column('ingkey',String(length=None),**{'primary_key':True}),
335
 
                                    Column('shopcategory',String(length=None),**{}),
 
335
                                    Column('ingkey',Text(),**{'primary_key':True}),
 
336
                                    Column('shopcategory',Text(),**{}),
336
337
                                    Column('position',Integer(),**{}),
337
338
                                    )
338
339
        class ShopCat (object): pass
340
341
        
341
342
        # shopcatsorder - Keep track of the order of shopping categories
342
343
        self.shopcatsorder_table = Table('shopcatsorder',self.metadata,
343
 
                                         Column('shopcategory',String(length=None),**{'primary_key':True}),
 
344
                                         Column('shopcategory',Text(),**{'primary_key':True}),
344
345
                                         Column('position',Integer(),**{}),
345
346
                                         )
346
347
        class ShopCatOrder (object): pass
349
350
        # pantry table -- which items are in the "pantry" (i.e. not to
350
351
        # be added to the shopping list)
351
352
        self.pantry_table = Table('pantry',self.metadata,
352
 
                                  Column('ingkey',String(length=None),**{'primary_key':True}),
 
353
                                  Column('ingkey',Text(),**{'primary_key':True}),
353
354
                                  Column('pantry',Boolean(),**{}),
354
355
                                  )
355
356
        class Pantry (object): pass
476
477
                self.add_column_to_table(self.recipe_table,('recipe_hash',String(length=32),{}))
477
478
                self.add_column_to_table(self.recipe_table,('ingredient_hash',String(length=32),{}))
478
479
                # Add a link field...
479
 
                self.add_column_to_table(self.recipe_table,('link',String(length=None),{}))
 
480
                self.add_column_to_table(self.recipe_table,('link',Text(),{}))
480
481
                print 'Searching for links in old recipe fields...'
481
482
                URL_SOURCES = ['instructions','source','modifications']
482
483
                recs = self.search_recipes(
1062
1063
 
1063
1064
    def add_rec (self, dic, accept_ids=False):
1064
1065
        """Dictionary is a dictionary of column values for our recipe.
 
1066
        Return the ID of the newly created recipe.
1065
1067
 
1066
1068
        If accept_ids is True, we accept recipes with IDs already
1067
1069
        set. These IDs need to have been reserved with the new_id()
1079
1081
            ret = self.do_add_rec(dic)
1080
1082
        except:
1081
1083
            print 'Problem adding recipe with dictionary...'
1082
 
            for k,v in dic.items(): print 'KEY:',k,'VALUE:',v
 
1084
            for k,v in dic.items(): print 'KEY:',k,'of type',type(k),'VALUE:',v,'of type',type(v)
1083
1085
            raise
1084
1086
        else:
1085
1087
            if type(ret)==int:
1086
1088
                ID = ret
 
1089
                ret = self.get_rec(ID) 
1087
1090
            else:
1088
1091
                ID = ret.id
1089
1092
            for c in cats:
1104
1107
            print 'Problem adding',dic
1105
1108
            raise
1106
1109
 
 
1110
    def add_ings (self, dics):
 
1111
        """Add multiple ingredient dictionaries at a time."""
 
1112
        for d in dics: self.validate_ingdic(d)
 
1113
        try:
 
1114
            self.ingredients_table.insert().execute(*dics)
 
1115
        except ValueError:
 
1116
            for d in dics: self.coerce_types(self.ingredients_table,d)
 
1117
            self.ingredients_table.insert().execute(*dics)
 
1118
 
1107
1119
    # Lower level DB access functions -- hopefully subclasses can
1108
1120
    # stick to implementing these    
1109
1121
 
1356
1368
        else:
1357
1369
            return amt
1358
1370
 
1359
 
    def get_amount_and_unit (self, ing, mult=1, conv=None,fractions=convert.FRACTIONS_ALL):
 
1371
    def get_amount_and_unit (self, ing, mult=1, conv=None,fractions=None):
1360
1372
        """Return a tuple of strings representing our amount and unit.
1361
1373
        
1362
1374
        If we are handed a converter interface, we will adjust the
1377
1389
    def get_amount_as_string (self,
1378
1390
                              ing,
1379
1391
                              mult=1,
1380
 
                              fractions=convert.FRACTIONS_ALL
 
1392
                              fractions=None,
1381
1393
                              ):
1382
1394
        """Return a string representing our amount.
1383
1395
        If we have a multiplier, multiply the amount before returning it.        
1385
1397
        amt = self.get_amount(ing,mult)
1386
1398
        return self._format_amount_string_from_amount(amt, fractions=fractions)
1387
1399
 
1388
 
    def _format_amount_string_from_amount (self, amt, fractions=convert.FRACTIONS_ALL):
 
1400
    def _format_amount_string_from_amount (self, amt, fractions=None):
1389
1401
        """Format our amount string given an amount tuple.
1390
1402
 
 
1403
        If fractions is None, we use the default setting from
 
1404
        convert.USE_FRACTIONS. Otherwise, we will override that
 
1405
        setting.
 
1406
        
1391
1407
        If you're thinking of using this function from outside, you
1392
1408
        should probably just use a convenience function like
1393
1409
        get_amount_as_string or get_amount_and_unit
1394
1410
        """
 
1411
        if fractions is None:
 
1412
            # None means use the default value
 
1413
            fractions = convert.USE_FRACTIONS
1395
1414
        if type(amt)==tuple:
1396
1415
            return "%s-%s"%(convert.float_to_frac(amt[0],fractions=fractions).strip(),
1397
1416
                            convert.float_to_frac(amt[1],fractions=fractions).strip())