def get_pxe_config_for_device(device, image=None):
    """
    Get hardware type for device and use with image to get pxe config name.
    If image is not given, use the device's last image.
    """
    conn = sql.get_conn()
    res = conn.execute(select(
            [model.devices.c.hardware_type_id,
             model.devices.c.last_image_id]).where(
            model.devices.c.name==device))
    row = res.fetchone()
    if row is None:
        raise NotFound
    hw_type_id = row[0]
    if not image:
        img_id = row[1]
    else:
        res = conn.execute(select([model.images.c.id]).where(
                model.images.c.name==image))
        row = res.fetchone()
        if row is None:
            raise NotFound
        img_id = row[0]
    res = sql.get_conn().execute(select(
            [model.pxe_configs.c.name],
            from_obj=[model.image_pxe_configs.join(
                    model.pxe_configs)]).where(and_(
                model.image_pxe_configs.c.hardware_type_id==hw_type_id,
                model.image_pxe_configs.c.image_id==img_id)))
    row = res.fetchone()
    if row is None:
        raise NotFound
    return row[0]

def set_state(tbl, id_col, id_val, state, timeout):
    """
    Set the state of this object, without counters
    """
    sql.get_conn().execute(tbl.update().
                           where(id_col==id_val).
                           values(state=state, state_timeout=timeout))

def set_counters(tbl, id_col, id_val, counters):
    """
    Set the counters for this object
    """
    sql.get_conn().execute(tbl.update().
                           where(id_col==id_val).
                           values(state_counters=json.dumps(counters)))

def add_pxe_config(name, description="Boot image",
                  contents="BOOT THIS THINGIE WITH THIS CONFIG",
                  id=None, active=True):
    sql.get_conn().execute(model.pxe_configs.insert(), name=name,
                           description=description,
                           contents=contents,
                           id=id,
                           active=active)

def add_image(name, boot_config_keys='[]', can_reuse=False, id=None,
              hidden=False, has_sut_agent=True):
    sql.get_conn().execute(model.images.insert(),
                           id=id,
                           name=name,
                           boot_config_keys=boot_config_keys,
                           can_reuse=can_reuse,
                           hidden=hidden,
                           has_sut_agent=has_sut_agent)

def update_pxe_config(name, description=None, active=None, contents=None):
    updates = {}
    if description:
        updates["description"] = description
    if active is not None:
        updates["active"] = active
    if contents is not None:
        updates["contents"] = contents
    sql.get_conn().execute(model.pxe_configs.update(model.pxe_configs.c.name == name), **updates)

def insert_device(values):
    """Insert a new device into the DB.  VALUES should be in the dictionary
    format used for inventorysync - see inventorysync.py"""
    values = values.copy()

    # convert imaging_server to its ID, and add a default state and counters
    values["imaging_server_id"] = find_imaging_server_id(values.pop("imaging_server"))
    values["state"] = "new"
    values["state_counters"] = "{}"

    sql.get_conn().execute(model.devices.insert(), [values])

def update_device(id, values):
    """Update an existing device with id ID into the DB.  VALUES should be in
    the dictionary format used for inventorysync - see inventorysync.py"""
    values = values.copy()

    # convert imaging_server to its ID, and strip the id
    values["imaging_server_id"] = find_imaging_server_id(values.pop("imaging_server"))
    if "id" in values:
        values.pop("id")

    sql.get_conn().execute(model.devices.update(whereclause=(model.devices.c.id == id)), **values)

def update_device(id, values):
    """Update an existing device with id ID into the DB.  VALUES should be in
    the dictionary format used for inventorysync - see inventorysync.py"""
    values = values.copy()

    # convert imaging_server to its ID, and strip the id
    values['imaging_server_id'] = find_imaging_server_id(values.pop('imaging_server'))
    if 'hardware_type' in values or 'hardware_model' in values:
        values['hardware_type_id'] = find_hardware_type_id(
            values.pop('hardware_type'), values.pop('hardware_model'))
    if 'id' in values:
        values.pop('id')

    sql.get_conn().execute(model.devices.update(whereclause=(model.devices.c.id==id)), **values)

def insert_device(values, _now=None):
    """Insert a new device into the DB.  VALUES should be in the dictionary
    format used for inventorysync - see inventorysync.py"""
    values = values.copy()

    # convert imaging_server to its ID, and add a default state and counters
    values['imaging_server_id'] = find_imaging_server_id(values.pop('imaging_server'))
    values['hardware_type_id'] = find_hardware_type_id(
        values.pop('hardware_type'), values.pop('hardware_model'))
    # set up the state machine in the 'new' state, with an immediate timeout
    values['state'] = 'new'
    values['state_timeout'] = _now or datetime.datetime.now()
    values['state_counters'] = '{}'

    sql.get_conn().execute(model.devices.insert(), [ values ])

def pxe_config_details(name):
    conn = sql.get_conn()
    res = conn.execute(select([model.pxe_configs], model.pxe_configs.c.name == name))
    row = res.fetchone()
    if row is None:
        raise NotFound
    return {"details": row_to_dict(row, model.pxe_configs, omit_cols=["id"])}

def dump_devices(*device_names):
    """
    Dump device data.  This returns a list of dictionaries with keys id, name,
    fqdn, invenetory_id, mac_address, imaging_server, relay_info, and state.
    Zero or more device names should be passed in as positional arguments.  If
    none are given, dumps all device data.
    """
    conn = sql.get_conn()
    devices = model.devices
    img_svrs = model.imaging_servers
    stmt = sqlalchemy.select(
        [
            devices.c.id,
            devices.c.name,
            devices.c.fqdn,
            devices.c.inventory_id,
            devices.c.mac_address,
            img_svrs.c.fqdn.label("imaging_server"),
            devices.c.relay_info,
            devices.c.state,
        ],
        from_obj=[devices.join(img_svrs)],
    )
    if device_names:
        id_exprs = []
        for i in device_names:
            id_exprs.append('devices.name=="%s"' % i)
        if len(id_exprs) == 1:
            id_exprs = id_exprs[0]
        else:
            id_exprs = or_(*id_exprs)
        stmt = stmt.where(id_exprs)
    res = conn.execute(stmt)
    return [dict(row) for row in res]

def device_fqdn(device):
    """
    Get the fqdn of device.
    """
    res = sql.get_conn().execute(select([model.devices.c.fqdn], model.devices.c.name == device))
    row = res.fetchone()
    return row["fqdn"]

def image_is_reusable(image):
    res = sql.get_conn().execute(select([model.images.c.can_reuse],
                                        model.images.c.name==image))
    row = res.fetchone()
    if not row:
        raise NotFound
    return row[0]

 def testInsertBoard(self):
     data.insert_device(dict(name='device2', fqdn='device2.fqdn', inventory_id=23,
         mac_address='aabbccddeeff', imaging_server='server2',
             relay_info='relay-2:bank2:relay2'))
     # device with existing imaging_server to test the insert-if-not-found behavior
     data.insert_device(dict(name='device3', fqdn='device3.fqdn', inventory_id=24,
         mac_address='aabbccddeeff', imaging_server='server1',
             relay_info='relay-2:bank2:relay2'))
     conn = sql.get_conn()
     res = conn.execute(model.devices.select())
     self.assertEquals(sorted([ dict(r) for r in res.fetchall() ]),
     sorted([
         {u'state': u'new', u'state_counters': u'{}', u'state_timeout': None,
          u'relay_info': u'relay-2:bank2:relay2', u'name': u'device2',
          u'fqdn': u'device2.fqdn', u'inventory_id': 23, u'imaging_server_id': 2,
          u'boot_config': None, u'mac_address': u'aabbccddeeff', u'id': 2,
          u'last_pxe_config_id': None},
         {u'state': u'new',u'state_counters': u'{}', u'state_timeout': None,
          u'relay_info': u'relay-2:bank2:relay2', u'name': u'device3',
          u'fqdn': u'device3.fqdn', u'inventory_id': 24, u'imaging_server_id': 1,
          u'boot_config': None, u'mac_address': u'aabbccddeeff', u'id': 3,
          u'last_pxe_config_id': None},
         {u'state': u'offline',u'state_counters': u'{}', u'state_timeout': None,
          u'relay_info': u'relay-1:bank1:relay1', u'name': u'device1',
          u'fqdn': u'device1', u'inventory_id': 1, u'imaging_server_id': 1,
          u'boot_config': u'{}', u'mac_address': u'000000000000', u'id': 1,
          u'last_pxe_config_id': None},
         ]))

 def add(self, name, message, source="webapp"):
     conn = sql.get_conn()
     values = {self.foreign_key_col.name: self._get_object_id(name),
               "ts": datetime.datetime.now(),
               "source": source,
               "message": message}
     conn.execute(self.logs_table.insert(), values)

def request_config(request_id):
    conn = sql.get_conn()
    res = conn.execute(
        select(
            [
                model.requests.c.requested_device,
                model.requests.c.assignee,
                model.requests.c.expires,
                model.requests.c.boot_config,
            ],
            model.requests.c.id == request_id,
        )
    )
    row = res.fetchone()
    if row is None:
        raise NotFound

    request = {
        "id": request_id,
        "requested_device": row[0].encode("utf-8"),
        "assignee": row[1].encode("utf-8"),
        "expires": row[2].isoformat(),
        "boot_config": row[3].encode("utf-8"),
        "assigned_device": "",
        "url": "http://%s/api/request/%d/" % (config.get("server", "fqdn"), request_id),
    }

    assigned_device = get_assigned_device(request_id)
    if assigned_device:
        request["assigned_device"] = assigned_device
    return request

def object_status(tbl, id_col, id_val, logs_obj):
    """
    Get the status of device.
    """
    res = sql.get_conn().execute(select([tbl.c.state], id_col == id_val))
    row = res.fetchall()[0]
    return {"state": row["state"].encode("utf-8"), "log": logs_obj.get(id_val)}

def add_request(server, assignee="slave", state="new", expires=None,
                device='any', boot_config='{}'):
    if not expires:
        expires = datetime.datetime.now() + datetime.timedelta(hours=1)
    conn = sql.get_conn()
    server_id = conn.execute(select([model.imaging_servers.c.id],
                                    model.imaging_servers.c.fqdn==server)).fetchone()[0]
    if server_id is None:
        raise data.NotFound
    res = conn.execute(model.requests.insert(),
                       imaging_server_id=server_id,
                       requested_device=device,
                       assignee=assignee,
                       expires=expires,
                       boot_config=boot_config,
                       state=state,
                       state_counters='{}')
    request_id = res.lastrowid
    if device and state != 'closed':
        device_id = conn.execute(select(
                [model.devices.c.id],
                model.devices.c.name==device)).fetchone()[0]
        conn.execute(model.device_requests.insert(),
                     request_id=request_id,
                     device_id=device_id)

def add_device(device, server="server", state="offline",
              mac_address="000000000000",
              log=[], config='{}', relayinfo="",
              last_image_id=None, hardware_type_id=1,
              environment=None):
    global inventory_id
    conn = sql.get_conn()
    id = conn.execute(select([model.imaging_servers.c.id],
                              model.imaging_servers.c.fqdn==server)).fetchone()[0]
    if id is None:
        raise data.NotFound
    conn.execute(model.devices.insert(),
                 name=device,
                 fqdn=device, #XXX
                 inventory_id=inventory_id,
                 state=state,
                 state_counters='{}',
                 mac_address=mac_address,
                 imaging_server_id=id,
                 relay_info=relayinfo,
                 boot_config=config,
                 last_image_id=last_image_id,
                 hardware_type_id=hardware_type_id,
                 environment=environment)
    inventory_id += 1