-
Notifications
You must be signed in to change notification settings - Fork 4
/
model.py
2721 lines (2271 loc) · 95.4 KB
/
model.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
from __future__ import annotations
import datetime
import json
import logging
import random
import re
import string
import uuid
import warnings
from collections import Counter, defaultdict
from typing import TYPE_CHECKING
import uszipcode
from flask_babel import lazy_gettext as _
from flask_bcrypt import check_password_hash, generate_password_hash
from geoalchemy2 import Geography, Geometry
from psycopg2.extensions import adapt as sqlescape
from sqlalchemy import (
Boolean,
Column,
DateTime,
Enum,
ForeignKey,
Index,
Integer,
String,
Table,
Unicode,
UniqueConstraint,
create_engine,
)
from sqlalchemy import exc as sa_exc
from sqlalchemy import func
from sqlalchemy.exc import IntegrityError, MultipleResultsFound, NoResultFound
from sqlalchemy.ext.hybrid import hybrid_property
from sqlalchemy.orm import (
aliased,
backref,
declarative_base,
relationship,
sessionmaker,
validates,
)
from sqlalchemy.orm.session import Session
from sqlalchemy.sql import compiler
from sqlalchemy.sql.expression import (
and_,
case,
cast,
join,
literal_column,
or_,
outerjoin,
select,
)
from config import Configuration
from emailer import Emailer
from util import GeometryUtility
from util.language import LanguageCodes
from util.short_client_token import ShortClientTokenTool
from util.string_helpers import random_string
if TYPE_CHECKING:
from sqlalchemy.engine import Connection, Engine
def production_session():
url = Configuration.database_url()
logging.debug("Database url: %s", url)
_db = SessionManager.session(url)
# The first thing to do after getting a database connection is to
# set up the logging configuration.
#
# If called during a unit test, this will configure logging
# incorrectly, but 1) this method isn't normally called during
# unit tests, and 2) package_setup() will call initialize() again
# with the right arguments.
from log import LogConfiguration
LogConfiguration.initialize(_db)
return _db
DEBUG = False
def generate_secret():
"""Generate a random secret."""
return random_string(24)
class SessionManager:
engine_for_url = {}
@classmethod
def engine(cls, url=None):
url = url or Configuration.database_url()
return create_engine(url, echo=DEBUG)
@classmethod
def sessionmaker(cls, url=None):
engine = cls.engine(url)
return sessionmaker(bind=engine)
@classmethod
def initialize(cls, url: str) -> tuple[Engine, Connection]:
"""Initialize the database connection
Create all the database tables from the models
Optionally, run the alembic migration scripts
:param db_url: The Database connection url"""
if url in cls.engine_for_url:
engine = cls.engine_for_url[url]
return engine, engine.connect()
engine = cls.engine(url)
Base.metadata.create_all(engine)
cls.engine_for_url[url] = engine
return engine, engine.connect()
@classmethod
def session(cls, url):
engine = connection = 0
with warnings.catch_warnings():
warnings.simplefilter("ignore", category=sa_exc.SAWarning)
engine, connection = cls.initialize(url)
session = Session(connection)
cls.initialize_data(session)
session.commit()
return session
@classmethod
def initialize_data(cls, session):
pass
def get_one(db, model, on_multiple="error", **kwargs):
q = db.query(model).filter_by(**kwargs)
try:
return q.one()
except MultipleResultsFound as e:
if on_multiple == "error":
raise e
elif on_multiple == "interchangeable":
# These records are interchangeable so we can use
# whichever one we want.
#
# This may be a sign of a problem somewhere else. A
# database-level constraint might be useful.
q = q.limit(1)
return q.one()
except NoResultFound:
return None
def dump_query(query):
dialect = query.session.bind.dialect
statement = query.statement
comp = compiler.SQLCompiler(dialect, statement)
comp.compile()
enc = dialect.encoding
params = {}
for k, v in comp.params.items():
if isinstance(v, str):
v = v.encode(enc)
params[k] = sqlescape(v)
return (comp.string.encode(enc) % params).decode(enc)
def get_one_or_create(db, model, create_method="", create_method_kwargs=None, **kwargs):
one = get_one(db, model, **kwargs)
if one:
return one, False
else:
__transaction = db.begin_nested()
try:
if "on_multiple" in kwargs:
# This kwarg is supported by get_one() but not by create().
del kwargs["on_multiple"]
obj = create(db, model, create_method, create_method_kwargs, **kwargs)
__transaction.commit()
return obj
except IntegrityError as e:
logging.info(
"INTEGRITY ERROR on %r %r, %r: %r",
model,
create_method_kwargs,
kwargs,
e,
)
__transaction.rollback()
return db.query(model).filter_by(**kwargs).one(), False
def create(db, model, create_method="", create_method_kwargs=None, **kwargs):
kwargs.update(create_method_kwargs or {})
created = getattr(model, create_method, model)(**kwargs)
db.add(created)
db.flush()
return created, True
Base = declarative_base()
class LibraryType:
"""Constant container for library types.
This is as defined here:
https://github.com/NYPL-Simplified/Simplified/wiki/LibraryRegistryPublicAPI#the-subject-scheme-for-library-types
"""
SCHEME_URI = "http://librarysimplified.org/terms/library-types"
LOCAL = "local"
COUNTY = "county"
STATE = "state"
PROVINCE = "province"
NATIONAL = "national"
UNIVERSAL = "universal"
# Different nations use different terms for referring to their
# administrative divisions, which translates into different terms in
# the library type vocabulary.
ADMINISTRATIVE_DIVISION_TYPES = {
"US": STATE,
"CA": PROVINCE,
}
NAME_FOR_CODE = {
LOCAL: "Local library",
COUNTY: "County library",
STATE: "State library",
PROVINCE: "Provincial library",
NATIONAL: "National library",
UNIVERSAL: "Online library",
}
class Library(Base):
"""An entry in this table corresponds more or less to an OPDS server.
Libraries generally serve everyone in a specific list of
Places. Libraries may also focus on a subset of the places they
serve, and may restrict their service to certain audiences.
"""
__tablename__ = "libraries"
id = Column(Integer, primary_key=True)
# The official name of the library. This is not unique because
# there are many "Springfield Public Library"s. This is nullable
# because there's a period during initial registration where a
# library has no name. (TODO: we might be able to change this.)
name = Column(Unicode, index=True)
# Human-readable explanation of who the library serves.
description = Column(Unicode)
# An internally generated unique URN. This is used in controller
# URLs to identify a library. A registry will always use the same
# URN to identify a given library, even if the library's OPDS
# server changes.
internal_urn = Column(
Unicode,
nullable=False,
index=True,
unique=True,
default=lambda: "urn:uuid:" + str(uuid.uuid4()),
)
# The URL to the library's Authentication for OPDS document. This
# URL may change over time as libraries move to different servers.
# This URL is generally unique, but that's not a database
# requirement, since a single library could potentially have two
# registry entries.
authentication_url = Column(Unicode, index=True)
# The URL to the library's OPDS server root.
opds_url = Column(Unicode)
# The URL to the library's patron-facing web page.
web_url = Column(Unicode)
# When our record of this library was last updated.
timestamp = Column(
DateTime,
index=True,
default=lambda: datetime.datetime.utcnow(),
onupdate=lambda: datetime.datetime.utcnow(),
)
# The library's logo, as a web url
logo_url = Column(Unicode)
# Constants for determining which stage a library is in.
#
# Which stage the library is actually in depends on the
# combination of Library.library_stage (the library's opinion) and
# Library.registry_stage (the registry's opinion).
#
# If either value is CANCELLED_STAGE, the library is in
# CANCELLED_STAGE.
#
# Otherwise, if either value is TESTING_STAGE, the library is in
# TESTING_STAGE.
#
# Otherwise, the library is in PRODUCTION_STAGE.
TESTING_STAGE = "testing" # Library should show up in test feed
PRODUCTION_STAGE = "production" # Library should show up in production feed
CANCELLED_STAGE = "cancelled" # Library should not show up in any feed
stage_enum = Enum(
TESTING_STAGE, PRODUCTION_STAGE, CANCELLED_STAGE, name="library_stage"
)
# The library's opinion about which stage a library should be in.
_library_stage = Column(
stage_enum,
index=True,
nullable=False,
default=TESTING_STAGE,
name="library_stage",
)
# The registry's opinion about which stage a library should be in.
registry_stage = Column(
stage_enum, index=True, nullable=False, default=TESTING_STAGE
)
# Can people get books from this library without authenticating?
#
# We store this specially because it might be useful to filter
# for libraries of this type.
anonymous_access = Column(Boolean, default=False)
# Can eligible people get credentials for this library through
# an online registration process?
#
# We store this specially because it might be useful to filter
# for libraries of this type.
online_registration = Column(Boolean, default=False)
# To issue Short Client Tokens for this library, the registry must
# share a short name and a secret with them.
short_name = Column(Unicode, index=True, unique=True)
# The shared secret is also used to authenticate requests in the
# case where a library's URL has changed.
shared_secret = Column(Unicode)
# A library may have alternate names, e.g. "BPL" for the Brooklyn
# Public Library.
aliases = relationship("LibraryAlias", backref="library")
# A library may serve one or more geographic areas.
service_areas = relationship("ServiceArea", backref="library")
# A library may serve one or more specific audiences.
audiences = relationship(
"Audience", secondary="libraries_audiences", back_populates="libraries"
)
# The registry may have information about the library's
# collections of materials. The registry doesn't need to know
# details, but it's useful to know approximate counts when finding
# libraries that serve specific language communities.
collections = relationship("CollectionSummary", backref="library")
# The registry may keep delegated patron identifiers (basically,
# Adobe IDs) for a library's patrons. This allows the library's
# patrons to decrypt Adobe ACS-encrypted books without having to
# license separate Adobe Vendor ID and without the registry
# knowing anything about the patrons.
delegated_patron_identifiers = relationship(
"DelegatedPatronIdentifier", backref="library"
)
# A library may have miscellaneous URIs associated with it. Generally
# speaking, the registry is only concerned about these URIs insofar as
# it needs to verify that they work.
hyperlinks = relationship("Hyperlink", backref="library")
settings = relationship(
"ConfigurationSetting",
backref="library",
lazy="joined",
cascade="all, delete",
)
# The PLS (Public Library Surveys) ID comes from the IMLS' annual survey
# (it isn't generated by our database). It enables us to gather data for metrics
# such as number of covered branches and size of service population.
PLS_ID = "pls_id"
@validates("short_name")
def validate_short_name(self, key, value):
if not value:
return value
if "|" in value:
raise ValueError("Short name cannot contain the pipe character.")
return value.upper()
@classmethod
def for_short_name(cls, _db, short_name):
"""Look up a library by short name."""
return get_one(_db, Library, short_name=short_name)
@classmethod
def for_urn(cls, _db, urn):
"""Look up a library by URN."""
return get_one(_db, Library, internal_urn=urn)
@classmethod
def random_short_name(cls, duplicate_check=None, max_attempts=20):
"""Generate a random short name for a library.
Library short names are six uppercase letters.
:param duplicate_check: Call this function to check whether a
generated name is a duplicate.
:param max_attempts: Stop trying to generate a name after this
many failures.
"""
attempts = 0
choice = None
while choice is None and attempts < max_attempts:
choice = "".join([random.choice(string.ascii_uppercase) for i in range(6)])
if duplicate_check and duplicate_check(choice):
choice = None
attempts += 1
if choice is None:
# This is very bad, but it's better to raise an exception
# than to be stuck in an infinite loop.
raise ValueError(
"Could not generate random short name after %d attempts!" % attempts
)
return choice
@hybrid_property
def library_stage(self):
return self._library_stage
@library_stage.setter
def library_stage(self, value):
"""A library can't unilaterally go from being in production to
not being in production.
"""
if self.in_production and value != self.PRODUCTION_STAGE:
raise ValueError(
"This library is already in production; only the registry can take it out of production."
)
self._library_stage = value
@property
def pls_id(self):
return ConfigurationSetting.for_library(Library.PLS_ID, self)
@property
def number_of_patrons(self):
db = Session.object_session(self)
# This is only meaningful if the library is in production.
if not self.in_production:
return 0
query = db.query(DelegatedPatronIdentifier).filter(
DelegatedPatronIdentifier.type
== DelegatedPatronIdentifier.ADOBE_ACCOUNT_ID,
DelegatedPatronIdentifier.library_id == self.id,
)
return query.count()
@classmethod
def patron_counts_by_library(self, _db, libraries):
"""Determine the number of registered Adobe Account IDs
(~patrons) for each of the given libraries.
:param _db: A database connection.
:param libraries: A list of Library objects.
:return: A dictionary mapping library IDs to patron counts.
"""
# The concept of 'patron count' only makes sense for
# production libraries.
library_ids = [library.id for library in libraries if library.in_production]
# Run the SQL query.
counts = (
select(
[
DelegatedPatronIdentifier.library_id,
func.count(DelegatedPatronIdentifier.id),
],
)
.where(
and_(
DelegatedPatronIdentifier.type
== DelegatedPatronIdentifier.ADOBE_ACCOUNT_ID,
DelegatedPatronIdentifier.library_id.in_(library_ids),
)
)
.group_by(DelegatedPatronIdentifier.library_id)
.select_from(DelegatedPatronIdentifier)
)
rows = _db.execute(counts)
# Convert the results to a dictionary.
results = dict()
for (library_id, count) in rows:
results[library_id] = count
return results
@property
def in_production(self):
"""Is this library in production?
If both the library and the registry think it should be, it is.
"""
prod = self.PRODUCTION_STAGE
return self.library_stage == prod and self.registry_stage == prod
@property
def types(self):
"""Return any special types for this library.
:yield: A sequence of code constants from LibraryTypes.
"""
service_area = self.service_area
if not service_area:
return
code = service_area.library_type
if code:
yield code
# TODO: in the future, more types, e.g. audience-based, can go
# here.
@property
def service_area(self):
"""Return the service area of this Library, assuming there is only
one.
:return: A Place, if there is one well-defined place this
library serves; otherwise None.
"""
everywhere = None
# Group the ServiceAreas by type.
by_type = defaultdict(set)
for a in self.service_areas:
if not a.place:
continue
if a.place.type == Place.EVERYWHERE:
# We will only return 'everywhere' if we don't find
# something more specific.
everywhere = a.place
continue
by_type[a.type].add(a)
# If there is a single focus area, use it.
# Otherwise, if there is a single eligibility area, use that.
service_area = None
for area_type in ServiceArea.FOCUS, ServiceArea.ELIGIBILITY:
if len(by_type[area_type]) == 1:
[service_area] = by_type[area_type]
if service_area.place:
return service_area.place
# This library serves everywhere, and it doesn't _also_ serve
# some more specific place.
if everywhere:
return everywhere
# This library does not have one ServiceArea that stands out.
return None
@property
def service_area_name(self):
"""Describe the library's service area in a short string a human would
understand, e.g. "Kern County, CA".
This library does the best it can to express a library's service
area as the name of a single place, but it's not always possible
since libraries can have multiple service areas.
TODO: We'll want to fetch a library's ServiceAreas (and their
Places) as part of the query that fetches libraries, so that
this doesn't result in extra DB queries per library.
:return: A string, or None if the library's service area can't be
described as a short string.
"""
if self.service_area:
return self.service_area.human_friendly_name
return None
@classmethod
def _feed_restriction(cls, production, library_field=None, registry_field=None):
"""Create a SQLAlchemy restriction that only finds libraries that
ought to be in the given feed.
:param production: A boolean. If True, then only libraries in
the production stage should be included. If False, then
libraries in the production or testing stages should be
included.
:return: A SQLAlchemy expression.
"""
# The library's opinion
if library_field is None:
library_field = Library.library_stage
# The registry's opinion
if registry_field is None:
registry_field = Library.registry_stage
prod = cls.PRODUCTION_STAGE
test = cls.TESTING_STAGE
if production:
# Both parties must agree that this library is
# production-ready.
return and_(library_field == prod, registry_field == prod)
else:
# Both parties must agree that this library is _either_
# in the production stage or the testing stage.
return and_(
library_field.in_((prod, test)), registry_field.in_((prod, test))
)
@classmethod
def relevant(cls, _db, target, language, audiences=None, production=True):
"""Find libraries that are most relevant for a user.
:param target: The user's current location. May be a Geometry object or
a 2-tuple (latitude, longitude).
:param language: The ISO 639-1 code for the user's language.
:param audiences: List of audiences the user is a member of.
By default, only libraries with the PUBLIC audience are shown.
:param production: If True, only libraries that are ready for
production are shown.
:return A Counter mapping Library objects to scores.
"""
# Constants that determine the weights of different components of the score.
# These may need to be adjusted when there are more libraries in the system to
# test with.
base_score = 1
audience_factor = 1.01
collection_size_factor = 1000
focus_area_distance_factor = 0.005
eligibility_area_distance_factor = 0.1
focus_area_size_factor = 0.00000001
score_threshold = 0.00001
# By default, only show libraries that are for the general public.
audiences = audiences or [Audience.PUBLIC]
# Convert the target to a single point.
if isinstance(target, tuple):
target = GeometryUtility.point(*target)
# Convert the language to 3-letter code.
language_code = LanguageCodes.string_to_alpha_3(language)
# Set up an alias for libraries and collection summaries for use in subqueries.
libraries_collections = outerjoin(
Library, CollectionSummary, Library.id == CollectionSummary.library_id
).alias("libraries_collections")
# Check if each library has a public audience.
public_audiences_subquery = (
select([func.count()])
.where(
and_(
Audience.name == Audience.PUBLIC,
libraries_audiences.c.library_id
== libraries_collections.c.libraries_id,
)
)
.select_from(libraries_audiences.join(Audience))
.scalar_subquery()
)
# Check if each library has a non-public audience from
# the user's audiences.
non_public_audiences_subquery = (
select([func.count()])
.where(
and_(
Audience.name != Audience.PUBLIC,
Audience.name.in_(audiences),
libraries_audiences.c.library_id
== libraries_collections.c.libraries_id,
)
)
.select_from(libraries_audiences.join(Audience))
.scalar_subquery()
)
# Increase the score if there was an audience match other than
# public, and set it to 0 if there's no match at all.
score = case(
[
# Audience match other than public.
(
non_public_audiences_subquery != literal_column(str(0)),
literal_column(str(base_score * audience_factor)),
),
# Public audience.
(
public_audiences_subquery != literal_column(str(0)),
literal_column(str(base_score)),
),
],
# No match.
else_=literal_column(str(0)),
)
# Function that decreases exponentially as its input increases.
def exponential_decrease(value):
original_exponent = -1 * value
# Prevent underflow and overflow errors by ensuring
# the exponent is between -500 and 500.
exponent = case(
[
(original_exponent > 500, literal_column(str(500))),
(original_exponent < -500, literal_column(str(-500))),
],
else_=original_exponent,
)
return func.exp(exponent)
# Get the maximum collection size for the user's language.
collections_by_size = (
_db.query(CollectionSummary)
.filter(CollectionSummary.language == language_code)
.order_by(CollectionSummary.size.desc())
)
if collections_by_size.count() == 0:
max = 0
else:
max = collections_by_size.first().size
# Only take collection size into account in the ranking if there's at
# least one library with a non-empty collection in the user's language.
if max > 0:
# If we don't have any information about a library's collection size,
# we'll just say there's one book. That way the library is ranked above
# a library we know has 0 books, but below any libraries with more.
# Maybe this should be larger, or should consider languages other than
# the user's language.
estimated_size = case(
[
(
libraries_collections.c.collectionsummaries_id == None,
literal_column("1"),
)
],
else_=libraries_collections.c.collectionsummaries_size,
)
score_multiplier = 1 - exponential_decrease(
1.0 * collection_size_factor * estimated_size / max
)
score = score * score_multiplier
# Create a subquery for a type of service area.
def service_area_subquery(type):
return (
select([Place.geometry, Place.type])
.where(
and_(
ServiceArea.library_id == libraries_collections.c.libraries_id,
ServiceArea.type == type,
)
)
.select_from(join(ServiceArea, Place, ServiceArea.place_id == Place.id))
.lateral()
)
# Find each library's eligibility areas.
eligibility_areas_subquery = service_area_subquery(ServiceArea.ELIGIBILITY)
# Find each library's focus areas.
focus_areas_subquery = service_area_subquery(ServiceArea.FOCUS)
# Get the minimum distance from the target to any service area returned
# by the subquery, in km. If a service area is "everywhere", the distance
# is 0.
def min_distance(subquery):
return (
func.min(
case(
[(subquery.c.type == Place.EVERYWHERE, literal_column(str(0)))],
else_=func.ST_DistanceSphere(target, subquery.c.geometry),
)
)
/ 1000
)
# Minimum distance to any eligibility area.
eligibility_min_distance = min_distance(eligibility_areas_subquery)
# Minimum distance to any focus area.
focus_min_distance = min_distance(focus_areas_subquery)
# Decrease the score based on how far away the library's eligibility area is.
score = score * exponential_decrease(
1.0 * eligibility_area_distance_factor * eligibility_min_distance
)
# Decrease the score based on how far away the library's focus area is.
score = score * exponential_decrease(
1.0 * focus_area_distance_factor * focus_min_distance
)
# Decrease the score based on the sum of the sizes of the library's focus areas, in km^2.
# This currently assumes that the library's focus areas don't overlap, which may not be true.
# If a focus area is "everywhere", the size is the area of Earth (510 million km^2).
focus_area_size = (
func.sum(
case(
[
(
focus_areas_subquery.c.type == Place.EVERYWHERE,
literal_column(str(510000000000000)),
)
],
else_=func.ST_Area(focus_areas_subquery.c.geometry),
)
)
/ 1000000
)
score = score * exponential_decrease(
1.0 * focus_area_size_factor * focus_area_size
)
# Rank the libraries by score, and remove any libraries
# that are below the score threshold.
library_id_and_score = (
select(
[
libraries_collections.c.libraries_id,
score.label("score"),
]
)
.having(score > literal_column(str(score_threshold)))
.where(
and_(
# Query for either the production feed or the testing feed.
cls._feed_restriction(
production,
libraries_collections.c.libraries_library_stage,
libraries_collections.c.libraries_registry_stage,
),
# Limit to the collection summaries for the user's
# language. If a library has no collection for the
# language, it's still included.
or_(
libraries_collections.c.collectionsummaries_language
== language_code,
libraries_collections.c.collectionsummaries_language == None,
),
)
)
.select_from(libraries_collections)
.group_by(
libraries_collections.c.libraries_id,
libraries_collections.c.collectionsummaries_id,
libraries_collections.c.collectionsummaries_size,
)
.order_by(score.desc())
)
result = _db.execute(library_id_and_score)
library_ids_and_scores = {r[0]: r[1] for r in result}
# Look up the Library objects and return them with the scores.
libraries = _db.query(Library).filter(
Library.id.in_(list(library_ids_and_scores.keys()))
)
c = Counter()
for library in libraries:
c[library] = library_ids_and_scores[library.id]
return c
@classmethod
def nearby(cls, _db, target, max_radius=150, production=True):
"""Find libraries whose service areas include or are close to the
given point.
:param target: The starting point. May be a Geometry object or
a 2-tuple (latitude, longitude).
:param max_radius: How far out from the starting point to search
for a library's service area, in kilometers.
:param production: If True, only libraries that are ready for
production are shown.
:return: A database query that returns lists of 2-tuples
(library, distance from starting point). Distances are
measured in meters.
"""
# We start with a single point on the globe. Call this Point
# A.
if isinstance(target, tuple):
target = GeometryUtility.point(*target)
target_geography = cast(target, Geography)
# Find another point on the globe that's 150 kilometers
# northeast of Point A. Call this Point B.
other_point = func.ST_Project(
target_geography, max_radius * 1000, func.radians(90.0)
)
other_point = cast(other_point, Geometry)
# Determine the distance between Point A and Point B, in
# radians. (150 kilometers is a different number of radians in
# different parts of the world.)
distance_to_other_point = func.ST_Distance(target, other_point)
# Find all Places that are no further away from A than that
# number of radians.
nearby = func.ST_DWithin(target, Place.geometry, distance_to_other_point)
# For each library served by such a place, calculate the
# minimum distance between the library's service area and
# Point A in meters.
min_distance = func.min(func.ST_DistanceSphere(target, Place.geometry))
qu = _db.query(Library).join(Library.service_areas).join(ServiceArea.place)
qu = qu.filter(cls._feed_restriction(production))
qu = qu.filter(nearby)
qu = (
qu.add_columns(min_distance)
.group_by(Library.id)
.order_by(min_distance.asc())
)
return qu
@classmethod
def search(cls, _db, target, query, production=True):
"""Try as hard as possible to find a small number of libraries
that match the given query.
:param target: Order libraries by their distance from this
point. May be a Geometry object or a 2-tuple (latitude,
longitude).
:param query: String to search for.
:param production: If True, only libraries that are ready for
production are shown.
"""
# We don't anticipate a lot of libraries or a lot of
# localities with the same name, but we need to have _some_
# kind of limit just to place an upper bound on how bad things
# can get. This will guarantee we never return more than 20
# results.
max_libraries = 10
if not query:
# No query, no results.
return []
if target:
if isinstance(target, tuple):
here = GeometryUtility.point(*target)
else:
here = target
else:
here = None
library_query, place_query, place_type = cls.query_parts(query)
# We start with libraries that match the name query.
if library_query:
libraries_for_name = (
cls.search_by_library_name(_db, library_query, here, production)
.limit(max_libraries)
.all()
)
else:
libraries_for_name = []
# We tack on any additional libraries that match a place query.
if place_query:
libraries_for_location = (
cls.search_by_location_name(
_db, place_query, place_type, here, production
)
.limit(max_libraries)
.all()
)
else:
libraries_for_location = []
if libraries_for_name and libraries_for_location:
# Filter out any libraries that show up in both lists.