Menu
▾
▴
[Openposs-cvs-commits] CVS: Database MySQL_Creation_Script,1.11,1.12
|
From: <ope...@li...> - 2002-07-09 01:14:55
|
Update of /cvsroot/openposs/Database In directory usw-pr-cvs1:/tmp/cvs-serv24721 Modified Files: MySQL_Creation_Script Log Message: Numerous changes. See CVS Log Index: MySQL_Creation_Script =================================================================== RCS file: /cvsroot/openposs/Database/MySQL_Creation_Script,v retrieving revision 1.11 retrieving revision 1.12 diff -C2 -d -r1.11 -r1.12 *** MySQL_Creation_Script 30 Jun 2002 16:07:26 -0000 1.11 --- MySQL_Creation_Script 9 Jul 2002 01:14:52 -0000 1.12 *************** *** 1,257 **** ! # Creation Script for the database of openPOS ! # ! # Database Name : openPOS ! # Database Version : Development V0.1 ! # Project Name : openPOS ! # Project Homepage : http://openposs.sourceforge.net/ ! # ! # Created : Friday April 5, 2002 ! # Creator : Brian A Cheeseman ! # ! # RDBMS : MySQL ! # ! ################################################################################ ! # Last Update # Who # Changes Made # ! #------------------------------------------------------------------------------# ! # Apr 5, 2002 # BAC # Initial Creation # ! # # # # ! # Jun 24, 2002 # CWS # Added Setup Instructions section. Added field 'term_ip' # ! # # # to table 'openPOS.Transactions'. The purpose of this # ! # # # is to provide a tracking methond for which terminal the # ! # # # transaction originated from. Also added 'HomePhone', # ! # # # 'WorkPhone', and 'OtherPhone' to table # ! # # # 'openPOS.Customer'. # ! # Jun 27,2002 # CWS # Added modules table. # ! # Jun 28,2002 # CWS # Primed modules table with generic values. once 'real' # ! # # # modules are avaiable, these need to be removed # ! ################################################################################ ! # ! # Who Key ! # BAC - Brian A Cheeseman ! # CWS - Chris W Shaffer ! ! # ! # Setup Instructions ! # ! # There are a few different ways to create teh database and insert the ! # necessary records. The most straight-forward way is through the ! # command line. At your terminal prompt (DOS window), enter the following ! # command: ! # ! # %> mysql -uUSER -p /path/to/MySQL_Creation_Script.txt ! # ! # where '%>' is your prompt (don't type that!) and USER is your database ! # username. You will be prompted for your database password, enter it, and ! # if no error messages were generated, you have a freshly installed copy of ! # the OpenPOS database and all the required tables. Now, to confirm that ! # the database exists: ! # ! # %> mysql -uUSER -p ! # ! # Once again, you will be prompted for your password. After you enter you ! # password, you will be greeted with the following prompt: ! # ! # mysql> ! # ! # Execute the following commands: ! # ! # mysql> USE openPOS; ! # ! # mysql> SHOW tables; ! # ! # If all went well with your installation, you will be greeted with ! # the following output: ! # ! # +-------------------+ ! # | Tables_in_openPOS | ! # +-------------------+ ! # | auditlog | ! # | configuration | ! # | customers | ! # | grouprights | ! # | orderitems | ! # | orders | ! # | stock_list | ! # | systemgroups | ! # | systemusers | ! # | transactions | ! # | userrights | ! # | usertogroup | ! # +-------------------+ ! # 12 rows in set (0.00 sec) ! # ! # Congratulations, you are now ready to procede with the rest of the ! # OpenPOSS setup. ! # ! # ! ######################################################################## ! ! ! # ! # Create the database ! # ! CREATE DATABASE openPOS; ! USE openPOS; ! ! # ! # Create the Configuration Table ! # ! # This table is used to hold System Wide configurational information. ! CREATE TABLE openPOS.Configuration (`Parameter` varchar(255) NOT NULL default "", ! `Value` varchar(255) NOT NULL default ""); ! ! # ! # Insert the default configuration into the configuration table ! # ! INSERT INTO openPOS.Configuration (`Parameter`, `Value`) VALUES ("DBVersion", "DEV 0.1"); ! INSERT INTO openPOS.Configuration (`Parameter`, `Value`) VALUES ("modUserManage", "On"); ! INSERT INTO openPOS.Configuration (`Parameter`, `Value`) VALUES ("modUserLogin", "On"); ! INSERT INTO openPOS.Configuration (`Parameter`, `Value`) VALUES ("modUserSecurity", "On"); ! INSERT INTO openPOS.Configuration (`Parameter`, `Value`) VALUES ("modUserTypes", "On"); ! ! # ! # Create the Stock List Table ! # ! # This table contains all the information describing the products available. ! CREATE TABLE openPOS.Stock_List (sid int auto_increment PRIMARY KEY, ! Description varchar(255) NOT NULL default "", ! OrderCode varchar(50) NOT NULL default "", ! ScanCode varchar(50) NOT NULL default "", ! Price decimal(7,2) NOT NULL default 0.00, ! StockLevel int NOT NULL default 0, ! MinStockLevel int NOT NULL default 0, ! StorageCap int NOT NULL default 0, ! ShelfCap int NOT NULL default 0, ! KEY idx_ScanCode (ScanCode(10)) ); ! ! # ! # Create the Users Table ! # ! # This table holds a list of users permitted to access the system. ! CREATE TABLE openPOS.SystemUsers (uid int auto_increment PRIMARY KEY, ! UserName varchar(255) NOT NULL default "", ! FullName varchar(255) NOT NULL default "", ! Password varchar(255) NOT NULL default "", ! Active tinyint NOT NULL default 0, ! SuperUser tinyint NOT NULL default 0); ! ! # Insert the initial SuperUser into the Users Table. ! # Password field correlates to "openPOS". The password of this user should be changed ! # immediately on a production server, as this account can change anything within the system. ! INSERT INTO openPOS.SystemUsers (UserName, FullName, Password, Active, SuperUser) VALUES ("Admin", "openPOS Admin User", "7c524cbe562633af1c281279e048dc5b", 1, 1); ! ! # ! # Create the Groups Table ! # ! # This table holds a list of Groups which should be user definable. ! CREATE TABLE openPOS.SystemGroups (gid int auto_increment PRIMARY KEY, ! GroupName varchar(255) NOT NULL default "", ! Active tinyint NOT NULL default 1); ! ! # ! # Table structure for table `modules` ! # ! ! CREATE TABLE modules ( ! name varchar(25) NOT NULL default '', ! desciption varchar(100) default NULL, ! status enum('A','I') NOT NULL default 'I', ! menu enum('Y','N') NOT NULL default 'N', ! UNIQUE KEY name (name)) TYPE=MyISAM; ! # -------------------------------------------------------- ! #--------REMOVE ME ONCE REAL MODULES ARE AVAILBE!!!------- ! ! INSERT INTO modules VALUES ('Transaction', 'The transaction module', 'A', 'Y'); ! INSERT INTO modules VALUES ('Inventory', 'The inventory management module', 'A', 'Y'); ! INSERT INTO modules VALUES ('Quote', NULL, 'A', 'Y'); ! INSERT INTO modules VALUES ('Scheduling', NULL, 'I', 'Y'); ! ! #********************************************************** ! # -------------------------------------------------------- ! # -------------------------------------------------------- ! ! ! # ! # Create the Users to Groups Crosslink table ! # ! # This table is used to link users to the groups which they belong. ! CREATE TABLE openPOS.UserToGroup (uid int NOT NULL PRIMARY KEY, ! gid int NOT NULL, ! KEY idx_gid (gid)); ! ! # ! # Create the Rights Table for Users ! # ! # This table allows for giving specific users specific rights. ! CREATE TABLE openPOS.UserRights (uid int PRIMARY KEY, ! Access int); ! ! # ! # Create the Rights Table for Groups ! # ! # This table allows for setting rights for a group of users. ! CREATE TABLE openPOS.GroupRights (gid int PRIMARY KEY, ! ACCESS int); ! ! # ! # Create the Audit Log Table ! # ! # This table is used to log all actions performed by the users. ! CREATE TABLE openPOS.AuditLog (aid int auto_increment PRIMARY KEY, ! uid int, ! DT datetime, ! Action varchar(255), ! KEY idx_AuditLog_uid (uid)); ! ! # ! # Create the Transaction Tracking Table ! # ! # This table is used to track transactions performed by users. ! CREATE TABLE openPOS.Transactions (tid int auto_increment PRIMARY KEY, ! uid int, ! oid int, ! term_ip varchar(15), ! DT datetime, ! KEY idx_Transaction_uid (uid)); ! ! # ! # Create the Order Table ! # ! # This table is used to link an Order/Transaction Number to ! # the items involved in the Order/Transaction. ! CREATE TABLE openPOS.OrderItems (iid int PRIMARY KEY, ! sid int); ! ! # ! # Create the Orders table ! # ! # This table is used to allow customer orders to be entered in the ! # system. This allows for auto-notification to users that an order ! # is able to be completed for a given customer. ! CREATE TABLE openPOS.Orders (oid int auto_increment PRIMARY KEY, ! iid int, ! cid int); ! ! # ! # Create the Customer Details table ! # ! # This table allows for storage of customer contact details. This ! # information will be used for orders which are placed through ! # the retail outlet. ! CREATE TABLE openPOS.Customers (cid int auto_increment PRIMARY KEY, ! Surname varchar(255), ! GivenNames varchar(255), ! PostalAddress varchar(255), ! PostalSuburb varchar(255), ! PostalState char(5), ! PostalZip char(10), ! PostalCountry varchar(255), ! BillingAddress varchar(255), ! BillingSuburb varchar(255), ! BillingState char(5), ! BillingZip char(10), ! BillingCountry varchar(255), ! HomePhone varchar(25) NULL, ! WorkPhone varchar(25) NULL, ! OtherPhone varchar(25) NULL, ! KEY idx_Customer_Surname (Surname(10))); --- 1,282 ---- ! # Creation Script for the database of openPOS ! # ! # Database Name : openPOS ! # Database Version : Development V0.1 ! # Project Name : openPOS ! # Project Homepage : http://openposs.sourceforge.net/ ! # ! # Created : Friday April 5, 2002 ! # Creator : Brian A Cheeseman ! # ! # RDBMS : MySQL ! # ! ################################################################################ ! # Last Update # Who # Changes Made # ! #------------------------------------------------------------------------------# ! # Apr 5, 2002 # BAC # Initial Creation # ! # # # # ! # Jun 24, 2002 # CWS # Added Setup Instructions section. Added field 'term_ip' # ! # # # to table 'openPOS.Transactions'. The purpose of this # ! # # # is to provide a tracking methond for which terminal the # ! # # # transaction originated from. Also added 'HomePhone', # ! # # # 'WorkPhone', and 'OtherPhone' to table # ! # # # 'openPOS.Customer'. # ! # Jun 27,2002 # CWS # Added modules table. # ! # Jun 28,2002 # CWS # Primed modules table with generic values. once 'real' # ! # # # modules are avaiable, these need to be removed # ! ################################################################################ ! # ! # Who Key ! # BAC - Brian A Cheeseman ! # CWS - Chris W Shaffer ! ! # ! # Setup Instructions ! # ! # There are a few different ways to create teh database and insert the ! # necessary records. The most straight-forward way is through the ! # command line. At your terminal prompt (DOS window), enter the following ! # command: ! # ! # %> mysql -uUSER -p /path/to/MySQL_Creation_Script.txt ! # ! # where '%>' is your prompt (don't type that!) and USER is your database ! # username. You will be prompted for your database password, enter it, and ! # if no error messages were generated, you have a freshly installed copy of ! # the OpenPOS database and all the required tables. Now, to confirm that ! # the database exists: ! # ! # %> mysql -uUSER -p ! # ! # Once again, you will be prompted for your password. After you enter you ! # password, you will be greeted with the following prompt: ! # ! # mysql> ! # ! # Execute the following commands: ! # ! # mysql> USE openPOS; ! # ! # mysql> SHOW tables; ! # ! # If all went well with your installation, you will be greeted with ! # the following output: ! # ! # +-------------------+ ! # | Tables_in_openPOS | ! # +-------------------+ ! # | auditlog | ! # | configuration | ! # | customers | ! # | grouprights | ! # | orderitems | ! # | orders | ! # | stock_list | ! # | systemgroups | ! # | systemusers | ! # | transactions | ! # | userrights | ! # | usertogroup | ! # +-------------------+ ! # 12 rows in set (0.00 sec) ! # ! # Congratulations, you are now ready to procede with the rest of the ! # OpenPOSS setup. ! # ! # ! ######################################################################## ! ! ! # ! # Create the database ! # ! CREATE DATABASE openPOS; ! USE openPOS; ! ! # ! # Create the Configuration Table ! # ! # This table is used to hold System Wide configurational information. ! CREATE TABLE openPOS.Configuration (`Parameter` varchar(255) NOT NULL default "", ! `Value` varchar(255) NOT NULL default ""); ! ! # ! # Insert the default configuration into the configuration table ! # ! INSERT INTO openPOS.Configuration (`Parameter`, `Value`) VALUES ("DBVersion", "DEV 0.1"); ! INSERT INTO openPOS.Configuration (`Parameter`, `Value`) VALUES ("modUserManage", "On"); ! INSERT INTO openPOS.Configuration (`Parameter`, `Value`) VALUES ("modUserLogin", "On"); ! INSERT INTO openPOS.Configuration (`Parameter`, `Value`) VALUES ("modUserSecurity", "On"); ! INSERT INTO openPOS.Configuration (`Parameter`, `Value`) VALUES ("modUserTypes", "On"); ! INSERT INTO openPOS.Configuration (`Parameter`, `Value`) VALUES ("systemInstalled", "No"); ! ! # ! # Create the Stock List Table ! # ! # This table contains all the information describing the products available. ! CREATE TABLE openPOS.Stock_List (sku int(6) unsigned zerofill NOT NULL auto_increment, ! Product varchar(100) NOT NULL default '', ! Description text, ! catagory int(6) NOT NULL default '999999', ! OrderCode varchar(50) NOT NULL default "", ! ScanCode varchar(50) NOT NULL default "", ! Cost decimal(7,2) unsigned NOT NULL default '0.00', ! Price decimal(7,2) unsigned NOT NULL default 0.00, ! StockLevel int NOT NULL default 0, ! MinStockLevel int NOT NULL default 0, ! StorageCap int NOT NULL default 0, ! ShelfCap int NOT NULL default 0, ! KEY idx_ScanCode (ScanCode(10)) ); ! ! # ! # Create the Users Table ! # ! # This table holds a list of users permitted to access the system. ! CREATE TABLE openPOS.SystemUsers (uid int auto_increment PRIMARY KEY, ! UserName varchar(255) NOT NULL default "", ! FullName varchar(255) NOT NULL default "", ! Password varchar(255) NOT NULL default "", ! Active tinyint NOT NULL default 0, ! SuperUser tinyint NOT NULL default 0); ! ! # Insert the initial SuperUser into the Users Table. ! # Password field correlates to "openPOS". The password of this user should be changed ! # immediately on a production server, as this account can change anything within the system. ! INSERT INTO openPOS.SystemUsers (UserName, FullName, Password, Active, SuperUser) VALUES ("Admin", "openPOS Admin User", "7c524cbe562633af1c281279e048dc5b", 1, 1); ! ! # ! # Create the Groups Table ! # ! # This table holds a list of Groups which should be user definable. ! CREATE TABLE openPOS.SystemGroups (gid int auto_increment PRIMARY KEY, ! GroupName varchar(255) NOT NULL default "", ! Active tinyint NOT NULL default 1); ! ! # ! # Table structure for table `modules` ! # ! ! CREATE TABLE openPOS.modules ( ! name varchar(25) NOT NULL default '', ! desciption varchar(100) default NULL, ! version varchar(15) NOT NULL default '', ! status enum('A','I') NOT NULL default 'I', ! menu enum('Y','N') NOT NULL default 'N', ! UNIQUE KEY name (name)) TYPE=MyISAM; ! # -------------------------------------------------------- ! #--------REMOVE ME ONCE REAL MODULES ARE AVAILBE!!!------- ! ! INSERT INTO modules VALUES ('Transaction', 'The transaction module', 'A', 'Y'); ! INSERT INTO modules VALUES ('Inventory', 'The inventory management module', 'A', 'Y'); ! INSERT INTO modules VALUES ('Quote', NULL, 'A', 'Y'); ! INSERT INTO modules VALUES ('Scheduling', NULL, 'I', 'Y'); ! ! #********************************************************** ! # -------------------------------------------------------- ! # -------------------------------------------------------- ! ! ! # ! # Create the Users to Groups Crosslink table ! # ! # This table is used to link users to the groups which they belong. ! CREATE TABLE openPOS.UserToGroup (uid int NOT NULL PRIMARY KEY, ! gid int NOT NULL, ! KEY idx_gid (gid)); ! ! # ! # Create the Rights Table for Users ! # ! # This table allows for giving specific users specific rights. ! CREATE TABLE openPOS.UserRights (uid int PRIMARY KEY, ! Access int); ! ! # ! # Create the Rights Table for Groups ! # ! # This table allows for setting rights for a group of users. ! CREATE TABLE openPOS.GroupRights (gid int PRIMARY KEY, ! ACCESS int); ! ! # ! # Create the Audit Log Table ! # ! # This table is used to log all actions performed by the users. ! CREATE TABLE openPOS.AuditLog (aid int auto_increment PRIMARY KEY, ! uid int, ! DT datetime, ! Action varchar(255), ! KEY idx_AuditLog_uid (uid)); ! ! # ! # Create the Transaction Tracking Table ! # ! # This table is used to track transactions performed by users. ! CREATE TABLE openPOS.Transactions (tid int auto_increment PRIMARY KEY, ! uid int, ! oid int, ! term_ip varchar(15), ! DT datetime, ! KEY idx_Transaction_uid (uid)); ! ! # ! # Create the Order Table ! # ! # This table is used to link an Order/Transaction Number to ! # the items involved in the Order/Transaction. ! CREATE TABLE openPOS.OrderItems (iid int PRIMARY KEY, ! sid int); ! ! # ! # Create the Orders table ! # ! # This table is used to allow customer orders to be entered in the ! # system. This allows for auto-notification to users that an order ! # is able to be completed for a given customer. ! CREATE TABLE openPOS.Orders (oid int auto_increment PRIMARY KEY, ! iid int, ! cid int); ! ! # ! # Create the Customer Details table ! # ! # This table allows for storage of customer contact details. This ! # information will be used for orders which are placed through ! # the retail outlet. ! CREATE TABLE openPOS.Customers (cid int auto_increment PRIMARY KEY, ! Surname varchar(255), ! GivenNames varchar(255), ! PostalAddress varchar(255), ! PostalSuburb varchar(255), ! PostalState char(5), ! PostalZip char(10), ! PostalCountry varchar(255), ! BillingAddress varchar(255), ! BillingSuburb varchar(255), ! BillingState char(5), ! BillingZip char(10), ! BillingCountry varchar(255), ! HomePhone varchar(25) NULL, ! WorkPhone varchar(25) NULL, ! OtherPhone varchar(25) NULL, ! KEY idx_Customer_Surname (Surname(10))); ! ! # ! # Table structure for table `inv_catagories` ! # ! # This table will keep track of the catagories for the inventory system. Two numbers are ! # reserverd: 000000 and 999999. 000000 in the parent field indicates that the current row ! # is at the top level of the catagory system. 999999 means that the catagory system is not ! # being used. ! ! CREATE TABLE inv_catagories (id int(6) unsigned NOT NULL auto_increment, ! name varchar(50) NOT NULL default '', ! description varchar(150) default NULL, ! parent int(6) unsigned NOT NULL default '000000', ! active enum('A','I') NOT NULL default 'A', ! PRIMARY KEY (id) ! ) TYPE=MyISAM; ! ! ! ! |
