SQL Antipatterns - bugs database

-- set this up on web page so I could copy/paste
-- to my android...

-- create database bugs;
-- use bugs;

DROP TABLE IF EXISTS Accounts;
DROP TABLE IF EXISTS BugStatus;
DROP TABLE IF EXISTS Bugs;
DROP TABLE IF EXISTS Comments;
DROP TABLE IF EXISTS Screenshots;
DROP TABLE IF EXISTS Tags;
DROP TABLE IF EXISTS Products;
DROP TABLE IF EXISTS BugsProducts;

#START:create
CREATE TABLE Accounts (
  account_id        SERIAL PRIMARY KEY,
  account_name      VARCHAR(20),
  first_name        VARCHAR(20),
  last_name         VARCHAR(20),
  email             VARCHAR(100),
  password_hash     CHAR(64),
  portrait_image    BLOB,
  hourly_rate       NUMERIC(9,2)
);

CREATE TABLE BugStatus (
  status            VARCHAR(20) PRIMARY KEY
);

CREATE TABLE Bugs (
  bug_id            SERIAL PRIMARY KEY,
  date_reported     DATE NOT NULL,
  summary           VARCHAR(80),
  description       VARCHAR(1000),
  resolution        VARCHAR(1000),
  reported_by       BIGINT UNSIGNED NOT NULL,
  assigned_to       BIGINT UNSIGNED,
  verified_by       BIGINT UNSIGNED,
  status            VARCHAR(20) NOT NULL DEFAULT 'NEW',
  priority          VARCHAR(20),
  hours             NUMERIC(9,2),
  FOREIGN KEY (reported_by) REFERENCES Accounts(account_id),
  FOREIGN KEY (assigned_to) REFERENCES Accounts(account_id),
  FOREIGN KEY (verified_by) REFERENCES Accounts(account_id),
  FOREIGN KEY (status) REFERENCES BugStatus(status)
);

CREATE TABLE Comments (
  comment_id        SERIAL PRIMARY KEY,
  bug_id            BIGINT UNSIGNED NOT NULL,
  author            BIGINT UNSIGNED NOT NULL,
  comment_date      DATETIME NOT NULL,
  comment           TEXT NOT NULL,
  FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id),
  FOREIGN KEY (author) REFERENCES Accounts(account_id)
);

CREATE TABLE Screenshots (
  bug_id            BIGINT UNSIGNED NOT NULL,
  image_id          BIGINT UNSIGNED NOT NULL,
  screenshot_image  BLOB,
  caption           VARCHAR(100),
  PRIMARY KEY      (bug_id, image_id),
  FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id)
);

CREATE TABLE Tags (
  bug_id            BIGINT UNSIGNED NOT NULL,
  tag               VARCHAR(20) NOT NULL,
  PRIMARY KEY      (bug_id, tag),
  FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id)
);

CREATE TABLE Products (
  product_id        SERIAL PRIMARY KEY,
  product_name      VARCHAR(50)
);

CREATE TABLE BugsProducts(
  bug_id            BIGINT UNSIGNED NOT NULL,
  product_id        BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY      (bug_id, product_id),
  FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id),
  FOREIGN KEY (product_id) REFERENCES Products(product_id)
);
#END:create

INSERT INTO Accounts () VALUES ();

INSERT INTO Products (product_id, product_name) VALUES
  (1, 'Open RoundFile'),
  (2, 'Visual TurboBuilder'),
  (3, 'ReConsider');

INSERT INTO Bugs (bug_id, summary) VALUES
  (1234, 'crash when I save'),
  (2345, 'increase performance'),
  (3456, 'screen goes blank'),
  (5678, 'unknown conflict between products');

INSERT INTO BugsProducts (bug_id, product_id) VALUES
  (1234, 1),
  (1234, 3),
  (3456, 2),
  (5678, 1),
  (5678, 3);

INSERT INTO Comments (comment_id, bug_id, comment) VALUES
  (6789, 1234, 'It crashes!'),
  (9876, 2345, 'Great idea!');

INSERT INTO Tags () VALUES ();

show_r.ksh

Handy Subversion script. Pass it a revision number and it shows the login comment ~ related
files.

 
xxx::/home/mbxxxxx/apps/datamodules/datamodules/trunk> . _CMD/show_r.ksh 114
------------------------------------------------------------------------
r114 | mbee | 2013-12-02 14:34:30 -0600 (Mon, 02 Dec 2013) | 1 line

null matrix id
------------------------------------------------------------------------ 

... 

    114 mbee             690 Dec 02 14:34 _CMD/process_null_matrix_ids.ksh
    114 mbee            9527 Dec 02 14:34 _CMD/pull_push_null_matrix_id_updates.ksh
    114 mbee            8553 Dec 02 14:34 _CMD/pull_rollback_of_matrix_id_updates.ksh
xxx::/home/mbxxxx/apps/datamodules/datamodules/trunk> cat _CMD/show_r.ksh
svn log -r $1
svn list -v -r $1 --depth=infinity|grep "$1 "

Copy_2_ro_folder.vbs

This copies a powerpivot model from it's source to a Read-Only target. The slicers and other pivot interactions still work the same -- but, users can't alter the sheet (directly).

Using PowerWindow table properties vs refresh...

I find using 'table properties' a better option for refreshing tables in the power window. Some of the tables are quite large -- and, some of our models have a lot of tables. Using table properties allows me to see _when_ a table was last updated; and, then, if choose to, I can _save_ to refresh the table. I'm investigating a way to capture this window information to add as a .csv imported table.

Talking Clipboard (for Android)

This is an ok tool. If you've some long text to get through - copy to the clipboard and it can read it back to you. I find this helpful. The program has a few quirks: it doesn't appear in your list of running apps - and there's no direct way to stop it (short of rebooting your device). However, even so it's handy you can give it a webpage to read back to you or it monitors the clipboard and can read what you copy to it.

Safari books online - download if you prefer

You can download material in epub and have it readable in your FireFox or SeaMonkey web browser. I much prefer this. I notice too, that my Android will bring up the epub in my favorite book reader -- moonreader.

Highlights from : From Scrum Shortcuts without Cutting Corners/Agile Tactics, Tools, & Tips / Ilan Goldstein...

Sometimes, I like reading from the end of the story('a snark was a boojum you see'). This book appears to have come from introspection after having finished many a game. Start anywhere. It's a non-sequential smorgasbord - worth your time. It will help from making false starts/mis-fires into scrum.

Google it

What they say on goodreads

Highlights

Shortcut 2: Fragile Agile /

Possibly one of the most frustrating comments that I hear when speaking to novice software teams is, “We do Scrum—we work in sprints, we have a daily scrum, and we even have a product backlog.” In addition, although they may not explicitly say it, you can often add, “We don’t write any documentation, we release haphazardly, we plan on the fly, and we don’t care about buggy code because we’ll just fix it up with a bug iteration.” ARGH! These people give Scrum a terrible name, and worse still, when their projects inevitably fail, it is very difficult, if not impossible, to win back the senior stakeholders who have been burnt by a badly warped Scrum implementation.

TOC


 Chapter 1. Scrum Startup
     Shortcut 1: Scrum on the Pitch
     Shortcut 2: Fragile Agile
     Shortcut 3: Creative Comfort
     Wrap Up
 Chapter 2. Attitudes and Abilities
     Shortcut 4: Masterful ScrumMaster
     Shortcut 5: Rock Stars or Studio Musicians?
     Shortcut 6: Picking Your Team Line-Up
     Wrap Up
 Chapter 3. Planning and Protecting
     Shortcut 7: Setting the Scrum Stage
     Shortcut 8: Plan the Sprint, Sprint the Plan
     Shortcut 9: Incriminating Impediments
     Wrap Up
 Chapter 4. Requirement Refinement
     Shortcut 10: Structuring Stories
     Shortcut 11: Developing the Definition of Done
     Shortcut 12: Progressive Revelations
     Wrap Up
 Chapter 5. Establishing Estimates
     Shortcut 13: Relating to Estimating
     Shortcut 14: Planning Poker at Pace
     Shortcut 15: Transitioning Relatively
     Wrap Up
 Chapter 6. Questioning Quality
     Shortcut 16: Bah! Scrum Bug!
     Shortcut 17: We Still Love the Testers!
     Shortcut 18: Automation Nation
     Wrap Up
 Chapter 7. Monitoring and Metrics
     Shortcut 19: Metrics That Matter
     Shortcut 20: Outstanding Stand-Ups
     Shortcut 21: Taming the Task Board
     Wrap Up
 Chapter 8. Retros, Reviews, and Risks
     Shortcut 22: To-Dos for Your Sprint Reviews
     Shortcut 23: Retrospective Irrespective
     Shortcut 24: Risk Takers and Mistake Makers
     Wrap Up
 Chapter 9. Managing the Managers
     Shortcut 25: Perception Is Reality
     Shortcut 26: Our Lords and Masters
     Shortcut 27: Morphing Managers in the Matrix
     Wrap Up
 Chapter 10. Larger Lessons
     Shortcut 28: Scrum Rollout Reckoning
     Shortcut 29: Eyes on the Prize
     Shortcut 30: Shortcut to the Final Level
     Final Wrap Up
 References

http://www.agilemanifesto.org/

How to get to 'Advanced' tab in power pivot window:

When in PowerPivot window - click on the icon left/top under titlebar-- there should be a drop down that allows you to switch to the 'advanced mode' view tab.  I find this most useful for the managing 'perspectives'. I create two perspectives 'CORE' for tables that require regular updates and 'LKUP' for those tables that remain mostly static over time.  I then use AutoIt scripts to refresh the 'CORE' tables.

How-to PowerPivot (sans Sharepoint)

Without Sharepoint refreshing powerpivot sheets is an onerous task. To take some of the sting/pain out of it here's what I'm presently doing:

1) Setup DSN's locally (in my case MySQL with UID/PWD)

2) Setup CORE and LKUP perspectives (see advanced mode tab):

-CORE are core data tables that are used regularly
-LKUP are static (more or less) tables used in relations
-Both are lined up horizontally in each perspective

3) Create a series of AutoIt executables to:

- Open a passed powerpivot sheet (via a pivotxls var)
- Go to powerpivot window
- Go to diagram view choose 'CORE' view
- Refresh one or more tables(via a passed notabs variable)
- Save main sheet

4) Join the smaller executables in #3 into a single AutoIt executable call it RefPPivot.exe)

5) Now I have a script that functions in the form:

RefPPivot.exe pivotxlsx notabs

EXAMPLE:

RefPPivot.exe P:\accounting\zPowerPivot\myPowerStuff.xlsx 8

...would refresh the P:\accounting\zPowerPivot\myPowerStuff.xlsx's
8 core tables.

6) Schedule #5 through myLauncher.hta

This works tolerably well. I created another, pretty much identical script to #4 but use for LKUP updates(RefPPivotLKUP.exe).

KEY: When creating / building connections *don't* supply the uid/pwd - assign them rather in the DSN itself. This works well for mySQL -- but I think this may work for some other DB's as well (like Oracle). When you get to the 'next' prompt to create table / query - first chose the 'All' tab and persist the security info.

I have samples scripts and further suggestions if anyone's interested.

ADDITIONAL: If you're trying to see if a powerpivot table has been updated go to the powerwindow / design view then click table properties - should be in red/magenta in lower right corner. I usually check the last table in my 'CORE' list -- assume that the ones prior in the list bear the same or slightly earlier date/time.

I use table-properties so often I found it helpful to put that as well as a few other commands on the quick-access toolbar:

Sample PowerPivot.xlsx and PowerWindow

sqlpocket mysql

-- -
-- This is the mysql version of the database used with:
-- sql pocket guide 3rd ed. by Jonathan Gennick
-- ...I placed here so I could copy/paste into KSWEB
-- -
-- drop database if exists sqlpocket;

create database sqlpocket;

use sqlpocket;

create table gov_unit (
 id  integer NOT NULL,
 parent_id integer DEFAULT 3,
 name  varchar(10),
 type  varchar(8),
 constraint gov_unit_pk 
  primary key (id),
 constraint gov_unit_type_chk
  check (type in ('County','Township','City','State')),
        constraint gov_unit_loop
                foreign key (parent_id)
                references gov_unit(id)
 ) engine=innodb;


create table township (
 id  integer,
 name  varchar(10),
 population integer
        ) engine=innodb;


create view small_township as
   select * from township 
   where population < 500;

create table county (
 id  integer not null,
 name  varchar(10),
 population integer,
        constraint county_pk
           primary key (id)
        ) engine=innodb;


create table state (
 id  integer,
 name  varchar(10),
 population integer
        ) engine=innodb;

create table other_unit (
 id  integer,
 name  varchar(10),
 population integer
        ) engine=innodb;

create table owner (
 id  integer not null,
 name  varchar(15),
 phone  varchar(12),
 type  varchar(7),
 constraint owner_pk
  primary key (id),
 constraint owner_type_chk
  check (type in ('public','private'))
 ) engine=innodb;

create table upfall (
 id  integer not null,
 name  varchar(15),
 datum  varchar(7),
 zone  integer,
 northing integer,
 easting  integer,
 lat_lon  varchar(20), 
 county_id integer,
 open_to_public varchar(1),
 owner_id integer,
 description varchar(80),
 confirmed_date timestamp,
 constraint upfalls_pk
  primary key (id),
 constraint open_to_public_chk
  check (open_to_public in ('y','n')),
 constraint owner_id_fk
  foreign key (owner_id)
  references owner (id),
 constraint gov_unit_id
  foreign key (county_id)
  references county (id)
 ) engine=innodb;

create table fall_location (
 id  integer,
 datum  varchar(7),
 zone  integer,
 northing integer,
 easting  integer
        ) engine=innodb;

create table fall_description (
 id  integer,
 name  varchar(15),
 description varchar(80)
        ) engine=innodb;

create table new_falls (
 id  integer,
 name  varchar(15),
 datum  varchar(7),
 zone  integer,
 northing integer,
 easting  integer,
 lat_lon  varchar(20), 
 county_id integer,
 open_to_public varchar(1),
 owner_id integer,
 description varchar(80),
 confirmed_date timestamp
        ) engine=innodb;

create table trip (
 name  varchar(10) not null,
 stop  integer not null,
 parent_stop integer,
 constraint trips_pk
  primary key (name, stop),
 constraint parent_stop_fk
  foreign key (name, parent_stop)
  references trip (name, stop),
        constraint stop_fk
                foreign key (stop) 
                references upfall (id)
 ) engine=innodb;

CREATE TABLE pivot (
   x integer
   ) engine=innodb;

CREATE TABLE parent_example (
   name varchar(15) not null,
   country varchar(2) not null DEFAULT 'CA',
      CONSTRAINT par_example_ctry
      CHECK (country IN ('CA','US')),
   CONSTRAINT parent_example_pk
      PRIMARY KEY(name, country)
) engine=innodb;

create table duel (
   dummy varchar(1)
);

insert into duel values ('X');

insert into gov_unit values (3,null,'Michigan','State');
insert into gov_unit values (2,3,'Alger','County');
insert into gov_unit values (1,2,'Munising','City');
insert into gov_unit values (4,2,'Munising','Township');
insert into gov_unit values (5,2,'Au Train','Township');
insert into gov_unit values (6,3,'Baraga','County');
insert into gov_unit values (7,3,'Ontonagon','County');
insert into gov_unit values (8,7,'Interior','Township');
insert into gov_unit values (9,3,'Dickinson','County');
insert into gov_unit values (10,3,'Gogebic','County');
insert into gov_unit values (11,3,'Delta','County');
insert into gov_unit values (12,11,'Masonville','Township');

insert into township values (4,'Munising',3125);
insert into township values (5,'Au Train',1172);
insert into township values (8,'Interior',375);
insert into township values (12,'Masonville',1877);

insert into county values (2,'Alger',9862);
insert into county values (6,'Baraga',8746);
insert into county values (7,'Ontonagon',7818);
insert into county values (9,'Dickinson',27472);
insert into county values (10,'Gogebic',17370);
insert into county values (11,'Delta',38520);

insert into state values (20, 'Montana', 902195);
insert into state values (21, 'N. Dakota', null);
insert into state values (3, 'Michigan', 9938444);

insert into owner values (1,'Pictured Rocks','906.387.2607','public');
insert into owner values (2,'Michigan Nature','517.655.5655','private');
insert into owner values (3,'AF LLC',null,'private');
insert into owner values (4,'MI DNR','906-228-6561','public');
insert into owner values (5,'Horseshoe Falls','906.387.2635','private');


insert into upfall values (1,'Munising Falls','NAD1927',16,5141184,0528971,null,2,'y',1,
                               'Munising''s namesake waterfall; the one on all the postcards',
                               timestamp '2005-12-08 19:30:00');
insert into upfall values (2,'Tannery Falls','NAD1927',16,5140000,0528808,null,2,'y',2,
                               'Like Munising Falls, but nicely undeveloped',
                               timestamp '2005-12-08 19:50:00');
insert into upfall values (3,'Alger Falls','NAD1927',16,5137795,0527046,null,2,'y',3,
                               'Nice falls at east entrance into town',
                               timestamp '2005-12-08 20:03:00');
insert into upfall values (4,'Wagner Falls','NAD1927',16,5137310,0526989,null,2,'y',4,
                               'Small, scenic falls near intersection of M-28 and M-94',
                               timestamp '2005-12-08 20:04:00');
insert into upfall values (5,'Horseshoe Falls','NAD1927',16,5138877,0527323,null,2,'y',null,
                               'Falls, garden, scenic walk, and a fish pond. Kids can feed the fish.',
                               timestamp '2005-12-08 20:07:00');
insert into upfall values (6,'Miners Falls',null,null,null,null,null,2,'y',1,
                               'Highest waterflow in the Pictured Rocks.',
                               timestamp '2005-12-08 20:10:00');
insert into upfall values (7,'Little Miners',null,null,null,null,null,2,'y',1,
                               'Scenic, must bushwhack two miles off the trail, on Little Miners River',
                               timestamp '2005-12-08 20:15:00');
insert into upfall values (8,'Scott Falls',null,null,null,null,null,2,'y',null,
                               'Scenic roadside falls near Face on the Rock',
                               timestamp '2005-12-18 21:28:00');
insert into upfall values (9,'Canyon Falls',null,null,null,null,null,6,'y',null,
                               'A few miles north of M-28 (two miles south of Alberta), but worth it',
                               timestamp '2005-12-18 21:35:00');
insert into upfall values (10,'Agate Falls',null,null,null,null,null,7,'y',null,
                               'Across road from Joseph F. Oravec Roadside Park',
                               timestamp '2005-12-18 21:35:00');
insert into upfall values (11,'Bond Falls',null,null,null,null,null,7,'y',null,
                               'Bond Falls Basin, nine miles south of Bruce Crossing',
                               timestamp '2005-12-18 21:50:00');
insert into upfall values (12,'Fumee Falls',null,null,null,null,null,9,'y',null,
                               'Start from Helen Z. Lien Roadside Park 3 1/2 miles west of Norway',
                               timestamp '2005-12-18 21:56:30');
insert into upfall values (13,'Kakabika Falls',null,null,null,null,null,10,'y',null,
                               '1/2 mile north of US-2 off of county road 527',
                               timestamp '2005-12-18 22:03:09');
insert into upfall values (14,'Rapid River Fls',null,null,null,null,null,11,'y',null,
                               'Park includes playground, barbecue pits, flowing well, picnic tables',
                               timestamp '2005-12-18 22:13:11');
insert into upfall values (30,'Twin Falls #1','NAD1927',16,5140500,0528641,null,2,'y',2,
                              'Near Twin Falls #2',
                              timestamp '2006-02-10 19:35:00');
insert into upfall values (31,'Twin Falls #2','NAD1927',16,5140500,0528663,null,2,'y',2,
                              'Near Twin Falls #1',
                              timestamp '2006-02-10 19:35:00');

insert into new_falls values (2,'Olson Falls',null,null,null,null,null,2,'y',2,
                               'Like Munising Falls, but nicely undeveloped',
                               timestamp '2005-12-08 19:50:00');
insert into new_falls values (20,'MNA Memorial',null,null,null,null,null,2,'y',2,
                               'Right near Tannery Falls',
                               timestamp '2006-01-31 09:54:00');

insert into trip values ('Munising',1,null);
insert into trip values ('Munising',2,1);
insert into trip values ('Munising',6,2);
insert into trip values ('Munising',4,6);
insert into trip values ('Munising',3,4);
insert into trip values ('Munising',5,3);
insert into trip values ('M-28',3,null);
insert into trip values ('M-28',1,3);
insert into trip values ('M-28',8,1);
insert into trip values ('M-28',9,8);
insert into trip values ('M-28',10,9);
insert into trip values ('M-28',11,10);
insert into trip values ('US-2',14,null);
insert into trip values ('US-2',12,14);
insert into trip values ('US-2',11,12);
insert into trip values ('US-2',13,11);


INSERT INTO pivot
SELECT huns.x+tens.x+ones.x FROM
  (SELECT 0 x FROM dual
   UNION ALL SELECT 1 x FROM dual
   UNION ALL SELECT 2 x FROM dual
   UNION ALL SELECT 3 x FROM dual
   UNION ALL SELECT 4 x FROM dual
   UNION ALL SELECT 5 x FROM dual
   UNION ALL SELECT 6 x FROM dual
   UNION ALL SELECT 7 x FROM dual
   UNION ALL SELECT 8 x FROM dual
   UNION ALL SELECT 9 x FROM dual) ones,
  (SELECT 0 x FROM dual
   UNION ALL SELECT 10 x FROM dual
   UNION ALL SELECT 20 x FROM dual
   UNION ALL SELECT 30 x FROM dual
   UNION ALL SELECT 40 x FROM dual
   UNION ALL SELECT 50 x FROM dual
   UNION ALL SELECT 60 x FROM dual
   UNION ALL SELECT 70 x FROM dual
   UNION ALL SELECT 80 x FROM dual
   UNION ALL SELECT 90 x FROM dual) tens,
  (SELECT 0 x FROM dual
   UNION ALL SELECT 100 x FROM dual
   UNION ALL SELECT 200 x FROM dual
   UNION ALL SELECT 300 x FROM dual
   UNION ALL SELECT 400 x FROM dual
   UNION ALL SELECT 500 x FROM dual
   UNION ALL SELECT 600 x FROM dual
   UNION ALL SELECT 700 x FROM dual
   UNION ALL SELECT 800 x FROM dual
   UNION ALL SELECT 900 x FROM dual) huns;

COMMIT;

mySQL for Android

I was going through a SQL text and was muttering to myself.  It sure would be nice if there was an Android version of mySQL I could use with these examples on my tablet. 

Guess what?  There is -- it works!

https://play.google.com/store/apps/details?id=ru.kslabs.ksweb&hl=en

wait_while_jobs_are_running.ksh

When running a number of jobs in the background -- I run this in the foreground:

::/apps/datamodules/_SQL> jobs
::/apps/datamodules/_SQL> cat ../_CMD/wait_while_jobs_are_running.ksh
 export jr=`jobs|grep -c "Running"`
 while [[ $jr -ne 0 ]];do
 sleep 30
 export jr=`jobs|grep -c "Running"`
 done

...it basically sleeps 30 seconds until there are no more running jobs.

myLauncher (it's not cron -- but it works for me)

https://app.box.com/s/naen8lwm4yv4eeso77c5

https://app.box.com/s/x00mja2auyygslqd8h39

UnDup SQL pattern

    /*
    || Recording this here for reference
    || it's a pattern that will repeat
    || In theory you can do without creating
    || interim table -- in practice I've found
    || otherwise...
    || 'min_record_id' is assumed to be a 
    || unique sequential number assigned to
    || records as they come in ...
    */
    CREATE TABLE min_record_id (record_id bigint(19));
    CREATE UNIQUE INDEX min_record_id_PK on min_record_id(record_id);
    INSERT INTO min_record_id
        SELECT *
          FROM (-- ilv
                   SELECT MIN(record_id) as record_id
                     FROM dup_file
                    GROUP
                       BY {uniquely identifying items}
               )
                  ilv
    ;
    CREATE TABLE file_unique
    SELECT  df.*
      FROM  dup_file df 
            inner join 
            min_record_id mri 
            on df.record_id
            =  mri.record_id
    ;
    DROP TABLE min_record_id;

#‎Powerpivot‬~look at datasource on xlsx sheet -- s/b 'powerpivot data' -- keep drilling down to see last refreshed.

#‎Powerpivot‬~look at datasource on xlsx sheet -- s/b 'powerpivot data' -- keep drilling down to see last refreshed.