Module: ArchestryLookup::Sql
- Defined in:
- lib/sql/pre.rb,
lib/sql/dbhandlers.rb,
lib/sql/lookup_mfv.rb,
lib/sql/view2ModelElements.rb
Class Method Summary collapse
- .archestry_model_lookup(args, state) ⇒ Object
- .getFolderViews(args, state, mfv) ⇒ Object
- .getModelFolders(state, args, mfv) ⇒ Object
- .getViewConnectionsFromView(args, state, mfv) ⇒ Object
- .initElementsHash(args, mtype, f) ⇒ Object
- .makeElementsLookups(args, keyType, schema, container, f1, f2, f3) ⇒ Object
-
.makeInternalLookups(args, keyType, valueType, container, id) ⇒ Object
TODO: flags ‘skip’ in props.
- .pgAnswerMap(answer) ⇒ Object
- .postCheck(args, mtype, f1, f2, f3, e, key, sure) ⇒ Object
- .preSql(state) ⇒ Object
- .sqliteAnswerMap(answer) ⇒ Object
- .sqlQuery(state, sql) ⇒ Object
Class Method Details
.archestry_model_lookup(args, state) ⇒ Object
31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 |
# 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) ⇒ Object
52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 |
# 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) ⇒ Object
74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 |
# 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) ⇒ Object
23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 |
# 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) ⇒ Object
17 18 19 20 |
# 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) ⇒ Object
193 194 195 196 197 198 199 200 201 202 203 204 |
# 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) ⇒ Object
TODO: flags ‘skip’ in props
19 20 21 22 23 24 25 26 27 28 |
# 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) ⇒ Object
61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 |
# 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) ⇒ Object
206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 |
# 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) ⇒ Object
18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 |
# 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 |
.sqliteAnswerMap(answer) ⇒ Object
32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 |
# 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 |
.sqlQuery(state, sql) ⇒ Object
16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
# 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 |