codeswitcher

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.

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

Still coyote ugly, but functional.

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.

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!

It worked! First production run this AM, and a recipient confirmed receipt.

As of right now, where I am:

The reminder system is operational and apparently working correctly (SMS only, command line/batch file/cron interface, on mixo in ~/forge).

The receipt of incoming SMS system is also working correctly (on muninn.sb/~$num) and mostly properly secured (SSL + HTTP Basic Auth), though the interface is v. crude and non-scalable. The "reply" function not yet implemented.

So things that need happening:

- ssh access to sb (where are we with this? I connected support about the key generation bug.)
- implementing the authorization check specified by Tilio on the security page
- make the reply function work
- decide what if any forwarding is desirable, and implement
- scalability of web interface: paging, other
- populate contacts
- build interface for populating/editing contacts
- make pretty (CSS)

Things that might be nice:

- multiuser? installer?
- template for schedule?
- remind self to make schedule?? :)
- swap to HTTP Digest Auth?
- web interface for scheduler?
- web interface for subscriptions?
- system for non-batch or exception reminders? ("and bring the book.")
- voice!
- refactoring

Self, good job at identifying the minimal adequate functionality and not oversolving!

Note to self: the original dev space is pointed at the dev db. There is now a fresh install of everything (except send_notifications), including a new db, at the shiny new subdomain on sere with SSL and authentication and everything. Ooooh. So responsible.

(Not everything; self, feel free to get around to implementing Twilio's authentication check thingy.)

We're still waiting on ssh access to sere.

Timezone seems correct?

To do:
Check to see if the helper library uses HTTPS.
Implement sending from sere.
How about those "reply" links.
Paging.
Styling for:
- attractiveness
- readability (alternating lines?)
Populate the contacts table
Decide what to do about forwarding to cell phone
- enabling replies?
- enabling initiating from cell?

So I just figured out the business model of services like easycron.com. The name threw me for a loop, in that, (1) their interface isn't significantly easier than just doing the old crontab -e thing for yourself; (2) and to the extent it is, you can just use a free online crontab generator and cut-n-paste; and (3) they charge money for something that is basically already free.

I had hypothesized that it serves people who want cron but aren't allowed to have it by their, say, shared web hosting hosts. Maybe that's a thing? My shared web hosting hosts have cpanel, and for as long as I've been with them (which isn't too terribly long, just a few years) they've had the cpanel interface to cron. Honestly, I would think most people who know how to spell cron would have access to it. (I say this as someone who has, an entirely embarassingly number of times, typed man chron at a prompt.)

But I've figured it out. The problem in my perspective is that I hadn't previously actually tried to use cron for anything actually important on a shared device, like a business purpose.

Vanilla-flavored cron -- there are allegely more enlightened varieties, but not on Debian -- interprets all times expressed in crontabs in whatever the timezone of the server is. Period.

There's a bunch of hacks you can do if you're running the server personally, but if you don't have root or otherwise aren't in a position to configure the whole machine, you're mostly out of luck, unless you want to run an every-minute cronjob which just fires off a smarter program that does your actual cronjobs.

Or you can use easycron.com, which lets you set the timezone of your jobs.

Seriously, apparently their business model is to some substantial extent leveraging the fact that Javier Fernndez-Sanguino Pea refuses to implement timezone sensitivity in Debian's cron. And whomever else is refusing to implement tz sensitivity into their distro's crons.

Now I'm looking into at.

S	M	T	W	T	F	S
						1
2	3	4	5	6	7	8
9	10	11	12	13	14	15
16	17	18	19	20	21	22
23	24	25	26	27	28	29
30

Entries tagged with muninn

Muninn: to do

Muninn: Interface for adding and updating contacts: functionally done

Muninn: Better time stamping on TABLE contacts

Muninn: Updating the main view

Update on Muninn

Muninn: Note to self: new dbs

[muninn, cron] cron TZ's as business opportunity

Profile

April 2023

Syndicate

Most Popular Tags

Page Summary

Style Credit

Expand Cut Tags