понедельник, 30 ноября 2009 г.

PostgreSQL 8.1 vs. SQLite 3.6.20 in the real application

For testing is used the production PostgreSQL database. I did build SQLite database as described here:
Trigger-based PostgreSQL to SQLite online replication

The test requests is real queries from the production system. The PostgreSQL is too slow on these queries and I search more performance database system for the intranet/internet portal. All tables have needed indices. Some tables are renamed in SQLite database but their names and fields names are similar.

Note
The PostgreSQL quries is rewrited for better index usage but SQLite query planner work fine without any additional tricks. The query conditions are generated by application query builder for user filters and settings and when you can see identical conditions these are not always identical.

Hardware


Intel(R) Core(TM)2 Quad CPU Q6700 @ 2.66GHz
MemTotal: 8310900 kB
HDD: 1 VelociRaptor 10000 rpm


PostgreSQL parameters

work_mem = 100000 # min 64, size in KB
maintenance_work_mem = 2000000 # min 1024, size in KB
shared_buffers = 200000 # min 16 or max_connections*2, 8KB each


SQLite parameters

sqlite> pragma page_size;
4096
sqlite> pragma cache_size;
32000


PostgreSQL version


select version();
version
----------------------------------------------------------------------------------------------------------------
PostgreSQL 8.1.15 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.1.2 20061115 (prerelease) (Debian 4.1.1-21)
(1 row)


SQLite version

sqlite> select sqlite_version();
3.6.20


PostgreSQL DB size

SELECT sum(relpages*8192/1024/1024) as size_mb FROM pg_class WHERE NOT relname LIKE 'pg_%';
size_mb
---------
1248
(1 row)


SQLite DB size

$ ls -lh work.db
-rw-rw-rw- 1 postgres postgres 1.1G 2009-11-30 18:50 work.db


PostgreSQL: the archive search by number (runtime is 1356 ms)


SELECT d.id,
d.phone_number AS phone,
r.name AS region,
p.code AS point,
p.is_priority AS point_is_priority,
c.code AS center_code,
a.value AS status,
u2.name AS user_5,
tp.name AS template,
c.name AS center,
(a.save_date at time zone interval '03:00')::date AS work_date,
date_trunc('second',(a.save_date at time zone interval '03:00')::time) AS work_time
FROM offline.documents d
JOIN auth.users u ON d.user_id = u.id
JOIN auth.regions r ON u.region_id = r.id
JOIN auth.points p ON u.point_id = p.id
JOIN auth.centers c ON u.center_id = c.id
JOIN offline.attributes a ON d.id = a.document_id
AND a.is_last
AND a.value not IN ('Удален', 'Выполнен')
JOIN offline.document_templates tp ON d.document_template_id = tp.id
JOIN auth.users u2 on a.user_id=u2.id
WHERE d.phone_number = 1234567890
--ORDER BY work_date ASC, work_time ASC, status ASC;
ORDER BY d.id;


explain analyze ...

"Sort (cost=79749.02..79749.02 rows=2 width=213) (actual time=1355.607..1355.607 rows=1 loops=1)"
" Sort Key: d.id"
" -> Merge Join (cost=79730.29..79749.01 rows=2 width=213) (actual time=1355.594..1355.598 rows=1 loops=1)"
" Merge Cond: ("outer".document_template_id = "inner".id)"
" -> Sort (cost=79730.29..79730.30 rows=2 width=163) (actual time=1355.494..1355.495 rows=1 loops=1)"
" Sort Key: d.document_template_id"
" -> Merge Join (cost=79718.34..79730.28 rows=2 width=163) (actual time=1355.480..1355.483 rows=1 loops=1)"
" Merge Cond: ("outer".center_id = "inner".id)"
" -> Sort (cost=79718.34..79718.34 rows=2 width=125) (actual time=1355.268..1355.268 rows=1 loops=1)"
" Sort Key: u.center_id"
" -> Merge Join (cost=79465.87..79718.33 rows=2 width=125) (actual time=1355.260..1355.263 rows=1 loops=1)"
" Merge Cond: ("outer".user_id = "inner".id)"
" -> Sort (cost=79465.87..79465.88 rows=2 width=101) (actual time=1352.649..1352.649 rows=1 loops=1)"
" Sort Key: a.user_id"
" -> Merge Join (cost=355.26..79465.86 rows=2 width=101) (actual time=1352.637..1352.642 rows=1 loops=1)"
" Merge Cond: ("outer".id = "inner".document_id)"
" -> Sort (cost=355.26..355.27 rows=2 width=70) (actual time=4.237..4.238 rows=1 loops=1)"
" Sort Key: d.id"
" -> Merge Join (cost=268.02..355.25 rows=2 width=70) (actual time=4.229..4.231 rows=1 loops=1)"
" Merge Cond: ("outer".point_id = "inner".id)"
" -> Sort (cost=268.02..268.03 rows=2 width=61) (actual time=2.765..2.765 rows=1 loops=1)"
" Sort Key: u.point_id"
" -> Merge Join (cost=262.64..268.01 rows=2 width=61) (actual time=2.758..2.761 rows=1 loops=1)"
" Merge Cond: ("outer".region_id = "inner".id)"
" -> Sort (cost=262.64..262.64 rows=2 width=34) (actual time=2.749..2.749 rows=1 loops=1)"
" Sort Key: u.region_id"
" -> Merge Join (cost=10.03..262.63 rows=2 width=34) (actual time=2.742..2.745 rows=1 loops=1)"
" Merge Cond: ("outer".user_id = "inner".id)"
" -> Sort (cost=10.03..10.04 rows=2 width=26) (actual time=0.037..0.038 rows=1 loops=1)"
" Sort Key: d.user_id"
" -> Index Scan using documents_phone_number_idx on documents d (cost=0.00..10.02 rows=2 width=26) (actual time=0.027..0.028 rows=1 loops=1)"
" Index Cond: (phone_number = '1234567890'::text)"
" -> Index Scan using users_pkey on users u (cost=0.00..248.75 rows=1807 width=16) (actual time=0.006..2.021 rows=1625 loops=1)"
" -> Index Scan using regions_pkey on regions r (cost=0.00..5.33 rows=7 width=35) (actual time=0.005..0.007 rows=3 loops=1)"
" -> Index Scan using points_pkey on points p (cost=0.00..84.74 rows=984 width=17) (actual time=0.005..1.094 rows=895 loops=1)"
" -> Index Scan using attributes_document_id_idx on attributes a (cost=0.00..78070.04 rows=416212 width=35) (actual time=1112.968..1347.803 rows=1327 loops=1)"
" Filter: (is_last AND (value <> 'Удален'::text) AND (value <> 'Выполнен'::text))"
" -> Index Scan using users_pkey on users u2 (cost=0.00..248.75 rows=1807 width=32) (actual time=0.008..1.918 rows=1625 loops=1)"
" -> Index Scan using center_pkey on centers c (cost=0.00..11.52 rows=159 width=46) (actual time=0.006..0.149 rows=148 loops=1)"
" -> Index Scan using document_templates_pkey on document_templates tp (cost=0.00..18.53 rows=51 width=58) (actual time=0.005..0.061 rows=51 loops=1)"
"Total runtime: 1355.782 ms"


SQLite: the archive search by number (runtime is 0 ms)


.timer on

SELECT d.id,
d.phone_number AS phone,
r.name AS region,
p.code AS point,
p.is_priority AS point_is_priority,
c.code AS center_code,
a.value AS status,
u2.name AS user_5,
tp.name AS template,
c.name AS center,
datetime(a.save_date,'localtime') as save_date
FROM document d
JOIN user u ON d.user_id = u.id
JOIN region r ON u.region_id = r.id
JOIN point p ON u.point_id = p.id
JOIN center c ON u.center_id = c.id
JOIN document_status a ON d.id = a.document_id
AND a.is_last=1
AND a.value not IN ('Удален', 'Выполнен')
JOIN template tp ON d.template_id = tp.id
JOIN user u2 on a.user_id=u2.id
WHERE d.phone_number = 1234567890
--ORDER BY work_date ASC, work_time ASC, status ASC;
ORDER BY d.id;


The timer result

CPU Time: user 0.000000 sys 0.000000



explain query plan ...

0|0|TABLE document AS d WITH INDEX document_phone_number_idx ORDER BY
1|1|TABLE user AS u USING PRIMARY KEY
2|2|TABLE region AS r USING PRIMARY KEY
3|3|TABLE point AS p USING PRIMARY KEY
4|4|TABLE center AS c USING PRIMARY KEY
5|6|TABLE template AS tp USING PRIMARY KEY
6|5|TABLE document_status AS a WITH INDEX document_status_complex2_idx
7|7|TABLE user AS u2 USING PRIMARY KEY



PostgreSQL: the documents listing (runtime is 1742 ms)


SELECT d.id AS document_id,
d.phone_number AS phone,
r.name AS region,
p.code AS point,
p.is_priority AS point_is_priority,
c.code AS center_code,
a.value AS status,
u2.name AS last_user,
tp.name AS template,
c.name AS center,
(a.save_date at time zone interval '03:00')::date AS work_date,
date_trunc('second',(a.save_date at time zone interval '03:00')::time) AS work_time
FROM offline.documents d
JOIN auth.users u ON d.user_id = u.id
JOIN auth.regions r ON u.region_id = r.id
JOIN auth.points p ON u.point_id = p.id
JOIN auth.centers c ON u.center_id = c.id
JOIN offline.attributes a ON d.id = a.document_id
AND a.is_last
AND (a.value='' OR ''='')
--- AND a.value!='Удален'
JOIN auth.users u2 on a.user_id=u2.id
JOIN offline.document_templates tp ON d.document_template_id = tp.id
AND (tp.id=NULL or ''='')
WHERE d.user_id IN (
SELECT id
FROM auth.users AS uu
WHERE group_id=5
AND (uu.region_id='5'::int or '5'='')
AND (uu.region_id='5'::int or '5'='')
AND (uu.center_id='1249'::int or '1249'='')
AND (uu.center_id='1249'::int or '1249'='')
AND (uu.point_id='1001684'::int or '1001684'='')
AND (uu.point_id='1001684'::int or '1001684'='')
)
AND d.save_date BETWEEN ('2009-11-28'::timestamp) AND ('2009-11-29'::timestamp + interval '1 day')
AND true
AND (d.document_template_id=NULL or ''='')
ORDER BY d.id ASC
OFFSET '0'::int
LIMIT '50'::int;


explain analyze ...

"Limit (cost=69005.02..69005.02 rows=1 width=213) (actual time=1741.917..1741.954 rows=33 loops=1)"
" -> Sort (cost=69005.02..69005.02 rows=1 width=213) (actual time=1741.915..1741.928 rows=33 loops=1)"
" Sort Key: d.id"
" -> Hash Join (cost=3780.16..69005.01 rows=1 width=213) (actual time=1732.782..1741.853 rows=33 loops=1)"
" Hash Cond: ("outer".user_id = "inner".id)"
" -> Hash Join (cost=3141.94..68360.72 rows=1209 width=131) (actual time=1726.624..1736.116 rows=1267 loops=1)"
" Hash Cond: ("outer".user_id = "inner".id)"
" -> Merge Join (cost=2888.68..68086.30 rows=1209 width=107) (actual time=1723.263..1731.276 rows=1267 loops=1)"
" Merge Cond: ("outer".id = "inner".document_id)"
" -> Sort (cost=2888.68..2891.71 rows=1213 width=76) (actual time=5.244..5.754 rows=1267 loops=1)"
" Sort Key: d.id"
" -> Hash Join (cost=30.94..2826.54 rows=1213 width=76) (actual time=1.534..3.882 rows=1267 loops=1)"
" Hash Cond: ("outer".document_template_id = "inner".id)"
" -> Bitmap Heap Scan on documents d (cost=12.28..2789.69 rows=1213 width=26) (actual time=1.412..2.380 rows=1267 loops=1)"
" Recheck Cond: ((save_date >= '2009-11-28 00:00:00'::timestamp without time zone) AND (save_date <= '2009-11-30 00:00:00'::timestamp without time zone))"
" -> Bitmap Index Scan on documents_save_date_idx (cost=0.00..12.28 rows=1213 width=0) (actual time=1.402..1.402 rows=1267 loops=1)"
" Index Cond: ((save_date >= '2009-11-28 00:00:00'::timestamp without time zone) AND (save_date <= '2009-11-30 00:00:00'::timestamp without time zone))"
" -> Hash (cost=18.53..18.53 rows=51 width=58) (actual time=0.109..0.109 rows=51 loops=1)"
" -> Index Scan using document_templates_pkey on document_templates tp (cost=0.00..18.53 rows=51 width=58) (actual time=0.007..0.069 rows=51 loops=1)"
" -> Index Scan using attributes_document_id_idx on attributes a (cost=0.00..63923.25 rows=502485 width=35) (actual time=0.011..1516.060 rows=502756 loops=1)"
" Filter: is_last"
" -> Hash (cost=248.75..248.75 rows=1807 width=32) (actual time=3.342..3.342 rows=1808 loops=1)"
" -> Index Scan using users_pkey on users u2 (cost=0.00..248.75 rows=1807 width=32) (actual time=0.004..2.072 rows=1808 loops=1)"
" -> Hash (cost=638.22..638.22 rows=1 width=94) (actual time=5.070..5.070 rows=1 loops=1)"
" -> Merge Join (cost=551.00..638.22 rows=1 width=94) (actual time=5.059..5.062 rows=1 loops=1)"
" Merge Cond: ("outer".point_id = "inner".id)"
" -> Sort (cost=551.00..551.01 rows=1 width=85) (actual time=4.001..4.001 rows=1 loops=1)"
" Sort Key: u.point_id"
" -> Merge Join (cost=539.06..550.99 rows=1 width=85) (actual time=3.990..3.993 rows=1 loops=1)"
" Merge Cond: ("outer".center_id = "inner".id)"
" -> Sort (cost=539.06..539.07 rows=1 width=47) (actual time=3.854..3.855 rows=1 loops=1)"
" Sort Key: u.center_id"
" -> Merge Join (cost=533.69..539.05 rows=1 width=47) (actual time=3.846..3.848 rows=1 loops=1)"
" Merge Cond: ("outer".region_id = "inner".id)"
" -> Sort (cost=533.69..533.69 rows=1 width=20) (actual time=3.834..3.835 rows=1 loops=1)"
" Sort Key: u.region_id"
" -> Merge Join (cost=280.39..533.68 rows=1 width=20) (actual time=3.827..3.829 rows=1 loops=1)"
" Merge Cond: ("outer".id = "inner".id)"
" -> Index Scan using users_pkey on users u (cost=0.00..248.75 rows=1807 width=16) (actual time=0.010..1.571 rows=1271 loops=1)"
" -> Sort (cost=280.39..280.40 rows=1 width=4) (actual time=1.718..1.718 rows=1 loops=1)"
" Sort Key: uu.id"
" -> HashAggregate (cost=280.37..280.38 rows=1 width=4) (actual time=1.712..1.712 rows=1 loops=1)"
" -> Index Scan using users_pkey on users uu (cost=0.00..280.37 rows=1 width=4) (actual time=1.269..1.706 rows=1 loops=1)"
" Filter: ((group_id = 5) AND (region_id = 5) AND (region_id = 5) AND (center_id = 1249) AND (center_id = 1249) AND (point_id = 1001684) AND (point_id = 1001684))"
" -> Index Scan using regions_pkey on regions r (cost=0.00..5.33 rows=7 width=35) (actual time=0.005..0.010 rows=6 loops=1)"
" -> Index Scan using center_pkey on centers c (cost=0.00..11.52 rows=159 width=46) (actual time=0.005..0.103 rows=79 loops=1)"
" -> Index Scan using points_pkey on points p (cost=0.00..84.74 rows=984 width=17) (actual time=0.006..0.790 rows=623 loops=1)"
"Total runtime: 1742.239 ms"


SQLite: the documents listing (runtime is 12 ms)


.timer on

SELECT d.id AS document_id,
d.phone_number AS phone,
r.name AS region,
p.code AS point,
p.is_priority AS point_is_priority,
c.code AS center_code,
a.value AS status,
u2.name AS last_user,
tp.name AS template,
c.name AS center,
datetime(a.save_date,'localtime') as save_date
FROM document d
JOIN user u ON d.user_id = u.id
JOIN region r ON u.region_id = r.id
JOIN point p ON u.point_id = p.id
JOIN center c ON u.center_id = c.id
JOIN document_status a ON d.id = a.document_id
AND a.is_last
AND (a.value='' OR ''='')
--- AND a.value!='Удален'
JOIN user u2 on a.user_id=u2.id
JOIN template tp ON d.template_id = tp.id
AND (tp.id=NULL or ''='')
WHERE d.user_id IN (
SELECT id
FROM user AS uu
WHERE group_id=5
AND (uu.region_id=5 or 5='')
AND (uu.region_id=5 or 5='')
AND (uu.center_id=1249 or 1249='')
AND (uu.center_id=1249 or 1249='')
AND (uu.point_id=1001684 or 1001684='')
AND (uu.point_id=1001684 or 1001684='')
)
AND d.save_date BETWEEN julianday('2009-11-28') AND julianday('2009-11-29','+1 day')
AND 1=1
AND (d.template_id=NULL or ''='')
ORDER BY d.id ASC
LIMIT 50
OFFSET 0;


The timer result is

CPU Time: user 0.004001 sys 0.008001


explain query plan ...

0|0|TABLE document AS d WITH INDEX document_user_id_idx
1|1|TABLE user AS u USING PRIMARY KEY
2|2|TABLE region AS r USING PRIMARY KEY
3|3|TABLE point AS p USING PRIMARY KEY
4|4|TABLE center AS c USING PRIMARY KEY
5|7|TABLE template AS tp USING PRIMARY KEY
6|5|TABLE document_status AS a WITH INDEX document_status_complex2_idx
7|6|TABLE user AS u2 USING PRIMARY KEY
0|0|TABLE user AS uu


PostgreSQL: the documents listing (runtime is 3430 ms) - simplified query


explain analyze
SELECT d.id AS document_id,
d.phone_number AS phone,
r.name AS region,
p.code AS point,
p.is_priority AS point_is_priority,
c.code AS center_code,
a.value AS status,
u2.name AS last_user,
tp.name AS template,
c.name AS center,
(a.save_date at time zone interval '03:00')::date AS work_date,
date_trunc('second',(a.save_date at time zone interval '03:00')::time) AS work_time
FROM offline.documents d
JOIN auth.users u ON d.user_id = u.id
JOIN auth.regions r ON u.region_id = r.id
JOIN auth.points p ON u.point_id = p.id
JOIN auth.centers c ON u.center_id = c.id
JOIN offline.attributes a ON d.id = a.document_id
AND a.is_last
AND (a.value='' OR ''='')
--- AND a.value!='Удален'
JOIN auth.users u2 on a.user_id=u2.id
JOIN offline.document_templates tp ON d.document_template_id = tp.id
AND (tp.id=NULL or ''='')
WHERE 1=1
AND u.group_id=5
AND (u.region_id='5'::int or '5'='')
AND (u.region_id='5'::int or '5'='')
AND (u.center_id='1249'::int or '1249'='')
AND (u.center_id='1249'::int or '1249'='')
AND (u.point_id='1001684'::int or '1001684'='')
AND (u.point_id='1001684'::int or '1001684'='')
AND d.save_date BETWEEN ('2009-11-28'::timestamp) AND ('2009-11-29'::timestamp + interval '1 day')
AND true
AND (d.document_template_id=NULL or ''='')
ORDER BY d.id ASC
OFFSET '0'::int
LIMIT '50'::int;


explain analyze ...

"Limit (cost=300123674.36..300126187.10 rows=1 width=213) (actual time=3396.515..3397.608 rows=33 loops=1)"
" -> Merge Join (cost=300123674.36..300126187.10 rows=1 width=213) (actual time=3396.514..3397.585 rows=33 loops=1)"
" Merge Cond: ("outer".document_id = "inner".id)"
" -> Sort (cost=120565.48..121821.83 rows=502542 width=59) (actual time=2964.899..3162.001 rows=502710 loops=1)"
" Sort Key: a.document_id"
" -> Hash Join (cost=253.27..72977.55 rows=502542 width=59) (actual time=3.427..2068.470 rows=503382 loops=1)"
" Hash Cond: ("outer".user_id = "inner".id)"
" -> Index Scan using attributes_document_id_idx on attributes a (cost=0.00..63929.80 rows=502542 width=35) (actual time=0.011..1482.646 rows=503426 loops=1)"
" Filter: is_last"
" -> Hash (cost=248.75..248.75 rows=1807 width=32) (actual time=3.405..3.405 rows=1808 loops=1)"
" -> Index Scan using users_pkey on users u2 (cost=0.00..248.75 rows=1807 width=32) (actual time=0.007..2.140 rows=1808 loops=1)"
" -> Sort (cost=300003108.88..300003108.88 rows=1 width=158) (actual time=4.889..4.902 rows=33 loops=1)"
" Sort Key: d.id"
" -> Merge Join (cost=300003090.19..300003108.87 rows=1 width=158) (actual time=4.660..4.814 rows=33 loops=1)"
" Merge Cond: ("outer".document_template_id = "inner".id)"
" -> Sort (cost=300003090.19..300003090.20 rows=1 width=108) (actual time=4.634..4.643 rows=33 loops=1)"
" Sort Key: d.document_template_id"
" -> Hash Join (cost=300000306.70..300003090.18 rows=1 width=108) (actual time=3.468..4.566 rows=33 loops=1)"
" Hash Cond: ("outer".user_id = "inner".id)"
" -> Bitmap Heap Scan on documents d (cost=12.28..2789.69 rows=1213 width=26) (actual time=1.414..2.334 rows=1267 loops=1)"
" Recheck Cond: ((save_date >= '2009-11-28 00:00:00'::timestamp without time zone) AND (save_date <= '2009-11-30 00:00:00'::timestamp without time zone))"
" -> Bitmap Index Scan on documents_save_date_idx (cost=0.00..12.28 rows=1213 width=0) (actual time=1.403..1.403 rows=1267 loops=1)"
" Index Cond: ((save_date >= '2009-11-28 00:00:00'::timestamp without time zone) AND (save_date <= '2009-11-30 00:00:00'::timestamp without time zone))"
" -> Hash (cost=300000294.42..300000294.42 rows=1 width=90) (actual time=1.684..1.684 rows=1 loops=1)"
" -> Nested Loop (cost=300000000.00..300000294.42 rows=1 width=90) (actual time=1.251..1.680 rows=1 loops=1)"
" -> Index Scan using users_pkey on users u (cost=0.00..280.37 rows=1 width=16) (actual time=1.221..1.644 rows=1 loops=1)"
" Filter: ((group_id = 5) AND (region_id = 5) AND (region_id = 5) AND (center_id = 1249) AND (center_id = 1249) AND (point_id = 1001684) AND (point_id = 1001684))"
" -> Nested Loop (cost=200000000.00..200000014.03 rows=1 width=98) (actual time=0.025..0.029 rows=1 loops=1)"
" -> Nested Loop (cost=100000000.00..100000009.40 rows=1 width=81) (actual time=0.014..0.016 rows=1 loops=1)"
" -> Index Scan using regions_pkey on regions r (cost=0.00..4.26 rows=1 width=35) (actual time=0.006..0.007 rows=1 loops=1)"
" Index Cond: (5 = id)"
" -> Index Scan using center_pkey on centers c (cost=0.00..5.13 rows=1 width=46) (actual time=0.006..0.007 rows=1 loops=1)"
" Index Cond: (1249 = id)"
" -> Index Scan using points_pkey on points p (cost=0.00..4.62 rows=1 width=17) (actual time=0.008..0.009 rows=1 loops=1)"
" Index Cond: (1001684 = id)"
" -> Index Scan using document_templates_pkey on document_templates tp (cost=0.00..18.53 rows=51 width=58) (actual time=0.005..0.086 rows=78 loops=1)"
"Total runtime: 3429.668 ms"


SQLite: the documents listing (runtime is 12 ms) - simplified query


.timer on

SELECT d.id AS document_id,
d.phone_number AS phone,
r.name AS region,
p.code AS point,
p.is_priority AS point_is_priority,
c.code AS center_code,
a.value AS status,
u2.name AS last_user,
tp.name AS template,
c.name AS center,
datetime(a.save_date,'localtime') as save_date
FROM document d
JOIN user u ON d.user_id = u.id
JOIN region r ON u.region_id = r.id
JOIN point p ON u.point_id = p.id
JOIN center c ON u.center_id = c.id
JOIN document_status a ON d.id = a.document_id
AND a.is_last
AND (a.value='' OR ''='')
--- AND a.value!='Удален'
JOIN user u2 on a.user_id=u2.id
JOIN template tp ON d.template_id = tp.id
AND (tp.id=NULL or ''='')
WHERE 1=1
AND u.group_id=5
AND (u.region_id=5 or 5='')
AND (u.region_id=5 or 5='')
AND (u.center_id=1249 or 1249='')
AND (u.center_id=1249 or 1249='')
AND (u.point_id=1001684 or 1001684='')
AND (u.point_id=1001684 or 1001684='')
AND d.save_date BETWEEN julianday('2009-11-28') AND julianday('2009-11-29','+1 day')
AND 1=1
AND (d.template_id=NULL or ''='')
ORDER BY d.id ASC
LIMIT 50
OFFSET 0;


The timer result is

CPU Time: user 0.004000 sys 0.008000


explain query plan ...

0|0|TABLE document AS d WITH INDEX document_save_date_idx
1|1|TABLE user AS u USING PRIMARY KEY
2|2|TABLE region AS r USING PRIMARY KEY
3|3|TABLE point AS p USING PRIMARY KEY
4|4|TABLE center AS c USING PRIMARY KEY
5|7|TABLE template AS tp USING PRIMARY KEY
6|5|TABLE document_status AS a WITH INDEX document_status_complex2_idx
7|6|TABLE user AS u2 USING PRIMARY KEY


Upd.
На форуме Dmitry Arefiev задал вопросы по трактовке полученных измерений. Привожу здесь сами вопросы и свои ответы:

- среда исполнения запросов;
- сколько записей каждый запрос должен возвратить;
- что за время мерилось;
- что там со статистикой;
- расположены файлы БД на одном носителе или на разных;
- кэш файловой системы / СУБД холодный или горячий;


1. Указано ."timer on", а с точки начинаются команды эскулайт шелла. Ни в каких врапперах этих команд нет, поскольку имплементированы они именно в коде шелла эскулайт.

2. Приведен explain analyze в постгресе, в котором кол-во возвращаемых записей подсчитывается:


"... (actual time=1355.607..1355.607 rows=1 loops=1)"
...
"... (actual time=1741.917..1741.954 rows=33 loops=1)"


Т.е. первый запрос вернет 1 строку, а второй - 33.

3. Сказано, что измерялось время встроенным таймером в эскулайт и командой explain analyze в постгресе. Смотрим "man time"
user - время CPU, которое занял пользователь (сумма значений tms_utime и
tms_cutime в структуре struct tms , которая возвращается вызовом times(2)),
sys - время CPU занятое системой (сумма значений tms_stime и tms_cstime в структуре struct tms ,
которая возвращается вызовом times(2)).
В документации постгреса сказано, что explain analyze вернет реальное время, опять же, обратимся к man time
real - реальное время выполнение между вызовом и завершением

Постгрес не считает время на передачу данных клиенту, т.к. это время не может быть посчитано самой СУБД. Показывается время извлечения данных, притом нужно учесть, что для большого кол-ва записей может потребоваться еще дополнительно значительное время на их передачу клиенту. Эскулайт исполняется в процессе приложения, потому извлеченные данные сразу же доступны клиенту, не требуется их куда-либо передавать.

Поясню на примере:

time sqlite3 work.db '.read query'
real 0m0.014s
user 0m0.012s
sys 0m0.004s

Здесь 14 мс - время на выборку, передачу данных клиенту и вывод их на консоль. Процессор многоядерный, потому real < user+sys.

4. Постгрес о наличи своей статистики отрапортовал. Притом, поскольку реальный результат совпал с ожидаемым (см. explain analyze), известно, что статистика актуальная. В эскулайте без разницы, была собрана статистика командой analyze или нет, потому и не упоминается.

5. В "Hardware" указан 1 диск, на нем все и расположено. А где же еще. Есть еще системный диск и проч., но в тестах они не участвуют, потому и не упоминаются.

6. Для эскулайт время sys как раз показывает заполнение кэша ФС, если кэш "горячий", то это время равно 0. В постгресе кэш горячий, т.к. сказано, что это _рабочая_ система.

Trigger-based PostgreSQL to SQLite online replication

For some reasons may be useful online replication from PostgreSQL database to SQLite database or databases. I write this as set of pltclu procedures for my PostgreSQL database like to


CREATE OR REPLACE FUNCTION document_status_sqlite3()
RETURNS "trigger" AS
$BODY$

set t1 [clock clicks]
load /usr/share/tcltk/tcl8.5/sqlite3/libtclsqlite3.so sqlite3
set db_file /var/www/project_name/res/dataset/work.db
sqlite3 db $db_file
db timeout 4000
# db eval {PRAGMA journal_mode = PERSIST}
if { $TG_op eq "INSERT" } {
set save_date [string range $NEW(save_date) 0 18]
db eval {insert into document_status (save_date,document_id,
user_id,value,
is_last,is_last_status,is_first)
values (julianday($save_date,'-3 hour'),$NEW(document_id),
$NEW(user_id),$NEW(value),
case when $NEW(is_last)='t' then 1 else 0 end,
case when $NEW(is_last_status)='t' then 1 else 0 end,
case when $NEW(is_first)='t' then 1 else 0 end)}
} elseif { $TG_op eq "UPDATE"} {

} elseif { $TG_op eq "DELETE"} {

}
db close
set t2 [clock clicks]
set t [expr ($t2-$t1)/1000]
elog NOTICE "SQLITE trigger attribute: $t ms"
return OK

$BODY$
LANGUAGE 'pltclu' VOLATILE SECURITY DEFINER;
ALTER FUNCTION document_status_sqlite3() OWNER TO offline;


CREATE TRIGGER trigger_document_status_sqlite3
AFTER INSERT OR UPDATE OR DELETE
ON document_status
FOR EACH ROW
EXECUTE PROCEDURE document_status_sqlite3();


The SQLite db is placed on single WD VelociRaptor 10000 rpm drive.


$ tail -f /var/log/postgresql-8.1/current -n 2000|grep "SQLITE trigger attribute"
2009-11-30 13:54:10 MSK NOTICE: SQLITE trigger attribute: 0 ms
2009-11-30 13:54:10 MSK NOTICE: SQLITE trigger attribute: 0 ms
2009-11-30 13:54:10 MSK NOTICE: SQLITE trigger attribute: 23 ms
2009-11-30 13:54:35 MSK NOTICE: SQLITE trigger attribute: 0 ms
2009-11-30 13:54:35 MSK NOTICE: SQLITE trigger attribute: 0 ms
2009-11-30 13:54:35 MSK NOTICE: SQLITE trigger attribute: 13 ms
2009-11-30 13:54:41 MSK NOTICE: SQLITE trigger attribute: 0 ms
2009-11-30 13:54:41 MSK NOTICE: SQLITE trigger attribute: 10 ms
2009-11-30 13:54:43 MSK NOTICE: SQLITE trigger attribute: 6 ms
2009-11-30 13:54:54 MSK NOTICE: SQLITE trigger attribute: 0 ms
2009-11-30 13:54:54 MSK NOTICE: SQLITE trigger attribute: 0 ms
2009-11-30 13:54:54 MSK NOTICE: SQLITE trigger attribute: 11 ms
2009-11-30 13:55:20 MSK NOTICE: SQLITE trigger attribute: 0 ms
2009-11-30 13:55:20 MSK NOTICE: SQLITE trigger attribute: 8 ms
2009-11-30 13:55:48 MSK NOTICE: SQLITE trigger attribute: 0 ms
2009-11-30 13:55:48 MSK NOTICE: SQLITE trigger attribute: 14 ms
2009-11-30 13:56:20 MSK NOTICE: SQLITE trigger attribute: 6 ms
2009-11-30 13:56:57 MSK NOTICE: SQLITE trigger attribute: 6 ms


The reports building on SQLite is much faster on the same hardware. We can place SQLite db to tmpfs (in-memory temp storage) for increasing write TPS (transactions per seconds) but on my hardware SQLite has the similar write TPS as PostgreSQL 8.1 (with fsync enabled in postgresql.conf).

четверг, 26 ноября 2009 г.

Структура базы телефонного биллинга

Для добавления новых коллекторов данных необходимо и достаточно создать БД определенного формата и заполнять в ней таблицу трафика (telephony_log). Привожу код утилиты, инициирующей структуру требуемой БД (утилита вызывается из коллекторов при создании новой БД при ротации). Также рекомендуем просмотреть код поставляемых коллекторов (файлы telephony_agent_tacacs.tcl и telephony_agent_callbuilder.tcl, размером около 15 килобайт каждый).

Использование

./telephony_dataset_init.tcl
Утилита инициализации БД для хранения телефонной статистики.
Создает БД SQLite3 с указанным именем. Если не удалось создать БД, возвращает ошибку, иначе завершается без выдачи сообщений.
Примечание: используются только стандартные функции СУБД SQLite 3.5.9, модули расширения для заполнения базы не требуются.

Используйте программу следующим образом:
./telephony_dataset_init.tcl путь_БД


telephony_dataset_init.tcl

#!/usr/bin/tclsh8.5
# /opt/bin/telephony_dataset_init.tcl /srv/dataset/telephony/test.db
package require sqlite3

proc error {message} {
puts $message
exit
}

if {$argc!=1} {
error "Утилита инициализации БД для хранения телефонной статистики.
Создает БД SQLite3 с указанным именем. Если не удалось создать БД, возвращает ошибку, иначе завершается без выдачи сообщений.
Примечание: используются только стандартные функции СУБД SQLite 3.5.9, модули расширения для заполнения базы не требуются.

Используйте программу следующим образом:
\t$argv0 путь_БД
"
}

# операционная база, хранит последние записи
# старые записи могут сбрасываться в базы по месяцам или по годам в зависимости от скорости их заполнения
sqlite3 db [file join [lindex $argv 0]]
db timeout 2000
# структура базы фиксирована
# моментом потребления услуги считается время окончания разговора, хотя зависящая от времени стоимость считается по началу
db eval {
create table if not exists telephony_log (
nas_name text not null,
username text not null,
port blob not null,
date_start real not null,
duration integer not null,
origin text not null,
src text not null,
dst text not null,
code integer not null,
is_new integer not null default 1,
unique (nas_name,port,duration,origin,date_start) on conflict ignore
);
CREATE INDEX if not exists telephony_log_date_start_idx on telephony_log(date_start);
CREATE INDEX if not exists telephony_log_is_new_idx on telephony_log(is_new);
CREATE INDEX if not exists telephony_log_src_date_start_idx on telephony_log(src,date_start);
CREATE INDEX if not exists telephony_log_dst_date_start_idx on telephony_log(dst,date_start);

CREATE TABLE telephony_log_rating (
save_date REAL NOT NULL DEFAULT (julianday('now')),
delete_date REAL,
log_id integer not null, -- идентификатор биллингуемой записи
user_service_id integer not null, -- услуга пользователя, в которой указан тариф, оборудование, пользователь
src_user_id integer, -- идентификатор пользователя, которому принадлежит вызывающий номер
dst_user_id integer, -- идентификатор пользователя, которому принадлежит вызываемый номер
port_user_id integer, -- идентификатор пользователя, которому принадлежит порт
destname text collate NOCASE, -- название направления
destcode text, -- префикс E.164 номера телефона, текстовый для возможности хранения начинающихся с нуля и длинных префиксов
rcode text collate NOCASE, -- код региона
price real not null, -- стоимость минуты разговора
duration integer not null default 0, -- тарифицируемая продолжительность разговора, целых секунд
cost real not null, -- цена, единиц валюты currency, всегда без НДС, если cost>=0 то balance=active, иначе balance=passive
is_new integer not null default 1,
unique (log_id,user_service_id) on conflict replace -- услугу можно пробиллинговать только один раз
);
CREATE INDEX if not exists telephony_log_rating_user_service_id_idx on telephony_log_rating(user_service_id);
CREATE INDEX if not exists telephony_log_rating_is_new_idx on telephony_log_rating(is_new);
CREATE INDEX if not exists telephony_log_rating_log_id_idx on telephony_log_rating(log_id);

create table if not exists telephony_log_error (
save_date REAL NOT NULL DEFAULT (julianday('now')),
delete_date REAL,
log_id integer not null, -- идентификатор биллингуемой записи
user_service_id integer not null, -- услуга пользователя, в которой указан тариф, оборудование, пользователь
message text not null, -- сообщение об ошибке
unique (log_id,user_service_id) on conflict replace -- сохраняем только последнюю ошибку, т.к. при возникновении ошибки обработка записи прекращается
);
CREATE INDEX if not exists telephony_log_error_user_service_id_idx on telephony_log_error(user_service_id);
CREATE INDEX if not exists telephony_log_error_log_id_idx on telephony_log_error(log_id);

CREATE TABLE telephony_log_counter (
save_date REAL NOT NULL DEFAULT (julianday('now')),
delete_date REAL,
user_service_id integer not null, -- услуга пользователя, в которой указан тариф, оборудование, пользователь
value int not null, -- значение счетчика, представляет собой продолжительность разговоров в минутах
unique (user_service_id) on conflict replace -- при увеличении счетчика вставляется новая запись, а старая автоматически стирается
);
CREATE INDEX if not exists telephony_log_counter_user_service_id_idx on telephony_log_counter(user_service_id);

CREATE VIEW view_telephony_log_result as
select (select count(*) from telephony_log) as log,
(select count(*) from telephony_log_rating) as rating,
(select count(*) from telephony_log_error) as error;
}
db close

Использование adp-wrapper в биллинге

Выгрузка отчетов
В некоторых случаях бывает удобным иметь возможность получать отчеты с помощью скрипта, например, для их рассылки пользователям на e-mail. Для автоматизации таких задач нами предлагается скрипт-обертка adp_wrapper, обеспечивающий выполнение adp-скриптов без обращения к веб-серверу.

Пример:

./adp_wrapper.tcl $MBGROOT/www/ext/report_ext_01/report_html_all.adp date $date user_id $id \
> $dir/${login}_all.html 2>/dev/null


В комплект входит скрипт telephony_report_ext_01.tcl для выгрузки форм расширенного отчета №1, который вызывается следующим образом:

MBGROOT=/var/www/billing2 ./telephony_report_ext_01.tcl 2009-10-01 2009-11-01


Приведенный ниже скрипт построит отчеты за 10-й и 11-й месяцы 2009 года для всех активных абонентов системы (за исключением провайдеров). Аналогично могут быть написаны скрипты для получения любых других отчетов, также можно добавить рассылку их на e-mail и т.п.

telephony_report_ext_01.tcl

#!/usr/bin/tclsh8.5
# start as
# AOLROOT=/var/www/billing2 ./telephony_report_ext_01.tcl
package require sqlite3

if {[info exists env(AOLROOT)] == 0} {
puts stderr {Not defined environment variable AOLROOT}
exit
}

sqlite3 db :memory:
db restore [file join $env(AOLROOT) res dataset work].db
db timeout 2000
db cache size 100

set date [db onecolumn {select date('now','start of month','-1 month')}]
set dir $env(AOLROOT)/www/share/$date
catch {file delete -force $dir}
catch {file mkdir $dir}
set user_id 229

db eval {SELECT id, login FROM view_user WHERE type != 'system' AND is_provider = 0
AND delete_date IS NULL ORDER BY name ASC} {
exec ./adp_wrapper.tcl $env(AOLROOT)/www/ext/report_ext_01/report_html_all.adp \
date
$date user_id $id > $dir/${login}_all.html 2>/dev/null
exec ./adp_wrapper.tcl $env(AOLROOT)/www/ext/report_ext_01/report_html_c.adp \
date $date user_id $id > $dir/${login}_c.html 2>/dev/null
exec ./adp_wrapper.tcl $env(AOLROOT)/www/ext/report_ext_01/report_html_cd.adp \
date $date user_id $id > $dir/${login}_cd.html 2>/dev/null
}
db close


Upd.
После установки пакета mbg-telephony-report-* можно делать вот так:

MBGROOT=/var/www/billing-admin telephony_report_ext_01 2009-12-01

Результаты будут сохранены в /var/www/billing-admin/www/share

Утилиты телефонного биллинга

Представленное решение для биллинга поддерживает распределенную работу компонентов и их дублирование для реализации отказоустойчивости. В случае запуска всех компонентов на одном сервере конфигурация может быть достаточно простой, причем ее настройка для распределенной работы на нескольких хостах не представляет больших сложностей.

Представленное решение включает в себя следующие файлы: набор тиклевых библиотек, некоторое множество SQLite баз данных, веб-сервер AOL Server с adp-скриптами для генерации web-интерфейса, набор консольных утилит и некоторые другие ресурсы.

Система обрабатывает миллионы звонков в месяц на десктопном оборудовании без каких-либо дополнительных действий по оптимизации баз данных. Для обработки большего числа звонков необходимо ежесуточно выполнять команду VACUUM для БД статистики за текущий месяц. При еще больших масштабах может потребоваться разделять БД не по месяцам, а по неделям или даже дням. "Ядро" биллинговой системы выполняет обработку около 500 звонков в секунду в расчете на 1 ядро процессора (тестировалось на Intel(R) Core(TM)2 Quad CPU Q6700 @ 2.66GHz); при этом один звонок может биллинговаться по двум-трем тарифам. Итак, на сервере с 1 процессором Core Quad можно обработать около 500 звонков ежесекундно (предполагается, что коллекторы запущены на этой же системе и также потребляют процессорное время). Обработка звонков выполняется пакетно, изменяя интервал от минимального 3 секунды и до сколь угодно большого, можно "сгладить" пиковую нагрузку. Для большинства реальных применений такой производительности более чем достаточно. Примечание: нагрузочные тесты для коллекторов будут выложены позднее.

Примечание
В открытом доступе выложены коллекторы и набор утилит для создания БД и проч. Система биллингования в настоящее время является закрытым продуктом.

Установка утилит

Используемая операционная система (ОС) - debian lenny. При необходимости можно пересобрать пакеты для других операционных систем или просто установить из исходников наших пакетов (не забыв патчи), но мы рекомендуем именно debian.

Репозиторий для Debian lenny:

deb http://mobigroup.ru/debian/ lenny main contrib non-free
deb-src http://mobigroup.ru/debian/ lenny main contrib non-free


Установить ключ с подписью репозитория:

sudo aptitude install debian-mobigroup-keyring


Следует установить следующие пакеты:

sudo aptitude install mbg-telephony-dataset mbg-telephony-tacacs-utils mbg-telephony-m200-utils


В настоящий момент в репозитории выложена сборка -comintern пакетов коллекторов, в которую включены "обертки" для скриптов с настроенными путями к лог-файлам, файлам БД и проч. Рекомендуем воспользоваться именно этими пакетами, выполнив соответствующую настройку или написав свои скрипты для вызова базовых утилит.

sudo aptitude install mbg-telephony-dataset \
mbg-telephony-tacacs-utils-comintern \
mbg-telephony-m200-utils-comintern


Пакет mbg-telephony-dataset содержит утилиты управления БД трафика (создание, биллингование), mbg-telephony-tacacs-utils - обеспечивает сбор данных с демонов tacacs (АТС Cisco) и mbg-telephony-m200-utils - выполняет сбор данных с демонов callbuilder (АТС m200).

Примечание: для работы "ядра" биллинга используются также следующие пакеты из вышеназванного репозитория:

sudo aptitude install sqlite3 libsqlite3-tcl tcl8.5

Но для работы описываемых здесь утилит достаточно и стандартных версий этих пакетов.

Файлы и каталоги
Основным параметром является корневая директория размещения каталогов MBGROOT, где в поддиректориях располагаются вышеперечисленные файлы, за исключением некоторых утилит, полностью независимых от основной системы (в т.ч. коллекторов). Структура директорий фиксирована, так что этого параметра достаточно, чтобы найти любой файл системы.

Любые функции системы могут быть вызваны из скриптов, не обращаясь к веб-интерфейсу. Ниже приведено описание некоторых скриптов из комплекта поставки системы, предназначенных для сбора и предварительной обработки траффика, сохранения его в БД и биллингования, а также начисления абонентской платы и выполнения заданий по расписанию по переключению услуг и тарифных планов.

Базы данных
Для понимания работы скриптов следует отметить, что система имеет основную БД, в которой хранится описание пользователей, услуг и проч., и набор БД с трафиком, разделенных по месяцам (типы трафика разделяются по директориям, телефония хранится в директории telephony). Коллекторы сбора данных с АТС сразу же производят месячную ротацию БД. При биллинговании необходимо и достаточно работать с активной БД траффика (примечание: в случае использования распределенной архитектуры следует выполнить биллингование за прошлый месяц по завершению сбора статистики с удаленных узлов). БД траффика допускают слияние - можно объединить две базы в одну без образования дубликатов, но при этом следует учитывать, что результаты биллингования не должны объединяться (допустимо объединять только таблицы telephony_log). Пробиллингованные записи имеют соответствующий флаг (telephony_log.is_new=0), так что уже обработанные записи при запуске биллингатора игнорируются, следовательно, его можно запускать сколь угодно часто (в том числе, можно биллинговать в реальном времени, например, с интервалом 30 секунд, установив такой же или меньший интервал синхронизации для коллекторов).

Коллекторы
Коллекторы для сбора данных имеют настраиваемый интервал синхронизации (параметр командной строки -interval), который указывает промежуток времени между операциями записи собранной статичтики в основную БД (накапливаемая в течении этого интервала статистика хранится в памяти коллектора). При невозможности выполнить синхронизацию коллектор сообщает об ошибке в лог и пропускает синхронизацию до следующего раза, продолжая накапливать данные. Коллектор способен в течении многих дней и даже месяцев аккумулировать данные при невозможности синхронизации - при возникновении такой ситуации просто устраните причину и данные будут синхронизированы (используется эффективный по объему и быстродействию формат хранения в памяти, так что 1 Гб ОЗУ достаточно для хранения статистики миллионов звонков). В целях обеспечения отказоустойчивости рекомендуется запуск нескольких коллекторов на разных хостах,
сохраняющих данные каждый в свою БД с периодическим слиянием этих БД в основную. Объединение статистики двух баз может быть выполнено следующей командой:

sqlite3 /mnt/host1/2009-10-01.db '.dump telephony_log'|egrep "BEGIN|INSERT|COMMIT" \
|sqlite3 2009-10-01.db

Здесь данные траффика из /mnt/host1/2009-10-01.db (вы можете как примонтировать удаленную ФС в /mnt/host1/, так и просто скопировать удаленный файл) копируются в файл 2009-10-01.db (напомним, дубликаты автоматически игнорируются).

Upd. Также вы можете воспользоваться предоставляемой нами утилитой для master-slave репликации SQLite баз.

Заметим, что поставляемые в комплекте коллекторы умеют как работать в режиме демона, собирая статистику в реальном времени, так и импортировать сохраненные на диск лог-файлы. Это позволяет настроить биллинговую систему на работу в режиме реального времени с обеспечением отказоустойчивости (запустив по завершению месяца импорт всех дисковых логов, на тот случай, если произошли какие-то потери - актуально в случае отсутствия резервных хостов сбора данных) или выполнять ежедневную/ежемесячную обработку накопленных логов (если вам по каким-либо причинам предпочтителен именно этот вариант).

Коллектор демона tacacs+ (сбор данных с cisco)
telephony_agent_tacacs

$ /usr/bin/telephony_agent_tacacs
Коллектор логов АТС cisco, выдаваемых программой tacacs+ в сокет (режим -demon 1) или
сохраненных на диск (режим -demon 0).
Результат обработки сохраняется в виде лог-файлов универсального формата в указанной директории.
Используйте программу следующим образом:
/usr/bin/telephony_agent_tacacs -host хост -interval интервал_сохранения_секунд \
-dataset директория_хранения_баз [-ports "порт1 порт2 ... "]
или
cat лог_файл | /usr/bin/telephony_agent_tacacs -dataset директория_хранения_баз

Параметры
-host
-ports
-interval
нужно указывать только в режиме демона (-demon 1).


Пример скрипта для запуска в режиме демона:
telephony_agent_tacacs-demon

/usr/bin/telephony_agent_tacacs -host nebu -interval 30 -ports 1881 -demon 1 \
-dataset /srv/dataset/telephony


Коллектор АТС m200
telephony_agent_callbuilder

$ ./telephony_agent_callbuilder
Коллектор логов АТС м200, выдаваемых программой callbuilder в сокет (режим -demon 1) или
сохраненных на диск (режим -demon 0).
Результат обработки сохраняется в виде лог-файлов универсального формата в указанной директории.
Используйте программу следующим образом:
/usr/bin/telephony_agent_callbuilder -host хост -interval интервал_сохранения_секунд \
-dataset директория_хранения_баз [-ports "порт1 имя_атс1 порт2 имя_атс2 ... "]
или
cat лог_файл | /usr/bin/telephony_agent_callbuilder -dataset директория_хранения_баз \
-ports "stdin имя_атс"

Параметры
-host
-interval
нужно указывать только в режиме демона (-demon 1).


Пример скрипта для запуска в режиме демона:
telephony_agent_callbuilder-demon

/usr/bin/telephony_agent_callbuilder -host localhost -interval 30 -demon 1 \
-dataset /srv/dataset/telephony \
-ports "10211 vk-pbx-1 10221 nati-txe-1 10231 vat-pbx-1 10241 vk-pbx-2 10251 \
m9-txe-1 10261 vat-pbx-2 10271 vk-txe-1"


Биллингование телефонного трафика

MBGROOT=/var/www/billing2 /usr/bin/telephony_rating_monthly \
`sqlite3 :memory: "select date('now','start of month')"`
или
MBGROOT=/var/www/billing2 /usr/bin/telephony_rating_monthly_force \
`sqlite3 :memory: "select date('now','start of month')"`

При вызове telephony_rating_monthly_force будет произведен полный пересчет за текущий месяц. Пересчет за один день не может быть выполнен, т.к. стоимость звонков в общем случае зависит от значений кумулятивных счетчиков.
Аналогично может быть выполнено биллингование за любой месяц.

Начисление абонентской платы

MBGROOT=/var/www/billing2 /usr/bin/billing_cost_daily \
`sqlite3 :memory: "select date('now','-1 day')"`
или
MBGROOT=/var/www/billing2 /usr/bin/billing_cost_daily_force \
`sqlite3 :memory: "select date('now','-1 day')"`

При вызове billing_cost_daily_force будет выполнено переначисление абонентской платы для всех услуг за указанный день.
Аналогично может быть выполнено начисление за любой день.

Кроме того, мы поставляем дополнительную утилиту month, которая выводит список всех дней указанного месяца. С помощью этой утилиты можно, например, легко произвести начисление абонентской платы за целый месяц:

MBGROOT=/var/www/billing2 /usr/bin/billing_cost_daily `month 2009-11-01|xargs`


Поясним список аргументов, передаваемый billing_cost_daily:

$month 2009-11-01|xargs
2009-11-01 2009-11-02 2009-11-03 2009-11-04 2009-11-05 2009-11-06 2009-11-07 \
2009-11-08 2009-11-09 2009-11-10 2009-11-11 2009-11-12 2009-11-13 2009-11-14 \
2009-11-15 2009-11-16 2009-11-17 2009-11-18 2009-11-19 2009-11-20 2009-11-21 \
2009-11-22 2009-11-23 2009-11-24 2009-11-25 2009-11-26 2009-11-27 2009-11-28 \
2009-11-29 2009-11-30


Планировщик управления услугами пользователей

MBGROOT=/var/www/billing2 /usr/bin/billing_planner_daily \
`sqlite3 :memory: "select date('now')"`

Указанная команда выполнит переключение услуг пользователей согласно расписанию на текущий день.
Аналогично может быть выполнено переключение за любой день.

Запуск коллекторов
Пример конфигурации /etc/inittab для телефонного биллинга на АТС m200 и cisco

sc1:2:respawn:/usr/bin/scomm 172.17.7.2 10000 10001 -outdir /srv/log/ats1
sc2:2:respawn:/usr/bin/scomm 213.148.6.162 10000 10002 -outdir /srv/log/ats2
...

sp1:2:respawn:/usr/bin/spider-start -scommport 10001 -serverport 10101 -outdir /srv/log/ats1
sp2:2:respawn:/usr/bin/spider-start -scommport 10002 -serverport 10102 -outdir /srv/log/ats2
...

cb1:2:respawn:/usr/bin/callbuilder-start -spiderport 10101 -outdir /srv/log/ats1 -serverport 10210
cb2:2:respawn:/usr/bin/callbuilder-start -spiderport 10102 -outdir /srv/log/ats2 -serverport 10220
...

ag1:2:respawn:/bin/su mobi -c "/usr/bin/telephony_agent_tacacs-demon \
> /srv/log/telephony_agent_tacacs.log"
ag2:2:respawn:/bin/su mobi -c "/usr/bin/telephony_agent_callbuilder-demon \
> /srv/log/telephony_agent_callbuilder.log"


Здесь используются вспомогательные скрипты, описанные ниже.
callbuilder-start

#!/bin/sh

/usr/bin/callbuilder -spiderip 127.0.0.1 -rotation 3 -str2boff -cdrformat 10 $@


spider-start

#!/bin/sh

/usr/bin/spider -scommip 127.0.0.1 -rotation 4 $@


Пакетный импорт логов демона tacacs+ (с АТС cisco)
Скрипт содержит в себе пути к лог-файлам, т.к. предназначен для работы в виде обертки к соответствующему коллектору. Логи могут быть в текстовом виде и сжатые gzip.

telephony_import_tacacs

#!/usr/bin/tclsh8.5
# парсит набор логов такакса, распаковывая их при необходимости

set src /root/tacacs/
set dst /srv/dataset/telephony

foreach fname [glob $src/*] {
if {[file extension $fname] eq {.gz}} {
puts "importing gzipped: $fname"
set cat zcat
} else {
puts "importing plain: $fname"
set cat cat
}
exec $cat $fname | /usr/bin/telephony_agent_tacacs -dataset $dst
}


Пакетный импорт логов демона callbuilder (с АТС m200)
Скрипт содержит в себе пути к лог-файлам, т.к. предназначен для работы в виде обертки к соответствующему коллектору. Логи могут быть в текстовом виде и сжатые gzip. Для загрузки лишь части лог-файлов предусмотрено задание масок года и месяца аргументами командной строки.

telephony_import_callbuilder

#!/usr/bin/tclsh8.5
# парсит набор логов m200, распаковывая их при необходимости
# первый аргумент - шаблон номера месяца, второй аргумент - шаблон номера года

set src /srv/log
set dst /srv/dataset/telephony

# можно указать набор аргументов, из которых будет составлен шаблон имени файла
foreach fname [glob $src/*/cdr_log_mon*[lindex $argv 0]*_*[lindex $argv 1]*.log] {
set ats_name [lindex [file split $fname] 3]
if {[file extension $fname] eq {.gz}} {
puts "importing gzipped log $ats_name: $fname"
set cat zcat
} else {
puts "importing plain log $ats_name: $fname"
set cat cat
}

exec $cat $fname | /usr/bin/telephony_agent_callbuilder -dataset $dst /
-ports "stdin $ats_name"
}


Трансляция лога демона tacacs+
При организации распределенной системы демон tacacs+ может находиться на хосте, отличном от того, на котором запущен соответсвующий коллектор. В этом случае мы рекомендуем следующее решение, которое корректно работает с ротируемыми файлами и позволяет подключение одного и более коллекторов:


sudo socat EXEC:"/usr/bin/tail -F -n 0 /var/log/tacacs" \
TCP4-LISTEN:9999,bind=localhost,su=nobody,fork,range=127.0.0.1/32,reuseaddr


Проверить работу транслятора можно так:

telnet localhost 9999


В приведенной команде из соображений безопасности разрешено подключение только с localhost, а далее порт может быть проброшен средствами ssh, что защитит передаваемые между распределенными офисами данные. Также можно настроить подключение из нужной подсети или с нужного хоста, если все происходит в одной доверенной сети (в т.ч., организованной с помощью VPN-подключений).

Ссылки
Структура базы телефонного биллинга
Телефонный биллинг: тариф "Направление посекундно"
Использование adp-wrapper в биллинге

понедельник, 23 ноября 2009 г.

Degradation of indexing speed in SQLite 3.6.20

Hardware

laptop with Intel(R) Core(TM)2 Duo CPU T5470 1.60GHz (working as 800MHz CPU for low heating)
1 GB RAM
HDD Western Digital Scorpio Black 2.5" 7200 rpm 16Mb (FS ext3)
OS Debian (lenny+testing+sid).

SQLite build
My debian package has these options to compile SQLite 3.6.20:

DEB_OPT_FLAG := -O2 -fno-strict-aliasing \
-DSQLITE_CORE=1 \
-DSQLITE_ENABLE_COLUMN_METADATA \
-DSQLITE_ENABLE_STAT2 \
-DSQLITE_DEFAULT_RECURSIVE_TRIGGERS=1 \
-DSQLITE_ENABLE_COMPRESS \
-DSQLITE_ENABLE_FTS3 \
-DSQLITE_ENABLE_INET \
-DSQLITE_ENABLE_MD5 \
-DSQLITE_ENABLE_RTREE \
-DSQLITE_ENABLE_TABLEFUNC \
-DSQLITE_ENABLE_VIRTUALTEXT \
-DSQLITE_ENABLE_ICU \
-DSQLITE_ENABLE_UUID \
-DSQLITE_ENABLE_VERSIONING \
-DSQLITE_SOUNDEX \
-DSQLITE_TEMP_STORE=3 \
-DSQLITE_DEFAULT_PAGE_SIZE=4096 \
-DSQLITE_DEFAULT_CACHE_SIZE=128000 \
-DSQLITE_DEFAULT_TEMP_CACHE_SIZE=8000 \
-DSQLITE_DEFAULT_FILE_FORMAT=4

LDFLAGS=-luuid -lz -ldl `icu-config --ldflags`


You can get binary and sources packages from http://mobigroup.ru/debian/pool/main/s/sqlite3/ (current build is 3.6.20-mobigroup.1).

Test results

┌────────────────────┬────┬───┬─────┬──────┬──────┬───────────────────────────┐
│Groups count (N) │1 │10 │100 │1000 │10000 │ │
├────────────────────┴────┴───┴─────┴──────┴──────┴───────────────────────────┤
│DB population │
├────────────────────┬────┬───┬─────┬──────┬──────┬───────────────────────────┤
│Creation time │2s │20s│205s │2225s │21329s│./test.tcl groups N │
│DB size │2,6M│26M│255M │2,6G │26G │items 25000 │
├────────────────────┴────┴───┴─────┴──────┴──────┴───────────────────────────┤
│Indexing after DB population │
├────────────────────┬────┬───┬─────┬──────┬──────┬───────────────────────────┤
│Single-column │ │ │ │ │ │ │
│indexing time │<1s │5s │49s │574s │ │CREATE INDEX single_idx on │
│DB size │2,9M│29M│290M │2,9G │- │test(destcode) │
├────────────────────┼────┼───┼─────┼──────┼──────┼───────────────────────────┤
│Analyze time │<1s │<1s│28s │588s │- │ANALYZE │
├────────────────────┴────┴───┴─────┴──────┴──────┴───────────────────────────┤
│Indexing after DB population │
├────────────────────┬────┬───┬─────┬──────┬──────┬───────────────────────────┤
│Multi-column │ │ │ │ │ │CREATE INDEX complex_idx │
│indexing time │1s │6s │80s │>5000s│ │on test │
│DB size │4,0M│40M│397M │ - │- │(destcode,name,delete_date)│
├────────────────────┼────┼───┼─────┼──────┼──────┼───────────────────────────┤
│Analyze time │<1s │4s │211s │- │- │ANALYZE │
├────────────────────┴────┴───┴─────┴──────┴──────┴───────────────────────────┤
│Single-column indexing with DB population │
├────────────────────┬────┬───┬─────┬──────┬──────┬───────────────────────────┤
│ │ │ │ │ │ │./test.tcl groups N │
│Creation time/DB │3s │29s│658s │>5000s│ │items 25000 index single │
│size │2,9M│29M│290M │ - │- │ │
├────────────────────┴────┴───┴─────┴──────┴──────┴───────────────────────────┤
│Multi-column indexing with DB population │
├────────────────────┬────┬───┬─────┬──────┬──────┬───────────────────────────┤
│ │ │ │ │ │ │./test.tcl groups N │
│Creation time/DB │3s │36s│1875s│ │ │items 25000 index complex │
│size │4,0M│40M│ 397M│- │- │ │
└────────────────────┴────┴───┴─────┴──────┴──────┴───────────────────────────┘


test.tcl

#!/usr/bin/tclsh8.5
# Use as ./test.tcl groups X items Y [index single|complex]
package require sqlite3

array set opts $argv

puts "Test of $opts(groups) with $opts(items) items in each group"

catch {file delete test$opts(groups).db}
catch {file delete test$opts(groups).db-journal}
sqlite3 db test$opts(groups).db
db eval {PRAGMA cache_size=16000}
#db eval {PRAGMA synchronous=OFF}

db eval {
CREATE TABLE test ( -- справочник направлений для телефонии
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
save_date REAL NOT NULL DEFAULT (julianday('now')),
delete_date REAL,
name text collate russian not null, -- группа направлений
destname text collate russian not null, -- название направления
destcode integer not null, -- префикс E.164 номера телефона
rcode text collate NOCASE not null, -- код региона
price real not null, -- стоимость минуты разговора
currency text collate NOCASE not null default 'RUB'
)
}
if {[info exists opts(index)] && $opts(index) eq {single}} {
db eval {CREATE INDEX single_idx on test(destcode)}
}
if {[info exists opts(index)] && $opts(index) eq {complex}} {
db eval {CREATE INDEX complex_idx on test(destcode,name,delete_date)}
}

for {set j 0} {$j<[expr $opts(groups)]} {incr j} {
set name "Группа направлений $j"
db transaction {
for {set i 0} {$i<$opts(items)} {incr i} {
set destname "Направление $i"
set destcode [string range [expr round(1000000000000000*rand())] 0 [expr round(15*rand())]]
set price [expr round(10000*rand())/100.]
# puts "insert into test (name,destname,destcode,rcode,price) values ($name,$destname,$destcode,'',$price)"
db eval {insert into test (name,destname,destcode,rcode,price) values ($name,$destname,$destcode,'',$price)}
}
}
}
db close


Upd.

I did perform additional test for speed of select query. Is used the real query from telephony billing system.

Select Time for
select rowid from test where name='Группа направлений 0' and destcode=500 and delete_date IS NULL

┌────────────────────┬────┬───┬─────┬──────┬──────┐
│Groups count (N) │1 │10 │100 │1000 │10000 │
├────────────────────┼────┼───┼─────┼──────┼──────┤
│without index │0.1s│1s │8s │78s │796s │
├────────────────────┼────┼───┼─────┼──────┼──────┤
│single index │0s │0s │0.08s│0.4s │- │
└────────────────────┴────┴───┴─────┴──────┴──────┘

Note: the query returns 1...3 rows. Time is measured by SQLite timer as user+sys times.

The test results
There is no speed degradation of no-indexed database.
As temporary fix of indexing problem we can use single-column realtime index and create complex index after full population of database (I split database monthly).

Upd.
I did perform the tests on server hardware (with 8 Gb RAM). As we can see the index creating speed degradation is result of index size more than SQLite page cache size.


$ time ./test.tcl groups 1000 items 25000; \
time sqlite3 test1000.db "pragma cache_size=500000;CREATE INDEX single_idx on test(destcode);"
Test of 1000 with 25000 items in each group

real 10m7.098s
user 9m36.932s
sys 0m17.545s

real 3m5.097s
user 2m55.639s
sys 0m4.392s

$ time ./test.tcl groups 2000 items 25000; \
time sqlite3 test2000.db "pragma cache_size=500000;CREATE INDEX single_idx on test(destcode);";
Test of 2000 with 25000 items in each group

real 21m7.168s
user 19m13.756s
sys 0m33.994s

real 6m42.785s
user 6m12.483s
sys 0m17.749s

$ > time ./test.tcl groups 4000 items 25000; \
time sqlite3 test4000.db "pragma cache_size=500000;CREATE INDEX single_idx on test(destcode);";
Test of 4000 with 25000 items in each group

real 40m36.081s
user 38m28.492s
sys 1m11.944s

real 13m42.090s
user 12m48.424s
sys 0m28.150s

$ time ./test.tcl groups 8000 items 25000; \
time sqlite3 test8000.db "pragma cache_size=500000;CREATE INDEX single_idx on test(destcode);"
Test of 8000 with 25000 items in each group

real 80m36.335s
user 76m46.752s
sys 2m9.436s
^CError: interrupted

real 214m12.327s
user 23m39.909s
sys 1m18.873s


The last index size must be more than 2Gb and can't be effectively created with cache_size equal to 2Gb. This command interrupted manually by Ctrl+C keys.

среда, 18 ноября 2009 г.

Разорительная экономика и экономическое разорение

Интересно мне стало проанализировать итоги первой волны "кризиса". Почему я считаю, что была лишь первая волна, - вопрос отдельный. Впрочем, на мой взгляд, последующие события станут перемещаться из разряда экономических потрясений в социальные. Отмечу, что "кризис" не случайно в кавычках - дело в том, что смена экономической формации это явление ожидаемое и в какой-то степени неотвратимое, разве что его можно замедлить, что сейчас и проделывается с большим или меньшим успехом.

Приводимые в СМИ прогнозы не вызывают не то что доверия, но даже какого-либо интереса. Дело в том, что современная экономика имеет в качестве инструментов некоторый набор моделей, которые и могут быть исследованы, но не имеет средств для построения адекватной экономической и социальной модели для наблюдаемой ситуации. Потому единственный результативный анализ заключается в том, что мы моделируем развитие ситуации, основываясь на некоторых методиках, а потом выбираем из них наиболее соответствующую реальности. Соответственно, можно рассчитывать на то, что полученный таким способом трэнд будет характеризовать развитие ситуации.

Ситуация 1.
Резкое падение курса рубля вследствии падение цен на нефть и другие ресурсы. Экономические последствия негативные в краткосрочной перспективе и благоприятны в долгосрочной. Первое есть очевидный результат гипертрофированной роли государства и его неэффективности, а также доминированию сырьевых монополий в экономике. Последнее в силу того, что резкое подорожание импорта стимулирует импортозамещение, что, в свою очередь, привлекает инвестиции в производство и сферу услуг. Уровень жизни населения падает, но быстро начинает компенсироваться ускорением денежного и товарного оборота - в ходу в основном дешевые товары, и те начинают производиться "на месте", что стимулирует процессы стабилизации экономики. Неэффективные монстры, например, предприятия машиностроения, могут пережить кризис благодаря поддержке государства. Серьезную проблему представляет утяжеление бремени валютных кредитов, но рефинансирование задолженности и продажа активов доступны крупному и, частично, среднему бизнесу. Конкуренция в основном происходит в области логистики, поскольку наиболее выгодны краткосрочные вложения и чем быстрее произведен и реализован товар, тем выше прибыль.

Таким образом, мелкие предприятия ускоряют товарооборот, чтобы обойтись без кредитов, крупный бизнес рефинансируется и прибегает к помощи государства. Под ударом оказывается средний бизнес, что приведет к еще большему усиления роли государства и монополий после кризиса.

Ситуация 2.
Искусственное удержание курса рубля, несмотря на обрушившиеся цены на энергоресурсы. Это возможно за счет того, что будет снижаться экономическая активность - отсутствие внутренних инвестиций, замедление товарооборота, недоступность кредитов; все это, вкупе с валютными играми Центробанка, позволит удерживать курс рубля или, по крайней мере, плавно его снижать, ожидая восстановления цен на энергоносители на внешних рынках. Ситуация очень искусственная и приводит к тому, что выживают в основном те предприятия, которые обеспечены поддержкой государства, все остальные оказываются в тяжелых условиях. Практически это идеальная среда для очередного "передела" собственности. Конкуренции нет, поскольку это совершенно не рыночная экономика. Кризиса как такового еще нет, что подтверждается покупками активов компаниями-монополистами, особенно активов за рубежом. Царит психологическая истерия, что приводит, в частности, к массовым увольнениям. Сочетание таких факторов, как сокращение числа сотрудников компаний и доходов оставшихся вкупе с покупкой активов ясно показывают, что именно проделывает государство со своим народом под личиной кризиса в пользу корпораций.

Итогом становится то, что кризис откладывается - экономика находится в "анабиозе", ожидании восстановления внешних рынков, в это время аккумулируются все существующие экономические и социальные проблемы. Восстановление мировой экономики, хотя бы частичное, вызывает оживление экономического полутрупа. Впрочем, этот экономический зомби продолжит разлагаться в средне и долгосрочной перспективе. Развитие ситуации можно охарактеризовать как разорительная экономика, поскольку сформированы все предпосылки для дальнейшей разрухи.

Ситуация 3.
Этот сценарий представляет собой предыдущий вариант, усугубленный периодическими провалами курса национальной валюты. Возникновение такой ситуации вероятно при непонимании правительством происходящего и возникновении излишнего оптимизма, когда начало восстановления мировых рынков полагается достаточным условием выхода страны из кризиса за счет повышения мировых цен на сырье. В этом случае под ударом окажутся и предприятия с государственной поддерхкой, поскольку вместо своевременной необходимой реорганизации поддержка просто будет прекращена, а при очередном появлении признаков ситуации 2 - возобновлена, опять же без какого-либо видения пути выхода предприятий из патового положения.

Представляется справедливым охарактеризовать описанное выше положение дел как экономическое разорение.

Эпилог.

Мы рассмотрели три возможных пути развития событий. Как мы можем видеть, текущее положение дел может быть описано в рамках ситуации 2 или 3, в зависимости от дальнейших событий.

Из рассмотрения специально исключены вопросы изношенной инфраструктуры, абсолютной коррупции и уничтоженных образования и медицины, поскольку, если здраво оценивать ситуацию, эти факторы многократно "перевешивают" все прочие - ну какой смысл рассматривать снижение жизненного уровня населения, когда в результате отсутствия квалифицированной медпомощи половина страны может в считанные недели сгинуть в результате эпидемии?..

Правило udev для VirtualBox в Debian

Ранее приходилось делать финт ушами, дописывая в файл /etc/fstab следующую строку:

usbfs /proc/bus/usb usbfs devgid=118,devmode=664 0 0


Теперь же это не нужно, так как появилось правило 10-vboxdrv.rules для udev:

KERNEL=="vboxdrv", NAME="vboxdrv", OWNER="root", GROUP="root", MODE="0600"
SUBSYSTEM=="usb_device", GROUP="vboxusers", MODE="0664"
SUBSYSTEM=="usb", ENV{DEVTYPE}=="usb_device", GROUP="vboxusers", MODE="0664"


У меня этот файл датирован 3-м ноября сего года.

Конечно, пользователь должен входить в группу vboxusers:
$ cat /etc/group|grep vboxusers
vboxusers:x:118:veter
Добавить пользователей в требуемую группу нетрудно (необходимо перелогиниться для вступления изменений в силу):
sudo addgroup veter vboxusers

Извлечение метаинформации о файлах

Получение метаинформации о файлах необходимо, например, для систем полнотекстового поиска. Для этих целей применяются более или менее навороченные скрипты, поставляемые в комплекте. Для меня подобный подход неприемлем, что и побудило меня поискать unix-way для решения поставленной задачи. И он нашелся - это утилита extract из одноименного пакета.

Пример использования:

$ extract MTS\ Connect.ppt
mimetype - application/vnd.ms-powerpoint
paragraph count - 215
title - PowerPoint Presentation
word count - 741
date - 2008-12-29T09:30:08Z
generator - Microsoft PowerPoint
last saved by - Иван Иваныч
creation date - 2009-04-22T19:24:48Z


Что и требовалось получить.

пятница, 13 ноября 2009 г.

Создание и продвижение своего продукта

Пару лет назад случайно пообщался с одним из посетителей форума sql.ru. Он задавал интересные вопросы и мне диалог с ним показался заслуживающим того, чтобы его сохранить. Собственно, от того человека привожу лишь две фразы, а остальное мой текст, но зато в его словах прозвучали самые известные вопросы, которые, по сути, могут быть озвучены как "с чего начать" и "как начать".Где возможно, цитирую слова собеседника, если же мой ответ касается сразу многих высказываний, просто ставлю многоточие. Во многих случая стоило бы добавить "имхо", но я прошу читателей помнить, что это из переписки на форуме, где само собой подразумевается, что собеседники делятся своими мыслями неформально.

Интелектуальная собственность НЕ МОЖЕТ принадлежать никому (юр. физ. лицу) кроме его автора.


Есть авторское и имущественное право. Первое всегда у автора, второе принадлежит или автору или работодателю. Имея имущественные права, можно спокойно продавать программу, единственное обязательство - где-нибудь указать имя автора. При этом автор не имеет ничего, кроме своего права...

Сделанный под заказ продукт требует в среднем около года на доработку до коммерческой версии, с которой можно начинать предлагать его покупателю и в приемлемые сроки установить и запустить в работу.

Продвижением заказной разработки способен заниматься лишь сам автор, потому что он знает предметную область, где стоит использовать его продукт. Человек со стороны или вообще не найдет покупателей, или того хуже - в процессе или после продажи окажется, что покупатель хотел купить другой продукт и нуждается в решении иных задач. Потерю времени и репутации будет очень сложно компенсировать, если это вообще удастся.

В первую очередь нужна документация на продукт, написанная грамотным техническим специалистом (но только не автором!), которая поможет понять, что из себя на данном этапе представляет продукт. Автор часто видит не то, что есть, а то, что хочется видеть. Эту документацию стоит показать грамотному менеджеру, который оценит востребованность и подскажет, как стоит позиционировать продукт. После этого, зная, что вы хотите продавать и что вы реально имеете на текущий момент, можно составлять план работы по подготовке версии на продажу. А вот теперь вы готовы к поиску в интернете конкурентов, анализу рынка и составлению бизнес-плана.

В общих чертах этого достаточно, чтобы за год-два выйти на рынок. К этому времени вопрос о праве собственности решится сам собой, потому что вы не раз полностью перепишите вашу программу и она будет вашей и только вашей. И не волнуйтесь - это окупится.

я не профессиональный программист и изначально среда разработки не C# или С++.


Поверьте, серьезный продукт только на С++ не сделать - слишком сложная техподдержка получается. Есть два выхода - встраивать в продукт на С++ свой интерпретатор или сразу использовать скриптовый язык (к которому можно писать свои модули на тех же плюсах). Если два года назад эти варианты были сравнимы, то сегодня однозначно нужно отдать предпочтение второму - заказчик ждет изменений не через неделю, а завтра или прямо сейчас. Раз ваш бизнес маленький -он должен быть шустрым, это обеспечит успех.

Суть имущественного права - это возможность распоряжаться программным продуктом как своим имуществом - продавать, сдавать в аренду, дарить... Надежный способ подтвердить свое имущественное право - заключить договор на продажу продукта. Если этот договор оформлен юридически правильно, он будет достаточным свидетельством для любого юриста. Такая вот штука получается - чтобы подтвердить свое право продавать продукт, нужно его продать. А еще лучше заключить авторский договор с фирмой, которая будет продавать. Это тоже непрошибаемый вариант. Плюс второго варианта в том, что никакие претензии государства к фирме-продавцу не повредят продукту (иначе могут "временно" запретить продажу, если вдруг с вами кто-то судиться начнет по вопросу правообладания, а это способно затянуться на неопределенно долгое время). А так - достаточно заключить договор с другой фирмой и работать дальше, пока идут разборки (подчеркиваю - права на продукт у вас вряд ли смогут отсудить, а вот убить ваш бизнес - запросто). Только не открывайте ИП, по крайней мере, на себя лично.

P.S. Искренний совет - найдите грамотного архитектора ПО, если только вы не гуру в этой области, пусть он проведет экспертизу вашего продукта. Не писал об этом раньше, поскольку мне самому этот шаг не требовался, вот и позабыл упомянуть.

P.P.S. Не стесняйтесь привлекать профессионалов в нужной области - их помощь может оказаться неоценимой.

...


Презентация во флэш, презентация в PowerPoint, документация есть? Выкладывайте в интернет, указывайте свой email. Поищите сайты потенциальных заказчиков, отправьте им анонс - "готовится к выходу новый продукт, если вы сегодня напишите свои пожелания, завтра получите скидку в 50%". Поскольку цену в анонсе указывать не нужно, вы ничем не рискуете, а внимание привлечете. К тому моменту, когда вы запустите проект в тираж, уже будет некоторое сообщество потенциальных пользователей. Если же начнете знакомить людей с продуктом только после его выхода, то потеряете время, пока люди ознакомятся с продуктом, сформулируют вопросы, зададут их вам и получат ответы, пока вы внесете нужные доработки... По собственному опыту скажу - ко всем моим проектам заказчики имеют доступ в любое время, то есть мы пишем код, а пользователи уже имеют возможность все "потрогать". Бывает так, что мы начинаем делать какую-то новую функцию, а нам уже идут звонки с предложениями, как сделать лучше. Если же убираем ненужную на наш взгляд вещь, но она нужна пользователям, об этом так же незамедлительно нам сообщат. Если вы сумеете заинтересовать возможных пользователей и они будут наблюдать непосредственно за процессом разработки, успех вам обеспечен. Создавайте сообщество вокруг вашего проекта, тогда он будет динамично развиваться и улучшаться с каждой новой версией.

...


Есть некоторые простые правила, которые следует соблюдать для создания успешного проекта. Я изложу их своими словами, так, как организую свои проекты. Если кому-то поможет - отлично, а кто захочет познакомиться с мнением авторитетных специалистов, сможет это сделать, прочитав соответствующую литературу и сам опробовав на практике найденные рекомендации.

1. Интернет-проекты делаются не более одного-двух кварталов. За больший срок изменится многое - и способ доступа в интернет (и его скорость), и интересы пользователей, и операционные системы, и браузеры, и СУБД (как минимум, их версии)... Так что через полгода после старта проекта он или станет успешным, или провалится, или идею скопируют сильные конкуренты (если подкачает техническое воплощение хорошей идеи). Далее проект может долго развиваться, но "вынашивается и рождается" он быстрее, чем слоненок.

2. У потенциальных участников проекта наибольший интерес вызовет личность лидера, от которого будет в первую очередь зависеть успех проекта.

3. Успешной идея становится только в том случае, если здесь и сейчас ее можно воплотить, что требует детальной проработки как технической ее части, так и организационной и и коммерческой сторон.

4. Нужно искать специалиста, который умеет делать Y с помощью технологии X. Неправильный подход - искать специалиста по технологии X, потому что сделать нужно Y, а X не более, чем средство.

5. Инвесторы и партнеры ищутся с уже готовым бизнес-планом на руках. В первую очередь вы дожны четко видеть цель. Если вы знаете, что хотите добиться, вам будет несложно рассказать об этом партнерам. И они уже будут решать, оценивая вас лично и ваш проект. Партнерство - улица с двусторонним движением.

6. Отсутствие денег само по себе ни о чем не говорит. Неумение их заработать - говорит о том, что рано браться за создание своего бизнеса. Не каждый стартап становится бизнесом, не каждый бизнес становится успешным. В создании успешного бизнеса вам не поможет ни технология X, ни человек, ею владеющий.

7. Совместная работа команды нужна для достижения определенных целей. Если вы не можете обеспечить финансовую заинтересованность участников, предложите им другие цели - возможность получить опыт, обзавестись нужными им связями и проч. "И чтоб никто не ушел обиженным" (с). Даже в том случае, если проект не удастся, каждый участник должен чувствовать, что он уже получил что-то, стоящее затраченных усилий.

8. Собрать команду - очень сложная задача. Особенно из знакомых и друзей. Если вы это делаете первый раз, уместнее будет вопрос "как создать команду?", а не "где найти такого-то специалиста?".

9. Ищите людей, а не специальности. Хорошая команда сможет выполнить проект, но вовсе не факт, что из хороших специалистов получится хоть какая-то команда. Сначала вы создаете команду, потом ставите задачу и организуете людей в зависимости от их умений. Привлекать узкого специалиста можно в помощь команде и только в том случае, если это необходимо команде - не пытайтесь решать априори.

10. Будьте готовы отдать все свое время на достижение поставленной цели. Ваши партнеры могут работать удаленно и когда им удобно, но вы должны работать постоянно. Привыкайте заранее к тому, что ваш проект - это работа, и притом работа на полную ставку, а часто и на полторы-две ставки.

А вот далее это явно мое заключение, которое я оставил "при себе".

Не буду комментировать написанное топикстартером, поскольку он "пролетает" по всем пунктам (по крайней мере, создается именно такое впечатление). Если человек в самом деле заинтересован в создании бизнеса, он сумеет разобраться и напишет грамотное предложение к сотрудничеству.

Построение отчетов - Использование защищенного интерпретатора в pltcl

В связи с усложнением логики построения отчетов появилась потребность в предоставлении пользователю возможности самому писать отчеты на скриптовом языке. Задача не новая, однако существующие ее решения зачастую мало приемлемы. Для сохранения высокого быстродействия при построении отчетов необходимо создавать их непосредственно в БД, таким образом, требуется возможность выполнения пользовательского кода в контексте функции в БД. Для защиты БД применим технологию защищенных интерпретаторов tcl. Пример безопасного выполнения пользовательского кода приведен ниже.

CREATE OR REPLACE FUNCTION public.interp_test(script text)
RETURNS void AS
$BODY$

set script $1

interp create -safe slave
interp alias slave elog {} elog
if {[catch {
interp eval slave $script
} errmsg]} {
interp delete slave
elog ERROR $errmsg
}
interp delete slave

return

$BODY$
LANGUAGE 'pltcl' VOLATILE SECURITY DEFINER;
ALTER FUNCTION public.interp_test(text) OWNER TO offline;


select public.interp_test('elog NOTICE "Safe interp works fine!"');



В интерпретаторе определяем набор функций и необходимые переменные. Никакие иные действия, кроме вызова указанных функций и операций над переменными, не могут быть выполнены. В случае возникновения ошибки будет выдано соответствующее сообщение и исполнение функции корректно завершится. При использовании pltcl код исполняется в защищенном интерпретаторе, но мы явно указываем, что создаваемый интерпретатор является защищенным, так что эта функция будет корректно работать и в untrusted tcl.

Таким образом, мы предоставили пользователю возможность определять произвольный алгоритм построения параметров отчетов, выполняемый с высоким быстродействием непосредственно в БД. Создавая набор функций и набор обрабатываемых данных, можно обеспечить простой и интуитивный язык анализа данных в рамках решаемой задачи.

А вот рабочее решение:


CREATE OR REPLACE FUNCTION reports.interp_checks_eval(script text, questions_list text, args text)
RETURNS text AS
$BODY$

array set scripts $1
set questions_list [lsort -dictionary -unique $2]
array set args $3

if { ![info exists args(cumulative)] } {
set args(cumulative) 0
}

spi_exec -array V "SELECT relname, pg_class.relkind as relkind FROM pg_class, pg_namespace
WHERE pg_class.relnamespace=pg_namespace.oid
AND pg_class.relkind IN ('r')
AND pg_namespace.nspname || '.' || relname='$args(table_out)'" {
if [info exists V(relname)] {
spi_exec "DROP TABLE $args(table_out) CASCADE"
}
}

set sql "create table $args(table_out) ("
foreach column $args(columns_key_list) {
spi_exec "SELECT data_type
FROM information_schema.columns
WHERE table_schema || '.' || table_name = '$args(table_in)' AND column_name = '$column'" {
append sql "$column ${data_type},"
}
}
append sql [join [array names scripts] " text, "] " text) WITHOUT OIDS"
spi_exec $sql

set func_names_list {}
foreach func [array names scripts] {
lappend func_names_list $func
set count [\
spi_exec "select code from reports.scripts where name='[quote $scripts($func)]'" {
set scripts($func) $code
}]
if { (![info exists args(allow_code)] || $args(allow_code) eq 0) && $count eq 0 } {
#elog ERROR "Не найден скрипт с названием '[quote $scripts($func)]'"
}
}

catch {interp delete slave}
interp create -safe slave
interp alias slave elog {} elog
interp alias slave quote {} quote

interp eval slave {
proc Result {question_id {answer_id {}} } {
upvar #0 answers_array answers_array
if { $answer_id eq {} } {
set answer_id $question_id
}
if { [info exists answers_array($answer_id)] } {
return $answers_array($answer_id)
} else {
return {}
}
}
}

set key_names_list {}
foreach key $args(columns_key_list) {
lappend key_names_list $key
}

# передать список вопросов указанный в параметрах вызова функции
interp eval slave [list set questions_list $questions_list]

if {[catch {
set count [\
spi_exec "select [join $args(columns_key_list) ,], answers_array,questions_list from $args(table_in)" {
interp eval slave [list array unset answers_array]
interp eval slave [list array set answers_array $answers_array]

set func_values_list {}
foreach func $func_names_list {
set result [interp eval slave [list eval $scripts($func)]]
lappend func_values_list "'[quote $result]'"
}

if { $args(cumulative) ne {1}} {
set key_values_list {}
foreach key $args(columns_key_list) {
upvar 0 $key value
lappend key_values_list "'[quote $value]'"
}

spi_exec "insert into $args(table_out) ([join $key_names_list ,], [join $func_names_list ,]) values ([join $key_values_list ,], [join $func_values_list ,])"
}
}]
} errmsg]} {
interp delete slave
elog ERROR $errmsg
}
interp delete slave

if { $args(cumulative) eq {1}} {
set key_values_list {}
foreach key $args(columns_key_list) {
lappend key_values_list "NULL"
}
spi_exec "insert into $args(table_out) ([join $key_names_list ,], [join $func_names_list ,]) values ([join $key_values_list ,], [join $func_values_list ,])"
}

if { $args(cumulative) ne {1}} {
foreach column $args(columns_key_list) {
spi_exec "CREATE INDEX [string map {. _} $args(table_out)]_${column}_idx ON $args(table_out) USING btree ($column)"
}
}

return $count

$BODY$
LANGUAGE 'pltcl' VOLATILE SECURITY DEFINER;


Приведенный ниже скрипт работает в одной из наших систем:


select reports.interp_checks_eval('data {
if { [Result 101 201] eq "1" } {
return "Работает"
}
if { [Result 101 202] eq "1" } {
return "Не работает"
}
if { [Result 101 203] eq "1" } {
return "Временно не работает"
}
if { [Result 101 204] eq "1" } {
return "Отказано"
}
return "Неизвестно"
}','','table_in cache.checks_yy07mm10 table_out reports.test_out column_data answers_array columns_key_list {check_date object_id user_id}


Разумеется, в таком виде скрипт изменять не очень удобно, однако после написания соответствующего транслятора пользователь может работать через веб-интерфейс (и не только) с его текстом в виде:


if { [Result {Статус точки} Работает] eq "1" } {
return "Работает"
}
if { [Result {Статус точки} {Не работает}] eq "1" } {
return "Не работает"
}
if { [Result {Статус точки} {Временно не работает}] eq "1" } {
return "Временно не работает"
}
if { [Result {Статус точки} Отказано] eq "1" } {
return "Отказано"
}
return "Неизвестно"


Логика анализа может быть и намного более сложной, а также можно указывать набор скриптов, которые будут выполнены над исходным набором данных. Заметим, что состояние интерпретатора сохраняется, что позволяет делать анализ с накоплением результата. Определив набор нужных функций и переменных, мы обеспечиваем возможность для анализатора получить любую хранимую в БД информацию и также применить ее в процессе анализа.

P.S. Приведенный код упрощен для иллюстрации идеи. Для промышленного использования необходимо обеспечить интерфейс доступа (на чтение) ко всей нужной информации, хранимой в БД. Но это совсем другая история.

P.P.S. Заметим, что на практике первой встает задача преобразования исходных данных в необходимый вид (тиклевский список, массив или более сложные структуры). Как показывает практика, в некоторых случаях можно ограничиться достаточно простым представлением (различные системы анкетирования, мерчендайзинга, etc.). Для систем документооборота и подобных получается довольно сложное представление данных, но указанный метод эффективен и для них.

(C) Alexey Pechnikov aka MBG, mobigroup.ru