SQL query help

  • The international public telecommunication numbering plan

    http://en.wikipedia.org/wiki/E.164

    4 different categories - every single one is made up of several components.

    Not a single one is a single character string.

    Now, you may choose to concatenate those separate parts of a number into a single string and then parse that string into those separate parts every time you need to call that number.

    If you believe the phone does not have anything better to do.

    But to me repeating the same parsing of already parsed number again and again looks extremely stupid.

    As to the people who desined paper forms more than half a century ago.

    That's what I meant when mentioned idiocracy.

    _____________
    Code for TallyGenerator

  • Sergiy (5/5/2015)


    Google's common Java, C++ and JavaScript library for parsing, formatting, storing and validating international phone numbers. The Java version is optimized for running on smartphones, and is used by the Android framework since 4.0 (Ice Cream Sandwich).

    https://github.com/googlei18n/libphonenumber

    Mapping Phone Numbers to carrier

    PhoneNumber swissMobileNumber =

    new PhoneNumber().setCountryCode(41).setNationalNumber(798765432L);

    PhoneNumberToCarrierMapper carrierMapper = PhoneNumberToCarrierMapper.getInstance();

    // Outputs "Swisscom"

    System.out.println(carrierMapper.getNameForNumber(swissMobileNumber, Locale.ENGLISH));

    Neat, you found the frontend code for parsing the strings into classes that can be used programatically.

    Which is a handy thing to have, since all those phone numbers in your contacts list are strings. Stored in contacts2.db. In a table called "data". So when you retrieve the string and go to dial it, you have a handy library turning it into a class, and then figuring out how to dial it based on where you are.

  • So, we have 2 options here.

    1. Parse the entered string once when it's being saved into predefined components and store those components in the class.

    2. Parse the entered string when entered for display purpose, but then drop the parsing, store entered string as is and parse it again into components every time we need to dial the number.

    The rest of the process is identical for both options.

    Which of the options a smart person would choose?

    _____________
    Code for TallyGenerator

  • Sergiy (5/1/2015)


    Most of the numbers stored in my phone contain country codes, even for my own home number. Just in case I'll use the phone when abroad.

    Nevertheless, I'm not charged with international rates when I call those numbers locally. That means that the phone identifies country codes in the numbers and excludes them from the dialling sequence when it matches the country code for my current location.

    Another example.

    I can store the same local number in 2 different ways:

    +6495553322 or

    095553322

    Resulting dialing sequences are the same for both of them.

    It clearly indicates that the phone identifies and separates country code (64 and NULL in this example), area code (9) and the local number when saving those strings. And it generates dialing sequences by building it from saved "blocks".

    I use dumb old fashioned phones that give me an audible beep for each digit dialled, so I know how many digits get sent when I make a call. I store numbers like for example +441234987650 and +34123987654, and whether I'm in the UK or Spain the phones will dial those as respectively 14 numbers and 13 numbers (translating + to 00 in either of those countries - it doesn't change +44 to 0 and just dial 11 numbers when I'm in the UK or change +34 to nothing and just dial 9 numbers in Spain); they translate + correctly to whatever the international prefix is where I am, not just in coutries which use 00, but it always dials all the numbers after the +, whatever it translates it to. It works. I get charged at the appropriate (local, long distance, or international) rate, despite having put whe full international number on the line.

    So clearly if you were in any of the countries I have been in you wouldn't be able to make the deductions that you are claiming from getting the right behaviour from your phone. Are you really sure you can validly make those deductions where you are?

    Currently I don't need pause codes in any of the numbers I call, so can't guess how those would work with today's systems; but way back in the past I used pause codes and kit that always paused when it saw one, which worked back then simply because manual dialling could be expected to have a few random pauses in it so unwanted pauses had to do no harm. I'll be surprised if that has changed.

    Sergiy (5/5/2015)


    Our local newspaper regularly publishes letters from the readers (mainly elderly one, who use not so smart old phones) which dial numbers as they are presented in, say, Yellow Pages, including the city codes and then charged with their telco for long distance calls.

    The papers raised the issue with the telcos but they responded that they cannot do much about that - as soon as they receive initial digit "0" (operator) the call i redirected to another commutator, and the following sequence of digits is processed over there.

    And here I call bullshit! If the telcos say that, they are almost certainly lying - I doubt they are still using ancient relay-based electromechanical exchanges in 2015, so what does "redirected to another commutator" mean? If we can get it right in most of Europe plus bits of the middle East and in India and Sri Lanka surely NZ's telcos could get it right if they wanted to. Perhaps they are just the sort of service provider that milks the vulnerable and lies about it? Maybe ypu don't have regulators for comms service providers in NZ, or maybe there are regulators who are too dumb to spot lies, or maybe too well rewarded for not spotting them?

    Tom

  • Sergiy (5/6/2015)


    So, we have 2 options here.

    1. Parse the entered string once when it's being saved into predefined components and store those components in the class.

    2. Parse the entered string when entered for display purpose, but then drop the parsing, store entered string as is and parse it again into components every time we need to dial the number.

    The rest of the process is identical for both options.

    Which of the options a smart person would choose?

    Remind the 'smart person' that in one of the cases he is maintaining 4 database columns, not 1, and updating all 4 on every phone number change.

    Then remind him that he still needs to perform logic on the predefined components to dial a number.

    Then remind him that in order to build that data entry box, a display field, or report output, he is going to have to build the separated numeric fields together into a string every time.

    Then remind him when you are dialling a number you are operating on one row you've already retrieved, that the relevant parsing takes fractions of nanoseconds. That it is in fact so inexpensive that you do it continuously while a number is being added ad hoc.

    Then remind him he may be sync-ing contacts with outside systems, so he could have to put the numbers together in bulk at each sync depending on that interface if he goes with option 2.

    Then remind him his dialler must be able to do the parsing anyway for ad hoc numbers anyway.

    Then remind him that as a person dials ad-hoc, with each character the phone will be searching through the stored contact numbers for any matching subsets so it can "suggest" auto-complete numbers, and that this searching goes across country codes, area codes, and actual numbers, meaning that if he chose to store them separated, he would also have to maintain the string representation as a copy, or build that string back together for every contact in the database every time a phone number is being dialled.

    And once you're done all that you can remind him that the question was purely academic because you could have just looked up the database for yourself and seen what is being done.

  • Nevyn (5/6/2015)

    Remind the 'smart person' that in one of the cases he is maintaining 4 database columns, not 1, and updating all 4 on every phone number change.

    Hmmm... what does it take exactly?

    Too much of extra memory? Extra CPU power? I don't think so.

    Then remind him that he still needs to perform logic on the predefined components to dial a number.

    Of course, that's what those components are made for.

    And, as we can see from that Google method, those components must be separated before dialing a number anyway.

    Then remind him that in order to build that data entry box, a display field, or report output, he is going to have to build the separated numeric fields together into a string every time.

    If there are some specific requirements for formatting display fields, report output, then yes.

    But is not what he need to do with a single string anyway? After parsing it into those components each time?

    Then remind him when you are dialling a number you are operating on one row you've already retrieved, that the relevant parsing takes fractions of nanoseconds. That it is in fact so inexpensive that you do it continuously while a number is being added ad hoc.

    It might be inexpensive but it's still is an unnecessary overhead.

    Job being done repeatedly on each call instead of having it done once.

    bad practice.

    Then remind him his dialler must be able to do the parsing anyway for both ad hoc numbers

    Why?

    Ad hoc numbers are parsed while typing. You can see the outcome of the parsing on the screen straight away.

    If he keeps the outcome of that parsing in memory (either operating or permanent) then dialer does not need to do any parsing anymore.

    And once you're done all that you can remind him that the question was purely academic because you could have just looked up the database for yourself and seen what is being done.

    The question is quite practical.

    Bad practices like this have become habits for majority of developers.

    Being used in every single piece of software they add up those overheads to the point when hardware does not have computing power for useful tasks - overheads eat everything.

    As a result - it takes almost 2 minutes to expand "Tables", find a table and open it in the design view in SSMS (all locally,on my laptop - double core, 2GHz, 64 bit) when I could do it within 10 seconds in EM on my old single core 700MHz laptop.

    As a result - Windows 8 simply cannot run of a machine which was perfectly running XP, despite all the maketing claims from MS.

    As a result - it takes about a minute to open MS Word 2010 on a contemporary machine, when I used to get MS Word 2000 started on Pentium 120 within 5 seconds.

    As a result - upgrading from iOS6 to iOS7 simply kills iPhone 4, it takes up to 10 seconds for it to react on pressing the home button. Just upgrading OS, nothing else, no new application, no new data.

    Habits to those "non-optimal" practices is what cause all the troubles when front-end developers are trying to do SQL.

    Overheads from ineffective storage design and coding become not so insignificant when they are used in multi-user concurrent environment with thousands and millions of records to process, instead of tens and hundreds.

    But those developers don't know other ways, they used to deal with data like that - and don't see anything wrong with that! Because it always worked for them! And that's how even Google does it!

    If total degradation of software effectiveness in last years is not a sign of idiocracy on the march - then what it is?

    _____________
    Code for TallyGenerator

  • Sergiy (5/6/2015)


    Hmmm... what does it take exactly?

    Too much of extra memory? Extra CPU power? I don't think so.

    But parsing one string before dialling a number takes "Too much of extra memory? Extra CPU power? I don't think so."

    Same argument, so save it.

    Why?

    Ad hoc numbers are parsed while typing.

    Exactly what I was saying. You are typing the number into the dialler and it is parsing it as you go.

    And as I pointed out in my edit above, it is also searching what you have typed so far for a profile match against every number in your phone, and the profile match is looking for substrings in any part of a number for each number in your contacts database. Want to ask the smart person of the efficiency of merging the separate numbers of your entire table into strings and then searching them?

    The question is quite practical.

    Bad practices like this have become habits for majority of developers.

    Being used in every single piece of software they add up those overheads to the point when hardware does not have computing power for useful tasks - overheads eat everything.

    As a result - it takes almost 2 minutes to expand "Tables", find a table and open it in the design view in SSMS (all locally,on my laptop - double core, 2GHz, 64 bit) when I could do it within 10 seconds in EM on my old single core 700MHz laptop.

    As a result - Windows 8 simply cannot run of a machine which was perfectly running XP, despite all the maketing claims from MS.

    As a result - it takes about a minute to open MS Word 2010 on a contemporary machine, when I used to get MS Word 2000 started on Pentium 120 within 5 seconds.

    As a result - upgrading from iOS6 to iOS7 simply kills iPhone 4, it takes up to 10 seconds for it to react on pressing the home button. Just upgrading OS, nothing else, no new application, no new data.

    Habits to those "non-optimal" practices is what cause all the troubles when front-end developers are trying to do SQL.

    Overheads from ineffective storage design and coding become not so insignificant when they are used in multi-user concurrent environment with thousands and millions of records to process, instead of tens and hundreds.

    But those developers don't know other ways, they used to deal with data like that - and don't see anything wrong with that! Because it always worked for them! And that's how even Google does it!

    If total degradation of software effectiveness in last years is not a sign of idiocracy on the march - then what it is?

    So are you still arguing about whether Android does it that way?

    Or are you arguing that Android does it the way I told you, but that it is stupid, and you know better than they do?

  • Nevyn (5/6/2015)

    But parsing one string before dialling a number takes "Too much of extra memory? Extra CPU power? I don't think so."

    Yes, it does take extra CPU power.

    Parsing a string according to some flexible rules depending on variable environment parameters is not a cheap process.

    It's better to do it once and reuse it outcome for further tasks.

    So are you still arguing about whether Android does it that way?

    Or are you arguing that Android does it the way I told you, but that it is stupid, and you know better than they do?

    Are you still arguing my point about idiocracy?

    Or you just OK with it progressing?

    _____________
    Code for TallyGenerator

  • Yes, it does take extra CPU power.

    Parsing a string according to some flexible rules depending on variable environment parameters is not a cheap process.

    It's better to do it once and reuse it outcome for further tasks.

    Oh, I get it, 3 additional numeric columns in a database table are not significant extra storage

    Converting and appending the numeric columns into strings all over the place including in bulk operations is not significant CPU power.

    But parsing a single retrieved string value is just too expensive for a multi-core processor to handle.

  • TomThomson (5/6/2015)

    Sergiy (5/5/2015)


    Our local newspaper regularly publishes letters from the readers (mainly elderly one, who use not so smart old phones) which dial numbers as they are presented in, say, Yellow Pages, including the city codes and then charged with their telco for long distance calls.

    The papers raised the issue with the telcos but they responded that they cannot do much about that - as soon as they receive initial digit "0" (operator) the call i redirected to another commutator, and the following sequence of digits is processed over there.

    And here I call bullshit!

    Wow-wow-wow!

    Check before you yell.

    https://www.actrix.co.nz/services-actrix-tolls

    We will charge for all calls made using an area code, so if you want to make a free local call please don't dial an area code. You should also check any one touch dial numbers in your phone to make sure they aren't using area codes. It is also worth checking any Speed Dial, Call Forwarding, Caller ID or Alarm Service settings in case they use area codes too.

    You may visit web pages of other NZ providers - Vodafone.co.nz, spart.co.nz, etc and see it works the same for all of them.

    BTW, you did not answer - what do you find so outdated about fax technology?

    _____________
    Code for TallyGenerator

  • Nevyn (5/6/2015)


    Oh, I get it, 3 additional numeric columns in a database table are not significant extra storage

    It's not. You still save same amount of data - probably even less because you remove unnecessary prefixes like "011".

    Increasing the number of fields in Contact.contract from 12 to 14 changes only metadata definitions, nothing else.

    Converting and appending the numeric columns into strings all over the place including in bulk operations is not significant CPU power.

    Are you trying to play stupid?

    I guess, we all agreed that this part has to be done anyway.

    Either way the dialing method needs to have area and international codes separated from a local number.

    But with a phone number stored as a single string you need to parse it first to 3 numeric columns, and only then convert and concatenate into a proper calling sequence having all necessary prefixes and pauses, if needed.

    But parsing a single retrieved string value is just too expensive for a multi-core processor to handle.

    To be honest, I'd prefer that lousy developers would not make my processor with any number of cores to do a job which is totally unnecessary.

    I prefer my iPhone to respond on clicking "Call" instantly, like it used to do under iOS5 and iOS6.

    Not with 2-5 seconds delay, like it does under iOS7 (same about my Alcatel phone under Android).

    _____________
    Code for TallyGenerator

  • Sergiy (5/7/2015)


    TomThomson (5/6/2015)

    Sergiy (5/5/2015)


    Our local newspaper regularly publishes letters from the readers (mainly elderly one, who use not so smart old phones) which dial numbers as they are presented in, say, Yellow Pages, including the city codes and then charged with their telco for long distance calls.

    The papers raised the issue with the telcos but they responded that they cannot do much about that - as soon as they receive initial digit "0" (operator) the call i redirected to another commutator, and the following sequence of digits is processed over there.

    And here I call bullshit!

    Wow-wow-wow!

    Check before you yell.

    You really are somewhat beyond the point at which it becomes pointless to continue the discussion, aren't you? Throw away the context so you you can quote a few words and comment on them as if they referred to something altogether other than what they referred to. now yoyu've done that so blatantly I would be fully justified to call bullshit on YOU as well as on the NZ telecom service providers who play this really nasty game.

    You haven't responded to a single one of the points I made. It's quite clear that the way phones work here is not the way you state they work there.

    Tom

  • TomThomson (5/8/2015)


    You haven't responded to a single one of the points I made.

    I must have missed them. Sorry.

    Can you please repeat them in form of, say, bullet points list?

    It would make it easier to see them.

    It's quite clear that the way phones work here is not the way you state they work there.

    Not so clear. At least for me.

    1st - I don't know where's "here".

    2nd - I did not try myself to make different kind of calls using networks "here" and I have not seen any reliable documentation about the way phones must be dialed "here".

    3rd - I cannot be sure same call patterns are used by all the network operating in "here".

    So, not clear at all, sorry.

    _____________
    Code for TallyGenerator

  • Sergiy (5/8/2015)


    TomThomson (5/8/2015)


    You haven't responded to a single one of the points I made.

    I must have missed them. Sorry.

    Can you please repeat them in form of, say, bullet points list?

    It would make it easier to see them.

    I suggest you read some of the earlier messages. In particular the one where I point out that I get corect charging in many European countries and a few other countries despite the cheap cell phones I use always dialing all of the phone number (country code, area code, local number - detectable because I get audible beeps for each number they send, so I don't have to make guesses as to what they are sending). Also the specific examples I gave using counry codes 34 and 44 should indicate that "here" includes Spain and UK even if you have decided that reading the list of countries that I included in one post is too much effort for you.

    Tom

  • There are no "local" calls from mobiles - you do realise that, right?

    Mobiles do only "long distance" and "international" calls.

    Every single call from a mobile phone starts with "0", at least in Spain and UK.

    Your experience with mobile networks is irrelevant to the discussion.

    Can we agree on this?

    _____________
    Code for TallyGenerator

Viewing 15 posts - 61 through 75 (of 76 total)

You must be logged in to reply to this topic. Login to reply