1
##############################################################################
3
# Copyright (c) 2004-2006 TINY SPRL. (http://tiny.be) All Rights Reserved.
5
# WARNING: This program as such is intended to be used by professional
6
# programmers who take the whole responsability of assessing all potential
7
# consequences resulting from its eventual inadequacies and bugs
8
# End users who are looking for a ready-to-use solution with commercial
9
# garantees and support are strongly adviced to contract a Free Software
12
# This program is Free Software; you can redistribute it and/or
13
# modify it under the terms of the GNU General Public License
14
# as published by the Free Software Foundation; either version 2
15
# of the License, or (at your option) any later version.
17
# This program is distributed in the hope that it will be useful,
18
# but WITHOUT ANY WARRANTY; without even the implied warranty of
19
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
20
# GNU General Public License for more details.
22
# You should have received a copy of the GNU General Public License
23
# along with this program; if not, write to the Free Software
24
# Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
26
##############################################################################
30
from pyparsing import *
35
from osv import fields,osv
38
from cube import levels
41
class olap_fact_database(osv.osv):
42
_name = "olap.fact.database"
43
_description = "Olap Fact Database"
47
def _connection_get(self,cr,uid,ids,field_name,arg,context = {}):
49
Return a connection string url needed by SQL Alchemy. Exemple:
50
'postgres://scott:tiger@localhost:5432/mydatabase'
53
for obj in self.browse(cr,uid,ids,context):
54
res[obj.id] = '%s://%s:%s@%s:%d/%s' % (obj.type,obj.db_login,obj.db_password,obj.db_host,obj.db_port,obj.db_name)
57
def test_connection(self,cr,uid,ids,context = {}):
59
self_obj = self.browse(cr,uid,ids,context)
65
password = obj.db_password
67
if type == 'postgres':
68
tdb = psycopg2.connect('host=%s port=%s dbname=%s user=%s password=%s' % (host,port,db_name,user,password))
72
tdb = MySQLdb.connect(host = host,port = port,db = db,user = user,passwd = passwd)
75
raise osv.except_osv('Error (MySQLdb) : ',e)
76
elif type == 'oracle':
79
tdb = cx_Oracle.connect(user,password,host)
82
raise osv.except_osv('Error (cx_Oracle) : ',e)
85
raise osv.except_osv('BI Error !',e)
90
'name': fields.char('Fact name',size = 64,required = True),
91
'db_name': fields.char('Database name',size = 64,required = True , help = "Name of the database to be used for analysis."),
92
'db_login': fields.char('Database login',size = 64,required = True, help = "Login for the database name specified."),
93
'db_password': fields.char('Database password',size = 64,invisible = True,required = True, help = "Password for the login."),
94
'db_host': fields.char('Database host',size = 64,required = True , help= "Give hostname to make connection to the database."),
95
'db_port': fields.integer('Database port',required = True, help = " Port to be used in connection"),
96
'type': fields.selection([('mysql','MySQL' ),('postgres','PostgreSQL' ),('oracle','Oracle' )],'Database type',required = True ),
97
'connection_type': fields.selection([('socket','Socket' ),('port','Port' )],'Connection type',required = True ),
98
'connection_url': fields.function(_connection_get,method = True,type = 'char',string = 'Connection URL',size = 128 ),
99
'table_ids': fields.one2many('olap.database.tables','fact_database_id','Tables' ),
100
'loaded': fields.boolean('Loaded',readonly = True ),
103
'type': lambda * args: 'postgres',
104
'connection_type': lambda * args: 'port',
105
'db_host': lambda * args: 'localhost',
106
'db_name': lambda * args: 'terp',
107
'db_port': lambda * args: '5432',
108
'loaded' : lambda * args: False,
112
class olap_schema(osv.osv ):
113
_name = "olap.schema"
114
_description = "Olap Schema"
116
def _app_detect(self,cr,uid,ids,field_name,arg,context = {}):
118
Return a Application type
122
for obj in self.browse(cr,uid,ids,context):
123
if obj.database_id.type == 'postgres':
124
e = sqlalchemy.create_engine(obj.database_id.connection_url)
125
app_objs = self.pool.get('olap.application')
126
app_ids = app_objs.search(cr,uid,[] )
127
app_res = app_objs.browse(cr,uid,app_ids)
128
for app_obj in app_res:
130
result = e.execute(app_obj.query)
132
res[obj.id] = app_obj.name + ' Application'
136
if not res.has_key(obj.id):
137
res[obj.id] = "Unknown Application"
139
res[obj.id] = "Unknown Application"
143
'name': fields.char('Schema name',size = 64,required = True),
144
'note': fields.text('Schema description' ),
145
'cube_ids': fields.one2many('olap.cube','schema_id','Cubes'),
146
'database_id': fields.many2one('olap.fact.database','Database Connection',required = True),
147
'loaded': fields.boolean('Loading Datastructure',readonly = True),
148
'configure': fields.boolean('Configuring Datastructure',readonly = True),
149
'ready': fields.boolean('Ready',readonly = True),
150
'state': fields.selection([
151
('none','Nothing has been Configured'),
152
('dbconnect','Database Connected' ),
153
('dbload','The Structure is Loaded'),
154
('dbconfigure','The Structure is Configured.'),
155
('dbready','Schema is ready to use'),
156
('done','We Can Start building Cube'),
157
],'Schema State',readonly = True),
158
'app_detect': fields.function(_app_detect,method = True,type = 'char',string = 'Connection URL',size = 128),
162
'loaded' : lambda * args: False,
163
'state': lambda * a: 'none',
164
'configure': lambda * a: False,
165
'ready': lambda * a: False
168
def action_dbconnect(self,cr,uid,ids,context = {}):
169
schema = self.browse(cr,uid,ids,context)[0]
170
type = schema.database_id.type
173
if type == 'postgres':
174
host = schema.database_id.db_host and "host=%s" % schema.database_id.db_host or ''
175
port = schema.database_id.db_port and "port=%s" % schema.database_id.db_port or ''
176
name = schema.database_id.db_name and "dbname=%s" % schema.database_id.db_name or ''
177
user = schema.database_id.db_login and "user=%s" % schema.database_id.db_login or ''
178
password = schema.database_id.db_password and "password=%s" % schema.database_id.db_password or ''
179
tdb = psycopg2.connect('%s %s %s %s %s' % (host,port,name,user,password))
181
elif type == 'mysql':
184
host = schema.database_id.db_host or ''
185
port = schema.database_id.db_port or ''
186
db = schema.database_id.db_name or ''
187
user = schema.database_id.db_login or ''
188
passwd = schema.database_id.db_password or ''
189
tdb = MySQLdb.connect(host = host,port = port,db = db,user = user,passwd = passwd)
191
raise osv.except_osv('Error (MySQLdb) : ',e)
193
elif type == 'oracle':
196
host = schema.database_id.db_host or ''
197
port = schema.database_id.db_port or ''
198
db = schema.database_id.db_name or ''
199
user = schema.database_id.db_name.upper() or ''
200
password = int(schema.database_id.db_password) or ''
201
tdb = cx_Oracle.connect(user,password,host)
203
raise osv.except_osv('Error (cx_Oracle) : ',e)
206
self.write(cr,uid,id,{'state':'dbconnect'})
209
raise osv.except_osv('BI Error !',e)
213
def action_dbload(self,cr,uid,ids,context = {}):
215
id_change = self.browse(cr,uid,id)
216
self.write(cr,uid,id,{'loaded':True})
217
self.write(cr,uid,id,{'state':'dbload'})
221
def action_dbconfigure(self,cr,uid,ids,context = {}):
223
id_browsed = self.browse(cr,uid,id)
224
if not id_browsed.state == 'dbconfigure':
225
self.write(cr,uid,id,{'state':'dbconfigure'})
226
self.write(cr,uid,id,{'configure':True})
229
def action_dbready(self,cr,uid,ids,context = {}):
231
self.write(cr,uid,id,{'ready':True})
232
self.write(cr,uid,id,{'state':'done'})
235
def action_done(self,cr,uid,ids,context = {}):
237
self.write(cr,uid,id,{'state':'done'})
240
def create_xml_schema(self,cr,uid,xml_schema,context = {}):
242
This function fill in the database according to a XML schema.
246
<Table name="sales_fact_1997"/>
247
<Dimension name="Gender" foreignKey="customer_id">
248
<Hierarchy hasAll="true" allMemberName="All Genders" primaryKey="customer_id">
249
<Table name="customer"/>
250
<Level name="Gender" column="gender" uniqueMembers="true"/>
253
<Dimension name="Time" foreignKey="time_id">
254
<Hierarchy hasAll="false" primaryKey="time_id">
255
<Table name="time_by_day"/>
256
<Level name="Year" column="the_year" type="Numeric" uniqueMembers="true"/>
257
<Level name="Quarter" column="quarter" uniqueMembers="false"/>
258
<Level name="Month" column="month_of_year" type="Numeric" uniqueMembers="false"/>
261
<Measure name="Store Sales" column="store_sales" aggregator="sum" formatString="#,###.##"/>
262
<Measure name="Store Cost" column="store_cost" aggregator="sum" formatString="#,###.00"/>
263
<CalculatedMember name="Profit" dimension="Measures" formula="[Measures].
264
[Store Sales]-[Measures].[Store Cost]">
265
<CalculatedMemberProperty name="FORMAT_STRING" value="$#,##0.00"/>
270
raise 'Not implemented !'
272
def request(self,cr,uid,name,request,context = {}):
273
ids = self.search(cr,uid,[('name','=',name)])
275
raise 'Schema not found !'
276
schema = self.browse(cr,uid,ids[0],context)
277
# warehouse = cube.warehouse()
278
# find_table = warehouse.match_table(cr, uid, request, context)
279
print 'Parsing MDX...'
281
mdx_parser = cube.mdx_parser()
282
mdx = mdx_parser.parse(request)
284
print 'Validating MDX...'
286
validate,cubex = mdx.validate(schema)
288
print 'Running MDX...'
289
res_comp = self.pool.get('res.company').search(cr,uid,([]))
290
res_comp = self.pool.get('res.company').browse(cr,uid,res_comp)
291
currency = res_comp[0].currency_id.name
292
print " Default Currency",currency
293
data = mdx.run(currency)
294
# qry_obj = self.pool.get('olap.query.logs')
295
# qry_id = qry_obj.search(cr, uid, [('query','=', request)])
299
# qry = qry_obj.browse(cr, uid, qry_id)[0]
301
# if qry.count >=3 and qry.table_name!='':
302
# data = warehouse.run(currency, qry)
304
# qry.count = qry.count +1
305
# qry_obj.write(cr, uid, qry_id, {'count': qry.count})
307
# data = mdx.run(currency)
309
# data = mdx.run(currency)
310
print 'Running Done...'
311
print 'Formatting Output...'
312
# if cubex.query_log and flag:
314
log = context.get('log')
316
connection = schema.database_id.connection_url
317
# warehouse.log(cr,uid,cubex,request,data,connection,context)
318
mdx.log(cr,uid,cubex,request,context)
319
return cube.mdx_output(data)
322
class olap_database_tables(osv.osv):
323
_name = "olap.database.tables"
324
_description = "Olap Database Tables"
326
'table_db_name': fields.char('Table Name',size = 64,required = True,readonly = True),
327
'name': fields.char('End-User Name',size = 64,required = True),
328
'columns': fields.one2many('olap.database.columns','table_id','Columns'),
329
'fact_database_id': fields.many2one('olap.fact.database','Database Id',required = True,ondelete = 'cascade',readonly = True),
330
'active': fields.boolean('Active'),
331
'hide': fields.boolean('Hidden'),
334
'active': lambda * args: True,
335
'hide': lambda * args: False
337
def name_get(self,cr,uid,ids,context = {}):
339
for t in self.browse(cr,uid,ids,context):
340
if t.name <> t.table_db_name:
341
result.append((t.id,t.name + ' (' + t.table_db_name + ')'))
343
result.append((t.id,t.name))
346
def show_col_view(self,cr,uid,ids,context = {}):
347
ids_cols = self.pool.get('olap.database.columns').search(cr,uid,([('table_id','=',ids[0])]))
348
id = self.pool.get('ir.ui.view').search(cr,uid,([('name','=','olap.database.columns.tree')]),context = {})[0]
350
'domain': "[('id','in', [" + ','.join(map(str,ids_cols)) + "])]",
351
'name': 'Database Columns',
354
'res_model': 'olap.database.columns',
355
'views': [(id,'tree'),(False,'form')],
356
'type': 'ir.actions.act_window',
359
def hide_col(self,cr,uid,ids,context = {}):
360
# To hide all the related columns also
362
self.write(cr,uid,id,{'hide':True})
365
def show_col(self,cr,uid,ids,context = {}):
366
# To show or unhide all the columns also
368
self.write(cr,uid,id,{'hide':False})
372
olap_database_tables()
374
class olap_database_columns(osv.osv):
375
_name = "olap.database.columns"
376
_description = "Olap Database Columns"
378
'timestamp': 'TimeStamp without Time Zone',
379
'timestampz': 'TimeStamp with Time Zone',
380
'numeric': 'Numeric',
382
'float8': 'Double Precesion',
383
'varchar': 'Character Varying',
386
'int2':'Small Integer',
388
'int8':'Big Integer',
391
'bpchar': 'Blank Padded Char',
392
'time': 'TimeStamp without Time Zone',
395
def _datatypes_get(self,*args,**argv):
396
return self.datatypes.items()
398
'column_db_name': fields.char('Column DBName',size = 64,required = True,readonly = True),
399
'name': fields.char('Column Name',size = 64,required = True),
400
'table_id': fields.many2one('olap.database.tables','Table Id',required = True,ondelete = 'cascade',select = True,readonly = True),
401
'primary_key': fields.boolean('Primary Key'),
402
'type': fields.selection(_datatypes_get,'Type',size = 64,required = True,readonly = True),
403
'related_to': fields.many2one('olap.database.tables','Related To',required = False,readonly = True),
404
'active': fields.boolean('Active'),
405
'hide': fields.boolean('Hidden'),
408
'hide': lambda * args: False,
409
'active': lambda * args: True,
410
'primary_key': lambda * args: False,
412
def name_get(self,cr,uid,ids,context = {}):
414
for t in self.browse(cr,uid,ids,context):
415
if t.name <> t.column_db_name:
416
result.append((t.id,t.table_id.table_db_name + ' (' + t.name + ')'))
418
result.append((t.id,t.table_id.table_db_name + ' (' + t.name + ')'))
422
def search(self,cr,uid,args,offset = 0,limit = None,order = None,
423
context = None,count = False):
425
return super(olap_database_columns,self).search(cr,uid,args,offset,limit,
426
order,context = context,count = count)
427
col_pool = self.pool.get('olap.database.columns')
428
if context and context.has_key('fk') and context['fk']:
429
if context.has_key('dim_x') and context['dim_x']:
430
dim_obj = self.pool.get('olap.dimension').browse(cr,uid,int(context['dim_x']))
432
make_ids.append(dim_obj.cube_id.table_id.column_link_id.table_id.id)
434
for lines in dim_obj.cube_id.table_id.line_ids:
435
make_ids.append(lines.field_id.related_to.id)
436
make_ids.append(lines.field_id.table_id.id)
437
args = [('table_id','in',make_ids),('related_to','<>',False),('hide','<>',True),('active','<>',False)]
438
return super(olap_database_columns,self).search(cr,uid,args,offset,limit,order,context = context,count = count)
440
if args and context and context.has_key('flag') and context['flag']:
441
ids = args[0][2][0][2]
444
col_obj = col_pool.browse(cr,uid,ids)
446
for lines in col_obj:
447
make_ids.append(lines.related_to.id)
448
link_id = col_pool.browse(cr,uid,int(context['link_col']))
449
make_ids.append(link_id.table_id.id)
450
args = ['|',('table_id','in',make_ids),('related_to','in',make_ids),('primary_key','<>',True),('hide','<>',True),('active','<>',False)]
451
ids = super(olap_database_columns,self).search(cr,uid,args,offset,limit,order,context = context,count = count)
453
elif context and context.has_key('master_dim') and context['master_dim']:
455
col_obj = col_pool.browse(cr,uid,int(context['link_col']))
456
args = ['|',('table_id','=',col_obj.related_to.id),('related_to','=',col_obj.table_id.id),('hide','<>',True),('active','<>',False)]
457
return super(olap_database_columns,self).search(cr,uid,args,offset,limit,order,context = context,count = count)
459
col = col_pool.browse(cr,uid,int(context['link_col']))
460
base_table = col.table_id
461
args = ['|',('table_id','=',base_table.id),('related_to','=',base_table.id),('hide','<>',True),('active','<>',False)]
462
return super(olap_database_columns,self).search(cr,uid,args,offset,limit,order,context = context,count = count)
465
if context and context.has_key('filter_cols_cube'):
466
cube_obj = self.pool.get('olap.cube').browse(cr,uid,int(context['filter_cols_cube']))
468
make_ids.append(cube_obj.table_id.column_link_id.related_to.id)
469
for lines in cube_obj.table_id.line_ids:
470
make_ids.append(lines.table_id.id)
472
make_ids.append(cube_obj.table_id.line_ids[len(cube_obj.table_id.line_ids) - 1].field_id.related_to.id)
473
args = [('table_id','in',make_ids),('related_to','=',False),('primary_key','<>',True),('type','not in',['date','timestamp','timestampz','time']),('hide','<>',True),('active','<>',False)]
474
ids = super(olap_database_columns,self).search(cr,uid,args,offset,limit,
475
order,context = context,count = count)
478
elif context and context.has_key('filter_cols_hier'):
479
hier_obj = self.pool.get('olap.hierarchy').browse(cr,uid,int(context['filter_cols_hier']))
481
if hier_obj.table_id.line_ids:
482
for lines in hier_obj.table_id.line_ids:
483
make_ids.append(lines.field_id.related_to.id)
486
make_ids.append(hier_obj.table_id.column_link_id.related_to.id)
487
make_ids.append(hier_obj.table_id.column_link_id.table_id.id)
488
args = [('table_id','in',make_ids),('hide','<>',True),('active','<>',False)]
489
ids = super(olap_database_columns,self).search(cr,uid,args,offset,limit,
490
order,context = context,count = count)
493
args = [('table_id','=',hier_obj.table_id.column_link_id.related_to.id)]
494
ids = super(olap_database_columns,self).search(cr,uid,args,offset,limit,
495
order,context = context,count = count)
497
elif context and context.has_key('fk') and context['fk']:
498
args = [('primary_key','=',True),('hide','<>',True),('active','<>',False)]
501
if context and context.has_key('master_dim') and context['master_dim']:
502
dim_obj = self.pool.get('olap.dimension').browse(cr,uid,int(context['master_dim']))
503
lines = dim_obj.cube_id.table_id.line_ids
506
table_ids.append(line.table_id.id)
507
args = [('table_id','in',table_ids),('related_to','<>',False),('hide','<>',True),('active','<>',False)]
508
elif context and context.has_key('master_schema') and context['master_schema']:
509
args = [('primary_key','=','True')]
510
return super(olap_database_columns,self).search(cr,uid,args,offset,limit,
511
order,context = context,count = count)
514
def hide_col(self,cr,uid,ids,context = {}):
516
self.write(cr,uid,id,{'hide':True})
519
def show_col(self,cr,uid,ids,context = {}):
521
self.write(cr,uid,id,{'hide':True})
524
def field_add(self,cr,uid,ids,context = {}):
525
col_data = self.pool.get('olap.database.columns').read(cr,uid,ids,[],context)[0]
527
if col_data['related_to']:
528
table_id = col_data['related_to'][0]
530
table_id = col_data['table_id'][0]
531
if context['parent_id']:
532
parent_id = context['parent_id']
534
'cube_table_id':parent_id,
538
id = self.pool.get('olap.cube.table.line').create(cr,uid,val,context)
540
parent_id = self.pool.get('olap.cube.table').create(cr,uid,{'name':col_data['table_id'][1]},context)
541
ctx_list = [('client','web'),('app','bi'),('id',parent_id)]
544
def make_hierarchy(self,cr,uid,ids,context = {}):
545
col_data = self.pool.get('olap.database.columns').read(cr,uid,ids,[],context)[0]
547
if context and context.has_key('hier_parent_id')and context['hier_parent_id']:
548
hier_obj = self.pool.get('olap.hierarchy').browse(cr,uid,context['hier_parent_id'])
549
pk_table = hier_obj.table_id.name
550
elif context and context.has_key('hier_parent_table'):
551
cube_table_obj = self.pool.get('olap.cube.table').browse(cr,uid,context['hier_parent_table'])
552
pk_table = cube_table_obj.name
554
'field_name':col_data['name'],
555
'name':col_data['name'],
556
'primary_key_table':pk_table
558
if context['hier_parent_id']:
559
id = self.pool.get('olap.hierarchy').write(cr,uid,context['hier_parent_id'],val,context)
561
if context['parent_name']: val['name'] = context['parent_name']
562
if context['parent_dimension'] :val['dimension_id'] = context['parent_dimension']
563
if context['hier_parent_table'] : val['table_id'] = context['hier_parent_table']
564
if context['parent_field_name'] : val['field_name'] = context['parent_field_name']
565
if context['parent_level'] : val['level_ids'] = conext['parent_level']
566
if context['parent_member_all']: val['member_all'] = context['parent_member_all']
567
if context['parent_member_default'] : val['member_default'] = context['parent_member_default']
568
if context['parent_type']: val['type'] = context['parent_type']
569
val['primary_key_table'] = col_data['table_id'][1]
570
id = self.pool.get('olap.hierarchy').create(cr,uid,val,context)
571
ctx_list = [('client','web'),('app','bi'),('id',id)]
574
olap_database_columns()
576
class olap_cube_table(osv.osv):
577
_name = "olap.cube.table"
578
_description = "Olap cube table"
580
def write(self,cr,uid,ids,vals,context = None):
581
if vals and vals.get('available_table_ids',0) and context and (context.has_key('master_dim') or context.has_key('d_id') or context.has_key('parent_schema_id')):
582
new_fields = vals['available_table_ids'][0][2]
584
for data in self.browse(cr,uid,ids):
586
for line in data.line_ids:
587
orignal_lines.append(line.id)
590
for line in data.line_ids:
591
orignal_fields.append(line.field_id.id)
592
if len(orignal_fields) < len(new_fields):
593
if new_fields[:len(orignal_fields)] == orignal_fields:
594
new_fields = new_fields[len(orignal_fields):]
595
cols_obj = self.pool.get('olap.database.columns').browse(cr,uid,new_fields)
597
val['cube_table_id'] = ids[0]
599
val['table_id'] = col.table_id.id
600
val['field_id'] = col.id
601
id = self.pool.get('olap.cube.table.line').create(cr,uid,val,context = context)
603
cols_obj = self.pool.get('olap.database.columns').unlink(cr,uid,orignal_lines)
604
cols_obj = self.pool.get('olap.database.columns').browse(cr,uid,new_fields)
606
val['cube_table_id'] = ids[0]
608
val['table_id'] = col.table_id.id
609
val['field_id'] = col.id
610
id = self.pool.get('olap.cube.table.line').create(cr,uid,val,context = context)
612
elif len(orignal_fields) > len(new_fields):
613
if orignal_fields[:len(new_fields)] == new_fields:
614
remove_id = orignal_lines[len(new_fields):]
615
id = self.pool.get('olap.cube.table.line').unlink(cr,uid,remove_id,context = context)
618
id = self.pool.get('olap.cube.table.line').unlink(cr,uid,orignal_lines ,context = context)
619
cols_obj = self.pool.get('olap.database.columns').browse(cr,uid,new_fields)
621
val['cube_table_id'] = ids[0]
623
val['table_id'] = col.table_id.id
624
val['field_id'] = col.id
625
id = self.pool.get('olap.cube.table.line').create(cr,uid,val,context = context)
626
return super(olap_cube_table,self).write(cr,uid,ids,vals,context)
628
def create(self,cr,uid,vals,context = None):
629
cube_table_id = super(olap_cube_table,self).create(cr,uid,vals,context)
630
if vals and vals.get('available_table_ids',0) and context and (context.has_key('d_id') or context.has_key('parent_schema_id') or context.has_key('master_dim') or context.has_key('d_id')):
631
lines_ids = vals['available_table_ids'][0][2]
632
cols_obj = self.pool.get('olap.database.columns').browse(cr,uid,lines_ids)
634
val['cube_table_id'] = cube_table_id
636
val['table_id'] = col.table_id.id
637
val['field_id'] = col.id
638
id = self.pool.get('olap.cube.table.line').create(cr,uid,val,context = context)
641
def search(self,cr,uid,args,offset = 0,limit = None,order = None,
642
context = None,count = False):
643
if context and context.has_key('parent_schema_id'):
644
args = [('schema_id','=',context['parent_schema_id'])]
645
if context and context.has_key('d_id'):
646
dim_obj = self.pool.get('olap.dimension').browse(cr,uid,int(context['d_id']))
647
args = [('schema_id','=',dim_obj.cube_id.schema_id.id)]
648
return super(olap_cube_table,self).search(cr,uid,args,offset,limit,order,context = context,count = count)
650
def _available_table_get(self,cr,uid,ids,name,arg,context = None):
653
parent_table_ids = []
654
field_obj = self.pool.get('olap.database.columns')
655
for table in self.browse(cr,uid,ids,context):
658
ids = map(lambda x: x.field_id.id,table.line_ids)
659
result[table.id] = ids
661
result[table.id] = []
664
def _set_schema(self,cr,uid,context = {}):
665
if context and context.has_key('d_id'):
666
dim_obj = self.pool.get('olap.dimension').browse(cr,uid,int(context['d_id']))
667
return dim_obj.cube_id.schema_id.id
668
if context and context.has_key('parent_schema_id'):
669
return context['parent_schema_id']
671
def _set_name(self,cr,uid,context = {}):
672
if context and context.has_key('d_id'):
673
dim_obj = self.pool.get('olap.dimension').browse(cr,uid,int(context['d_id']))
674
return dim_obj.cubeAid.table_id.name
676
def _get_id(self,cr,uid,ids,context = {}):
677
if context and context.has_key('d_id'):
678
dim_obj = self.pool.get('olap.dimension').browse(cr,uid,int(context['d_id']))
679
table_id = self.pool.get('olap.database.tables').search(cr,uid,[('table_db_name','in',[dim_obj.cube_id.table_id.name]),('fact_database_id','=',dim_obj.cube_id.schema_id.database_id.id)])
680
col_ids = self.pool.get('olap.database.columns').search(cr,uid,[('table_id','in',table_id),('hide','<>',True),('related_to','<>',False)])
682
col_ids = self.pool.get('olap.database.columns').search(cr,uid,[('primary_key','=',True),('hide','<>',True)])
685
def _def_set(self,cr,uid,context = {}):
689
'name': fields.char('Table name',size = 64,required = True),
690
'line_ids': fields.one2many('olap.cube.table.line','cube_table_id','Database Tables',required = True),
691
'schema_id':fields.many2one('olap.schema','Schema id',ondelete = 'cascade'),
692
'column_link_id':fields.many2one('olap.database.columns','Relational Column' ,required = True),
693
'available_table_ids': fields.function(
694
_available_table_get,
696
relation = 'olap.database.columns',
697
string = 'Available Tables',
702
'schema_id':_set_schema,
704
def field_add(self,cr,uid,ids,context = {}):
708
class olap_cube_table_line(osv.osv):
709
_name = "olap.cube.table.line"
710
_description = "Olap cube table"
711
_rec_name = 'table_id'
713
'cube_table_id': fields.many2one('olap.cube.table','Cube Table',required = True,ondelete = 'cascade'),
714
'table_id': fields.many2one('olap.database.tables','Database Table',required = True,ondelete = 'cascade'),
715
'field_id': fields.many2one('olap.database.columns','Link Field'),
717
# Set the Table when changing field_id
718
def onchange_field_id(self,*args,**argv):
720
olap_cube_table_line()
722
class olap_cube(osv.osv):
724
_description = "Olap cube"
726
def _set_schema(self,cr,uid,context = {}):
727
if context and context.has_key('schema_id'):
728
return context['schema_id']
732
'name': fields.char('Cube name',size = 64,required = True),
733
'table_id': fields.many2one('olap.cube.table','Fact table',size = 64,required = True, help="Table(s) for cube."),
734
'schema_id': fields.many2one('olap.schema','Schema',readonly = True),
735
'dimension_ids': fields.one2many('olap.dimension','cube_id','Dimensions'),
736
'measure_ids': fields.one2many('olap.measure','cube_id','Measures'),
737
'query_log': fields.boolean('Query Logging', help = "Enabling this will log all the queries in the browser"),
738
'query_ids': fields.one2many('olap.query.logs','cube_id','Queries'),
741
'schema_id':_set_schema
745
class olap_query_logs(osv.osv):
746
_name = "olap.query.logs"
747
_description = "Olap query logs"
749
'user_id' : fields.many2one('res.users','Tiny ERP User'),
750
'query':fields.text('Query',required = True),
751
'time':fields.datetime('Time',required = True),
752
'result_size':fields.integer('Result Size',readonly = True),
753
'cube_id': fields.many2one('olap.cube','Cube',required = True),
754
'count': fields.integer('Count', readonly=True),
755
'schema_id': fields.many2one('olap.schema','Schema',readonly = True),
756
# 'table_name': fields.char('Table Name', size=164, readonly = True),
760
'count':lambda * args: 0
765
class olap_dimension(osv.osv):
766
_name = "olap.dimension"
767
_description = "Olap dimension"
769
def _set_cube(self,cr,uid,context = {}):
770
if context and context.has_key('cube_id'):
771
return context['cube_id']
775
'name': fields.char('Dimension name',size = 64,required = True),
776
'cube_id': fields.many2one('olap.cube','Cube',required = True),
777
'hierarchy_ids': fields.one2many('olap.hierarchy','dimension_id','Hierarchies'),
785
class olap_hierarchy(osv.osv):
786
_name = "olap.hierarchy"
787
_description = "Olap hierarchy"
788
_order = "sequence, id"
791
'date_year': 'Year of Date',
792
'date_quarter': 'Quarter of Date',
793
'date_month': 'Month of Date',
794
'many2one': 'Many2one'
797
def _set_dimension(self,cr,uid,context = {}):
798
if context and context.has_key('dimension_id'):
799
return context['dimension_id']
802
def _set_name(self,cr,uid,context = {}):
803
if context and context.has_key('dimension_id'):
804
dim_obj = self.pool.get('olap.dimension').browse(cr,uid,int(context['dimension_id']))
808
def _hierarchy_get(self,*args,**argv):
809
return self.hierarchy_type.items()
812
'name': fields.char('Hierarchy name',size = 64,required = True),
813
'primary_key': fields.char('Primary key',size = 64),
814
'primary_key_table': fields.char('Primary key table',size = 64),
815
'sequence': fields.integer('Sequence',required = True),
816
'dimension_id': fields.many2one('olap.dimension','Dimension',required = True),
817
'level_ids': fields.one2many('olap.level','hierarchy_id','Levels'),
818
'table_id': fields.many2one('olap.cube.table','Fact table(s)',required = True , help ="Table(s) to make hierarchy on the cube."),
821
'sequence': lambda * args: 1,
822
'primary_key': lambda * args: 'id',
823
'dimension_id':_set_dimension,
829
class olap_level(osv.osv):
831
_description = "Olap level"
832
_order = "sequence, id"
834
'normal': levels.level_normal(),
835
'date_year': levels.level_date_year(),
836
'date_quarter': levels.level_date_quarter(),
837
'date_month': levels.level_date_month()
840
def _set_hierarchy(self,cr,uid,context = {}):
841
if context and context.has_key('hierarchy_id'):
842
return context['hierarchy_id']
845
def _set_name(self,cr,uid,context = {}):
846
if context and context.has_key('hierarchy_id'):
847
hier_obj = self.pool.get('olap.hierarchy').browse(cr,uid,int(context['hierarchy_id']))
850
def onchange_column_name(self,cr,uid,ids,column,context = {}):
854
col = self.pool.get('olap.database.columns').browse(cr,uid,column)
855
val['table_name'] = col.table_id.table_db_name
856
val['column_id_name'] = col.column_db_name
857
if (col.type == 'date'):
858
val['type'] = 'date_year'
861
def _type_get(self,cr,uid,*args,**argv):
862
keys = self._types.keys()
863
return map(lambda x: (x,x),keys)
867
'name': fields.char('Level name',size = 64,required = True),
868
'column_name':fields.many2one('olap.database.columns','Columns Name',required = True),
869
'column_id_name': fields.char('Column ID',size = 64,required = True),
870
'type': fields.selection(selection = _type_get,string = 'Level class',size = 64,required = True),
871
'table_name': fields.char('Table name',size = 64,required = True,help = "The name of the table on which the column is defined. If False, take the table from the hierarchy."),
872
'sequence': fields.integer('Sequence',required = True),
873
'hierarchy_id': fields.many2one('olap.hierarchy','Hierarchy',required = True),
877
'column_id_name': lambda * args: 'name',
878
'sequence':lambda * args: '1',
879
'type':lambda * args:'normal',
880
'hierarchy_id':_set_hierarchy,
887
class olap_measure(osv.osv):
888
_name = "olap.measure"
889
_description = "Olap measure"
891
def _set_cube(self,cr,uid,context = {}):
892
if context and context.has_key('cube_id'):
893
return context['cube_id']
896
def onchange_measure_name(self,cr,uid,ids,column,context = {}):
900
col = self.pool.get('olap.database.columns').browse(cr,uid,column)
901
val['table_name'] = col.table_id.table_db_name
902
val['value_column_id_name'] = col.column_db_name
903
val['name'] = col.column_db_name
907
'name': fields.char('Measure name',size = 64,required = True),
908
'cube_id': fields.many2one('olap.cube','Cube',required = True),
909
'value_column': fields.many2one('olap.database.columns','Fact Table Column'),
910
'value_column_id_name': fields.char('Column ID',size = 64),
911
'table_name': fields.char('Table name',size = 64,help = "The name of the table on which the column is defined. If False, take the table from the cube."),
912
'measure_type':fields.selection([('fact_column','Fact Table Column'),('sql_expr','SQL Expression')],'Measure Type',required = True,help = "Select between auto column or sql expression for the measures"),
913
'value_sql': fields.char('SQL Expression',size = 200,help = "You can provide valid sql expression. Make sure it have function with fully qualified column name like (sum,avg ...)(tablename.columnname (+,- ...) tablename.columnname)"),
914
'agregator': fields.selection([('sum','Sum'),('count','count'),('avg','Average')],'Agregator',required = True),
915
'datatype': fields.selection([('int','Integer'),('float','Float')],'Datatype',required = True),
916
'formatstring': fields.selection([
917
('none','None (0000.00)'),
918
('cr_prefix','Prefix Default Currency (EUR 0000.00)'),
919
('cr_postfix','Postfix Default Currency(0000.00 EUR)'),
920
('cr_prefix_comma','Prefix Default Currency with comma seperator (EUR 0,000.00)'),
921
('cr_postfix_comma','Postfix Default Currency with comma seperator (0,000.00 EUR)'),
922
('comma_sep', 'Comma Seperator (0,000)')
924
'Format string',required = True, help=" Let you specify how the measure to be displayed in cube browser"),
927
'agregator': lambda * args: 'sum',
928
'datatype': lambda * args: 'float',
929
'formatstring': lambda * args: 'none',
931
'measure_type':lambda * args:'fact_column',
935
class olap_application(osv.osv):
936
_name = "olap.application"
937
_description = "Olap application"
939
'name': fields.char('Application name',size = 64,required = True),
940
'query':fields.text('Application Query'),
941
'table_ids':fields.one2many('olap.application.table','application_id','Tables'),
942
'field_ids': fields.one2many('olap.application.field','application_id','Fields'),
947
class olap_application_table(osv.osv):
948
_name = "olap.application.table"
949
_description = "Olap application table"
951
'name': fields.char('Application table name',size = 64,required = True),
952
'table_name': fields.char('Table name',size = 64,required = True),
953
'is_hidden': fields.boolean('Hidden'),
954
'application_id': fields.many2one('olap.application','Application Id',required = True),
956
olap_application_table()
958
class olap_application_field(osv.osv):
959
_name = "olap.application.field"
960
_description = "Olap application field"
962
'name': fields.char('Application field name',size = 64,required = True),
963
'table_name': fields.char('Application table name',size = 64),
964
'field_name':fields.char('Field name',size = 64),
965
'is_hidden': fields.boolean('Hidden'),
966
'application_id': fields.many2one('olap.application','Application Id',required = True),
968
olap_application_field()
970
class olap_saved_query(osv.osv):
971
_name = "olap.saved.query"
972
_decription = "Olap Saved Query"
973
# _rec_name = 'user_id'
975
'name': fields.text('Query Name',size = 64),
976
'user_id' : fields.many2one('res.users','User'),
977
'query': fields.text('Query',required = True),
978
'cube_id': fields.many2one('olap.cube','Cube',required = True),
979
'mdx_id': fields.char('Module', size=64),
980
'schema_id': fields.many2one('olap.schema','Schema',required = True),
981
'time':fields.datetime('Time',required = True),
982
'axis_keys': fields.text('Axis Keys'),
985
# Wizard for the Load Data Structure
986
# Replacement for the Load Wizard according to the new structure
987
class bi_load_db_wizard(osv.osv_memory):
988
_name = 'bi.load.db.wizard'
990
def _get_fact_table(self,cr,uid,ctx):
991
if ctx and ctx.has_key('active_id'):
992
schema_obj = self.pool.get('olap.schema').browse(cr,uid,ctx['active_id'])
993
return schema_obj.name
996
def _get_db_name(self,cr,uid,ctx):
997
if ctx and ctx.has_key('active_id'):
998
schema_obj = self.pool.get('olap.schema').browse(cr,uid,ctx['active_id'])
999
return schema_obj.database_id.name
1003
'fact_table':fields.char('Fact Name' ,size = 64,readonly = True),
1004
'db_name':fields.char('Database Name',size = 64,readonly = True)
1008
'fact_table':_get_fact_table,
1009
'db_name':_get_db_name,
1012
def action_load(self,cr,uid,ids,context = None):
1013
if context and context.has_key('active_id'):
1014
lines = self.pool.get('olap.schema').browse(cr,uid,context['active_id'])
1015
pool = pooler.get_pool(cr.dbname)
1016
# lines=pool.get('olap.schema').browse(cr, uid, part['id'],context)
1017
id_db = lines.database_id.id
1018
type = lines.database_id.type
1019
db_name = lines.database_id.db_name
1020
tobj = pool.get('olap.database.tables')
1021
tcol = pool.get('olap.database.columns')
1022
if type == 'postgres':
1023
# host = lines.database_id.db_host and "host=%s" % lines.database_id.db_host or ''
1024
# port = lines.database_id.db_port and "port=%s" % lines.database_id.db_port or ''
1025
# name = lines.database_id.db_name and "dbname=%s" % lines.database_id.db_name or ''
1026
# user = lines.database_id.db_login and "user=%s" % lines.database_id.db_login or ''
1027
# password = lines.database_id.db_password and "password=%s" % lines.database_id.db_password or ''
1028
# tdb = psycopg2.connect('%s %s %s %s %s' % (host, port, name, user, password))
1029
# cr_db = tdb.cursor()
1030
# cr.execute('select table_db_name,id from olap_database_tables where fact_database_id=%d', (id_db,))
1031
# tables = dict(cr.fetchall())
1032
# # Format for storing the tables
1033
# # tables['table_db_name']=id
1034
# tables_id = map(lambda x: str(tables[x]),tables)
1037
# cr.execute('select column_db_name,id,table_id from olap_database_columns where table_id in (' + ','.join(tables_id) +')')
1039
# cr.execute('select column_db_name,id,table_id from olap_database_columns')
1041
# for data in cr.fetchall():
1042
# cols[str(data[1])]=(data[0],int(data[2]))
1043
# # Format of storing the cols
1044
# # cols['id']=(col_db_name,table_id)
1045
# print 'Creating / Updating Tables...'
1046
# cr_db.execute("select table_name, table_catalog from INFORMATION_SCHEMA.tables as a where a.table_schema = 'public'")
1047
# for table in cr_db.fetchall():
1049
# 'fact_database_id':id_db,
1050
# 'table_db_name':table[0]
1053
# if table[0] in tables.keys():
1054
# table_id=tobj.write(cr,uid,[tables[table[0]]], val, context)
1056
# val['name']=table[0]
1057
# tables[val['name']] = tobj.create(cr,uid,val, context)
1058
# print 'Creating / Updating Columns...'
1059
# cr_db.execute("""SELECT
1060
# table_name, column_name, udt_name
1062
# INFORMATION_SCHEMA.columns
1063
# WHERE table_schema = 'public'""")
1065
# for col in cr_db.fetchall():
1067
# 'table_id': tables[col[0]],
1068
# 'column_db_name': col[1],
1072
# id_made=filter(lambda x:(int(cols[x][1])==int(tables[col[0]])),cols)
1073
# if col[1] in cols.keys() and col[0] in tables.keys()and id_made:
1074
# col_id=tcol.write(cr,uid,cols[tables[str(col[0])]], val, context)
1076
# val['name']=col[1]
1077
# id_made = tcol.create(cr,uid,val, context)
1078
# cols[str(id_made)] = (val['name'],int(val['table_id']))
1079
# print 'Creating / Updating Constraints...'
1080
# cr_db.execute("""select
1081
# table_name,column_name
1083
# INFORMATION_schema.key_column_usage
1085
# constraint_name in (
1086
# select constraint_name from INFORMATION_SCHEMA .table_constraints
1088
# constraint_type = 'PRIMARY KEY')""")
1089
# print "Updating the Primary Key Constraint"
1090
# for constraint in cr_db.fetchall():
1092
# 'primary_key':True
1095
# id_to_write=filter(lambda x:(int(cols[x][1])==int(tables[constraint[0]])and(constraint[1]==cols[x][0])),cols)
1096
# col_id=tcol.write(cr,uid,int(id_to_write[0]),val,context)
1097
# print "Updating the Foreign key constraint"
1098
# cr_db.execute("""select
1099
# constraint_name,table_name
1101
# INFORMATION_schema.constraint_column_usage
1103
# constraint_name in (
1104
# select constraint_name from INFORMATION_SCHEMA.table_constraints
1106
# constraint_type = 'FOREIGN KEY')""")
1107
# for_key=dict(cr_db.fetchall())
1109
# cr_db.execute("""select
1110
# table_name,column_name,constraint_name
1112
# INFORMATION_schema.key_column_usage
1114
# constraint_name in (
1115
# select constraint_name from INFORMATION_SCHEMA.table_constraints
1117
# constraint_type = 'FOREIGN KEY')""")
1119
# for constraint in cr_db.fetchall():
1121
# 'related_to':tables[for_key[constraint[2]]]
1123
# id_to_write=filter(lambda x:(int(cols[x][1])==int(tables[constraint[0]])and (constraint[1]==cols[x][0])),cols)
1124
# col_id=tcol.write(cr,uid,int(id_to_write[0]),val,context)
1126
host = lines.database_id.db_host and "host=%s" % lines.database_id.db_host or ''
1127
port = lines.database_id.db_port and "port=%s" % lines.database_id.db_port or ''
1128
name = lines.database_id.db_name and "dbname=%s" % lines.database_id.db_name or ''
1129
user = lines.database_id.db_login and "user=%s" % lines.database_id.db_login or ''
1130
password = lines.database_id.db_password and "password=%s" % lines.database_id.db_password or ''
1131
tdb = psycopg2.connect('%s %s %s %s %s' % (host,port,name,user,password))
1132
cr_db = tdb.cursor()
1133
cr.execute('select table_db_name,id from olap_database_tables where fact_database_id=%s',(id_db,))
1134
tables = dict(cr.fetchall())
1135
# Format for storing the tables
1136
# tables['table_db_name']=id
1137
tables_id = map(lambda x: str(tables[x]),tables)
1138
# Format of storing the cols
1139
# cols['id']=(col_db_name,table_id)
1140
print 'Creating / Updating Tables...'
1141
cr_db.execute("select table_name, table_catalog from INFORMATION_SCHEMA.tables as a where a.table_schema = 'public'")
1142
for table in cr_db.fetchall():
1144
'fact_database_id':id_db,
1145
'table_db_name':table[0]
1147
if table[0] in tables.keys():
1148
table_id = tobj.write(cr,uid,[tables[table[0]]],val,context)
1150
val['name'] = table[0]
1151
tables[val['name']] = tobj.create(cr,uid,val,context)
1153
print 'Creating / Updating Columns ....'
1156
cr.execute('select column_db_name,id,table_id from olap_database_columns where table_id in (' + ','.join(tables_id) + ')')
1158
# cr.execute('select column_db_name,id,table_id from olap_database_columns ')
1159
cr.execute("select olap_database_columns.column_db_name, olap_database_columns.id, olap_database_columns.table_id from olap_database_columns join olap_database_tables on olap_database_columns.table_id = olap_database_tables.id where olap_database_tables.fact_database_id=%s",(id_db,))
1163
table_col[str(tables[x])] = [{}]
1164
for data in cr.fetchall():
1165
cols[str(data[1])] = (data[0],int(data[2]))
1166
table_col[str(data[2])][0][data[0]] = data[1]
1167
cols_name[str(data[0])] = (data[1],int(data[2]))
1168
cr_db.execute("""SELECT
1169
table_name, column_name, udt_name
1171
INFORMATION_SCHEMA.columns
1172
WHERE table_schema = 'public'""")
1173
for col in cr_db.fetchall():
1175
'table_id': tables[col[0]],
1176
'column_db_name': col[1],
1179
id_made = filter(lambda x:(int(cols[x][1]) == int(tables[col[0]])),cols)
1180
if col[0] in tables.keys() and col[1] in cols_name.keys() and id_made:
1181
if table_col[str(tables[col[0]])][0] and col[1] in table_col[str(tables[col[0]])][0].keys():
1182
col_id = tcol.write(cr,uid,table_col[str(tables[col[0]])][0][col[1]],val,context)
1184
val['name'] = col[1]
1185
id_made = tcol.create(cr,uid,val,context)
1186
cols[str(id_made)] = (val['name'],int(val['table_id']))
1188
val['name'] = col[1]
1189
id_made = tcol.create(cr,uid,val,context)
1190
cols[str(id_made)] = (val['name'],int(val['table_id']))
1192
print 'Creating / Updating Constraints...'
1193
cr_db.execute("""select
1194
table_name,column_name
1196
INFORMATION_schema.key_column_usage
1198
constraint_name in (
1199
select constraint_name from INFORMATION_SCHEMA .table_constraints
1201
constraint_type = 'PRIMARY KEY')""")
1203
print "Updating the Primary Key Constraint"
1204
for constraint in cr_db.fetchall():
1209
id_to_write = filter(lambda x:(int(cols[x][1]) == int(tables[constraint[0]])and(constraint[1] == cols[x][0])),cols)
1210
col_id = tcol.write(cr,uid,int(id_to_write[0]),val,context)
1212
print "Updating the Foreign key constraint"
1213
cr_db.execute("""select
1214
constraint_name,table_name
1216
INFORMATION_schema.constraint_column_usage
1218
constraint_name in (
1219
select constraint_name from INFORMATION_SCHEMA.table_constraints
1221
constraint_type = 'FOREIGN KEY')""")
1222
for_key = dict(cr_db.fetchall())
1224
cr_db.execute("""select
1225
table_name,column_name,constraint_name
1227
INFORMATION_schema.key_column_usage
1229
constraint_name in (
1230
select constraint_name from INFORMATION_SCHEMA.table_constraints
1232
constraint_type = 'FOREIGN KEY')""")
1234
for constraint in cr_db.fetchall():
1236
'related_to':tables[for_key[constraint[2]]]
1238
id_to_write = filter(lambda x:(int(cols[x][1]) == int(tables[constraint[0]])and (constraint[1] == cols[x][0])),cols)
1240
col_id = tcol.write(cr,uid,int(id_to_write[0]),val,context)
1243
elif type == 'mysql':
1246
host = lines.database_id.db_host or ''
1247
port = lines.database_id.db_port or ''
1248
db = lines.database_id.db_name or ''
1249
user = lines.database_id.db_login or ''
1250
passwd = lines.database_id.db_password or ''
1251
tdb = MySQLdb.connect(host = host,port = port,db = db,user = user,passwd = passwd)
1254
raise osv.except_osv('MySQLdb Packages Not Installed.',e)
1256
cr_db = tdb.cursor()
1257
cr.execute('select table_db_name,id from olap_database_tables where fact_database_id=%d',(id_db,))
1258
tables = dict(cr.fetchall())
1259
tables_id = map(lambda x: str(tables[x]),tables)
1262
cr.execute('select column_db_name,id,table_id from olap_database_columns where table_id in (' + ','.join(tables_id) + ')')
1264
cr.execute('select column_db_name,id,table_id from olap_database_columns')
1266
for data in cr.fetchall():
1267
cols[str(data[1])] = (data[0],int(data[2]))
1268
cr_db.execute("select table_name, table_catalog from INFORMATION_SCHEMA.tables where table_schema =%s",(db_name))
1270
for table in cr_db.fetchall():
1272
'fact_database_id':id_db,
1273
'table_db_name':table[0]
1276
if table[0] in tables.keys():
1277
table_id = tobj.write(cr,uid,[tables[table[0]]],val,context)
1280
val['name'] = table[0]
1281
tables[val['name']] = tobj.create(cr,uid,val,context)
1282
cr_db.execute("""SELECT
1283
table_name, column_name, data_type
1285
INFORMATION_SCHEMA.columns
1286
WHERE table_schema = %s""",(db_name))
1288
for col in cr_db.fetchall():
1291
'table_id': tables[col[0]],
1292
'column_db_name': col[1],
1296
id_made = filter(lambda x:(int(cols[x][1]) == int(tables[col[0]])),cols)
1297
if col[1] in cols.keys() and col[0] in tables.keys()and id_made:
1298
col_id = tcol.write(cr,uid,cols[tables[str(col[0])]],val,context)
1300
val['name'] = col[1]
1301
id_made = tcol.create(cr,uid,val,context)
1302
cols[str(id_made)] = (val['name'],int(val['table_id']))
1304
cr_db.execute("""select
1305
REFERENCED_COLUMN_NAME,REFERENCED_TABLE_NAME,COLUMN_NAME,TABLE_NAME
1307
INFORMATION_schema.key_column_usage
1308
where table_schema= %s and
1309
constraint_name in (
1310
select constraint_name from INFORMATION_SCHEMA .table_constraints
1312
constraint_type in('PRIMARY KEY','FOREIGN KEY'))
1314
# lines=pool.get('olap.schema').browse(cr, uid, part['id'],context)
1315
for constraint in cr_db.fetchall():
1319
'related_to':tables[constraint[1]]
1326
id_to_write = filter(lambda x:(int(cols[x][1]) == int(tables[constraint[3]])and(constraint[2] == cols[x][0])),cols)
1327
col_id = tcol.write(cr,uid,int(id_to_write[0]),val,context)
1329
elif type == 'oracle':
1332
host = lines.database_id.db_host or ''
1333
port = lines.database_id.db_port or ''
1334
db = lines.database_id.db_name or ''
1335
user = lines.database_id.db_login.upper() or ''
1336
passwd = lines.database_id.db_password or ''
1337
tdb = cx_Oracle.connect(user,passwrd,host)
1340
raise osv.except_osv('cx_Oracle Packages Not Installed.',e)
1342
cr_db = tdb.cursor()
1343
cr.execute('select table_db_name,id from olap_database_tables where fact_database_id=%d',(id_db,))
1344
tables = dict(cr.fetchall())
1345
tables_id = map(lambda x: str(tables[x]),tables)
1348
cr.execute('select column_db_name,id,table_id from olap_database_columns where table_id in (' + ','.join(tables_id) + ')')
1350
cr.execute('select column_db_name,id,table_id from olap_database_columns')
1352
for data in cr.fetchall():
1353
cols[str(data[1])] = (data[0],int(data[2]))
1355
cr_db.execute("select table_name from all_tables where owner =%s",(user))
1356
temp = cr_db.fetchall()
1359
'fact_database_id':id_db,
1360
'table_db_name':table[0]
1363
if table[0] in tables.keys():
1364
table_id = tobj.write(cr,uid,[tables[table[0]]],val,context)
1367
val['name'] = table[0]
1368
tables[val['name']] = tobj.create(cr,uid,val,context)
1370
cr_db.execute("""SELECT
1371
table_name, column_name, data_type
1374
WHERE owner = %s""",(user))
1375
temp = cr_db.fetchall()
1377
if col[2] == 'NUMBER':
1378
type_col = 'numeric'
1379
elif col[2] == 'DATE':
1381
elif col[2] == 'VARCHAR2':
1382
type_col = 'varchar'
1386
'table_id': tables[col[0]],
1387
'column_db_name': col[1],
1391
id_made = filter(lambda x:(int(cols[x][1]) == int(tables[col[0]])),cols)
1392
if col[1] in cols.keys() and col[0] in tables.keys()and id_made:
1393
col_id = tcol.write(cr,uid,cols[tables[str(col[0])]],val,context)
1395
val['name'] = col[1]
1396
id_made = tcol.create(cr,uid,val,context)
1397
cols[str(id_made)] = (val['name'],int(val['table_id']))
1399
cr_db.execute("""select
1400
table_name,column_name,constraint_name
1404
constraint_name in (
1405
select constraint_name from all_constraints
1407
constraint_type = 'P' and owner= %s)
1409
temp = cr_db.fetchall()
1411
for constraint in temp:
1413
'primary_key' : True
1415
pk_table[constraint[2]] = constraint[0]
1417
id_to_write = filter(lambda x : (int(cols[x][1]) == int(tables[constraint[0]])and(constraint[1] == cols[x][0])),cols)
1418
col_id = tcol.write(cr,uid,int(id_to_write[0]),val,context)
1420
cr_db.execute("""select
1421
constraint_name,r_constraint_name from all_constraints
1423
constraint_type = 'R' and owner = %s
1425
constraints_map = {}
1426
for data in cr_db.fetchall():
1427
constraints_map[data[0]] = data[1]
1429
cr_db.execute("""select
1430
table_name,column_name,constraint_name
1434
constraint_name in (
1435
select constraint_name from all_constraints
1437
constraint_type = 'R' and owner = %s)
1441
temp = cr_db.fetchall()
1442
for constraint in temp:
1443
rel_constraint_name = constraints_map[constraint[2]]
1444
req_table = pk_table[rel_constraint_name]
1446
'related_to' : tables[req_table]
1448
id_to_write = filter(lambda x:(int(cols[x][1]) == int(tables[constraint[0]])and (constraint[1] == cols[x][0])),cols)
1449
col_id = tcol.write(cr,uid,int(id_to_write[0]),val,context)
1451
temp = pooler.get_pool(cr.dbname).get('olap.fact.database').write(cr,uid,[id_db],{'loaded':True})
1452
wf_service = netsvc.LocalService('workflow')
1453
wf_service.trg_validate(uid,'olap.schema',context['active_id'],'dbload',cr)
1454
model_data_ids = self.pool.get('ir.model.data').search(cr,uid,[('model','=','ir.ui.view'),('name','=','view_olap_schema_form')])
1455
resource_id = self.pool.get('ir.model.data').read(cr,uid,model_data_ids,fields = ['res_id'])[0]['res_id']
1457
return {'type':'ir.actions.act_window_close' }
1460
# 'name': 'view_olap_schema_form',
1461
# 'view_type': 'form',
1462
# 'view_mode': 'form,tree',
1463
# 'res_id': context['active_id'],
1464
# 'res_model': 'olap.schema',
1465
# 'view': [(resource_id,'form')],
1466
# 'type': 'ir.actions.act_window_close'
1470
def action_cancel(self,cr,uid,ids,context = None):
1472
return {'type':'ir.actions.act_window_close' }
1478
# Wizard for the Automatic Application Configuration
1479
# Replacement for the Load Wizard according to the new structure
1480
class bi_auto_configure_wizard(osv.osv_memory):
1481
_name = 'bi.auto.configure.wizard'
1484
def _get_name(self,cr,uid,ctx):
1485
if ctx and ctx.has_key('active_id'):
1486
schema_obj = self.pool.get('olap.schema').browse(cr,uid,ctx['active_id'])
1487
return schema_obj.name
1491
'name':fields.char('Fact Name' ,size = 64,readonly = True),
1499
def action_load(self,cr,uid,ids,context = None):
1502
appfieldnew_vals = {}
1504
ids = pooler.get_pool(cr.dbname).get('olap.schema').browse(cr,uid,context['active_id'])
1506
if ids.app_detect == "Unknown Application":
1507
raise wizard.except_wizard('Warning','The Application is Unknown, we can not configure it automatically.')
1510
app_objs = pooler.get_pool(cr.dbname).get('olap.application')
1511
app_ids = app_objs.search(cr,uid,[])
1512
app_res = app_objs.browse(cr,uid,app_ids)
1514
for x_app in app_res:
1515
app_id = x_app['id']
1517
apptab_objs = pooler.get_pool(cr.dbname).get('olap.application.table')
1518
apptab_ids = apptab_objs.search(cr,uid,[])
1519
apptab_res = apptab_objs.browse(cr,uid,apptab_ids)
1521
map_apptab_name = {}
1522
map_apptab_name_id = {}
1523
for aptab in apptab_res:
1524
apptab_name.append(aptab.name)
1525
map_apptab_name_id[aptab.table_name] = aptab
1527
appfield_objs = pooler.get_pool(cr.dbname).get('olap.application.field')
1528
appfield_ids = appfield_objs.search(cr,uid,[])
1529
appfield_res = appfield_objs.browse(cr,uid,appfield_ids)
1530
appfield_data_res = appfield_objs.browse(cr,uid,appfield_ids)
1532
for apcol in appfield_res:
1533
appcol_name.append(apcol.name)
1535
dbtab_obj = pooler.get_pool(cr.dbname).get('olap.database.tables')
1536
# id_tables=dbtab_obj.search(cr,uid,[('fact_database_id','=',ids.database_id.id),('table_db_name','not in',['inherit','res_roles','user_rule_group_rel','res_roles_users_rel','group_rule_group_rel'])])
1537
id_tables = dbtab_obj.search(cr,uid,[('fact_database_id','=',ids.database_id.id)])
1538
tables_main = dbtab_obj.read(cr,uid,id_tables,context = {'wizard':True})
1539
for tables in tables_main:
1540
end_user_name = {'name':(" ").join(map(lambda x:x.capitalize(),tables['table_db_name'].split("_")))}
1541
table_new = dbtab_obj.write(cr,uid,tables['id'],end_user_name)
1542
if not(tables['table_db_name'].startswith('ir') or tables['table_db_name'].startswith('wkf') or tables['table_db_name'].startswith('res_groups') or tables['table_db_name'].startswith('res_role')) and tables['table_db_name'] not in ['inherit','user_rule_group_rel','group_rule_group_rel']:
1545
if len(apptab_ids) == 0 and (tables['table_db_name'] not in apptab_name):
1546
vals['table_name'] = tables['table_db_name']
1547
vals['name'] = (" ").join(map(lambda x:x.capitalize(),tables['name'].split("_")))
1548
vals['is_hidden'] = tables['hide']
1549
vals['application_id'] = app_id
1550
table_new = dbtab_obj.write(cr,uid,tables['id'],{'hide':False})
1551
apptab_new_obj = apptab_objs.create(cr,uid,vals)
1553
if map_apptab_name_id.has_key(tables['table_db_name']):
1554
app_table = map_apptab_name_id[tables['table_db_name']]
1555
if ((app_table['table_name'] == tables['table_db_name']) and not (app_table['table_name'] == tables['name'])):
1556
vals['name'] = (" ").join(map(lambda x:x.capitalize(),tables['name'].split("_")))
1557
vals['is_hidden'] = tables['hide']
1558
tables_obj_new = apptab_objs.write(cr,uid,app_table['id'],vals)
1560
vals['table_name'] = tables['table_db_name']
1561
vals['name'] = (" ").join(map(lambda x:x.capitalize(),tables['table_db_name'].split("_")))
1562
vals['is_hidden'] = tables['hide']
1563
vals['application_id'] = app_id
1564
apptab_new_obj = apptab_objs.create(cr,uid,vals)
1565
id_columns = pooler.get_pool(cr.dbname).get('olap.database.columns').search(cr,uid,[('table_id','in',id_tables)])
1566
columns = pooler.get_pool(cr.dbname).get('olap.database.columns').read(cr,uid,id_columns,[])
1567
for columns in columns:
1569
if len(appfield_ids) == 0 and (columns['column_db_name'] not in appcol_name):
1570
vals['field_name'] = columns['column_db_name']
1571
vals['table_name'] = columns['table_id'][1]
1572
vals['name'] = (" ").join(map(lambda x:x.capitalize(),columns['name'].split("_")))
1573
vals['is_hidden'] = columns['hide']
1574
vals['application_id'] = x_app['id']
1575
appfield_new_obj = appfield_objs.create(cr,uid,vals)
1577
filter_column = filter(lambda x: columns['column_db_name'] == x['field_name'] and columns['table_id'][1] == x['table_name'],appfield_data_res)
1578
if map_apptab_name_id.has_key(columns['table_id'][1]) and filter_column:
1579
table_id_write = map_apptab_name_id[columns['table_id'][1]]
1580
vals['name'] = (" ").join(map(lambda x:x.capitalize(),columns['name'].split("_")))
1581
vals['is_hidden'] = columns['hide']
1582
appfield_new_obj = appfield_objs.write(cr,uid,filter_column[0]['id'],vals)
1584
vals['field_name'] = columns['column_db_name']
1585
vals['table_name'] = columns['table_id'][1]
1586
vals['name'] = (" ").join(map(lambda x:x.capitalize(),columns['name'].split("_")))
1587
vals['is_hidden'] = columns['hide']
1588
vals['application_id'] = x_app['id']
1589
appfield_new_obj = appfield_objs.create(cr,uid,vals)
1592
database_tables = pooler.get_pool(cr.dbname).get('olap.database.tables')
1593
id_tables = database_tables.search(cr,uid,[('fact_database_id','=',ids.database_id.id)])
1594
tables = database_tables.read(cr,uid,id_tables,[])
1596
for table in tables:
1598
if (table['table_db_name'].startswith('ir') or table['table_db_name'].startswith('wkf')) or (table['table_db_name'].startswith('res_groups')) or (table['table_db_name'] in ['inherit','res_roles','user_rule_group_rel','res_roles_users_rel','group_rule_group_rel']):
1600
vals['active'] = False
1601
make_id.append(table['id'])
1602
database_tables.write(cr,uid,table['id'],vals)
1604
database_columns = pooler.get_pool(cr.dbname).get('olap.database.columns')
1605
id_columns = database_columns.search(cr,uid,[('table_id','in',make_id)])
1606
columns = database_columns.read(cr,uid,id_columns,[])
1610
vals['active'] = False
1611
database_columns.write(cr,uid,col['id'],vals)
1614
wf_service = netsvc.LocalService('workflow')
1615
wf_service.trg_validate(uid,'olap.schema',context['active_id'],'dbconfigure',cr)
1616
model_data_ids = self.pool.get('ir.model.data').search(cr,uid,[('model','=','ir.ui.view'),('name','=','view_olap_schema_form')])
1617
resource_id = self.pool.get('ir.model.data').read(cr,uid,model_data_ids,fields = ['res_id'])[0]['res_id']
1619
return {'type':'ir.actions.act_window_close' }
1621
def action_cancel(self,cr,uid,ids,context = None):
1623
return {'type':'ir.actions.act_window_close' }
1625
bi_auto_configure_wizard()
1628
class olap_warehouse_wizard(osv.osv_memory):
1629
_name = "olap.warehouse.wizard"
1630
_description = "Olap Warehouse"
1632
def _get_queries(self, cr, uid, context = {}):
1633
query_obj = self.pool.get('olap.query.logs')
1634
qry_ids = query_obj.search(cr, uid, [('user_id','=',uid),('count','>=',3)])
1637
for id in query_obj.browse(cr,uid,qry_ids,context):
1641
query = query + '\n'+id.query
1645
def action_ok(self, cr, uid, ids, context = {}):
1646
return {'type':'ir.actions.act_window_close' }
1649
'query': fields.text('Query', readonly=True),
1652
'query': _get_queries,
1654
olap_warehouse_wizard()
1655
class olap_parameters_config_wizard(osv.osv_memory):
1656
_name = "olap.parameters.config.wizard"
1657
_description = "Olap Server Parameters"
1659
def _get_host(self,cr,uid,context = None):
1660
obj = self.pool.get('olap')
1661
objid = self.pool.get('ir.model.data')
1662
aid = objid._get_id(cr,uid,'olap','menu_url_cube_browser')
1663
aid = objid.browse(cr,uid,aid,context = context).res_id
1664
aid = self.pool.get('ir.actions.url').browse(cr,uid,aid,context = context)
1665
s_p = Literal("http://").suppress() + Word(alphanums + "_" + ".") + Literal(":").suppress() + Word(nums) + Literal("/").suppress() + Word(alphanums + "_" + " ").suppress()
1666
return s_p.parseString(aid.url)[0]
1668
def _get_port(self,cr,uid,context = None):
1669
obj = self.pool.get('olap')
1670
objid = self.pool.get('ir.model.data')
1671
aid = objid._get_id(cr,uid,'olap','menu_url_cube_browser')
1672
aid = objid.browse(cr,uid,aid,context = context).res_id
1673
aid = self.pool.get('ir.actions.url').browse(cr,uid,aid,context = context)
1674
s_p = Literal("http://").suppress() + Word(alphanums + "_" + ".") + Literal(":").suppress() + Word(nums) + Literal("/").suppress() + Word(alphanums + "_" + " ").suppress()
1675
return s_p.parseString(aid.url)[1]
1678
'host_name' : fields.char('Server Name',size = 64,help = "Put here the server address or IP \
1679
Put localhost if its not clear.",required = True),
1680
'host_port' : fields.char('Port',size = 4,help = "Put the port for the server. Put 8080 if \
1681
its not clear.",required = True),
1685
'host_name': _get_host,
1686
'host_port': _get_port,
1689
def action_cancel(self,cr,uid,ids,conect = None):
1691
'view_type': 'form',
1692
"view_mode": 'form',
1693
'res_model': 'ir.actions.configuration.wizard',
1694
'type': 'ir.actions.act_window',
1698
def action_config(self,cr,uid,ids,context = None):
1699
conf = self.browse(cr,uid,ids[0],context)
1700
obj = self.pool.get('olap')
1701
objid = self.pool.get('ir.model.data')
1702
aid = objid._get_id(cr,uid,'olap','menu_url_cube_browser')
1703
aid = objid.browse(cr,uid,aid,context = context).res_id
1704
self.pool.get('ir.actions.url').write(cr,uid,[aid],{'url': 'http://' + (conf.host_name or 'localhost') + ':' + (conf.host_port or '8080') + '/browser'})
1706
aid = objid._get_id(cr,uid,'olap','menu_url_cube_designer')
1707
aid = objid.browse(cr,uid,aid,context = context).res_id
1708
self.pool.get('ir.actions.url').write(cr,uid,[aid],{'url': 'http://' + (conf.host_name or 'localhost') + ':' + (conf.host_port or '8080') + '/designer'})
1711
'view_type': 'form',
1712
"view_mode": 'form',
1713
'res_model': 'ir.actions.configuration.wizard',
1714
'type': 'ir.actions.act_window',
1717
olap_parameters_config_wizard()
1719
# vim: ts=4 sts=4 sw=4 si et
added
removed
bias_olap/olap.py
