Halcyon Repose

General

MySQL Notes

by on Oct.14, 2005, under General

Connect to MySQL from the command line:

msql -u *username* -p

Connect to the database (schema):

USE *database name*

Check to see what database you are currently set to:

SELECT DATABASE();

NOTES on SCFC Database

CREATE TABLE users (
ID INT PRIMARY KEY,
FirstName varchar(30) NOT NULL,
LastName varchar(30) NOT NULL,
Email varchar(50) NOT NULL,
Phone varchar(20),
Password varchar(20) NOT NULL,
CoachID INT Not NULL
);

CREATE TABLE Lesson_Slots(
ID int primary key,
InstructorID int,
StartTime datetime,
StudentID INT DEFAULT 0,
Weapon INT DEFAULT 0
);

insert into users Values (1, ‘Jaime’, ‘Wood’, ‘jaime@southcoastfencing.com’, ‘(714) 836-9505′,’temp’,0);

insert into Lesson_Slots values (1, 1, ’2005-10-21 19:00:00′, 1, 1);
insert into Lesson_Slots values (2, 1, ’2005-10-21 19:20:00′, 1, 1);
insert into Lesson_Slots values (3, 1, ’2005-10-22 19:00:00′, 1, 1);
insert into Lesson_Slots values (4, 1, ’2005-10-22 19:20:00′, 1, 1);

select date_format(StartTime,’%M %d, %Y – %a’) from Lesson_Slots;

PERL Code;
#Initialize
use DBI;

my $dsn = ‘DBI:mysql:southcoa_scfc:localhost’;
my $db_user_name = ‘southcoa_scfc’;
my $db_password = ‘RealFencing’;
my ($id, $password);
my $dbh = DBI->connect($dsn, $db_user_name, $db_password);

# Query (using arguments
my $rs = $dbh->prepare(‘SELECT firstname, lastname from users
WHERE upper(firstname) = upper(?)
and upper(lastname) = upper(?)
and password = ?
and CoachID > -1′);

$rs->execute($in_fname, $in_lname, $in_code);
my ($sFirstName, $sLastName) = $rs->fetchrow_array();

$rs->finish();

# Number of rows returned
$rs->rows()
– Table to hold each individual class
DROP TABLE class_dates;
CREATE TABLE class_dates(
class_id integer(5) not null primary key auto_increment,
session_id integer(5) not null,
class_date date not null,
class_start time not null,
class_end time not null,
class_type integer(2) not null,
class_title varchar(100) not null,
order_id integer(2) not null,
deleted
);
ALTER TABLE class_dates ADD INDEX(session_id);

– Table to hold sessions (class groupings)
DROP TABLE class_session;
CREATE TABLE class_session(
session_id integer(5) not null primary key auto_increment,
session_title varchar(100) not null,
start_date date not null,
post_date date not null
);
ALTER TABLE class_session ADD INDEX(post_date);

– Table to hold email bodies
DROP TABLE mail_body;
CREATE TABLE mail_body(
Mail_ID integer(5) not null primary key auto_increment,
Mail_Type varchar(255),
Mail_Body text
);

– Table to hold all the class templates
DROP TABLE class_template;
CREATE TABLE class_template(
Template_ID integer(5) not null primary key auto_increment,
Class_Title varchar(100) not null,
Class_Type int(2) not null,
Class_Notes text,
Class_DayTime varchar(255),
Class_Length varchar(20),
Class_Cost varchar(10),
Class_Day int(1),
Class_Default int(1) default 0,
Deleted int(1) default 0
);
ALTER TABLE class_template ADD INDEX(Template_ID);

– Table to hold each individual who registers for class
DROP TABLE class_registration;
CREATE TABLE class_registration(
Registration_ID integer(5) not null primary key auto_increment,
Template_ID integer(5) not null,
Session_ID integer(5) not null,
Class_Date date not null,
Deleted int(1) default 0
);
ALTER TABLE class_registration ADD INDEX(Registration_ID);

– User Table
DROP TABLE scfc_users;
CREATE TABLE scfc_users(
User_ID integer(5) not null primary key auto_increment,
First_Name varchar(25),
Last_Name varchar(25),
Email varchar(50),
Address text,
Phone varchar(15),
Release binary(1),
Notes text,
Username varchar(25),
Password varchar(32)
);
ALTER TABLE scfc_users ADD INDEX(First_Name, Last_Name);
ALTER TABLE scfc_users ADD INDEX(Username);

– User Class Registration
DROP TABLE class_users;
CREATE TABLE class_users(
User_ID integer(5) not null,
Class_ID integer(5) not null,
Paid binary(1),
Comments text
);
ALTER TABLE class_users ADD INDEX (Class_ID);

– Instructor Table
DROP TABLE Instructor;
CREATE TABLE Instructor(
Instructor_ID integer(5) not null primary key auto_increment,
Instructor_Name varchar(30)
);

– Private Lesson Templates
DROP TABLE Lesson_Template;
CREATE TABLE Lesson_Template(
Template_ID integer(5) not null primary key auto_increment,
Instructor_ID integer(5) not null,
Time time not null,
Day integer(2) not null,
Student_ID integer(5) default NULL,
Status integer(2) default 0
);
ALTER TABLE Lesson_Template ADD INDEX (Instructor_ID);
ALTER TABLE Lesson_Template ADD INDEX (Day);

– Private Lesson Table
DROP TABLE Private_Lesson;
CREATE TABLE Private_Lesson(
Lesson_ID integer(5) not null primary key auto_increment,
Instructor_ID integer(5) not null,
Lesson_Date DateTime not null,
Student_ID integer(5),
Status integer(2) default 0
);
ALTER TABLE Private_Lesson ADD INDEX (Instructor_ID);
ALTER TABLE Private_Lesson ADD INDEX (Lesson_Date);

– For use on the web page, gets the next session that has not already passed
select min(post_date) from class_session where post_date > now();

SQL Scraps

select cd.class_id, ct.type_desc, date_format(cd.class_date, ‘%W’),
date_format(cd.class_date,’%M %D’), date_format(cd.class_start,’%l:%i’),
date_format(cd.class_end,’%l:%i %p’), cd.class_title
from class_dates cd, class_type ct
where cd.session_id = ?
order by ct.type_id, cd.order_id;

select min(session_id) from class_session
where start_date < CURDATE()
and post_date > CURDATE();

select session_id from class_session
where start_date = (SELECT max(start_date) from class_session);

(my $Class_ID, $Class_Type, $Class_Date, $Class_Start, $Class_end, $Class_Title) = $rs->fetchrow_array();

select date_format(class_end,’%l:%i %p’)
from class_dates;

– Class List
SELECT cr.Registration_ID, ct.Class_Title, ct.Class_Notes, ct.Class_DayTime,
cr.Class_Date, ct.Class_Length, ct.Class_Cost
FROM class_template ct, class_registration cr
WHERE cr.Session_ID = 14
AND cr.Template_ID = ct.Template_ID
AND cr.Deleted = 0;

DROP TABLE Whats_New;
CREATE TABLE Whats_New(
ID int not null primary key auto_increment,
PostDate datetime,
PostSubject varchar(255),
PostBody text,
Deleted int(1) default 0,
Visible int(1) default 1,
DisplayOrder int(1) default 0
);
ALTER TABLE Whats_New ADD INDEX(ID);
ALTER TABLE Whats_New ADD INDEX(PostDate);

DROP TABLE Image_Rotate;
CREATE TABLE Image_Rotate(
ID int not null primary key auto_increment,
Image_Location varchar(100),
Image_Comment varchar(75),
Display_Order int(2) default 0,
Date_Created timestamp
);
ALTER TABLE Image_Rotate ADD INDEX(ID);

–Payment Table
DROP TABLE Payment;
CREATE TABLE Payment(
ID int not null primary key auto_increment,
User_ID integer(5) not null,
Payment_Amt float(4,2),
Pay_Start datetime,
Pay_End datetime,
Payment_Type integer(1),
Payment_Note TEXT,
Payment_Currency integer(1),
Date_Entered datetime,
Entered_By integer(5)
);

ALTER TABLE Payment ADD INDEX(Pay_Start, Pay_End);
ALTER TABLE Payment ADD INDEX(User_ID);

– Credit Table
DROP TABLE Payment_Credit;
CREATE TABLE Payment_Credit(
ID int not null primary key auto_increment,
User_ID integer(5) not null,
Credit_Amt float(4,2),
Credit_Remain float(4,2),
Credit_Note TEXT,
Date_Entered datetime,
Entered_By integer(5)
);

ALTER TABLE Payment_Credit ADD INDEX(User_ID);

– Payment Types
DROP TABLE Payment_Type;
CREATE TABLE Payment_Type(
ID int not null primary key auto_increment,
Payment_Type varchar(25)
);

insert into Payment_Type values (1,’Dues’);
insert into Payment_Type values (2,’Class’);
insert into Payment_Type values (3,’Equipment’);

– Payment Types
DROP TABLE Payment_Currency;
CREATE TABLE Payment_Currency(
ID int not null primary key auto_increment,
Currency varchar(25)
);

insert into Payment_Currency values (1,’Cash’);
insert into Payment_Currency values (2,’Check’);
insert into Payment_Currency values (3,’Credit Card’);
insert into Payment_Currency values (4,’SCFC Credit’);

Links:

Tutorial
Date and Time Functions

1 Comment more...

More Serenity Gushing

by on Oct.04, 2005, under General

I have been a fan of Joss Whedon’s for a really long time. My friend Shane got me into Buffy the Vampire Slayer at the beginning of the second season, and I never really looked back. When he spawned off Angel, I added it to my list of must see TV and when he started Firefly, I was really excited about the fresh start he was making with the show.

Joss’s writing has always had an indefinable something for me that always kept me coming back for more. He can take some serious issues, and tackle them in a way that brings out the humor and yet still stays poignant. His dialogue is always witty, and he has a way of giving his characters a voice or style of speaking that is instantly recognizable. Every character interacts with everyone else as a person, not a plot device, yet they still fulfill the same purpose in the end. I know that Joss must spend much of his time working on his projects, but some how they come off so brilliantly tongue in cheek that they seem to spring fully formed from his mind.

It saddens me that I don’t get my dose of Joss every week now. Firefly was cancelled, and Buffy and Angel both drew to a close, so I feel like there is a hole in my life, and it is Joss shaped. I can only hope that Serenity does well enough to warrant some more movies, and that Joss can continue to work his magic. He is working on the screen play for Wonder Woman, so gives me good hopes for the film, and he also works on a number of comic books, which causes the little geek in me to squeal like a little girl. I haven’t done comics in ages, but I would go back for Joss.

Finally the whole reason for this little Joss tribute has to do with an article written by Orson Scott Card about Serenity. I have read numerous books by Orson, chief among them the Ender’s Game and the Seventh Son books. His character development is some of the best I have seen, and for him to give props to Joss in this way really raises my opinion of both of them.

Leave a Comment more...

Serenity

by on Sep.30, 2005, under General

It is Serenity Day!

Everyone, make sure you check this movie out. Do it for the kids :)

2 Comments more...

Rain

by on Sep.20, 2005, under General

How rare the rain here is in Southern California. A storm has been threatening all night, lightening cutting through the air, and the sound of thunder echoing through the night.

Such thunder storms are not common here, and they bring back memories of my years in Nashville. I miss the storms, for the first time in all my life I felt something akin to fear when the thunder started rolling this evening. I wouldn’t exactly call it fear, but I was in awe of the wonders of nature. I could certainly understand how many people could fear a thunderstorm, or why dogs hide under the bed as lightening cracks across the sky. Back in Nashville it was a common occurrence, but either I have been so long without it, or there was a something different that was present in the storm tonight.

Either way it seemed to be more primal, even if the storm didn’t last that long. It called to mind images and reminded me of days gone by. Good days, fun days, days that I am more than happy to lose myself in.

I need to call my parents……

Leave a Comment more...

What is in a name?

by on Sep.19, 2005, under General

So why chose the name HalcyonRepose.com?

As is always the case with the internet, my first few choices were already taken. I have long been enamoured with the word halcyon, so I knew my goal from the beginning was to find an available URL that would accomidate my word passion (my other word of choice being ornery, but that doesn’t have the same website possibilities). My first choice was HalcyonDreams.com, and of course that was already taken. Back to the drawing board it was. I did some brain storming lists, but I kept coming back to halcyon. I was doing some research into the word for some ideas, and in the process I found a quotion from Thomas De Quincy that was simply “Deep, halcyon repose”. It immediately struck me as ‘right’ (as all important decisions should) and now here we are.

Where we go from here, we will have to see.

Is this a blog? Well technically yes.

Will it be my online journal? Yes and no. In my efforts to improve myself, one of my perceived defficencies is my lack of ability to communicate. I feel I have made great strides in this over the years, but this is another step down that road. Every time I bear my soul, so to speak, it gets a little easier. In fact just the act of organizing my thoughts into a form I can portray, seems to make the entire process more automatic.

Of course in addition to the above, this will also be a place to put all those digital items that I feel others need access to.

6 Comments more...

Looking for something?

Use the form below to search the site:

Still not finding what you're looking for? Drop a comment on a post or contact us so we can take care of it!