Merge ~gavin.lin/cc-lab-manager:customized_naming into cc-lab-manager:master

[Gavin Lin]

Generate device id and use it as maas node name

[Gavin Lin (gavin.lin)]

Self approved, tested with some devices in lab5.
It's weekend now, and this need to be landed to re-construct the infrastructure before people start using the lab in Monday.

review: Approve

diff --git a/cc_lab_manager/commands/cc_lab_manager.py b/cc_lab_manager/commands/cc_lab_manager.py
index 3b78d8e..1ed8b1b 100644
--- a/cc_lab_manager/commands/cc_lab_manager.py
+++ b/cc_lab_manager/commands/cc_lab_manager.py
@@ -2,13 +2,15 @@ import logging
 import os
 import sys
 
-from cc_lab_manager.gsheet_db import gsheet_db
-from cc_lab_manager.gsheet_db import db_gsheet
 from cc_lab_manager.c3_db import c3_db
 from cc_lab_manager.dhcp import dhcp_config_generator
 from cc_lab_manager.dhcp import restart_dhcp_servers
-from cc_lab_manager.maas import create_maas_node
 from cc_lab_manager.gen_config import gen_agent_tf_config
+from cc_lab_manager.gsheet_db import gsheet_db
+from cc_lab_manager.gsheet_db import db_gsheet
+from cc_lab_manager.insert_data import gen_device_id
+from cc_lab_manager.maas import create_maas_node
+from cc_lab_manager.maas import update_maas_node_network
 
 
 logger = logging.getLogger('cc-lab-manager')
@@ -20,12 +22,18 @@ def main():
     # Get needed information from c3
     c3_db.main()
 
+    # Generate device id
+    result = gen_device_id.main()
+    if result == 1:
+        return 1
+
     # Generate and apply DHCP config for each subnet
     dhcp_config_generator.main()
     restart_dhcp_servers.main()
 
     # Create MAAS nodes for DUTs
     create_maas_node.main()
+    update_maas_node_network.main()
 
     # Write data in db back to the google sheet
     db_gsheet.main()
diff --git a/cc_lab_manager/gsheet_db/db_gsheet.py b/cc_lab_manager/gsheet_db/db_gsheet.py
index c535445..34e99d1 100644
--- a/cc_lab_manager/gsheet_db/db_gsheet.py
+++ b/cc_lab_manager/gsheet_db/db_gsheet.py
@@ -50,7 +50,6 @@ def get_column_to_be_updated(token_file):
     for key in col_to_be_updated:
         col_to_be_updated[key] = col_position_list[started_index]
         started_index += 1
-    
     return col_to_be_updated
     
 
@@ -71,7 +70,7 @@ def sync_c3_data_to_sheet(db_machine_list,token_file):
 
         row_num += 1
     wks.update_cells(cell_list)
-        
+
 
 def read_data_from_db(cursor, token_file):
     
@@ -82,6 +81,43 @@ def read_data_from_db(cursor, token_file):
     
     return list(results)
 
+
+def sync_device_id_to_sheet(cursor, token_file):
+
+    col_in_c3 = ['CID','Lab','Device_ID',
+                 'Lab', 'CID']
+    sqlcmd = 'select {},{},{} from lab_hw \
+              order by {} ASC, {} ASC'.format(*col_in_c3)
+    cursor.execute(sqlcmd)
+    db_device_list = cursor.fetchall()
+
+    device_dict = {}
+    for device in db_device_list:
+        if device['Lab'] not in device_dict:
+            device_dict[device['Lab']] = {}
+        device_dict[device['Lab']][device['CID']] = device['Device_ID']
+
+    gc = pygsheets.authorize(service_file=token_file)
+    sht = gc.open_by_url(gsheet_url)
+
+    for lab in device_dict:
+        try:
+            wks = sht.worksheet_by_title(lab)
+        except:
+            continue
+        col_names = wks.get_row(1)
+        target_col = col_names.index('Device_ID') + 1
+        cid_in_wks = wks.get_col(1)
+        id_list = []
+        for target_row in range(1, len(cid_in_wks)):
+            cid = cid_in_wks[target_row]
+            try:
+                id_list.append(device_dict[lab][cid])
+            except:
+                id_list.append('')
+        wks.update_col(target_col, id_list, row_offset=1)
+
+
 def environ_or_required(key):
     if os.environ.get(key):
         return {'default': os.environ.get(key)}
@@ -100,6 +136,7 @@ def main():
     db = get_db_obj(args.database)
     cursor = get_cursor_obj(db)    
     sync_c3_data_to_sheet(read_data_from_db(cursor, args.token),args.token)
+    sync_device_id_to_sheet(cursor, args.token)
     close_db(db)
 
 
diff --git a/cc_lab_manager/insert_data/__init__.py b/cc_lab_manager/insert_data/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/cc_lab_manager/insert_data/__init__.py
diff --git a/cc_lab_manager/insert_data/gen_device_id.py b/cc_lab_manager/insert_data/gen_device_id.py
new file mode 100644
index 0000000..4af0c21
--- /dev/null
+++ b/cc_lab_manager/insert_data/gen_device_id.py
@@ -0,0 +1,102 @@
+import sqlite3
+import os
+import yaml
+import argparse
+import re
+
+from cc_lab_manager.cc_lab_manager_maptable import account_id_maptable
+
+def get_db_obj(path_to_db):
+    db = sqlite3.connect(path_to_db)
+    db.row_factory = sqlite3.Row
+    return db
+
+
+def get_cursor_obj(db_obj):
+    cursor = db_obj.cursor()
+    return cursor
+
+
+def close_db(db):
+    db.commit() 
+    db.close()
+
+    
+def read_data_from_db(cursor):
+    col_in_c3 = ['CID', 'Device_ID', 'Account', 'Model',
+                 'CID']
+    
+    sqlcmd = 'select {},{},{},{} from lab_hw \
+              where CID not like \'dummy-%\' \
+              order by {} ASC'.format(*col_in_c3)
+    cursor.execute(sqlcmd)
+    results = cursor.fetchall()
+    return list(results)
+
+
+def update_device_id(cursor, update_dev_id):
+    update_list = []
+    for cid in update_dev_id:
+        update_list.append([update_dev_id[cid], cid])
+
+    sqlcmd = 'update lab_hw set Device_ID=? where CID=?'
+    cursor.executemany(sqlcmd, update_list)
+
+
+def generate_device_id(db_device_list):
+    update_dev_id = {}
+    for device in db_device_list:
+        if not re.match('^[0-9]{6}-[0-9]{4,}$', device['CID']):
+            continue
+        if device['Device_ID'] == '':
+            if device['Account'] in account_id_maptable:
+                account_id = account_id_maptable[device['Account']]
+            else:
+                account_id = device['Account'].split(' ')[0]
+            cid_num = 'c' + device['CID'].split('-')[-1]
+            platform_id = re.sub('[^A-Za-z0-9 -]+', '', device['Model'].split('(')[0])
+            platform_id = platform_id.strip().replace(' ', '-')
+            if account_id in platform_id:
+                update_dev_id[device['CID']] = '-'.join([platform_id, cid_num]).lower()
+            else:
+                update_dev_id[device['CID']] = '-'.join([account_id, platform_id, cid_num]).lower()
+        else:
+            update_dev_id[device['CID']] = re.sub('[^A-Za-z0-9-]+', '', device['Device_ID']).lower()
+
+    # Check if there's dupliacte in current device id
+    device_id_list = []
+    for cid in update_dev_id:
+        if update_dev_id[cid] in device_id_list:
+            print('Duplicate device id:', cid)
+            return 1
+        else:
+            device_id_list.append(update_dev_id[cid])
+            
+    return update_dev_id
+
+
+def environ_or_required(key):
+    if os.environ.get(key):
+        return {'default': os.environ.get(key)}
+    else:
+        return {'required': True}
+
+
+def main():
+    parser = argparse.ArgumentParser()
+    parser.add_argument('--database', help="Database location",**environ_or_required('CCLM_HARDWARE_DB'))
+    args = parser.parse_args()
+    db = get_db_obj(args.database)
+    cursor = get_cursor_obj(db)
+    device_list = read_data_from_db(cursor)
+    update_dev_id = generate_device_id(device_list)
+    if update_dev_id == 1:
+        return_code = 1
+    else:
+        update_device_id(cursor, update_dev_id)
+        return_code = 0
+    close_db(db)
+    return return_code
+
+if __name__ == '__main__':
+        main()
diff --git a/cc_lab_manager/maas/create_maas_node.py b/cc_lab_manager/maas/create_maas_node.py
index 1ba9261..83cafb1 100644
--- a/cc_lab_manager/maas/create_maas_node.py
+++ b/cc_lab_manager/maas/create_maas_node.py
@@ -26,7 +26,7 @@ def close_db(db):
     
 def read_data_from_db(cursor):
     
-    col_in_c3 = ["CID","MAC","CanonicalLabel","Power","IP","PDU_IP","PDU_Outlet","MAAS_Server","MAAS_Node_ID"]
+    col_in_c3 = ["CID","MAC","CanonicalLabel","Provision","Power","Device_ID","IP","PDU_IP","PDU_Outlet","MAAS_Server","MAAS_Node_ID"]
     
     sqlcmd = 'select {} from lab_hw where CID not like \'dummy-%\' order by MAAS_Server'.format(",".join(col_in_c3[0:]))
     cursor.execute(sqlcmd)
@@ -69,6 +69,9 @@ def create_maas_node(machine_list, maas_admin, maas_admin_password):
     client = ""
     
     for machine in machine_list:
+
+        if machine["Provision"] not in ['maas_uefi', 'maas_legacy']:
+            continue
         
         if not mass_is_connecting:
             mass_is_connecting = machine["MAAS_Server"]
@@ -81,7 +84,7 @@ def create_maas_node(machine_list, maas_admin, maas_admin_password):
         
         power_type = machine["Power"].lower() if machine["Power"] != "" else "manual"
         power_parameter = {}
-        node_name = machine["cid"] + "-" + re.sub('[^A-Za-z0-9\-]+', '', machine["CanonicalLabel"])
+        node_name = machine["Device_ID"]
         
         if machine["MAC"] in maas_mac_list or machine["MAC"] == '':
             continue
diff --git a/cc_lab_manager/maas/update-maas-node-network-setting.py b/cc_lab_manager/maas/update_maas_node_network.py
similarity index 100%
rename from cc_lab_manager/maas/update-maas-node-network-setting.py
rename to cc_lab_manager/maas/update_maas_node_network.py