<br><br><div><span class="gmail_quote">On 6/23/07, <b class="gmail_sendername">SteveC</b> <<a href="mailto:steve@asklater.com">steve@asklater.com</a>> wrote:</span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<div style=""><br><div><div><span class="q" id="q_11358aa8f5b75550_1"><div>On 23 Jun 2007, at 12:02, Tom Hughes wrote:</div><br><blockquote type="cite"><div style="margin: 0px;">In message <<a href="mailto:233e28ed4e.tom@loxley.compton.nu" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">
233e28ed4e.tom@loxley.compton.nu</a>></div><div style="margin: 0px;"><span> </span>Tom Hughes <<a href="mailto:tom@compton.nu" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">tom@compton.nu
</a>> wrote:</div><div style="margin: 0px; min-height: 14px;"><br></div> <blockquote type="cite"><div style="margin: 0px;">I now have a migration script which produces a schema that more or</div><div style="margin: 0px;">
less matches the create_database.sql script in the repository.</div><div style="margin: 0px; min-height: 14px;"><br></div><div style="margin: 0px;">The only place where it doesn't match is two columns which have been</div>
<div style="margin: 0px;">declared with an unsigned qualifier in the SQL script, and I propose</div><div style="margin: 0px;">that we drop the qualifier from those - in at last one case other</div><div style="margin: 0px;">
columns that are logically the same type don't have the qualifier</div><div style="margin: 0px;">anyway.</div><div style="margin: 0px; min-height: 14px;"><br></div><div style="margin: 0px;">I would therefore like to propose a plan of action:
</div><div style="margin: 0px; min-height: 14px;"><br></div><div style="margin: 0px;"><span> </span>1. Somebody dumps the actual schema from the live database so I can</div><div style="margin: 0px;"><span> </span>make sure it matches create_database.sql and correct anything in
</div><div style="margin: 0px;"><span> </span>my migration script that doesn't quite match the live schema.</div><div style="margin: 0px; min-height: 14px;"><br></div><div style="margin: 0px;"><span> </span>2. Commit my fixed migration script and run an SQL script provided
</div><div style="margin: 0px;"><span> </span>by me to drop those unsigned qualifiers from the live database</div><div style="margin: 0px;"><span> </span>so that the two are fully in sync.</div> </blockquote><div style="margin: 0px; min-height: 14px;">
<br></div><div style="margin: 0px;">These first two steps are now complete, so the migration script in</div><div style="margin: 0px;">the repository now produces a databases which matches the live one.</div><div style="margin: 0px; min-height: 14px;">
<br></div> <blockquote type="cite"><div style="margin: 0px;"><span> </span>3. I produce a new migration script to migrate the database to a</div><div style="margin: 0px;"><span> </span>new schema version that fixes the missing unique indexes and
</div><div style="margin: 0px;"><span> </span>any other oddidities in the current schema.</div> </blockquote><div style="margin: 0px; min-height: 14px;"><br></div><div style="margin: 0px;">I've had a look through the schema and have a series of proposals to
</div><div style="margin: 0px;">clean things up, which (on a table by table basis) look like:</div><div style="margin: 0px; min-height: 14px;"><br></div><div style="margin: 0px;"><span> </span>current_nodes</div><div style="margin: 0px; min-height: 14px;">
<br></div><div style="margin: 0px;"><span> </span>- Make the index on id a primary key, thus guaranteeing uniqueness.</div><div style="margin: 0px; min-height: 14px;"><br></div><div style="margin: 0px;"><span> </span>
- Split the current index on lat+lon into two separate indexes</div><div style="margin: 0px;"><span> </span>for each axis - as it stands the lon component of the index can</div><div style="margin: 0px;"><span> </span>
only be used when exactly one latitude has been selected (more</div><div style="margin: 0px;"><span> </span>or less impossible as it is floating point) so is never used. With</div><div style="margin: 0px;"><span>
</span>separate indexes the database will be able to decide which is most</div><div style="margin: 0px;"><span> </span>efficient for each query and the keys will be smaller so there</div><div style="margin: 0px;"><span>
</span>will be less I/O for whichever index it uses.</div><div style="margin: 0px; min-height: 14px;"><br></div><div style="margin: 0px;"><span> </span>current_segments</div><div style="margin: 0px; min-height: 14px;">
<br></div><div style="margin: 0px;"><span> </span>- Make the index on id a primary key, thus guaranteeing uniqueness.</div><div style="margin: 0px; min-height: 14px;"><br></div><div style="margin: 0px;"><span> </span>
- Drop the visible component from the id index as it is pointless</div><div style="margin: 0px;"><span> </span>if the id component is unique anyway. If an index to help find</div><div style="margin: 0px;"><span>
</span>only visible components is needed then visible should come first.</div><div style="margin: 0px; min-height: 14px;"><br></div><div style="margin: 0px;"><span> </span>current_way_segments</div><div style="margin: 0px; min-height: 14px;">
<br></div><div style="margin: 0px;"><span> </span>- Make segment_id bigint(64) to match other tables.</div><div style="margin: 0px; min-height: 14px;"><br></div><div style="margin: 0px;"><span> </span>- Make the index on id a primary key by adding sequence_id.
</div><div style="margin: 0px; min-height: 14px;"><br></div><div style="margin: 0px;"><span> </span>current_way_tags</div><div style="margin: 0px; min-height: 14px;"><br></div><div style="margin: 0px;"><span> </span>- No changes proposed.
</div><div style="margin: 0px; min-height: 14px;"><br></div><div style="margin: 0px;"><span> </span>current_ways</div><div style="margin: 0px; min-height: 14px;"><br></div><div style="margin: 0px;"><span> </span>- No changes proposed.
</div><div style="margin: 0px; min-height: 14px;"><br></div><div style="margin: 0px;"><span> </span>friends</div><div style="margin: 0px; min-height: 14px;"><br></div><div style="margin: 0px;"><span> </span>- Add index on user_id so that a user's friends can be found
</div><div style="margin: 0px;"><span> </span>efficiently.</div><div style="margin: 0px; min-height: 14px;"><br></div><div style="margin: 0px;"><span> </span>gps_points</div><div style="margin: 0px; min-height: 14px;">
<br></div><div style="margin: 0px;"><span> </span>- Make gpx_id bigint(64) to match other tables.</div><div style="margin: 0px; min-height: 14px;"><br></div><div style="margin: 0px;"><span> </span>- Drop user_id column and index as this information can be
</div><div style="margin: 0px;"><span> </span>found by joining to gpx_files and recording the user_id for</div><div style="margin: 0px;"><span> </span>every point seems pointless.</div><div style="margin: 0px; min-height: 14px;">
<br></div><div style="margin: 0px;"><span> </span>- Split lat/lon index into two separate indexes, dropping the</div><div style="margin: 0px;"><span> </span>user_id component - reasons for this are the same as for nodes.
</div><div style="margin: 0px; min-height: 14px;"><br></div><div style="margin: 0px;"><span> </span>gpx_gile_tags</div><div style="margin: 0px; min-height: 14px;"><br></div><div style="margin: 0px;"><span> </span>- Drop the id field and primary key as they don't seem to serve
</div><div style="margin: 0px;"><span> </span>any purpose.</div><div style="margin: 0px; min-height: 14px;"><br></div><div style="margin: 0px;"><span> </span>gpx_files</div><div style="margin: 0px; min-height: 14px;">
<br></div><div style="margin: 0px;"><span> </span>- What is the visible field for here? There are a few records</div><div style="margin: 0px;"><span> </span>with it set to zero but the code ignores it - if it isn't
</div><div style="margin: 0px;"><span> </span>used then let's remove it.</div></blockquote><div><br></div></span></div><div>isnt that whats used to delete them?</div></div></div></blockquote><div><br>If it is used for deletion purposes, rename the field. Visible is hardly a name for showing if the record is deleted or not.
<br><br><br> </div><br><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"><div style=""><div><div><span class="e" id="q_11358aa8f5b75550_3"><blockquote type="cite">
<div style="margin: 0px;">gpx_pending_files</div><div style="margin: 0px; min-height: 14px;"><br></div><div style="margin: 0px;"><span> </span>- This seems to be unused so let's remove it.</div><div style="margin: 0px; min-height: 14px;">
<br></div><div style="margin: 0px;"><span> </span>messages</div><div style="margin: 0px; min-height: 14px;"><br></div><div style="margin: 0px;"><span> </span>- Drop user_id column as it serves no purpose (from_user_id
</div><div style="margin: 0px;"><span> </span>and to_user_id record who the message is from and to) and</div><div style="margin: 0px;"><span> </span>it is breaking the message functionality at the moment.</div><div style="margin: 0px; min-height: 14px;">
<br></div><div style="margin: 0px;"><span> </span>- Add index on to_user_id so we can find a user's messages</div><div style="margin: 0px;"><span> </span>efficiently.</div><div style="margin: 0px; min-height: 14px;">
<br></div><div style="margin: 0px;"><span> </span>- Drop from_display_name column and index as although it is</div><div style="margin: 0px;"><span> </span>filled in it is never used, and the information is available
</div><div style="margin: 0px;"><span> </span>by joining to the user table anyway.</div><div style="margin: 0px; min-height: 14px;"><br></div><div style="margin: 0px;"><span> </span>meta_areas</div><div style="margin: 0px; min-height: 14px;">
<br></div><div style="margin: 0px;"><span> </span>- Seems to be unused (but has a few records) and it presumbly</div><div style="margin: 0px;"><span> </span>part of the old area code so should be dropped.</div><div style="margin: 0px; min-height: 14px;">
<br></div><div style="margin: 0px;"><span> </span>nodes</div><div style="margin: 0px; min-height: 14px;"><br></div><div style="margin: 0px;"><span> </span>- Split lat/lon index in to as for current_nodes.</div><div style="margin: 0px; min-height: 14px;">
<br></div><div style="margin: 0px;"><span> </span>segments</div><div style="margin: 0px; min-height: 14px;"><br></div><div style="margin: 0px;"><span> </span>- No changes proposed.</div><div style="margin: 0px; min-height: 14px;">
<br></div><div style="margin: 0px;"><span> </span>users</div><div style="margin: 0px; min-height: 14px;"><br></div><div style="margin: 0px;"><span> </span>- Make email and display name indexes unique.</div><div style="margin: 0px; min-height: 14px;">
<br></div><div style="margin: 0px;"><span> </span>way_segments</div><div style="margin: 0px; min-height: 14px;"><br></div><div style="margin: 0px;"><span> </span>- Make segment_id bigint(64) to match other tables.</div>
<div style="margin: 0px; min-height: 14px;"><br></div><div style="margin: 0px;"><span> </span>way_tags</div><div style="margin: 0px; min-height: 14px;"><br></div><div style="margin: 0px;"><span> </span>- No changes proposed.
</div><div style="margin: 0px; min-height: 14px;"><br></div><div style="margin: 0px;">Please let me know what you think folks...</div><div style="margin: 0px; min-height: 14px;"><br></div><div style="margin: 0px;">Tom</div>
<div style="margin: 0px; min-height: 14px;"><br></div><div style="margin: 0px;">--<span> </span></div><div style="margin: 0px;">Tom Hughes (<a href="mailto:tom@compton.nu" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">
tom@compton.nu</a>)</div><div style="margin: 0px;"><a href="http://www.compton.nu" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">http://www.compton.nu</a>/</div><div style="margin: 0px; min-height: 14px;">
<br></div><div style="margin: 0px;">_______________________________________________</div><div style="margin: 0px;">dev mailing list</div><div style="margin: 0px;"><a href="mailto:dev@openstreetmap.org" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">
dev@openstreetmap.org</a></div><div style="margin: 0px;"><a href="http://lists.openstreetmap.org/cgi-bin/mailman/listinfo/dev" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">http://lists.openstreetmap.org/cgi-bin/mailman/listinfo/dev
</a></div><div style="margin: 0px; min-height: 14px;"><br></div> </blockquote></span></div></div><br><div> <span style="border-collapse: separate; border-spacing: 0px; color: rgb(0, 0, 0); font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px;">
<span style="border-collapse: separate; border-spacing: 0px; color: rgb(0, 0, 0); font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px;">
<span style="border-collapse: separate; border-spacing: 0px; color: rgb(0, 0, 0); font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px;">
<div>have fun,</div><div><br></div><div>SteveC | <a href="mailto:steve@asklater.com" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">steve@asklater.com</a> | <a href="http://www.asklater.com/steve/" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">
http://www.asklater.com/steve/</a></div><br></span></span></span> </div><br></div><br>_______________________________________________<br>dev mailing list<br><a onclick="return top.js.OpenExtLink(window,event,this)" href="mailto:dev@openstreetmap.org">
dev@openstreetmap.org</a><br><a onclick="return top.js.OpenExtLink(window,event,this)" href="http://lists.openstreetmap.org/cgi-bin/mailman/listinfo/dev" target="_blank">http://lists.openstreetmap.org/cgi-bin/mailman/listinfo/dev
</a><br><br></blockquote></div><br><br clear="all"><br>-- <br>Ray Booysen<br><a href="mailto:raybooysen@rjb.za.net">raybooysen@rjb.za.net</a>