Country Table

[gm3dmo]

This is a simple list of countries and is used for where a thing is. Usually this is the location of a cemetery.

### Tasks
- [x] Dump the data from the legacy site
- [x] Investigate other forms of countries table
- [x] Insert country data into new site
- [ ] Write tests to match legacy database country id's are available in the new model.

mysql> describe cmpm_country
    -> ;
+---------------+-------------+------+-----+---------+----------------+
| Field         | Type        | Null | Key | Default | Extra          |
+---------------+-------------+------+-----+---------+----------------+
| id            | int(11)     | NO   | PRI | NULL    | auto_increment |
| Name          | varchar(50) | NO   |     | NULL    |                |
| Alpha2        | varchar(2)  | NO   |     | NULL    |                |
| Alpha3        | varchar(3)  | NO   |     | NULL    |                |
| CountryNumber | varchar(4)  | NO   |     | NULL    |                |
| FipsCode      | varchar(50) | NO   |     | NULL    |                |
+---------------+-------------+------+-----+---------+----------------+

[ryaustin]

@gm3dmo Take a look at this repo for unicode flags: https://github.com/mledoze/countries

[gm3dmo]

Using the dist/countries.csv file from above I was able to make Copilot emit this sweet model:

from django.db import models

class Country(models.Model):
    name_common = models.CharField(max_length=255)
    name_official = models.CharField(max_length=255)
    tld = models.CharField(max_length=10)
    cca2 = models.CharField(max_length=2)
    ccn3 = models.CharField(max_length=3)
    cca3 = models.CharField(max_length=3)
    cioc = models.CharField(max_length=3)
    independent = models.BooleanField()
    status = models.CharField(max_length=255)
    unMember = models.BooleanField()
    currencies = models.CharField(max_length=255)
    idd_root = models.CharField(max_length=10)
    idd_suffixes = models.CharField(max_length=255)
    capital = models.CharField(max_length=255)
    alt_spellings = models.CharField(max_length=255)
    region = models.CharField(max_length=255)
    subregion = models.CharField(max_length=255)
    languages = models.CharField(max_length=255)
    translations_ces_official = models.CharField(max_length=255)
    translations_ces_common = models.CharField(max_length=255)
    translations_deu_official = models.CharField(max_length=255)
    translations_deu_common = models.CharField(max_length=255)
    translations_est_official = models.CharField(max_length=255)
    translations_est_common = models.CharField(max_length=255)
    translations_fin_official = models.CharField(max_length=255)
    translations_fin_common = models.CharField(max_length=255)
    translations_fra_official = models.CharField(max_length=255)
    translations_fra_common = models.CharField(max_length=255)
    translations_hrv_official = models.CharField(max_length=255)
    translations_hrv_common = models.CharField(max_length=255)
    translations_hun_official = models.CharField(max_length=255)
    translations_hun_common = models.CharField(max_length=255)
    translations_ita_official = models.CharField(max_length=255)
    translations_ita_common = models.CharField(max_length=255)
    translations_jpn_official = models.CharField(max_length=255)
    translations_jpn_common = models.CharField(max_length=255)
    translations_kor_official = models.CharField(max_length=255)
    translations_kor_common = models.CharField(max_length=255)
    translations_nld_official = models.CharField(max_length=255)
    translations_nld_common = models.CharField(max_length=255)
    translations_per_official = models.CharField(max_length=255)
    translations_per_common = models.CharField(max_length=255)
    translations_pol_official = models.CharField(max_length=255)
    translations_pol_common = models.CharField(max_length=255)
    translations_por_official = models.CharField(max_length=255)
    translations_por_common = models.CharField(max_length=255)
    translations_rus_official = models.CharField(max_length=255)
    translations_rus_common = models.CharField(max_length=255)
    translations_slk_official = models.CharField(max_length=255)
    translations_slk_common = models.CharField(max_length=255)
    translations_spa_official = models.CharField(max_length=255)
    translations_spa_common = models.CharField(max_length=255)
    translations_swe_official = models.CharField(max_length=255)
    translations_swe_common = models.CharField(max_length=255)
    translations_urd_official = models.CharField(max_length=255)
    translations_urd_common = models.CharField(max_length=255)
    translations_zho_official = models.CharField(max_length=255)
    translations_zho_common = models.CharField(max_length=255)
    latlng = models.CharField(max_length=255)
    landlocked = models.BooleanField()
    borders = models.CharField(max_length=255)
    area = models.FloatField()
    flag = models.CharField(max_length=255)
    demonyms_eng_f = models.CharField(max_length=255)
    demonyms_eng_m = models.CharField(max_length=255)
    demonyms_fra_f = models.CharField(max_length=255)
    demonyms_fra_m = models.CharField(max_length=255)
    callingCodes = models.CharField(max_length=255)

    class Meta:
        verbose_name_plural = "Countries"

    def __str__(self):
        return self.name_common

[gm3dmo]

The ccn3 column appears to match my country.id in the legacy database.

[gm3dmo]

sqlite> select "name.common" from countries where ccn3 = "";
name.common = Kosovo

So Kosovo does not have a ccn3 country code.

[gm3dmo]

Kosovo

In the mledoze/countries data Kosovo has no ccn3 value. We need this as a key for the cemeteries table. We want to use the meldoze data particularly because of the flag data.

Solution: Give Kosovo the made up number 999

Because the largest number currently is:

sqlite> select max(ccn3) from countries
   ...> ;
max(ccn3) = 894

we can use 999 for Kosovo.

[gm3dmo]

Create a smaller model using https://peric.github.io/GetCountries/

[gm3dmo]

https://cmp-production.up.railway.app/countries/