MySQL – Hex, Bugs and More Physics | Emre S. Tasci http://www.emresururi.com/physics a blog about physics, computation, computational physics and materials... Wed, 15 Jan 2014 13:36:05 +0000 en-US hourly 1 https://wordpress.org/?v=4.9.3 The marriage of heaven (common elements) and hell (code writing code). http://www.emresururi.com/physics/?p=76 http://www.emresururi.com/physics/?p=76#respond Thu, 26 Jun 2008 10:57:09 +0000 http://www.emresururi.com/physics/?p=76 So, as Blake had said, "Energy is Eternal Delight." or in other words, let’s merge the preceding two entries to write a code for a function that produces MySQL queries that will fetch the common elements involved with the given elements:

function fetch_elements($el,$order,$total)
{
    // produces the query to select the elements that have binaries with the $el
    // $order and $total are used to adjust the tab positions and designating the sets
 
    $abc = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
    $tab = str_repeat("\t",($total-$order));
    $lom = <<<LOM
$tab SELECT symbol_A AS symb, val1 AS val$abc[$order]
$tab FROM `dbl014`
$tab WHERE symbol_B = "$el"
$tab GROUP BY Symbol_A
$tab UNION
$tab SELECT symbol_B AS symb, val1 AS val$abc[$order]
$tab FROM `dbl014`
$tab WHERE (
$tab     symbol_A = "$el"
$tab     AND symbol_B != ""
$tab )
$tab GROUP BY Symbol_B
LOM;
    return $lom;
}
 
function merge_queries($str1,$str2,$or1,$or2,$tab)
{
    // merges two node (or node group) queries (formed via fetch_elements() function)
    // using the INNER JOIN function
    return "$tab SELECT *\n$tab FROM (\n".$str1."\n$tab ) AS $or1\n$tab INNER JOIN (\n".$str2."\n$tab ) AS $or2\n$tab USING (symb)\n$tab ";
}
 
function fetch_common_elements($ar_el)
{
    // produces the query that selects the common elements that have binaries with all of the elements given in the $ar_el array (using their symbols)
    $total = sizeof($ar_el);
    $abc = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
    //$tab = str_repeat("\t",($total-$order-1));
    $str[0] = fetch_elements($ar_el[0],0,$total-1);
    $str[1] = fetch_elements($ar_el[1],1,$total);
    $stri = merge_queries($str[0],$str[1],"A","B",str_repeat("\t",($total-2)));
    for($i=2;$i<$total;$i++)
    {
        $str[$i] = fetch_elements($ar_el[$i],$i,$total);
        $stri = merge_queries($str[$i],$stri,$abc[$i],$abc[($i+1)],str_repeat("\t",($total-$i-1)));
    }
    return $stri;
}

Now, we can call it with, for example:
echo fetch_common_elements(Array("Ga","Fe","Gd","Y","Ti","Ge","Ce"));
Which will output:

 SELECT *
 FROM (
         SELECT symbol_A AS symb, val1 AS valG
         FROM `dbl014`
         WHERE symbol_B = "Ce"
         GROUP BY Symbol_A
         UNION
         SELECT symbol_B AS symb, val1 AS valG
         FROM `dbl014`
         WHERE (
             symbol_A = "Ce"
             AND symbol_B != ""
         )
         GROUP BY Symbol_B
 ) AS G
 INNER JOIN (
         SELECT *
         FROM (
                 SELECT symbol_A AS symb, val1 AS valF
                 FROM `dbl014`
                 WHERE symbol_B = "Ge"
                 GROUP BY Symbol_A
                 UNION
                 SELECT symbol_B AS symb, val1 AS valF
                 FROM `dbl014`
                 WHERE (
                     symbol_A = "Ge"
                     AND symbol_B != ""
                 )
                 GROUP BY Symbol_B
         ) AS F
         INNER JOIN (
                 SELECT *
                 FROM (
                         SELECT symbol_A AS symb, val1 AS valE
                         FROM `dbl014`
                         WHERE symbol_B = "Ti"
                         GROUP BY Symbol_A
                         UNION
                         SELECT symbol_B AS symb, val1 AS valE
                         FROM `dbl014`
                         WHERE (
                             symbol_A = "Ti"
                             AND symbol_B != ""
                         )
                         GROUP BY Symbol_B
                 ) AS E
                 INNER JOIN (
                         SELECT *
                         FROM (
                                 SELECT symbol_A AS symb, val1 AS valD
                                 FROM `dbl014`
                                 WHERE symbol_B = "Y"
                                 GROUP BY Symbol_A
                                 UNION
                                 SELECT symbol_B AS symb, val1 AS valD
                                 FROM `dbl014`
                                 WHERE (
                                     symbol_A = "Y"
                                     AND symbol_B != ""
                                 )
                                 GROUP BY Symbol_B
                         ) AS D
                         INNER JOIN (
                                 SELECT *
                                 FROM (
                                         SELECT symbol_A AS symb, val1 AS valC
                                         FROM `dbl014`
                                         WHERE symbol_B = "Gd"
                                         GROUP BY Symbol_A
                                         UNION
                                         SELECT symbol_B AS symb, val1 AS valC
                                         FROM `dbl014`
                                         WHERE (
                                             symbol_A = "Gd"
                                             AND symbol_B != ""
                                         )
                                         GROUP BY Symbol_B
                                 ) AS C
                                 INNER JOIN (
                                         SELECT *
                                         FROM (
                                                 SELECT symbol_A AS symb, val1 AS valA
                                                 FROM `dbl014`
                                                 WHERE symbol_B = "Ga"
                                                 GROUP BY Symbol_A
                                                 UNION
                                                 SELECT symbol_B AS symb, val1 AS valA
                                                 FROM `dbl014`
                                                 WHERE (
                                                     symbol_A = "Ga"
                                                     AND symbol_B != ""
                                                 )
                                                 GROUP BY Symbol_B
                                         ) AS A
                                         INNER JOIN (
                                                 SELECT symbol_A AS symb, val1 AS valB
                                                 FROM `dbl014`
                                                 WHERE symbol_B = "Fe"
                                                 GROUP BY Symbol_A
                                                 UNION
                                                 SELECT symbol_B AS symb, val1 AS valB
                                                 FROM `dbl014`
                                                 WHERE (
                                                     symbol_A = "Fe"
                                                     AND symbol_B != ""
                                                 )
                                                 GROUP BY Symbol_B
                                         ) AS B
                                         USING (symb)
 
                                 ) AS D
                                 USING (symb)
 
                         ) AS E
                         USING (symb)
 
                 ) AS F
                 USING (symb)
 
         ) AS G
         USING (symb)
 
 ) AS H
 USING (symb)

and when fed to the database will yield:

 

 thank y’all.. 8)

]]> http://www.emresururi.com/physics/?feed=rss2&p=76 0 (Un)Common? http://www.emresururi.com/physics/?p=75 http://www.emresururi.com/physics/?p=75#respond Tue, 24 Jun 2008 12:06:16 +0000 http://www.emresururi.com/physics/?p=75 They say that, it is the opposite (unsimilar) couples that make up for much stabler relationships… If you have only 2 different values, (+) and (-), spin up and down, man and woman, then the unsimilar of a thing that is unsimilar to you equals to a thing that is similar to you. Although it is generally not valid for elements, it gives you an insight nonetheless.. So if Y, Gd, Fe and Ga play a similar part in dearest ZrO2‘s life, let’s check for their other common "partners" that are known to have some connection/involvement this or that way…

The table we’re interested in today holds the formula and the constituent elements as in the example below:

 

(Actually the table contains way more properties than the formula and the constituent elements but I’ve focused only to the relevant props)

symbol_A and symbol_B are position oriented wrt the written formula, meaning that for an AxBy binary, you have A for symbol_A and B for symbol_B whereas if it is BxAy, then they are reversed.

We will be looking for elements that form binaries with the aforementioned 4 elements, namely: Yttrium, Gadolinium, Iron and Gallium.

We can search for each of these 4 via the following query and then add it up:

SELECT symbol_A as symb,val1 FROM `dbl014`
   WHERE  symbol_B="Gd" 
   GROUP BY Symbol_A
UNION
SELECT symbol_B as symb, val1 FROM `dbl014`
   WHERE (symbol_A="Gd" AND symbol_B!="") 
   GROUP BY Symbol_B

 

For the intersection of two elements, we glue the two like the above one via the INNER JOIN function:

SELECT * FROM
(
   SELECT symbol_A as symb,val1
      FROM `dbl014`
      WHERE  symbol_B="Gd"
      GROUP BY Symbol_A

   UNION

   SELECT symbol_B as symb, val1
      FROM `dbl014` WHERE (symbol_A="Gd" AND symbol_B!="")
      GROUP BY Symbol_B
) AS A

INNER JOIN

(
   SELECT symbol_A as symb,val1
      FROM `dbl014` WHERE symbol_B="Y" 
      GROUP BY Symbol_A

   UNION

   SELECT symbol_B as symb, val1
      FROM `dbl014` where (symbol_A="Y" AND symbol_B!="") 
      GROUP BY Symbol_B
) AS B
USING (symb)
ORDER BY symb

 

And for the ultimate quattro, it gets even exciting! :

SELECT *
FROM (

    SELECT symbol_A AS symb, val1 AS val
    FROM `dbl014`
    WHERE symbol_B = "Fe"
    GROUP BY Symbol_A
    UNION
    SELECT symbol_B AS symb, val1 AS val
    FROM `dbl014`
    WHERE (
        symbol_A = "Fe"
        AND symbol_B != ""
    )
    GROUP BY Symbol_B
) AS A
INNER JOIN (
    SELECT *
    FROM (

        SELECT symbol_A AS symb, val1 AS valA
        FROM `dbl014`
        WHERE symbol_B = "Gd"
        GROUP BY Symbol_A
        UNION
        SELECT symbol_B AS symb, val1 AS valA
        FROM `dbl014`
        WHERE (
            symbol_A = "Gd"
            AND symbol_B != ""
        )
        GROUP BY Symbol_B
    ) AS A
    INNER JOIN (
        SELECT *
        FROM (

            SELECT symbol_A AS symb, val1 AS valB
            FROM `dbl014`
            WHERE symbol_B = "Ga"
            GROUP BY Symbol_A
            UNION
            SELECT symbol_B AS symb, val1 AS valB
            FROM `dbl014`
            WHERE (
                symbol_A = "Ga"
                AND symbol_B != ""
            )
            GROUP BY Symbol_B
        ) AS A
        INNER JOIN (
            SELECT symbol_A AS symb, val1 AS valC
            FROM `dbl014`
            WHERE symbol_B = "Y"
            GROUP BY Symbol_A
            UNION
            SELECT symbol_B AS symb, val1 AS valC
            FROM `dbl014`
            WHERE (
                symbol_A = "Y"
                AND symbol_B != ""
            )
            GROUP BY Symbol_B
        ) AS B
        USING ( symb )
    ) AS C
    USING ( symb )
) AS D
USING ( symb )
ORDER BY symb

and voila!

a total of 42 all in all. The "val" columns are there for verificational purposes only – since after applying the GROUP BY function, they all lose their meanings.

]]> http://www.emresururi.com/physics/?feed=rss2&p=75 0 Pettifor Map 2000 Edition http://www.emresururi.com/physics/?p=63 http://www.emresururi.com/physics/?p=63#respond Wed, 09 Apr 2008 13:54:06 +0000 http://www.emresururi.com/physics/?p=63 Suppose you have the formulas, structures and temperature and pressure of the measuring environment stored in your database. Here are some steps to draw a 2000 version of the Pettifor Map.

First of all, here is what my formula table in the database looks like:

The information in molA and molB columns are populated from formulas using the following MySQL commands:



AB
UPDATE dbl014 SET molA=1, molB=1 WHERE val1 NOT REGEXP "[0-9]"

AB[0-9]
UPDATE `dbl014` SET molA=1 WHERE val1 NOT REGEXP "[0-9][A-Z]" AND molA IS NULL

A[0-9]B
UPDATE `dbl014` SET molB=1 WHERE val1 NOT REGEXP "[0-9]$" AND molB IS NULL

A2B
UPDATE `dbl014` SET molA=2 WHERE val1 REGEXP "[A-z]2[A-z]" AND molB =1

AB2
UPDATE `dbl014` SET molB=2 WHERE val1 REGEXP "[A-z]2$" AND molA =1

But we will be focusing only to the AB compositon today.

In addition, I have another table where I store information about elements:

So I can go between Symbol to Atomic Number to Mendeleev Number as I please..

Here is what I did:

* Retrieved the complete list of AB compositions.

* Retrieved the reported structures and the temperature and pressure of the entries for each composition.

* Filtered the ones that weren’t measured/calculated at ambient pressure

* Also recorded each component’s Mendeleev number, so at the end I obtained the following table where formula and structure information is enumerated (for instance, the values you see in the formula column are nothing but the ids of the formulas you can see in the first figure):

 

* Next, selected those that were measured in ambient temperature (something like SELECT * FROM db.table GROUP BY structure, formula WHERE temp=298)

* Builded a histogram for structure populations, took the first 10 most populated structures as distinct and joining the rest as "other" (assigned structure number of "0"). By the way, here are the top 20 structures for AB compositions with the first number in parentheses showing its rank among binary compounds while the second is the number AB compositions registered in that structure type:

 1 — NaCl,cF8,225 (4) (347)
 2 — CsCl,cP2,221 (7) (306)
 3 — TlI,oS8,63 (14) (126)
 4 — FeB-b,oP8,62 (32) (69)
 5 — NiAs,hP4,194 (16) (62)
 6 — ZnS,cF8,216 (9) (51)
 7 — CuAu,tP2,123 (44) (42)
 8 — CuTi,tP2,123 (76) (34)
 9 — Cu,cF4,225 (3) (34)
10 — FeAs,oP8,62 (51) (30)
11 — ZnO,hP4,186 (23) (28)
12 — W,cI2,229 (2) (26)
13 — FeSi,cP8,198 (57) (25)
14 — Mg,hP2,194 (5) (25)
15 — NaPb,tI64,142 (207) (14)
16 — ZrCl,hR12,166 (365) (14)
17 — NaO,hP12,189 (208) (13)
18 — AuCd,oP4,51 (121) (10)
19 — WC,hP2,187 (138) (10)
20 — DyAl,oP16,57 (149) (10)

* Wrote these data into a file ("str_wo_temp.txt" for further references) with [a] | [b] | [structure] being the columns as in :
79  100 11
26  93  11
24  93  11
73  92  11
78  100 11
27  64  0
66  92  0
12  97  0
53  101 0
61  86  4
31  84  4
23  85  4
32  85  4
33  85  4
27  85  4

so it was piece of a cake (not that easy, actually 8) to plot this using gnuplot :

set term wxt
set palette rgb 33,13,10
set view map
splot "str_wo_temp.txt" with points palette pt 5 ps 0.6
set xtics rotate by 90
set xtics("He" 1, "Ne     " 2, "Ar" 3, "Kr     " 4, "Xe" 5, "Rn     " 6, "Fr" 7, "Cs     " 8, "Rb"   9, "K     " 10, "Na" 11, "Li     " 12, "Ra" 13, "Ba     " 14, "Sr" 15, "Ca     " 16, "Yb" 17,        "Eu     " 18, "Sc" 19, "Lu     " 20, "Tm" 21, "Er     " 22, "Ho" 23, "Dy     " 24, "Y" 25, "Tb     " 26, "Gd" 27, "Sm     " 28, "Pm" 29, "Nd     " 30, "Pr" 31, "Ce     " 32, "La" 33, "Lr     " 34, "No" 35, "Md     " 36, "Fm" 37, "Es     " 38, "Cf" 39, "Bk     " 40, "Cm" 41, "Am     " 42, "Pu" 43,      "Np     " 44, "U" 45, "Pa     " 46, "Th" 47, "Ac     " 48, "Zr" 49, "Hf     " 50, "Ti" 51, "Ta     " 52, "Nb" 53, "V     " 54, "W" 55, "Mo     " 56, "Cr" 57, "Re     " 58, "Tc" 59, "Mn     " 60, "Fe"   61, "Ru     " 62, "Os" 63, "Co     " 64, "Rh" 65, "Ir     " 66, "Ni" 67, "Pt     " 68, "Pd" 69,      "Au     " 70, "Ag" 71, "Cu     " 72, "Mg" 73, "Hg     " 74, "Cd" 75, "Zn     " 76, "Be" 77, "Tl      " 78, "In" 79, "Al     " 80, "Ga" 81, "Pb     " 82, "Sn" 83, "Ge     " 84, "Si" 85, "B     " 86,     "Bi" 87, "Sb     " 88, "As" 89, "P     " 90, "Po" 91, "Te     " 92, "Se" 93, "S     " 94, "C" 95,    "At     " 96, "I" 97, "Br     " 98, "Cl" 99, "N     " 100, "O" 101, "F     " 102, "H" 103)
set ytics("He" 1, "Ne     " 2, "Ar" 3, "Kr     " 4, "Xe" 5, "Rn     " 6, "Fr" 7, "Cs     " 8, "Rb"   9, "K     " 10, "Na" 11, "Li     " 12, "Ra" 13, "Ba     " 14, "Sr" 15, "Ca     " 16, "Yb" 17,        "Eu     " 18, "Sc" 19, "Lu     " 20, "Tm" 21, "Er     " 22, "Ho" 23, "Dy     " 24, "Y" 25, "Tb     " 26, "Gd" 27, "Sm     " 28, "Pm" 29, "Nd     " 30, "Pr" 31, "Ce     " 32, "La" 33, "Lr     " 34, "No" 35, "Md     " 36, "Fm" 37, "Es     " 38, "Cf" 39, "Bk     " 40, "Cm" 41, "Am     " 42, "Pu" 43,      "Np     " 44, "U" 45, "Pa     " 46, "Th" 47, "Ac     " 48, "Zr" 49, "Hf     " 50, "Ti" 51, "Ta     " 52, "Nb" 53, "V     " 54, "W" 55, "Mo     " 56, "Cr" 57, "Re     " 58, "Tc" 59, "Mn     " 60, "Fe"   61, "Ru     " 62, "Os" 63, "Co     " 64, "Rh" 65, "Ir     " 66, "Ni" 67, "Pt     " 68, "Pd" 69,      "Au     " 70, "Ag" 71, "Cu     " 72, "Mg" 73, "Hg     " 74, "Cd" 75, "Zn     " 76, "Be" 77, "Tl      " 78, "In" 79, "Al     " 80, "Ga" 81, "Pb     " 82, "Sn" 83, "Ge     " 84, "Si" 85, "B     " 86,     "Bi" 87, "Sb     " 88, "As" 89, "P     " 90, "Po" 91, "Te     " 92, "Se" 93, "S     " 94, "C" 95,    "At     " 96, "I" 97, "Br     " 98, "Cl" 99, "N     " 100, "O" 101, "F     " 102, "H" 103)
set xtics font "Helvatica,8"
set ytics font "Helvatica,8"
set xrange [0:103]
set yrange [0:103]
#set term postscript enhanced color
#set output "pettifor.ps"
replot

and voila (or the real one with the accent which I couldn’t manage to 8) ! So here is the updated Pettifor Map for you (and for the AB compositons 8) But mind you, it doesn’t contain possible entries after year 2000.

 

You can plot the "same" map with Octave somewhat as follows (since I’m a newbie in Octave myself, I’ll take you as far as I could go – from there on, you’re on your own 8)

First, load the data file into a matrix:

load str_wo_temp.txt

then map this matrix contents into another matrix with the first (a) and second (b) columns being the new matrix’s column and row number and the third column (structure) being the value:

for i = 1:length(str_wo_temp)

      A(str_wo_temp(i,1),str_wo_temp(i,2)) = str_wo_temp(i,3);

endfor

now, finally we can plot the density matrix:

imagesc(A)

which produces:

Oh, don’t be so picky about the labels and that thing called "legend"! 8) (By the way, if you know how to add those, please let me know, too – I haven’t started googling around the issue for the moment..)

 

While I was at it, I also plotted a 3-D Pettifor Map with the z-axis representing the temperature at which the structure was determined. This time, instead of saving all the data into one file, I saved the [a] [b] [temp] values into "structure name" files. Then, I picked up the most populated structure types (i.e., the ones contating the most lines) and moved the remaining into a temporary folder where I issued the

cat *.txt > others.txt

command. Then plotted this 3D map via gnuplot with the following command:
splot "NaCl,cF8,225.txt", "CsCl,cP2,221.txt", "TlI,oS8,63.txt", "FeB-b,oP8,62.txt", "NiAs,hP4,194.txt", "ZnS,cF8,216.txt", "FeAs,oP8,62.txt", "Cu,cF4,225.txt", "W,cI2,229.txt", "CuTi,tP2,123.txt", "CuAu,tP2,123.txt", "ZnO,hP4,186.txt", "Mg,hP2,194.txt", "FeSi,cP8,198.txt", "GeS,oP8,62.txt", "NaPb,tI64,142.txt", "AuCd,oP4,51.txt", "ZrCl,hR12,166.txt", "NaO,hP12,189.txt", "CuI,cF20,216.txt", "DyAl,oP16,57.txt", "InBi,tP4,129.txt", "PbO,tP4,129.txt", "CoO,tI4,139.txt", "WC,hP2,187.txt", "MoC,hP8,194.txt", "NaTl,cF16,227.txt", "GeTe,hR6,160.txt", "Sn,tI4,141.txt", "HgIn,hR6,166.txt", "In,tI2,139.txt", "PdBi,mP16,4.txt", "Nd,hP4,194.txt", "CuTi,tP4,129.txt", "NaP,oP16,19.txt", "HgS,hP6,152.txt", "CoSn,hP6,191.txt", "KGe,cP64,218.txt", "HgCl,tI8,139.txt", "AgI,cI38,229.txt", "others.txt"

  Here are two snapshots:

 

I’m planning to include a java version of this 3D map where you can more easily read what you are looking…

]]> http://www.emresururi.com/physics/?feed=rss2&p=63 0 Protected: mP12, oP12, tP6 http://www.emresururi.com/physics/?p=59 http://www.emresururi.com/physics/?p=59#respond Mon, 14 Jan 2008 13:05:10 +0000 http://www.emresururi.com/physics/?p=59

This content is password protected. To view it please enter your password below:

]]> http://www.emresururi.com/physics/?feed=rss2&p=59 0 Less is More MySQL http://www.emresururi.com/physics/?p=58 http://www.emresururi.com/physics/?p=58#respond Wed, 09 Jan 2008 09:26:33 +0000 http://www.emresururi.com/physics/?p=58 (-Right now, I’m going to define a new category: the MySQL category!-)

Suppose that, you have a database which contains a huge number of entries about the materials (like, for instance, the Pauling Database). Let it have 163 different properties we can query about. It is optimized for queries, so, the values are enumerated and the labels for these are kept in "pauling.dblxxx" tables, which may be something like this for the "Chemical System" property:

The values are kept in "pauling.valxxx" tables:

(Where the first one is the EntryCode, the PRIMARY key that relates all the tables and the second value is the enumeration for the value. For example, 1422 for the 13. property is actually Ho-Ir 🙂

and there is one more set of tables, the paulingv2.valxxx tables which are stored and keyed in val order, so:

  • if we want to find the EntryCodes corresponding to a given property, we query the paulingv2.valxxx tables
  • if we want to find the property that is corresponding to a given EntryCode we query the pauling.valxxx tables
  • if we want to "translate" the property’s enumeration, we query the pauling.dblxxx tables.

Here is the thing: Let’s say that we want the Structure Types that have an Atomic Enviroment Type (AET) of a rhombic dodecahedron , with a/b ratio 1, alpha=beta=90o. The property numbers for these are:

Structure Type : 32
AET : 86
a/b ratio : 44
alpha : 41
beta : 42

Since the last three properties are numeric, we don’t enumerate them and the enumeration corresponding to the rhombic dodecahedron for AET is 6. So, fasten your seat belts, we are about to lift off! :

SELECT id,pauling.dbl032.val FROM pauling.dbl032
  INNER JOIN
  (
   SELECT DISTINCT val FROM val032
   INNER JOIN
   (
   SELECT v AS EntryCode FROM
   (
    (
     SELECT val001.val AS v FROM val001
     INNER JOIN paulingv2.val086
     USING (EntryCode)
     WHERE paulingv2.val086.val=6
    ) AS A
    INNER JOIN
    (
     (
      SELECT val001.val AS v FROM val001
      INNER JOIN paulingv2.val044
      USING (EntryCode)
      WHERE paulingv2.val044.val=1
     ) AS B
     INNER JOIN
     (
      # 41=90 && 42= 90
      (
       SELECT val001.val AS v FROM val001
       INNER JOIN paulingv2.val041
       USING (EntryCode)
       WHERE paulingv2.val041.val=90
      ) AS C
      INNER JOIN
      (
       SELECT val001.val AS v FROM val001
       INNER JOIN paulingv2.val042
       USING (EntryCode)
       WHERE paulingv2.val042.val=90
      ) AS D
      USING (v)
     )
     USING (v)
    )
    USING (v)
   )
   ) AS G
   USING (EntryCode)
  )
  AS Q ON (id = Q.val) ORDER BY val;

Later addition: Assuming 86=6; we don’t really need the other constraints 44=1, 42=90, 41=90 – do we?… So it’s just the boring:

SELECT id FROM pauling.dbl032
  INNER JOIN
  (
   SELECT DISTINCT val FROM val032
   INNER JOIN
   (
   SELECT v AS EntryCode FROM
   (
    (
     SELECT val001.val AS v FROM val001
     INNER JOIN paulingv2.val086
     USING (EntryCode)
     WHERE paulingv2.val086.val=6
    ) AS A
   )
   ) AS G
   USING (EntryCode)
  )
  AS Q ON (id = Q.val) ORDER BY Q.val;

The result of this is something like this:

To be honest, it is actually something like this :

The strange symbols are the price we pay for using non-standard charsets! 😉

So, to tidy up, I import this  to a table with the following structure:

CREATE TABLE IF NOT EXISTS `bcc` (
  `id` smallint(5) unsigned NOT NULL default ‘0’,
  `val` varchar(254) NOT NULL default ”,
  `usagecount` smallint(5) unsigned NOT NULL default ‘0’,
  `val1` varchar(30) NOT NULL default ‘0’,
  `val2` varchar(6) default NULL,
  `val3` varchar(3) default NULL,
  `val1t` varchar(30) NOT NULL,
  `val2sp` smallint(5) unsigned NOT NULL,
  `SG` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `val` (`val`),
  KEY `val1` (`val1`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

You have already met the id and val columns. "usagecount" will be imported from the pauling.dbl013 table; val1, val2, val3 are the seperated structure type information (ie, for "CuTi,tp4,129", val1="CuTi", val2="tp4" and val3="129"); val1t is the "translated" version of val1 which is the readeable one (ie, "(Ag¡•¦§Zn¡•¥¥)©Zn" is translated as "(Ag0.56Zn0.44)8Zn"). The translation is done via the following simpe php function:

function translate_symbols($string,$f_tr2symb=true)
{
$symbol_array = array("•","¡","¢","£","¤","¥","¦","§","¨","©","ª");//subscript values
$transl_array = array(".","0","1","2","3","4","5","6","7","8","9");//subscript values

if(!$f_tr2symb)return str_replace($transl_array,$symbol_array,$string);
else return str_replace($symbol_array,$transl_array,$string);
}

"val2sp" is the sliced numeric value from the Pearson Symbol stored in the val2 column.

To tidy up:

$query = "SELECT id, val1, val2 FROM bcc";
$qresult = mysql_query($query);

while($result = mysql_fetch_array($qresult))
{
 $query2 = "UPDATE bcc SET val1t=\"".translate_symbols($result["val1"])."\", val2sp=SUBSTRING(val2,3,20) WHERE id = ".$result["id"]." LIMIT 1";
 //echo $query2."\n";
 $q2result = mysql_query($query2);
 echo mysql_error();
}

You can refer to my previous entry for slicing up the "val" column. I had already done this while constructing the pauling.dblxxx tables, so in fact the actual view of the pauling.dbl032 table is the following one

meaning, I can just import them using the id’s of my new table:

UPDATE bcc, pauling.dbl032 SET
bcc.val = pauling.dbl032.val,
bcc.usagecount = pauling.dbl032.usagecount,
bcc.val1 = pauling.dbl032.val1,
bcc.val2 = pauling.dbl032.val2,
bcc.val3 = pauling.dbl032.val3
WHERE bcc.id = pauling.dbl032.id

Now we have something like:

We still have some work to do. Let’s take the two structures Ni2Al and Ni2In. Say that we are looking for superstructures, which have the property that the "atoms occupy atomic positions according to the parent crystal structure". The AET for Ni2Al is given as 14-b;14-b;14-b; whereas the AET for Ni2In is given as 11-a;11-a;14-b; . We want every atom to have the parent crystal structure (14-b – the rhombic dodecahedron for bcc), so we will eliminate those ones that have other AET.

mysql_query("USE pauling");
$ids_q = mysql_query("SELECT id FROM s07pt.bcc");
while($ids = mysql_fetch_row($ids_q))
{
 $id= $ids[0];
 $query = "
 SELECT COUNT(DISTINCT val) FROM pauling.val032
 INNER JOIN
  (
         SELECT v AS EntryCode FROM
          (
                 SELECT pauling.val001.val AS v FROM pauling.val001
                 INNER JOIN paulingv2.val032
                 USING (EntryCode)
                 WHERE paulingv2.val032.val = ".$id."
          ) AS A
         INNER JOIN
          (
                 SELECT pauling.val001.val AS v FROM pauling.val001
                 INNER JOIN paulingv2.val086
                 USING (EntryCode)
                 WHERE paulingv2.val086.val != 6
          ) AS B
  USING (v)
 ) as C USING (EntryCode)";

 $query = mysql_query($query);
 $result = mysql_fetch_row($query);
 $result = mysql_result($query,0);
 $result = ($result+1)%2;
 $query = "UPDATE s07pt.bcc SET incl_theo = $result where id = $id LIMIT 1";
 $j++;
 echo $j.".\t".$query."\n";
 mysql_query($query);
}

"incl_theo" is the column which is equal to 1 if the structure in question contains no AET other than 14-b, 0 otherwise. This gives us smt. like:

 

]]> http://www.emresururi.com/physics/?feed=rss2&p=58 0 Boasting? I guess so… 8) http://www.emresururi.com/physics/?p=57 http://www.emresururi.com/physics/?p=57#comments Fri, 21 Dec 2007 09:10:09 +0000 http://www.emresururi.com/physics/?p=57 Suppose that you’ve collected some data from the output of a program. Let’s say that some part of this data consists of Author names something similar to:

You want to split the initials from the surnames. This is piece of cake with PHP but I don’t want to go parsing each row of which there are many… So, take a look at this ugly beauty:

 

UPDATE dbl004 set val1 = IF(LOCATE(".",val),TRIM(SUBSTRING(SUBSTRING_INDEX(val,".",1),1, LENGTH(SUBSTRING_INDEX(val,".",1)) – LENGTH(SUBSTRING_INDEX(SUBSTRING_INDEX(val,".",1)," ",-1)))),val), val2 = IF(LOCATE(".",val), TRIM(SUBSTRING(val,LENGTH(SUBSTRING_INDEX(val,".",1)) – LENGTH(SUBSTRING_INDEX(SUBSTRING_INDEX(val,".",1)," ",-1)))),"");

aaaaand here is what you get:

if you are thinking something similar to

 

UPDATE dbl004 SET val1 = LEFT(val,LOCATE(" ",val)-1), val2 = RIGHT(val,LENGTH(val)-LOCATE(" ",val));

or

UPDATE dbl004 set val1 = TRIM(SUBSTRING(SUBSTRING_INDEX(val,".",1),1,LENGTH(SUBSTRING_INDEX(val,".",1)) – LENGTH(SUBSTRING_INDEX(SUBSTRING_INDEX(val,".",1)," ",-1)))), val2 = TRIM(SUBSTRING(val, LENGTH(SUBSTRING_INDEX(val,".",1)) – LENGTH(SUBSTRING_INDEX(SUBSTRING_INDEX(val,".",1)," ",-1))));

Try to process these 3 values: "van der Graaf K.L. Jr.", "Not Available" and "Editor".

About this entry: I couldn’t refrain myself from boasting after I managed to come up with that beautiful MySQL query… sorry for that. (Yes, I know, superbia, the 7th and the most deadly…) So let me try to balance this arrogant entry of mine:

With my best regards,
Your humble blogger…

]]> http://www.emresururi.com/physics/?feed=rss2&p=57 4