Muninn: Updating the main view

As per previously, I've changed the structure of the msgs table to support the reality that contacts' phone numbers change, and that the relationship between the msgs and contacts TABLES can't be dynamic or future changes to phone numbers orphan msgs in the log.

Now that I have FIELDs fromcontactname and tocontactname in TABLE msgs, I don't need VIEW v_msgs_2 to hit TABLE contacts, as per below.

I am about to eviscerate it, so I'd like to store this wonder for posterity:

CREATE VIEW `v_msgs_2` AS
SELECT
       `msgs`.`id` AS `id`,
       `msgs`.`body` AS `body`,
       `msgs`.`created_on` AS `created_on`,
       (CASE
             WHEN (`msgs`.`tonum` = '+1617XXXXXXX') THEN 'from'
             WHEN (`msgs`.`fromnum` = '+1617XXXXXXX') THEN 'to'
             ELSE NULL
             END
             ) AS `tf`,
       (CASE

                WHEN (`msgs`.`tonum` = '+1617XXXXXXX') THEN `msgs`.`fromnum`
                WHEN (`msgs`.`fromnum` = '+1617XXXXXXX') THEN `msgs`.`tonum`
                ELSE NULL
                END
                ) AS `num`,
       (CASE
                WHEN (`msgs`.`tonum` = '+1617XXXXXXX') THEN `cf`.`name`
                WHEN (`msgs`.`fromnum` = '+1617XXXXXXX') THEN `ct`.`name`
                ELSE NULL
                END
                ) AS `name`
FROM (
        (`msgs` left join `contacts` `cf` on
                (
                     (`msgs`.`fromnum` = `cf`.`number`)
                )
        ) left join `contacts` `ct` on
               (
                        (`msgs`.`tonum` = `ct`.`number`)
                )
        )
ORDER BY
      `msgs`.`created_on`,
      `msgs`.`id`

The new improved view is:

CREATE VIEW `v_msgs_2` AS
SELECT
       `msgs`.`id` AS `id`,
       `msgs`.`body` AS `body`,
       `msgs`.`created_on` AS `created_on`,
       (CASE
             WHEN (`msgs`.`tonum` = '+1617XXXXXXX') THEN 'from'
             WHEN (`msgs`.`fromnum` = '+1617XXXXXXX') THEN 'to'
             ELSE NULL
             END
             ) AS `tf`,
       (CASE

                WHEN (`msgs`.`tonum` = '+1617XXXXXXX') THEN `msgs`.`fromnum`
                WHEN (`msgs`.`fromnum` = '+1617XXXXXXX') THEN `msgs`.`tonum`
                ELSE NULL
                END
                ) AS `num`,
       (CASE
                WHEN (`msgs`.`tonum` = '+1617XXXXXXX') THEN `msgs`.`fromcontactname`
                WHEN (`msgs`.`fromnum` = '+1617XXXXXXX') THEN `msgs`.`tocontactname`
                ELSE NULL
                END
                ) AS `name`
FROM msgs
ORDER BY
      `msgs`.`created_on`,
      `msgs`.`id`

Works great.

Next: update the view name and corresponding PHP call. ETA: done!

Muninn: Better time stamping on TABLE contacts

Since I'm making TABLE contacts editable, I'd better have an updated_on TIMESTAMP field....

Except MySQL, for reasons unknown, doesn't permit more than one TIMESTAMP field on a table. So this is a job for trigger functions.

So I added a column, updated_on of type TIMPSTAMP that is nullable and defaults to null. Then...

CREATE TRIGGER `contacts_updated_on_ts_update` BEFORE UPDATE ON `contacts`
 FOR EACH ROW BEGIN
	IF NEW.updated_on IS NULL THEN
        SET NEW.updated_on = CURRENT_TIMESTAMP;
	END IF;
END

And while we're at it, we'd better handle INSERTs, too:

CREATE TRIGGER `contacts_updated_on_ts_insert` BEFORE INSERT ON `contacts`
 FOR EACH ROW BEGIN
	IF NEW.updated_on IS NULL THEN
        SET NEW.updated_on = CURRENT_TIMESTAMP;
	END IF;
END

Let's make it as if that had always been there:

UPDATE `contacts` SET `updated_on`=`created_on` WHERE `updated_on` IS NULL

Beautiful.

Muninn: Interface for adding and updating contacts: functionally done

Muninn now has a web interface for managing contacts, whoo!

Still coyote ugly, but functional.

Muninn: to do

Forward incoming txts: to txt? to email?

Web interface for sending texts

Txt interface for sending txts

Paging of log

Log by contact

Also, when ct gets a new number, log should be checked and any txts with that number that are not assigned to a ct should be assigned to that ct. Maybe.

Should a contact be able to have multiple numbers? This means breaking out a join table, with all that entails. Is the added complexity worth it?

One upside: one could track when numbers were added, for freshness.