module ArchestryLookup::Sql

Public Class Methods

archestry_model_lookup(args, state) click to toggle source
# File lib/sql/lookup_mfv.rb, line 31
def archestry_model_lookup(args, state)
  mfv = {}

  
  sqlQuery(state, "SELECT m1.id as id, m1.name as name, m1.version as ver, m1.created_by as cby, m1.created_on as con FROM models as m1 WHERE version = (select max(m2.version) FROM models as m2 WHERE m1.id = m2.id)").each do |id, model|
    sqlQuery(state, "SELECT p1.name, p1.value FROM properties as p1 WHERE p1.parent_id LIKE '#{model[:id]}' and p1.name LIKE 'modelType' and p1.parent_version = #{model[:ver]}").each do |id, type|
      model[:type] = type[:value].to_sym
      
      makeInternalLookups(args, :models, :models, model, model[:id])
      args[:elements][:base][model[:type]][:id] ||= {}
      
      if args[:models][:models][:id][model[:id]][:type] == :library or args[:models][:models][:id][model[:id]][:name] == state[:funcFolder]
        ArchestryLookup.logger.info "Use model '#{model[:name]}' version #{model[:ver]} type '#{model[:type]}' created by '#{model[:cby]}' on #{model[:con]}"
        mfv[:model] = model
        getModelFolders(state, args, mfv)
      end
    end
  end
end
getFolderViews(args, state, mfv) click to toggle source
# File lib/sql/lookup_mfv.rb, line 52
      def getFolderViews(args, state, mfv)
        a = sqlQuery(state, "select view_id as id, model_version as mver FROM views_in_model as vm1
                      WHERE vm1.parent_folder_id in ('#{mfv[:folder][:id]}') 
                      and vm1.model_version = (select max(vm2.model_version) FROM views_in_model as vm2 WHERE vm2.view_id in (vm1.view_id))").each do |k, viewInM|
          
          viewAtr = sqlQuery(state, "select v1.id, v1.version as ver, v1.name, v1.viewpoint, v1.created_by as cby, v1.created_on as con FROM views as v1 WHERE id in ('#{viewInM[:id]}') and version = (select max(version) FROM views as v2 WHERE v2.id in ('#{viewInM[:id]}'))")

          view = viewAtr[0]
#           view[:tid]= view[:id].tr("-", "_")
          
          makeInternalLookups(args, :views, :models,  mfv[:model],  view[:id])
          makeInternalLookups(args, :views, :folders, mfv[:folder], view[:id])          
          makeInternalLookups(args, :views, :views,   view,         view[:id])
          
          mfv[:view] = view
          view[:viewpoint] == '' ? viewpoint = 'none' : viewpoint = view[:viewpoint]
          ArchestryLookup.logger.info "Model '#{mfv[:model][:name]}' of type '#{mfv[:model][:type]}': view '#{view[:name]}' (id #{view[:id]}, view ver.#{view[:ver]}, model ver.#{mfv[:model][:ver]}) with viewpoint '#{viewpoint}' suits for me!"
          getViewConnectionsFromView(args, state, mfv)    
        end
      end
getModelFolders(state, args, mfv) click to toggle source
# File lib/sql/lookup_mfv.rb, line 74
      def getModelFolders(state, args, mfv)
        
        args[:folders] ||={}
        
        foldersInModel  = sqlQuery(state, "select fm1.folder_id as id,
                                    fm1.parent_folder_id as pid,
                                    f1.name as name,
                                    f1.type as type,
                                    f1.version as ver,                                  
                                    f1.root_type as rtype,
                                    f1.created_by as cby,
                                    f1.created_on as con
                                    FROM folders_in_model as fm1 
                                    INNER JOIN folders as f1 ON f1.id = fm1.folder_id and f1.version = (select max(f2.version) FROM folders as f2 WHERE f2.id = fm1.folder_id) 
                                    WHERE fm1.model_id in ('#{mfv[:model][:id]}')
                                    and fm1.model_version = (select max(fm2.model_version) FROM folders_in_model as fm2 WHERE fm2.model_id LIKE '#{mfv[:model][:id]}')
                                  ")
        
        foldersInModel.each do |id, folder|
          if folder[:rtype].to_i == 7 and folder[:type].to_i == 0
            
            makeInternalLookups(args, :folders, :models,  mfv[:model], folder[:id])
            makeInternalLookups(args, :folders, :folders, folder,      folder[:id])
            
            mfv[:folder] = folder
            getFolderViews(args, state, mfv)
#             mod = :view
          else
            true
#             p "This is Archestry core view '#{folder[:name]}' (type #{folder[:type]}, Root type #{folder[:rtype]})"
#             mod = :objfolder
          end
                              
        end

      end
getViewConnectionsFromView(args, state, mfv) click to toggle source
# File lib/sql/view2ModelElements.rb, line 23
      def getViewConnectionsFromView(args, state, mfv)
        
        rows  = {}
        links = {}
        i = 1
        mtype = mfv[:model][:type]
        
  #  (views_objects.id in (views_connections.source_object_id) AND views_objects.id not in (views_connections.target_object_id))
        sqlQuery(state,  "
          select distinct
          
          views_objects.id as o_oid,
          views_objects.version as o_ver,
          views_objects.created_by as o_cby, 
          views_objects.created_on as o_con, 

          elements.class as e_class,
          elements.name  as e_name, 
          elements.documentation as e_doc,
          elements.version as e_ver,           
          elements.created_by as e_cby, 
          elements.created_on as e_con, 


          relationships.id    as r_id, 
          relationships.version  as r_ver,                
          relationships.class as r_class, 
          relationships.name  as r_name,                 
          relationships.source_id  as s_eid, 
          relationships.target_id  as t_eid,
          relationships.created_by  as r_cby,
          relationships.created_on  as r_con,

                
          views_connections.source_object_id as s_oid, 
          views_connections.target_object_id as t_oid,
               
                
          properties.name  as prop_name,                
          properties.value as prop_value
                                        
          from views_connections_in_view
          INNER JOIN views_connections ON views_connections_in_view.connection_id = views_connections.id
                                  AND views_connections.version = (select max(vc1.version) from views_connections as vc1 where vc1.id = views_connections_in_view.connection_id )
          INNER JOIN relationships ON relationships.id = views_connections.relationship_id 

          INNER JOIN views_objects ON (views_objects.id = views_connections.source_object_id OR  
                                    views_objects.id = views_connections.target_object_id) AND  

                                    views_objects.version = (select max(vo1.version) from views_objects as vo1 where (
                                    (vo1.id in (views_connections.target_object_id) AND vo1.id not in (views_connections.source_object_id))
                                    ))
                                
          INNER JOIN elements ON elements.id = views_objects.element_id AND elements.version = (select max(e1.version) from elements as e1 where e1.id in (views_objects.element_id))
          LEFT JOIN properties ON properties.parent_id = elements.id AND  
                                      properties.parent_version = (select max(p1.parent_version) from properties as p1 where p1.parent_id in (elements.id)) 
                                      #{state[:db][:propSQL]}                                      
        
          where views_connections_in_view.view_id like '#{mfv[:view][:id]}' 
          and views_connections_in_view.view_version = (select max(vciv1.view_version) from views_connections_in_view as vciv1 where vciv1.view_id like '#{mfv[:view][:id]}')
          #{state[:db][:relSQL]}
          #{state[:db][:elementsSQL]}
          and elements.version = (select max(e2.version) from elements as e2 where e2.id in (views_objects.element_id))

        ").each  do |rowID, row|
          
          #unpack("H8H4H4H4H12").join('-')
          row[:m_id]   = mfv[:model][:id]
          row[:m_name] = mfv[:model][:name]
          row[:m_ver] = mfv[:model][:ver]          
          row[:m_type] = mtype
          
          row[:f_id] = mfv[:folder][:id]
          row[:f_name] = mfv[:folder][:name]
          row[:f_ver] = mfv[:folder][:ver]
          
          row[:v_id] = mfv[:view][:id]
          row[:v_name] = mfv[:view][:name]
          row[:v_ver] = mfv[:view][:ver]
          row[:v_point] = mfv[:view][:viewpoint]
          
          
         case row[:o_oid]
          when row[:s_oid]
            f12 = {:self => :s, :d => 12, :f1 => row[:s_eid], :f2 => row[:t_eid], :f3 => row[:s_oid], :myOID => row[:s_oid], :pairOID => row[:t_oid]}
            f21 = {:self => :t, :d => 21, :f1 => row[:t_eid], :f2 => row[:s_eid], :f3 => row[:t_oid], :myOID => row[:s_oid], :pairOID => row[:t_oid]}
       
          when row[:t_oid]
            f12 = {:self => :s, :d => 12, :f1 => row[:t_eid], :f2 => row[:s_eid], :f3 => row[:t_oid], :myOID => row[:t_oid], :pairOID => row[:s_oid]}
            f21 = {:self => :t, :d => 21, :f1 => row[:s_eid], :f2 => row[:t_eid], :f3 => row[:s_oid], :myOID => row[:t_oid], :pairOID => row[:s_oid]}        
          end  
                    

          initElementsHash(args, mtype, f12)
          initElementsHash(args, mtype, f21)  
          
          args[:elements][:base][mtype][:oid] ||= {}
          args[:elements][:base][mtype][:oid][12] ||= {}
          args[:elements][:base][mtype][:oid][21] ||= {}
          args[:elements][:base][mtype][:oid][12][f12[:myOID]] = {:f1 => f12[:f1], :f2 => f12[:f2], :f3 => f12[:f3]}
          args[:elements][:base][mtype][:oid][21][f21[:myOID]] = {:f1 => f21[:f1], :f2 => f21[:f2], :f3 => f21[:f3]}
          
          args[:elements][:base][mtype][:id][f12[:f1]][f12[:f2]][f12[:f3]][f12[:self]][:myOID] = f12[:myOID]
          args[:elements][:base][mtype][:id][f21[:f1]][f21[:f2]][f21[:f3]][f21[:self]][:myOID] = f21[:myOID]
          
          args[:elements][:base][mtype][:id][f12[:f1]][f12[:f2]][f12[:f3]][f12[:self]][:pairOID] = f12[:pairOID]
          args[:elements][:base][mtype][:id][f21[:f1]][f21[:f2]][f21[:f3]][f21[:self]][:pairOID] = f21[:pairOID]    
                

#           args[:elements][:base][mtype][:id][f12[:f1]][f12[:f2]][f12[:f3]][f12[:self]][:direction] = f12[:d]
#           args[:elements][:base][mtype][:id][f21[:f1]][f21[:f2]][f21[:f3]][f21[:self]][:direction] = f21[:d]

          row.each do |k, v|
            key = k.to_s.split('_')
            key[1] =~ /id$/ ? key = "#{key[0]}#{key[1].upcase}".to_sym : key = "#{key[0]}#{key[1].capitalize}".to_sym
                        
            case key
            when :propName
              unless row[:prop_name].nil?  
                args[:elements][:base][mtype][:id][f12[:f1]][f12[:f2]][f12[:f3]][f12[:self]][:props] ||= {}
                args[:elements][:base][mtype][:id][f12[:f1]][f12[:f2]][f12[:f3]][f12[:self]][:props][row[:prop_name]] = row[:prop_value] 
              
                args[:elements][:base][mtype][:id][f21[:f1]][f21[:f2]][f21[:f3]][f21[:self]][:props] ||= {}
                args[:elements][:base][mtype][:id][f21[:f1]][f21[:f2]][f21[:f3]][f21[:self]][:props][row[:prop_name]] = row[:prop_value]                
              else
                args[:elements][:base][mtype][:id][f12[:f1]][f12[:f2]][f12[:f3]][f12[:self]][:props] ||= nil
                args[:elements][:base][mtype][:id][f21[:f1]][f21[:f2]][f21[:f3]][f21[:self]][:props] ||= nil
              end          
            when :propValue
              next
            when :rClass, :mID, :mName, :mVer, :mType, :fID, :fName, :fVer, :vID, :vName, :vVer, :vPoint, :rID, :rCby, :rVer
              args[:elements][:base][mtype][:id][f12[:f1]][f12[:f2]][f12[:f3]][:c][key] = v
              args[:elements][:base][mtype][:id][f21[:f1]][f21[:f2]][f21[:f3]][:c][key] = v
            when :rCon
              epoh = Time.parse(v).to_i              
              args[:elements][:base][mtype][:id][f12[:f1]][f12[:f2]][f12[:f3]][:c][key] = epoh
              args[:elements][:base][mtype][:id][f21[:f1]][f21[:f2]][f21[:f3]][:c][key] = epoh           
            when :eClass, :eName, :oCby, :oVer, :eVer
              args[:elements][:base][mtype][:id][f12[:f1]][f12[:f2]][f12[:f3]][f12[:self]][key] = v
              args[:elements][:base][mtype][:id][f21[:f1]][f21[:f2]][f21[:f3]][f21[:self]][key] = v
            when :oCon
              epoh = Time.parse(v).to_i
              args[:elements][:base][mtype][:id][f12[:f1]][f12[:f2]][f12[:f3]][f12[:self]][key] = epoh
              args[:elements][:base][mtype][:id][f21[:f1]][f21[:f2]][f21[:f3]][f21[:self]][key] = epoh          
            when :sOID, :tOID
              true
            else
#               pp "#{key} - #{v}"
              true
            end
          end

          ArchestryLookup.logger.debug "### ROW ##{i}: #{row}" 
          i = i + 1
        end    
                              
        i = 1
        schema = ArchestryLookup::Schema.getElementsSchema
        args[:elements][:base][mtype][:id].each do |f1, f1Params|
          f1Params.each do |f2, f2Params|
            f2Params.clone.each do |f3, e| 
              postCheck(args, mtype, f1, f2, f3, e, :s, :t)
              postCheck(args, mtype, f1, f2, f3, e, :t, :s)
              makeElementsLookups(args, :elements, schema, e, f1, f2, f3)
            end
          end
        end
                           
      end
initElementsHash(args, mtype, f) click to toggle source
# File lib/sql/view2ModelElements.rb, line 17
def initElementsHash(args, mtype, f)
  args[:elements][:base][mtype][:id][f[:f1]][f[:f2]][f[:f3]][:c] ||= {}
  args[:elements][:base][mtype][:id][f[:f1]][f[:f2]][f[:f3]][f[:self]] ||= {}
end
makeElementsLookups(args, keyType, schema, container, f1, f2, f3) click to toggle source
# File lib/sql/view2ModelElements.rb, line 193
      def makeElementsLookups(args, keyType, schema, container, f1, f2, f3)
        schema[keyType][:schema].each do |k|
#           p "#{k} :: #{container[:c][k]}"
          args[keyType][k][container[:c][k]][f1][f2][f3] ||= {}
        end
        schema[keyType][:schemaDir].each do |k|
          args[keyType][k] ||= {}
          [:s, :t].each do |mod|
            args[keyType][k][container[mod][k]][f1][f2][f3] ||= {}
          end
        end        
      end
makeInternalLookups(args, keyType, valueType, container, id) click to toggle source

TODO: flags 'skip' in props

# File lib/sql/lookup_mfv.rb, line 19
def makeInternalLookups(args, keyType, valueType, container, id)
  args[keyType][valueType] ||= {}
  args[keyType][valueType][:id] ||= {}
  args[keyType][valueType][:id][id] = container
  args[valueType][:schema].each do |k|      
    args[keyType][valueType][k] ||= {}
    args[keyType][valueType][k][container[k]] ||= []
    args[keyType][valueType][k][container[k]] << id
  end
end
pgAnswerMap(answer) click to toggle source
# File lib/sql/dbhandlers.rb, line 61
      def pgAnswerMap(answer)
        res = {}
        m = {}
        i = 0
        answer.each do |result|
#           puts "##values::#{result}"
          result.each do |k, v|
            res[i] ||= {}
            res[i][k.to_sym] = v
          end
          i += 1          
        end   
        
        res
      end
postCheck(args, mtype, f1, f2, f3, e, key, sure) click to toggle source
# File lib/sql/view2ModelElements.rb, line 206
      def postCheck(args, mtype, f1, f2, f3, e, key, sure)
        unless e.has_key?(key)

          fPairOID12 = args[:elements][:base][mtype][:oid][12][e[sure][:pairOID]]
          fPairOID21 = args[:elements][:base][mtype][:oid][21][e[sure][:pairOID]]
          
          is_lookup = true if args[:elements][:base][mtype][:id].has_key?(fPairOID12[:f1]) and args[:elements][:base][mtype][:id][fPairOID12[:f1]].has_key?(fPairOID12[:f2]) and args[:elements][:base][mtype][:id][fPairOID12[:f1]][fPairOID12[:f2]].has_key?(fPairOID12[:f3]) and args[:elements][:base][mtype][:id][fPairOID12[:f1]][fPairOID12[:f2]][fPairOID12[:f3]].has_key?(key)
#           lookup21 = args[:elements][:base][mtype][:id][fPairOID21[:f1]][fPairOID21[:f2]][fPairOID21[:f3]]
          
          if is_lookup == true
            args[:elements][:base][mtype][:id][f1][f2][f3][key]  = args[:elements][:base][mtype][:id][fPairOID12[:f1]][fPairOID12[:f2]][fPairOID12[:f3]][key]
          else
            p "#{f1} - #{f2} - #{f3}"            
            puts "## !!! No key ':#{key}' in view '#{e[:c][:vName]}': probably fail in link '#{e[:c][:rClass]}' of element '#{e[sure][:eName]}' of class #{e[sure][:eClass]}'! \n## Remove all unused elements & relations, recreate obj links and try again."
            exit 1
          end
        end
      end
preSql(state) click to toggle source
# File lib/sql/pre.rb, line 18
def preSql(state)
  state[:db] = {}

  case state[:dbtype]
  when :sqlite
    require 'sqlite3'
    db = state[:dbprop][:filepath]
    state[:db][:handler] = SQLite3::Database.new db
  when :pg
    require 'pg'
    state[:db][:handler] = PG.connect( dbname: state[:dbprop][:database])
  else
    state[:dbtype] = :sqlite
    require 'sqlite3'
    db = "/tmp/archestry.sqlite"
    state[:db][:handler] = SQLite3::Database.new db          
  end
    
  case state[:myAppName]
  when 'Dialog'
    state[:db][:relSQL] = "and relationships.class in ('AggregationRelationship', 'TriggeringRelationship', 'FlowRelationship', 'AccessRelationship', 'SpecializationRelationship', 'CompositionRelationship', 'ServingRelationship', 'RealizationRelationship')"
    state[:db][:elementsSQL] = "and (elements.class LIKE 'Application%' OR elements.class in ('DataObject', 'Grouping'))"
    state[:db][:propSQL] = "AND properties.name LIKE 'fqn'"
  when 'Archestry'
    state[:db][:relSQL] = " "
    state[:db][:elementsSQL] = ""
    state[:db][:propSQL] = ""
  end
  
end
sqlQuery(state, sql) click to toggle source
# File lib/sql/dbhandlers.rb, line 16
      def sqlQuery(state, sql)
        
#         p "##SQL:: #{sql}"
        res = {}

        case state[:dbtype]
        when :sqlite
          res = sqliteAnswerMap(state[:db][:handler].execute2(sql))
        when :pg
          res = pgAnswerMap(state[:db][:handler].exec(sql))
        end
          
#         p "## SQL_RES:: #{res}"
        res        
      end
sqliteAnswerMap(answer) click to toggle source
# File lib/sql/dbhandlers.rb, line 32
      def sqliteAnswerMap(answer)
        res = {}
        m = {}
        n = 0
        j = 0
        answer.each do |result|
          if n == 0
#             puts "##keys::#{result}"
            i = 0
            result.each do |v|
              m[i] = v.to_sym
              i += 1
            end 
          else
#             puts  "##values::#{result}"
            i = 0
            result.each do |v|
              res[j] ||= {}
              res[j][m[i]] = v
              i += 1               
            end 
            j += 1            
          end
          n += 1
        end   
        
        res
      end