from functools import wraps
from psycopg2 import errorcodes
import psycopg2, time, traceback
from psycopg2.extensions import ISOLATION_LEVEL_AUTOCOMMIT
from psycopg2.extras import RealDictCursor
from . import config
from .wylog import log, logf, logging
from contextlib import contextmanager
[docs]def useCursor(func):
'''Decorator that creates a cursor object to pass to the wrapped method in
case one wasn't passed originally. The wrapped function should accept
`cur` as an argument.'''
@wraps(func)
def needsCursor(self, *args, **kwargs):
# Check if a cursor was passed
if 'cur' in kwargs:
return func(self, *args, **kwargs)
else:
# Create one otherwise
with self.conn, self.conn.cursor() as cur:
return func(self, *args, **kwargs, cur=cur)
return needsCursor
[docs]class sql_logger():
'''Utility class to enable logging and timing SQL execute statements,
as well as handling specific errors'''
def __init__(self, proc, ignore_duplicates=True):
self.proc = proc
# If a duplicate constraint is hit, ignore it.
self.ignore_duplicates = ignore_duplicates
def __enter__(self):
log('Beginning execution in [{}]'.format(self.proc),
proc=self.proc, v=logging.D)
self.start = time.time()
def __exit__(self, ty, val, tb):
end = time.time()
# Ignore the problem if we just added a duplicate
if ty is None:
log('SQL execution in [{}] completed without error. Duration: [{:.3f}]'.format(
self.proc, end - self.start), proc=self.proc, v=logging.D)
# Handle duplicate entry violations
elif (ty is psycopg2.IntegrityError) and self.ignore_duplicates:
if (val.pgcode in (errorcodes.UNIQUE_VIOLATION,
errorcodes.NOT_NULL_VIOLATION,
)):
log('SQL execution in [{}] completed. Null or Unique constraint hit [{}]. Duration: [{:.3f}]'.format(
self.proc, val.pgerror, end - self.start), proc=self.proc, v=logging.I)
return True
else:
log('Finished SQL execution in [{}] after [{:.3f}] seconds with [{}] error [{}]. Traceback: [{}]'.format(
self.proc, end - self.start, ty.__name__, str(val), traceback.format_tb(tb)),
proc=self.proc, v=logging.I)
[docs]class sql_database():
'''
A base class to facilitate SQL database operations.
Keyword Args:
clean (bool): If True, this causes all database tables to
be dropped in order to start with a clean database.
.. warning:: Obviously, this is really dangerous.
'''
def __init__(self, **kwargs):
self.clean = kwargs.get('clean', False)
[docs] def delete_database(self, dbname):
'''Deletes a database
Returns:
bool: True if the database was created
Raises:
FileExistsError: If the database to be deleted
does not exist.
IOError: If the database could not be deleted and
still exists after execution
'''
proc= 'sql_database.delete_database'
if not self.database_exists(dbname):
log('Database [{}] does not exist'.format(dbname), v=logging.A, proc= proc)
raise FileExistsError('Database [{}] does not exist'.format(dbname))
else:
log('Database [{}] exists, proceeding to delete'.format(dbname), v=logging.I, proc= proc)
with psycopg2.connect(**config.cc.postgres.args) as conn:
with conn.cursor() as cur, sql_logger(proc):
cur.execute('''
-- Disallow new connections
UPDATE pg_database SET datallowconn = 'false' WHERE datname = '{0}';
ALTER DATABASE {0} CONNECTION LIMIT 1;
-- Terminate existing connections
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = '{0}';
'''.format(dbname))
# Create a new isolated transaction block to drop the database
with psycopg2.connect(**config.cc.postgres.args) as conn:
conn.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
with conn.cursor() as cur, sql_logger(proc):
cur.execute('DROP DATABASE {0}'.format(dbname))
# Check to make sure it worked
if not self.database_exists(dbname):
log('Database [{}] deleted'.format(dbname), v=logging.N, proc= proc)
return True
else:
log('Database [{}] could not be deleted'.format(dbname), v=logging.CRITICAL, proc= proc)
raise IOError('Database [{}] could not be deleted'.format(dbname))
@logf
[docs] def execute_sql(self, *args, proc=None, fetch= True):
'''
Executes a SQL snippet and optionally gets the results
Arguments:
*args: The arguments to pass along
to :meth:`pyscopg2.cursor.execute`. Usually a string
containing the SQL statement, and potentially a tuple of
parameters.
Keyword Args:
proc (str): The name of the parent process, for logging purposes
fetch (bool): If True, fetches all results from the query
Returns:
tuple: The results of :meth:`pyscopg2.cursor.fetchall`
'''
with self.conn, self.conn.cursor() as cur, sql_logger(proc):
cur.execute(*args)
if fetch: return cur.fetchall()
else: return True
[docs] def execute_sql_gen(self, *args, proc= None):
'''
Executes a SQL snippet and gets the results in a generator
Arguments:
*args: The arguments to pass along
to :meth:`pyscopg2.cursor.execute`. Usually a string
containing the SQL statement, and potentially a tuple of
parameters.
Keyword Args:
proc (str): The name of the parent process, for logging purposes
Returns:
generator: The results of :meth:`pyscopg2.cursor.fetchall`
'''
with self.conn, self.conn.cursor() as cur, sql_logger(proc):
cur.execute(*args)
# Iterate over results and yield them in a generator
for result in cur:
if result is None: return True
else: yield result
@useCursor
[docs] def count(self, table,
column= '*',
value= None,
partial_value= None,
distinct= False,
cur= None):
'''Counts the occurrences of the specified `column` in
a given `table`.
Args:
table (str): The table to search in
Keyword Args:
column (str): The column to count
distinct (bool): If True, count only unique matches
value (str): If not None, adds a where clause to the count in the
format:
.. code-block:: sql
WHERE column = 'value'
partial_value (str): If not None, adds a where clause which
will match a partial string in the format:
.. code-block:: sql
WHERE column like '%partial_value1%'
Returns:
int: The number of matches
'''
proc= 'io.sql.count'
# Make the Where statements
where= ''
if value is not None:
assert isinstance(value, str)
where+= " WHERE {column} = '{value}' ".format(
column= column, value= value)
if partial_value: where += ' AND '
if partial_value is not None:
assert isinstance(partial_value, str)
where+= " WHERE {column} like '%{value}% ".format(
column= column, value= partial_value)
# Add the distinct qualifier to the statement
if distinct: d= ' DISTINCT '
else: d= ''
cur.execute(
'''
SELECT {distinct} count({column})
FROM {table}
{where};
'''.format(
distinct= d,
column= column,
table= table,
where= where,
))
# Get the result
result= cur.fetchone()
if result: return int(result[0])
else: return 0
@logf
[docs] def database_exists(self, db):
'''
Returns true is the specified database exists
Args:
db (str): A database name
Returns:
bool: True if the database exists
'''
proc = 'sql_database._database_exists'
with psycopg2.connect(**config.cc.postgres.args) as conn:
with conn.cursor() as cur, sql_logger(proc):
cur.execute("SELECT 1 from pg_database WHERE datname= %s", (db,))
return bool(cur.fetchone())
@logf
[docs] def create_database(self, new_db):
'''
Creates a new database
Args:
new_db (str): Database name to create
'''
proc = 'sql_database.create_database'
if self.database_exists(new_db):
return True
else:
with psycopg2.connect(**config.cc.postgres.args) as conn:
conn.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
with conn.cursor() as cur, sql_logger(proc):
cur.execute('CREATE DATABASE {};'.format(new_db))
return True
[docs] def close(self):
'''Closes the connection to the database if it is open'''
if not self.conn.closed: self.conn.close()
[docs] def ip_exists(self, ip, table):
'''
Check if a given IP exists in the database
Args:
ip (str): The IP address to check for
table (str): The table to check
Raises:
ValueError: If an argument is an improper type
'''
proc = 'sql_database.ip_exists'
if ip is None or table is None:
raise ValueError(proc + ': IP[{}] or Table[{}] missing'.format(
ip, table))
with self.conn, self.conn.cursor() as cur:
cur.execute('''
select exists
(select * from {t}
where ip= %(ip)s
limit 1);
'''.format(t=table),
{'ip': ip}
)
return cur.fetchone()[0] # Returns a (False,) tuple
[docs] def ip_name_exists(self, ip, name, table, cur=None):
'''Check if a given IP OR Name exists in the database'''
proc = 'sql_database.ip_name_exists'
if None in (ip,
table,
name):
raise ValueError(proc + ': IP[{}], Name [{}] or Table[{]] missing'.format(
ip, name, table))
with self.conn, self.conn.cursor() as cur:
cur.execute('''
select exists
(select * from %(table)s
where
ip= %(ip)s
OR
device_name= %(device)s
limit 1);''',
{'table': table, 'ip': ip, 'device': name})
return cur.fetchone()[0] # Returns a (False,) tuple
[docs]class main_db(sql_database):
def __init__(self, **kwargs):
proc = 'main_db.__init__'
sql_database.__init__(self, **kwargs)
# Get the database name from config
self.dbname= config.cc.main.name
try: self.create_database(self.dbname)
except FileExistsError: pass
self.conn = psycopg2.connect(**config.cc.main.args)
self.create_table(drop_tables=self.clean)
self.ignore_visited = kwargs.get('ignore_visited', True)
with self.conn, self.conn.cursor() as cur, sql_logger(proc):
# Delete everything in the visited table
if self.ignore_visited: cur.execute('DELETE FROM visited')
# Then set all pending entries as not working
cur.execute('''
UPDATE pending
SET working= FALSE
''')
def __len__(self):
return sql_database.count(self, self.dbname)
[docs] def count_pending(self):
'''Counts the number of rows in the table'''
return sql_database.count(self, 'pending')
[docs] def count_unique_visited(self):
'''Counts the number of unique devices in the database'''
with self.conn, self.conn.cursor() as cur:
cur.execute('''
SELECT count(distinct device_name)
FROM visited;''')
return cur.fetchone()[0]
[docs] def remove_pending_record(self, _id):
'''Removes a record from the pending table'''
proc = 'main_db.remove_processed'
# Error checking
assert isinstance(_id, int), (
proc + ': _id [{}] is not int'.format(type(_id)))
# Delete the processed entry
with self.conn, self.conn.cursor() as cur:
cur.execute('''
DELETE FROM
pending
WHERE
pending_id = %s
''', (_id,))
[docs] def remove_visited_record(self, ip):
'''Removes a record from the pending table'''
proc = 'main_db.remove_processed'
# Error checking
assert isinstance(ip, str), (
proc + ': ip [{}] is not str'.format(type(ip)))
# Delete the processed entry
with self.conn, self.conn.cursor() as cur:
cur.execute('''
DELETE FROM
visited
WHERE
ip = %s
''', (ip,))
[docs] def get_next(self):
'''Gets the next pending device.
Returns:
Dict: The next pending device as a dictionary object
with the names of the rows as keys.
'''
proc = 'main_db.get_next'
# User a special cursor which returns results as dicts
with self.conn, self.conn.cursor(cursor_factory=RealDictCursor) as cur:
cur.execute('''
SELECT * FROM
pending
WHERE
working= FALSE
ORDER BY pending_id ASC LIMIT 1
''')
output = cur.fetchone()
# Mark the new entry as being worked on
if output:
cur.execute('''
UPDATE pending
SET working= TRUE
WHERE pending_id= %s
''',
(output['pending_id'],))
# Return the next device
output = dict(output)
return output
else: return None
[docs] def add_pending_device_d(self, device_d=None, cur=None, **kwargs):
proc = 'main_db.add_pending_device_d'
# Pending dict template
_device_d = {
'device_name': None,
'ip_list': None,
'netmiko_platform': None,
'system_platform': None,
'source_interface': None,
'neighbor_interface': None,
'software': None,
'raw_cdp': None,
}
# If a dict was supplied, add values from it into the template
if device_d:
for key, value in device_d.items():
_device_d[key] = value
# If the function was passed with keyword args instead
elif kwargs:
# Template for the seed device
for key, value in kwargs.items():
_device_d[key] = value
else: return False
# Break if no IP address or platform was supplied
if ((_device_d['ip_list'] is None) or
(len(_device_d['ip_list']) == 0 or
(_device_d['netmiko_platform'] is None))):
return False
with self.conn, self.conn.cursor() as cur:
for ip in _device_d['ip_list']:
if sql_database.ip_exists(self, ip, 'visited'):
log('[{}] already in visited table'.format(ip),
v=logging.I, proc=proc)
if sql_database.ip_exists(self, ip, 'pending'):
log('[{}] already in pending table'.format(ip),
v=logging.I, proc=proc)
continue
with sql_logger(proc):
cur.execute('''
INSERT INTO pending
(
working,
ip,
device_name,
netmiko_platform,
system_platform,
source_interface,
neighbor_interface,
software,
raw_cdp
)
VALUES
(FALSE,
%(ip)s,
%(device_name)s,
%(netmiko_platform)s,
%(system_platform)s,
%(source_interface)s,
%(neighbor_interface)s,
%(software)s,
%(raw_cdp)s
);
''',
{
'ip': ip,
'device_name': _device_d['device_name'],
'netmiko_platform': _device_d['netmiko_platform'],
'system_platform': _device_d['system_platform'],
'source_interface': _device_d['source_interface'],
'neighbor_interface': _device_d['neighbor_interface'],
'software': _device_d['software'],
'raw_cdp': _device_d['raw_cdp'],
})
[docs] def add_device_pending_neighbors(self, _device=None, _list=None):
"""Appends a device or a list of devices to the database
Optional Args:
_device (network_device): A single device
_list (List): List of devices
Returns:
Boolean: True if write was successful, False otherwise.
"""
proc = 'main_db.add_device_pending_neighbors'
if not _list: _list = []
log('Adding neighbors to pending table', proc=proc,
v=logging.N)
# If a single device was passed, add it to the list
if _device: _list.append(_device)
# Return an error if no data was passed
if not _list:
log('No devices to add', proc=proc, v=logging.A)
return False
# Process each device in one transaction
for device in _list:
for neighbor in device.all_neighbors():
if not neighbor.get('netmiko_platform'):
log('Neighbor [{}] has no platform. Skipping'.format(
neighbor), v=logging.I, proc=proc)
continue
# Add it to the list of ips to check
self.add_pending_device_d(neighbor)
[docs] def create_table(self, drop_tables=True):
proc = 'main_db.create_table'
log('Creating main.db tables',
proc=proc, v=logging.I)
with self.conn, self.conn.cursor() as cur:
if drop_tables:
cur.execute('''
DROP TABLE IF EXISTS
pending,
visited
CASCADE;
''')
cur.execute('''
CREATE TABLE IF NOT EXISTS pending(
pending_id SERIAL PRIMARY KEY,
ip TEXT NOT NULL UNIQUE,
working BOOLEAN NOT NULL,
device_name TEXT,
netmiko_platform TEXT,
system_platform TEXT,
source_interface TEXT,
neighbor_interface TEXT,
software TEXT,
raw_cdp TEXT,
updated TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);
CREATE TABLE IF NOT EXISTS visited(
visited_id SERIAL PRIMARY KEY,
ip TEXT UNIQUE,
device_name TEXT,
updated TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);
''')
[docs] def add_visited_device_d(self, device_d=None, cur=None, **kwargs):
proc = 'main_db.add_visited_device_d'
_device_d = {
'device_name': None,
'ip': None,
}
# If a dict was supplied, add values from it into the template
if device_d:
for key, value in device_d.items():
_device_d[key] = value
# If the function was passed with keyword args instead
elif kwargs:
# Template for the seed device
for key, value in kwargs.items():
_device_d[key] = value
else: return False
# Break if no IP addres was supplied
if _device_d['ip'] is None:
raise ValueError(proc + ': No IP or platform was supplied in [{}]'.format(_device_d))
def _execute(_device_d, cur):
with sql_logger(proc):
cur.execute('''
INSERT INTO visited
(
ip,
device_name
)
VALUES
(
%(ip)s,
%(device_name)s
);
''',
{
'ip': _device_d['ip'], # Must have an IP
'device_name': _device_d['device_name'],
})
# Create a cursor if none was passed
if cur is None:
with self.conn, self.conn.cursor() as cur:
return _execute(_device_d, cur)
# Otherwise use the passed cursor
else: return _execute(_device_d, cur)
[docs] def add_visited_device_nd(self, _device=None, _list=None, cur=None):
"""Appends a device or a list of devices to the database
Optional Args:
_device (network_device): A single network_device
_list (List): List of network_device objects
Returns:
Boolean: True if write was successful, False otherwise.
"""
proc = 'main_db.add_visited_device_nd'
if not _list: _list = []
log('Adding device(s) to table.'.format(self.dbname),
proc=proc, v=logging.N)
# If a single device was passed, add it to the list so that we can
# simplify the code later on
if _device: _list.append(_device)
# Return an error if no data was passed
if not _list:
log('No devices to add', proc=proc,
v=logging.A)
return False
def _execute(_list, cur):
# Process each device
for _device in _list:
# Get the IP's from the device
ip_list = _device.get_ips()
log('{} has {} ip(s)'.format(_device.device_name, len(ip_list)),
proc=proc, v=logging.I)
# For failed devices which couldn't be fully polled:
with sql_logger(proc):
cur.execute('''
INSERT INTO visited
(
device_name,
ip
)
VALUES
(%s, %s);
''',
(_device.device_name,
_device.ip)
)
for ip in ip_list:
with sql_logger(proc):
cur.execute('''
INSERT INTO visited
(
device_name,
ip
)
VALUES
(%s, %s);
''',
(_device.device_name,
ip)
)
# Create a cursor if none was passed
if cur is None:
with self.conn, self.conn.cursor() as cur:
return _execute(_list, cur)
# Otherwise use the passed cursor
else: return _execute(_list, cur)
log('Added {} devices to visited table'.format(len(_list)),
proc=proc, v=logging.I)
return True
[docs]class device_db(sql_database):
def __init__(self, **kwargs):
proc = 'device_db.__init__'
sql_database.__init__(self, **kwargs)
# Get the database name from config
self.dbname = config.cc.inventory.name
try: self.create_database(self.dbname)
except FileExistsError: pass
self.conn = psycopg2.connect(**config.cc.inventory.args)
self.create_table(drop_tables=self.clean)
def __len__(self):
'Returns the number of devices in the database'
return sql_database.count(self, 'devices')
[docs] def ip_exists(self, ip):
return sql_database.ip_exists(self, ip, 'interfaces')
@useCursor
[docs] def locate_mac(self, mac, cur= None):
cur.execute('''
SELECT distinct devices.device_name as device, interface_name as interface, neighbors.device_name as neighbor
FROM mac
JOIN devices ON mac.device_id=devices.device_id
JOIN interfaces on mac.interface_id=interfaces.interface_id
LEFT JOIN neighbors on mac.interface_id=neighbors.interface_id
WHERE mac_address like %s;
''', ('%' + mac + '%', ))
return cur.fetchall()
@useCursor
[docs] def delete_device_record(self, id, cur= None):
'''Removes a record from the devices table'''
proc = 'device_db.remove_device_record'
# Error checking
assert isinstance(id, int), (
proc + ': id [{}] is not int'.format(type(id)))
# Delete the entry
cur.execute('''
DELETE
FROM devices
WHERE device_id = %s
''', (id, ))
[docs] def devices_on_subnet(self, subnet):
with self.conn, self.conn.cursor() as cur:
cur.execute('''
SELECT distinct interfaces.device_id
FROM interfaces
JOIN devices on interfaces.device_id=devices.device_id
WHERE network_ip is %s;
''', (subnet, ))
results= cur.fetchall()
# Return a nicely formatted list of device ID's
results= [x[0] for x in results]
return sorted(set(results))
[docs] def macs_on_subnet(self, subnet):
with self.conn, self.conn.cursor() as cur:
cur.execute('''
SELECT distinct mac_address
FROM (
SELECT distinct interface_id
FROM (
SELECT distinct device_id
FROM interfaces
WHERE network_ip = %s) as foo
JOIN interfaces ON interfaces.device_id=foo.device_id) as bar
JOIN mac on mac.interface_id = bar.interface_id;
''', (subnet, ))
# Create a generator over the macs so that we don't
# get overwhelmed
for mac in cur:
if mac is None: return True
else: yield mac[0]
[docs] def device_macs(self, device_id):
with self.conn, self.conn.cursor() as cur:
cur.execute('''
SELECT mac_address
FROM mac
JOIN interfaces on mac.interface_id=interfaces.interface_id
WHERE interfaces.device_id = %s;
''', (device_id, ))
return cur.fetchall()
#===========================================================================
# def device_id_exists(self, id):
# '''Returns True if a given device_id exists'''
#
# proc = 'io_sql.device_id_exists'
#
# with self.conn, self.conn.cursor() as cur:
# cur.execute('''
# SELECT device_id
# FROM devices
# WHERE device_id = %s;
# ''',
# (id, ))
# result= cur.fetchone()
#
# if result is None: return False
# else: return result[0]
#===========================================================================
#===========================================================================
# def unique_name_exists(self, name):
# '''Returns True if a given unique_name already exists'''
# proc = 'io_sql.unique_name_exists'
#
# with self.conn, self.conn.cursor() as cur:
# cur.execute('''
# SELECT device_id
# FROM devices
# WHERE unique_name = %s;
# ''',
# (name.upper(),))
# result= cur.fetchone()
#
# if result is None: return False
# else: return result[0]
#===========================================================================
[docs] def exists(self,
device_id= None,
unique_name= None,
device_name= None,
):
'''
Checks whether a device record is present in the devices table. Tries
each supplied identifier in order until a match is found, then
returns the device_id of the found record.
Keyword Args:
device_id (int): If not None, check the device_id column for a match
unique_name (str): If not None, check the unique_name column for a match
device_name (str): If not None, check the device_name column for a match
Returns
int: The device_id of the first match found
bool: False if not found
'''
# Error checking
if (device_id is None and
unique_name is None and
device_name is None):
raise ValueError('No values passed')
with self.conn, self.conn.cursor() as cur:
# Try each possible method until a match is found
if device_id:
cur.execute('''
SELECT device_id
FROM devices
WHERE device_id = %s
limit 1;
''',
(device_id, ))
result= cur.fetchone()
if result: return int(result[0])
if unique_name:
cur.execute('''
SELECT device_id
FROM devices
WHERE unique_name = %s
limit 1;
''',
(unique_name, ))
result= cur.fetchone()
if result: return int(result[0])
if device_name:
cur.execute('''
SELECT device_id
FROM devices
WHERE device_name = %s
limit 1;
''',
(device_name, ))
result= cur.fetchone()
if result: return int(result[0])
return False
[docs] def add_device_nd(self, _device):
"""Appends a device to the database
Args:
_device (network_device): A single network_device
Returns:
Boolean: False if write was unsuccessful
Int: Index of the device that was added, if successful
"""
proc = 'device_db.add_device_nd'
# Return an error if no data was passed
if not _device:
log('No devices to add', proc=proc, v=logging.A)
return False
log('Adding device to devices table'.format(self.dbname), proc=proc, v=logging.N)
# Do everything in one transaction
with self.conn, self.conn.cursor() as cur:
device_id = self.insert_device_entry(_device, cur)
# Add all the device's serials
for serial in _device.serial_numbers:
self.insert_serial_entry(device_id, serial, cur)
# Add all of the device's interfaces
for interf in _device.interfaces:
interface_id = self.insert_interface_entry(device_id, interf, cur)
# Add all the interface's mac addresses
for mac_address in interf.mac_address_table:
mac_id = self.insert_mac_entry(device_id, interface_id, mac_address, cur)
# Add each neighbor + ip that was matched to an interface
for neighbor in interf.neighbors:
neighbor_id = self.insert_neighbor_entry(device_id, interface_id, neighbor, cur)
for n_ip in neighbor: self.insert_neighbor_ip_entry(neighbor_id, n_ip, cur)
# Add each neighbor + ip not matched to an interface
for neighbor in _device.neighbors:
neighbor_id = self.insert_neighbor_entry(device_id, None, neighbor, cur)
for n_ip in neighbor: self.insert_neighbor_ip_entry(neighbor_id, n_ip, cur)
self.conn.commit()
return device_id
[docs] def get_device_record(self,
column,
value):
'''Get a device record based on a lookup column.
'WHERE column = value'
Returns:
psycopg2 dict object
'''
with self.conn as conn, conn.cursor(
cursor_factory=psycopg2.extras.DictCursor) as cur:
cur.execute('''
SELECT *
FROM devices
WHERE {} = %s
limit 1;
'''.format(column),
(value, )
)
return cur.fetchone()
##############################################
# Update existing device
##############################################
[docs] def process_duplicate_device(self, device):
'''
Parent method for handling an existing device which
needs to be updated.
1. Determine if the `device` exists and, if so, get the device_id
2. Overwrite all entries in the device with the new device
3. Set a new updated time for all dependent tables
4. Delete any interfaces and serials which no longer exist
5. Add any new interfaces and serials
6. Add any new MAC addresses
7. Update any newly non-existent MAC addresses
Args:
device (NetworkDevice): A network device object to check against
for duplicates
Returns:
bool: True if a duplicate was found and updated
'''
proc= 'main.process_duplicate_device'
# Check if the device exists in the database. If so, get the device_id
index = self.exists(unique_name= device.unique_name)
if not index:
log('Not a duplicate record: [{}]'.format(
device.device_name), v=logging.I, proc= proc)
return False
# Proceed to process the duplicate device
log('Positive Duplicate record: [{}]'.format(
device.device_name), v=logging.N, proc= proc)
self.update_device_entry()
return True
@useCursor
[docs] def update_device_entry(self, device,
cur= None,
device_id= None,
unique_name= None
):
'''
Overwrites all entries in the Devices table with a matching `device_id`
or `unique_name` with the information in `device`.
Args:
device (NetworkDevice): The device to source updates from
Keyword Args:
cur (psycopg2.cursor): Cursor object used to update the database
device_id (int): If not None, overwrites the row at this index
with `device`
unique_name (str): If not None, overwrites any row with a matching
`unique_name` field with `device`.
.. note:: If both `device_id` and `unique_name` are given, the method
will update **all** entries that match **either** key.
Raises:
ValueError: No `unique_name` or `device_id` passed to the method
'''
proc= 'device_db.update_device_entry'
# Make sure we got a valid id or name
if (unique_name is None and
device_id is None):
log('No unique_name or device_id passed to to the method',
proc=proc, v= logging.A)
raise ValueError(
proc+ ': No unique_name or device_id passed to to the method')
# Make the 'where' clause
where_clause= ' OR '.join([
'{} = %({})s\n'.format(x, 'w'+x) for x in
('device_id', 'unique_name') if x is not None])
# Update the devices table with the device
cur.execute('''
UPDATE devices
SET
device_name= %(device_name)s,
unique_name= %(unique_name)s,
netmiko_platform= %(netmiko_platform)s,
system_platform= %(system_platform)s,
software= %(software)s,
raw_cdp= %(raw_cdp)s,
config= %(config)s,
failed= %(failed)s,
error_log= %(error_log)s,
processing_error= %(processing_error)s,
tcp_22= %(tcp_22)s,
tcp_23= %(tcp_23)s,
username= %(username)s,
password= %(password)s,
cred_type= %(cred_type)s,
updated= now()
WHERE
{}
'''.format(where_clause),
{
'device_name': device.device_name,
'unique_name': device.unique_name,
'netmiko_platform': device.netmiko_platform,
'system_platform': device.system_platform,
'software': device.software,
'raw_cdp': device.raw_cdp,
'config': device.config,
'failed': device.failed,
'error_log': device.error_log,
'processing_error': device.processing_error,
'tcp_22': device.tcp_22,
'tcp_23': device.tcp_23,
'username': device.username,
'password': device.short_pass(),
'cred_type': device.cred_type,
# Where
'wdevice_id': device_id,
'wunique_name': unique_name,
})
return True
@useCursor
[docs] def set_dependents_as_updated(self, device_id, cur= None):
'''Sets the last touched time on all dependents of the
given device_id to now'''
cur.execute('''
UPDATE devices
SET updated = now()
WHERE device_id = %(d)s;
UPDATE interfaces
SET updated = now()
WHERE device_id = %(d)s;
UPDATE mac
SET updated = now()
WHERE device_id = %(d)s;
UPDATE neighbors
SET updated = now()
WHERE device_id = %(d)s;
UPDATE serials
SET updated = now()
WHERE device_id = %(d)s;
UPDATE neighbor_ips
SET updated = now()
WHERE neighbor_id =
(SELECT neighbor_id
FROM neighbors
WHERE device_id = %(d)s);
''', {'d': device_id})
######################################################
# Add new device Methods
######################################################
[docs] def insert_device_entry(self, device, cur):
# Add the device into the database
cur.execute('''
INSERT INTO devices (
device_name,
unique_name,
netmiko_platform,
system_platform,
software,
raw_cdp,
config,
failed,
error_log,
processing_error,
tcp_22,
tcp_23,
username,
password,
cred_type
)
VALUES (
%(device_name)s,
%(unique_name)s,
%(netmiko_platform)s,
%(system_platform)s,
%(software)s,
%(raw_cdp)s,
%(config)s,
%(failed)s,
%(error_log)s,
%(processing_error)s,
%(tcp_22)s,
%(tcp_23)s,
%(username)s,
%(password)s,
%(cred_type)s
)
RETURNING device_id;
''',
{
'device_name': device.device_name,
'unique_name': device.unique_name,
'netmiko_platform': device.netmiko_platform,
'system_platform': device.system_platform,
'software': device.software,
'raw_cdp': device.raw_cdp,
'config': device.config,
'failed': device.failed,
'error_log': device.error_log,
'processing_error': device.processing_error,
'tcp_22': device.tcp_22,
'tcp_23': device.tcp_23,
'username': device.username,
'password': device.short_pass(),
'cred_type': device.cred_type,
})
device.device_id= cur.fetchone()[0]
return device.device_id
[docs] def insert_interface_entry(self, device_id, interf, cur):
cur.execute('''
INSERT INTO interfaces (
device_id,
interface_name,
interface_type,
interface_number,
ip,
subnet,
virtual_ip,
description,
raw_interface,
network_ip
)
VALUES (
%(device_id)s,
%(interface_name)s,
%(interface_type)s,
%(interface_number)s,
%(ip)s,
%(subnet)s,
%(virtual_ip)s,
%(description)s,
%(raw_interface)s,
%(network_ip)s
)
RETURNING interface_id;
''',
{
'device_id': device_id,
'interface_name': interf.interface_name,
'interface_type': interf.interface_type,
'interface_number': interf.interface_number,
'ip': interf.interface_ip,
'subnet': interf.interface_subnet,
'virtual_ip': interf.virtual_ip,
'description': interf.interface_description,
'raw_interface': interf.raw_interface,
'network_ip': interf.network_ip,
})
return cur.fetchone()[0]
[docs] def insert_serial_entry(self, device_id, serial, cur):
cur.execute('''
INSERT INTO serials (
device_id,
serialnum,
name,
description,
productid,
vendorid
)
VALUES (
%(device_id)s,
%(serialnum)s,
%(name)s,
%(description)s,
%(productid)s,
%(vendorid)s
)
RETURNING serial_id;
''',
{
'device_id': device_id,
'serialnum': serial.get('serialnum', None),
'name': serial.get('name', None),
'description': serial.get('desc', None),
'productid': serial.get('productid', None),
'vendorid': serial.get('vendorid', None),
})
return cur.fetchone()[0]
[docs] def insert_neighbor_ip_entry(self, neighbor_id, ip, cur):
cur.execute('''
INSERT INTO neighbor_ips
(
neighbor_id,
ip
)
VALUES
(
%(neighbor_id)s,
%(ip)s
)
RETURNING neighbor_ip_id;
''',
{
'neighbor_id': neighbor_id,
'ip': ip,
})
return cur.fetchone()[0]
[docs] def insert_neighbor_entry(self, device_id, interface_id, neighbor, cur):
cur.execute('''
INSERT INTO neighbors
(
device_id,
interface_id,
device_name,
netmiko_platform,
system_platform,
source_interface,
neighbor_interface,
software,
raw_cdp
)
VALUES
(
%(device_id)s,
%(interface_id)s,
%(device_name)s,
%(netmiko_platform)s,
%(system_platform)s,
%(source_interface)s,
%(neighbor_interface)s,
%(software)s,
%(raw_cdp)s
)
RETURNING neighbor_id;
''',
{
'device_id': device_id,
'interface_id': interface_id,
'device_name': neighbor.get('device_name', None),
'netmiko_platform': neighbor.get('netmiko_platform', None),
'system_platform': neighbor.get('system_platform', None),
'source_interface': neighbor.get('source_interface', None),
'neighbor_interface': neighbor.get('neighbor_interface', None),
'software': neighbor.get('software', None),
'raw_cdp': neighbor.get('raw_cdp', None),
})
return cur.fetchone()[0]
[docs] def insert_mac_entry(self, device_id, interface_id, mac_address, cur):
cur.execute('''
INSERT INTO mac
(
device_id,
interface_id,
mac_address
)
VALUES
(
%(device_id)s,
%(interface_id)s,
%(mac_address)s
)
RETURNING mac_id;
''',
{
'device_id': device_id,
'interface_id': interface_id,
'mac_address': mac_address,
})
return cur.fetchone()[0]
[docs] def create_table(self, drop_tables=True):
proc = 'device_db.create_table'
with self.conn, self.conn.cursor() as cur:
if drop_tables: cur.execute('''
DROP TABLE IF EXISTS
neighbor_IPs,
mac,
serials,
neighbors,
interfaces,
devices
CASCADE;
''')
cur.execute('''
CREATE TABLE IF NOT EXISTS devices(
device_id SERIAL PRIMARY KEY ,
device_name TEXT,
unique_name TEXT,
netmiko_platform TEXT,
system_platform TEXT,
software TEXT,
raw_cdp TEXT,
config TEXT,
failed BOOLEAN,
error_log TEXT,
processing_error BOOLEAN,
tcp_22 BOOLEAN,
tcp_23 BOOLEAN,
username TEXT,
password TEXT,
cred_type TEXT,
updated TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);
CREATE TABLE IF NOT EXISTS Interfaces(
interface_id BIGSERIAL PRIMARY KEY ,
device_id INTEGER NOT NULL,
interface_name TEXT NOT NULL,
interface_number TEXT,
interface_type TEXT,
ip TEXT,
subnet TEXT,
virtual_ip TEXT,
description TEXT,
raw_interface TEXT,
network_ip TEXT,
updated TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
FOREIGN KEY(device_id) REFERENCES Devices(device_id)
ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS MAC(
mac_id BIGSERIAL PRIMARY KEY ,
device_id INTEGER NOT NULL,
interface_id INTEGER NOT NULL,
mac_address TEXT NOT NULL,
seen_last_scan BOOLEAN DEFAULT TRUE,
updated TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
last_seen TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
FOREIGN KEY(interface_id) REFERENCES Interfaces(interface_id)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY(device_id) REFERENCES Devices(device_id)
ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS Serials(
serial_id SERIAL PRIMARY KEY ,
device_id INTEGER NOT NULL,
serialnum TEXT NOT NULL,
name TEXT,
description TEXT,
productid TEXT,
vendorid TEXT,
updated TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
FOREIGN KEY(device_id) REFERENCES Devices(device_id)
ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS Neighbors(
neighbor_id BIGSERIAL PRIMARY KEY ,
device_id INTEGER NOT NULL,
interface_id INTEGER,
device_name TEXT,
netmiko_platform TEXT,
system_platform TEXT,
source_interface TEXT,
neighbor_interface TEXT,
software TEXT,
raw_cdp TEXT,
updated TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
FOREIGN KEY(device_id) REFERENCES Devices(device_id)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY(interface_id) REFERENCES Interfaces(interface_id)
ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS Neighbor_IPs(
neighbor_ip_id BIGSERIAL PRIMARY KEY ,
neighbor_id INTEGER NOT NULL,
ip TEXT NOT NULL,
type TEXT,
updated TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
FOREIGN KEY(neighbor_id) REFERENCES Neighbors(neighbor_id)
ON DELETE CASCADE ON UPDATE CASCADE
);
''')