Database Queries

Getting started

In some rare cases it may be required to work directly with the database. In these cases you can use the sqlite3 command.

By default, sqlite will not format database command outputs. To cleanup the output when running commands in sqlite, open your database as shown below.

sqlite3 -header -column /var/lib/vce/database.sqlite

Common queries

Registered users

sqlite> select * from user;
id          username    email            fullname  
----------  ----------  ---------------  ----------
1           admin       admin@localhost  admin
2           basic       basic@localhost  basic

ID of ethernet 15/4 on test-switch.domain.com

sqlite> select interface.id, interface.name from interface
   ...> join switch on switch.id=interface.switch_id
   ...> where switch.name='test-switch.domain.com'
   ...> and interface.name='ethernet 15/4';
id          name         
----------  -------------
2           ethernet 15/4

ACLs on an interface

sqlite> select * from acl where interface_id=3 order by low asc;
id          interface_id  workgroup_id  low         high      
----------  ------------  ------------  ----------  ----------
8           3             1             200         300       
5           3             1             310         500    

Owners of an interface

sqlite> select user.* from user
   ...> join user_workgroup on user.id=user_workgroup.user_id
   ...> join workgroup on workgroup.id=user_workgroup.workgroup_id
   ...> where workgroup.id=(
   ...> select workgroup_id from interface where id=2
   ...> );
id          username    email            fullname  
----------  ----------  ---------------  ----------
1           basic       basic@localhost  basic     
2           admin       admin@localhost  admin