The Seven Levels of SQL

This just absolutely amazes me.
RJ Writes, "When I first began working at the company in question, I was trying to get a handle on the flow of database queries during a fund transfer, so I enabled query logging, and stepped through an example transaction. (Platform, PHP/MySQL on Linux). For any who read this: DON'T PANIC - the system never saw real production use until after it was completely revamped in a proper DBMS.

Order of SQL Queries for "Dispatcher sending funds" transaction:

Example transaction: customer sends funds from US location to overseas location in Buenos Aires, Argentina, which will then disburse the money to a 3rd party.

1. user logs in: (POST from /php/dispatcher_log.php to /php/members.php, redirects to /forms/welcome.php upon success)
  a. select from 'uaccount'
  b. insert into 'session', 'log'
  c. select from 'profile'
(didn't bother with details on these queries)

2. user clicks on 'Dispatcher' (/forms/dispatcher_menu.php)
  a.  select email, balance,available,type from uaccount where uid=2192
  b.  select valid from bankinfo where uid=2192
  c.  select email, balance,available,type from uaccount where uid=2192
  d.  select date_format(curdate(),'%W,  %M %D,  %Y')

3. user clicks on 'Sending Money' (/php/dispatcher_tx-1.2.php)
  a. select type from uaccount where uid=2192
  b. CREATE TABLE IF NOT EXISTS `Dispatchers_tmp`
  c. insert into Dispatchers_tmp (tid) values('0351634373');
  d. select tid from Dispatchers_tmp where tid='0351634373'
  e. select * from Dispatchers_tmp where tid='0351634373'
  f. select * from Dispatchers_tmp where tid='0351634373'
  g. select disp_rx_id from Dispatchers_tmp where tid='0351634373'
  h. select email, balance,available,type from uaccount where uid=2192
  i. select valid from bankinfo where uid=2192
  j. select email, balance,available,type from uaccount where uid=2192
  k. select date_format(curdate(),'%W,  %M %D,  %Y')

4. user enters remote location (ZIP or city!?! -- isn't there a better way?)
  a. select type from uaccount where uid=2192
  b. CREATE TABLE IF NOT EXISTS `Dispatchers_tmp`
  c. select tid from Dispatchers_tmp where tid='0351634373'
  d. select profile.* from profile
        left join uaccount on profile.uid = uaccount.uid
        where (city like '%Buenos Aires%' or zipcode like '%Buenos Aires%')
        and uaccount.type='102' and uaccount.uid <> 2192

  e. select email, balance,available,type from uaccount where uid=2192
  f. select valid from bankinfo where uid=2192
  g. select email, balance,available,type from uaccount where uid=2192
  h. select date_format(curdate(),'%W,  %M %D,  %Y')

5. user clicks on a remote dispatcher (still at /php/dispatcher_tx-1.2.php)
  a. select type from uaccount where uid=2192
  b. CREATE TABLE IF NOT EXISTS `Dispatchers_tmp`
  c. select tid from Dispatchers_tmp where tid='0351634373' (keeps state of last transaction # even after logout/login)
  d. update Dispatchers_tmp set disp_rx_id ='2191' where tid='0351634373'
  e. select profile.* from profile
        left join uaccount on profile.uid = uaccount.uid
        where (city like '%%' or zipcode like '%%')
        and uaccount.type='102' and uaccount.uid <> 2192

  f. select email, balance,available,type from uaccount where uid=2192
  g. select valid from bankinfo where uid=2192
  h. select email, balance,available,type from uaccount where uid=2192 (again?)
  i. select date_format(curdate(),'%W,  %M %D,  %Y')

6. (Again!? must be a page refresh here, to dispatcher_tx-1.2.php?action=amount, because everything starts again)
  a. select type from uaccount where uid=2192
  b. CREATE TABLE IF NOT EXISTS `Dispatchers_tmp'
  c. select tid from Dispatchers_tmp where tid='0351634373'
  d.   select * from Dispatchers_tmp where tid='0351634373'
  e.   select * from Dispatchers_tmp where tid='0351634373'
  f.   select disp_rx_id from Dispatchers_tmp where tid='0351634373'
  g.   select profile.fname,profile.mname,profile.lname,profile.address1,profile.address2,profile.city,profile.zipcode,uaccount.email
      from profile LEFT JOIN uaccount ON profile.uid=uaccount.uid where uaccount.uid=2191

  h.   select amount from Dispatchers_tmp where tid='0351634373'
  i.   select sender_id from Dispatchers_tmp where tid='0351634373'
  j.   select receptor_id from Dispatchers_tmp where tid='0351634373'
  k.   select email, balance,available,type from uaccount where uid=2192
  l.   select valid from bankinfo where uid=2192
  m.   select email, balance,available,type from uaccount where uid=2192
  n.   select date_format(curdate(),'%W,  %M %D,  %Y')

7. user enters amount, submits (to /php/dispatcher_tx-1.2.php?action=search_sender)
  a. select type from uaccount where uid=2192
  b. CREATE TABLE IF NOT EXISTS `Dispatchers_tmp'
  c. select tid from Dispatchers_tmp where tid='0351634373'
  d. select * from Dispatchers_tmp where tid='0351634373'
  e. select * from Dispatchers_tmp where tid='0351634373'
  f. select disp_rx_id from Dispatchers_tmp where tid='0351634373'
  g. select profile.fname,profile.mname,profile.lname,profile.address1,profile.address2,profile.city,profile.zipcode,uaccount.email
      from profile LEFT JOIN uaccount ON profile.uid=uaccount.uid where uaccount.uid=2191
  h. update Dispatchers_tmp set amount = 34 where tid = '0351634373';
  i. select amount from Dispatchers_tmp where tid='0351634373'
  j. select sender_id from Dispatchers_tmp where tid='0351634373'
  k. select receptor_id from Dispatchers_tmp where tid='0351634373'
  l. select email, balance,available,type from uaccount where uid=2192
  m. select valid from bankinfo where uid=2192
  n. select email, balance,available,type from uaccount where uid=2192
  o. select date_format(curdate(),'%W,  %M %D,  %Y')

8. user enters new uaccount for receptor (submits to /php/dispatcher_tx-1.2.php)
  a. select type from uaccount where uid=2192
  b. CREATE TABLE IF NOT EXISTS `Dispatchers_tmp` ...
  c. select tid from Dispatchers_tmp where tid='0103252648'
  d. LOCK TABLES uaccount WRITE
       insert into uaccount values (null,'13jQ8ghw9b','[email protected]','13jQ8ghw9b','N',0,0,100,0,'0.20','0.20','0.02','0.20','')
       UNLOCK TABLES
  e. LOCK TABLES profile WRITE
       insert into profile (uid,fname,lname,address1,address2,city,state,zipcode,countrycode,nphone,secretquestion,secretanswer)
       values (3278,'tester','testingperson','123 Santander','','Buenos Aires','Argentina','00000','CO','011-456450546','uaccount opened by','2192')
       UNLOCK TABLES
  f. LOCK TABLES Dispatchers_tmp WRITE
       update Dispatchers_tmp set receptor_id ='3278' where tid='0103252648'
       UNLOCK TABLES
  h. select * from Dispatchers_tmp where tid='0103252648'
  i. select disp_rx_id from Dispatchers_tmp where tid='0103252648'
  j. select profile.fname,profile.mname,profile.lname,profile.address1,profile.address2,profile.city,profile.zipcode,uaccount.email
       from profile LEFT JOIN uaccount ON profile.uid=uaccount.uid where uaccount.uid=2191
  k. select amount from Dispatchers_tmp where tid='0103252648'
  l. select sender_id from Dispatchers_tmp where tid='0103252648'
  m. select profile.fname,profile.mname,profile.lname,profile.address1,profile.address2,profile.city,profile.zipcode,uaccount.email
        from profile LEFT JOIN uaccount ON profile.uid=uaccount.uid where uaccount.uid=3277
  n. select receptor_id from Dispatchers_tmp where tid='0103252648'
  o. select profile.fname,profile.mname,profile.lname,profile.address1,profile.address2,profile.city,profile.zipcode,uaccount.email
        from profile LEFT JOIN uaccount ON profile.uid=uaccount.uid where uaccount.uid=3278
  p. select email, balance,available,type from uaccount where uid=2192
  q. select valid from bankinfo where uid=2192
  r. select email, balance,available,type from uaccount where uid=2192
  s. select date_format(curdate(),'%W,  %M %D,  %Y')

9. (I guess page re-directs, because it starts all over again:)
  a. select type from uaccount where uid=2192
  b. CREATE TABLE IF NOT EXISTS `Dispatchers_tmp` (
  c. select tid from Dispatchers_tmp where tid='0103252648'
  d. select * from Dispatchers_tmp where tid='0103252648'
  e. select * from Dispatchers_tmp where tid='0103252648'
  f. select disp_rx_id from Dispatchers_tmp where tid='0103252648'
  g. select profile.fname,profile.mname,profile.lname,profile.address1,profile.address2,profile.city,profile.zipcode,uaccount.email
        from profile LEFT JOIN uaccount ON profile.uid=uaccount.uid where uaccount.uid=2191
  h. select amount from Dispatchers_tmp where tid='0103252648'
  i. select sender_id from Dispatchers_tmp where tid='0103252648'
  j. select profile.fname,profile.mname,profile.lname,profile.address1,profile.address2,profile.city,profile.zipcode,uaccount.email
        from profile LEFT JOIN uaccount ON profile.uid=uaccount.uid where uaccount.uid=3277
  k. select receptor_id from Dispatchers_tmp where tid='0103252648'
  l. select profile.fname,profile.mname,profile.lname,profile.address1,profile.address2,profile.city,profile.zipcode,uaccount.email
        from profile LEFT JOIN uaccount ON profile.uid=uaccount.uid where uaccount.uid=3278
  m. select email, balance,available,type from uaccount where uid=2192
  n. select valid from bankinfo where uid=2192
  o. select email, balance,available,type from uaccount where uid=2192
  p. select date_format(curdate(),'%W,  %M %D,  %Y')

10. (and I guess page re-directs again, ending up at /php/dispatcher_tx-1.2.php?action=confirm)
  a. select type from uaccount where uid=2192
  b. CREATE TABLE IF NOT EXISTS `Dispatchers_tmp` (...
  c. select tid from Dispatchers_tmp where tid='0103252648'
  d. select * from Dispatchers_tmp where tid='0103252648'
  e. select disp_rx_id from Dispatchers_tmp where tid='0103252648'
  f. select profile.fname,profile.mname,profile.lname,profile.address1,profile.address2,profile.city,profile.zipcode,uaccount.email
        from profile LEFT JOIN uaccount ON profile.uid=uaccount.uid where uaccount.uid=2191
  g. select amount from Dispatchers_tmp where tid='0103252648'
  h. select sender_id from Dispatchers_tmp where tid='0103252648'
  i. select profile.fname,profile.mname,profile.lname,profile.address1,profile.address2,profile.city,profile.zipcode,uaccount.email
        from profile LEFT JOIN uaccount ON profile.uid=uaccount.uid where uaccount.uid=3277
  j. select receptor_id from Dispatchers_tmp where tid='0103252648'
  k. select profile.fname,profile.mname,profile.lname,profile.address1,profile.address2,profile.city,profile.zipcode,uaccount.email
        from profile LEFT JOIN uaccount ON profile.uid=uaccount.uid where uaccount.uid=3278
  l. select email, balance,available,type from uaccount where uid=2192
  m. select valid from bankinfo where uid=2192
  n. select email, balance,available,type from uaccount where uid=2192
  o. select date_format(curdate(),'%W,  %M %D,  %Y')

11. Now, from here we have the final "sending" confirmation (POSTs either $submit or $delete to //php/dispatcher_tx-1.2.php)
    (redirects to printing page, and then back to beginning "Send" form.)
  a. select type from uaccount where uid=2192
  b. CREATE TABLE IF NOT EXISTS `Dispatchers_tmp` (...
  c. select tid from Dispatchers_tmp where tid='0103252648'
  d. select * from Dispatchers_tmp where tid='0103252648'
  e. select uaccountno from uaccount where uid='2192'
  f. select uaccountno from uaccount where uid='2191'
  g. LOCK TABLES trans WRITE
        insert into trans values(null,'0103252648','6GjSid8KoJ','34f9YC78AZ','0102',42.45,'USD',20020128122249,null,'Y',null)
        UNLOCK TABLES
  h. LOCK TABLES uaccount WRITE
        update uaccount set balance=balance-42.45,available=available-42.45 where uid=2192
        UNLOCK TABLES
  i. LOCK TABLES uaccount WRITE
        update uaccount set balance=balance+42.45,available=available+42.45 where uid=2191
        UNLOCK TABLES
  j. LOCK TABLES trans WRITE
        insert into trans values(null,'0103252648','6GjSid8KoJ','34f9YC78AZ','0102',1,'USD',20020128122249,null,'Y',null)
        UNLOCK TABLES
  k. LOCK TABLES uaccount WRITE
        update uaccount set balance=balance-1,available=available-1 where uid=2192
        UNLOCK TABLES
  l. LOCK TABLES uaccount WRITE
        update uaccount set balance=balance+1,available=available+1 where uid=2191
        UNLOCK TABLES
  m. LOCK TABLES trans WRITE
        insert into trans values(null,'0103252648','6GjSid8KoJ','TESTAC0002','0102',1,'USD',20020128122249,null,'Y',null)
        UNLOCK TABLES
  n. LOCK TABLES uaccount WRITE
        update uaccount set balance=balance-1,available=available-1 where uid=2192
        UNLOCK TABLES
  o. LOCK TABLES dispatchers_trans WRITE, Dispatchers_tmp READ
        insert into dispatchers_trans select * from Dispatchers_tmp where tid='0103252648'
        UNLOCK TABLES
  p. select * from dispatchers_trans where RemID='123'
  q. select * from profile where uid=2192
  r. select * from profile where uid=2191
  s. select * from profile where uid=3277
  t. select * from profile where uid=3278
  u. select * from uaccount where uid=2192
  v. select * from uaccount where uid=2191
  w. select * from uaccount where uid=3277
  x. select * from uaccount where uid=3278
  y. select * from uaccount where uid=3278
  z. select date_format('20020128122249','%M %d, %Y %H:%i:%s')
  zz.select date_format('','%M %d, %Y %H:%i:%s')

12. Re-direct to printing page
  a. select type from uaccount where uid=2192
  b. CREATE TABLE IF NOT EXISTS `Dispatchers_tmp` (...
  c. select tid from Dispatchers_tmp where tid='0103252648'
  d. select * from Dispatchers_tmp where tid=''
  e. select * from Dispatchers_tmp where tid=''
  f. select disp_rx_id from Dispatchers_tmp where tid=''
  g. select email, balance,available,type from uaccount where uid=2192
  h. select valid from bankinfo where uid=2192
  i. select email, balance,available,type from uaccount where uid=2192
  j. select date_format(curdate(),'%W,  %M %D,  %Y')

13. Re-direct back to main "Sending" page
  a. select type from uaccount where uid=2192
  b. CREATE TABLE IF NOT EXISTS `Dispatchers_tmp` (...
  c. select tid from Dispatchers_tmp where tid='0351454703'
  d. LOCK TABLES Dispatchers_tmp WRITE
      insert into Dispatchers_tmp (tid) values('0351454703')
      update Dispatchers_tmp set disp_tx_id ='2192' where tid='0351454703'
      UNLOCK TABLES
  e. select * from Dispatchers_tmp where tid='0351454703'
  f. select * from Dispatchers_tmp where tid='0351454703'
  g. select disp_rx_id from Dispatchers_tmp where tid='0351454703'
  h. select email, balance,available,type from uaccount where uid=2192
  i. select valid from bankinfo where uid=2192
  j. select email, balance,available,type from uaccount where uid=2192
  k. select date_format(curdate(),'%W,  %M %D,  %Y')

Final count: 134 SELECT statements 26 LOCK TABLE statements 11 CREATE TABLE statements 9 INSERT statements 9 UPDATE statements

Note: it might also be amusing to also bear in mind that "tid", or Transaction ID, was not an auto-generated number in the database, since the DB designer elected not to create a table for master records and a sub-table for actual monetary value transfers. Thus, the major 'trans' table ended up with at least 3 rows for any one transaction, ERGO, you could not make "tid" an auto-generated primary key. They settled for a nifty little PHP function which would generate a text string of 10 random digits. Well, actually it was only 9 random digits, because the first digit always returned as "0". By the time I arrived, they were already getting corruption from duplicate transaction IDs.

[Advertisement] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!