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