+ # ----- putway (user token, way, array of co-ordinates, array of attributes,
+ # baselong, basey, masterscale)
+ # returns current way ID, new way ID, hash of renumbered nodes,
+ # xmin,xmax,ymin,ymax
+
+ def putway(args)
+ usertoken,originalway,points,attributes,baselong,basey,masterscale=args
+ uid=getuserid(usertoken)
+ return if !uid
+ db_uqs='uniq'+uid.to_s+originalway.to_i.abs.to_s+Time.new.to_i.to_s # temp uniquesegments table name, typically 51 chars
+ db_uqn='unin'+uid.to_s+originalway.to_i.abs.to_s+Time.new.to_i.to_s # temp uniquenodes table name, typically 51 chars
+ db_now='@now'+uid.to_s+originalway.to_i.abs.to_s+Time.new.to_i.to_s # 'now' variable name, typically 51 chars
+ ActiveRecord::Base.connection.execute("SET #{db_now}=NOW()")
+ originalway=originalway.to_i
+
+ # -- 3. read original way into memory
+
+ xc={}; yc={}; tagc={}; seg={}
+ if originalway>0
+ way=originalway
+ readwayquery(way).each { |row|
+ id1=row['id1'].to_i; xc[id1]=row['long1'].to_f; yc[id1]=row['lat1'].to_f; tagc[id1]=row['tags1']
+ id2=row['id2'].to_i; xc[id2]=row['long2'].to_f; yc[id2]=row['lat2'].to_f; tagc[id2]=row['tags2']
+ seg[row['segment_id'].to_i]=id1.to_s+'-'+id2.to_s
+ }
+ ActiveRecord::Base.connection.update("UPDATE current_ways SET timestamp=#{db_now},user_id=#{uid},visible=1 WHERE id=#{way}")
+ else
+ way=ActiveRecord::Base.connection.insert("INSERT INTO current_ways (user_id,timestamp,visible) VALUES (#{uid},#{db_now},1)")
+ end
+
+ # -- 4. get version by inserting new row into ways
+
+ version=ActiveRecord::Base.connection.insert("INSERT INTO ways (id,user_id,timestamp,visible) VALUES (#{way},#{uid},#{db_now},1)")
+
+ # -- 5. compare nodes and update xmin,xmax,ymin,ymax
+
+ xmin = ymin = 999999
+ xmax = ymax = -999999
+ insertsql = nodelist = ''
+ renumberednodes={}
+
+ points.each_index do |i|
+ xs=coord2long(points[i][0],masterscale,baselong)
+ ys=coord2lat(points[i][1],masterscale,basey)
+ xmin=[xs,xmin].min; xmax=[xs,xmax].max
+ ymin=[ys,ymin].min; ymax=[ys,ymax].max
+ node=points[i][2].to_i
+ tagstr=array2tag(points[i][4])
+ tagsql="'"+sqlescape(tagstr)+"'"
+
+ # compare node
+ if node<0
+ # new node - create
+ newnode=ActiveRecord::Base.connection.insert("INSERT INTO current_nodes ( latitude,longitude,timestamp,user_id,visible,tags) VALUES ( #{ys},#{xs},#{db_now},#{uid},1,#{tagsql})")
+ ActiveRecord::Base.connection.insert("INSERT INTO nodes (id,latitude,longitude,timestamp,user_id,visible,tags) VALUES (#{newnode},#{ys},#{xs},#{db_now},#{uid},1,#{tagsql})")
+ points[i][2]=newnode
+ renumberednodes[node.to_s]=newnode.to_s
+
+ elsif xc.has_key?(node)
+ # old node from original way - update
+ if (xs!=xc[node] or (ys/0.0000001).round!=(yc[node]/0.0000001).round or tagstr!=tagc[node])
+ ActiveRecord::Base.connection.insert("INSERT INTO nodes (id,latitude,longitude,timestamp,user_id,visible,tags) VALUES (#{node},#{ys},#{xs},#{db_now},#{uid},1,#{tagsql})")
+ ActiveRecord::Base.connection.update("UPDATE current_nodes SET latitude=#{ys},longitude=#{xs},timestamp=#{db_now},user_id=#{uid},tags=#{tagsql},visible=1 WHERE id=#{node}")
+ else
+ if (nodelist!='') then nodelist+=',' end; nodelist+=node.to_s
+ end
+ else
+ # old node, created in another way and now added to this way
+ if (nodelist!='') then nodelist+=',' end; nodelist+=node.to_s
+ end
+
+ end
+
+ if nodelist!='' then
+ ActiveRecord::Base.connection.update("UPDATE current_nodes SET timestamp=#{db_now},user_id=#{uid},visible=1 WHERE id IN (#{nodelist})")
+ end
+
+ # -- 6.i compare segments
+
+ numberedsegments={}
+ seglist=''
+ for i in (0..(points.length-2))
+ if (points[i+1][3].to_i==0) then next end
+ segid=points[i+1][5].to_i
+ from =points[i ][2].to_i
+ to =points[i+1][2].to_i
+ if seg.has_key?(segid)
+ if seg[segid]=="#{from}-#{to}" then
+ if (seglist!='') then seglist+=',' end; seglist+=segid.to_s
+ next
+ end
+ end
+ segid=ActiveRecord::Base.connection.insert("INSERT INTO current_segments ( node_a,node_b,timestamp,user_id,visible,tags) VALUES ( #{from},#{to},#{db_now},#{uid},1,'')")
+ ActiveRecord::Base.connection.insert("INSERT INTO segments (id,node_a,node_b,timestamp,user_id,visible,tags) VALUES (#{segid},#{from},#{to},#{db_now},#{uid},1,'')")
+ points[i+1][5]=segid
+ numberedsegments[(i+1).to_s]=segid.to_s
+ end
+ # numberedsegments.each{|a,b| RAILS_DEFAULT_LOGGER.error("Sending back: seg no. #{a} -> id #{b}") }
+
+ if seglist!='' then
+ ActiveRecord::Base.connection.update("UPDATE current_segments SET timestamp=#{db_now},user_id=#{uid},visible=1 WHERE id IN (#{seglist})")
+ end
+
+
+ # -- 6.ii insert new way segments
+
+ createuniquesegments(way,db_uqs)
+
+ # delete segments from uniquesegments (and not in modified way)
+
+ sql=<<-EOF
+ INSERT INTO segments (id,node_a,node_b,timestamp,user_id,visible)
+ SELECT DISTINCT segment_id,node_a,node_b,#{db_now},#{uid},0
+ FROM current_segments AS cs, #{db_uqs} AS us
+ WHERE cs.id=us.segment_id AND cs.visible=1
+ AND (cs.timestamp!=#{db_now} OR cs.user_id!=#{uid})
+ EOF
+ ActiveRecord::Base.connection.insert(sql)
+
+ sql=<<-EOF
+ UPDATE current_segments AS cs, #{db_uqs} AS us
+ SET cs.timestamp=#{db_now},cs.visible=0,cs.user_id=#{uid}
+ WHERE cs.id=us.segment_id AND cs.visible=1
+ AND (cs.timestamp!=#{db_now} OR cs.user_id!=#{uid})
+ EOF
+ ActiveRecord::Base.connection.update(sql)
+
+ # delete nodes not in modified way or any other segments
+
+ createuniquenodes(db_uqs,db_uqn)
+
+ sql=<<-EOF
+ INSERT INTO nodes (id,latitude,longitude,timestamp,user_id,visible)
+ SELECT DISTINCT cn.id,cn.latitude,cn.longitude,#{db_now},#{uid},0
+ FROM current_nodes AS cn,#{db_uqn}
+ WHERE cn.id=node_id
+ EOF
+ ActiveRecord::Base.connection.insert(sql)
+
+ sql=<<-EOF
+ UPDATE current_nodes AS cn, #{db_uqn}
+ SET cn.timestamp=#{db_now},cn.visible=0,cn.user_id=#{uid}
+ WHERE cn.id=node_id
+ EOF
+ ActiveRecord::Base.connection.update(sql)
+
+ ActiveRecord::Base.connection.execute("DROP TABLE #{db_uqs}")
+ ActiveRecord::Base.connection.execute("DROP TABLE #{db_uqn}")
+
+ # insert new version of route into way_segments
+
+ insertsql =''
+ currentsql=''
+ sequence =1
+ for i in (0..(points.length-2))
+ if (points[i+1][3].to_i==0) then next end
+ if insertsql !='' then insertsql +=',' end
+ if currentsql!='' then currentsql+=',' end
+ insertsql +="(#{way},#{points[i+1][5]},#{version})"
+ currentsql+="(#{way},#{points[i+1][5]},#{sequence})"
+ sequence +=1
+ end
+
+ ActiveRecord::Base.connection.execute("DELETE FROM current_way_segments WHERE id=#{way}");
+ ActiveRecord::Base.connection.insert("INSERT INTO way_segments (id,segment_id,version ) VALUES #{insertsql}");
+ ActiveRecord::Base.connection.insert("INSERT INTO current_way_segments (id,segment_id,sequence_id) VALUES #{currentsql}");
+
+ # -- 7. insert new way tags
+
+ insertsql =''
+ currentsql=''
+ attributes.each do |k,v|
+ if v=='' then next end
+ if v[0,6]=='(type ' then next end
+ if insertsql !='' then insertsql +=',' end
+ if currentsql!='' then currentsql+=',' end
+ insertsql +="(#{way},'"+sqlescape(k)+"','"+sqlescape(v)+"',version)"
+ currentsql+="(#{way},'"+sqlescape(k)+"','"+sqlescape(v)+"')"
+ end
+
+ ActiveRecord::Base.connection.execute("DELETE FROM current_way_tags WHERE id=#{way}")
+ if (insertsql !='') then ActiveRecord::Base.connection.insert("INSERT INTO way_tags (id,k,v,version) VALUES #{insertsql}" ) end
+ if (currentsql!='') then ActiveRecord::Base.connection.insert("INSERT INTO current_way_tags (id,k,v) VALUES #{currentsql}") end
+
+ [originalway,way,renumberednodes,numberedsegments,xmin,xmax,ymin,ymax]
+ end
+
+ # ----- deleteway (user token, way)
+ # returns way ID only
+
+ def deleteway(args)
+ usertoken,way=args
+ uid=getuserid(usertoken); if !uid then return end
+ way=way.to_i
+
+ db_uqs='uniq'+uid.to_s+way.to_i.abs.to_s+Time.new.to_i.to_s # temp uniquesegments table name, typically 51 chars
+ db_uqn='unin'+uid.to_s+way.to_i.abs.to_s+Time.new.to_i.to_s # temp uniquenodes table name, typically 51 chars
+ db_now='@now'+uid.to_s+way.to_i.abs.to_s+Time.new.to_i.to_s # 'now' variable name, typically 51 chars
+ ActiveRecord::Base.connection.execute("SET #{db_now}=NOW()")
+ createuniquesegments(way,db_uqs)
+
+ # - delete any otherwise unused segments
+
+ sql=<<-EOF
+ INSERT INTO segments (id,node_a,node_b,timestamp,user_id,visible)
+ SELECT DISTINCT segment_id,node_a,node_b,#{db_now},#{uid},0
+ FROM current_segments AS cs, #{db_uqs} AS us
+ WHERE cs.id=us.segment_id
+ EOF
+ ActiveRecord::Base.connection.insert(sql)
+
+ sql=<<-EOF
+ UPDATE current_segments AS cs, #{db_uqs} AS us
+ SET cs.timestamp=#{db_now},cs.visible=0,cs.user_id=#{uid}
+ WHERE cs.id=us.segment_id
+ EOF
+ ActiveRecord::Base.connection.update(sql)
+
+ # - delete any unused nodes
+
+ createuniquenodes(db_uqs,db_uqn)
+
+ sql=<<-EOF
+ INSERT INTO nodes (id,latitude,longitude,timestamp,user_id,visible)
+ SELECT DISTINCT cn.id,cn.latitude,cn.longitude,#{db_now},#{uid},0
+ FROM current_nodes AS cn,#{db_uqn}
+ WHERE cn.id=node_id
+ EOF
+ ActiveRecord::Base.connection.insert(sql)
+
+ sql=<<-EOF
+ UPDATE current_nodes AS cn, #{db_uqn}
+ SET cn.timestamp=#{db_now},cn.visible=0,cn.user_id=#{uid}
+ WHERE cn.id=node_id
+ EOF
+ ActiveRecord::Base.connection.update(sql)
+
+ ActiveRecord::Base.connection.execute("DROP TABLE #{db_uqs}")
+ ActiveRecord::Base.connection.execute("DROP TABLE #{db_uqn}")
+
+ # - delete way
+
+ ActiveRecord::Base.connection.insert("INSERT INTO ways (id,user_id,timestamp,visible) VALUES (#{way},#{uid},#{db_now},0)")
+ ActiveRecord::Base.connection.update("UPDATE current_ways SET user_id=#{uid},timestamp=#{db_now},visible=0 WHERE id=#{way}")
+ ActiveRecord::Base.connection.execute("DELETE FROM current_way_segments WHERE id=#{way}")
+ ActiveRecord::Base.connection.execute("DELETE FROM current_way_tags WHERE id=#{way}")
+
+ way
+end
+
+# ====================================================================
+# Support functions for remote calls
+
+def readwayquery(id)
+ ActiveRecord::Base.connection.select_all "SELECT n1.latitude AS lat1,n1.longitude AS long1,n1.id AS id1,n1.tags as tags1, "+
+ " n2.latitude AS lat2,n2.longitude AS long2,n2.id AS id2,n2.tags as tags2,segment_id "+
+ " FROM current_way_segments,current_segments,current_nodes AS n1,current_nodes AS n2 "+
+ " WHERE current_way_segments.id=#{id} "+
+ " AND segment_id=current_segments.id "+
+ " AND current_segments.visible=1 "+
+ " AND n1.id=node_a and n2.id=node_b "+
+ " AND n1.visible=1 AND n2.visible=1 "+
+ " ORDER BY sequence_id"
+end
+
+def createuniquesegments(way,uqs_name)
+ # Finds segments which appear in this way and no other
+ sql=<<-EOF
+ CREATE TEMPORARY TABLE #{uqs_name}
+ SELECT a.segment_id
+ FROM (SELECT DISTINCT segment_id FROM current_way_segments
+ WHERE id = #{way}) a
+ LEFT JOIN current_way_segments b
+ ON b.segment_id = a.segment_id
+ AND b.id != #{way}
+ WHERE b.segment_id IS NULL
+ EOF
+ ActiveRecord::Base.connection.execute(sql)
+end
+
+def createuniquenodes(uqs_name,uqn_name)
+ # Finds nodes which appear in uniquesegments but no other segments
+ sql=<<-EOF
+ CREATE TEMPORARY TABLE #{uqn_name}
+ SELECT DISTINCT node_id
+ FROM (SELECT cn.id AS node_id
+ FROM current_nodes AS cn,
+ current_segments AS cs,
+ #{uqs_name} AS us
+ WHERE cs.id=us.segment_id
+ AND (cn.id=cs.node_a OR cn.id=cs.node_b)) AS n
+ LEFT JOIN current_segments AS cs2 ON node_id=cs2.node_a AND cs2.visible=1
+ LEFT JOIN current_segments AS cs3 ON node_id=cs3.node_b AND cs3.visible=1
+ WHERE cs2.node_a IS NULL
+ AND cs3.node_b IS NULL
+ EOF
+ ActiveRecord::Base.connection.execute(sql)
+end
+
+def sqlescape(a)
+ a.gsub("'","''").gsub(92.chr,92.chr+92.chr)
+end
+
+def tag2array(a)
+ tags={}
+ a.gsub(';;;','#%').split(';').each do |b|
+ b.gsub!('#%',';;;')
+ b.gsub!('===','#%')
+ k,v=b.split('=')
+ if k.nil? then k='' end
+ if v.nil? then v='' end
+ tags[k.gsub('#%','=')]=v.gsub('#%','=')
+ end
+ tags
+end
+
+def array2tag(a)
+ str=''
+ a.each do |k,v|
+ if v=='' then next end
+ if v[0,6]=='(type ' then next end
+ if str!='' then str+=';' end
+ str+=k.gsub(';',';;;').gsub('=','===')+'='+v.gsub(';',';;;').gsub('=','===')
+ end
+ str
+end
+
+def getuserid(token)
+ token=sqlescape(token)
+ if (token=~/^(.+)\+(.+)$/) then
+ return ActiveRecord::Base.connection.select_value("SELECT id FROM users WHERE active=1 AND email='#{$1}' AND pass_crypt=MD5('#{$2}')")
+ else
+ return ActiveRecord::Base.connection.select_value("SELECT id FROM users WHERE active=1 AND token='#{token}'")
+ end
+end
projects / rails.git / blobdiff