View Issue Details

Jump to NotesJump to History
IDProjectCategoryView StatusDate SubmittedLast Update
0000280Main CAcert Websitemy accountpublic2006-08-05 04:082013-01-14 10:44
Reporterunnilennium Assigned To 
PrioritynormalSeveritytrivialReproducibilityalways
Status closedResolutionfixed 
Fixed in Version2007 
Summary0000280: Impossible to be localised in France
DescriptionIn the location no French cities are listed.
I would like to localized me in Toulouse,Midi-Pyrennee, France , but it's impossible
Additional Informationsame probleme for finding an assurer
TagsNo tags attached.
Reviewed by
Test Instructions

Activities

unnilennium

2006-08-08 04:33

reporter   ~0000330

the probleme is not i cant' see myself in the assurer list at all.

The problem is the ajax system does not permit to enter a french city ( try Paris, Toulouse, Marseille, Lyon , Rennes, Brest , Clermont-Ferrand, Nice ....)

IS french cities have been forgotten from the database for the ajax system ?

duane

2006-08-14 14:26

developer   ~0000448

Last edited: 2006-08-16 06:55

 Added 64625 rows...

France, a small country, counts more than 36000 towns and villages !

homer

2006-08-15 21:39

reporter   ~0000491

Last edited: 2006-08-16 06:53

 I am afraid it's the old buggy US DB for France

Paris is
"Paris, Rhone-Alpes, France" (Rhones-Alpes is 600 or 700 km far from Paris)
instead of
"Paris, Ile-de-France, France"

I'll look at Benjamin's work and get the latest cacert.sql if available

if we stick to 'departement' schema
it would be
"Paris, Paris, France" as Paris is a town and also a departement (formely known as "Paris,Seine,France" in the 1970's

duane

2006-08-16 04:01

developer   ~0000496

are the region names correct atleast?

homer

2006-08-16 05:59

reporter   ~0000504

Last edited: 2006-08-16 06:17

 Well... The regions are "correct." of course

But, FYI, noone in France use thoses names to "explain" someone's location. We use a subdivision of the region named "departement"

We have 22 regions and >80 "departements"

... due to EU pressure, France is pushed to withdraw the subdivision "departement" :) which make the things more complicated.

duane

2006-08-16 06:24

developer   ~0000507

Do you have a latin version of the department names I can upload?

homer

2006-08-16 06:48

reporter   ~0000509

Last edited: 2006-08-16 06:57

 The cacert.sql file in the tarball is outdated 4th Junuary 2005

We were expecting it to be updated... :(

Benji's work is based on data from UN DB

http://users.skynet.be/vegalix/frenchDB-loc.tar.bz2

in the script
"insert into regions" refer to 'departements'

If you could provide SQL CREATEs for the related tables I would help

2006-08-20 10:13

 

frenchdb.zip (70,659 bytes)

homer

2006-08-20 10:15

reporter   ~0000618

Based on Benji's work who unfortunately had not the latest DB schema

The file frenchdb.zip has been tested on test1

deufrai

2006-08-25 18:23

reporter   ~0000637

finally managed to set location to Paris, Paris, France using https://www.test1.cacert.at/

good work :)

benji

2006-08-27 06:23

updater   ~0000638

okay, i'm back again. First , thanks to Guillaume to create my account here.
Secondly, what's the problem now ?
What's the current status of the french DB ?

Benji.

homer

2006-08-27 07:32

reporter   ~0000639

Last edited: 2006-08-27 07:39

 I guess it's ok now, it just needs to be installed on production when possible.

Someone kindly updated the DB on test1, so I was able to complete the task.

unnilennium

2006-08-27 07:41

reporter   ~0000641

sounds great !

thank you for your work Guys!
I just test this on the test server it's wonderfull it's work.

just waiting this on the prod server.

duane

2006-08-27 07:47

developer   ~0000642

The data contains UTF-8 chars and I'm in the process of converting to US-ascii due to the the current policy of allowing everyone to access the information regardless of keyboards/backgrounds/etc.

These entries (if converted to html entities) can be used as secondaries however.

benji

2006-08-27 19:33

updater   ~0000644

I already done latin1 and utf-8 versions, as i known prod DB is running in latin1.

benja@vinea:~/data/cacertdb/sql> tar tvjf frenchDB-loc.tar.bz2
-rw-r--r-- benja/users 626708 2006-07-18 11:47:27 frenchDB-locations-lat1.sql
-rw-r--r-- benja/users 627717 2006-07-18 11:40:30 frenchDB-locations-UTF8.sql
-rw-r--r-- benja/users 124 2006-07-18 11:50:03 md5sum.txt

If you wan tany thing else, let me known. :-)
a+
Benji

homer

2006-08-27 20:06

reporter   ~0000645

I've been using latin1 version of benji's work

Sourcerer

2006-09-13 23:21

administrator   ~0000675

Ok, the database generally looks nice to me. There is just one final problem:
CAcert has a system for alternative location names. CAcert decided to have a us-ascii representation (just english characters, no Latin1 characeters) as the main encoding for location names, and fully Unicode encoded names as alternative location names. This is to make sure that everyone can enter the locations and find assurers even if only an english keyboard is available. Now the difficult problem is that there are different ways to transform the correct name into a us-ascii one. For example the german umlauts are officially translated with an e afterwards: ä -> ae , ö -> oe ... but people who don´t know about those official rules would translate it without the e: ä -> a . So we should add both forms if possible.
So please prepare this french location database that way.
Additionally, I am sorry that we didn´t communicate that problem clearly enough earlier.

homer

2006-09-14 05:47

reporter   ~0000676

OK I see... a bit of extra work.

There's no problem in french all the accentuated letters can be converted as is.

the only exception (which has nearly disappeared) is the "o"+"e" letter which is spelled "oe" :)

benji

2006-09-14 06:24

updater   ~0000677

Let me know if I have to convert something, but effectively, french is less complex as german for some sounds.
So, tell me what... :-)
a+
Benji

homer

2006-09-14 11:14

reporter   ~0000678

Hi Sourcerer,

Here the db structure on test1

Do we need to change the structure to hold the ASCII version of the names ?

Because there is no field for this ???

Benji : you have the structure and the file with the data I transformed from you

-- phpMyAdmin SQL Dump
-- version 2.7.0
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Sep 14, 2006 at 01:14 AM
-- Server version: 4.1.11
-- PHP Version: 4.3.10-16
--
-- Database: `cacert`
--

-- --------------------------------------------------------

--
-- Table structure for table `countries`
--

CREATE TABLE `countries` (
  `id` int(3) NOT NULL auto_increment,
  `name` varchar(50) NOT NULL default '',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=82 ;

-- --------------------------------------------------------

--
-- Table structure for table `locations`
--

CREATE TABLE `locations` (
  `id` int(7) NOT NULL auto_increment,
  `regid` int(4) NOT NULL default '0',
  `ccid` int(3) NOT NULL default '0',
  `name` varchar(50) NOT NULL default '',
  `lat` double(6,3) NOT NULL default '0.000',
  `long` double(6,3) NOT NULL default '0.000',
  PRIMARY KEY (`id`),
  KEY `ccid` (`ccid`),
  KEY `regid` (`regid`),
  KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=7904257 ;

-- --------------------------------------------------------

--
-- Table structure for table `regions`
--

CREATE TABLE `regions` (
  `id` int(5) NOT NULL auto_increment,
  `ccid` int(3) NOT NULL default '0',
  `name` varchar(50) NOT NULL default '',
  PRIMARY KEY (`id`),
  KEY `ccid` (`ccid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=79098 ;

duane

2006-09-14 12:33

developer   ~0000679

> Do we need to change the structure to hold the ASCII version of the names ?
> Because there is no field for this ???

We tried to store alternate names in a separate tables, but this didn't work so well in the past so currently we simply duplicate the data (long/lat) with the alternate location names.

benji

2006-09-14 17:26

updater   ~0000680

Last edited: 2006-09-14 17:34

 Here's the locations table structure I used :

CREATE TABLE `locations` (
  `id` int(11) NOT NULL auto_increment,
  `ccid` int(11) NOT NULL default '0',
  `regid` int(11) NOT NULL default '0',
  `abbr` char(3) NOT NULL default '',
  `long` varchar(255) NOT NULL default '',
  `longalt` varchar(255) NOT NULL default '',
  `lat` decimal(8,5) NOT NULL default '0.00000',
  `lon` decimal(8,5) NOT NULL default '0.00000',
  PRIMARY KEY (`id`),
  UNIQUE KEY `thekey` (`ccid`,`regid`,`abbr`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

And in the `longalt` field, I've the "unformatted" data translated of `long`.
Example :

mysql> select id,`long`,longalt from locations where longalt like 'Amb%' ;
+-------+-------------------------+-----------------------+
| id | long | longalt |
+-------+-------------------------+-----------------------+
| 17320 | Ambarès-et-Lagrave | Ambares-et-Lagrave |
| 17321 | Ambérieu-en-Bugey | Amberieu-en-Bugey |
| 17322 | Ambérieux | Amberieux |
| 17323 | Ambert | Ambert |
| 17324 | Ambès | Ambes |
| 17325 | Ambillou | Ambillou |
| 17326 | Ambilly | Ambilly |
| 17327 | Amboise | Amboise |
| 17328 | Ambrières-les-Vallées | Ambrieres-les-Vallees |
| 17329 | Ambronay | Ambronay |
+-------+-------------------------+-----------------------+
10 rows in set (0.01 sec)

So, I could easily make another script to insert unicode data for the `longalt` field, refereced on the `id` field.

It has been a little difficult to me to works with two differents DB schemas...
If you want a dump of my cacertdb, I could provide it too of course.
Just tell me what's the easier for you... :-)

Benji

2006-09-15 07:53

 

frenchDB_ascii.zip (396 bytes)

homer

2006-09-15 07:55

reporter   ~0000683

here's the update script as requested.

loaded in test1

unnilennium

2007-01-04 22:44

reporter   ~0000763

Last edited: 2007-01-04 22:49

 still nothing to come ?
the test server seems out of order.

may i give a help ?

In france we can use Postal ZIP code (but they could match to more than one city, and some cities have more than one ZIP CODE) or unique INSEE code for each city

Sourcerer

2007-03-28 11:58

administrator   ~0000824

FrenchDB has been imported now. Thanks for your help!
Please test it, and close the bug then.

Issue History

Date Modified Username Field Change
2006-08-05 04:08 unnilennium New Issue
2006-08-08 04:12 duane Relationship added duplicate of 0000261
2006-08-08 04:13 duane Status new => closed
2006-08-08 04:13 duane Resolution open => duplicate
2006-08-08 04:13 duane Fixed in Version => production
2006-08-08 04:33 unnilennium Status closed => needs feedback
2006-08-08 04:33 unnilennium Resolution duplicate => reopened
2006-08-08 04:33 unnilennium Note Added: 0000330
2006-08-08 05:32 Sourcerer Relationship deleted 0000261
2006-08-14 12:16 duane Status needs feedback => needs work
2006-08-14 12:16 duane Assigned To => duane
2006-08-14 14:25 duane Assigned To duane => homer
2006-08-14 14:26 duane Status needs work => solved?
2006-08-14 14:26 duane Resolution reopened => fixed
2006-08-14 14:26 duane Note Added: 0000448
2006-08-15 21:39 homer Note Added: 0000491
2006-08-16 04:01 duane Note Added: 0000496
2006-08-16 04:04 duane Status solved? => needs feedback
2006-08-16 04:04 duane Resolution fixed => reopened
2006-08-16 05:59 homer Note Added: 0000504
2006-08-16 06:17 homer Note Edited: 0000504
2006-08-16 06:24 duane Note Added: 0000507
2006-08-16 06:48 homer Note Added: 0000509
2006-08-16 06:52 homer Note Edited: 0000491
2006-08-16 06:53 homer Note Edited: 0000491
2006-08-16 06:55 homer Note Edited: 0000448
2006-08-16 06:57 homer Note Edited: 0000509
2006-08-20 10:07 homer File Added: frenchdb.txt
2006-08-20 10:07 homer File Deleted: frenchdb.txt
2006-08-20 10:13 homer File Added: frenchdb.zip
2006-08-20 10:15 homer Note Added: 0000618
2006-08-20 10:18 homer Assigned To homer => duane
2006-08-20 10:18 homer Status needs feedback => needs work
2006-08-25 18:23 deufrai Note Added: 0000637
2006-08-27 06:23 benji Note Added: 0000638
2006-08-27 07:32 homer Note Added: 0000639
2006-08-27 07:39 homer Note Edited: 0000639
2006-08-27 07:41 unnilennium Note Added: 0000641
2006-08-27 07:47 duane Note Added: 0000642
2006-08-27 19:33 benji Note Added: 0000644
2006-08-27 20:06 homer Note Added: 0000645
2006-09-13 23:21 Sourcerer Note Added: 0000675
2006-09-14 05:47 homer Note Added: 0000676
2006-09-14 06:24 benji Note Added: 0000677
2006-09-14 11:14 homer Note Added: 0000678
2006-09-14 12:33 duane Note Added: 0000679
2006-09-14 17:26 benji Note Added: 0000680
2006-09-14 17:31 benji Note Edited: 0000680
2006-09-14 17:34 benji Note Edited: 0000680
2006-09-15 07:52 homer File Added: frenchDB_ascii.txt
2006-09-15 07:53 homer File Deleted: frenchDB_ascii.txt
2006-09-15 07:53 homer File Added: frenchDB_ascii.zip
2006-09-15 07:55 homer Note Added: 0000683
2007-01-04 22:44 unnilennium Note Added: 0000763
2007-01-04 22:49 unnilennium Note Edited: 0000763
2007-03-28 11:58 Sourcerer Status needs work => solved?
2007-03-28 11:58 Sourcerer Resolution reopened => fixed
2007-03-28 11:58 Sourcerer Note Added: 0000824
2007-10-24 05:52 evaldo Assigned To duane =>
2007-10-24 05:52 evaldo Status solved? => closed
2013-01-14 10:44 Werner Dworak Fixed in Version => 2007