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

[Gavin Lin]

Combine functions into a workflow and work in a single command.

[Kevin Yeh (kevinyeh)]

LGTM +1

review: Approve

diff --git a/cc-lab-manager/c3-db/__init__.py b/build/lib/cc_lab_manager/__init__.py
 similarity index 100%
 rename from cc-lab-manager/c3-db/__init__.py
 rename to build/lib/cc_lab_manager/__init__.py
diff --git a/cc-lab-manager/dhcp/__init__.py b/build/lib/cc_lab_manager/c3_db/__init__.py
 similarity index 100%
 rename from cc-lab-manager/dhcp/__init__.py
 rename to build/lib/cc_lab_manager/c3_db/__init__.py
diff --git a/cc-lab-manager/c3-db/c3-db.py b/build/lib/cc_lab_manager/c3_db/c3_db.py
 similarity index 86%
 rename from cc-lab-manager/c3-db/c3-db.py
 rename to build/lib/cc_lab_manager/c3_db/c3_db.py
index a9f6779..b9b0d7e 100644
--- a/cc-lab-manager/c3-db/c3-db.py
+++ b/build/lib/cc_lab_manager/c3_db/c3_db.py
@@ -1,7 +1,9 @@
 import sqlite3
 import logging
+import argparse
 import c3.api.query as c3query
 from c3.api.api_utils import APIQuery
+import os
 
 logger = logging.getLogger('cc-lab-manager')
 
@@ -22,6 +24,8 @@ def c3_to_db(db_path):
     for cid_db in cid_list:
         cid = ''.join(cid_db)
         logger.info('Processing {}...'.format(cid))
+        if '-' not in cid or 'TEL' in cid:
+            continue
 
         # Query hardware info from c3
         result = ''
@@ -94,17 +98,30 @@ def c3_to_db(db_path):
     hwdb.close()
 
 
+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('--conf', help="Config file for C3 API", default = os.environ.get('CCLM_CC_TOOL_BOX') + '/config/c3-cli/my_conf.ini')
+    parser.add_argument('--c3user', help="C3 User Account", **environ_or_required('CCLM_C3_USERNAME'))
+    parser.add_argument('--c3apikey', help="C3 API Key", **environ_or_required('CCLM_C3_APIKEY'))
+    parser.add_argument('--hwdb', help="C3 API Key", **environ_or_required('CCLM_HARDWARE_DB'))
+    args = parser.parse_args()
+
     ci = c3query.configuration.get_instance()
-    ci.read_configuration('my_conf.ini')
+    ci.read_configuration(args.conf)
 
     api = APIQuery(ci.config['C3']['URI'])
-    rparam = {"username": ci.config['C3']['UserName'],
-              "api_key": ci.config['C3']['APIKey']}
+    rparam = {"username": args.c3user,
+              "api_key": args.c3apikey}
 
     c3query.api_instance.set_api_params(api, rparam)
-    hwdb = './hardware.db'
-    c3_to_db(hwdb)
+    c3_to_db(args.hwdb)
 
 
 if __name__ == '__main__':
diff --git a/build/lib/cc_lab_manager/commands/__init__.py b/build/lib/cc_lab_manager/commands/__init__.py
 new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/build/lib/cc_lab_manager/commands/__init__.py
diff --git a/build/lib/cc_lab_manager/commands/cc_lab_manager.py b/build/lib/cc_lab_manager/commands/cc_lab_manager.py
 new file mode 100644
index 0000000..7da03df
--- /dev/null
+++ b/build/lib/cc_lab_manager/commands/cc_lab_manager.py
@@ -0,0 +1,49 @@
+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.maas import create_maas_node
+from cc_lab_manager.gen_config import gen_agent_tf_config
+
+logger = logging.getLogger('cc-lab-manager')
+
+def main():
+    # Read data from the google sheet to db
+    gsheet_db.main()
+
+    # Get needed information from c3
+    c3_db.main()
+
+    # Generate DHCP config for each subnets
+    # TODO: Currently these config files need to be copied
+    # manually, then MAAS will apply it automatically
+    dhcp_config_generator.main()
+
+    # Create MAAS nodes for DUTs
+    create_maas_node.main()
+
+    # Create agent configs for DUTs
+    # TODO: Currently these configs need to be push/pull
+    # and applied manually
+    gen_agent_tf_config.main()
+
+    # Write data in db back to the google sheet
+    db_gsheet.main()
+
+    #ci = c3query.configuration.get_instance()
+
+    #api = APIQuery(ci.config['C3']['URI'])
+    #rparam = {"username": ci.config['C3']['UserName'],
+    #          "api_key": ci.config['C3']['APIKey']}
+
+    #c3query.api_instance.set_api_params(api, rparam)
+    #hwdb = './hardware.db'
+    #c3_to_db(hwdb)
+
+
+if __name__ == '__main__':
+    main()
diff --git a/build/lib/cc_lab_manager/dhcp/__init__.py b/build/lib/cc_lab_manager/dhcp/__init__.py
 new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/build/lib/cc_lab_manager/dhcp/__init__.py
diff --git a/cc-lab-manager/dhcp/dhcp-config-generator.py b/build/lib/cc_lab_manager/dhcp/dhcp_config_generator.py
 similarity index 69%
 rename from cc-lab-manager/dhcp/dhcp-config-generator.py
 rename to build/lib/cc_lab_manager/dhcp/dhcp_config_generator.py
index 4ea854f..e586dd4 100644
--- a/cc-lab-manager/dhcp/dhcp-config-generator.py
+++ b/build/lib/cc_lab_manager/dhcp/dhcp_config_generator.py
@@ -1,7 +1,8 @@
 import sqlite3
 import logging
+import argparse
+import os
 from jinja2 import Template
-import c3.api.query as c3query
 
 logger = logging.getLogger('cc-lab-manager')
 
@@ -38,15 +39,19 @@ host {{Lab}}-{{Frame}}-S{{Shelf}}-P{{Partition}}-ctl {
 
 '''
 
-    def generate_dhcp_conf(self, ip_mac_list, dhcp_conf):
-        dhcp_conf_data = ''
+    def generate_dhcp_conf(self, ip_mac_list, dhcp_conf_path):
+        dhcp_conf_data = {}
         for ip_mac in ip_mac_list:
             if ip_mac['IP'] == '' or ip_mac['Controller_IP'] == ''\
                or ip_mac['Lab'] == '' or ip_mac['Frame'] == ''\
                or ip_mac['Shelf'] == '' or ip_mac['Partition'] == '':
-                logger.error("Missing required information for", ip_mac['CID'])
+                if ip_mac['CID'] == '':
+                    ip_mac_list.remove(ip_mac)
+                else:
+                    logger.info("Missing required information for", ip_mac['CID'])
                 continue
 
+            # Generate MAC for dummy devices or devices without fixed MAC
             if ip_mac['MAC'] == '':
                 ip = ip_mac['IP'].split('.')
                 ip_mac['MAC'] = (f'ff:ff:ff:{int(ip[1]):02x}:'
@@ -56,23 +61,35 @@ host {{Lab}}-{{Frame}}-S{{Shelf}}-P{{Partition}}-ctl {
                 ip_mac['Controller_MAC'] = (f'ff:ff:ff:{int(ip[1]):02x}:'
                                             f'{int(ip[2]):02x}:'
                                             f'{int(ip[3]):02x}')
+
             tm = Template(self.dhcp_template)
             machine_ip = tm.render(ip_mac)
-            dhcp_conf_data += machine_ip
-        with open(dhcp_conf, 'w') as out:
-            out.write(dhcp_conf_data)
+            if ip_mac['Lab'] not in dhcp_conf_data:
+                dhcp_conf_data[ip_mac['Lab']] = machine_ip
+            else:
+                dhcp_conf_data[ip_mac['Lab']] += machine_ip
 
+        for lab_id in dhcp_conf_data:
+            with open(os.path.join(dhcp_conf_path, 'cert-' + lab_id.lower() + '-dhcpd-pool.conf'), 'w') as out:
+                out.write(dhcp_conf_data[lab_id])
 
-def main():
 
-    ci = c3query.configuration.get_instance()
-    ci.read_configuration('my_conf.ini')
+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('--cctoolbox', help="Path to cc-tool-box for storing dhcp config", **environ_or_required('CCLM_CC_TOOL_BOX'))
+    parser.add_argument('--hwdb', help="Hardware db to read ip mapping", **environ_or_required('CCLM_HARDWARE_DB'))
+    args = parser.parse_args()
 
-    hwdb = ci.config['CC-LAB-MANAGER']['HWDB']
-    dhcp_conf = ci.config['CC-LAB-MANAGER']['DHCP_CONFIG']
-    ip_mac_list = read_data_from_db(hwdb)
+    ip_mac_list = read_data_from_db(args.hwdb)
     dm = DhcpManager()
-    dm.generate_dhcp_conf(ip_mac_list, dhcp_conf)
+    dm.generate_dhcp_conf(ip_mac_list, os.path.join(args.cctoolbox, 'config', 'dhcp'))
 
 
 if __name__ == '__main__':
diff --git a/build/lib/cc_lab_manager/gen_config/__init__.py b/build/lib/cc_lab_manager/gen_config/__init__.py
 new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/build/lib/cc_lab_manager/gen_config/__init__.py
diff --git a/cc-lab-manager/gen-config/gen-agent-tf-config.py b/build/lib/cc_lab_manager/gen_config/gen_agent_tf_config.py
 similarity index 73%
 rename from cc-lab-manager/gen-config/gen-agent-tf-config.py
 rename to build/lib/cc_lab_manager/gen_config/gen_agent_tf_config.py
index 337fe3e..8056656 100644
--- a/cc-lab-manager/gen-config/gen-agent-tf-config.py
+++ b/build/lib/cc_lab_manager/gen_config/gen_agent_tf_config.py
@@ -6,7 +6,7 @@ import argparse
 agent_config = {"device_ip": None,
         "secure_id": None,
         "node_id": None,
-        "maas_user": "sru-pool",
+        "maas_user": "cert_maas_admin",
         "agent_name": None,
         "node_name": None,
         "reset_efi": "true",
@@ -65,27 +65,32 @@ def read_data_from_db(cursor):
     results = cursor.fetchall()
     return list(results)
 
-def create_agent_config_dir(db_machine_list):
+def create_agent_config_dir(db_machine_list, cfg_path):
     
     for machine in db_machine_list:
         
-        agent_name = "sru"+machine["cid"].replace("-","")
+        if machine['lab'] == '':
+            continue
+        agent_name = "dut"+machine["cid"].replace("-","")
         try:
-            os.makedirs("{}".format(agent_name))
+            os.makedirs(os.path.join(cfg_path, 'data-' + machine['lab'].lower(), agent_name))
         except:
             print("dir exist")
 
-def generate_agent_config(db_machine_list):
+def generate_agent_config(db_machine_list, cfg_path):
     
     for machine in db_machine_list:
         
+        if machine['lab'] == '':
+            continue
         agent_conf = agent_config.copy()
-        agent_name = "sru" + machine["cid"].replace("-","")
+        agent_name = "dut" + machine["cid"].replace("-","")
         secure_id = machine["SecureID"]
         ip = machine["IP"]
         node_id = machine["MAAS_Node_ID"]
-        node_name = machine["Model"].replace(")","",).replace("(","",).replace(" ","-")+ "-" + \
-            machine["sku"].replace(" ","").split("-")[-1] + "-" + machine["cid"]
+        # node_name = machine["Model"].replace(")","",).replace("(","",).replace(" ","-")+ "-" + \
+        #    machine["sku"].replace(" ","").split("-")[-1] + "-" + machine["cid"]
+        node_name = machine["cid"]
 
         agent_conf["node_name"] = node_name.lower()
         agent_conf["agent_name"] = agent_name
@@ -95,7 +100,7 @@ def generate_agent_config(db_machine_list):
         agent_conf["env"]["HEXR_DEVICE_SECURE_ID"] = secure_id
         agent_conf["env"]["DEVICE_IP"] = ip
         
-        with open("{}/default.yaml".format(agent_name), "w+") as f:
+        with open(os.path.join(cfg_path, 'data-' + machine['lab'].lower(), agent_name, 'default.yaml'), "w+") as f:
             
             for key in agent_config:
                 if key == "env":
@@ -108,36 +113,39 @@ def generate_agent_config(db_machine_list):
                     f.write("{key}: {value}\n".format(key=key,value=agent_conf[key]))
 
         
-def generate_tf_config(db_machine_list):
+def generate_tf_config(db_machine_list, cfg_path):
         
     for machine in db_machine_list:
+        if machine['lab'] == '':
+            continue
         tf_conf = tf_config.copy()
         
-        agent_name = "sru" + machine["cid"].replace("-","")
+        agent_name = "dut" + machine["cid"].replace("-","")
         tf_conf["agent_id"] = agent_name
         tf_conf["execution_basedir"] = tf_conf["execution_basedir"].format(agent_name)
         tf_conf["logging_basedir"] = tf_conf["logging_basedir"].format(agent_name)
         tf_conf["results_basedir"] = tf_conf["results_basedir"].format(agent_name)
         
-        with open("{}/testflinger-agent.conf".format(agent_name), "w+") as f:
+        with open(os.path.join(cfg_path, 'data-' + machine['lab'].lower(), agent_name, 'testflinger-agent.conf'), "w+") as f:
             for key in tf_conf:
                 if key == "job_queues":
                     f.write(key+":\n"+"- "+machine["cid"] + "\n")
                 else:
                     f.write(key + ": " + tf_conf[key] + "\n")
                     
-def create_agent_yaml(db_machine_list):
+def create_agent_yaml(db_machine_list, cfg_path):
     
-    if not os.path.isfile("ce-tf-agents.yaml"):
+    # TODO: We need to separate configs for labs
+    if not os.path.isfile(os.path.join(cfg_path, "ce-tf-agents-tel-l5.yaml")):
         
-        with open("ce-tf-agents.yaml","w") as f:
+        with open(os.path.join(cfg_path, "ce-tf-agents-tel-l5.yaml"),"w") as f:
             data = {
                 "series": "focal",
                 "description": "cert-testflinger-agents",
                 "applications":{
-                      "agent-host-server":{
+                      "agent-host-server" + "-tel-l5":{
                         "series": "focal",
-                        "constraints": "tags=agent-host",
+                        "constraints": "tags=agent-host" + "-tel-l5",
                         "charm": "../charms/testflinger-agent-host-charm",
                         "num_units": 1,
                         "resources":{
@@ -149,19 +157,21 @@ def create_agent_yaml(db_machine_list):
             }
             yaml.dump(data, f, default_flow_style=False, encoding='utf-8', allow_unicode=True, sort_keys=False)
 
-    with open("ce-tf-agents.yaml","r+") as f:
+    with open(os.path.join(cfg_path, "ce-tf-agents-tel-l5.yaml"),"r+") as f:
         
         data = yaml.safe_load(f)
         for machine in db_machine_list:
-            agent_name = "sru" + machine["cid"].replace("-","")
+            if machine['lab'] == '':
+                continue
+            agent_name = "dut" + machine["cid"].replace("-","")
             if agent_name in data["applications"]:
                 print("exist")
             else:
                 data["applications"][agent_name] = {
                     "charm":"../charms/testflinger-agent-charm",
                     "resources":{
-                            "device_configfile":"data/{}/default.yaml".format(agent_name),
-                            "testflinger_agent_configfile":"data/{}/testflinger-agent.conf".format(agent_name)
+                            "device_configfile":os.path.join(cfg_path, 'data-' + machine['lab'].lower(), agent_name, 'default.yaml'),
+                            "testflinger_agent_configfile":os.path.join(cfg_path, 'data-' + machine['lab'].lower(), agent_name, 'testflinger-agent.conf')
                         },
                     "to": ["agent-host-server"]
                 }
@@ -183,10 +193,10 @@ def main():
     db = get_db_obj(args.database)
     cursor = get_cursor_obj(db)
     machine_list = read_data_from_db(cursor)
-    create_agent_config_dir(machine_list)
-    generate_agent_config(machine_list)
-    generate_tf_config(machine_list)
-    create_agent_yaml(machine_list)
+    create_agent_config_dir(machine_list, os.path.join(os.environ.get('CCLM_CERT_JUJU'), 'ce-cert'))
+    generate_agent_config(machine_list, os.path.join(os.environ.get('CCLM_CERT_JUJU'), 'ce-cert'))
+    generate_tf_config(machine_list, os.path.join(os.environ.get('CCLM_CERT_JUJU'), 'ce-cert'))
+    create_agent_yaml(machine_list, os.path.join(os.environ.get('CCLM_CERT_JUJU'), 'ce-cert'))
     close_db(db)
 
 if __name__ == '__main__':
diff --git a/build/lib/cc_lab_manager/gsheet_db/__init__.py b/build/lib/cc_lab_manager/gsheet_db/__init__.py
 new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/build/lib/cc_lab_manager/gsheet_db/__init__.py
diff --git a/cc-lab-manager/gsheet-db/db-gsheet.py b/build/lib/cc_lab_manager/gsheet_db/db_gsheet.py
 similarity index 92%
 rename from cc-lab-manager/gsheet-db/db-gsheet.py
 rename to build/lib/cc_lab_manager/gsheet_db/db_gsheet.py
index a0c7100..c535445 100644
--- a/cc-lab-manager/gsheet-db/db-gsheet.py
+++ b/build/lib/cc_lab_manager/gsheet_db/db_gsheet.py
@@ -35,9 +35,9 @@ def iter_all_strings():
             if  tmp_string == "az":
                 return col_position_list
 
-def get_column_to_be_updated():
+def get_column_to_be_updated(token_file):
     
-    gc = pygsheets.authorize(service_file='token.json')
+    gc = pygsheets.authorize(service_file=token_file)
     sht = gc.open_by_url(gsheet_url)
     wks = sht[0]
     machine_list = wks.get_all_records(empty_value='', head=1, majdim='ROWS', numericise_data=False)
@@ -61,7 +61,7 @@ def sync_c3_data_to_sheet(db_machine_list,token_file):
     wks = sht[0]
     
     # mapping table for position of column 
-    col_to_be_updated = get_column_to_be_updated()
+    col_to_be_updated = get_column_to_be_updated(token_file)
     
     row_num = 2
     cell_list = [] # list for storing cell that are waiting for update.
@@ -73,9 +73,9 @@ def sync_c3_data_to_sheet(db_machine_list,token_file):
     wks.update_cells(cell_list)
         
 
-def read_data_from_db(cursor):
+def read_data_from_db(cursor, token_file):
     
-    col_in_c3 = list(get_column_to_be_updated().keys())
+    col_in_c3 = list(get_column_to_be_updated(token_file).keys())
     sqlcmd = 'select {} from lab_hw'.format(",".join(col_in_c3[0:]))
     cursor.execute(sqlcmd)
     results = cursor.fetchall()
@@ -99,9 +99,9 @@ def main():
     gsheet_url = args.url
     db = get_db_obj(args.database)
     cursor = get_cursor_obj(db)    
-    sync_c3_data_to_sheet(read_data_from_db(cursor),args.token)
+    sync_c3_data_to_sheet(read_data_from_db(cursor, args.token),args.token)
     close_db(db)
 
 
 if __name__ == '__main__':
-        main()
 \ No newline at end of file
+        main()
diff --git a/cc-lab-manager/gsheet-db/gsheet-db.py b/build/lib/cc_lab_manager/gsheet_db/gsheet_db.py
 similarity index 100%
 rename from cc-lab-manager/gsheet-db/gsheet-db.py
 rename to build/lib/cc_lab_manager/gsheet_db/gsheet_db.py
diff --git a/build/lib/cc_lab_manager/maas/__init__.py b/build/lib/cc_lab_manager/maas/__init__.py
 new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/build/lib/cc_lab_manager/maas/__init__.py
diff --git a/cc-lab-manager/maas/create-maas-node.py b/build/lib/cc_lab_manager/maas/create_maas_node.py
 similarity index 88%
 rename from cc-lab-manager/maas/create-maas-node.py
 rename to build/lib/cc_lab_manager/maas/create_maas_node.py
index e57d8a5..05be6dc 100644
--- a/cc-lab-manager/maas/create-maas-node.py
+++ b/build/lib/cc_lab_manager/maas/create_maas_node.py
@@ -33,12 +33,11 @@ def read_data_from_db(cursor):
     
     return list(results)
 
-def get_maas_session(maas_ip):
+def get_maas_session(maas_ip, maas_admin, maas_admin_password):
     client = login(
     "http://{}:5240/MAAS/".format(maas_ip),
-    username="ubuntu", password="ubuntu",
+    username=maas_admin, password=maas_admin_password,
     )
-    print(type(client))
     return client
 
 def get_mac_list(machine_list):
@@ -63,7 +62,7 @@ def get_maas_mac_list(client):
     
     return mac_list
 
-def create_maas_node(machine_list):
+def create_maas_node(machine_list, maas_admin, maas_admin_password):
     
     mass_is_connecting = ""
     client = ""
@@ -72,28 +71,28 @@ def create_maas_node(machine_list):
         
         if not mass_is_connecting:
             mass_is_connecting = machine["MAAS_Server"]
-            client = get_maas_session(mass_is_connecting)
+            client = get_maas_session(mass_is_connecting, maas_admin, maas_admin_password)
             maas_mac_list = get_maas_mac_list(client)
         elif machine["MAAS_Server"] != mass_is_connecting:
             mass_is_connecting = machine["MAAS_Server"]
-            client = get_maas_session(mass_is_connecting)
+            client = get_maas_session(mass_is_connecting, maas_admin, maas_admin_password)
             maas_mac_list = get_maas_mac_list(client)
         
         power_type = machine["Power"].lower() if machine["Power"] != "" else "manual"
         power_parameter = {}
         node_name = (machine["Model"].replace(")","",).replace("(","",).replace(" ","-")+ "-" + \
             machine["sku"].replace(" ","").split("-")[-1] + "-" + machine["cid"]).lower()
+        node_name = machine["cid"].lower()
         
-        if machine["MAC"] in maas_mac_list:
+        if machine["MAC"] in maas_mac_list or machine["MAC"] == '':
             continue
         if power_type == "apc" or power_type == "raritan":
             power_parameter["power_address"] = machine["PDU_IP"]
             power_parameter["node_outlet"] = machine["PDU_Outlet"]
             power_parameter["power_on_delay"] = 30
-            
         elif power_type == "amt":
             power_parameter["power_address"] = machine["IP"]
-            power_parameter["power_pass"] = "Insecure_101"
+            power_parameter["power_pass"] = "Insecure-101"
         
         maas_node = client.machines.create("amd64",machine["MAC"], "manual",hostname=node_name)
        
@@ -111,11 +110,13 @@ def main():
     
     parser = argparse.ArgumentParser()
     parser.add_argument('--database', help="Database location",**environ_or_required('CCLM_HARDWARE_DB'))
+    parser.add_argument('--maas_admin', help="MAAS Admin account",**environ_or_required('CCLM_MAAS_ADMIN'))
+    parser.add_argument('--maas_admin_password', help="Password for MAAS Admin account",**environ_or_required('CCLM_MAAS_ADMIN_PASSWD'))
     args = parser.parse_args()
     db = get_db_obj(args.database)
     cursor = get_cursor_obj(db)
     machine_list = read_data_from_db(cursor)
-    create_maas_node(machine_list)
+    create_maas_node(machine_list, args.maas_admin, args.maas_admin_password)
     update_node_id(cursor)
     close_db(db)
 
diff --git a/cc_lab_manager.egg-info/PKG-INFO b/cc_lab_manager.egg-info/PKG-INFO
 new file mode 100644
index 0000000..03809d0
--- /dev/null
+++ b/cc_lab_manager.egg-info/PKG-INFO
@@ -0,0 +1,11 @@
+Metadata-Version: 1.1
+Name: cc-lab-manager
+Version: 0.1
+Summary: Automated managing hardwares in Canonical Certification Labs
+Home-page: https://code.launchpad.net/cc-lab-manager/
+Author: Gavin Lin (gavin.lin)
+Author-email: [e-mail]
+License: UNKNOWN
+Description: UNKNOWN
+Platform: UNKNOWN
+Classifier: Programming Language :: Python
diff --git a/cc_lab_manager.egg-info/SOURCES.txt b/cc_lab_manager.egg-info/SOURCES.txt
 new file mode 100644
index 0000000..7cfee1b
--- /dev/null
+++ b/cc_lab_manager.egg-info/SOURCES.txt
@@ -0,0 +1,23 @@
+README.md
+setup.cfg
+setup.py
+cc_lab_manager/__init__.py
+cc_lab_manager.egg-info/PKG-INFO
+cc_lab_manager.egg-info/SOURCES.txt
+cc_lab_manager.egg-info/dependency_links.txt
+cc_lab_manager.egg-info/entry_points.txt
+cc_lab_manager.egg-info/requires.txt
+cc_lab_manager.egg-info/top_level.txt
+cc_lab_manager/c3_db/__init__.py
+cc_lab_manager/c3_db/c3_db.py
+cc_lab_manager/commands/__init__.py
+cc_lab_manager/commands/cc_lab_manager.py
+cc_lab_manager/dhcp/__init__.py
+cc_lab_manager/dhcp/dhcp_config_generator.py
+cc_lab_manager/gen_config/__init__.py
+cc_lab_manager/gen_config/gen_agent_tf_config.py
+cc_lab_manager/gsheet_db/__init__.py
+cc_lab_manager/gsheet_db/db_gsheet.py
+cc_lab_manager/gsheet_db/gsheet_db.py
+cc_lab_manager/maas/__init__.py
+cc_lab_manager/maas/create_maas_node.py
 \ No newline at end of file
diff --git a/cc_lab_manager.egg-info/dependency_links.txt b/cc_lab_manager.egg-info/dependency_links.txt
 new file mode 100644
index 0000000..8b13789
--- /dev/null
+++ b/cc_lab_manager.egg-info/dependency_links.txt
@@ -0,0 +1 @@
+
diff --git a/cc_lab_manager.egg-info/entry_points.txt b/cc_lab_manager.egg-info/entry_points.txt
 new file mode 100644
index 0000000..21bd2e3
--- /dev/null
+++ b/cc_lab_manager.egg-info/entry_points.txt
@@ -0,0 +1,3 @@
+[console_scripts]
+cc-lab-manager = cc_lab_manager.commands.cc_lab_manager:main
+
diff --git a/cc_lab_manager.egg-info/requires.txt b/cc_lab_manager.egg-info/requires.txt
 new file mode 100644
index 0000000..26dccbc
--- /dev/null
+++ b/cc_lab_manager.egg-info/requires.txt
@@ -0,0 +1,4 @@
+aiohttp==3.7.2
+jinja2
+pysheets
+python-libmaas
diff --git a/cc_lab_manager.egg-info/top_level.txt b/cc_lab_manager.egg-info/top_level.txt
 new file mode 100644
index 0000000..b831c50
--- /dev/null
+++ b/cc_lab_manager.egg-info/top_level.txt
@@ -0,0 +1 @@
+cc_lab_manager
diff --git a/cc_lab_manager/__init__.py b/cc_lab_manager/__init__.py
 new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/cc_lab_manager/__init__.py
diff --git a/cc_lab_manager/c3_db/__init__.py b/cc_lab_manager/c3_db/__init__.py
 new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/cc_lab_manager/c3_db/__init__.py
diff --git a/cc_lab_manager/c3_db/c3_db.py b/cc_lab_manager/c3_db/c3_db.py
 new file mode 100644
index 0000000..b9b0d7e
--- /dev/null
+++ b/cc_lab_manager/c3_db/c3_db.py
@@ -0,0 +1,128 @@
+import sqlite3
+import logging
+import argparse
+import c3.api.query as c3query
+from c3.api.api_utils import APIQuery
+import os
+
+logger = logging.getLogger('cc-lab-manager')
+
+
+def c3_to_db(db_path):
+    """
+    Query machine info from c3 and write to db.
+    """
+
+    # Read CID from db
+    try:
+        hwdb = sqlite3.connect(db_path)
+        cur = hwdb.execute('SELECT cid FROM lab_hw')
+        cid_list = cur.fetchall()
+    except Exception as e:
+        logger.error('Error when reading db: ', e)
+
+    for cid_db in cid_list:
+        cid = ''.join(cid_db)
+        logger.info('Processing {}...'.format(cid))
+        if '-' not in cid or 'TEL' in cid:
+            continue
+
+        # Query hardware info from c3
+        result = ''
+        try:
+            result = c3query.query_over_api_hardware(cid)
+        except Exception as e:
+            logger.error("Error when query c3: ", e)
+
+        if result:
+            # Map hardware info from c3 to db column
+            try:
+                location = result['location']['name']
+            except Exception as e:
+                logger.warning('Failed Reading location: {}'.format(e))
+                location = 'None'
+            try:
+                account = result['account']['name']
+            except Exception as e:
+                logger.warning('Failed reading account: {}'.format(e))
+                account = 'None'
+            try:
+                platform_name = result['platform']['name']
+                codename = result['platform']['codename']
+                form_factor = result['platform']['form_factor']
+                model = result['platform']['aliases']
+            except Exception as e:
+                logger.warning('Failed reading platform: {}'.format(e))
+                platform_name = 'None'
+                codename = 'None'
+                form_factor = 'None'
+                model = 'None'
+            try:
+                canonical_contact = result['canonical_contact']['display_name']
+            except Exception as e:
+                logger.warning('Failed reading canonical_contact: {}'
+                               .format(e))
+                canonical_contact = 'None'
+            try:
+                holder = result['holder']['name']
+            except Exception as e:
+                logger.warning('Failed reading holder: {}'.format(e))
+                holder = 'None'
+            hw_info = {'Location': location,
+                       'Status': result['status'],
+                       'Account': account,
+                       'Project': result['project_name'],
+                       'Model': model,
+                       'CanonicalLabel': result['canonical_label'],
+                       'PlatformName': platform_name,
+                       'CodeName': codename,
+                       'SKU': result['sku'],
+                       'HardwareBuild': result['hardware_build'],
+                       'Form': form_factor,
+                       'CanonicalContact': canonical_contact,
+                       'Holder': holder,
+                       'SecureID': result['secure_id']}
+
+            # Write hardware info to db
+            try:
+                for key in hw_info:
+                    sql_cmd = 'UPDATE lab_hw SET {} = "{}" WHERE CID = "{}"' \
+                              .format(key, hw_info[key],
+                                      result['canonical_id'])
+                    hwdb.execute(sql_cmd)
+                hwdb.commit()
+            except Exception as e:
+                logger.error('Error writing to db: ', e)
+                hwdb.close()
+
+    hwdb.close()
+
+
+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('--conf', help="Config file for C3 API", default = os.environ.get('CCLM_CC_TOOL_BOX') + '/config/c3-cli/my_conf.ini')
+    parser.add_argument('--c3user', help="C3 User Account", **environ_or_required('CCLM_C3_USERNAME'))
+    parser.add_argument('--c3apikey', help="C3 API Key", **environ_or_required('CCLM_C3_APIKEY'))
+    parser.add_argument('--hwdb', help="C3 API Key", **environ_or_required('CCLM_HARDWARE_DB'))
+    args = parser.parse_args()
+
+    ci = c3query.configuration.get_instance()
+    ci.read_configuration(args.conf)
+
+    api = APIQuery(ci.config['C3']['URI'])
+    rparam = {"username": args.c3user,
+              "api_key": args.c3apikey}
+
+    c3query.api_instance.set_api_params(api, rparam)
+    c3_to_db(args.hwdb)
+
+
+if __name__ == '__main__':
+    main()
diff --git a/cc_lab_manager/commands/__init__.py b/cc_lab_manager/commands/__init__.py
 new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/cc_lab_manager/commands/__init__.py
diff --git a/cc_lab_manager/commands/cc_lab_manager.py b/cc_lab_manager/commands/cc_lab_manager.py
 new file mode 100644
index 0000000..7da03df
--- /dev/null
+++ b/cc_lab_manager/commands/cc_lab_manager.py
@@ -0,0 +1,49 @@
+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.maas import create_maas_node
+from cc_lab_manager.gen_config import gen_agent_tf_config
+
+logger = logging.getLogger('cc-lab-manager')
+
+def main():
+    # Read data from the google sheet to db
+    gsheet_db.main()
+
+    # Get needed information from c3
+    c3_db.main()
+
+    # Generate DHCP config for each subnets
+    # TODO: Currently these config files need to be copied
+    # manually, then MAAS will apply it automatically
+    dhcp_config_generator.main()
+
+    # Create MAAS nodes for DUTs
+    create_maas_node.main()
+
+    # Create agent configs for DUTs
+    # TODO: Currently these configs need to be push/pull
+    # and applied manually
+    gen_agent_tf_config.main()
+
+    # Write data in db back to the google sheet
+    db_gsheet.main()
+
+    #ci = c3query.configuration.get_instance()
+
+    #api = APIQuery(ci.config['C3']['URI'])
+    #rparam = {"username": ci.config['C3']['UserName'],
+    #          "api_key": ci.config['C3']['APIKey']}
+
+    #c3query.api_instance.set_api_params(api, rparam)
+    #hwdb = './hardware.db'
+    #c3_to_db(hwdb)
+
+
+if __name__ == '__main__':
+    main()
diff --git a/cc_lab_manager/dhcp/__init__.py b/cc_lab_manager/dhcp/__init__.py
 new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/cc_lab_manager/dhcp/__init__.py
diff --git a/cc_lab_manager/dhcp/dhcp_config_generator.py b/cc_lab_manager/dhcp/dhcp_config_generator.py
 new file mode 100644
index 0000000..e586dd4
--- /dev/null
+++ b/cc_lab_manager/dhcp/dhcp_config_generator.py
@@ -0,0 +1,96 @@
+import sqlite3
+import logging
+import argparse
+import os
+from jinja2 import Template
+
+logger = logging.getLogger('cc-lab-manager')
+
+
+def read_data_from_db(path_to_db):
+    db = sqlite3.connect(path_to_db)
+    db.row_factory = sqlite3.Row
+    col_in_db = ["CID", "MAC", "Controller_MAC", "Provision", "IP",
+                 "Controller_IP", "Lab", "Frame", "Shelf", "Partition"]
+    sqlcmd = ('select {},{},{},{},{},{},{},{},{},{} from lab_hw'
+              .format(*col_in_db))
+    db_row_objs = db.execute(sqlcmd).fetchall()
+    db.close()
+    db_dict_list = []
+    for row_obj in db_row_objs:
+        db_dict_list.append({k: row_obj[k] for k in row_obj.keys()})
+    return db_dict_list
+
+
+class DhcpManager:
+    def __init__(self):
+        self.dhcp_template = '''
+# {{CID}}
+host {{Lab}}-{{Frame}}-S{{Shelf}}-P{{Partition}} {
+  hardware ethernet {{MAC}};
+  fixed-address {{IP}};
+}
+
+# {{CID}}-controller
+host {{Lab}}-{{Frame}}-S{{Shelf}}-P{{Partition}}-ctl {
+  hardware ethernet {{Controller_MAC}};
+  fixed-address {{Controller_IP}};
+}
+
+'''
+
+    def generate_dhcp_conf(self, ip_mac_list, dhcp_conf_path):
+        dhcp_conf_data = {}
+        for ip_mac in ip_mac_list:
+            if ip_mac['IP'] == '' or ip_mac['Controller_IP'] == ''\
+               or ip_mac['Lab'] == '' or ip_mac['Frame'] == ''\
+               or ip_mac['Shelf'] == '' or ip_mac['Partition'] == '':
+                if ip_mac['CID'] == '':
+                    ip_mac_list.remove(ip_mac)
+                else:
+                    logger.info("Missing required information for", ip_mac['CID'])
+                continue
+
+            # Generate MAC for dummy devices or devices without fixed MAC
+            if ip_mac['MAC'] == '':
+                ip = ip_mac['IP'].split('.')
+                ip_mac['MAC'] = (f'ff:ff:ff:{int(ip[1]):02x}:'
+                                 f'{int(ip[2]):02x}:{int(ip[3]):02x}')
+            if ip_mac['Controller_MAC'] == '':
+                ip = ip_mac['Controller_IP'].split('.')
+                ip_mac['Controller_MAC'] = (f'ff:ff:ff:{int(ip[1]):02x}:'
+                                            f'{int(ip[2]):02x}:'
+                                            f'{int(ip[3]):02x}')
+
+            tm = Template(self.dhcp_template)
+            machine_ip = tm.render(ip_mac)
+            if ip_mac['Lab'] not in dhcp_conf_data:
+                dhcp_conf_data[ip_mac['Lab']] = machine_ip
+            else:
+                dhcp_conf_data[ip_mac['Lab']] += machine_ip
+
+        for lab_id in dhcp_conf_data:
+            with open(os.path.join(dhcp_conf_path, 'cert-' + lab_id.lower() + '-dhcpd-pool.conf'), 'w') as out:
+                out.write(dhcp_conf_data[lab_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('--cctoolbox', help="Path to cc-tool-box for storing dhcp config", **environ_or_required('CCLM_CC_TOOL_BOX'))
+    parser.add_argument('--hwdb', help="Hardware db to read ip mapping", **environ_or_required('CCLM_HARDWARE_DB'))
+    args = parser.parse_args()
+
+    ip_mac_list = read_data_from_db(args.hwdb)
+    dm = DhcpManager()
+    dm.generate_dhcp_conf(ip_mac_list, os.path.join(args.cctoolbox, 'config', 'dhcp'))
+
+
+if __name__ == '__main__':
+    main()
diff --git a/cc_lab_manager/gen_config/__init__.py b/cc_lab_manager/gen_config/__init__.py
 new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/cc_lab_manager/gen_config/__init__.py
diff --git a/cc_lab_manager/gen_config/gen_agent_tf_config.py b/cc_lab_manager/gen_config/gen_agent_tf_config.py
 new file mode 100644
index 0000000..8056656
--- /dev/null
+++ b/cc_lab_manager/gen_config/gen_agent_tf_config.py
@@ -0,0 +1,203 @@
+import sqlite3
+import os
+import yaml
+import argparse
+
+agent_config = {"device_ip": None,
+        "secure_id": None,
+        "node_id": None,
+        "maas_user": "cert_maas_admin",
+        "agent_name": None,
+        "node_name": None,
+        "reset_efi": "true",
+        "env":{"HEXR_DEVICE_SECURE_ID": None,
+               "WPA_BG_SSID": "ubuntu-cert-bg-wpa-tpelab",
+               "WPA_BG_PSK": "insecure",
+               "WPA_N_SSID": "ubuntu-cert-n-wpa-tpelab",
+               "WPA_N_PSK": "insecure",
+               "WPA_AC_SSID": "ubuntu-cert-ac-wpa-tpelab",
+               "WPA_AC_PSK": "insecure",
+               "OPEN_BG_SSID": "ubuntu-cert-bg-open-tpelab",
+               "OPEN_N_SSID": "ubuntu-cert-n-open-tpelab",
+               "OPEN_AC_SSID": "ubuntu-cert-ac-open-tpelab",
+               "DEVICE_IP": None
+               }
+        }
+
+tf_config = {"agent_id": None, 
+             "server_address": "https://testflinger.canonical.com", 
+             "global_timeout": "43200", 
+             "output_timeout": "8000", 
+             "execution_basedir": "/home/ubuntu/testflinger/{}/run", 
+             "logging_basedir": "/home/ubuntu/testflinger/{}/logs", 
+             "results_basedir": "/home/ubuntu/testflinger/{}/results", 
+             "logging_level": "INFO", 
+             "job_queues": None,
+             "setup_command": "tf-setup", 
+             "provision_command": "tf-provision", 
+             "test_command": "tf-test", 
+             "reserve_command": "tf-reserve", 
+             "cleanup_command": "tf-cleanup", 
+             "provision_type": "maas2"
+             }
+
+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","Lab","IP","SKU","MAAS_Node_ID","Model","SecureID"]
+    
+    sqlcmd = 'select {},{},{},{},{},{},{} from lab_hw where CID not like \'TEL-%\''.format(*col_in_c3)
+    cursor.execute(sqlcmd)
+    results = cursor.fetchall()
+    return list(results)
+
+def create_agent_config_dir(db_machine_list, cfg_path):
+    
+    for machine in db_machine_list:
+        
+        if machine['lab'] == '':
+            continue
+        agent_name = "dut"+machine["cid"].replace("-","")
+        try:
+            os.makedirs(os.path.join(cfg_path, 'data-' + machine['lab'].lower(), agent_name))
+        except:
+            print("dir exist")
+
+def generate_agent_config(db_machine_list, cfg_path):
+    
+    for machine in db_machine_list:
+        
+        if machine['lab'] == '':
+            continue
+        agent_conf = agent_config.copy()
+        agent_name = "dut" + machine["cid"].replace("-","")
+        secure_id = machine["SecureID"]
+        ip = machine["IP"]
+        node_id = machine["MAAS_Node_ID"]
+        # node_name = machine["Model"].replace(")","",).replace("(","",).replace(" ","-")+ "-" + \
+        #    machine["sku"].replace(" ","").split("-")[-1] + "-" + machine["cid"]
+        node_name = machine["cid"]
+
+        agent_conf["node_name"] = node_name.lower()
+        agent_conf["agent_name"] = agent_name
+        agent_conf["secure_id"] = secure_id
+        agent_conf["node_id"] = node_id
+        agent_conf["device_ip"] = ip
+        agent_conf["env"]["HEXR_DEVICE_SECURE_ID"] = secure_id
+        agent_conf["env"]["DEVICE_IP"] = ip
+        
+        with open(os.path.join(cfg_path, 'data-' + machine['lab'].lower(), agent_name, 'default.yaml'), "w+") as f:
+            
+            for key in agent_config:
+                if key == "env":
+                    f.write(key+" :\n")
+                    for env_key in agent_config["env"]:
+                        if "SSID" in env_key:
+                            agent_conf["env"][env_key] += machine["lab"][-1]
+                        f.write("  {key}: {value}\n".format(key=env_key,value=agent_conf["env"][env_key]))
+                else:
+                    f.write("{key}: {value}\n".format(key=key,value=agent_conf[key]))
+
+        
+def generate_tf_config(db_machine_list, cfg_path):
+        
+    for machine in db_machine_list:
+        if machine['lab'] == '':
+            continue
+        tf_conf = tf_config.copy()
+        
+        agent_name = "dut" + machine["cid"].replace("-","")
+        tf_conf["agent_id"] = agent_name
+        tf_conf["execution_basedir"] = tf_conf["execution_basedir"].format(agent_name)
+        tf_conf["logging_basedir"] = tf_conf["logging_basedir"].format(agent_name)
+        tf_conf["results_basedir"] = tf_conf["results_basedir"].format(agent_name)
+        
+        with open(os.path.join(cfg_path, 'data-' + machine['lab'].lower(), agent_name, 'testflinger-agent.conf'), "w+") as f:
+            for key in tf_conf:
+                if key == "job_queues":
+                    f.write(key+":\n"+"- "+machine["cid"] + "\n")
+                else:
+                    f.write(key + ": " + tf_conf[key] + "\n")
+                    
+def create_agent_yaml(db_machine_list, cfg_path):
+    
+    # TODO: We need to separate configs for labs
+    if not os.path.isfile(os.path.join(cfg_path, "ce-tf-agents-tel-l5.yaml")):
+        
+        with open(os.path.join(cfg_path, "ce-tf-agents-tel-l5.yaml"),"w") as f:
+            data = {
+                "series": "focal",
+                "description": "cert-testflinger-agents",
+                "applications":{
+                      "agent-host-server" + "-tel-l5":{
+                        "series": "focal",
+                        "constraints": "tags=agent-host" + "-tel-l5",
+                        "charm": "../charms/testflinger-agent-host-charm",
+                        "num_units": 1,
+                        "resources":{
+                            "ssh_priv_key": "agent-host/id_rsa",
+                            "ssh_pub_key": "agent-host/id_rsa.pub",
+                        },
+                    }
+                }
+            }
+            yaml.dump(data, f, default_flow_style=False, encoding='utf-8', allow_unicode=True, sort_keys=False)
+
+    with open(os.path.join(cfg_path, "ce-tf-agents-tel-l5.yaml"),"r+") as f:
+        
+        data = yaml.safe_load(f)
+        for machine in db_machine_list:
+            if machine['lab'] == '':
+                continue
+            agent_name = "dut" + machine["cid"].replace("-","")
+            if agent_name in data["applications"]:
+                print("exist")
+            else:
+                data["applications"][agent_name] = {
+                    "charm":"../charms/testflinger-agent-charm",
+                    "resources":{
+                            "device_configfile":os.path.join(cfg_path, 'data-' + machine['lab'].lower(), agent_name, 'default.yaml'),
+                            "testflinger_agent_configfile":os.path.join(cfg_path, 'data-' + machine['lab'].lower(), agent_name, 'testflinger-agent.conf')
+                        },
+                    "to": ["agent-host-server"]
+                }
+        f.seek(0)       
+        yaml.dump(data, f, default_flow_style=False, encoding='utf-8', allow_unicode=True, sort_keys=False)
+        
+        
+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)
+    machine_list = read_data_from_db(cursor)
+    create_agent_config_dir(machine_list, os.path.join(os.environ.get('CCLM_CERT_JUJU'), 'ce-cert'))
+    generate_agent_config(machine_list, os.path.join(os.environ.get('CCLM_CERT_JUJU'), 'ce-cert'))
+    generate_tf_config(machine_list, os.path.join(os.environ.get('CCLM_CERT_JUJU'), 'ce-cert'))
+    create_agent_yaml(machine_list, os.path.join(os.environ.get('CCLM_CERT_JUJU'), 'ce-cert'))
+    close_db(db)
+
+if __name__ == '__main__':
+        main()
diff --git a/cc_lab_manager/gsheet_db/__init__.py b/cc_lab_manager/gsheet_db/__init__.py
 new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/cc_lab_manager/gsheet_db/__init__.py
diff --git a/cc_lab_manager/gsheet_db/db_gsheet.py b/cc_lab_manager/gsheet_db/db_gsheet.py
 new file mode 100644
index 0000000..c535445
--- /dev/null
+++ b/cc_lab_manager/gsheet_db/db_gsheet.py
@@ -0,0 +1,107 @@
+import pygsheets
+import sqlite3
+import sys
+from string import ascii_lowercase
+import itertools
+import argparse
+import os
+
+gsheet_url = ''
+start_column_name = 'Location'
+
+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 iter_all_strings():
+    # generate alphabet list a,b,c.........az for accessing cell's position.
+
+    col_position_list = []
+    for size in itertools.count(1):
+        for s in itertools.product(ascii_lowercase,repeat = size):
+            tmp_string = "".join(s)
+            col_position_list.append("".join(tmp_string))
+            if  tmp_string == "az":
+                return col_position_list
+
+def get_column_to_be_updated(token_file):
+    
+    gc = pygsheets.authorize(service_file=token_file)
+    sht = gc.open_by_url(gsheet_url)
+    wks = sht[0]
+    machine_list = wks.get_all_records(empty_value='', head=1, majdim='ROWS', numericise_data=False)
+    keylist = list(machine_list[0].keys())
+    
+    started_index = keylist.index(start_column_name)
+    col_to_be_updated = dict.fromkeys(keylist[started_index:])
+    col_position_list = iter_all_strings()
+    
+    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
+    
+
+def sync_c3_data_to_sheet(db_machine_list,token_file):
+    
+    gc = pygsheets.authorize(service_file=token_file)
+    sht = gc.open_by_url(gsheet_url)
+    wks = sht[0]
+    
+    # mapping table for position of column 
+    col_to_be_updated = get_column_to_be_updated(token_file)
+    
+    row_num = 2
+    cell_list = [] # list for storing cell that are waiting for update.
+    for machine in db_machine_list:
+        for key in col_to_be_updated:
+            cell_list.append(pygsheets.Cell(col_to_be_updated[key]+str(row_num),machine[key]))
+
+        row_num += 1
+    wks.update_cells(cell_list)
+        
+
+def read_data_from_db(cursor, token_file):
+    
+    col_in_c3 = list(get_column_to_be_updated(token_file).keys())
+    sqlcmd = 'select {} from lab_hw'.format(",".join(col_in_c3[0:]))
+    cursor.execute(sqlcmd)
+    results = cursor.fetchall()
+    
+    return list(results)
+
+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('--url', help="Google sheets URL",**environ_or_required('CCLM_GOOGLE_SHEET'))
+    parser.add_argument('--token', help="Google API token",**environ_or_required('CCLM_GOOGLE_TOKEN'))
+    parser.add_argument('--database', help="Database location",**environ_or_required('CCLM_HARDWARE_DB'))
+    args = parser.parse_args()
+    global gsheet_url
+    gsheet_url = args.url
+    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)
+    close_db(db)
+
+
+if __name__ == '__main__':
+        main()
diff --git a/cc_lab_manager/gsheet_db/gsheet_db.py b/cc_lab_manager/gsheet_db/gsheet_db.py
 new file mode 100644
index 0000000..1b11014
--- /dev/null
+++ b/cc_lab_manager/gsheet_db/gsheet_db.py
@@ -0,0 +1,91 @@
+import pygsheets
+import sqlite3
+import sys
+import argparse
+import os
+
+gsheet_url = ''
+
+def get_machine_list(token_file):
+    
+    gc = pygsheets.authorize(service_file=token_file)
+    sht = gc.open_by_url(gsheet_url)    
+    wks = sht[0]
+    machine_list = wks.get_all_records(empty_value='', head=1, majdim='ROWS', numericise_data=False)
+    
+    return machine_list
+
+def get_db_obj(path_to_db):
+    
+    db = sqlite3.connect(path_to_db)
+    return db
+
+def get_cursor_obj(db_obj):
+    
+    cursor = db_obj.cursor()
+    return cursor
+
+def close_db(db):
+    db.commit() 
+    db.close()
+
+def check_db_table_exist(cursor,table_name):
+    
+    sqlcmd = 'select name from sqlite_master where type=\'table\' and name="{table_name}"'.format(table_name=table_name)
+    cursor.execute(sqlcmd)
+    result = cursor.fetchone()
+    return result
+
+def write_machine_list_to_db(cursor,token_file):
+
+    machine_list = get_machine_list(token_file)
+    keylist = [*machine_list[0]]
+    create_cmd = "CREATE TABLE lab_hw ({})".format(keylist[0]+ " PRIMARY KEY," +",".join(keylist[1:]))
+    
+    #check table is exist or not
+    if check_db_table_exist(cursor,"lab_hw"):
+        print('table exist')
+        cursor.execute("DELETE FROM lab_hw")
+        
+    else:
+        try:
+            cursor.execute(create_cmd)
+            
+        except sqlite3.Error as er:
+            print('SQLite error: %s' % (' '.join(er.args)))
+   
+    insert_cmd_fmt = 'insert into lab_hw values("{}")'
+
+    for machine in machine_list:
+        if machine["CID"] == "":
+            print("1")
+            machine["CID"] = machine["Lab"] + machine["Frame"] + machine["Shelf"] + machine["Partition"]
+        insert_cmd = insert_cmd_fmt.format('","'.join(machine.values()))
+        try:
+            cursor.execute(insert_cmd)
+            
+        except sqlite3.Error as er:
+            print('SQLite error: %s' % (' '.join(er.args)))
+    
+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('--url', help="Google sheets URL",**environ_or_required('CCLM_GOOGLE_SHEET'))
+    parser.add_argument('--token', help="Google API token",**environ_or_required('CCLM_GOOGLE_TOKEN'))
+    parser.add_argument('--database', help="Database location",**environ_or_required('CCLM_HARDWARE_DB'))
+    args = parser.parse_args()
+    global gsheet_url
+    gsheet_url = args.url
+    db = get_db_obj(args.database)
+    cursor = get_cursor_obj(db)
+    write_machine_list_to_db(cursor,args.token)
+    close_db(db)
+
+if __name__ == '__main__':
+        main()
diff --git a/cc_lab_manager/maas/__init__.py b/cc_lab_manager/maas/__init__.py
 new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/cc_lab_manager/maas/__init__.py
diff --git a/cc_lab_manager/maas/create_maas_node.py b/cc_lab_manager/maas/create_maas_node.py
 new file mode 100644
index 0000000..05be6dc
--- /dev/null
+++ b/cc_lab_manager/maas/create_maas_node.py
@@ -0,0 +1,124 @@
+from doctest import master
+from maas.client import connect
+from maas.client import login
+from maas.client.enum import LinkMode
+import argparse
+import os
+import sqlite3
+
+update_node_id_list = []
+
+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","MAC","Model","SKU","Power","IP","PDU_IP","PDU_Outlet","MAAS_Server","MAAS_Node_ID"]
+    
+    sqlcmd = 'select {} from lab_hw where CID not like \'TEL-%\' order by MAAS_Server'.format(",".join(col_in_c3[0:]))
+    cursor.execute(sqlcmd)
+    results = cursor.fetchall()
+    
+    return list(results)
+
+def get_maas_session(maas_ip, maas_admin, maas_admin_password):
+    client = login(
+    "http://{}:5240/MAAS/".format(maas_ip),
+    username=maas_admin, password=maas_admin_password,
+    )
+    return client
+
+def get_mac_list(machine_list):
+    
+    mac_list = []
+    for machine in machine_list:
+        mac_list.append(machine["mac"])
+        
+    return mac_list
+
+def update_node_id(cursor):
+    
+    sqlcmd = 'update lab_hw set MAAS_Node_ID=? where CID=?'
+    cursor.executemany(sqlcmd, update_node_id_list)
+    
+def get_maas_mac_list(client):
+    
+    mac_list = []
+    for machine in client.machines.list():
+        for key in machine.interfaces.by_name:
+            mac_list.append(machine.interfaces.by_name[key].mac_address)
+    
+    return mac_list
+
+def create_maas_node(machine_list, maas_admin, maas_admin_password):
+    
+    mass_is_connecting = ""
+    client = ""
+    
+    for machine in machine_list:
+        
+        if not mass_is_connecting:
+            mass_is_connecting = machine["MAAS_Server"]
+            client = get_maas_session(mass_is_connecting, maas_admin, maas_admin_password)
+            maas_mac_list = get_maas_mac_list(client)
+        elif machine["MAAS_Server"] != mass_is_connecting:
+            mass_is_connecting = machine["MAAS_Server"]
+            client = get_maas_session(mass_is_connecting, maas_admin, maas_admin_password)
+            maas_mac_list = get_maas_mac_list(client)
+        
+        power_type = machine["Power"].lower() if machine["Power"] != "" else "manual"
+        power_parameter = {}
+        node_name = (machine["Model"].replace(")","",).replace("(","",).replace(" ","-")+ "-" + \
+            machine["sku"].replace(" ","").split("-")[-1] + "-" + machine["cid"]).lower()
+        node_name = machine["cid"].lower()
+        
+        if machine["MAC"] in maas_mac_list or machine["MAC"] == '':
+            continue
+        if power_type == "apc" or power_type == "raritan":
+            power_parameter["power_address"] = machine["PDU_IP"]
+            power_parameter["node_outlet"] = machine["PDU_Outlet"]
+            power_parameter["power_on_delay"] = 30
+        elif power_type == "amt":
+            power_parameter["power_address"] = machine["IP"]
+            power_parameter["power_pass"] = "Insecure-101"
+        
+        maas_node = client.machines.create("amd64",machine["MAC"], "manual",hostname=node_name)
+       
+        update_node_id_list.append([maas_node.system_id, machine["CID"]])
+        maas_node.set_power(power_type=power_type.lower(),power_parameters=power_parameter)
+        
+        
+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'))
+    parser.add_argument('--maas_admin', help="MAAS Admin account",**environ_or_required('CCLM_MAAS_ADMIN'))
+    parser.add_argument('--maas_admin_password', help="Password for MAAS Admin account",**environ_or_required('CCLM_MAAS_ADMIN_PASSWD'))
+    args = parser.parse_args()
+    db = get_db_obj(args.database)
+    cursor = get_cursor_obj(db)
+    machine_list = read_data_from_db(cursor)
+    create_maas_node(machine_list, args.maas_admin, args.maas_admin_password)
+    update_node_id(cursor)
+    close_db(db)
+
+if __name__ == '__main__':
+        main()
diff --git a/dist/cc_lab_manager-0.1-py3.8.egg b/dist/cc_lab_manager-0.1-py3.8.egg
 new file mode 100644
index 0000000..cd9a0a4
 Binary files /dev/null and b/dist/cc_lab_manager-0.1-py3.8.egg differ
diff --git a/setup.py b/setup.py
index a609291..d39888c 100644
--- a/setup.py
+++ b/setup.py
@@ -1,7 +1,7 @@
 #!/usr/bin/env python3
 from setuptools import setup, find_packages
 
-INSTALL_REQUIRES = ['pysheets']
+INSTALL_REQUIRES = ['pysheets', 'jinja2', 'python-libmaas', 'aiohttp==3.7.2']
 
 setup(
     name='cc-lab-manager',
@@ -12,4 +12,12 @@ setup(
     author_email='[e-mail]',
     url='https://code.launchpad.net/cc-lab-manager/',
     install_requires=INSTALL_REQUIRES,
+    entry_points={
+        'console_scripts': [
+            'cc-lab-manager=cc_lab_manager.commands.cc_lab_manager:main',
+        ]
+    },
+    classifiers=[
+        "Programming Language :: Python",
+    ]
 )