summaryrefslogtreecommitdiff
path: root/sql/Pg-tables.sql
blob: 7c1676bfd2675427087401d3e3a40ea03e2ae049 (plain)
  1. --
  2. CREATE SEQUENCE id start 10000;
  3. SELECT nextval ('id');
  4. --
  5. CREATE SEQUENCE invoiceid;
  6. SELECT nextval ('invoiceid');
  7. --
  8. CREATE SEQUENCE orderitemsid;
  9. SELECT nextval ('orderitemsid');
  10. --
  11. CREATE SEQUENCE jcitemsid;
  12. SELECT nextval ('jcitemsid');
  13. --
  14. CREATE TABLE makemodel (
  15. parts_id int,
  16. make text,
  17. model text
  18. );
  19. --
  20. CREATE TABLE gl (
  21. id int DEFAULT nextval ( 'id' ),
  22. reference text,
  23. description text,
  24. transdate date DEFAULT current_date,
  25. employee_id int,
  26. notes text,
  27. department_id int default 0
  28. );
  29. --
  30. CREATE TABLE chart (
  31. id int DEFAULT nextval ( 'id' ),
  32. accno text NOT NULL,
  33. description text,
  34. charttype char(1) DEFAULT 'A',
  35. category char(1),
  36. link text,
  37. gifi_accno text,
  38. contra bool DEFAULT 'f',
  39. PRIMARY KEY (id)
  40. );
  41. --
  42. CREATE TABLE gifi (
  43. accno text,
  44. description text
  45. );
  46. --
  47. CREATE TABLE defaults (
  48. inventory_accno_id int,
  49. income_accno_id int,
  50. expense_accno_id int,
  51. fxgain_accno_id int,
  52. fxloss_accno_id int,
  53. sinumber text,
  54. sonumber text,
  55. yearend varchar(5),
  56. weightunit varchar(5),
  57. businessnumber text,
  58. version varchar(8),
  59. curr text,
  60. closedto date,
  61. revtrans bool DEFAULT 'f',
  62. ponumber text,
  63. sqnumber text,
  64. rfqnumber text,
  65. audittrail bool default 'f',
  66. vinumber text,
  67. employeenumber text,
  68. partnumber text,
  69. customernumber text,
  70. vendornumber text,
  71. glnumber text,
  72. projectnumber text
  73. );
  74. INSERT INTO defaults (version) VALUES ('2.6.18');
  75. --
  76. CREATE TABLE acc_trans (
  77. trans_id int,
  78. chart_id int NOT NULL REFERENCES chart (id),
  79. amount NUMERIC,
  80. transdate date DEFAULT current_date,
  81. source text,
  82. cleared bool DEFAULT 'f',
  83. fx_transaction bool DEFAULT 'f',
  84. project_id int,
  85. memo text,
  86. invoice_id int
  87. );
  88. --
  89. CREATE TABLE invoice (
  90. id int DEFAULT nextval ( 'invoiceid' ),
  91. trans_id int,
  92. parts_id int,
  93. description text,
  94. qty integer,
  95. allocated integer,
  96. sellprice NUMERIC,
  97. fxsellprice NUMERIC,
  98. discount float4, -- jd: check into this
  99. assemblyitem bool DEFAULT 'f',
  100. unit varchar(5),
  101. project_id int,
  102. deliverydate date,
  103. serialnumber text,
  104. notes text
  105. );
  106. --
  107. CREATE TABLE customer (
  108. id int default nextval('id'),
  109. name varchar(64),
  110. address1 varchar(32),
  111. address2 varchar(32),
  112. city varchar(32),
  113. state varchar(32),
  114. zipcode varchar(10),
  115. country varchar(32),
  116. contact varchar(64),
  117. phone varchar(20),
  118. fax varchar(20),
  119. email text,
  120. notes text,
  121. discount numeric,
  122. taxincluded bool default 'f',
  123. creditlimit NUMERIC default 0,
  124. terms int2 default 0,
  125. customernumber varchar(32),
  126. cc text,
  127. bcc text,
  128. business_id int,
  129. taxnumber varchar(32),
  130. sic_code varchar(6),
  131. iban varchar(34),
  132. bic varchar(11),
  133. employee_id int,
  134. language_code varchar(6),
  135. pricegroup_id int,
  136. curr char(3),
  137. startdate date,
  138. enddate date
  139. );
  140. --
  141. --
  142. CREATE TABLE parts (
  143. id int DEFAULT nextval ( 'id' ),
  144. partnumber text,
  145. description text,
  146. unit varchar(5),
  147. listprice NUMERIC,
  148. sellprice NUMERIC,
  149. lastcost NUMERIC,
  150. priceupdate date DEFAULT current_date,
  151. weight numeric,
  152. onhand numeric DEFAULT 0,
  153. notes text,
  154. makemodel bool DEFAULT 'f',
  155. assembly bool DEFAULT 'f',
  156. alternate bool DEFAULT 'f',
  157. rop float4, -- jd: what is this
  158. inventory_accno_id int,
  159. income_accno_id int,
  160. expense_accno_id int,
  161. bin text,
  162. obsolete bool DEFAULT 'f',
  163. bom bool DEFAULT 'f',
  164. image text,
  165. drawing text,
  166. microfiche text,
  167. partsgroup_id int,
  168. project_id int,
  169. avgcost NUMERIC
  170. );
  171. --
  172. CREATE TABLE assembly (
  173. id int,
  174. parts_id int,
  175. qty numeric,
  176. bom bool,
  177. adj bool
  178. ) WITH OIDS;
  179. --
  180. CREATE TABLE ar (
  181. id int DEFAULT nextval ( 'id' ),
  182. invnumber text,
  183. transdate date DEFAULT current_date,
  184. customer_id int,
  185. taxincluded bool,
  186. amount NUMERIC,
  187. netamount NUMERIC,
  188. paid NUMERIC,
  189. datepaid date,
  190. duedate date,
  191. invoice bool DEFAULT 'f',
  192. shippingpoint text,
  193. terms int2 DEFAULT 0,
  194. notes text,
  195. curr char(3),
  196. ordnumber text,
  197. employee_id int,
  198. till varchar(20),
  199. quonumber text,
  200. intnotes text,
  201. department_id int default 0,
  202. shipvia text,
  203. language_code varchar(6),
  204. ponumber text
  205. );
  206. --
  207. CREATE TABLE ap (
  208. id int DEFAULT nextval ( 'id' ),
  209. invnumber text,
  210. transdate date DEFAULT current_date,
  211. vendor_id int,
  212. taxincluded bool DEFAULT 'f',
  213. amount NUMERIC,
  214. netamount NUMERIC,
  215. paid NUMERIC,
  216. datepaid date,
  217. duedate date,
  218. invoice bool DEFAULT 'f',
  219. ordnumber text,
  220. curr char(3),
  221. notes text,
  222. employee_id int,
  223. till varchar(20),
  224. quonumber text,
  225. intnotes text,
  226. department_id int DEFAULT 0,
  227. shipvia text,
  228. language_code varchar(6),
  229. ponumber text,
  230. shippingpoint text,
  231. terms int2 DEFAULT 0
  232. );
  233. --
  234. CREATE TABLE partstax (
  235. parts_id int,
  236. chart_id int
  237. );
  238. --
  239. CREATE TABLE tax (
  240. chart_id int,
  241. rate numeric,
  242. taxnumber text,
  243. validto date
  244. );
  245. --
  246. CREATE TABLE customertax (
  247. customer_id int,
  248. chart_id int
  249. );
  250. --
  251. CREATE TABLE vendortax (
  252. vendor_id int,
  253. chart_id int
  254. );
  255. --
  256. CREATE TABLE oe (
  257. id int default nextval('id'),
  258. ordnumber text,
  259. transdate date default current_date,
  260. vendor_id int,
  261. customer_id int,
  262. amount NUMERIC,
  263. netamount NUMERIC,
  264. reqdate date,
  265. taxincluded bool,
  266. shippingpoint text,
  267. notes text,
  268. curr char(3),
  269. employee_id int,
  270. closed bool default 'f',
  271. quotation bool default 'f',
  272. quonumber text,
  273. intnotes text,
  274. department_id int default 0,
  275. shipvia text,
  276. language_code varchar(6),
  277. ponumber text,
  278. terms int2 DEFAULT 0
  279. );
  280. --
  281. CREATE TABLE orderitems (
  282. id int default nextval('orderitemsid'),
  283. trans_id int,
  284. parts_id int,
  285. description text,
  286. qty numeric,
  287. sellprice NUMERIC,
  288. discount numeric,
  289. unit varchar(5),
  290. project_id int,
  291. reqdate date,
  292. ship numeric,
  293. serialnumber text,
  294. notes text
  295. ) WITH OIDS;
  296. --
  297. CREATE TABLE exchangerate (
  298. curr char(3),
  299. transdate date,
  300. buy numeric,
  301. sell numeric
  302. );
  303. --
  304. create table employee (
  305. id int default nextval('id'),
  306. login text,
  307. name varchar(64),
  308. address1 varchar(32),
  309. address2 varchar(32),
  310. city varchar(32),
  311. state varchar(32),
  312. zipcode varchar(10),
  313. country varchar(32),
  314. workphone varchar(20),
  315. homephone varchar(20),
  316. startdate date default current_date,
  317. enddate date,
  318. notes text,
  319. role varchar(20),
  320. sales bool default 'f',
  321. email text,
  322. ssn varchar(20),
  323. iban varchar(34),
  324. bic varchar(11),
  325. managerid int,
  326. employeenumber varchar(32),
  327. dob date
  328. );
  329. --
  330. create table shipto (
  331. trans_id int,
  332. shiptoname varchar(64),
  333. shiptoaddress1 varchar(32),
  334. shiptoaddress2 varchar(32),
  335. shiptocity varchar(32),
  336. shiptostate varchar(32),
  337. shiptozipcode varchar(10),
  338. shiptocountry varchar(32),
  339. shiptocontact varchar(64),
  340. shiptophone varchar(20),
  341. shiptofax varchar(20),
  342. shiptoemail text
  343. );
  344. --
  345. CREATE TABLE vendor (
  346. id int default nextval('id'),
  347. name varchar(64),
  348. address1 varchar(32),
  349. address2 varchar(32),
  350. city varchar(32),
  351. state varchar(32),
  352. zipcode varchar(10),
  353. country varchar(32),
  354. contact varchar(64),
  355. phone varchar(20),
  356. fax varchar(20),
  357. email text,
  358. notes text,
  359. terms int2 default 0,
  360. taxincluded bool default 'f',
  361. vendornumber varchar(32),
  362. cc text,
  363. bcc text,
  364. gifi_accno varchar(30),
  365. business_id int,
  366. taxnumber varchar(32),
  367. sic_code varchar(6),
  368. discount numeric,
  369. creditlimit numeric default 0,
  370. iban varchar(34),
  371. bic varchar(11),
  372. employee_id int,
  373. language_code varchar(6),
  374. pricegroup_id int,
  375. curr char(3),
  376. startdate date,
  377. enddate date
  378. );
  379. --
  380. CREATE TABLE project (
  381. id int default nextval('id'),
  382. projectnumber text,
  383. description text,
  384. startdate date,
  385. enddate date,
  386. parts_id int,
  387. production numeric default 0,
  388. completed numeric default 0,
  389. customer_id int
  390. );
  391. --
  392. CREATE TABLE partsgroup (
  393. id int default nextval('id'),
  394. partsgroup text
  395. );
  396. --
  397. CREATE TABLE status (
  398. trans_id int,
  399. formname text,
  400. printed bool default 'f',
  401. emailed bool default 'f',
  402. spoolfile text
  403. );
  404. --
  405. CREATE TABLE department (
  406. id int default nextval('id'),
  407. description text,
  408. role char(1) default 'P'
  409. );
  410. --
  411. -- department transaction table
  412. CREATE TABLE dpt_trans (
  413. trans_id int,
  414. department_id int
  415. );
  416. --
  417. -- business table
  418. CREATE TABLE business (
  419. id int default nextval('id'),
  420. description text,
  421. discount numeric
  422. );
  423. --
  424. -- SIC
  425. CREATE TABLE sic (
  426. code varchar(6),
  427. sictype char(1),
  428. description text
  429. );
  430. --
  431. CREATE TABLE warehouse (
  432. id int default nextval('id'),
  433. description text
  434. );
  435. --
  436. CREATE TABLE inventory (
  437. warehouse_id int,
  438. parts_id int,
  439. trans_id int,
  440. orderitems_id int,
  441. qty numeric,
  442. shippingdate date,
  443. employee_id int
  444. ) WITH OIDS;
  445. --
  446. CREATE TABLE yearend (
  447. trans_id int,
  448. transdate date
  449. );
  450. --
  451. CREATE TABLE partsvendor (
  452. vendor_id int,
  453. parts_id int,
  454. partnumber text,
  455. leadtime int2,
  456. lastcost NUMERIC,
  457. curr char(3)
  458. );
  459. --
  460. CREATE TABLE pricegroup (
  461. id int default nextval('id'),
  462. pricegroup text
  463. );
  464. --
  465. CREATE TABLE partscustomer (
  466. parts_id int,
  467. customer_id int,
  468. pricegroup_id int,
  469. pricebreak numeric,
  470. sellprice NUMERIC,
  471. validfrom date,
  472. validto date,
  473. curr char(3)
  474. );
  475. --
  476. CREATE TABLE language (
  477. code varchar(6),
  478. description text
  479. );
  480. --
  481. CREATE TABLE audittrail (
  482. trans_id int,
  483. tablename text,
  484. reference text,
  485. formname text,
  486. action text,
  487. transdate timestamp default current_timestamp,
  488. employee_id int
  489. );
  490. --
  491. CREATE TABLE translation (
  492. trans_id int,
  493. language_code varchar(6),
  494. description text
  495. );
  496. --
  497. CREATE TABLE recurring (
  498. id int,
  499. reference text,
  500. startdate date,
  501. nextdate date,
  502. enddate date,
  503. repeat int2,
  504. unit varchar(6),
  505. howmany int,
  506. payment bool default 'f'
  507. );
  508. --
  509. CREATE TABLE recurringemail (
  510. id int,
  511. formname text,
  512. format text,
  513. message text
  514. );
  515. --
  516. CREATE TABLE recurringprint (
  517. id int,
  518. formname text,
  519. format text,
  520. printer text
  521. );
  522. --
  523. CREATE TABLE jcitems (
  524. id int default nextval('jcitemsid'),
  525. project_id int,
  526. parts_id int,
  527. description text,
  528. qty numeric,
  529. allocated numeric,
  530. sellprice NUMERIC,
  531. fxsellprice NUMERIC,
  532. serialnumber text,
  533. checkedin timestamp with time zone,
  534. checkedout timestamp with time zone,
  535. employee_id int,
  536. notes text
  537. );
  538. -- Session tracking table
  539. CREATE SEQUENCE session_session_id_seq;
  540. CREATE TABLE session(
  541. session_id INTEGER PRIMARY KEY DEFAULT nextval('session_session_id_seq'),
  542. sl_login VARCHAR(50),
  543. token CHAR(32),
  544. last_used TIMESTAMP default now()
  545. );
  546. create table transaction_ledger (
  547. id int PRIMARY KEY,
  548. table_name text
  549. );
  550. insert into transaction_ledger (id, table_name) SELECT id, 'ap' FROM ap;
  551. CREATE RULE ap_id_track_i AS ON insert TO ap
  552. DO ALSO INSERT INTO transaction_ledger (id, table_name) VALUES (new.id, 'ap');
  553. CREATE RULE ap_id_track_u AS ON update TO ap
  554. DO ALSO UPDATE transaction_ledger SET id = new.id WHERE id = old.id;
  555. insert into transaction_ledger (id, table_name) SELECT id, 'ar' FROM ap;
  556. CREATE RULE ar_id_track_i AS ON insert TO ar
  557. DO ALSO INSERT INTO transaction_ledger (id, table_name) VALUES (new.id, 'ar');
  558. CREATE RULE ar_id_track_u AS ON update TO ar
  559. DO ALSO UPDATE transaction_ledger SET id = new.id WHERE id = old.id;
  560. INSERT INTO transaction_ledger (id, table_name) SELECT id, 'business' FROM business;
  561. CREATE RULE business_id_track_i AS ON insert TO business
  562. DO ALSO INSERT INTO transaction_ledger (id, table_name) VALUES (new.id, 'business');
  563. CREATE RULE business_id_track_u AS ON update TO business
  564. DO ALSO UPDATE transaction_ledger SET id = new.id WHERE id = old.id;
  565. INSERT INTO transaction_ledger (id, table_name) SELECT id, 'chart' FROM chart;
  566. CREATE RULE chart_id_track_i AS ON insert TO chart
  567. DO ALSO INSERT INTO transaction_ledger (id, table_name) VALUES (new.id, 'chart');
  568. CREATE RULE chart_id_track_u AS ON update TO chart
  569. DO ALSO UPDATE transaction_ledger SET id = new.id WHERE id = old.id;
  570. INSERT INTO transaction_ledger (id, table_name) SELECT id, 'customer' FROM customer;
  571. CREATE RULE customer_id_track_i AS ON insert TO customer
  572. DO ALSO INSERT INTO transaction_ledger (id, table_name) VALUES (new.id, 'customer');
  573. CREATE RULE customer_id_track_u AS ON update TO customer
  574. DO ALSO UPDATE transaction_ledger SET id = new.id WHERE id = old.id;
  575. INSERT INTO transaction_ledger (id, table_name) SELECT id, 'department' FROM department;
  576. CREATE RULE department_id_track_i AS ON insert TO department
  577. DO ALSO INSERT INTO transaction_ledger (id, table_name) VALUES (new.id, 'department');
  578. CREATE RULE department_id_track_u AS ON update TO department
  579. DO ALSO UPDATE transaction_ledger SET id = new.id WHERE id = old.id;
  580. INSERT INTO transaction_ledger (id, table_name) SELECT id, 'employee' FROM employee;
  581. CREATE RULE employee_id_track_i AS ON insert TO employee
  582. DO ALSO INSERT INTO transaction_ledger (id, table_name) VALUES (new.id, 'employee');
  583. CREATE RULE employee_id_track_u AS ON update TO employee
  584. DO ALSO UPDATE transaction_ledger SET id = new.id WHERE id = old.id;
  585. INSERT INTO transaction_ledger (id, table_name) SELECT id, 'gl' FROM gl;
  586. CREATE RULE gl_id_track_i AS ON insert TO gl
  587. DO ALSO INSERT INTO transaction_ledger (id, table_name) VALUES (new.id, 'gl');
  588. CREATE RULE gl_id_track_u AS ON update TO gl
  589. DO ALSO UPDATE transaction_ledger SET id = new.id WHERE id = old.id;
  590. INSERT INTO transaction_ledger (id, table_name) SELECT id, 'oe' FROM oe;
  591. CREATE RULE oe_id_track_i AS ON insert TO oe
  592. DO ALSO INSERT INTO transaction_ledger (id, table_name) VALUES (new.id, 'oe');
  593. CREATE RULE oe_id_track_u AS ON update TO oe
  594. DO ALSO UPDATE transaction_ledger SET id = new.id WHERE id = old.id;
  595. INSERT INTO transaction_ledger (id, table_name) SELECT id, 'parts' FROM parts;
  596. CREATE RULE parts_id_track_i AS ON insert TO parts
  597. DO ALSO INSERT INTO transaction_ledger (id, table_name) VALUES (new.id, 'parts');
  598. CREATE RULE parts_id_track_u AS ON update TO parts
  599. DO ALSO UPDATE transaction_ledger SET id = new.id WHERE id = old.id;
  600. INSERT INTO transaction_ledger (id, table_name) SELECT id, 'partsgroup' FROM partsgroup;
  601. CREATE RULE partsgroup_id_track_i AS ON insert TO partsgroup
  602. DO ALSO INSERT INTO transaction_ledger (id, table_name) VALUES (new.id, 'partsgroup');
  603. CREATE RULE partsgroup_id_track_u AS ON update TO partsgroup
  604. DO ALSO UPDATE transaction_ledger SET id = new.id WHERE id = old.id;
  605. INSERT INTO transaction_ledger (id, table_name) SELECT id, 'pricegroup' FROM pricegroup;
  606. CREATE RULE pricegroup_id_track_i AS ON insert TO pricegroup
  607. DO ALSO INSERT INTO transaction_ledger (id, table_name) VALUES (new.id, 'pricegroup');
  608. CREATE RULE pricegroup_id_track_u AS ON update TO pricegroup
  609. DO ALSO UPDATE transaction_ledger SET id = new.id WHERE id = old.id;
  610. INSERT INTO transaction_ledger (id, table_name) SELECT id, 'project' FROM project;
  611. CREATE RULE project_id_track_i AS ON insert TO project
  612. DO ALSO INSERT INTO transaction_ledger (id, table_name) VALUES (new.id, 'project');
  613. CREATE RULE project_id_track_u AS ON update TO project
  614. DO ALSO UPDATE transaction_ledger SET id = new.id WHERE id = old.id;
  615. INSERT INTO transaction_ledger (id, table_name) SELECT id, 'vendor' FROM vendor;
  616. CREATE RULE vendor_id_track_i AS ON insert TO vendor
  617. DO ALSO INSERT INTO transaction_ledger (id, table_name) VALUES (new.id, 'vendor');
  618. CREATE RULE employee_id_track_u AS ON update TO vendor
  619. DO ALSO UPDATE transaction_ledger SET id = new.id WHERE id = old.id;
  620. INSERT INTO transaction_ledger (id, table_name) SELECT id, 'warehouse' FROM warehouse;
  621. CREATE RULE warehouse_id_track_i AS ON insert TO warehouse
  622. DO ALSO INSERT INTO transaction_ledger (id, table_name) VALUES (new.id, 'employee');
  623. CREATE RULE warehouse_id_track_u AS ON update TO warehouse
  624. DO ALSO UPDATE transaction_ledger SET id = new.id WHERE id = old.id;
  625. CREATE TABLE custom_table_catalog (
  626. table_id SERIAL PRIMARY KEY,
  627. extends TEXT,
  628. table_name TEXT
  629. );
  630. CREATE TABLE custom_field_catalog (
  631. field_id SERIAL PRIMARY KEY,
  632. table_id INT REFERENCES custom_table_catalog,
  633. field_name TEXT
  634. );