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

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)
sql.commit()

To check if a valid extension is called

digits = session.getVariable("digits")

c.execute("SELECT nr FROM extensions WHERE nr=%s" % digits)
if not len(c.fetchall()):
     return # exit python script and hang up