These are chat archives for ipcortex/FAC-FMC

10th
Jun 2016
Thomas Upton
@tu6619
Jun 10 2016 09:34
this is useful for sql commands
Thomas Upton
@tu6619
Jun 10 2016 09:57
Here's our fmc-schema.txt file so far, any comments / suggestions would be most welcome!
Rob Pickering
@rjp44
Jun 10 2016 10:42
Hi Tom, at first glance that looks sane. I'm going to run it past our SQL expert here for a second opinion in a bit more detail but it looks pretty well thought out.
How is it all going folks, is there anything else you need from us?
eleanorreem
@eleanorreem
Jun 10 2016 10:48
Hi Rob, hold that thought.
I have found a small bug and want to fix it before you send it on.
Rob Pickering
@rjp44
Jun 10 2016 11:06
That's fine, I sent the link to the file in your schema branch to Steve at the same time as the earlier reply. If you need to do tweaks just update the branch and I'll tell him to hold off until you are ready.
Peter Wilson
@peterw8102
Jun 10 2016 12:50

I couldn't find the schema file in the git branch and I'm probably not going to get another chance to take a look today so my initial comments are below. Apologies if these are the things you've spotted yourselves :-)

  • files table. What's the file_index used for?

  • The tags_calls table provides a many-to-many relationship between calls and tags. I don't think rows in that table need their own sequence number.

  • 'users' table. You can index this on the contact_id (BIGINT) from the PABX I think. The 'contact_id' of the logged in user is unique within the PABX so there's no need to add a new identifier. As long as the system is only polling ONE PABX this is fine. If we want the app to poll multiple PABX then we'd need another layer somewhere. This is the ID that's returned by the PABX as part of the authentication process. What is the PABX column here? I also don't think users are restricted to companies, or if they are whether you're told this information as the result of authentication. I don't think it needs to be for this usage (somewhere to store filters).

  • Your comment before "participants_calls_fk" is wrong - it says that when you delete the company or call then you want to delete the associated participants but you have placed a restriction instead. I think deleting a call should automatically delete the participants. Deleting a call is most likely part of the archiving policy for a company and what you want to do is delete the call and take everything else with it. Makes the SQL much simpler.

  • You don't need the foreign keys on the companies tables. You put those keys on the "dependant" tables. 'companies' do not depend on 'users' but 'users' do depend on the 'company'. It might help to think of it as a tree flowing downwards. So 'calls' can only exist if there's a 'company' but a 'company' can exist with no 'calls'.

  • Think more carefully about your foreign key restrictions. I think you might be making it too restrictive. Generally, if you can think of it as a tree, then if it makes sense to delete a 'thing' like a call then you want to automatically delete all the things that depend on that. So deleting a call would delete all the parties. I'd probably argue that deleting a company would delete everything owned by the company. You might not want to do that for safety of course - but restricvtions mean to delete something you have to first explicitly delete all the things that depend on it.

Steve Davies
@davies147
Jun 10 2016 13:26

I think Pete's comments must be from a different schema - I looked at FAC-FMC/src/db/schema.txt and much of the info above does not seem to apply any more (which is good :) ).

It looks pretty good to me. I've not delved in to the 'tagging' mechanism much.

One issue I have picked up is that there does not seem to be an attempt to store 'filename' of a recording anywhere, even though that is the 'key' that the REST API will use when sending data. I think you might need a simple 'recording.filename' column with a UNIQUE index on it. I would personally also store 'file-size' and 'duration' as you'll be handed that by the API.

In case it helps, the proposed API result set for a list of call recordings is currently:

{
    "result": "success",
    "values": [
        {
            "file": "filename",
            "start": epoch-seconds,
            "caller": "caller number",
            "callee": "callee-number",
            "size": bytes,
            "duration": seconds
        },
        ...
    ],
    "numrows": number-of-rows
}
Peter Wilson
@peterw8102
Jun 10 2016 13:30
I don't think the schema is in the git code area yet. I couldn't find it. Scroll up this conversation a little and the file you should be looking at is linked as fmc-schema.txt (https://files.gitter.im/ipcortex/FAC-FMC/QGvb/fmc-schema.txt)
eleanorreem
@eleanorreem
Jun 10 2016 13:39
Hi all, there has been a bit of a confusion
The schema is only just now on the master branch for all to check out.
its called db_schema.txt
src/db/db_schema.txt
Peter thanks for your comments. We have made some changes. In response to your question:
  1. What is the file_index used for ?
    file_id = unique number serial
    file_index = indicates where in our file system the .wav file is stored
    file_name = what the PBX refers to the file as
    We can see that the file_index could possibly be redundant as the file_id
    could be the file_index.
    What do you think?
Steve Davies
@davies147
Jun 10 2016 14:10
I do like the simplification of calls having a PK of just call_id - much cleaner.
It may be necessary to search on files.file_name in order to check for duplicates before downloading a file, so you probably want to CREATE UNIQUE INDEX ON files(file_name);
Peter Wilson
@peterw8102
Jun 10 2016 14:15
Except that on of the key filters on 'calls' will be the company they belong to - so to me having the company_id part of the key makes more sense? So I never want to see all calls in the roles we have. It will always be SELECT xxx FROM calls WHERE company_id=1234 AND (other things)
file_index might be useful in future although presumably initially the files will be stored in the local file system with the same name as that given by the PABX
Steve Davies
@davies147
Jun 10 2016 14:21
I am reserving judgement on the use of company as I am not confident that I understand how it is being used well enough to comment fully. Looking at the schema, either call_id or call_id + company_id could be valid depending on the most common use-case and whether it is useful to limit the data in that manner or not.
I agree that file_indexis probably redundant.
The local filename could be the same as file_name or it could be 'recording_' + file_id
Peter Wilson
@peterw8102
Jun 10 2016 14:26
In the 'users' table you probably don't need to store the role. That should be calculated each time the user is authenticated with the PABX, which covers them becoming an admin or loosing that permission.
eleanorreem
@eleanorreem
Jun 10 2016 14:29
Hi both, these comments are super useful. We're trying to copy your comments over to the github issues #26 as they come . But It would be really great if you could continue to debate on the issue so we can easily keep track of all your comments! Thanks!