summaryrefslogtreecommitdiff
path: root/sql/Pg-tables.sql
blob: 92dfbf26be5686d9be78c988c3ae8bf1d69b75b5 (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 transactions (
  15. id int PRIMARY KEY,
  16. table_name text
  17. );
  18. --
  19. CREATE TABLE makemodel (
  20. parts_id int PRIMARY KEY,
  21. make text,
  22. model text
  23. );
  24. --
  25. CREATE TABLE gl (
  26. id int DEFAULT nextval ( 'id' ) PRIMARY KEY,
  27. reference text,
  28. description text,
  29. transdate date DEFAULT current_date,
  30. employee_id int,
  31. notes text,
  32. department_id int default 0
  33. );
  34. --
  35. CREATE TABLE chart (
  36. id int DEFAULT nextval ( 'id' ) PRIMARY KEY,
  37. accno text NOT NULL,
  38. description text,
  39. charttype char(1) DEFAULT 'A',
  40. category char(1),
  41. link text,
  42. gifi_accno text,
  43. contra bool DEFAULT 'f'
  44. );
  45. --
  46. CREATE TABLE gifi (
  47. accno text PRIMARY KEY,
  48. description text
  49. );
  50. --
  51. CREATE TABLE defaults (
  52. setting_key text primary key,
  53. value text
  54. );
  55. /*
  56. inventory_accno_id int,
  57. income_accno_id int,
  58. expense_accno_id int,
  59. fxgain_accno_id int,
  60. fxloss_accno_id int,
  61. */
  62. \COPY defaults FROM stdin WITH DELIMITER |
  63. sinumber|1
  64. sonumber|1
  65. yearend|1
  66. businessnumber|1
  67. version|1.2.0
  68. closedto|\N
  69. revtrans|1
  70. ponumber|1
  71. sqnumber|1
  72. rfqnumber|1
  73. audittrail|0
  74. vinumber|1
  75. employeenumber|1
  76. partnumber|1
  77. customernumber|1
  78. vendornumber|1
  79. glnumber|1
  80. projectnumber|1
  81. \.
  82. -- */
  83. CREATE TABLE acc_trans (
  84. trans_id int,
  85. chart_id int NOT NULL REFERENCES chart (id),
  86. amount NUMERIC,
  87. transdate date DEFAULT current_date,
  88. source text,
  89. cleared bool DEFAULT 'f',
  90. fx_transaction bool DEFAULT 'f',
  91. project_id int,
  92. memo text,
  93. invoice_id int,
  94. entry_id SERIAL PRIMARY KEY
  95. );
  96. --
  97. CREATE TABLE invoice (
  98. id int DEFAULT nextval ( 'invoiceid' ) PRIMARY KEY,
  99. trans_id int,
  100. parts_id int,
  101. description text,
  102. qty integer,
  103. allocated integer,
  104. sellprice NUMERIC,
  105. fxsellprice NUMERIC,
  106. discount float4, -- jd: check into this
  107. assemblyitem bool DEFAULT 'f',
  108. unit varchar(5),
  109. project_id int,
  110. deliverydate date,
  111. serialnumber text,
  112. notes text
  113. );
  114. --
  115. CREATE TABLE customer (
  116. id int default nextval('id') PRIMARY KEY,
  117. name varchar(64),
  118. address1 varchar(32),
  119. address2 varchar(32),
  120. city varchar(32),
  121. state varchar(32),
  122. zipcode varchar(10),
  123. country varchar(32),
  124. contact varchar(64),
  125. phone varchar(20),
  126. fax varchar(20),
  127. email text,
  128. notes text,
  129. discount numeric,
  130. taxincluded bool default 'f',
  131. creditlimit NUMERIC default 0,
  132. terms int2 default 0,
  133. customernumber varchar(32),
  134. cc text,
  135. bcc text,
  136. business_id int,
  137. taxnumber varchar(32),
  138. sic_code varchar(6),
  139. iban varchar(34),
  140. bic varchar(11),
  141. employee_id int,
  142. language_code varchar(6),
  143. pricegroup_id int,
  144. curr char(3),
  145. startdate date,
  146. enddate date
  147. );
  148. --
  149. --
  150. CREATE TABLE parts (
  151. id int DEFAULT nextval ( 'id' ) PRIMARY KEY,
  152. partnumber text,
  153. description text,
  154. unit varchar(5),
  155. listprice NUMERIC,
  156. sellprice NUMERIC,
  157. lastcost NUMERIC,
  158. priceupdate date DEFAULT current_date,
  159. weight numeric,
  160. onhand numeric DEFAULT 0,
  161. notes text,
  162. makemodel bool DEFAULT 'f',
  163. assembly bool DEFAULT 'f',
  164. alternate bool DEFAULT 'f',
  165. rop float4, -- jd: what is this
  166. inventory_accno_id int,
  167. income_accno_id int,
  168. expense_accno_id int,
  169. bin text,
  170. obsolete bool DEFAULT 'f',
  171. bom bool DEFAULT 'f',
  172. image text,
  173. drawing text,
  174. microfiche text,
  175. partsgroup_id int,
  176. project_id int,
  177. avgcost NUMERIC
  178. );
  179. --
  180. CREATE TABLE assembly (
  181. id int,
  182. parts_id int,
  183. qty numeric,
  184. bom bool,
  185. adj bool,
  186. PRIMARY KEY (id, parts_id)
  187. );
  188. --
  189. CREATE TABLE ar (
  190. id int DEFAULT nextval ( 'id' ) PRIMARY KEY,
  191. invnumber text,
  192. transdate date DEFAULT current_date,
  193. customer_id int,
  194. taxincluded bool,
  195. amount NUMERIC,
  196. netamount NUMERIC,
  197. paid NUMERIC,
  198. datepaid date,
  199. duedate date,
  200. invoice bool DEFAULT 'f',
  201. shippingpoint text,
  202. terms int2 DEFAULT 0,
  203. notes text,
  204. curr char(3),
  205. ordnumber text,
  206. employee_id int,
  207. till varchar(20),
  208. quonumber text,
  209. intnotes text,
  210. department_id int default 0,
  211. shipvia text,
  212. language_code varchar(6),
  213. ponumber text
  214. );
  215. --
  216. CREATE TABLE ap (
  217. id int DEFAULT nextval ( 'id' ) PRIMARY KEY,
  218. invnumber text,
  219. transdate date DEFAULT current_date,
  220. vendor_id int,
  221. taxincluded bool DEFAULT 'f',
  222. amount NUMERIC,
  223. netamount NUMERIC,
  224. paid NUMERIC,
  225. datepaid date,
  226. duedate date,
  227. invoice bool DEFAULT 'f',
  228. ordnumber text,
  229. curr char(3),
  230. notes text,
  231. employee_id int,
  232. till varchar(20),
  233. quonumber text,
  234. intnotes text,
  235. department_id int DEFAULT 0,
  236. shipvia text,
  237. language_code varchar(6),
  238. ponumber text,
  239. shippingpoint text,
  240. terms int2 DEFAULT 0
  241. );
  242. --
  243. CREATE TABLE partstax (
  244. parts_id int,
  245. chart_id int,
  246. PRIMARY KEY (parts_id, chart_id)
  247. );
  248. --
  249. CREATE TABLE taxmodule (
  250. taxmodule_id serial PRIMARY KEY,
  251. taxmodulename text NOT NULL
  252. );
  253. --
  254. CREATE TABLE tax (
  255. chart_id int PRIMARY KEY,
  256. rate numeric,
  257. taxnumber text,
  258. validto date,
  259. pass integer DEFAULT 0 NOT NULL,
  260. taxmodule_id int DEFAULT 1 NOT NULL,
  261. FOREIGN KEY (chart_id) REFERENCES chart (id),
  262. FOREIGN KEY (taxmodule_id) REFERENCES taxmodule (taxmodule_id)
  263. );
  264. --
  265. CREATE TABLE customertax (
  266. customer_id int,
  267. chart_id int,
  268. PRIMARY KEY (customer_id, chart_id)
  269. );
  270. --
  271. CREATE TABLE vendortax (
  272. vendor_id int,
  273. chart_id int,
  274. PRIMARY KEY (vendor_id, chart_id)
  275. );
  276. --
  277. CREATE TABLE oe (
  278. id int default nextval('id') PRIMARY KEY,
  279. ordnumber text,
  280. transdate date default current_date,
  281. vendor_id int,
  282. customer_id int,
  283. amount NUMERIC,
  284. netamount NUMERIC,
  285. reqdate date,
  286. taxincluded bool,
  287. shippingpoint text,
  288. notes text,
  289. curr char(3),
  290. employee_id int,
  291. closed bool default 'f',
  292. quotation bool default 'f',
  293. quonumber text,
  294. intnotes text,
  295. department_id int default 0,
  296. shipvia text,
  297. language_code varchar(6),
  298. ponumber text,
  299. terms int2 DEFAULT 0
  300. );
  301. --
  302. CREATE TABLE orderitems (
  303. id int default nextval('orderitemsid') PRIMARY KEY,
  304. trans_id int,
  305. parts_id int,
  306. description text,
  307. qty numeric,
  308. sellprice NUMERIC,
  309. discount numeric,
  310. unit varchar(5),
  311. project_id int,
  312. reqdate date,
  313. ship numeric,
  314. serialnumber text,
  315. notes text
  316. );
  317. --
  318. CREATE TABLE exchangerate (
  319. curr char(3),
  320. transdate date,
  321. buy numeric,
  322. sell numeric,
  323. PRIMARY KEY (curr, transdate)
  324. );
  325. --
  326. create table employee (
  327. id int default nextval('id') PRIMARY KEY,
  328. login text,
  329. name varchar(64),
  330. address1 varchar(32),
  331. address2 varchar(32),
  332. city varchar(32),
  333. state varchar(32),
  334. zipcode varchar(10),
  335. country varchar(32),
  336. workphone varchar(20),
  337. homephone varchar(20),
  338. startdate date default current_date,
  339. enddate date,
  340. notes text,
  341. role varchar(20),
  342. sales bool default 'f',
  343. email text,
  344. ssn varchar(20),
  345. iban varchar(34),
  346. bic varchar(11),
  347. managerid int,
  348. employeenumber varchar(32),
  349. dob date
  350. );
  351. --
  352. create table shipto (
  353. trans_id int,
  354. shiptoname varchar(64),
  355. shiptoaddress1 varchar(32),
  356. shiptoaddress2 varchar(32),
  357. shiptocity varchar(32),
  358. shiptostate varchar(32),
  359. shiptozipcode varchar(10),
  360. shiptocountry varchar(32),
  361. shiptocontact varchar(64),
  362. shiptophone varchar(20),
  363. shiptofax varchar(20),
  364. shiptoemail text,
  365. entry_id SERIAL PRIMARY KEY
  366. );
  367. --
  368. CREATE TABLE vendor (
  369. id int default nextval('id') PRIMARY KEY,
  370. name varchar(64),
  371. address1 varchar(32),
  372. address2 varchar(32),
  373. city varchar(32),
  374. state varchar(32),
  375. zipcode varchar(10),
  376. country varchar(32),
  377. contact varchar(64),
  378. phone varchar(20),
  379. fax varchar(20),
  380. email text,
  381. notes text,
  382. terms int2 default 0,
  383. taxincluded bool default 'f',
  384. vendornumber varchar(32),
  385. cc text,
  386. bcc text,
  387. gifi_accno varchar(30),
  388. business_id int,
  389. taxnumber varchar(32),
  390. sic_code varchar(6),
  391. discount numeric,
  392. creditlimit numeric default 0,
  393. iban varchar(34),
  394. bic varchar(11),
  395. employee_id int,
  396. language_code varchar(6),
  397. pricegroup_id int,
  398. curr char(3),
  399. startdate date,
  400. enddate date
  401. );
  402. --
  403. CREATE TABLE project (
  404. id int default nextval('id') PRIMARY KEY,
  405. projectnumber text,
  406. description text,
  407. startdate date,
  408. enddate date,
  409. parts_id int,
  410. production numeric default 0,
  411. completed numeric default 0,
  412. customer_id int
  413. );
  414. --
  415. CREATE TABLE partsgroup (
  416. id int default nextval('id') PRIMARY KEY,
  417. partsgroup text
  418. );
  419. --
  420. CREATE TABLE status (
  421. trans_id int PRIMARY KEY,
  422. formname text,
  423. printed bool default 'f',
  424. emailed bool default 'f',
  425. spoolfile text
  426. );
  427. --
  428. CREATE TABLE department (
  429. id int default nextval('id') PRIMARY KEY,
  430. description text,
  431. role char(1) default 'P'
  432. );
  433. --
  434. -- department transaction table
  435. CREATE TABLE dpt_trans (
  436. trans_id int PRIMARY KEY,
  437. department_id int
  438. );
  439. --
  440. -- business table
  441. CREATE TABLE business (
  442. id int default nextval('id') PRIMARY KEY,
  443. description text,
  444. discount numeric
  445. );
  446. --
  447. -- SIC
  448. CREATE TABLE sic (
  449. code varchar(6) PRIMARY KEY,
  450. sictype char(1),
  451. description text
  452. );
  453. --
  454. CREATE TABLE warehouse (
  455. id int default nextval('id') PRIMARY KEY,
  456. description text
  457. );
  458. --
  459. CREATE TABLE inventory (
  460. warehouse_id int,
  461. parts_id int,
  462. trans_id int,
  463. orderitems_id int,
  464. qty numeric,
  465. shippingdate date,
  466. employee_id int,
  467. entry_id SERIAL PRIMARY KEY
  468. );
  469. --
  470. CREATE TABLE yearend (
  471. trans_id int PRIMARY KEY,
  472. transdate date
  473. );
  474. --
  475. CREATE TABLE partsvendor (
  476. vendor_id int,
  477. parts_id int,
  478. partnumber text,
  479. leadtime int2,
  480. lastcost NUMERIC,
  481. curr char(3),
  482. entry_id SERIAL PRIMARY KEY
  483. );
  484. --
  485. CREATE TABLE pricegroup (
  486. id int default nextval('id') PRIMARY KEY,
  487. pricegroup text
  488. );
  489. --
  490. CREATE TABLE partscustomer (
  491. parts_id int,
  492. customer_id int,
  493. pricegroup_id int,
  494. pricebreak numeric,
  495. sellprice NUMERIC,
  496. validfrom date,
  497. validto date,
  498. curr char(3),
  499. entry_id SERIAL PRIMARY KEY
  500. );
  501. --
  502. CREATE TABLE language (
  503. code varchar(6) PRIMARY KEY,
  504. description text
  505. );
  506. --
  507. CREATE TABLE audittrail (
  508. trans_id int,
  509. tablename text,
  510. reference text,
  511. formname text,
  512. action text,
  513. transdate timestamp default current_timestamp,
  514. employee_id int,
  515. entry_id BIGSERIAL PRIMARY KEY
  516. );
  517. --
  518. CREATE TABLE translation (
  519. trans_id int,
  520. language_code varchar(6),
  521. description text,
  522. PRIMARY KEY (trans_id, language_code)
  523. );
  524. --
  525. CREATE TABLE recurring (
  526. id int PRIMARY KEY,
  527. reference text,
  528. startdate date,
  529. nextdate date,
  530. enddate date,
  531. repeat int2,
  532. unit varchar(6),
  533. howmany int,
  534. payment bool default 'f'
  535. );
  536. --
  537. CREATE TABLE recurringemail (
  538. id int PRIMARY KEY,
  539. formname text,
  540. format text,
  541. message text
  542. );
  543. --
  544. CREATE TABLE recurringprint (
  545. id int PRIMARY KEY,
  546. formname text,
  547. format text,
  548. printer text
  549. );
  550. --
  551. CREATE TABLE jcitems (
  552. id int default nextval('jcitemsid') PRIMARY KEY,
  553. project_id int,
  554. parts_id int,
  555. description text,
  556. qty numeric,
  557. allocated numeric,
  558. sellprice NUMERIC,
  559. fxsellprice NUMERIC,
  560. serialnumber text,
  561. checkedin timestamp with time zone,
  562. checkedout timestamp with time zone,
  563. employee_id int,
  564. notes text
  565. );
  566. insert into transactions (id, table_name) SELECT id, 'ap' FROM ap;
  567. CREATE RULE ap_id_track_i AS ON insert TO ap
  568. DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'ap');
  569. CREATE RULE ap_id_track_u AS ON update TO ap
  570. DO UPDATE transactions SET id = new.id WHERE id = old.id;
  571. insert into transactions (id, table_name) SELECT id, 'ar' FROM ap;
  572. CREATE RULE ar_id_track_i AS ON insert TO ar
  573. DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'ar');
  574. CREATE RULE ar_id_track_u AS ON update TO ar
  575. DO UPDATE transactions SET id = new.id WHERE id = old.id;
  576. INSERT INTO transactions (id, table_name) SELECT id, 'business' FROM business;
  577. CREATE RULE business_id_track_i AS ON insert TO business
  578. DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'business');
  579. CREATE RULE business_id_track_u AS ON update TO business
  580. DO UPDATE transactions SET id = new.id WHERE id = old.id;
  581. INSERT INTO transactions (id, table_name) SELECT id, 'chart' FROM chart;
  582. CREATE RULE chart_id_track_i AS ON insert TO chart
  583. DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'chart');
  584. CREATE RULE chart_id_track_u AS ON update TO chart
  585. DO UPDATE transactions SET id = new.id WHERE id = old.id;
  586. INSERT INTO transactions (id, table_name) SELECT id, 'customer' FROM customer;
  587. CREATE RULE customer_id_track_i AS ON insert TO customer
  588. DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'customer');
  589. CREATE RULE customer_id_track_u AS ON update TO customer
  590. DO UPDATE transactions SET id = new.id WHERE id = old.id;
  591. INSERT INTO transactions (id, table_name) SELECT id, 'department' FROM department;
  592. CREATE RULE department_id_track_i AS ON insert TO department
  593. DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'department');
  594. CREATE RULE department_id_track_u AS ON update TO department
  595. DO UPDATE transactions SET id = new.id WHERE id = old.id;
  596. INSERT INTO transactions (id, table_name) SELECT id, 'employee' FROM employee;
  597. CREATE RULE employee_id_track_i AS ON insert TO employee
  598. DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'employee');
  599. CREATE RULE employee_id_track_u AS ON update TO employee
  600. DO UPDATE transactions SET id = new.id WHERE id = old.id;
  601. INSERT INTO transactions (id, table_name) SELECT id, 'gl' FROM gl;
  602. CREATE RULE gl_id_track_i AS ON insert TO gl
  603. DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'gl');
  604. CREATE RULE gl_id_track_u AS ON update TO gl
  605. DO UPDATE transactions SET id = new.id WHERE id = old.id;
  606. INSERT INTO transactions (id, table_name) SELECT id, 'oe' FROM oe;
  607. CREATE RULE oe_id_track_i AS ON insert TO oe
  608. DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'oe');
  609. CREATE RULE oe_id_track_u AS ON update TO oe
  610. DO UPDATE transactions SET id = new.id WHERE id = old.id;
  611. INSERT INTO transactions (id, table_name) SELECT id, 'parts' FROM parts;
  612. CREATE RULE parts_id_track_i AS ON insert TO parts
  613. DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'parts');
  614. CREATE RULE parts_id_track_u AS ON update TO parts
  615. DO UPDATE transactions SET id = new.id WHERE id = old.id;
  616. INSERT INTO transactions (id, table_name) SELECT id, 'partsgroup' FROM partsgroup;
  617. CREATE RULE partsgroup_id_track_i AS ON insert TO partsgroup
  618. DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'partsgroup');
  619. CREATE RULE partsgroup_id_track_u AS ON update TO partsgroup
  620. DO UPDATE transactions SET id = new.id WHERE id = old.id;
  621. INSERT INTO transactions (id, table_name) SELECT id, 'pricegroup' FROM pricegroup;
  622. CREATE RULE pricegroup_id_track_i AS ON insert TO pricegroup
  623. DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'pricegroup');
  624. CREATE RULE pricegroup_id_track_u AS ON update TO pricegroup
  625. DO UPDATE transactions SET id = new.id WHERE id = old.id;
  626. INSERT INTO transactions (id, table_name) SELECT id, 'project' FROM project;
  627. CREATE RULE project_id_track_i AS ON insert TO project
  628. DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'project');
  629. CREATE RULE project_id_track_u AS ON update TO project
  630. DO UPDATE transactions SET id = new.id WHERE id = old.id;
  631. INSERT INTO transactions (id, table_name) SELECT id, 'vendor' FROM vendor;
  632. CREATE RULE vendor_id_track_i AS ON insert TO vendor
  633. DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'vendor');
  634. CREATE RULE employee_id_track_u AS ON update TO vendor
  635. DO UPDATE transactions SET id = new.id WHERE id = old.id;
  636. INSERT INTO transactions (id, table_name) SELECT id, 'warehouse' FROM warehouse;
  637. CREATE RULE warehouse_id_track_i AS ON insert TO warehouse
  638. DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'employee');
  639. CREATE RULE warehouse_id_track_u AS ON update TO warehouse
  640. DO UPDATE transactions SET id = new.id WHERE id = old.id;
  641. CREATE TABLE custom_table_catalog (
  642. table_id SERIAL PRIMARY KEY,
  643. extends TEXT,
  644. table_name TEXT
  645. );
  646. CREATE TABLE custom_field_catalog (
  647. field_id SERIAL PRIMARY KEY,
  648. table_id INT REFERENCES custom_table_catalog,
  649. field_name TEXT
  650. );
  651. INSERT INTO taxmodule (
  652. taxmodule_id, taxmodulename
  653. ) VALUES (
  654. 1, 'Simple'
  655. );
  656. -- USERS stuff --
  657. CREATE TABLE users (id serial UNIQUE, username varchar(30) primary key);
  658. COMMENT ON TABLE users IS
  659. $$username is the actual primary key here because we don't want duplicate users$$;
  660. CREATE TABLE users_conf(id integer primary key references users(id) deferrable initially deferred,
  661. acs text,
  662. address text,
  663. businessnumber text,
  664. company text,
  665. countrycode text,
  666. currency text,
  667. dateformat text,
  668. dbconnect text,
  669. dbdriver text default 'Pg',
  670. dbhost text default 'localhost',
  671. dbname text,
  672. dboptions text,
  673. dbpasswd text,
  674. dbport text,
  675. dbuser text,
  676. email text,
  677. fax text,
  678. menuwidth text,
  679. name text,
  680. numberformat text,
  681. password varchar(32) check(length(password) = 32),
  682. print text,
  683. printer text,
  684. role text,
  685. sid text,
  686. signature text,
  687. stylesheet text,
  688. tel text,
  689. templates text,
  690. timeout numeric,
  691. vclimit numeric);
  692. COMMENT ON TABLE users_conf IS 'This is a completely dumb table that is a place holder to get usersconf into the database. Next major release will have a much more sane implementation';
  693. COMMENT ON COLUMN users_conf.id IS 'Yes primary key with a FOREIGN KEY to users(id) is correct';
  694. COMMENT ON COLUMN users_conf.password IS 'This means we have to get rid of the current password stuff and move to presumably md5()';
  695. -- Per conversation with ChriseH, if the admin user has a null password a couple of things happen.
  696. -- 1. It is implicit that this is an initial install
  697. -- 2. If the admin password does not match the ledger-smb.conf admin password, we throw a hijack alert
  698. -- The two below statements must be run from a single session
  699. INSERT INTO users(username) VALUES ('admin');
  700. INSERT INTO users_conf(id,password) VALUES (currval('users_id_seq'),NULL);
  701. CREATE FUNCTION create_user(text) RETURNS int4 AS $$
  702. INSERT INTO users(username) VALUES ('$1');
  703. SELECT currval('user_id_seq');
  704. $$ LANGUAGE 'SQL';
  705. COMMENT ON FUNCTION create_user(text) IS $$ Function to create user. Returns users.id if successful, else it is an error. $$;
  706. CREATE FUNCTION update_user(int4,text) RETURNS int4 AS $$
  707. UPDATE users SET username = '$2' WHERE id = $1;
  708. SELECT 1;
  709. $$ LANGUAGE 'SQL';
  710. COMMENT ON FUNCTION update_user(int4,text) IS $$ Takes int4 which is users.id and text which is username. Will update username based on id. Username is unique $$;
  711. -- Session tracking table
  712. CREATE TABLE session(
  713. session_id serial PRIMARY KEY,
  714. sl_login VARCHAR(50),
  715. token VARCHAR(32) CHECK(length(token) = 32),
  716. last_used TIMESTAMP default now(),
  717. users_id INTEGER -- NOT NULL references users(id)
  718. );