gcpdiag.queries.cloudsql

 28class Instance(models.Resource):
 29  """Represents CloudSQL Instance"""
 30
 31  _resource_data: dict
 32
 33  def __init__(self, project_id: str, resource_data: dict):
 34    super().__init__(project_id)
 35    self._resource_data = resource_data
 36
 37  @property
 38  def name(self) -> str:
 39    return self._resource_data['name']
 40
 41  @property
 42  def master_instance_name(self) -> str:
 43    return self._resource_data.get('masterInstanceName', '')
 44
 45  @property
 46  def state(self) -> str:
 47    return self._resource_data['state']
 48
 49  @property
 50  def version(self) -> str:
 51    return self._resource_data['databaseVersion']
 52
 53  @property
 54  def is_regional(self) -> bool:
 55    return (
 56      get_path(
 57        self._resource_data,
 58        ('settings', 'availabilityType'),
 59        default='ZONAL',
 60      )
 61      == 'REGIONAL'
 62    )
 63
 64  @property
 65  def ip_addresses(self) -> Iterable[network.IPv4AddrOrIPv6Addr]:
 66    return [
 67      ipaddress.ip_address(nic['ipAddress']) for nic in self._resource_data.get('ipAddresses', [])
 68    ]
 69
 70  @property
 71  def has_public_ip(self) -> bool:
 72    return get_path(self._resource_data, ('settings', 'ipConfiguration', 'ipv4Enabled'))
 73
 74  @property
 75  def has_maint_window(self) -> int:
 76    try:
 77      return get_path(self._resource_data, ('settings', 'maintenanceWindow', 'day'))
 78    except KeyError:
 79      return 0
 80
 81  @property
 82  def is_storage_auto_resize_enabled(self) -> bool:
 83    return get_path(self._resource_data, ('settings', 'storageAutoResize'))
 84
 85  @property
 86  def has_del_protection(self) -> bool:
 87    return get_path(self._resource_data, ('settings', 'deletionProtectionEnabled'), False)
 88
 89  @property
 90  def authorizednetworks(self) -> List[str]:
 91    authorizednetworks = get_path(
 92      self._resource_data,
 93      ('settings', 'ipConfiguration', 'authorizedNetworks'),
 94      [],
 95    )
 96    return [authorizednetwork['value'] for authorizednetwork in authorizednetworks]
 97
 98  @property
 99  def is_publically_accessible(self) -> List[str]:
100    return self.authorizednetworks
101
102  @property
103  def is_automated_backup_enabled(self) -> bool:
104    return get_path(self._resource_data, ('settings', 'backupConfiguration', 'enabled'))
105
106  @property
107  def is_pitr_enabled(self) -> bool:
108    return get_path(
109      self._resource_data, ('settings', 'backupConfiguration', 'pointInTimeRecoveryEnabled'), False
110    )
111
112  @property
113  def is_binary_log_enabled(self) -> bool:
114    return get_path(
115      self._resource_data, ('settings', 'backupConfiguration', 'binaryLogEnabled'), False
116    )
117
118  @property
119  def is_suspended_state(self) -> bool:
120    return self.state == 'SUSPENDED'
121
122  @property
123  def is_shared_core(self) -> bool:
124    shared_core_tiers = ['db-g1-small', 'db-f1-micro']
125    return self.tier in shared_core_tiers
126
127  @property
128  def tier(self) -> str:
129    return get_path(self._resource_data, ('settings', 'tier'), default='')
130
131  @property
132  def is_high_available(self) -> bool:
133    return get_path(self._resource_data, ('settings', 'availabilityType')) == 'REGIONAL'
134
135  @property
136  def flags(self) -> dict:
137    flags = get_path(self._resource_data, ('settings', 'databaseFlags'), [])
138    return {flag['name']: flag['value'] for flag in flags}
139
140  @property
141  def is_log_output_configured_as_table(self) -> bool:
142    return self.flags.get('log_output') == 'TABLE'
143
144  @property
145  def self_link(self) -> str:
146    return self._resource_data['selfLink']
147
148  @property
149  def full_path(self) -> str:
150    return self.self_link
151
152  def __str__(self) -> str:
153    return self.self_link

156@caching.cached_api_call
157def get_instances(context: models.Context) -> Iterable[Instance]:
158  if not apis.is_enabled(context.project_id, 'sqladmin'):
159    return []
160
161  api = apis.get_api('sqladmin', 'v1', context.project_id)
162  query = api.instances().list(project=context.project_id)
163  resp = query.execute(num_retries=config.API_RETRIES)
164  databases = []
165  for d in resp.get('items', []):
166    location = d.get('region', '')
167    labels = d.get('userLabels', {})
168    resource = d.get('name', '')
169    if not context.match_project_resource(location=location, labels=labels, resource=resource):
170      continue
171
172    databases.append(Instance(context.project_id, d))
173  return databases

176@caching.cached_api_call
177def get_release_schedule() -> dict:
178  """Extract the release schedule for Cloud SQL instances.
179
180  Returns:
181    A dictionary of release schedule.
182  """
183  page_url = 'https://cloud.google.com/sql/docs/db-versions'
184  release_data = {}
185  try:
186    tables = web.fetch_and_parse_all_tables(page_url)
187
188    def parse_date(date_str) -> Optional[datetime.date]:
189      date_str = date_str.strip().replace('*', '')
190      if not date_str or date_str in ['—', '-', 'N/A']:
191        return None
192      try:
193        return datetime.datetime.strptime(date_str, '%B %d, %Y').date()
194      except ValueError:
195        return None
196
197    for table in tables:
198      for row in table:
199        if len(row) < 5:
200          continue
201        version_str = row[0]
202
203        # Identify DB type and version
204        version_key = None
205        if 'MySQL' in version_str:
206          v = re.search(r'MySQL (\d+\.\d+)', version_str)
207          if v:
208            version_key = f'MYSQL_{v.group(1).replace(".", "_")}'
209        elif 'PostgreSQL' in version_str:
210          v = re.search(r'PostgreSQL (\d+)', version_str)
211          if not v:
212            v = re.search(r'PostgreSQL (\d+\.\d+)', version_str)
213          if v:
214            version_key = f'POSTGRES_{v.group(1).replace(".", "_")}'
215        elif 'SQL Server' in version_str:
216          v = re.search(r'SQL Server (\d+)', version_str)
217          if v:
218            version_key = f'SQLSERVER_{v.group(1)}'
219
220        if not version_key:
221          continue
222
223        regular_support_end = parse_date(row[3])
224        extended_support_end = parse_date(row[4])
225
226        release_data[version_key] = {
227          'regular_support_end': regular_support_end,
228          'extended_support_end': extended_support_end,
229        }
230  except Exception as e:
231    logging.error('Error extracting Cloud SQL release schedule: %s', e)
232  return release_data