Coverage for aisdb/database/sqlfcn.py: 100%

1''' pass these functions to DBQuery.gen_qry() as the function argument ''' 

2import os 

3 

4from aisdb import sqlpath 

5 

6with open(os.path.join(sqlpath, 'cte_dynamic_clusteredidx.sql'), 'r') as f: 

7 sql_dynamic = f.read() 

8 

9with open(os.path.join(sqlpath, 'cte_static_aggregate.sql'), 'r') as f: 

10 sql_static = f.read() 

11 

12with open(os.path.join(sqlpath, 'select_join_dynamic_static_clusteredidx.sql'), 

13 'r') as f: 

14 sql_leftjoin = f.read() 

15 

16with open(os.path.join(sqlpath, 'cte_aliases.sql'), 'r') as f: 

17 sql_aliases = f.read() 

18 

19 

20def _dynamic(*, month, callback, **kwargs): 

21 ''' SQL common table expression for selecting from dynamic tables ''' 

22 args = [month for _ in range(len(sql_dynamic.split('{}')) - 1)] 

23 sql = sql_dynamic.format(*args) 

24 sql += callback(month=month, alias='d', **kwargs) 

25 return sql 

26 

27 

28def _static(*, month='197001', **_): 

29 ''' SQL common table expression for selecting from static tables ''' 

30 args = [month for _ in range(len(sql_static.split('{}')) - 1)] 

31 return sql_static.format(*args) 

32 

33 

34def _leftjoin(month='197001'): 

35 ''' SQL select statement using common table expressions. 

36 Joins columns from dynamic, static, and coarsetype_ref tables. 

37 ''' 

38 args = [month for _ in range(len(sql_leftjoin.split('{}')) - 1)] 

39 return sql_leftjoin.format(*args) 

40 

41 

42def _aliases(*, month, callback, kwargs): 

43 ''' declare common table expression aliases ''' 

44 args = (month, _dynamic(month=month, callback=callback, 

45 **kwargs), month, _static(month=month)) 

46 return sql_aliases.format(*args) 

47 

48 

49def crawl_dynamic(*, months, callback, **kwargs): 

50 ''' iterate over position reports tables to create SQL query spanning 

51 desired time range 

52 

53 this function should be passed as a callback to DBQuery.gen_qry(), 

54 and should not be called directly 

55 ''' 

56 sql_dynamic = '\nUNION\n'.join([ 

57 _dynamic(month=month, callback=callback, **kwargs) for month in months 

58 ]) + '\nORDER BY 1,2' 

59 return sql_dynamic 

60 

61 

62def crawl_dynamic_static(*, months, callback, **kwargs): 

63 ''' iterate over position reports and static messages tables to create SQL 

64 query spanning desired time range 

65 

66 this function should be passed as a callback to DBQuery.gen_qry(), 

67 and should not be called directly 

68 ''' 

69 sqlfile = 'cte_coarsetype.sql' 

70 with open(os.path.join(sqlpath, sqlfile), 'r') as f: 

71 sql_coarsetype = f.read() 

72 sql_aliases = ''.join([ 

73 _aliases(month=month, callback=callback, kwargs=kwargs) 

74 for month in months 

75 ]) 

76 sql_union = '\nUNION\n'.join([_leftjoin(month=month) for month in months]) 

77 sql_qry = f'WITH\n{sql_aliases}\n{sql_coarsetype}\n{sql_union}' 

78 sql_qry += ' ORDER BY 1,2' 

79 return sql_qry