Today Mr. Good Bot gets his life long wish of having a brain… wait, no… that was Scarecrow bot.

Come to think of it… Scarecrow bot never did get his brain, just a GED certificate out of the bottom of an old steamer trunk!

In any case, like Scarecrow, Mr. Good Bot needs a brain.

Thankfully this won’t take a trip to the Emerald City and surviving the Chordettes lollipop-guild-fanclub or swarms of flying-monkey bots!

 

Mr. Good Bots brain will be a My SQL database which allows the system to store important persistent data needed by front-end & back-end code.

If you are new to my content or just need a refresher, here are other posts in this series:

If you just want the featured image for this post as a wallpaper here you go:

Mr. Good Bot Database Wallpaper
Mr. Good Bot Database Wallpaper

 

So with that…

We’re, off to create a database…

 

Mr. Good Bot Database

Databases (like brains) are useful and are usually full lots of delicious… uh… stuff, like bot friendly data!

Hmmm, that worked way better in my head (brains, go figure!) but no matter, we’ve walked on too many yellow bricks to turn back and fix it now! 😛

Anyway, there are two tables in the database (“botstate” & “statements”) though more tables can always be added as needed.

Bot State

This table holds the state of the bots that are managed by the Good Bot system though the state information needed is currently limited to a name and tracking if the bot is speaking or not.

The fields in this table are:

  • ID
  • Name
  • Speaking
  • CustomData

ID stores a unique “number” for each bot record in this table.

Name stores the bot name string of characters as a “varchar”.

Speaking is a “tinyint” and holds a 0 or 1 depending if the bot is speaking.

CustomData is a “text” type and I added it so that the front-end and back-end can exchange love letters and other types of persistent data in the form of JSON as needed but I’m not actually using it at the moment.

Statements

Think of this table like a one sided conversation log (it only contains the bots statements, not users statements) but it’s purpose is to store things the bot needs to say and to keep track of if it already said something or not.

Further, I already know what the bot is going to say because currently I am telling the bot what to say and it’s really me that Xavier is talking to.

It’s like talking to a puppet, you talk to the puppet but somebody is behind the curtain pulling the strings.

Maybe if everyone is not already bored with this series we could think about making Mr. Good Bot into an autonomous chat bot but with the Nielsen ratings my Good Bot posts have been getting lately I’ll be surprised if my show doesn’t get canceled by the network before the end of sweeps week!

In any case…

The fields in this table are:

  • ID
  • Bot
  • Status
  • Statement

ID stores a unique “number” for each statement record in this table.

Bot stores the name of bot this statement is for. It’s a as a “varchar” which means that it’s unnecessarily repeating a larger string instead of an int and this could be improved by storing the ID of the bot from the Bot State table but we’re just experimenting and not worried about efficiency.

Status is a “tinyint” and holds a 0 or 1 depending on if the bot is speaking.

Statement is a “text” type field and holds the text string of whatever the bot is going to say.

Code

This as an SQL dump from my test database including

Database.sql
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";

--
-- Database: `mrgoodbot`
--
CREATE DATABASE IF NOT EXISTS `mrgoodbot` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
USE `mrgoodbot`;

-- --------------------------------------------------------

--
-- Table structure for table `botstate`
--

CREATE TABLE `botstate` (
  `ID` int(11) NOT NULL,
  `Name` varchar(255) NOT NULL,
  `Speaking` tinyint(1) NOT NULL,
  `CustomData` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `botstate`
--

INSERT INTO `botstate` (`ID`, `Name`, `Speaking`, `CustomData`) VALUES
(1, 'Mr. Good Bot', 0, '');

-- --------------------------------------------------------

--
-- Table structure for table `statements`
--

CREATE TABLE `statements` (
  `ID` int(11) NOT NULL,
  `Bot` varchar(255) NOT NULL,
  `Status` tinyint(1) NOT NULL DEFAULT '0',
  `Statement` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `statements`
--

INSERT INTO `statements` (`ID`, `Bot`, `Status`, `Statement`) VALUES
(1, 'Mr. Good Bot', 0, 'Hello World!'),
(2, 'Mr. Good Bot', 0, 'Testing One, Two, Three.'),
(3, 'Mr. Good Bot', 0, 'If you can hear me then my vocal systems are operational.');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `botstate`
--
ALTER TABLE `botstate`
  ADD PRIMARY KEY (`ID`);

--
-- Indexes for table `statements`
--
ALTER TABLE `statements`
  ADD PRIMARY KEY (`ID`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `botstate`
--
ALTER TABLE `botstate`
  MODIFY `ID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- AUTO_INCREMENT for table `statements`
--
ALTER TABLE `statements`
  MODIFY `ID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;
COMMIT;

I may update this code before the project is complete so you will want to follow along on GitHub.

GitHub

All of the most up to date code and images for Mr. Good Bot are available to download on GitHub for free.

GitHub: MrGoodBot

Next week we should be looking at wiring the database to the Mr. Good Bot system so stay tuned!


There's no place like home!
There’s no place like home!

Scarecrow wanted a brain, the Tin Man wanted a heart and the Cowardly Lion wanted courage.

I just want a home where I can share my bots and thoughts with you!

If that sounds good then like, share, comment and subscribe!

And if you happen to want to contribute financially to my efforts, I have a Patreon where you can pledge $1 or more a month for one or more months, cancel any time.

Much Love,

~Joy