User:Birgit bachler/Freeswitch/sqltable

db sqlite3

create table extensions(nr INTEGER, outthere INTEGER, UNIQUE(nr));
create index ext_idx on extensions(nr);
create table calls(id INTEGER PRIMARY KEY, caller varchar(22), extension varchar(6), recording INTEGER, start_time DATETIME, end_time DATETIME);
create table recording(id INTEGER PRIMARY KEY, filename varchar(33), start_time DATETIME, end_time DATETIME);


insert into calls (caller, extension, start_time) values("+3167892348", "12345", DATETIME("NOW"));
insert into recording (filename) values ("foo1");
update calls set recording=1, end_time=DATETIME("NOW") where id=1;
update recording set rec_length=TIME("5:03") where id=1;

insert into calls (caller, extension, start_time) values("Nobody", "12345", DATETIME("NOW"));
insert into recording (filename) values ("foo2");
update calls set recording=2 where id=3;
update calls set end_time=DATETIME("NOW") where id=2;
update calls set end_time=DATETIME("NOW") where id=3;
update recording set rec_length=TIME("NOW") where id=2;

import sqlite3
 sql = sqlite3.connect("test.db")
c = sql.cursor()

c.execute("select * from calls")
c.fetchall()

c.execute("select * from calls where extension=%s" % 12345)
c.fetchall()

c.execute("select * from calls where id=%s" % 3)
c.fetchall()

c.execute("insert into calls (caller,extension,start_time) values (+123456,00034,DATETIME(\"NOW\"))")
sql.commit()
c.execute("select * from calls where extension=%s" % 3)
c.fetchall()

c.execute("select * from calls where extension=%s" % 34)
c.fetchall()

To create database-tables:

import sqlite3
sql = sqlite3.connect("/home/merglind/var/hello.db")
c = sql.cursor()

# extensions table
c.execute("DROP TABLE IF EXISTS extensions")
c.execute("CREATE TABLE extensions(nr INTEGER, outthere INTEGER, UNIQUE(nr))")
c.execute("DROP INDEX IF EXISTS ext_idx")
c.execute("CREATE INDEX ext_idx ON extensions(nr)")
sql.commit()

# calls table
c.execute("DROP TABLE IF EXISTS calls")
c.execute("CREATE TABLE calls(id INTEGER PRIMARY KEY, caller TEXT, extension TEXT, recording INTEGER, start_time DATETIME, end_time DATETIME)")
c.execute("DROP INDEX IF EXISTS calls_by_ext")
c.execute("CREATE INDEX calls_by_ext ON calls(extension, start_time)")
c.execute("DROP INDEX IF EXISTS calls_by_caller")
c.execute("CREATE INDEX calls_by_caller ON calls(caller, start_time)")
sql.commit()

# recordings table
c.execute("DROP TABLE IF EXISTS recordings")
c.execute("CREATE TABLE recordings(id INTEGER PRIMARY KEY, filename TEXT, start_time DATETIME, end_time DATETIME)")
sql.commit()

Maybe the above groups of execute() calls can be made into multi-line stings, and then executed with executescript()

To initialize extension-nrs

import sqlite3, random

sql = sqlite3.connect("/home/merglind/var/hello.db")
c = sql.cursor()

# how many extensions to add
ext_count = 100

while ext_count:
    ext = random.randrange(100000)
    # check if this number already exists in DB
    c.execute("SELECT * FROM extensions WHERE nr=%d" % ext")
    if len(c.fetchall()):
        continue

    c.execute("INSERT INTO extensions (nr, outthere) values (%d, 0)" % ext)
    sql.commit()

    ext_count -= 1;

To get extensions from the DB that are not yet out there

import sqlite3

sql = sqlite3.connect("/home/merglind/var/hello.db")
c = sql.cursor()

# how many extensions to get

ext_count = 100

c.execute("SELECT (nr, outthere) FROM extensions WHERE outthere=0 LIMIT %d" % ext_count)

for record in c.fetchall():
    (nr, outthere) = record
    print(nr)

    c.execute("UPDATE extensions SET outthere=%d WHERE nr=%d" % (outthere + 1, nr))

sql.commit()

To log incoming calls to DB

import sqlite3
from freeswitch import *

sql = sqlite3.connect("/home/merglind/var/hello.db")
c = sql.cursor()

caller = session.getVariable("caller_id_number")

c.execute('INSERT INTO calls (caller, start_time) values (%d, DATETIME("NOW")' % caller)
# get call_id nr IMMEDIATELY, before sql.commit()
c.execute("SELECT id FROM calls ORDER BY id DESC LIMIT 1")
call_id = c.fetchone()[0]
sql.commit()

What to do if a valid extension is called

digits = session.getVariable("digits")

c.execute("SELECT nr FROM extensions WHERE nr=%s" % digits)
if len(c.fetchall()):
    # if we are here, caller called a valid extension
    c.execute("UPDATE calls SET extension=%s WHERE id=%d" % (digits, call_id))
    sql.commit()

    # check how many messages are recorded under this extension
    c.execute("SELECT recording FROM calls WHERE extension=%s" % digits)
    msg_ids = []
    for record in c.fetchall():
        msg_id = record[0]
        if msg_id == None:
            continue

        msg_ids.append(msg_id)

    if len(msg_ids):   # we have messages
        for msg_id in msg_ids:
            # get filename
            c.execute("SELECT filename FROM recordings WHERE id=%d" % msg_id)
            filename = c.fetchone()[0]
            # play the message here...

    # record a message
    filename = "%s-%d.wav" % (extension, len(msg_ids))
    c.execute('INSERT INTO recordings (filename, start_time) values (%s, DATETIME("NOW")')
    c.execute("SELECT id FROM recordings ORDER BY id DESC LIMIT 1")
    rec_id = c.fetchone()[0]
    c.execute("UPDATE calls SET recording=%d WHERE id=%d" % (rec_id, call_id))
    sql.commit()
    # record file here...
    
    c.execute('UPDATE recordings SET end_time=DATETIME("NOW") WHERE id=%d' % rec_id)
    sql.commit()

And, before leaving the script

c.execute('UPDATE calls SET end_time=DATETIME("NOW") WHERE id=%d' % call_id)
sql.commit()

# close the cursor and the DB-file
c.close()
sql.close()