~ubuntu-branches/ubuntu/maverick/gourmet/maverick-proposed : revision 15

1

## For testing - DELETE ME

2

#import sys

3

#sys.path.append('/usr/share/')

4

## End for testing - DELETE ME

5

import shutil

6

import types

7

import os.path

8

from gourmet.gdebug import debug, TimeAction, debug_decorator

9

import re, pickle, string, os.path, string, time

10

from gettext import gettext as _

11

import gourmet.gglobals as gglobals

12

from gourmet import Undo, keymanager, convert

13

from gourmet.defaults import lang as defaults

14

import StringIO

15

from gourmet import ImageExtras

16

import gourmet.version

17

import gourmet.recipeIdentifier as recipeIdentifier

18

from gourmet.plugin_loader import Pluggable, pluggable_method

19

from gourmet.plugin import DatabasePlugin

20

21

import sqlalchemy, sqlalchemy.orm

22

from sqlalchemy import Integer, Binary, String, Float, Boolean, Numeric, Table, Column, ForeignKey, Text

23

try:

24

from sqlalchemy import LargeBinary

25

except:

26

from sqlalchemy import Binary as LargeBinary

27

from sqlalchemy.sql import and_, or_, case

28

from sqlalchemy import func

29

30

def map_type_to_sqlalchemy (typ):

31

"""A convenience method -- take a string type and map it into a

32

sqlalchemy type.

33

"""

34

if typ=='int': return Integer()

35

if typ.find('char(')==0:

36

return String(

37

length=int(typ[typ.find('(')+1:typ.find(')')])

38

)

39

if typ=='text': return Text()

40

if typ=='bool': return Boolean()

41

if typ=='float': return Float()

42

if typ=='binary': return LargeBinary()

43

44

def fix_colnames (dict, *tables):

45

"""Map column names to sqlalchemy columns.

46

"""

47

# This is a convenience method -- throughout Gourmet, the column

48

# names are handed around as strings. This converts them into the

49

# object sqlalchemy prefers.

50

newdict = {}

51

for k,v in dict.items():

52

got_prop = False

53

for t in tables:

54

try:

55

newdict[getattr(t.c,k)]=v

56

except:

57

1

58

else:

59

got_prop = True

60

if not got_prop: raise ValueError("Could not find column %s in tables %s"%(k,tables))

61

return newdict

62

63

def make_simple_select_arg (criteria,*tables):

64

args = []

65

for k,v in fix_colnames(criteria,*tables).items():

66

if type(v)==str:

67

v = unicode(v)

68

if type(v)==tuple:

69

operator,value = v

70

if type(value)==str:

71

value = unicode(value)

72

if operator=='in':

73

args.append(k.in_(value))

74

elif hasattr(k,operator):

75

args.append(getattr(k,operator)(value))

76

elif hasattr(k,operator+'_'): # for keywords like 'in'

77

args.append(getattr(k,operator+'_')(value))

78

else:

79

args.append(k.op(operator)(value))

80

else:

81

args.append(k==v)

82

if len(args)>1:

83

return [and_(*args)]

84

elif args:

85

return [args[0]]

86

else:

87

return []

88

89

def make_order_by (sort_by, table, count_by=None, join_tables=[]):

90

ret = []

91

for col,direction in sort_by:

92

if col=='count' and not hasattr(table.c,'count'):

93

col = sqlalchemy.func.count(getattr(table.c,count_by))

94

else:

95

if hasattr(table.c,col):

96

col = getattr(table.c,col)

97

elif join_tables:

98

broken = True

99

for t in join_tables:

100

if hasattr(t.c,col):

101

broken = False

102

col = getattr(t.c,col)

103

break

104

if broken:

105

raise ValueError("No such column for tables %s %s: %s"%(table, join_tables, col))

106

if isinstance(col.type, Text):

107

# Sort nulls last rather than first using case statement...

108

col = case([(col == None, '"%s"'%'z'*20),

109

(col == '', '"%s"'%'z'*20),

110

],else_=func.lower(col))

111

if direction==1: # Ascending

112

ret.append(sqlalchemy.asc(col))

113

else:

114

ret.append(sqlalchemy.desc(col))

115

return ret

116

117

class DBObject:

118

pass

119

# CHANGES SINCE PREVIOUS VERSIONS...

120

# categories_table: id -> recipe_id, category_entry_id -> id

121

# ingredients_table: ingredient_id -> id, id -> recipe_id

122

123

class RecData (Pluggable):

124

125

"""RecData is our base class for handling database connections.

126

127

Subclasses implement specific backends, such as metakit, sqlite, etc."""

128

129

# constants for determining how to get amounts when there are ranges.

130

AMT_MODE_LOW = 0

131

AMT_MODE_AVERAGE = 1

132

AMT_MODE_HIGH = 2

133

134

_singleton = {}

135

136

def __init__ (self, file=os.path.join(gglobals.gourmetdir,'recipes.db'),

137

custom_url=None):

138

# hooks run after adding, modifying or deleting a recipe.

139

# Each hook is handed the recipe, except for delete_hooks,

140

# which is handed the ID (since the recipe has been deleted)

141

if RecData._singleton.has_key(file):

142

raise RecData._singleton[file]

143

else:

144

RecData._singleton[file] = self

145

# We keep track of IDs we've handed out with new_id() in order

146

# to prevent collisions

147

self.new_ids = []

148

self._created = False

149

if custom_url:

150

self.url = custom_url

151

self.filename = None

152

else:

153

self.filename = file

154

self.url = 'sqlite:///' + self.filename

155

self.add_hooks = []

156

self.modify_hooks = []

157

self.delete_hooks = []

158

self.add_ing_hooks = []

159

timer = TimeAction('initialize_connection + setup_tables',2)

160

self.initialize_connection()

161

Pluggable.__init__(self,[DatabasePlugin])

162

self.setup_tables()

163

self.metadata.create_all()

164

self.update_version_info(gourmet.version.version)

165

self._created = True

166

timer.end()

167

168

# Basic setup functions

169

170

def initialize_connection (self):

171

"""Initialize our database connection.

172

173

This should also set self.new_db accordingly"""

174

debug('Initializing DB connection',1)

175

if self.filename:

176

self.new_db = not os.path.exists(self.filename)

177

#print 'Connecting to file ',self.filename,'new=',self.new_db

178

else:

179

self.new_db = True # ??? How will we do this now?

180

self.db = sqlalchemy.create_engine(self.url,strategy='threadlocal')

181

self.db.begin()

182

self.metadata = sqlalchemy.MetaData(self.db)

183

# Be noisy... (uncomment for debugging/fiddling)

184

# self.metadata.bind.echo = True

185

try:

186

self.session = sqlalchemy.orm.create_session()

187

except AttributeError:

188

# older sqlalchemy support

189

self.session = sqlalchemy.create_session()

190

#raise NotImplementedError

191

def regexp(expr, item):

192

if item:

193

return re.search(expr,item,re.IGNORECASE) is not None

194

else:

195

return False

196

def instr(s,subs): return s.lower().find(subs.lower())+1

197

# Workaround to create REGEXP function in sqlite

198

if self.url.startswith('sqlite'):

199

sqlite_connection = self.db.connect().connection

200

sqlite_connection.create_function('regexp',2,regexp)

201

#c = sqlite_connection.cursor()

202

#c.execute('select name from sqlite_master')

203

#sqlite_connection.create_function('instr',2,instr)

204

self.db.commit() # Somehow necessary to prevent "DB Locked" errors

205

debug('Done initializing DB connection',1)

206

207

def save (self):

208

"""Save our database (if we have a separate 'save' concept)"""

209

row = self.fetch_one(self.info_table)

210

if row:

211

self.do_modify(

212

self.info_table,

213

row,

214

{'last_access':time.time()},

215

id_col = None

216

)

217

else:

218

self.do_add(

219

self.info_table,

220

{'last_access':time.time()}

221

)

222

self.db.commit()

223

224

def _setup_object_for_table (self, table, klass):

225

self.__table_to_object__[table] = klass

226

#print 'Mapping ',repr(klass),'->',repr(table)

227

if True in [col.primary_key for col in table.columns]:

228

sqlalchemy.orm.mapper(klass,table)

229

else:

230

# if there's no primary key...

231

raise "All tables need a primary key -- specify 'rowid'/Integer/Primary Key in table spec for %s"%table

232

233

@pluggable_method

234

def setup_tables (self):

235

"""

236

Subclasses should do any necessary adjustments/tweaking before calling

237

this function."""

238

# Info table - for versioning info

239

self.__table_to_object__ = {}

240

self.setup_base_tables()

241

self.setup_shopper_tables() # could one day be part of a plugin

242

243

def setup_base_tables (self):

244

self.setup_info_table()

245

self.setup_recipe_table()

246

self.setup_category_table()

247

self.setup_ingredient_table()

248

249

def setup_info_table (self):

250

self.info_table = Table('info',self.metadata,

251

Column('version_super',Integer(),**{}), # three part version numbers 2.1.10, etc. 1.0.0

252

Column('version_major',Integer(),**{}),

253

Column('version_minor',Integer(),**{}),

254

Column('last_access',Integer(),**{}),

255

Column('rowid',Integer(),**{'primary_key':True})

256

)

257

class Info (object):

258

pass

259

self._setup_object_for_table(self.info_table, Info)

260

self.plugin_info_table = Table('plugin_info',self.metadata,

261

Column('plugin',Text(),**{}),

262

# three part version numbers

263

# 2.1.10, etc. 1.0.0 -- these

264

# contain the Gourmet version

265

# at the last time of

266

# plugging-in

267

Column('id',Integer(),**{'primary_key':True}),

268

Column('version_super',Integer(),**{}),

269

Column('version_major',Integer(),**{}),

270

Column('version_minor',Integer(),**{}),

271

# Stores the last time the plugin was used...

272

Column('plugin_version',String(length=32),**{}))

273

class PluginInfo (object):

274

pass

275

self._setup_object_for_table(self.plugin_info_table, PluginInfo)

276

277

def setup_recipe_table (self):

278

self.recipe_table = Table('recipe',self.metadata,

279

Column('id',Integer(),**{'primary_key':True}),

280

Column('title',Text(),**{}),

281

Column('instructions',Text(),**{}),

282

Column('modifications',Text(),**{}),

283

Column('cuisine',Text(),**{}),

284

Column('rating',Integer(),**{}),

285

Column('description',Text(),**{}),

286

Column('source',Text(),**{}),

287

Column('preptime',Integer(),**{}),

288

Column('cooktime',Integer(),**{}),

289

# Note: we're leaving servings

290

# around as a legacy column... it is

291

# replaced by yields/yield_unit, but

292

# update is much easier if it's

293

# here, and it doesn't do much harm

294

# to have it around.

295

Column('servings',Float(),**{}),

296

Column('yields',Float(),**{}),

297

Column('yield_unit',String(length=32),**{}),

298

Column('image',LargeBinary(),**{}),

299

Column('thumb',LargeBinary(),**{}),

300

Column('deleted',Boolean(),**{}),

301

# A hash for uniquely identifying a recipe (based on title etc)

302

Column('recipe_hash',String(length=32),**{}),

303

# A hash for uniquely identifying a recipe (based on ingredients)

304

Column('ingredient_hash',String(length=32),**{}),

305

Column('link',Text(),**{}), # A field for a URL -- we ought to know about URLs

306

Column('last_modified',Integer(),**{}),

307

) # RECIPE_TABLE_DESC

308

309

class Recipe (object): pass

310

self._setup_object_for_table(self.recipe_table,Recipe)

311

312

def setup_category_table (self):

313

self.categories_table = Table('categories',self.metadata,

314

Column('id',Integer(),primary_key=True),

315

Column('recipe_id',Integer,ForeignKey('recipe.id'),**{}), #recipe ID

316

Column('category',Text(),**{}) # Category ID

317

) # CATEGORY_TABLE_DESC

318

class Category (object): pass

319

self._setup_object_for_table(self.categories_table,Category)

320

321

def setup_ingredient_table (self):

322

self.ingredients_table = Table('ingredients',self.metadata,

323

Column('id',Integer(),primary_key=True),

324

Column('recipe_id',Integer,ForeignKey('recipe.id'),**{}),

325

Column('refid',Integer,ForeignKey('recipe.id'),**{}),

326

Column('unit',Text(),**{}),

327

Column('amount',Float(),**{}),

328

Column('rangeamount',Float(),**{}),

329

Column('item',Text(),**{}),

330

Column('ingkey',Text(),**{}),

331

Column('optional',Boolean(),**{}),

332

#Integer so we can distinguish unset from False

333

Column('shopoptional',Integer(),**{}),

334

Column('inggroup',Text(),**{}),

335

Column('position',Integer(),**{}),

336

Column('deleted',Boolean(),**{}),

337

)

338

class Ingredient (object): pass

339

self._setup_object_for_table(self.ingredients_table, Ingredient)

340

341

def setup_keylookup_table (self):

342

# Keylookup table - for speedy keylookup

343

self.keylookup_table = Table('keylookup',self.metadata,

344

Column('id',Integer(),primary_key=True),

345

Column('word',Text(),**{}),

346

Column('item',Text(),**{}),

347

Column('ingkey',Text(),**{}),

348

Column('count',Integer(),**{})

349

) # INGKEY_LOOKUP_TABLE_DESC

350

class KeyLookup (object): pass

351

self._setup_object_for_table(self.keylookup_table, KeyLookup)

352

353

def setup_shopper_tables (self):

354

355

self.setup_keylookup_table()

356

357

# shopcats - Keep track of which shoppin category ingredients are in...

358

self.shopcats_table = Table('shopcats',self.metadata,

359

Column('ingkey',Text(),**{'primary_key':True}),

360

Column('shopcategory',Text(),**{}),

361

Column('position',Integer(),**{}),

362

)

363

class ShopCat (object): pass

364

self._setup_object_for_table(self.shopcats_table, ShopCat)

365

366

# shopcatsorder - Keep track of the order of shopping categories

367

self.shopcatsorder_table = Table('shopcatsorder',self.metadata,

368

Column('shopcategory',Text(),**{'primary_key':True}),

369

Column('position',Integer(),**{}),

370

)

371

class ShopCatOrder (object): pass

372

self._setup_object_for_table(self.shopcatsorder_table, ShopCatOrder)

373

374

# pantry table -- which items are in the "pantry" (i.e. not to

375

# be added to the shopping list)

376

self.pantry_table = Table('pantry',self.metadata,

377

Column('ingkey',Text(),**{'primary_key':True}),

378

Column('pantry',Boolean(),**{}),

379

)

380

class Pantry (object): pass

381

self._setup_object_for_table(self.pantry_table, Pantry)

382

383

# Keep track of the density of items...

384

self.density_table = Table('density',self.metadata,

385

Column('dkey',String(length=150),**{'primary_key':True}),

386

Column('value',String(length=150),**{})

387

)

388

class Density (object): pass

389

self._setup_object_for_table(self.density_table, Density)

390

391

self.crossunitdict_table = Table('crossunitdict',self.metadata,

392

Column('cukey',String(length=150),**{'primary_key':True}),

393

Column('value',String(length=150),**{}),

394

)

395

class CrossUnit (object): pass

396

self._setup_object_for_table(self.crossunitdict_table,CrossUnit)

397

398

self.unitdict_table = Table('unitdict',self.metadata,

399

Column('ukey',String(length=150),**{'primary_key':True}),

400

Column('value',String(length=150),**{}),

401

)

402

class Unitdict (object):

403

pass

404

self._setup_object_for_table(self.unitdict_table, Unitdict)

405

406

self.convtable_table = Table('convtable',self.metadata,

407

Column('ckey',String(length=150),**{'primary_key':True}),

408

Column('value',String(length=150),**{})

409

)

410

class Convtable (object):

411

pass

412

self._setup_object_for_table(self.convtable_table, Convtable)

413

414

def backup_db (self):

415

"""Make a backup copy of the DB -- this ensures experimental

416

code won't permanently screw our users."""

417

import time, os.path

418

backup_file_name = self.filename + '.backup-' + time.strftime('%d-%m-%y')

419

while os.path.exists(backup_file_name):

420

backup_file_name += 'I'

421

print 'Making a backup copy of DB in ',backup_file_name

422

print 'You can use it to restore if something ugly happens.'

423

shutil.copy(self.filename,backup_file_name) # Make a backup...

424

import gourmet.gtk_extras.dialog_extras as de

425

import gtk

426

de.show_message(

427

title=_("Upgrading database"),

428

label=_("Upgrading database"),

429

sublabel=_("Depending on the size of your database, this may be an intensive process and may take some time. Your data has been automatically backed up in case something goes wrong."),

430

expander=(_("Details"),_("A backup has been made in %s in case something goes wrong. If this upgrade fails, you can manually rename your backup file recipes.db to recover it for use with older Gourmet.")%backup_file_name),

431

message_type=gtk.MESSAGE_INFO)

432

433

def update_version_info (self, version_string):

434

"""Report our version to the database.

435

436

If necessary, we'll do some version-dependent updates to the GUI

437

"""

438

stored_info = self.fetch_one(self.info_table)

439

version = [s for s in version_string.split('.')]

440

current_super = int(version[0])

441

current_major = int(version[1])

442

current_minor = int(version[2])

443

if not stored_info or not stored_info.version_major:

444

# Default info -- the last version before we added the

445

# version tracker...

446

default_info = {'version_super':0,

447

'version_major':11,

448

'version_minor':0}

449

if not stored_info:

450

if not self.new_db:

451

self.do_add(self.info_table,

452

default_info)

453

else:

454

self.do_add(self.info_table,

455

{'version_super':current_super,

456

'version_major':current_major,

457

'version_minor':current_minor,}

458

)

459

else:

460

self.do_modify(

461

self.info_table,

462

stored_info,

463

default_info)

464

stored_info = self.fetch_one(self.info_table)

465

466

### Code for updates between versions...

467

if not self.new_db:

468

sv_text = "%s.%s.%s"%(stored_info.version_super,stored_info.version_major,stored_info.version_minor)

469

#print 'STORED_INFO:',stored_info.version_super,stored_info.version_major,stored_info.version_minor

470

# Change from servings to yields! ( we use the plural to avoid a headache with keywords)

471

if (stored_info.version_super == 0 and ((stored_info.version_major <= 14 and stored_info.version_minor <= 7)

472

or

473

(stored_info.version_major < 14)

474

)):

475

print 'Database older than 0.14.7 -- updating',sv_text

476

# Don't change the table defs here without changing them

477

# above as well (for new users) - sorry for the stupid

478

# repetition of code.

479

self.add_column_to_table(self.recipe_table,('yields',Float(),{}))

480

self.add_column_to_table(self.recipe_table,('yield_unit',String(length=32),{}))

481

#self.db.execute('''UPDATE recipes SET yield = servings, yield_unit = "servings" WHERE EXISTS servings''')

482

self.recipe_table.update(whereclause=self.recipe_table.c.servings

483

).values({

484

self.recipe_table.c.yield_unit:'servings',

485

self.recipe_table.c.yields:self.recipe_table.c.servings

486

}

487

).execute()

488

if stored_info.version_super == 0 and stored_info.version_major < 14:

489

print 'Database older than 0.14.0 -- updating',sv_text

490

self.backup_db()

491

# Name changes to make working with IDs make more sense

492

# (i.e. the column named 'id' should always be a unique

493

# identifier for a given table -- it should not be used to

494

# refer to the IDs from *other* tables

495

print 'Upgrade from < 0.14',sv_text

496

self.alter_table('categories',self.setup_category_table,

497

{'id':'recipe_id'},['category'])

498

print 'RECREATE INGREDIENTS TABLE (This could take a while...)'

499

self.alter_table('ingredients',self.setup_ingredient_table,

500

{'id':'recipe_id'},

501

['refid', 'unit', 'amount', 'rangeamount',

502

'item', 'ingkey', 'optional', 'shopoptional',

503

'inggroup', 'position', 'deleted'])

504

print 'RECREATE KEYLOOKUP TABLE'

505

self.alter_table('keylookup',self.setup_keylookup_table,

506

{},['word','item','ingkey','count'])

507

# Add recipe_hash, ingredient_hash and link fields

508

# (These all get added in 0.13.0)

509

if stored_info.version_super == 0 and stored_info.version_major <= 12:

510

self.backup_db()

511

print 'UPDATE FROM < 0.13.0...',sv_text

512

# Don't change the table defs here without changing them

513

# above as well (for new users) - sorry for the stupid

514

# repetition of code.

515

self.add_column_to_table(self.recipe_table,('last_modified',Integer(),{}))

516

self.add_column_to_table(self.recipe_table,('recipe_hash',String(length=32),{}))

517

self.add_column_to_table(self.recipe_table,('ingredient_hash',String(length=32),{}))

518

# Add a link field...

519

self.add_column_to_table(self.recipe_table,('link',Text(),{}))

520

print 'Searching for links in old recipe fields...',sv_text

521

URL_SOURCES = ['instructions','source','modifications']

522

recs = self.search_recipes(

523

[

524

{'column':col,

525

'operator':'LIKE',

526

'search':'%://%',

527

'logic':'OR'

528

}

529

for col in URL_SOURCES

530

])

531

for r in recs:

532

rec_url = ''

533

for src in URL_SOURCES:

534

blob = getattr(r,src)

535

url = None

536

if blob:

537

m = re.search('\w+://[^ ]*',blob)

538

if m:

539

rec_url = blob[m.start():m.end()]

540

if rec_url[-1] in ['.',')',',',';',':']:

541

# Strip off trailing punctuation on

542

# the assumption this is part of a

543

# sentence -- this will break some

544

# URLs, but hopefully rarely enough it

545

# won't harm (m)any users.

546

rec_url = rec_url[:-1]

547

break

548

if rec_url:

549

if r.source==rec_url:

550

new_source = rec_url.split('://')[1]

551

new_source = new_source.split('/')[0]

552

self.do_modify_rec(

553

r,

554

{'link':rec_url,

555

'source':new_source,

556

}

557

)

558

else:

559

self.do_modify_rec(

560

r,

561

{'link':rec_url,}

562

)

563

# Add hash values to identify all recipes...

564

for r in self.fetch_all(self.recipe_table): self.update_hashes(r)

565

566

if stored_info.version_super == 0 and stored_info.version_major <= 11 and stored_info.version_minor <= 3:

567

print 'version older than 0.11.4 -- doing update',sv_text

568

self.backup_db()

569

print 'Fixing broken ingredient-key view from earlier versions.'

570

# Drop keylookup_table table, which wasn't being properly kept up

571

# to date...

572

self.delete_by_criteria(self.keylookup_table,{})

573

# And update it in accord with current ingredients (less

574

# than an ideal decision, alas)

575

for ingredient in self.fetch_all(self.ingredients_table,deleted=False):

576

self.add_ing_to_keydic(ingredient.item,ingredient.ingkey)

577

578

for plugin in self.plugins:

579

self.update_plugin_version(plugin,

580

(current_super,current_major,current_minor)

581

)

582

### End of code for updates between versions...

583

if (current_super!=stored_info.version_super

584

or

585

current_major!=stored_info.version_major

586

or

587

current_minor!=stored_info.version_minor

588

):

589

self.do_modify(

590

self.info_table,

591

stored_info,

592

{'version_super':current_super,

593

'version_major':current_major,

594

'version_minor':current_minor,},

595

id_col=None

596

)

597

598

def update_plugin_version (self, plugin, current_version=None):

599

if current_version:

600

current_super,current_major,current_minor = current_version

601

else:

602

i = self.fetch_one(self.info_table)

603

current_super,current_major,current_minor = (i.version_super,

604

i.version_major,

605

i.version_minor)

606

existing = self.fetch_one(self.plugin_info_table,

607

plugin=plugin.name)

608

if existing:

609

sup,maj,minor,plugin_version = (existing.version_super,

610

existing.version_major,

611

existing.version_minor,

612

existing.plugin_version)

613

else:

614

# Default to the version before our plugin system existed

615

sup,maj,minor = 0,13,9

616

plugin_version = 0

617

try:

618

plugin.update_version(

619

gourmet_stored=(sup,maj,minor),

620

plugin_stored = plugin_version,

621

gourmet_current=(current_super,current_major,current_minor),

622

plugin_current = plugin.version,

623

)

624

except:

625

print 'Problem updating plugin',plugin,plugin.name

626

raise

627

# Now we store the information so we know we've done an update

628

info = {

629

'plugin':plugin.name,

630

'version_super':current_super,

631

'version_major':current_major,

632

'version_minor':current_minor,

633

'plugin_version':plugin.version}

634

if existing and (

635

current_minor != minor or

636

current_major != maj or

637

current_super != sup or

638

plugin.version != plugin_version):

639

self.do_modify(self.plugin_info_table,existing,info)

640

else:

641

self.do_add(self.plugin_info_table,info)

642

643

def run_hooks (self, hooks, *args):

644

"""A basic hook-running function. We use hooks to allow parts of the application

645

to tag onto data-modifying events and e.g. update the display"""

646

for h in hooks:

647

t = TimeAction('running hook %s with args %s'%(h,args),3)

648

h(*args)

649

t.end()

650

651

# basic DB access functions

652

def fetch_all (self, table, sort_by=[], **criteria):

653

return table.select(*make_simple_select_arg(criteria,table),

654

**{'order_by':make_order_by(sort_by,table)}

655

).execute().fetchall()

656

657

def fetch_one (self, table, **criteria):

658

"""Fetch one item from table and arguments"""

659

return table.select(*make_simple_select_arg(criteria,table)).execute().fetchone()

660

661

def fetch_count (self, table, column, sort_by=[],**criteria):

662

"""Return a counted view of the table, with the count stored in the property 'count'"""

663

result = sqlalchemy.select(

664

[sqlalchemy.func.count(getattr(table.c,column)).label('count'),

665

getattr(table.c,column)],

666

*make_simple_select_arg(criteria,table),

667

**{'group_by':column,

668

'order_by':make_order_by(sort_by,table,count_by=column),

669

}

670

).execute().fetchall()

671

return result

672

673

def fetch_len (self, table, **criteria):

674

"""Return the number of rows in table that match criteria

675

"""

676

if criteria:

677

return table.count(*make_simple_select_arg(criteria,table)).execute().fetchone()[0]

678

else:

679

return table.count().execute().fetchone()[0]

680

681

def fetch_join (self, table1, table2, col1, col2,

682

column_names=None, sort_by=[], **criteria):

683

if column_names:

684

raise 'column_names KWARG NO LONGER SUPPORTED BY fetch_join!'

685

return table1.join(table2,getattr(table1.c,col1)==getattr(table2.c,col2)).select(

686

*make_simple_select_arg(criteria,table1,table2)

687

).execute().fetchall()

688

689

def fetch_food_groups_for_search (self, words):

690

"""Return food groups that match a given set of words."""

691

where_statement = or_(

692

*[self.nutrition_table.c.desc.like('%%%s%%'%w.lower())

693

for w in words]

694

)

695

return [r[0] for r in sqlalchemy.select(

696

[self.nutrition_table.c.foodgroup],

697

where_statement,

698

distinct=True).execute().fetchall()]

699

700

def search_nutrition (self, words, group=None):

701

"""Search nutritional information for ingredient keys."""

702

where_statement = and_(

703

*[self.nutrition_table.c.desc.like('%%%s%%'%w)

704

for w in words])

705

if group:

706

where_statement = and_(self.nutrition_table.c.foodgroup==group,

707

where_statement)

708

return self.nutrition_table.select(where_statement).execute().fetchall()

709

710

def __get_joins (self, searches):

711

joins = []

712

for s in searches:

713

if type(s)==tuple:

714

joins.append(self.__get_joins(s[0]))

715

else:

716

if s['column'] == 'category':

717

if self.categories_table not in joins:

718

joins.append(self.categories_table,self.categories_table.c.id,

719

self.recipe_table.c.id)

720

elif s['column'] == 'ingredient':

721

if self.ingredients_table not in joins:

722

joins.append(self.ingredients_table)

723

return joins

724

725

def get_criteria (self,crit):

726

if type(crit)==tuple:

727

criteria,logic = crit

728

if logic=='and':

729

return and_(*[self.get_criteria(c) for c in criteria])

730

elif logic=='or':

731

return or_(*[self.get_criteria(c) for c in criteria])

732

elif type(crit)!=dict: raise TypeError

733

else:

734

#join_crit = None # if we need to add an extra arg for a join

735

if crit['column']=='category':

736

subtable = self.categories_table

737

col = subtable.c.category

738

elif crit['column'] in ['ingkey','item']:

739

subtable = self.ingredients_table

740

col = getattr(subtable.c,crit['column'])

741

elif crit['column']=='ingredient':

742

d1 = crit.copy(); d1.update({'column':'ingkey'})

743

d2 = crit.copy(); d2.update({'column':'item'}),

744

return self.get_criteria(([d1,d2],

745

'or'))

746

elif crit['column']=='anywhere':

747

searches = []

748

for column in ['ingkey','item','category','cuisine','title','instructions','modifications',

749

'source','link']:

750

d = crit.copy(); d.update({'column':column})

751

searches.append(d)

752

return self.get_criteria((searches,'or'))

753

else:

754

subtable = None

755

col = getattr(self.recipe_table.c,crit['column'])

756

if crit.get('operator','LIKE')=='LIKE':

757

retval = (col.like(crit['search']))

758

elif crit['operator']=='REGEXP':

759

retval = (col.op('REGEXP')(crit['search']))

760

else:

761

retval = (col==crit['search'])

762

if subtable:

763

retval = self.recipe_table.c.id.in_(

764

sqlalchemy.select([subtable.c.recipe_id],retval)

765

)

766

return retval

767

768

def search_recipes (self, searches, sort_by=[]):

769

"""Search recipes for columns of values.

770

771

"category" and "ingredient" are handled magically

772

773

sort_by is a list of tuples (column,1) [ASCENDING] or (column,-1) [DESCENDING]

774

"""

775

if 'rating' in [t[0] for t in sort_by]:

776

i = [t[0] for t in sort_by].index('rating')

777

d = (sort_by[i][1]==1 and -1 or 1)

778

sort_by[i] = ('rating',d)

779

criteria = self.get_criteria((searches,'and'))

780

if 'category' in [s[0] for s in sort_by]:

781

return sqlalchemy.select([c for c in self.recipe_table.c],# + [self.categories_table.c.category],

782

criteria,distinct=True,

783

from_obj=[sqlalchemy.outerjoin(self.recipe_table,self.categories_table)],

784

order_by=make_order_by(sort_by,self.recipe_table,

785

join_tables=[self.categories_table])

786

).execute().fetchall()

787

else:

788

return sqlalchemy.select([self.recipe_table],criteria,distinct=True,

789

order_by=make_order_by(sort_by,self.recipe_table,),

790

).execute().fetchall()

791

792

def filter (self, table, func):

793

"""Return a table representing filtered with func.

794

795

func is called with each row of the table.

796

"""

797

raise NotImplementedError

798

799

def get_unique_values (self, colname,table=None,**criteria):

800

"""Get list of unique values for column in table."""

801

if table is None: table=self.recipe_table

802

if criteria: table = table.select(*make_simple_select_arg(criteria,table))

803

if colname=='category' and table==self.recipe_table:

804

print 'WARNING: you are using a hack to access category values.'

805

table=self.categories_table

806

retval = [r[0] for

807

r in sqlalchemy.select([getattr(table.c,colname)],distinct=True).execute().fetchall()

808

]

809

return filter(lambda x: x is not None, retval) # Don't return null values

810

811

def get_ingkeys_with_count (self, search={}):

812

"""Get unique list of ingredient keys and counts for number of times they appear in the database.

813

"""

814

if search:

815

col = getattr(self.ingredients_table.c,search['column'])

816

operator = search.get('operator','LIKE')

817

if operator=='LIKE':

818

criteria = col.like(search['search'])

819

elif operator=='REGEXP':

820

criteria = col.op('REGEXP')(search['search'])

821

else:

822

criteria = col==crit['search']

823

else:

824

criteria = []

825

result = sqlalchemy.select(

826

[sqlalchemy.func.count(self.ingredients_table.c.ingkey).label('count'),

827

self.ingredients_table.c.ingkey],

828

criteria,

829

**{'group_by':'ingkey',

830

'order_by':make_order_by([],self.ingredients_table,count_by='ingkey'),

831

}

832

).execute().fetchall()

833

return result

834

return self.fetch_count(self.ingredients_table,'ingkey',)

835

s = sqlalchemy.select([

836

self.ingredients_table.c.ingkey,

837

func.count(self.ingredients_table.c.ingkey).label('count')

838

]).execute()

839

return s.fetchall()

840

841

def delete_by_criteria (self, table, criteria):

842

"""Table is our table.

843

Criteria is a dictionary of criteria to delete by.

844

"""

845

criteria = fix_colnames(criteria,table)

846

delete_args = []

847

for k,v in criteria.items():

848

delete_args.append(k==v)

849

if len(delete_args) > 1:

850

delete_args = [and_(*delete_args)]

851

table.delete(*delete_args).execute()

852

853

def update_by_criteria (self, table, update_criteria, new_values_dic):

854

try:

855

to_del = []

856

for k in new_values_dic:

857

if type(k) != str:

858

to_del.append(k)

859

for k in to_del:

860

v = new_values_dic[k]

861

del new_values_dic[k]

862

new_values_dic[str(k)] = v

863

table.update(*make_simple_select_arg(update_criteria,table)).execute(**new_values_dic)

864

except:

865

print 'update_by_criteria error...'

866

print 'table:',table

867

print 'UPDATE_CRITERIA:'

868

for k,v in update_criteria.items(): print '','KEY:',k,'VAL:',v

869

print 'NEW_VALUES_DIC:'

870

for k,v in new_values_dic.items(): print '','KEY:',k,type(k),'VAL:',v

871

raise

872

873

def add_column_to_table (self, table, column_spec):

874

"""table is a table, column_spec is a tuple defining the

875

column, following the format for new tables.

876

"""

877

name = table.name; new_col = column_spec[0]; coltyp = column_spec[1]

878

if hasattr(coltyp ,'dialect_impl'):

879

coltyp = coltyp.dialect_impl(self.db.dialect).get_col_spec()

880

sql = 'ALTER TABLE %(name)s ADD %(new_col)s %(coltyp)s;'%locals()

881

try:

882

self.db.execute(sql)

883

except:

884

print 'FAILED TO EXECUTE',sql

885

print 'Ignoring error in add_column_to_table'

886

import traceback; traceback.print_exc()

887

888

def alter_table (self, table_name, setup_function, cols_to_change={}, cols_to_keep=[]):

889

"""Change table, moving some columns.

890

891

table is the table object. table_name is the table

892

name. setup_function is a function that will setup our correct

893

table. cols_to_change is a list of columns that are changing

894

names (key=orig, val=new). cols_to_keep is a list of columns

895

that should be copied over as is.

896

897

This works by renaming our table to a temporary name, then

898

recreating our initial table. Finally, we copy over table

899

data and then delete our temporary table (i.e. our old table)

900

901

This is much less efficient than an alter table command, but

902

will allow us to e.g. change/add primary key columns to sqlite

903

tables

904

"""

905

print 'Attempting to alter ',table_name,setup_function,cols_to_change,cols_to_keep

906

try:

907

self.db.execute('ALTER TABLE %(t)s RENAME TO %(t)s_temp'%{'t':table_name})

908

except:

909

do_raise = True

910

import traceback; traceback.print_exc()

911

try:

912

self.db.execute('DROP TABLE %(t)s_temp'%{'t':table_name})

913

except:

914

1

915

else:

916

do_raise = False

917

self.db.execute('ALTER TABLE %(t)s RENAME TO %(t)s_temp'%{'t':table_name})

918

if do_raise:

919

raise

920

del self.metadata.tables[table_name]

921

setup_function()

922

getattr(self,'%s_table'%table_name).create()

923

TO_COLS = cols_to_keep[:]

924

FROM_COLS = cols_to_keep[:]

925

for fro,to_ in cols_to_change.items():

926

FROM_COLS.append(fro)

927

TO_COLS.append(to_)

928

stmt = '''INSERT INTO %(t)s (%(to_cols)s)

929

SELECT %(from_cols)s FROM %(t)s_temp

930

'''%{'t':table_name,

931

'from_cols':', '.join(FROM_COLS),

932

'to_cols':', '.join(TO_COLS),

933

}

934

self.db.execute(stmt)

935

self.db.execute('DROP TABLE %s_temp'%table_name)

936

937

# Metakit has no AUTOINCREMENT, so it has to do special magic here

938

def increment_field (self, table, field):

939

"""Increment field in table, or return None if the DB will do

940

this automatically.

941

"""

942

return None

943

944

945

def row_equal (self, r1, r2):

946

"""Test whether two row references are the same.

947

948

Return True if r1 and r2 reference the same row in the database.

949

"""

950

return r1==r2

951

952

def find_duplicates (self, by='recipe',recipes=None, include_deleted=True):

953

"""Find all duplicate recipes by recipe or ingredient.

954

955

Returns a nested list of IDs, where each nested list is a list

956

of duplicates.

957

958

This uses the recipe_hash and ingredient_hash respectively.

959

To find only those recipes that have both duplicate recipe and

960

ingredient hashes, use find_all_duplicates

961

"""

962

if by=='recipe':

963

col = self.recipe_table.c.recipe_hash

964

elif by=='ingredient':

965

col = self.recipe_table.c.ingredient_hash

966

args = []

967

if not include_deleted: args.append(self.recipe_table.c.deleted==False)

968

kwargs = dict(having=sqlalchemy.func.count(col)>1,

969

group_by=col)

970

duped_hashes = sqlalchemy.select([col],

971

*args,

972

**kwargs)

973

query = sqlalchemy.select([self.recipe_table.c.id,col],

974

include_deleted and col.in_(duped_hashes) or and_(col.in_(duped_hashes),

975

self.recipe_table.c.deleted==False),

976

order_by=col).execute()

977

recs_by_hash = {}

978

for result in query.fetchall():

979

rec_id = result[0]; hsh = result[1]

980

if not recs_by_hash.has_key(hsh):

981

recs_by_hash[hsh] = []

982

recs_by_hash[hsh].append(rec_id)

983

results = recs_by_hash.values()

984

if recipes:

985

rec_ids = [r.id for r in recipes]

986

results = filter(lambda reclist: True in [(rid in rec_ids) for rid in reclist], results)

987

return results

988

989

def find_complete_duplicates (self, recipes=None, include_deleted=True):

990

"""Find all duplicate recipes (by recipe_hash and ingredient_hash)."""

991

args = []

992

if not include_deleted: args.append(self.recipe_table.c.deleted==False)

993

994

ing_hashes,rec_hashes = [sqlalchemy.select([col],

995

*args,

996

**dict(having=sqlalchemy.func.count(col)>1,

997

group_by=col)

998

) for col in [self.recipe_table.c.ingredient_hash,

999

self.recipe_table.c.recipe_hash]

1000

]

1001

if not include_deleted: select_statements = [self.recipe_table.c.deleted==False]

1002

else: select_statements = []

1003

select_statements.append(self.recipe_table.c.ingredient_hash.in_(ing_hashes))

1004

select_statements.append(self.recipe_table.c.recipe_hash.in_(rec_hashes))

1005

1006

query = sqlalchemy.select([self.recipe_table.c.id,

1007

self.recipe_table.c.recipe_hash,

1008

self.recipe_table.c.ingredient_hash],

1009

and_(*select_statements),

1010

order_by=[self.recipe_table.c.recipe_hash,

1011

self.recipe_table.c.ingredient_hash]).execute()

1012

recs_by_hash = {}

1013

for result in query.fetchall():

1014

rec_id = result[0]; rhsh = result[1]; ihsh = result[2]

1015

if not recs_by_hash.has_key((rhsh,ihsh)):

1016

recs_by_hash[(rhsh,ihsh)] = []

1017

recs_by_hash[(rhsh,ihsh)].append(rec_id)

1018

results = recs_by_hash.values()

1019

if recipes:

1020

rec_ids = [r.id for r in recipes]

1021

results = filter(lambda reclist: True in [(rid in rec_ids) for rid in reclist], results)

1022

return results

1023

1024

# convenience DB access functions for working with ingredients,

1025

# recipes, etc.

1026

1027

def delete_ing (self, ing):

1028

"""Delete ingredient permanently."""

1029

self.delete_by_criteria(self.ingredients_table,

1030

{'id':ing.id})

1031

1032

def modify_rec (self, rec, dic):

1033

"""Modify recipe based on attributes/values in dictionary.

1034

1035

Return modified recipe.

1036

"""

1037

self.validate_recdic(dic)

1038

debug('validating dictionary',3)

1039

if dic.has_key('category'):

1040

newcats = dic['category'].split(', ')

1041

newcats = filter(lambda x: x, newcats) # Make sure our categories are not blank

1042

curcats = self.get_cats(rec)

1043

for c in curcats:

1044

if c not in newcats:

1045

self.delete_by_criteria(self.categories_table,{'recipe_id':rec.id,'category':c})

1046

for c in newcats:

1047

if c not in curcats:

1048

self.do_add_cat({'recipe_id':rec.id,'category':c})

1049

del dic['category']

1050

debug('do modify rec',3)

1051

retval = self.do_modify_rec(rec,dic)

1052

self.update_hashes(rec)

1053

return retval

1054

1055

def validate_recdic (self, recdic):

1056

if not recdic.has_key('last_modified'):

1057

recdic['last_modified']=time.time()

1058

if recdic.has_key('image') and not recdic.has_key('thumb'):

1059

# if we have an image but no thumbnail, we want to create the thumbnail.

1060

try:

1061

img = ImageExtras.get_image_from_string(recdic['image'])

1062

thumb = ImageExtras.resize_image(img,40,40)

1063

ofi = StringIO.StringIO()

1064

thumb.save(ofi,'JPEG')

1065

recdic['thumb']=ofi.getvalue()

1066

ofi.close()

1067

except:

1068

del recdic['image']

1069

print """Warning: gourmet couldn't recognize the image.

1070

1071

Proceding anyway, but here's the traceback should you

1072

wish to investigate.

1073

"""

1074

import traceback

1075

traceback.print_stack()

1076

for k,v in recdic.items():

1077

try:

1078

recdic[k]=unicode(v.strip())

1079

except:

1080

pass

1081

1082

def modify_ings (self, ings, ingdict):

1083

# allow for the possibility of doing a smarter job changing

1084

# something for a whole bunch of ingredients...

1085

for i in ings: self.modify_ing(i,ingdict)

1086

1087

def modify_ing_and_update_keydic (self, ing, ingdict):

1088

"""Update our key dictionary and modify our dictionary.

1089

1090

This is a separate method from modify_ing because we only do

1091

this for hand-entered data, not for mass imports.

1092

"""

1093

# If our ingredient has changed, update our keydic...

1094

if ing.item!=ingdict.get('item',ing.item) or ing.ingkey!=ingdict.get('ingkey',ing.ingkey):

1095

if ing.item and ing.ingkey:

1096

self.remove_ing_from_keydic(ing.item,ing.ingkey)

1097

self.add_ing_to_keydic(

1098

ingdict.get('item',ing.item),

1099

ingdict.get('ingkey',ing.ingkey)

1100

)

1101

return self.modify_ing(ing,ingdict)

1102

1103

def update_hashes (self, rec):

1104

rhash,ihash = recipeIdentifier.hash_recipe(rec,self)

1105

self.do_modify_rec(rec,{'recipe_hash':rhash,'ingredient_hash':ihash})

1106

1107

def find_duplicates_of_rec (self, rec, match_ingredient=True, match_recipe=True):

1108

"""Return recipes that appear to be duplicates"""

1109

if match_ingredient and match_recipe:

1110

perfect_matches = self.fetch_all(ingredient_hash=rec.ingredient_hash,recipe_hash=rec.recipe_hash)

1111

elif match_ingredient:

1112

perfect_matches = self.fetch_all(ingredient_hash=rec.ingredient_hash)

1113

else:

1114

perfect_matches = self.fetch_all(recipe_hash=rec.recipe_hash)

1115

matches = []

1116

if len(perfect_matches) == 1:

1117

return []

1118

else:

1119

for r in perfect_matches:

1120

if r.id != rec.id:

1121

matches.append(r)

1122

return matches

1123

1124

def find_all_duplicates (self):

1125

"""Return a list of sets of duplicate recipes."""

1126

raise NotImplementedError

1127

1128

def merge_mergeable_duplicates (self):

1129

"""Merge all duplicates for which a simple merge is possible.

1130

For those recipes which can't be merged, return:

1131

[recipe-id-list,to-merge-dic,diff-dic]

1132

"""

1133

dups = self.find_all_duplicates()

1134

unmerged = []

1135

for recs in dups:

1136

rec_objs = [self.fetch_one(self.recipe_table,id=r) for r in recs]

1137

merge_dic,diffs = recipeIdentifier.merge_recipes(self,rec_objs)

1138

if not diffs:

1139

if merge_dic:

1140

self.modify_rec(rec_objs[0],merge_dic)

1141

for r in rec_objs[1:]: self.delete_rec(r)

1142

else:

1143

unmerged.append([recs,merge_dic,diffs])

1144

return unmerged

1145

1146

def modify_ing (self, ing, ingdict):

1147

self.validate_ingdic(ingdict)

1148

return self.do_modify_ing(ing,ingdict)

1149

1150

def add_rec (self, dic, accept_ids=False):

1151

"""Dictionary is a dictionary of column values for our recipe.

1152

Return the ID of the newly created recipe.

1153

1154

If accept_ids is True, we accept recipes with IDs already

1155

set. These IDs need to have been reserved with the new_id()

1156

method.

1157

"""

1158

cats = []

1159

if dic.has_key('category'):

1160

cats = dic['category'].split(', ')

1161

del dic['category']

1162

if dic.has_key('servings'):

1163

dic['servings'] = float(dic['servings'])

1164

if not dic.has_key('deleted'): dic['deleted']=False

1165

self.validate_recdic(dic)

1166

try:

1167

ret = self.do_add_rec(dic)

1168

except:

1169

print 'Problem adding recipe with dictionary...'

1170

for k,v in dic.items(): print 'KEY:',k,'of type',type(k),'VALUE:',v,'of type',type(v)

1171

raise

1172

else:

1173

if type(ret)==int:

1174

ID = ret

1175

ret = self.get_rec(ID)

1176

else:

1177

ID = ret.id

1178

for c in cats:

1179

if c: self.do_add_cat({'recipe_id':ID,'category':c})

1180

self.update_hashes(ret)

1181

return ret

1182

1183

def add_ing_and_update_keydic (self, dic):

1184

if dic.has_key('item') and dic.has_key('ingkey') and dic['item'] and dic['ingkey']:

1185

self.add_ing_to_keydic(dic['item'],dic['ingkey'])

1186

return self.add_ing(dic)

1187

1188

def add_ing (self, dic):

1189

self.validate_ingdic(dic)

1190

try:

1191

return self.do_add_ing(dic)

1192

except:

1193

print 'Problem adding',dic

1194

raise

1195

1196

def add_ings (self, dics):

1197

"""Add multiple ingredient dictionaries at a time."""

1198

for d in dics:

1199

self.validate_ingdic(d)

1200

for k in ['refid','unit','amount','rangeamount','item','ingkey','optional','shopoptional','inggroup','position']:

1201

if not k in d:

1202

d[k] = None

1203

try:

1204

# Warning: this method relies on all the dictionaries

1205

# looking identical. validate_ingdic should be taking care

1206

# of this for us now, but if parameters change in the

1207

# future, this rather subtle bug could well rear its ugly

1208

# head again.

1209

rp = self.ingredients_table.insert().execute(*dics)

1210

except ValueError:

1211

for d in dics: self.coerce_types(self.ingredients_table,d)

1212

self.ingredients_table.insert().execute(*dics)

1213

1214

# Lower level DB access functions -- hopefully subclasses can

1215

# stick to implementing these

1216

1217

def coerce_types (self, table, dic):

1218

"""Modify dic to make sure types are correct for table.

1219

"""

1220

type_to_pytype = {Float:float,

1221

Integer:int,

1222

String:str,

1223

Boolean:bool,

1224

Numeric:float,

1225

}

1226

for k,v in dic.copy().items():

1227

column_obj = getattr(table.c,k)

1228

if column_obj.type.__class__ in type_to_pytype:

1229

try:

1230

v = type_to_pytype[column_obj.type.__class__](v)

1231

except:

1232

v = None

1233

if dic[k] != v:

1234

dic[k] = v

1235

1236

def commit_fast_adds (self):

1237

if hasattr(self,'extra_connection'):

1238

self.extra_connection.commit()

1239

1240

def do_add_fast (self, table, dic):

1241

'''Add fast -- return None'''

1242

if not hasattr(self,'extra_connection'):

1243

self.extra_connection = self.db.connect().connection

1244

try:

1245

tname = table.name

1246

SQL = 'INSERT INTO ' + tname + '('+', '.join(dic.keys()) + ')'

1247

SQL += ' VALUES (' + ", ".join(['?']*len(dic)) + ')'

1248

self.extra_connection.execute(SQL,dic.values())

1249

except:

1250

return self.do_add(table,dic)

1251

1252

def do_add (self, table, dic):

1253

insert_statement = table.insert()

1254

self._force_unicode(dic)

1255

try:

1256

result_proxy = insert_statement.execute(**dic)

1257

except ValueError:

1258

print 'Had to coerce types',table,dic

1259

self.coerce_types(table,dic)

1260

result_proxy = insert_statement.execute(**dic)

1261

return result_proxy

1262

1263

def do_add_and_return_item (self, table, dic, id_prop='id'):

1264

result_proxy = self.do_add(table,dic)

1265

select = table.select(getattr(table.c,id_prop)==result_proxy.lastrowid)

1266

return select.execute().fetchone()

1267

1268

def do_add_ing (self,dic):

1269

return self.do_add_and_return_item(self.ingredients_table,dic,id_prop='id')

1270

1271

def do_add_cat (self, dic):

1272

return self.do_add_and_return_item(self.categories_table,dic)

1273

1274

def do_add_rec (self, rdict):

1275

"""Add a recipe based on a dictionary of properties and values."""

1276

self.changed=True

1277

if not rdict.has_key('deleted'):

1278

rdict['deleted']=0

1279

if rdict.has_key('id'):

1280

# If our dictionary has an id, then we assume we are a

1281

# reserved ID

1282

if rdict['id'] in self.new_ids:

1283

rid = rdict['id']; del rdict['id']

1284

self.new_ids.remove(rid)

1285

self.update_by_criteria(self.recipe_table,

1286

{'id':rid},

1287

rdict)

1288

return self.recipe_table.select(self.recipe_table.c.id==rid).execute().fetchone()

1289

else:

1290

raise ValueError('New recipe created with preset id %s, but ID is not in our list of new_ids'%rdict['id'])

1291

insert_statement = self.recipe_table.insert()

1292

select = self.recipe_table.select(self.recipe_table.c.id==insert_statement.execute(**rdict).lastrowid)

1293

return select.execute().fetchone()

1294

1295

def validate_ingdic (self,dic):

1296

"""Do any necessary validation and modification of ingredient dictionaries."""

1297

if not dic.has_key('deleted'): dic['deleted']=False

1298

self._force_unicode(dic)

1299

1300

def _force_unicode (self, dic):

1301

for k,v in dic.items():

1302

if type(v)==str:

1303

# force unicode...

1304

dic[k]=unicode(v)

1305

1306

def do_modify_rec (self, rec, dic):

1307

"""This is what other DBs should subclass."""

1308

return self.do_modify(self.recipe_table,rec,dic)

1309

1310

def do_modify_ing (self, ing, ingdict):

1311

"""modify ing based on dictionary of properties and new values."""

1312

return self.do_modify(self.ingredients_table,ing,ingdict)

1313

1314

def do_modify (self, table, row, d, id_col='id'):

1315

if id_col:

1316

try:

1317

qr = table.update(getattr(table.c,id_col)==getattr(row,id_col)).execute(**d)

1318

except:

1319

print 'do_modify failed with args'

1320

print 'table=',table,'row=',row

1321

print 'd=',d,'id_col=',id_col

1322

raise

1323

select = table.select(getattr(table.c,id_col)==getattr(row,id_col))

1324

else:

1325

qr = table.update().execute(**d)

1326

select = table.select()

1327

return select.execute().fetchone()

1328

1329

def get_ings (self, rec):

1330

"""Handed rec, return a list of ingredients.

1331

1332

rec should be an ID or an object with an attribute ID)"""

1333

if hasattr(rec,'id'):

1334

id=rec.id

1335

else:

1336

id=rec

1337

return self.fetch_all(self.ingredients_table,recipe_id=id,deleted=False)

1338

1339

def get_cats (self, rec):

1340

svw = self.fetch_all(self.categories_table,recipe_id=rec.id)

1341

cats = [c.category or '' for c in svw]

1342

# hackery...

1343

while '' in cats:

1344

cats.remove('')

1345

return cats

1346

1347

def get_referenced_rec (self, ing):

1348

"""Get recipe referenced by ingredient object."""

1349

if hasattr(ing,'refid') and ing.refid:

1350

rec = self.get_rec(ing.refid)

1351

if rec: return rec

1352

# otherwise, our reference is no use! Something's been

1353

# foobared. Unfortunately, this does happen, so rather than

1354

# screwing our user, let's try to look based on title/item

1355

# name (the name of the ingredient *should* be the title of

1356

# the recipe, though the user could change this)

1357

if hasattr(ing,'item'):

1358

rec = self.fetch_one(self.recipe_table,**{'title':ing.item})

1359

if rec:

1360

self.modify_ing(ing,{'refid':rec.id})

1361

return rec

1362

else:

1363

print 'Very odd: no match for',ing,'refid:',ing.refid

1364

1365

def include_linked_recipes (self, recs):

1366

'''Handed a list of recipes, append any recipes that are

1367

linked as ingredients in those recipes to the list.

1368

1369

Modifies the list in place.

1370

'''

1371

import sqlalchemy

1372

ids = [r.id for r in recs]

1373

extra_ings = self.ingredients_table.select(and_(

1374

self.ingredients_table.c.refid,

1375

self.ingredients_table.c.recipe_id.in_(ids)

1376

)

1377

).execute().fetchall()

1378

for i in extra_ings:

1379

if i.refid not in ids:

1380

recs.append(self.get_referenced_rec(i))

1381

1382

def get_rec (self, id, recipe_table=None):

1383

"""Handed an ID, return a recipe object."""

1384

if recipe_table:

1385

print 'handing get_rec an recipe_table is deprecated'

1386

print 'Ignoring recipe_table handed to get_rec'

1387

recipe_table=self.recipe_table

1388

return self.fetch_one(self.recipe_table, id=id)

1389

1390

def delete_rec (self, rec):

1391

"""Delete recipe object rec from our database."""

1392

if type(rec)!=int: rec=rec.id

1393

debug('deleting recipe ID %s'%rec,0)

1394

self.delete_by_criteria(self.recipe_table,{'id':rec})

1395

self.delete_by_criteria(self.categories_table,{'recipe_id':rec})

1396

self.delete_by_criteria(self.ingredients_table,{'recipe_id':rec})

1397

debug('deleted recipe ID %s'%rec,0)

1398

1399

def new_rec (self):

1400

"""Create and return a new, empty recipe"""

1401

blankdict = {'title':_('New Recipe'),

1402

#'servings':'4'}

1403

}

1404

return self.add_rec(blankdict)

1405

1406

def new_id (self):

1407

#raise NotImplementedError("WARNING: NEW_ID IS NO LONGER FUNCTIONAL, FIND A NEW WAY AROUND THE PROBLEM")

1408

#rec = self.new_rec()

1409

rec = self.do_add_rec({'deleted':1})

1410

self.new_ids.append(rec.id)

1411

return rec.id

1412

1413

# Convenience functions for dealing with ingredients

1414

1415

def order_ings (self, ings):

1416

"""Handed a view of ingredients, we return an alist:

1417

[['group'|None ['ingredient1', 'ingredient2', ...]], ... ]

1418

"""

1419

defaultn = 0

1420

groups = {}

1421

group_order = {}

1422

n = 0; group = 0

1423

for i in ings:

1424

# defaults

1425

if not hasattr(i,'inggroup'):

1426

group = None

1427

else:

1428

group=i.inggroup

1429

if group == None:

1430

group = n; n+=1

1431

if not hasattr(i,'position'):

1432

print 'Bad: ingredient without position',i

1433

i.position=defaultn

1434

defaultn += 1

1435

if groups.has_key(group):

1436

groups[group].append(i)

1437

# the position of the group is the smallest position of its members

1438

# in other words, positions pay no attention to groups really.

1439

if i.position < group_order[group]: group_order[group]=i.position

1440

else:

1441

groups[group]=[i]

1442

group_order[group]=i.position

1443

# now we just have to sort an i-listify

1444

def sort_groups (x,y):

1445

if group_order[x[0]] > group_order[y[0]]: return 1

1446

elif group_order[x[0]] == group_order[y[0]]: return 0

1447

else: return -1

1448

alist=groups.items()

1449

alist.sort(sort_groups)

1450

def sort_ings (x,y):

1451

if x.position > y.position: return 1

1452

elif x.position == y.position: return 0

1453

else: return -1

1454

for g,lst in alist:

1455

lst.sort(sort_ings)

1456

final_alist = []

1457

last_g = -1

1458

for g,ii in alist:

1459

if type(g)==int:

1460

if last_g == None:

1461

final_alist[-1][1].extend(ii)

1462

else:

1463

final_alist.append([None,ii])

1464

last_g = None

1465

else:

1466

final_alist.append([g,ii])

1467

last_g = g

1468

return final_alist

1469

1470

def replace_ings (self, ingdicts):

1471

"""Add a new ingredients and remove old ingredient list."""

1472

## we assume (hope!) all ingdicts are for the same ID

1473

id=ingdicts[0]['id']

1474

debug("Deleting ingredients for recipe with ID %s"%id,1)

1475

self.delete_by_criteria(self.ingredients_table,{'id':id})

1476

for ingd in ingdicts:

1477

self.add_ing(ingd)

1478

1479

def ingview_to_lst (self, view):

1480

"""Handed a view of ingredient data, we output a useful list.

1481

The data we hand out consists of a list of tuples. Each tuple contains

1482

amt, unit, key, alternative?"""

1483

ret = []

1484

for i in view:

1485

ret.append([self.get_amount(i), i.unit, i.ingkey,])

1486

return ret

1487

1488

def get_amount (self, ing, mult=1):

1489

"""Given an ingredient object, return the amount for it.

1490

1491

Amount may be a tuple if the amount is a range, a float if

1492

there is a single amount, or None"""

1493

amt=getattr(ing,'amount')

1494

try:

1495

ramt = getattr(ing,'rangeamount')

1496

except:

1497

# this blanket exception is here for our lovely upgrade

1498

# which requires a working export with an out-of-date DB

1499

ramt = None

1500

if mult != 1:

1501

if amt: amt = amt * mult

1502

if ramt: ramt = ramt * mult

1503

if ramt:

1504

return (amt,ramt)

1505

else:

1506

return amt

1507

1508

@pluggable_method

1509

def get_amount_and_unit (self, ing, mult=1, conv=None, fractions=None, adjust_units=False,

1510

favor_current_unit=True,preferred_unit_groups=[]):

1511

"""Return a tuple of strings representing our amount and unit.

1512

1513

If we are handed a converter interface, we will adjust the

1514

units to make them readable.

1515

"""

1516

amt = self.get_amount(ing,mult)

1517

unit = ing.unit

1518

ramount = None

1519

if type(amt)==tuple: amt,ramount = amt

1520

if adjust_units or preferred_unit_groups:

1521

if not conv:

1522

conv = convert.get_converter()

1523

amt,unit = conv.adjust_unit(amt,unit,

1524

favor_current_unit=favor_current_unit,

1525

preferred_unit_groups=preferred_unit_groups)

1526

if ramount and unit != ing.unit:

1527

# if we're changing units... convert the upper range too

1528

ramount = ramount * conv.converter(ing.unit, unit)

1529

if ramount: amt = (amt,ramount)

1530

return (self._format_amount_string_from_amount(amt,fractions=fractions,unit=unit),unit)

1531

1532

def get_amount_as_string (self,

1533

ing,

1534

mult=1,

1535

fractions=None,

1536

):

1537

"""Return a string representing our amount.

1538

If we have a multiplier, multiply the amount before returning it.

1539

"""

1540

amt = self.get_amount(ing,mult)

1541

return self._format_amount_string_from_amount(amt, fractions=fractions)

1542

1543

def _format_amount_string_from_amount (self, amt, fractions=None, unit=None):

1544

"""Format our amount string given an amount tuple.

1545

1546

If fractions is None, we use the default setting from

1547

convert.USE_FRACTIONS. Otherwise, we will override that

1548

setting.

1549

1550

If you're thinking of using this function from outside, you

1551

should probably just use a convenience function like

1552

get_amount_as_string or get_amount_and_unit

1553

"""

1554

if fractions is None:

1555

# None means use the default value

1556

fractions = convert.USE_FRACTIONS

1557

if unit:

1558

approx = defaults.unit_rounding_guide.get(unit,0.01)

1559

else:

1560

approx = 0.01

1561

if type(amt)==tuple:

1562

return "%s-%s"%(convert.float_to_frac(amt[0],fractions=fractions,approx=approx).strip(),

1563

convert.float_to_frac(amt[1],fractions=fractions,approx=approx).strip())

1564

elif type(amt) in (float,int):

1565

return convert.float_to_frac(amt,fractions=fractions,approx=approx)

1566

else: return ""

1567

1568

def get_amount_as_float (self, ing, mode=1): #1 == self.AMT_MODE_AVERAGE

1569

"""Return a float representing our amount.

1570

1571

If we have a range for amount, this function will ignore the range and simply

1572

return a number. 'mode' specifies how we deal with the mode:

1573

self.AMT_MODE_AVERAGE means we average the mode (our default behavior)

1574

self.AMT_MODE_LOW means we use the low number.

1575

self.AMT_MODE_HIGH means we take the high number.

1576

"""

1577

amt = self.get_amount(ing)

1578

if type(amt) in [float, int, type(None)]:

1579

return amt

1580

else:

1581

# otherwise we do our magic

1582

amt=list(amt)

1583

amt.sort() # make sure these are in order

1584

low,high=amt

1585

if mode==self.AMT_MODE_AVERAGE: return (low+high)/2.0

1586

elif mode==self.AMT_MODE_LOW: return low

1587

elif mode==self.AMT_MODE_HIGH: return high # mode==self.AMT_MODE_HIGH

1588

else:

1589

raise ValueError("%s is an invalid value for mode"%mode)

1590

1591

@pluggable_method

1592

def add_ing_to_keydic (self, item, key):

1593

#print 'add ',item,key,'to keydic'

1594

# Make sure we have unicode...

1595

if type(item)==str: item = unicode(item)

1596

if type(key)==str: key = unicode(key)

1597

if not item or not key: return

1598

else:

1599

if item: item = unicode(item)

1600

if key: key = unicode(key)

1601

row = self.fetch_one(self.keylookup_table, item=item, ingkey=key)

1602

if row:

1603

self.do_modify(self.keylookup_table,row,{'count':row.count+1})

1604

else:

1605

self.do_add(self.keylookup_table,{'item':item,'ingkey':key,'count':1})

1606

# The below code should move to a plugin for users who care about ingkeys...

1607

for w in item.split():

1608

w=str(w.decode('utf8').lower())

1609

row = self.fetch_one(self.keylookup_table,word=unicode(w),ingkey=unicode(key))

1610

if row:

1611

self.do_modify(self.keylookup_table,row,{'count':row.count+1})

1612

else:

1613

self.do_add(self.keylookup_table,{'word':unicode(w),'ingkey':unicode(key),'count':1})

1614

1615

def remove_ing_from_keydic (self, item, key):

1616

#print 'remove ',item,key,'to keydic'

1617

row = self.fetch_one(self.keylookup_table,item=item,ingkey=key)

1618

if row:

1619

new_count = row.count - 1

1620

if new_count:

1621

self.do_modify(self.keylookup_table,row,{'count':new_count})

1622

else:

1623

self.delete_by_criteria(self.keylookup_table,{'item':item,'ingkey':key})

1624

for w in item.split():

1625

w=str(w.decode('utf8').lower())

1626

row = self.fetch_one(self.keylookup_table,item=item,ingkey=key)

1627

if row:

1628

new_count = row.count - 1

1629

if new_count:

1630

self.do_modify(self.keylookup_table,row,{'count':new_count})

1631

else:

1632

self.delete_by_criteria(self.keylookup_table,{'word':w,'ingkey':key})

1633

1634

def ing_shopper (self, view):

1635

return DatabaseShopper(self.ingview_to_lst(view))

1636

1637

# functions to undoably modify tables

1638

1639

def get_dict_for_obj (self, obj, keys):

1640

orig_dic = {}

1641

for k in keys:

1642

if k=='category':

1643

v = ", ".join(self.get_cats(obj))

1644

else:

1645

v=getattr(obj,k)

1646

orig_dic[k]=v

1647

return orig_dic

1648

1649

def undoable_modify_rec (self, rec, dic, history=[], get_current_rec_method=None,

1650

select_change_method=None):

1651

"""Modify our recipe and remember how to undo our modification using history."""

1652

orig_dic = self.get_dict_for_obj(rec,dic.keys())

1653

reundo_name = "Re_apply"

1654

reapply_name = "Re_apply "

1655

reundo_name += string.join(["%s <i>%s</i>"%(k,v) for k,v in orig_dic.items()])

1656

reapply_name += string.join(["%s <i>%s</i>"%(k,v) for k,v in dic.items()])

1657

redo,reundo=None,None

1658

if get_current_rec_method:

1659

def redo (*args):

1660

r=get_current_rec_method()

1661

odic = self.get_dict_for_obj(r,dic.keys())

1662

return ([r,dic],[r,odic])

1663

def reundo (*args):

1664

r = get_current_rec_method()

1665

odic = self.get_dict_for_obj(r,orig_dic.keys())

1666

return ([r,orig_dic],[r,odic])

1667

1668

def action (*args,**kwargs):

1669

"""Our actual action allows for selecting changes after modifying"""

1670

self.modify_rec(*args,**kwargs)

1671

if select_change_method:

1672

select_change_method(*args,**kwargs)

1673

1674

obj = Undo.UndoableObject(action,action,history,

1675

action_args=[rec,dic],undo_action_args=[rec,orig_dic],

1676

get_reapply_action_args=redo,

1677

get_reundo_action_args=reundo,

1678

reapply_name=reapply_name,

1679

reundo_name=reundo_name,)

1680

obj.perform()

1681

1682

def undoable_delete_recs (self, recs, history, make_visible=None):

1683

"""Delete recipes by setting their 'deleted' flag to True and add to UNDO history."""

1684

def do_delete ():

1685

for rec in recs:

1686

debug('rec %s deleted=True'%rec.id,1)

1687

self.modify_rec(rec,{'deleted':True})

1688

if make_visible: make_visible(recs)

1689

def undo_delete ():

1690

for rec in recs:

1691

debug('rec %s deleted=False'%rec.id,1)

1692

self.modify_rec(rec,{'deleted':False})

1693

if make_visible: make_visible(recs)

1694

obj = Undo.UndoableObject(do_delete,undo_delete,history)

1695

obj.perform()

1696

1697

def undoable_modify_ing (self, ing, dic, history, make_visible=None):

1698

"""modify ingredient object ing based on a dictionary of properties and new values.

1699

1700

history is our undo history to be handed to Undo.UndoableObject

1701

make_visible is a function that will make our change (or the undo or our change) visible.

1702

"""

1703

orig_dic = self.get_dict_for_obj(ing,dic.keys())

1704

key = dic.get('ingkey',None)

1705

item = key and dic.get('item',ing.item)

1706

def do_action ():

1707

debug('undoable_modify_ing modifying %s'%dic,2)

1708

self.modify_ing(ing,dic)

1709

if key:

1710

self.add_ing_to_keydic(item,key)

1711

if make_visible: make_visible(ing,dic)

1712

def undo_action ():

1713

debug('undoable_modify_ing unmodifying %s'%orig_dic,2)

1714

self.modify_ing(ing,orig_dic)

1715

if key:

1716

self.remove_ing_from_keydic(item,key)

1717

if make_visible: make_visible(ing,orig_dic)

1718

obj = Undo.UndoableObject(do_action,undo_action,history)

1719

obj.perform()

1720

1721

def undoable_delete_ings (self, ings, history, make_visible=None):

1722

"""Delete ingredients in list ings and add to our undo history."""

1723

def do_delete():

1724

modded_ings = [self.modify_ing(i,{'deleted':True}) for i in ings]

1725

if make_visible:

1726

make_visible(modded_ings)

1727

def undo_delete ():

1728

modded_ings = [self.modify_ing(i,{'deleted':False}) for i in ings]

1729

if make_visible: make_visible(modded_ings)

1730

obj = Undo.UndoableObject(do_delete,undo_delete,history)

1731

obj.perform()

1732

1733

def get_default_values (self, colname):

1734

try:

1735

return defaults.fields[colname]

1736

except:

1737

return []

1738

1739

1740

class RecipeManager (RecData):

1741

1742

def __init__ (self,*args,**kwargs):

1743

debug('recipeManager.__init__()',3)

1744

RecData.__init__(self,*args,**kwargs)

1745

#self.km = keymanager.KeyManager(rm=self)

1746

self.km = keymanager.get_keymanager(rm=self)

1747

1748

def key_search (self, ing):

1749

"""Handed a string, we search for keys that could match

1750

the ingredient."""

1751

result=self.km.look_for_key(ing)

1752

if type(result)==type(""):

1753

return [result]

1754

elif type(result)==type([]):

1755

# look_for contains an alist of sorts... we just want the first

1756

# item of every cell.

1757

if len(result)>0 and result[0][1]>0.8:

1758

return map(lambda a: a[0],result)

1759

else:

1760

## otherwise, we make a mad attempt to guess!

1761

k=self.km.generate_key(ing)

1762

l = [k]

1763

l.extend(map(lambda a: a[0],result))

1764

return l

1765

else:

1766

return None

1767

1768

def ingredient_parser (self, s, conv=None, get_key=True):

1769

"""Handed a string, we hand back a dictionary representing a parsed ingredient (sans recipe ID)"""

1770

debug('ingredient_parser handed: %s'%s,0)

1771

s = unicode(s) # convert to unicode so our ING MATCHER works properly

1772

s=s.strip("\n\t #*+-")

1773

debug('ingredient_parser handed: "%s"'%s,1)

1774

m=convert.ING_MATCHER.match(s)

1775

if m:

1776

debug('ingredient parser successfully parsed %s'%s,1)

1777

d={}

1778

a,u,i=(m.group(convert.ING_MATCHER_AMT_GROUP),

1779

m.group(convert.ING_MATCHER_UNIT_GROUP),

1780

m.group(convert.ING_MATCHER_ITEM_GROUP))

1781

if a:

1782

asplit = convert.RANGE_MATCHER.split(a)

1783

if len(asplit)==2:

1784

d['amount']=convert.frac_to_float(asplit[0].strip())

1785

d['rangeamount']=convert.frac_to_float(asplit[1].strip())

1786

else:

1787

d['amount']=convert.frac_to_float(a.strip())

1788

if u:

1789

if conv and conv.unit_dict.has_key(u.strip()):

1790

# Don't convert units to our units!

1791

d['unit']=u.strip()

1792

else:

1793

# has this unit been used

1794

prev_uses = self.fetch_all(self.ingredients_table,unit=u.strip())

1795

if prev_uses:

1796

d['unit']=u

1797

else:

1798

# otherwise, unit is not a unit

1799

i = u + ' ' + i

1800

if i:

1801

optmatch = re.search('\s+\(?[Oo]ptional\)?',i)

1802

if optmatch:

1803

d['optional']=True

1804

i = i[0:optmatch.start()] + i[optmatch.end():]

1805

d['item']=i.strip()

1806

if get_key: d['ingkey']=self.km.get_key(i.strip())

1807

debug('ingredient_parser returning: %s'%d,0)

1808

return d

1809

else:

1810

debug("Unable to parse %s"%s,0)

1811

return None

1812

1813

def ing_search (self, ing, keyed=None, recipe_table=None, use_regexp=True, exact=False):

1814

"""Search for an ingredient."""

1815

if not recipe_table: recipe_table = self.recipe_table

1816

vw = self.joined_search(recipe_table,self.ingredients_table,'ingkey',ing,use_regexp=use_regexp,exact=exact)

1817

if not keyed:

1818

vw2 = self.joined_search(recipe_table,self.ingredients_table,'item',ing,use_regexp=use_regexp,exact=exact)

1819

if vw2 and vw:

1820

vw = vw.union(vw2)

1821

else: vw = vw2

1822

return vw

1823

1824

def joined_search (self, table1, table2, search_by, search_str, use_regexp=True, exact=False, join_on='id'):

1825

raise NotImplementedError

1826

1827

def ings_search (self, ings, keyed=None, recipe_table=None, use_regexp=True, exact=False):

1828

"""Search for multiple ingredients."""

1829

raise NotImplementedError

1830

1831

def clear_remembered_optional_ings (self, recipe=None):

1832

"""Clear our memories of optional ingredient defaults.

1833

1834

If handed a recipe, we clear only for the recipe we've been

1835

given.

1836

1837

Otherwise, we clear *all* recipes.

1838

"""

1839

if recipe:

1840

vw = self.get_ings(recipe)

1841

else:

1842

vw = self.ingredients_table

1843

# this is ugly...

1844

vw1 = vw.select(shopoptional=1)

1845

vw2 = vw.select(shopoptional=2)

1846

for v in vw1,vw2:

1847

for i in v: self.modify_ing(i,{'shopoptional':0})

1848

1849

class DatabaseConverter(convert.Converter):

1850

def __init__ (self, db):

1851

self.db = db

1852

convert.converter.__init__(self)

1853

## FIXME: still need to finish this class and then

1854

## replace calls to convert.converter with

1855

## calls to DatabaseConverter

1856

1857

def create_conv_table (self):

1858

self.conv_table = dbDic('ckey','value',self.db.convtable_table, self.db,

1859

pickle_key=True)

1860

for k,v in defaults.CONVERTER_TABLE.items():

1861

if not self.conv_table.has_key(k):

1862

self.conv_table[k]=v

1863

1864

def create_density_table (self):

1865

self.density_table = dbDic('dkey','value',

1866

self.db.density_table,self.db)

1867

for k,v in defaults.DENSITY_TABLE.items():

1868

if not self.density_table.has_key(k):

1869

self.density_table[k]=v

1870

1871

def create_cross_unit_table (self):

1872

self.cross_unit_table=dbDic('cukey','value',self.db.crossunitdict_table,self.db)

1873

for k,v in defaults.CROSS_UNIT_TABLE:

1874

if not self.cross_unit_table.has_key(k):

1875

self.cross_unit_table[k]=v

1876

1877

def create_unit_dict (self):

1878

self.units = defaults.UNITS

1879

self.unit_dict=dbDic('ukey','value',self.db.unitdict_table,self.db)

1880

for itm in self.units:

1881

key = itm[0]

1882

variations = itm[1]

1883

self.unit_dict[key] = key

1884

for v in variations:

1885

self.unit_dict[v] = key

1886

1887

class dbDic:

1888

def __init__ (self, keyprop, valprop, view, db, pickle_key=False, pickle_val=True):

1889

"""Create a dictionary interface to a database table."""

1890

self.pickle_key = pickle_key

1891

self.pickle_val = pickle_val

1892

self.vw = view

1893

self.kp = keyprop

1894

self.vp = valprop

1895

self.db = db

1896

self.just_got = {}

1897

1898

def has_key (self, k):

1899

try:

1900

self.just_got = {k:self.__getitem__(k)}

1901

return True

1902

except:

1903

try:

1904

self.__getitem__(k)

1905

return True

1906

except:

1907

return False

1908

1909

def __setitem__ (self, k, v):

1910

if self.pickle_key:

1911

k=pickle.dumps(k)

1912

if self.pickle_val: store_v=pickle.dumps(v)

1913

else: store_v = v

1914

row = self.db.fetch_one(self.vw,**{self.kp:k})

1915

if row:

1916

self.db.do_modify(self.vw, row, {self.vp:store_v},id_col=self.kp)

1917

else:

1918

self.db.do_add(self.vw,{self.kp:k,self.vp:store_v})

1919

self.db.changed=True

1920

return v

1921

1922

def __getitem__ (self, k):

1923

if self.just_got.has_key(k): return self.just_got[k]

1924

if self.pickle_key:

1925

k=pickle.dumps(k)

1926

v = getattr(self.db.fetch_one(self.vw,**{self.kp:k}),self.vp)

1927

if v and self.pickle_val:

1928

try:

1929

return pickle.loads(v)

1930

except:

1931

print "Problem unpickling ",v

1932

raise

1933

else:

1934

return v

1935

1936

def __repr__ (self):

1937

retstr = "<dbDic> {"

1938

#for i in self.vw:

1939

# if self.pickle_key:

1940

# retstr += "%s"%pickle.loads(getattr(i,self.kp))

1941

# else:

1942

# retstr += getattr(i,self.kp)

1943

# retstr += ":"

1944

# if self.pickle_val:

1945

# retstr += "%s"%pickle.loads(getattr(i,self.vp))

1946

# else:

1947

# retstr += "%s"%getattr(i,self.vp)

1948

# retstr += ", "

1949

retstr += "}"

1950

return retstr

1951

1952

def initialize (self, d):

1953

'''Initialize values based on dictionary d

1954

1955

We assume the DB is known to be empty.

1956

1957

'''

1958

dics = []

1959

for k in d:

1960

if self.pickle_val:

1961

store_v = pickle.dumps(d[k])

1962

else:

1963

store_v = d[k]

1964

if type(store_v) in types.StringTypes:

1965

store_v = unicode(store_v)

1966

if type(k) in types.StringTypes:

1967

k = unicode(k)

1968

dics.append({self.kp:k,self.vp:store_v})

1969

self.vw.insert().execute(*dics)

1970

1971

def keys (self):

1972

ret = []

1973

for i in self.db.fetch_all(self.vw):

1974

ret.append(getattr(i,self.kp))

1975

return ret

1976

1977

def values (self):

1978

ret = []

1979

for i in self.db.fetch_all(self.vw):

1980

val = getattr(i,self.vp)

1981

if val and self.pickle_val: val = pickle.loads(val)

1982

ret.append(val)

1983

return ret

1984

1985

def items (self):

1986

ret = []

1987

for i in self.db.fetch_all(self.vw):

1988

key = getattr(i,self.kp)

1989

val = getattr(i,self.vp)

1990

if key and self.pickle_key:

1991

try:

1992

key = pickle.loads(key)

1993

except:

1994

print 'Problem unpickling key ',key

1995

raise

1996

if val and self.pickle_val:

1997

try:

1998

val = pickle.loads(val)

1999

except:

2000

print 'Problem unpickling value ',val, ' for key ',key

2001

raise

2002

ret.append((key,val))

2003

return ret

2004

2005

# To change

2006

# fetch_one -> use whatever syntax sqlalchemy uses throughout

2007

# fetch_all ->

2008

#recipe_table -> recipe_table

2009

# To eliminate

2010

2011

def test_db ():

2012

import tempfile

2013

db = RecData(file=tempfile.mktemp())

2014

print 'BEGIN TESTING'

2015

from db_tests import test_db

2016

test_db(db)

2017

print 'END TESTING'

2018

2019

def add_sample_recs ():

2020

for rec,ings in [[dict(title='Spaghetti',cuisine='Italian',category='Easy, Entree'),

2021

[dict(amount=1,unit='jar',item='Marinara Sauce',ingkey='sauce, marinara'),

2022

dict(amount=0.25,unit='c.',item='Parmesan Cheese',ingkey='cheese, parmesan'),

2023

dict(amount=.5,unit='lb.',item='Spaghetti',ingkey='spaghetti, dried')]],

2024

[dict(title='Spaghetti w/ Meatballs',cuisine='Italian',category='Easy, Entree'),

2025

[dict(amount=1,unit='jar',item='Marinara Sauce',ingkey='sauce, marinara'),

2026

dict(amount=0.25,unit='c.',item='Parmesan Cheese',ingkey='cheese, parmesan'),

2027

dict(amount=.5,unit='lb.',item='Spaghetti',ingkey='spaghetti, dried'),

2028

dict(amount=0.5,unit='lb.',item='Meatballs',ingkey='Meatballs, prepared'),

2029

]],

2030

[dict(title='Toasted cheese',cuisine='American',category='Sandwich, Easy',

2031

servings=2),

2032

[dict(amount=2,unit='slices',item='bread'),

2033

dict(amount=2,unit='slices',item='cheddar cheese'),

2034

dict(amount=2,unit='slices',item='tomato')]]

2035

]:

2036

r = db.add_rec(rec)

2037

for i in ings:

2038

i['recipe_id']=r.id

2039

db.add_ing(i)

2040

2041

def get_database (*args,**kwargs):

2042

try:

2043

return RecData(*args,**kwargs)

2044

except RecData, rd:

2045

return rd

2046

2047

if __name__ == '__main__':

2048

db = RecData()