Menu
▾
▴
[Pocolap-cvscommit] pocolap/data/scripts_tools/outdated/oldtools setupNSS_hsqldb.sql,NONE,1.1 README.txt,NONE,1.1 build.xml,NONE,1.1 pocohsqldb.jar,NONE,1.1 hsqldbConverter.pl,NONE,1.1
|
From: todd r. <to...@us...> - 2005-03-31 20:54:21
|
Update of /cvsroot/pocolap/pocolap/data/scripts_tools/outdated/oldtools In directory sc8-pr-cvs1.sourceforge.net:/tmp/cvs-serv9992/data/scripts_tools/outdated/oldtools Added Files: setupNSS_hsqldb.sql README.txt build.xml pocohsqldb.jar hsqldbConverter.pl Log Message: Reorganization of resources --- NEW FILE: pocohsqldb.jar --- (This appears to be a binary file; contents omitted.) --- NEW FILE: build.xml --- <project name="HSQLDB Date functions" basedir="." default="run"> <property name="jarname" value="pocohsqldb.jar" /> <property name="buildDir" value="build" /> <target name="run" > <antcall target="cleanAll" /> <antcall target="compile" /> <antcall target="jar" /> <antcall target="cleanAll" /> </target> <target name="buildDir"> <mkdir dir="${buildDir}" /> </target> <target name="cleanAll"> <delete dir="${buildDir}" /> </target> <target name="compile" depends="buildDir"> <javac srcdir="./com/pocolap/hsqldb/datetime" destdir="${buildDir}" classpath="com.pocolap.hsqldb.datetime" /> </target> <target name="jar"> <jar destfile="${jarname}" basedir ="${buildDir}" includes="com/**" /> </target> </project> --- NEW FILE: README.txt --- This is the README.txt file for pocolap tools used to convert data for use with the MyCgiServer live demo site. The "tools" directory includes: hsqldbConverter.pl - a Perl script that converts the setupNSS.sql script from MySQL syntax for use with HSQLDB v1.6.1. pocohsqldb.jar - jar file HSQLDB must have access to in order to run the converted setupNSS.sql script created by scripter.pl. Make sure that this jar file is in your CLASSPATH before attempting to run the HSQLDB script created by scripter.pl build.xml - ant build file used to build pocohsqldb.jar The following file is included, but not guaranteed up-to-date with each release: setupNSS_hsqldb.sql - the converted setupNSS.sql script created with hsqldbConverter.pl The following files are created and in use on the MCS site, but not included in any distribution, mostly because they may contain some security information. nss.backup, nss.data, nss.properties, nss.script - HSQLDB data files created by running the setupNSS_hsqldb.sql script in HSQLDB. --- NEW FILE: hsqldbConverter.pl --- #! /usr/bin/perl ############################# # This script is used to convert setupNSS.sql # from MySQL syntax to HSQLDB v1.6.1. # It was originally written for v1.7.1 and later # discovered that it needed to run in v1.6.1. As # a result, this script makes a lot of modifications # to the SQL statements, including changing "not null" # fields to "null", removing all DEFAULT values and # converting all tables to upper case. # # This script is intended to make my life easier, and not meant # for production use. If you want to extend this # to create scripts for other RDBMS's, # please feel free! (Just share it with the # rest of us, if you would). # # In order for the resulting script to run, you # will need to ensure that com.pocolap.hsqldb.datetime # package is on your CLASSPATH, otherwise # HSQLDB won't be able to run this script ############################# $num = $#ARGV; if ($num<0){ print "USAGE: scripter.pl <outputFileName>"; exit; } @tables = ("CALENDAR","CUSTOMERS","SALABLEITEMS","SALES"); open(SCRIPTFILE, "<", "../../../userdocs/setupNSS.sql") || die "Couldn't open script file"; open(OUTFILE, ">", $ARGV[0]) || die "Couldn't open file for writing"; #Put instructions in script print OUTFILE "-- You must have com.pocolap.hsqldb.datetime.DateAgent\n-- in your CLASSPATH before running this script\n"; #Special Aliases print OUTFILE "create alias DATE_ADD for \"com.pocolap.hsqldb.datetime.DateAgent.dateAdd\"\n"; print OUTFILE "create alias DATE_FORMAT for \"com.pocolap.hsqldb.datetime.DateAgent.dateFormat\"\n"; while(defined ($str=<SCRIPTFILE>)){ if ($str =~ m/^create database/i || $str =~ m/^use nss\;$/i || $str =~ m/^drop/i){ print OUTFILE "-- skipped declarartion"; }elsif ($str =~ /^grant/i){ $user = substr($str,0,index($str, "@")); @items = split(/ /,$user); $user = $items[$#items]; @items = split(/ /,$str); $pass = $items[$#items]; $pass =~ s/\'//g; #This will still have the ; on it! print OUTFILE "create user $user password $pass\n"; foreach my $tab (@tables){ print OUTFILE "grant select on \"$tab\" to $user\;\n"; } }else{ #Replace quote identifiers $str =~ s/\`/\"/g; #Remove Unknown keywords $str =~ s/ unsigned//i; #Remove unsupported keywords $str =~ s/default '.*' not //i; #Replace different keywords $str =~ s/auto_increment/identity/i; #Odd syntax differences if ($str =~ m/ if exists/i){ $str =~ s/ if exists//i ; $str =~ s/\;$/ if exists\;/; } if ($str =~ m/INTERVAL \d DAY/i){ $str =~ s/INTERVAL //i; $str =~ s/ DAY//i; } #convert case of everything $str =~ s/\"(\w+)\"/\"\U$1\"/g; #here's what makes perl and RE's so cool and crappy! print OUTFILE $str; } } print 'Done'; close(SCRIPTFILE); close(OUTFILE); --- NEW FILE: setupNSS_hsqldb.sql --- -- You must have com.pocolap.hsqldb.datetime.DateAgent -- in your CLASSPATH before running this script create alias DATE_ADD for "com.pocolap.hsqldb.datetime.DateAgent.dateAdd" create alias DATE_FORMAT for "com.pocolap.hsqldb.datetime.DateAgent.dateFormat" -- skipped declarartion -- skipped declarartion -- skipped declarartion-- skipped declarartion-- skipped declarartion-- skipped declarartion create table "CUSTOMERS"( "CUSTOMERID" int identity primary key , "CUSTOMERNUMBER" int, "FIRSTNAME" varchar(50) null, "LASTNAME" varchar(50) null , "ADDRESS" varchar(100) null, "CITY" varchar(60) null, "STATENAME" varchar(50) null, "STATEABBREV" char(2) null, "ZIP" varchar (15) null, "PHONE" varchar(25) null, "BIRTHDATE" date null, "SIZINGGENDER" varchar(20) null , "SHOESIZE" float null, "WIDESIZES" bit null ); create table "SALABLEITEMS"( "SALABLEITEMSID" int identity primary key , "SKU" int null, "DESCRIPTION" varchar(50) null, "ISPRODUCT" bit null, "ISSIZEDEPENDANT" bit null, "COLOR" varchar(30) null, "VENDOR" varchar(80) null, "VENDORNUMBER" int null); create table "CALENDAR"( "CALENDARID" int identity primary key, "DAYDATE" date null, "DAYNAME" varchar(12) null, "DAYABBREV" char(3) null, "DAYOFWEEK" int null, "DAYOFMONTH" int null, "MONTHNAME" varchar(12) null, "MONTHABBREV" char(3) null, "MONTHOFYEAR" int null, "YEAR" int null, "ISHOLIDAY" bit null, "WASOPENED" bit null); create table "SALES"( "CUSTOMERID" int null, "SALABLEITEMSID" int null, "CALENDARID" int null, "AMOUNT" float null, "WEBSALE" bit null); create user nssUser password nssPass; grant select on "CALENDAR" to nssUser; grant select on "CUSTOMERS" to nssUser; grant select on "SALABLEITEMS" to nssUser; grant select on "SALES" to nssUser; insert into "CUSTOMERS" ("CUSTOMERNUMBER","FIRSTNAME","LASTNAME","ADDRESS","CITY", "STATENAME","STATEABBREV","ZIP","PHONE","BIRTHDATE","SIZINGGENDER","SHOESIZE", "WIDESIZES") values (0, 'Nobody', 'Blank', '0 Identity Rd', 'Portland', 'Oregon', 'OR', '97201', '503-555-0001', '1950-01-01','mens','10.0', false); insert into "CUSTOMERS" ("CUSTOMERNUMBER","FIRSTNAME","LASTNAME","ADDRESS","CITY", "STATENAME","STATEABBREV","ZIP","PHONE","BIRTHDATE","SIZINGGENDER","SHOESIZE", "WIDESIZES") values (17, 'Tom', 'Ling', '112 SomeStreet', 'Portland', 'Oregon', 'OR', '97201', '503-555-1233', '1962-02-25','mens','7.5', false); insert into "CUSTOMERS" ("CUSTOMERNUMBER","FIRSTNAME","LASTNAME","ADDRESS","CITY", "STATENAME","STATEABBREV","ZIP","PHONE","BIRTHDATE","SIZINGGENDER","SHOESIZE", "WIDESIZES") values (19, 'Sammy', 'Hamm', '4102 W. First', 'Portland', 'Oregon', 'OR', '97210', '503-555-4754', '1964-10-04','mens','9.0', true); insert into "CUSTOMERS" ("CUSTOMERNUMBER","FIRSTNAME","LASTNAME","ADDRESS","CITY", "STATENAME","STATEABBREV","ZIP","PHONE","BIRTHDATE","SIZINGGENDER","SHOESIZE", "WIDESIZES") values (24, 'Sarah', 'Weston', '421 Sideline St. Apt #4', 'Lake Oswego', 'Oregon', 'OR', '97035', '503-555-8874', '1974-03-21','mens','8.0', false); insert into "CUSTOMERS" ("CUSTOMERNUMBER","FIRSTNAME","LASTNAME","ADDRESS","CITY", "STATENAME","STATEABBREV","ZIP","PHONE","BIRTHDATE","SIZINGGENDER","SHOESIZE", "WIDESIZES") values (25, 'Lisa', 'Weston', '421 Sideline St. Apt #4', 'Lake Oswego', 'Oregon', 'OR', '97035', '503-555-8874', '1991-10-07','girls','2.0', true); insert into "CUSTOMERS" ("CUSTOMERNUMBER","FIRSTNAME","LASTNAME","ADDRESS","CITY", "STATENAME","STATEABBREV","ZIP","PHONE","BIRTHDATE","SIZINGGENDER","SHOESIZE", "WIDESIZES") values (26, 'Bill', 'Franklin', '801 Lake Ave.', 'Portland', 'Oregon', 'OR', '97202', '503-555-4120', '1965-09-21','mens','10.0', false); insert into "CUSTOMERS" ("CUSTOMERNUMBER","FIRSTNAME","LASTNAME","ADDRESS","CITY", "STATENAME","STATEABBREV","ZIP","PHONE","BIRTHDATE","SIZINGGENDER","SHOESIZE", "WIDESIZES") values (28, 'Wilma', 'Barkley', '7021 SW 66th', 'Tigard', 'Oregon', 'OR', '97224', '503-555-4998', '1975-09-21','womens','7.0', false); insert into "CUSTOMERS" ("CUSTOMERNUMBER","FIRSTNAME","LASTNAME","ADDRESS","CITY", "STATENAME","STATEABBREV","ZIP","PHONE","BIRTHDATE","SIZINGGENDER","SHOESIZE", "WIDESIZES") values (29, 'Walter', 'McClellan', '10425 Walling Cir', 'West Linn', 'Oregon', 'OR', '97068', '503-555-8856', '1938-02-29', 'mens','8.5', true); insert into "SALABLEITEMS" ("SKU","DESCRIPTION","ISPRODUCT","ISSIZEDEPENDANT", "COLOR","VENDOR","VENDORNUMBER") values ('234001', '36" laces', true, false, 'Brown', 'Laces USA', '78778'); insert into "SALABLEITEMS" ("SKU","DESCRIPTION","ISPRODUCT","ISSIZEDEPENDANT", "COLOR","VENDOR","VENDORNUMBER") values ('234002', '36" laces', true, false, 'Black', 'Laces USA', '78778'); insert into "SALABLEITEMS" ("SKU","DESCRIPTION","ISPRODUCT","ISSIZEDEPENDANT", "COLOR","VENDOR","VENDORNUMBER") values ('234003', '36" laces', true, false, 'Beige', 'Laces USA', '78778'); insert into "SALABLEITEMS" ("SKU","DESCRIPTION","ISPRODUCT","ISSIZEDEPENDANT", "COLOR","VENDOR","VENDORNUMBER") values ('234004', '36" laces', true, false, 'White', 'Laces USA', '78778'); insert into "SALABLEITEMS" ("SKU","DESCRIPTION","ISPRODUCT","ISSIZEDEPENDANT", "COLOR","VENDOR","VENDORNUMBER") values ('234011', '54" laces', true, false, 'Brown', 'Laces USA', '78778'); insert into "SALABLEITEMS" ("SKU","DESCRIPTION","ISPRODUCT","ISSIZEDEPENDANT", "COLOR","VENDOR","VENDORNUMBER") values ('234012', '54" laces', true, false, 'Black', 'Laces USA', '78778'); insert into "SALABLEITEMS" ("SKU","DESCRIPTION","ISPRODUCT","ISSIZEDEPENDANT", "COLOR","VENDOR","VENDORNUMBER") values ('234013', '54" laces', true, false, 'Beige', 'Laces USA', '78778'); insert into "SALABLEITEMS" ("SKU","DESCRIPTION","ISPRODUCT","ISSIZEDEPENDANT", "COLOR","VENDOR","VENDORNUMBER") values ('234014', '54" laces', true, false, 'White', 'Laces USA', '78778'); insert into "SALABLEITEMS" ("SKU","DESCRIPTION","ISPRODUCT","ISSIZEDEPENDANT", "COLOR","VENDOR","VENDORNUMBER") values ('170211', 'odor insoles', true, true, 'none', 'feeterEater', '117'); insert into "SALABLEITEMS" ("SKU","DESCRIPTION","ISPRODUCT","ISSIZEDEPENDANT", "COLOR","VENDOR","VENDORNUMBER") values ('170211', 'cushioned insoles', true, true, 'none', 'feeterEater', '117'); insert into "SALABLEITEMS" ("SKU","DESCRIPTION","ISPRODUCT","ISSIZEDEPENDANT", "COLOR","VENDOR","VENDORNUMBER") values ('170211', 'cushioned odor insoles', true, true, 'none', 'feeterEater', '117'); insert into "SALABLEITEMS" ("SKU","DESCRIPTION","ISPRODUCT","ISSIZEDEPENDANT", "COLOR","VENDOR","VENDORNUMBER") values ('42187', 'Polish', true, false, 'Black', 'Shine Right', '4242'); insert into "SALABLEITEMS" ("SKU","DESCRIPTION","ISPRODUCT","ISSIZEDEPENDANT", "COLOR","VENDOR","VENDORNUMBER") values ('42188', 'Polish', true, false, 'Brown', 'Shine Right', '4242'); insert into "SALABLEITEMS" ("SKU","DESCRIPTION","ISPRODUCT","ISSIZEDEPENDANT", "COLOR","VENDOR","VENDORNUMBER") values ('42189', 'Polish', true, false, 'Tan', 'Shine Right', '4242'); insert into "SALABLEITEMS" ("SKU","DESCRIPTION","ISPRODUCT","ISSIZEDEPENDANT", "COLOR","VENDOR","VENDORNUMBER") values ('42190', 'Polish', true, false, 'Beige', 'Shine Right', '4242'); insert into "SALABLEITEMS" ("SKU","DESCRIPTION","ISPRODUCT","ISSIZEDEPENDANT", "COLOR","VENDOR","VENDORNUMBER") values ('42191', 'Polish', true, false, 'Red', 'Shine Right', '4242'); insert into "SALABLEITEMS" ("SKU","DESCRIPTION","ISPRODUCT","ISSIZEDEPENDANT", "COLOR","VENDOR","VENDORNUMBER") values ('42192', 'Polish', true, false, 'Blue', 'Shine Right', '4242'); insert into "SALABLEITEMS" ("SKU","DESCRIPTION","ISPRODUCT","ISSIZEDEPENDANT", "COLOR","VENDOR","VENDORNUMBER") values ('100', 'Restitch', false, false, 'none', 'Self', '1'); insert into "SALABLEITEMS" ("SKU","DESCRIPTION","ISPRODUCT","ISSIZEDEPENDANT", "COLOR","VENDOR","VENDORNUMBER") values ('101', 'Glue', false, false, 'none', 'Self', '1'); insert into "SALABLEITEMS" ("SKU","DESCRIPTION","ISPRODUCT","ISSIZEDEPENDANT", "COLOR","VENDOR","VENDORNUMBER") values ('102', 'Shine', false, false, 'none', 'Self', '1'); insert into "CALENDAR" ("DAYDATE") values ('2002-12-29'); insert into "CALENDAR" ("DAYDATE") values ('2003-01-05'); insert into "CALENDAR" ("DAYDATE") values ('2003-01-12'); insert into "CALENDAR" ("DAYDATE") values ('2003-01-19'); insert into "CALENDAR" ("DAYDATE") values ('2003-01-26'); insert into "CALENDAR" ("DAYDATE") values ('2003-02-02'); insert into "CALENDAR" ("DAYDATE") values ('2003-02-09'); insert into "CALENDAR" ("DAYDATE") values ('2003-02-16'); insert into "CALENDAR" ("DAYDATE") values ('2003-02-23'); insert into "CALENDAR" ("DAYDATE") values ('2003-03-02'); insert into "CALENDAR" ("DAYDATE") values ('2003-03-09'); insert into "CALENDAR" ("DAYDATE") values ('2003-03-16'); insert into "CALENDAR" ("DAYDATE") values ('2003-03-23'); insert into "CALENDAR" ("DAYDATE") values ('2003-03-30'); insert into "CALENDAR" ("DAYDATE") values ('2003-04-06'); insert into "CALENDAR" ("DAYDATE") values ('2003-04-13'); insert into "CALENDAR" ("DAYDATE") values ('2003-04-20'); insert into "CALENDAR" ("DAYDATE") values ('2003-04-27'); insert into "CALENDAR" ("DAYDATE") values ('2003-05-04'); insert into "CALENDAR" ("DAYDATE") values ('2003-05-11'); insert into "CALENDAR" ("DAYDATE") values ('2003-05-18'); insert into "CALENDAR" ("DAYDATE") values ('2003-05-25'); insert into "CALENDAR" ("DAYDATE") values ('2003-06-01'); update "CALENDAR" set "DAYNAME" = DATE_FORMAT("DAYDATE", '%W'); insert into "CALENDAR" ("DAYDATE") select DATE_ADD("DAYDATE", 1) from "CALENDAR"; insert into "CALENDAR" ("DAYDATE") select DATE_ADD("DAYDATE", 2) from "CALENDAR" where "DAYNAME" = 'Sunday'; insert into "CALENDAR" ("DAYDATE") select DATE_ADD("DAYDATE", 3) from "CALENDAR" where "DAYNAME" = 'Sunday'; insert into "CALENDAR" ("DAYDATE") select DATE_ADD("DAYDATE", 4) from "CALENDAR" where "DAYNAME" = 'Sunday'; insert into "CALENDAR" ("DAYDATE") select DATE_ADD("DAYDATE", 5) from "CALENDAR" where "DAYNAME" = 'Sunday'; insert into "CALENDAR" ("DAYDATE") select DATE_ADD("DAYDATE", 6) from "CALENDAR" where "DAYNAME" = 'Sunday'; update "CALENDAR" set "DAYNAME" = DATE_FORMAT("DAYDATE", '%W'), "DAYABBREV" = DATE_FORMAT("DAYDATE", '%a'), "DAYOFWEEK" = DATE_FORMAT("DAYDATE", '%w'), "DAYOFMONTH" = DATE_FORMAT("DAYDATE", '%e'), "MONTHNAME" = DATE_FORMAT("DAYDATE", '%M'), "MONTHABBREV" = DATE_FORMAT("DAYDATE", '%b'), "MONTHOFYEAR" = DATE_FORMAT("DAYDATE",'%c'), "YEAR" = DATE_FORMAT("DAYDATE", '%Y'); update "CALENDAR" set "WASOPENED"='1'; update "CALENDAR" set "WASOPENED"='0' where "DAYNAME" = 'Sunday' or "DAYNAME"='Monday'; update "CALENDAR" set "ISHOLIDAY"='1' where "DAYDATE" = '2002-12-31' or "DAYDATE" = '2003-01-01' or "DAYDATE" = '2003-02-17' or "DAYDATE" = '2003-05-05' or "DAYDATE" = '2003-05-26'; insert into "SALES" ("CUSTOMERID","SALABLEITEMSID","CALENDARID","AMOUNT","WEBSALE") values ('1', '3', '72', '2.25', false); insert into "SALES" ("CUSTOMERID","SALABLEITEMSID","CALENDARID","AMOUNT","WEBSALE") values ('1', '2', '72', '2.25', false); insert into "SALES" ("CUSTOMERID","SALABLEITEMSID","CALENDARID","AMOUNT","WEBSALE") values ('1', '2', '72', '2.25', false); insert into "SALES" ("CUSTOMERID","SALABLEITEMSID","CALENDARID","AMOUNT","WEBSALE") values ('4', '7', '101', '2.50', false); insert into "SALES" ("CUSTOMERID","SALABLEITEMSID","CALENDARID","AMOUNT","WEBSALE") values ('4', '7', '100', '2.50', false); insert into "SALES" ("CUSTOMERID","SALABLEITEMSID","CALENDARID","AMOUNT","WEBSALE") values ('3', '5', '47', '2.50', false); insert into "SALES" ("CUSTOMERID","SALABLEITEMSID","CALENDARID","AMOUNT","WEBSALE") values ('3', '6', '47', '2.50', false); insert into "SALES" ("CUSTOMERID","SALABLEITEMSID","CALENDARID","AMOUNT","WEBSALE") values ('3', '7', '47', '2.50', false); insert into "SALES" ("CUSTOMERID","SALABLEITEMSID","CALENDARID","AMOUNT","WEBSALE") values ('3', '6', '116', '2.50', false); insert into "SALES" ("CUSTOMERID","SALABLEITEMSID","CALENDARID","AMOUNT","WEBSALE") values ('3', '10', '116', '15.00', false); insert into "SALES" ("CUSTOMERID","SALABLEITEMSID","CALENDARID","AMOUNT","WEBSALE") values ('7', '11', '75', '17.50', false); insert into "SALES" ("CUSTOMERID","SALABLEITEMSID","CALENDARID","AMOUNT","WEBSALE") values ('7', '20', '75', '10.00', false); insert into "SALES" ("CUSTOMERID","SALABLEITEMSID","CALENDARID","AMOUNT","WEBSALE") values ('2', '15', '105', '7.25', false); insert into "SALES" ("CUSTOMERID","SALABLEITEMSID","CALENDARID","AMOUNT","WEBSALE") values ('2', '10', '105', '15.00', false); insert into "SALES" ("CUSTOMERID","SALABLEITEMSID","CALENDARID","AMOUNT","WEBSALE") values ('2', '13', '105', '7.25', false); insert into "SALES" ("CUSTOMERID","SALABLEITEMSID","CALENDARID","AMOUNT","WEBSALE") values ('2', '18', '105', '8.00', false); |
