[wiaflos-devel] COMMIT - r217 - trunk/database
svn at linuxrulz.org
svn at linuxrulz.org
Sun Aug 10 17:12:04 GMT 2008
Author: nkukard
Date: 2008-08-10 17:12:04 +0000 (Sun, 10 Aug 2008)
New Revision: 217
Modified:
trunk/database/schema.tsql
Log:
* Added indexing to schema
* Added support for client account transactions
Modified: trunk/database/schema.tsql
===================================================================
--- trunk/database/schema.tsql 2008-08-10 16:02:07 UTC (rev 216)
+++ trunk/database/schema.tsql 2008-08-10 17:12:04 UTC (rev 217)
@@ -51,6 +51,10 @@
INSERT INTO soap_caps (GroupID,Capability) VALUES (1,'Clients/Remove');
INSERT INTO soap_caps (GroupID,Capability) VALUES (1,'Clients/Show');
INSERT INTO soap_caps (GroupID,Capability) VALUES (1,'Clients/GL/Show');
+INSERT INTO soap_caps (GroupID,Capability) VALUES (1,'Clients/GL/Add');
+INSERT INTO soap_caps (GroupID,Capability) VALUES (1,'Clients/GL/List');
+INSERT INTO soap_caps (GroupID,Capability) VALUES (1,'Clients/GL/Get');
+INSERT INTO soap_caps (GroupID,Capability) VALUES (1,'Clients/GL/Post');
/* GL */
INSERT INTO soap_caps (GroupID,Capability) VALUES (1,'GL/Show');
INSERT INTO soap_caps (GroupID,Capability) VALUES (1,'GL/Add');
@@ -136,6 +140,8 @@
UNIQUE (Username)
) @CREATE_TABLE_SUFFIX@;
+CREATE INDEX soap_users_idx1 ON soap_users (Username);
+
/* user: admin / password */
INSERT INTO soap_users (Username,Password) VALUES ('admin','{md5}X03MO1qnZdYdgyfeuILPmQ==');
@@ -154,6 +160,8 @@
FOREIGN KEY (GroupID) REFERENCES soap_groups(ID)
) @CREATE_TABLE_SUFFIX@;
+CREATE INDEX soap_grptousr_idx1 ON soap_grptousr (UserID);
+
/* group mappings */
INSERT INTO soap_grptousr (UserID,GroupID) VALUES (1,1);
@@ -174,6 +182,8 @@
UNIQUE (Code)
) @CREATE_TABLE_SUFFIX@;
+CREATE INDEX gl_fincats_idx1 ON gl_fincats (Code);
+
INSERT INTO gl_fincats (Code,Description) VALUES ('A01','Asset');
INSERT INTO gl_fincats (Code,Description) VALUES ('B01','Liability');
INSERT INTO gl_fincats (Code,Description) VALUES ('C01','Equity');
@@ -192,6 +202,8 @@
UNIQUE (Code)
) @CREATE_TABLE_SUFFIX@;
+CREATE INDEX gl_rwcats_idx1 ON gl_rwcats (Code);
+
INSERT INTO gl_rwcats (Code,Description) VALUES ('00','Asset');
INSERT INTO gl_rwcats (Code,Description) VALUES ('20','Liability');
INSERT INTO gl_rwcats (Code,Description) VALUES ('40','Equity');
@@ -219,12 +231,15 @@
FOREIGN KEY (RwCatID) REFERENCES gl_rwcats(ID)
) @CREATE_TABLE_SUFFIX@;
+CREATE INDEX gl_accounts_idx1 ON gl_accounts (ParentGLAccountID);
+CREATE INDEX gl_accounts_idx2 ON gl_accounts (Code);
+CREATE INDEX gl_accounts_idx3 ON gl_accounts (FinCatID);
+CREATE INDEX gl_accounts_idx4 ON gl_accounts (RwCatID);
-
/* General ledger transactions */
CREATE TABLE gl_transactions (
@@ -243,7 +258,9 @@
*/
) @CREATE_TABLE_SUFFIX@;
+CREATE INDEX gl_transactions_idx1 ON gl_transactions (TransactionDate);
+
/* Transaction entries */
CREATE TABLE gl_entries (
@@ -261,6 +278,8 @@
FOREIGN KEY (GLAccountID) REFERENCES gl_accounts(ID)
) @CREATE_TABLE_SUFFIX@;
+CREATE INDEX gl_entries_idx1 ON gl_entries (GLTransactionID);
+CREATE INDEX gl_entries_idx2 ON gl_entries (GLAccountID);
@@ -321,6 +340,7 @@
FOREIGN KEY (TaxTypeID) REFERENCES tax_types(ID)
) @CREATE_TABLE_SUFFIX@;
+CREATE INDEX inventory_idx1 ON inventory (Code);
CREATE TABLE inventory_tracking (
@@ -343,6 +363,10 @@
FOREIGN KEY (GLTransactionID) REFERENCES gl_transactions(ID)
) @CREATE_TABLE_SUFFIX@;
+CREATE INDEX inventory_tracking_idx1 ON inventory_tracking (ParentInventoryTrackingID);
+CREATE INDEX inventory_tracking_idx2 ON inventory_tracking (InventoryID);
+CREATE INDEX inventory_tracking_idx3 ON inventory_tracking (GLTransactionID);
+CREATE INDEX inventory_tracking_idx4 ON inventory_tracking (Closed);
@@ -372,9 +396,10 @@
FOREIGN KEY (GLAccountID) REFERENCES gl_accounts(ID)
) @CREATE_TABLE_SUFFIX@;
+CREATE INDEX clients_idx1 ON clients (Code);
-CREATE TABLE client_addrs (
+CREATE TABLE client_addresses (
ID @SERIAL_TYPE@,
ClientID @SERIAL_REF_TYPE@ NOT NULL,
@@ -385,9 +410,10 @@
FOREIGN KEY (ClientID) REFERENCES clients(ID)
) @CREATE_TABLE_SUFFIX@;
+CREATE INDEX clients_addresses_idx1 ON clients (Code);
-CREATE TABLE client_phnums (
+CREATE TABLE client_phone_numbers (
ID @SERIAL_TYPE@,
ClientID @SERIAL_REF_TYPE@ NOT NULL,
@@ -400,9 +426,11 @@
FOREIGN KEY (ClientID) REFERENCES clients(ID)
) @CREATE_TABLE_SUFFIX@;
+CREATE INDEX client_phone_numbers_idx1 ON client_phone_numbers (ClientID);
+CREATE INDEX client_phone_numbers_idx2 ON client_phone_numbers (ClientID,Type);
-CREATE TABLE client_email (
+CREATE TABLE client_email_addresses (
ID @SERIAL_TYPE@,
ClientID @SERIAL_REF_TYPE@ NOT NULL,
@@ -415,6 +443,7 @@
FOREIGN KEY (ClientID) REFERENCES clients(ID)
) @CREATE_TABLE_SUFFIX@;
+CREATE INDEX client_email_addresses_idx1 ON client_email_addresses (ClientID);
/*
@@ -442,10 +471,12 @@
FOREIGN KEY (GLAccountID) REFERENCES gl_accounts(ID)
) @CREATE_TABLE_SUFFIX@;
+CREATE INDEX suppliers_idx1 ON suppliers (Code);
-CREATE TABLE supplier_addrs (
+
+CREATE TABLE supplier_addresses (
ID @SERIAL_TYPE@,
SupplierID @SERIAL_REF_TYPE@ NOT NULL,
@@ -456,9 +487,11 @@
FOREIGN KEY (SupplierID) REFERENCES suppliers(ID)
) @CREATE_TABLE_SUFFIX@;
+CREATE INDEX suppliers_addresses_idx1 ON supplier_addresses (SupplierID);
-CREATE TABLE supplier_phnums (
+
+CREATE TABLE supplier_phone_numbers (
ID @SERIAL_TYPE@,
SupplierID @SERIAL_REF_TYPE@ NOT NULL,
@@ -471,9 +504,11 @@
FOREIGN KEY (SupplierID) REFERENCES suppliers(ID)
) @CREATE_TABLE_SUFFIX@;
+CREATE INDEX suppliers_phone_numbers_idx1 ON supplier_phone_numbers (SupplierID);
-CREATE TABLE supplier_email (
+
+CREATE TABLE supplier_email_addresses (
ID @SERIAL_TYPE@,
SupplierID @SERIAL_REF_TYPE@ NOT NULL,
@@ -486,8 +521,10 @@
FOREIGN KEY (SupplierID) REFERENCES suppliers(ID)
) @CREATE_TABLE_SUFFIX@;
+CREATE INDEX suppliers_email_addresses_idx1 ON supplier_email_addresses (SupplierID);
+
/*
* P U R C H A S I N G
*/
@@ -518,15 +555,19 @@
GLTransactionID @SERIAL_REF_TYPE@, /* Not null if posted */
- Paid SMALLINT NOT NULL DEFAULT '0', /* Allocations can be referenced with SupplierInvoiceID */
+ Paid SMALLINT NOT NULL DEFAULT '0',
FOREIGN KEY (SupplierID) REFERENCES suppliers(ID),
FOREIGN KEY (GLTransactionID) REFERENCES gl_transactions(ID)
) @CREATE_TABLE_SUFFIX@;
+CREATE INDEX suppliers_invoices_idx1 ON supplier_invoices (SupplierID);
+CREATE INDEX suppliers_invoices_idx2 ON supplier_invoices (Number);
+CREATE INDEX suppliers_invoices_idx3 ON supplier_invoices (SupplierInvoiceNumber);
+
CREATE TABLE supplier_invoice_transactions (
ID @SERIAL_TYPE@,
@@ -542,7 +583,11 @@
FOREIGN KEY (PaymentAllocationID) REFERENCES payment_allocations(ID)
) @CREATE_TABLE_SUFFIX@;
+CREATE INDEX suppliers_invoices_transactions_idx1 ON supplier_invoice_transactions (SupplierInvoiceID);
+CREATE INDEX suppliers_invoices_transactions_idx2 ON supplier_invoice_transactions (SupplierCreditNoteID);
+CREATE INDEX suppliers_invoices_transactions_idx3 ON supplier_invoice_transactions (PaymentAllocationID);
+
CREATE TABLE supplier_invoice_items (
ID @SERIAL_TYPE@,
@@ -575,7 +620,9 @@
FOREIGN KEY (TaxTypeID) REFERENCES tax_types(ID)
) @CREATE_TABLE_SUFFIX@;
+CREATE INDEX suppliers_invoice_items_idx1 ON supplier_invoice_items (SupplierInvoiceID);
+
CREATE TABLE supplier_invoice_item_tracking (
ID @SERIAL_TYPE@,
@@ -591,6 +638,7 @@
FOREIGN KEY (InventoryTrackingID) REFERENCES inventory_tracking(ID)
) @CREATE_TABLE_SUFFIX@;
+CREATE INDEX suppliers_invoice_item_tracking_idx1 ON supplier_invoice_item_tracking (SupplierInvoiceItemID);
@@ -620,7 +668,12 @@
FOREIGN KEY (GLTransactionID) REFERENCES gl_transactions(ID)
) @CREATE_TABLE_SUFFIX@;
+CREATE INDEX payments_idx1 ON payments (SupplierID);
+CREATE INDEX payments_idx2 ON payments (Number);
+CREATE INDEX payments_idx3 ON payments (TransactionDate);
+CREATE INDEX payments_idx4 ON payments (Closed);
+
CREATE TABLE payment_allocations (
ID @SERIAL_TYPE@,
@@ -635,6 +688,8 @@
FOREIGN KEY (SupplierInvoiceID) REFERENCES supplier_invoices(ID)
) @CREATE_TABLE_SUFFIX@;
+CREATE INDEX payment_allocations_idx1 ON payment_allocations (PaymentID);
+CREATE INDEX payment_allocations_idx2 ON payment_allocations (SupplierInvoiceID);
@@ -656,7 +711,7 @@
TaxReference VARCHAR(255), /* Tax reference number, VAT/GST ... etc */
/* Credit note details */
- SupplierCNNumber VARCHAR(255) NOT NULL,
+ SupplierCreditNoteNumber VARCHAR(255) NOT NULL,
IssueDate DATE NOT NULL,
SubTotal DECIMAL(10,2), /* Add up all items */
@@ -675,6 +730,10 @@
FOREIGN KEY (GLTransactionID) REFERENCES gl_transactions(ID)
) @CREATE_TABLE_SUFFIX@;
+CREATE INDEX supplier_creditnotes_idx1 ON supplier_creditnotes (SupplierID);
+CREATE INDEX supplier_creditnotes_idx2 ON supplier_creditnotes (Number);
+CREATE INDEX supplier_creditnotes_idx3 ON supplier_creditnotes (SupplierCreditNoteNumber);
+CREATE INDEX supplier_creditnotes_idx4 ON supplier_creditnotes (Closed);
CREATE TABLE supplier_creditnote_items (
@@ -698,7 +757,9 @@
FOREIGN KEY (SupplierCreditNoteID) REFERENCES supplier_creditnotes(ID)
) @CREATE_TABLE_SUFFIX@;
+CREATE INDEX supplier_creditnote_items_idx1 ON supplier_creditnote_items (SupplierCreditNoteID);
+
CREATE TABLE supplier_creditnote_item_linking (
ID @SERIAL_TYPE@,
@@ -719,7 +780,9 @@
FOREIGN KEY (SupplierInvoiceItemID) REFERENCES supplier_invoice_items(ID)
) @CREATE_TABLE_SUFFIX@;
+CREATE INDEX supplier_creditnote_item_linking_idx1 ON supplier_creditnote_item_linking (SupplierCreditNoteItemID);
+
CREATE TABLE supplier_creditnote_item_tracking (
ID @SERIAL_TYPE@,
@@ -731,6 +794,10 @@
FOREIGN KEY (InventoryTrackingID) REFERENCES inventory_tracking(ID)
) @CREATE_TABLE_SUFFIX@;
+CREATE INDEX supplier_creditnote_item_tracking_idx1 ON supplier_creditnote_item_tracking (SupplierCreditNoteItemID);
+CREATE INDEX supplier_creditnote_item_tracking_idx2 ON supplier_creditnote_item_tracking (InventoryTrackingID);
+
+
CREATE TABLE supplier_creditnote_transactions (
ID @SERIAL_TYPE@,
@@ -746,6 +813,7 @@
FOREIGN KEY (SupplierReceiptAllocationID) REFERENCES supplier_receipt_allocations(ID)
) @CREATE_TABLE_SUFFIX@;
+CREATE INDEX supplier_creditnote_transactions_idx1 ON supplier_creditnote_transactions (SupplierCreditNoteID);
@@ -776,7 +844,12 @@
FOREIGN KEY (GLTransactionID) REFERENCES gl_transactions(ID)
) @CREATE_TABLE_SUFFIX@;
+CREATE INDEX supplier_receipts_idx1 ON supplier_receipts (SupplierID);
+CREATE INDEX supplier_receipts_idx2 ON supplier_receipts (Number);
+CREATE INDEX supplier_receipts_idx3 ON supplier_receipts (TransactionDate);
+CREATE INDEX supplier_receipts_idx4 ON supplier_receipts (Closed);
+
CREATE TABLE supplier_receipt_allocations (
ID @SERIAL_TYPE@,
@@ -793,6 +866,8 @@
FOREIGN KEY (SupplierCreditNoteTransactionID) REFERENCES supplier_creditnote_transactions(ID)
) @CREATE_TABLE_SUFFIX@;
+CREATE INDEX supplier_receipt_allocations_idx1 ON supplier_receipt_allocations (SupplierReceiptID);
+CREATE INDEX supplier_receipt_allocations_idx2 ON supplier_receipt_allocations (SupplierCreditNoteID);
@@ -832,13 +907,17 @@
GLTransactionID @SERIAL_REF_TYPE@, /* Not null if posted */
- Paid SMALLINT NOT NULL DEFAULT '0', /* Allocations can be referenced with SupplierInvoiceID */
+ Paid SMALLINT NOT NULL DEFAULT '0',
FOREIGN KEY (ClientID) REFERENCES clients(ID),
FOREIGN KEY (GLTransactionId) REFERENCES gl_transactions(ID)
) @CREATE_TABLE_SUFFIX@;
+CREATE INDEX invoices_idx1 ON invoices (ClientID);
+CREATE INDEX invoices_idx2 ON invoices (Number);
+CREATE INDEX invoices_idx3 ON invoices (IssueDate);
+CREATE INDEX invoices_idx4 ON invoices (DueDate);
CREATE TABLE invoice_items (
@@ -871,7 +950,9 @@
FOREIGN KEY (TaxTypeID) REFERENCES tax_types(ID)
) @CREATE_TABLE_SUFFIX@;
+CREATE INDEX invoice_items_idx1 ON invoice_items (InvoiceID);
+
CREATE TABLE invoice_item_tracking (
ID @SERIAL_TYPE@,
@@ -883,7 +964,10 @@
FOREIGN KEY (InventoryTrackingID) REFERENCES inventory_tracking(ID)
) @CREATE_TABLE_SUFFIX@;
+CREATE INDEX invoice_item_tracking_idx1 ON invoice_item_tracking (InvoiceItemID);
+CREATE INDEX invoice_item_tracking_idx2 ON invoice_item_tracking (InventoryTrackingID);
+
CREATE TABLE invoice_transactions (
ID @SERIAL_TYPE@,
@@ -899,9 +983,62 @@
FOREIGN KEY (ReceiptAllocationID) REFERENCES receipt_allocations(ID)
) @CREATE_TABLE_SUFFIX@;
+CREATE INDEX invoice_transactions_idx1 ON invoice_transactions (InvoiceID);
+
/*
+ * C L I E N T M A N U A L G L A C C O U N T T R A N S A C T I O N S
+ */
+
+CREATE TABLE client_account_transactions (
+ ID @SERIAL_TYPE@,
+
+ ClientID @SERIAL_REF_TYPE@ NOT NULL,
+
+ Number VARCHAR(255), /* Transaction number */
+
+ /* Our reference */
+ Reference VARCHAR(255) NOT NULL,
+
+ /* Transaction details */
+ GLAccountID @SERIAL_REF_TYPE@ NOT NULL, /* destination account */
+ TransactionDate DATE NOT NULL,
+ Amount DECIMAL(10,2),
+
+ GLTransactionID @SERIAL_REF_TYPE@, /* Not null if posted */
+
+ Closed SMALLINT NOT NULL DEFAULT '0',
+
+
+ FOREIGN KEY (ClientID) REFERENCES clients(ID),
+ FOREIGN KEY (GLAccountID) REFERENCES gl_accounts(ID),
+ FOREIGN KEY (GLTransactionID) REFERENCES gl_transactions(ID)
+) @CREATE_TABLE_SUFFIX@;
+
+CREATE INDEX client_account_transactions_idx1 ON client_account_transactions (ClientID);
+CREATE INDEX client_account_transactions_idx2 ON client_account_transactions (TransactionDate);
+
+/* This is part of receipting */
+CREATE TABLE client_account_transaction_allocations (
+ ID @SERIAL_TYPE@,
+
+ AccountTransactionID @SERIAL_REF_TYPE@ NOT NULL,
+
+/* CreditNoteID @SERIAL_REF_TYPE@, */
+ ReceiptAllocationID @SERIAL_REF_TYPE@,
+
+ Amount DECIMAL(10,2) NOT NULL,
+
+ FOREIGN KEY (AccountTransactionID) REFERENCES client_account_transactions(ID),
+/* FOREIGN KEY (CreditNoteID) REFERENCES creditnotes(ID), */
+ FOREIGN KEY (ReceiptAllocationID) REFERENCES receipt_allocations(ID)
+) @CREATE_TABLE_SUFFIX@;
+
+CREATE INDEX client_account_transaction_allocations_idx ON client_account_transaction_allocations (AccountTransactionID);
+
+
+/*
* R E C E I P T S
*/
@@ -927,23 +1064,37 @@
FOREIGN KEY (GLTransactionID) REFERENCES gl_transactions(ID)
) @CREATE_TABLE_SUFFIX@;
+CREATE INDEX receipts_idx1 ON receipts (ClientID);
+CREATE INDEX receipts_idx2 ON receipts (Number);
+CREATE INDEX receipts_idx3 ON receipts (TransactionDate);
+CREATE INDEX receipts_idx4 ON receipts (Closed);
+
CREATE TABLE receipt_allocations (
ID @SERIAL_TYPE@,
ReceiptID @SERIAL_REF_TYPE@ NOT NULL,
- InvoiceID @SERIAL_REF_TYPE@ NOT NULL,
-
+
+ InvoiceID @SERIAL_REF_TYPE@,
InvoiceTransactionID @SERIAL_REF_TYPE@,
+ AccountTransactionID @SERIAL_REF_TYPE@,
+ AccountTransactionAllocationID @SERIAL_REF_TYPE@,
+
Amount DECIMAL(10,2),
FOREIGN KEY (ReceiptID) REFERENCES receipts(ID),
+
FOREIGN KEY (InvoiceID) REFERENCES invoices(ID),
- FOREIGN KEY (InvoiceTransactionID) REFERENCES invoice_transactions(ID)
+ FOREIGN KEY (InvoiceTransactionID) REFERENCES invoice_transactions(ID),
+
+ FOREIGN KEY (AccountTransactionID) REFERENCES client_account_transactions(ID),
+ FOREIGN KEY (AccountTransactionAllocationID) REFERENCES client_account_transaction_allocations(ID)
+
) @CREATE_TABLE_SUFFIX@;
+CREATE INDEX receipt_allocations_idx1 ON receipt_allocations (ReceiptID);
More information about the wiaflos-devel
mailing list