How do I retrieve all files in a site?

You might try to do this by using a WHERE filter on parentsite or whfspath, but you'll find this to be too slow because these fields are calculated - and trying to use these fields in a WHERE clause might require the database to calculate the parentsite for every file in the database.

An efficient approach is to use the GetDescendantFolderids API on a WHFS object. This function will retrieve all folder ids from a specifed point (and requires only one database query per depth level of the site) and you can then use this with an IN query on the parent field. This field is indexed and queries on this field should execute much faster than on fields like parentsite

INTEGER ARRAY subfolders := OpenWHFSObject(siteid)->GetDescendantFolderids();
INTEGER ARRAY allfolders := [ siteid, ...subfolders ];

RECORD ARRAY allfiles := 
   SELECT id, name, title
     FROM system.fs_objects
    WHERE parent IN allfolders AND isfolder = FALSE;