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()