# -*- coding: latin-1 -*-
""" 
   Copyright (C) 2001-2003 PimenTech SARL (http://www.pimentech.net)

   This library is free software; you can redistribute it and/or
   modify it under the terms of the GNU Library General Public License as
   published by the Free Software Foundation; either version 2 of the
   License, or (at your option) any later version.

   This library is distributed in the hope that it will be useful,
   but WITHOUT ANY WARRANTY; without even the implied warranty of
   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
   Library General Public License for more details.

   You should have received a copy of the GNU Library General Public
   License along with this library; see the file COPYING.LIB.  If not,
   write to the Free Software Foundation, Inc., 59 Temple Place - Suite 330,
   Boston, MA 02111-1307, USA.  
"""

__version__='$Revision: 1.21 $'[11:-2]

from Products.PimenTechLibCommon.map import *
from Products.PimenTechLibCommon.sqlcommon import *

import sys
from string import *
from cStringIO import StringIO

from Globals import HTMLFile
from Globals import MessageDialog

class CoreTable(Map):
	"the core table handles tables and relations"
	
	meta_type = 'CoreTable'

	try:
		index_html = HTMLFile('dtml/coreTableIndex', globals()) # View Interface
	except:
		pass

	manage_options = ( # what management options are there?
		{'label': 'Edit',       'action': 'manage_main'},
		{'label': 'View',       'action': ''}, # defaults to index_html
		{'label': 'Security',   'action': 'manage_access'},
		) 

	__ac_permissions__=( # what permissions make sense for us? 
		('View management screens', ('manage_tabs','manage_main')),
		('Change permissions',      ('manage_access',)           ),
		('Change Borings'     ,     ('manage_edit',)             ),
		('View Borings',            ('',)                        ),
		)
	
	operatorMap = {
		'equal' : '=',
		'eq' : '=',
		'not-equal' : '!=',
		'ne' : '!=',
		'neq' : '!=',			  
		'less' : '<',
		'greater' : '>',
		'lt' : '<',
		'gt' : '>',
		'less-or-equal' : '<=',
		'greater-or-equal' : '>=',
		'leq' : '<=',
		'geq' : '>=',
		'le' : '<=',
		'ge' : '>=',
		'like' : 'like'
		}
	
	PGVERSION = 72
	
	def __init__(self, id, title = '',
							 idSequence = None, usePostgreSqlIsa = None, useIndices = None):
		Map.__init__(self, id, title)
		self.isa = None
		self.idSequence = idSequence # sequence de l'id (peut etre forcée avec option -i sur pgml2sql)
		self.usePostgreSqlIsa = usePostgreSqlIsa # usage ou non du mot cle postgreSql isa
		self.useIndices = useIndices
		self.conditionMap = Map('conditionMap')
		
	def write_dot(self, output):
		output.write('%s [ shape=box label="%s" ];\n' % (str(self),self._get_dot_label()))

	def write_pgml(self, output):
		output.write("<table name='%s'" % str(self))
		if self.usePostgreSqlIsa and self.isa:
			output.write(" isa='%s'>\n" % str(self.isa))
		else:
			output.write(">\n")
		self._write_attributes_pgml(output)
		output.write('</table>\n')

	def _write_attributes_pgml(self, output):
		for (name, properties) in self.items():
			output.write("<attribute name='%s' type='%s'" % (name,properties['type']))
			if properties['default']:
				output.write(" default='%s'" % properties['default'])
			if properties['references']:
				output.write(" references='%s'" % properties['references'])
			if properties['constraints']:
				output.write(" constraints='%s'" % properties['constraints'])
			output.write("/>\n")
		
	def _get_dot_label(self):
		dot_label = "%s" % str(self)
		if self.items():
			dot_label = '%s\\n__' % dot_label
		for (name, properties) in self.items():
			dot_label = '%s\\n%s : %s' % (dot_label,name,properties['type'])
		return dot_label
		
	def insert_attribute(self, attribute, property_name, property_value):
		if not self[attribute]:
			self[attribute] = PDictMap('%s' % attribute, 'Properties of %s' % attribute)
		self[attribute][property_name] = property_value
		
	def insert_condition(self, attribute, operator, value):
		if not self.conditionMap[attribute]:
			self.conditionMap[attribute] = PDictMap('%s' % attribute, 'Operators of %s' % attribute)
		if not self.conditionMap[attribute][operator]:
			self.conditionMap[attribute][operator] = []
		self.conditionMap[attribute][operator].append(value)

	def insert_unary_condition(self, attribute, operator):
		self.insert_condition(attribute, operator, 1)

	def _write_sql_uid_indexes(self, output):

		if self.useIndices:
			if self.idSequence: # si la sequence de l'id est forcée
				uidName = split(self.idSequence)[0]
				output.write("create index idx_%s_%s on %s (%s);\n" % (str(self), uidName, str(self), uidName))
			else: # sinon id standard
				output.write("create index idx_%s_id_%s on %s (id_%s);\n" % (str(self), str(self), str(self), str(self)))

	def _write_sql_indexes(self, output):

		if self.useIndices:
			isa = self
			while isa:
				for (name, properties) in isa.items():
					if properties['references'] or properties['index']:
						output.write("create index idx_%s_%s on %s (%s);\n" % (str(self), name, str(self), name))
				isa = self.usePostgreSqlIsa and isa.isa
				
	def _write_sql_parent_unique_indexes(self, output):

		isa = self
		while isa:
			for (name, properties) in isa.items():
				if isa == self and properties['type']: continue
				if properties['constraints'] and find(properties['constraints'],'unique')!=-1:
					output.write("create unique index %s_%s_key on %s (%s);\n" % (str(self), name, str(self), name))
			isa = self.usePostgreSqlIsa and isa.isa

	def write_sql(self, output):
		output.write("CREATE TABLE %s (\n" % str(self))
		
		commaFlag = 1 # gere la premiere virgule
		# si la table n'herites pas ou si la table herites et que l'on utilises pas l'isa postgreSql 
		if not self.isa or (self.isa and not self.usePostgreSqlIsa):
			if self.idSequence: # si la sequence de l'id est forcée
				output.write("\t%s" % self.idSequence)
			else: # sinon id standard
				output.write("\tid_%s SERIAL PRIMARY KEY" % str(self))
		else:
			commaFlag = 0
			
		for (name, properties) in self.items():

			if not properties['type']: continue
			
			if commaFlag:
				output.write(",\n")
			else:
				commaFlag = 1
			
			output.write("\t%s %s" % (name,properties['type']))
			if properties['default']:
				output.write(" DEFAULT '%s'" % properties['default'])
			if properties['constraints']:
				output.write(" %s" % properties['constraints'])
				
		output.write("\n)")
		if self.isa and self.usePostgreSqlIsa:
			output.write(" inherits (%s)" % str(self.isa))
		output.write(";\n")

		self._write_sql_parent_unique_indexes(output)
		self._write_sql_uid_indexes(output)
		self._write_sql_indexes(output)

	def get_sql(self):
		stringIO = StringIO()
		self.write_sql(stringIO)
		return stringIO.getvalue()
		
	def write_ssql(self, output):
		nameStruct = str(self)
		output.write("CREATE TABLE struct%s () inherits (structtable);\n" % str(self))
		output.write("insert into structtable (champ,denom,editable,rang,isoid,type,defaut,disp) VALUES ('%s','%s','1','1','t','Config','','');\n" % (str(self),str(self)))
		
		if self.isa and self.usePostgreSqlIsa:
			output.write(" insert into struct%s select * from struct%s;\n" % (str(self),str(self.isa)))

		for (name, properties) in self.items():
			reference='';
			if properties['references']:
				reference = properties['references']
				
			output.write("insert into struct%s (champ,denom,editable,rang,isoid,type,defaut,disp) VALUES ('%s','%s','1','1','t','%s','','%s');\n" % (str(self),name,name,properties['type'],reference))
		
	def write_ldb(self, output):
		output.write("btree %s\n" % str(self))
		
		# si la table n'herites pas ou si la table herites et que l'on utilises pas l'isa postgreSql 
		if not self.isa or (self.isa and not self.usePostgreSqlIsa):
			if self.idSequence: # si la sequence de l'id est forcée
				output.write("key %s\n" % self.idSequence)
			else: # sinon id standard
				output.write("key id_%s\n" % str(self))
			
		for (name, properties) in self.items():
			
			output.write("field %s\n" % name)
			# en fonction du type normalement (type=properties['type'])
			output.write("type nt\n")
			if properties['default']:
				output.write("default %s\n" % properties['default'])
			if properties['references']:
				output.write("joint %s\n" % properties['references'])
				if (find(properties['constraints'], "cascade")!=-1):
					output.write("on_delete cascade\n");

	def get_fields(self):
		l = []
		isa = self
		while isa:
			l = l + isa.keys()
			isa = self.usePostgreSqlIsa and isa.isa
		return l

	def get_field(self, name):
		isa = self
		while isa:
			if isa.has_key(name):
				return isa[name]
			isa = self.usePostgreSqlIsa and isa.isa
		return None

	def get_select_fields(self):
		l = []
		for (name, conditions) in self.conditionMap.items():
			if conditions['view']:
				l.append(name)
		return l

	def get_where_clause(self):
		src_where = ''
		and_tag = 0
		for (name, conditions) in self.conditionMap.items():
			for (operator, values) in conditions.items():
				if self.operatorMap[operator] and values:
					# sys.stderr.write("self.operatorMap[%s]=%s\n" % (operator,self.operatorMap[operator]))
					for value in values:
						if and_tag:
							src_where = '%s and' % src_where
						else:
							and_tag = 1
						src_where = '%s %s%s%s' % (src_where, name, self.operatorMap[operator], sqlFilter(value))
		return src_where

	def get_uid_select(self, uidName = 'uid'):
		"get_uid_select"
		src = 'select %s' % uidName

		for name in self.get_select_fields():
			src = '%s,%s' % (src, name)

		if self.PGVERSION > 71:
			src = '%s from %s' % (src, self.getId())
		else:
			src = '%s from %s*' % (src, self.getId())
			
		src_where = self.get_where_clause()

		if src_where:
			return '%s where%s' % (src, src_where)
		else:
			return src
	
	def get_insert(self):

		src = 'insert into %s (' % self.getId()

		comma_tag = 0
		for (name, conditions) in self.conditionMap.items():
			values = conditions['set'] or conditions['get'] or conditions['equal']
			if values and len(values) == 1:
				if comma_tag:
					src = '%s,' % src
				else:
					comma_tag = 1
				src = '%s%s' % (src, name)
				
		src = '%s) values (' % src

		comma_tag = 0
		for (name, conditions) in self.conditionMap.items():
			values = conditions['set'] or conditions['get'] or conditions['equal']
			if values and len(values) == 1:			
				if comma_tag:
					src = '%s,' % src
				else:
					comma_tag = 1
				src = '%s%s' % (src, sqlFilter(values[0]))
		
		src = '%s)' % src
		return src
	
	def get_update(self):

		src = 'update %s set ' % self.getId()
		comma_tag = 0
		for (name, conditions) in self.conditionMap.items():
			values = conditions['set'] or conditions['get']
			if values and len(values) == 1:
				if comma_tag:
					src = '%s,' % src
				else:
					comma_tag = 1
					
				src = '%s%s=%s' % (src, name, sqlFilter(values[0]))
				
		src_where = self.get_where_clause()

		if src_where:
			return '%s where%s' % (src, src_where)
		else:
			return src		

	def get_table_and_parents(self):
			
		l = []
		isa = self
		while isa:
			l.append(isa)
			isa = isa.isa
		return l