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