Navigating the Database
Now that you have setup your development server account you need to be able to navigate the database. Understanding the database and it's structure is important. Although you won't be adding any cameras manually, you must be able to query the database to check if any cameras have been imported from a specific domain. Getting familiar with MySQL is important so we will review a few commands and give some examples.
To connect to the development database, ssh into your development server account and simply type "db" anywhere. If this does not automatically start MySQL try the command "mysql". You will always use the cam2 database on the develupment server so type USE cam2; to navigate to the cam2 database. Now that you are in the cam2 database we will go thorough a few examples of how to query the database.
First we need to understand the structure of the cam2 database. We can use the command SHOW TABLES; to show the tables on the database. Notice all MySQL commands end with a semi-colon.
Each camera and all its information is stored in the 'camera' table on the cam2 database. We can use the command DESCRIBE camera; to show all the column headers in the camera table.
DESCRIBE is useful for getting a good understanding of the structure of the database before you query. When looking for camera websites to parse, you want to look for ways to easily get the information highlighted above in red. We will talk more about finding this information in later articles. When parsing Non-IP cameras there is one other table you should be concerned with the 'non_ip_camera' table. We can use the DESCRIBE command again to show the column headers of the 'non_ip_camera' table.
As you can see the 'non_ip_camera' table contains the 'camera_id' header. The 'camera_id' column contains the ID of the camera which references back to the unique ID automatically given to each camera when it is imported into the database. The 'non_ip_camera' table also contains the field 'snapshot_url'. The 'snapshot_url' field is the url to the snapshot image published by the camera. You must also look for this information when parsing cameras.
Now that you understand how to show the structure of the database we will go through a few querying techniques to find out if a website has been imported and is in the database.
The "SELECT" command determines what you want your output to be. The "FROM" command selects which table you would like to "SELECT" from. For example if we want to retrieve the 'camera_id' from the 'non_ip_camera' table then we would use the command:
SELECT camera_id FROM non_ip_camera;
This would print out every single camera ID in the table 'non_ip_camera'.
Using the "WHERE" command allows us to introduce logic into our query. For example if we want to output every 'camera_id' from the table 'non_ip_camera' that is between the numbers 100 and 6000 then we would use the command:
SELECT camera_id FROM non_ip_camera WHERE camera_id >= 100 AND camera_id <=6000;
There are many other logic commands you can use to select cameras. There are more basic examples here.
The "LIKE" command is used in conjunction with the "WHERE" command to find strings that are similar to other strings. This combination is very useful to see if a website has been parsed. For instance if we want to see if cameras from the domain https://www.bbc.co.uk/travel have been parsed then we can query that database using the command:
SELECT snapshot_url FROM non_ip_camera WHERE snapshot_url LIKE "%bbc.co%";
This command will output all the 'snapshot_url' entries where the string "bbc.co" appears anywhere inside them. The wildcard characters "%" mean that any string can come before and after "bbc.co" thus returning any 'snapshot_url' with the string "bbc.co" anywhere inside it.
You can also query information from multiple databases at once using the "." operator. For example if you want to find the 'latitude' and 'longitude' of every camera in the 'camera' database but wanted to limit your output to just cameras in the 'non_ip_camera' database who's 'snapshot_url' had the string "bbc.co" in it. In this example you would be selecting from the 'camera' table where information from the 'non_ip_camera' table met certain criteria. The full command would be used as fallows:
SELECT camera.latitude, camera.longitude FROM camera, non_ip_camera WHERE non_ip_camera.snapshot_url LIKE "bbc.co";
Now that you know some basic SQL commands, you should be able to check if a website has been parsed and is in the database. The next step is to find a website to parse and identifying a parsing method.