sts-sponsors team mailing list archive

[Thorsten Merten <mp+439266@xxxxxxxxxxxxxxxxxx>]

Thorsten Merten has proposed merging ~thorsten-merten/maas-site-manager:MAASENG-1486-add-testdata into maas-site-manager:main.

Commit message:
feat: add test data and import tools

docs: add documentation wrt test data
* how to clear db
* how to add test data

feat: make datamodel reflect input from UX
* change machine status properties
* remove identifier
* add country
* change timezone to decimal (3 char string is ambiguous)
* add users table
* rename tables



Requested reviews:
  MAAS Committers (maas-committers)

For more details, see:
https://code.launchpad.net/~thorsten-merten/maas-site-manager/+git/maas-site-manager/+merge/439266
-- 
Your team MAAS Committers is requested to review the proposed merge of ~thorsten-merten/maas-site-manager:MAASENG-1486-add-testdata into maas-site-manager:main.

diff --git a/backend/README.md b/backend/README.md
index 9bf524c..966db8c 100644
--- a/backend/README.md
+++ b/backend/README.md
@@ -86,3 +86,49 @@ The application can be run via
 ```
 tox -e run run
 ```
+
+## Delete and recreate the database
+
+There is not yet functionality to migrate the database with MAAS site manager.
+However a database is created for you on application startup. If the schema changes
+or you just want a fresh start, you can do the following.
+
+### Using docker
+
+To recreate the database stop the app (e.g. `docker compose down`) and run
+
+```
+docker volume rm maas-site-manager_postgres-data
+```
+
+A new database will be created for you on the next `docker-compose up`.
+
+### Any postgresql database
+
+(This also works if you use docker. In that case make sure to connect with the correct credentials)
+
+If you are using your own postgres you can usually do the following to empty the database:
+
+- Connect to your database
+```
+psql
+\c <database_name>
+```
+
+- Execute the following to empty and recreate the database.
+```
+DROP SCHEMA public CASCADE;
+CREATE SCHEMA public;
+
+GRANT ALL ON SCHEMA public TO postgres;
+GRANT ALL ON SCHEMA public TO public;
+```
+
+### Importing test data to the database
+
+There are CSV files available that can be used as test data.
+
+If you are running the app in docker you can easily load those
+
+- go to the test data directory `cd ../testdata`
+- run the loading script `./import.sh`
diff --git a/backend/msm/db/_tables.py b/backend/msm/db/_tables.py
index 98d1e42..6d25ecf 100644
--- a/backend/msm/db/_tables.py
+++ b/backend/msm/db/_tables.py
@@ -1,11 +1,12 @@
 from uuid import uuid4
 
 from sqlalchemy import (
+    Boolean,
     Column,
-    DECIMAL,
     ForeignKey,
     Integer,
     MetaData,
+    Numeric,
     String,
     Table,
     Text,
@@ -16,43 +17,58 @@ from sqlalchemy.types import DateTime
 METADATA = MetaData()
 
 Site = Table(
-    "site",
+    "sites",
     METADATA,
-    Column("identifier", String(250), unique=True),
-    Column("city", String(250)),
     Column("id", Integer, primary_key=True, index=True),
+    Column("city", String(250)),
+    # ISO 3166 Alpha2
+    Column("country", String(2)),
     # Decimal(8/6)/(9/6) = 16cm precision
-    Column("latitude", DECIMAL(precision=8, scale=6)),
-    Column("longitude", DECIMAL(precision=9, scale=6)),
-    Column("name", String(250)),
+    Column("latitude", Numeric(precision=8, scale=6)),
+    Column("longitude", Numeric(precision=9, scale=6)),
+    Column("name", String(250), unique=True),
     Column("note", Text),
     Column("region", String(250)),
     Column("street", String(250)),
-    Column("timezone", String(3)),
+    # Timezones need be up to x.25 accuracy
+    Column("timezone", Numeric(precision=3, scale=2)),
     Column("url", String(2048)),
 )
 
 
+User = Table(
+    "users",
+    METADATA,
+    Column("id", Integer, primary_key=True, index=True),
+    Column("email", String(250), unique=True, index=True),
+    Column("full_name", String),
+    # this is the hashed password
+    Column("password", String(100)),
+    Column("disabled", Boolean),
+)
+
+
 Token = Table(
-    "token",
+    "tokens",
     METADATA,
     Column("id", Integer, primary_key=True, index=True),
-    Column("site_id", Integer, ForeignKey("site.id"), index=True),
+    Column("site_id", Integer, ForeignKey("sites.id"), index=True),
     Column(
         "value", UUID(as_uuid=True), nullable=False, index=True, default=uuid4
     ),
     Column("expiration", DateTime, nullable=False),
 )
 
+
 SiteData = Table(
     "site_data",
     METADATA,
     Column("id", Integer, primary_key=True, index=True),
     Column(
-        "site_id", Integer, ForeignKey("site.id"), index=True, nullable=False
+        "site_id", Integer, ForeignKey("sites.id"), index=True, nullable=False
     ),
-    Column("total_machines", Integer),
-    Column("occupied_machines", Integer),
+    Column("allocated_machines", Integer),
+    Column("deployed_machines", Integer),
     Column("ready_machines", Integer),
     Column("error_machines", Integer),
     Column("last_seen", DateTime),
diff --git a/backend/msm/db/queries.py b/backend/msm/db/queries.py
index a2d4dbc..a18ba36 100644
--- a/backend/msm/db/queries.py
+++ b/backend/msm/db/queries.py
@@ -86,7 +86,6 @@ async def get_filtered_sites(
     stmt = select(
         Site.c.id,
         Site.c.name,
-        Site.c.identifier,
         Site.c.city,
         Site.c.latitude,
         Site.c.longitude,
@@ -106,7 +105,6 @@ async def get_sites(session: AsyncSession) -> Iterable[SiteSchema]:
     stmt = select(
         Site.c.id,
         Site.c.name,
-        Site.c.identifier,
         Site.c.city,
         Site.c.latitude,
         Site.c.longitude,
diff --git a/backend/msm/schema.py b/backend/msm/schema.py
index 38a4ad5..10c726d 100644
--- a/backend/msm/schema.py
+++ b/backend/msm/schema.py
@@ -2,9 +2,42 @@ from datetime import (
     datetime,
     timedelta,
 )
+from decimal import Decimal
 from uuid import UUID
 
-from pydantic import BaseModel
+from pydantic import (
+    BaseModel,
+    SecretStr,
+)
+from pydantic.fields import Field
+
+# see discussion at
+# https://stackabuse.com/python-validate-email-address-with-regular-expressions-regex/
+valid_email = r"""
+    ([-!#-'*+/-9=?A-Z^-~]+(\.[-!#-'*+/-9=?A-Z^-~]+)*|
+    \"([]!#-[^-~ \t]|(\\[\t -~]))+\")
+    @
+    ([-!#-'*+/-9=?A-Z^-~]+(\.[-!#-'*+/-9=?A-Z^-~]+)*|\[[\t -Z^-~]*])"""
+
+
+class CreateUser(BaseModel):
+    """
+    A MAAS Site Manager User
+    """
+
+    email: str = Field(
+        title="email@xxxxxxxxxxx",
+        max_length=250,
+        min_length=3,
+        regex=valid_email,
+    )
+    full_name: str
+    password: SecretStr = Field(min_length=8, max_length=50)
+    disabled: bool
+
+
+class User(CreateUser):
+    id: int
 
 
 class CreateSite(BaseModel):
@@ -13,14 +46,13 @@ class CreateSite(BaseModel):
     """
 
     name: str
-    identifier: str
     city: str | None
-    latitude: str | None
-    longitude: str | None
+    latitude: str | Decimal | None
+    longitude: str | Decimal | None
     note: str | None
     region: str | None
     street: str | None
-    timezone: str | None
+    timezone: str | Decimal | None
     url: str
     # TODO: we will need to add tags
 
@@ -39,9 +71,8 @@ class CreateSiteData(BaseModel):
     """
 
     site_id: int
-    total_machines: int
-    # TODO: might include more states in future
-    occupied_machines: int  # TODO: name might change
+    allocated_machines: int
+    deployed_machines: int
     ready_machines: int
     error_machines: int
     last_seen: datetime
diff --git a/backend/msm/user_api/tests/test_handlers.py b/backend/msm/user_api/tests/test_handlers.py
index 20f9b5e..4ffe3e9 100644
--- a/backend/msm/user_api/tests/test_handlers.py
+++ b/backend/msm/user_api/tests/test_handlers.py
@@ -22,22 +22,21 @@ async def test_list_sites(
     site1 = {
         "id": 1,
         "name": "LondonHQ",
-        "identifier": "site one",
         "city": "London",
         "latitude": "51.509865",
         "longitude": "-0.118092",
         "note": "the first site",
         "region": "Blue Fin Bldg",
         "street": "110 Southwark St",
-        "timezone": "GMT",
+        "timezone": "0.00",
         "url": "https://londoncalling.example.com";,
     }
     site2 = site1.copy()
     site2["id"] = 2
-    site2["identifier"] = "site two"
     site2["name"] = "BerlinHQ"
+    site2["timezone"] = "-7.00"
     site2["city"] = "Berlin"
-    await fixture.create("site", [site1, site2])
+    await fixture.create("sites", [site1, site2])
     response = user_app_client.get("/sites?city=onDo")  # vs London
     assert response.status_code == 200
     assert response.json() == [site1]
@@ -76,7 +75,7 @@ async def test_list_tokens(
             "expiration": datetime.fromisoformat("2023-02-23T11:28:54.382456"),
         },
     ]
-    await fixture.create("token", tokens)
+    await fixture.create("tokens", tokens)
     response = user_app_client.get("/tokens")
     assert response.status_code == 200
     assert len(response.json()) == 2
diff --git a/mocks/import.sh b/mocks/import.sh
new file mode 100755
index 0000000..592405c
--- /dev/null
+++ b/mocks/import.sh
@@ -0,0 +1,27 @@
+#!/bin/sh
+#
+# This script imports the testdata from this directory into the postgres database
+#
+psql=psql
+envfile="../.env.dev"
+
+if ! command -v psql > /dev/null
+then
+  echo "The ${psql} command was not found. It is need it to import the data. Try:"
+  echo "apt-get install postgresql-client"
+  exit 1
+fi
+
+if ! test -s ${envfile}
+then
+  echo "Cannot source dev environment. Make sure ../.env.dev exists."
+  exit 1
+fi
+. ${envfile}
+
+docker compose cp users.csv postgres:/
+docker compose cp sites.csv postgres:/
+docker compose cp tokens.csv postgres:/
+docker compose cp site_data.csv postgres:/
+
+${psql} postgresql://"${POSTGRES_USER}":"${POSTGRES_PASSWORD}"@localhost:"${POSTGRES_PORT}"/"${POSTGRES_DB}" -f import.sql
diff --git a/mocks/import.sql b/mocks/import.sql
new file mode 100644
index 0000000..a7198af
--- /dev/null
+++ b/mocks/import.sql
@@ -0,0 +1,23 @@
+COPY sites(id, city, country, latitude, longitude, name, note, region, street, timezone, url)
+FROM '/sites.csv'
+DELIMITER ','
+QUOTE '"'
+CSV HEADER;
+
+COPY tokens(site_id, value, expiration)
+FROM '/tokens.csv'
+DELIMITER ','
+QUOTE '"'
+CSV HEADER;
+
+COPY users(email, full_name, disabled, password)
+FROM '/users.csv'
+DELIMITER ','
+QUOTE '"'
+CSV HEADER;
+
+COPY site_data(site_id, allocated_machines, deployed_machines, ready_machines, error_machines, last_seen)
+FROM '/site_data.csv'
+DELIMITER ','
+QUOTE '"'
+CSV HEADER;
diff --git a/mocks/site_data.csv b/mocks/site_data.csv
new file mode 100644
index 0000000..e6a4960
--- /dev/null
+++ b/mocks/site_data.csv
@@ -0,0 +1,7 @@
+site_id, allocated_machines, deployed_machines, ready_machines, error_machines, last_seen
+1,10,1,8,1,"01-01-2023 08:00"
+2,11,0,8,3,"01-01-2023 09:00"
+3,12,2,4,6,"01-01-2023 10:00"
+4,13,0,13,0,"01-01-2023 11:00"
+5,14,13,1,0,"01-01-2023 12:00"
+6,15,2,10,3,"01-01-2023 13:00"
diff --git a/mocks/sites.csv b/mocks/sites.csv
new file mode 100644
index 0000000..1218db3
--- /dev/null
+++ b/mocks/sites.csv
@@ -0,0 +1,9 @@
+id,city, country, latitude, longitude, name, note, region, street, timezone, url
+1,London,GB,51.501990,-0.092200,Canonical Group Limited,4th Floor,,201 Borough High Street,0.0,https://london.canonical.example.com
+2,Austin,US,30.269612,-97.741057,Canonical USA Inc.,Perry Brooks Building - Suite 300,,720 Brazos Street,-5.0,https://austin.canonical.example.com
+3,Boston,US,42.358859,-71.059615,Canonical USA Inc. 001,Suite 210,,18 Tremont Street,-4.0,https://boston.canonical.example.com
+4,Shanghai,CN,31.187270,121.436829,Canonical China,上海市漕溪北路331号12楼1246室,,No. 331 North Caoxi Road,+8.0,https://shanghai.canonical.example.com
+5,Beijing,CN,39.908447,116.448690,Canonical China 001,China World Office 1; 北京市朝阳区建国门外大街1号国贸写字楼1座11层1118-19室 100004,Chaoyang District,1 Jianguomenwai Avenue,+8.0,https://shanghai.canonical.example.com
+6,Taipei City,TW,25.058098,121.543406,Canonical Group Limited - Taiwan Branch,105402 台北市松山區民生東路三段100號12樓,Songshan Dist.,"12F.,No. 100,Sec. 3,Minsheng E. Rd.",+8.0,https://taiwan.canonical.example.com
+7,Douglas,IM,54.153072,-4.481012,Canonical Limited,2nd Floor - Clarendon House,,Victoria Street,0,https://canonical.example.com
+8,Tokyo,JP,35.673242,139.740669,Canonical Japan K.K,3rd Floor - Sanno Park Tower,,2-11-1 Nagata-cho Chiyoda-ku,9.0,https://japan.canonical.example.com
diff --git a/mocks/tokens.csv b/mocks/tokens.csv
new file mode 100644
index 0000000..0a4848b
--- /dev/null
+++ b/mocks/tokens.csv
@@ -0,0 +1,12 @@
+site_id,value,expiration
+1,c96abba0-9962-41e5-9f40-6e2b55578c06,2023-03-01 00:00:00
+2,8960d8a1-7c5e-4331-8f58-591cf145a313,2023-03-01 00:00:00
+3,dffa13fe-d2c5-43b4-81e1-1efde6eeec76,2023-03-01 00:00:00
+4,a31a7408-09ee-4403-98c1-5c59ca3534c2,2023-03-01 00:00:00
+5,4d015359-3093-4216-ac87-6dfa29d0d6c5,2023-03-01 00:00:00
+6,9f404751-a17b-456c-8fa9-46b7258cb471,2023-03-01 00:00:00
+7,269773b7-b47d-46e8-9825-eb25eda99771,2023-03-01 00:00:00
+8,fce21bd4-3a92-42af-adb4-6b46dc0aefd4,2023-03-01 00:00:00
+,0e846493-fde9-4d15-844c-2ca0341d1e84,2024-01-01 00:00:00
+,e15a7d3c-9df8-40c7-b81b-ed4796e777bc,2024-01-01 00:00:00
+,87a62d9a-7645-43b5-9dd4-eaf53e768c4a,2024-01-01 00:00:00
diff --git a/mocks/users.csv b/mocks/users.csv
new file mode 100644
index 0000000..4cc531b
--- /dev/null
+++ b/mocks/users.csv
@@ -0,0 +1,3 @@
+email, full_name, disabled, password (admin)
+admin@xxxxxxxxxxx,MAAS Admin,false,$2b$12$F5sgrhRNtWAOehcoVO.XK.oSvupmcg8.0T2jCHOTg15M8N8LrpRwS
+disabled@xxxxxxxxxxx,Obsolete Admin,true,$2b$12$iEPLFcNocyeUDgu2ywDVGeFHyrksI89bzSvdAwvU1N4zYFtofme3S