Re: Proposal for LedgerSMB 1.5: Move to Pg 9.2 and use JSON for extended attributes

[Andres Basile <..hidden..>]

Hello Chris,

My personal opinion would be to move to 9.2 and using json as protocol between server and client, but, my understanding is that it will need a new kind of client, able to request, understand json and fill the html, also, it would need a new kind of handler. Instead of work on that, I would focus efforts on database and store procedures, my personal experience contributing to this project was to reach the database layer and to don't know what to do. What I mean is, if the database had a full set of procedures working as abstraction layer it would be easy for development to add new extensions. When I was writing the wxpos, I started to write a new approach with procedures, the idea was to give all the logic to the database and use the client/handler just like an IO interface. Let me try to explain this with an example:

wxpos_invoice_sell_new (integer): This function gets the customer id and set a couple of temporary tables (like taxes, list of items, etc) to follow the operation.

wxpos_invoice_sell_add (integer, text): This one gets quantity and barcode, add the item to temporary tables, calculate taxes, etc.

wxpos_invoice_sell_del (integer): WIP - This will delete the item id and reverse all previous calculations.

wxpos_invoice_sell_post (): TODO - Once we are happy to go, this function will post all temporary tables calculations with a store procedure. warranting to save data or not.

I'm not an expert to say it this is better or not, but I found this paradigm to be easy to extend without affect the core logic, I'm attaching a txt so you can take a look to them. I hope you find these comments useful, I'm a little far now a days, but I would like to contribute ever that I can.

Andres.- 

On Mon, Sep 17, 2012 at 6:44 AM, Chris Travers <..hidden..> wrote:

Hi all;

I have been thinking about the way we handle customizations in old code vs new code where new fields need to be added.  Currently adding these for old code is pretty easy, but adding them for new code is a little more problematic.  We have two options going forward:

1)  We can move to Pg 9.1 as the required version and add a dependency on hstore.  An hstore field can be attached to every major object type, retrieved and submitted with the stored procs, etc.  We could actually also do the same in 8.4 using a text field to store JSON but the db would be unable to validate it.

2)  We can move to Pg 9.2 as a required version and use JSON in the same way.  We wouldn't need to depend, out of the box, on plv8js or any other extensions but customizations that might use this would be possible.

3)  We could continue with 8.4 and store as XML.  This would give us validation and xpath functions.  There is more XML functionality in PostgreSQL (any version) than there is JSON functionality. 

My preference currently is to move to JSON since it is a little easier to reliably convert to/from Perl data structures, and would be a little easier in other languages, probably, too.  What does everyone else think?

Best Wishes,

Chris Travers

------------------------------------------------------------------------------
Live Security Virtual Conference
Exclusive live event will cover all the ways today's security and
threat landscape has changed and how IT managers can respond. Discussions
will include endpoint security, mobile security and the latest in malware
threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
_______________________________________________
Ledger-smb-devel mailing list
..hidden..
https://lists.sourceforge.net/lists/listinfo/ledger-smb-devel

--
-- Name: wxpos_get_taxes_by_pair(integer, text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION wxpos_get_taxes_by_pair(customer integer, part text) RETURNS SETOF record
    LANGUAGE plpgsql
    AS $$
-- select * FROM wxpos_get_taxes_by_pair(ENTITY_ID, 'PART_NUMBER') AS (acc_id int, rate numeric, description text, part_id int, eca_id int, price numeric)
DECLARE
 taxes RECORD;
BEGIN
 FOR taxes IN SELECT a.id AS acc_id, t.rate, a.description, p.id AS part_id, eca.id AS eca_id, p.sellprice
 FROM tax t
 JOIN partstax pt ON (t.chart_id = pt.chart_id)
 JOIN parts p ON (p.id = pt.parts_id)
 JOIN customertax ct ON (t.chart_id = ct.chart_id)
 JOIN account a ON (a.id = ct.chart_id)
 JOIN entity_credit_account eca ON (eca.id = ct.customer_id)
 WHERE (eca.entity_id = customer)
 AND (p.partnumber = part) LOOP
 RETURN NEXT taxes;
 END LOOP;
END;
$$;

--
-- Name: wxpos_invoice_sell_add(integer, text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION wxpos_invoice_sell_add(qty integer, part_number text) RETURNS record
    LANGUAGE plpgsql
    AS $_$-- select WxPos_invoice_sell_add($quantity, $part_number)
DECLARE
-- Session
 v_token session.token%TYPE;

-- Customer Taxes
 t_ct RECORD;

-- Part
 t_parts RECORD;

-- Part Taxes
 t_pt RECORD;

-- Total
 t_total RECORD;

-- Variables used to calculate
 v_a NUMERIC;
 v_b NUMERIC;
 v_c NUMERIC;

BEGIN
-- Getting session token
 SELECT s.token INTO v_token
  FROM session s
  JOIN users u ON (s.users_id = u.id)
  WHERE u.username = session_user;

-- Getting part by partnumber
  INSERT INTO INVOICE_PARTS (session_token, partid, partnumber, description, quantity, sellprice, stotal, total, inventory_accno_id, income_accno_id, expense_accno_id)
   SELECT v_token, id, partnumber, description, qty, sellprice, qty * sellprice, qty * sellprice, inventory_accno_id, income_accno_id, expense_accno_id
   FROM parts
   WHERE partnumber = part_number;
 IF NOT FOUND
  THEN
   RAISE EXCEPTION 'part % not found', part_number;
 ELSE
   SELECT * INTO t_parts FROM INVOICE_PARTS WHERE id = currval('INVOICE_PARTS_id_seq');
 END IF;

-- Getting each tax by part_id
 FOR t_pt IN SELECT chart_id
  FROM partstax pt
  WHERE (pt.parts_id = t_parts.partid) LOOP
   t_ct := NULL;

--- Looking for tax in customer list
   SELECT COALESCE(rate, 0) AS rate, COALESCE(ammount, 0) AS ammount, chart_id INTO t_ct FROM INVOICE_CUSTOMER_TAXES WHERE chart_id = t_pt.chart_id;
   IF FOUND
   THEN

---- Calculating taxes
    v_a := NULL;
    v_a := t_ct.rate * t_parts.stotal;
    INSERT INTO INVOICE_PARTS_TAXES (session_token, IP_id, chart_id, rate, ammount) VALUES (v_token, t_parts.id, t_ct.chart_id, t_ct.rate, v_a);

---- Updating Customer Taxes (summary)
     v_b := NULL;
     SELECT COALESCE(ammount, 0) INTO v_b FROM INVOICE_CUSTOMER_TAXES WHERE chart_id = t_ct.chart_id;
     UPDATE INVOICE_CUSTOMER_TAXES SET ammount = (v_a + v_b) WHERE chart_id = t_ct.chart_id;
   END IF;
 END LOOP;

-- Updating Parts Income
 v_a := NULL;
 SELECT COALESCE(stotal, 0) INTO v_a FROM INVOICE_PARTS WHERE id = t_parts.id;
 INSERT INTO INVOICE_PARTS_INCOME (session_token, IP_id, chart_id, ammount) VALUES (v_token, t_parts.id, t_parts.income_accno_id, v_a);

-- Calculating Total
 v_b := NULL;
 SELECT COALESCE(sum(ammount), 0) INTO v_b FROM INVOICE_PARTS_TAXES WHERE IP_id = t_parts.id;
 v_c := NULL;
 v_c := v_a + v_b;

-- Updating Parts Total
 UPDATE INVOICE_PARTS SET total = v_c WHERE id = t_parts.id;

-- Updating Total
 v_a := NULL;
 v_b := NULL;
 v_c := NULL;
 SELECT COALESCE(sum(ammount), 0) INTO v_a FROM INVOICE_PARTS_INCOME;
 SELECT COALESCE(sum(ammount), 0) INTO v_b FROM INVOICE_CUSTOMER_TAXES;
 v_c := v_a + v_b;
 UPDATE INVOICE_TOTAL SET net = v_a, tax = v_b, total = v_c WHERE session_token = v_token;

 RETURN t_parts;
END;$_$;


--
-- Name: FUNCTION wxpos_invoice_sell_add(qty integer, part_number text); Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON FUNCTION wxpos_invoice_sell_add(qty integer, part_number text) IS 'This function gets quantity (how many units) and product (barcode). If the product doesn''t exists it returns an exception. Else, it gets customer taxes from temporal table and calculates them where they are present at both, product and customer.';


--
-- Name: wxpos_invoice_sell_del(integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION wxpos_invoice_sell_del(integer) RETURNS integer
    LANGUAGE plpgsql
    AS $$DECLARE
BEGIN
END;$$;

--
-- Name: FUNCTION wxpos_invoice_sell_del(integer); Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON FUNCTION wxpos_invoice_sell_del(integer) IS 'TODO - This function gets a reference to the row that will be eliminated on the temporary tables. All the prevous calculation have to be reversed.';


--
-- Name: wxpos_invoice_sell_new(integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION wxpos_invoice_sell_new(customer integer) RETURNS character varying
    LANGUAGE plpgsql
    AS $$-- select WxPos_new_invoice_sell(customer)
DECLARE
 v_token session.token%TYPE;
 v_ammount NUMERIC;
BEGIN
-- Get session token.
 SELECT s.token INTO v_token
  FROM session s
  JOIN users u ON (s.users_id = u.id)
  WHERE u.username = session_user;

-- Get customer information:
  CREATE TEMP TABLE INVOICE_CUSTOMER AS SELECT v_token AS token, name, discount, discount_account_id, business_id, pricegroup_id, ar_ap_account_id, cash_account_id, bank_account, taxform_id
  FROM entity e
  JOIN entity_credit_account eca ON (e.id = eca.entity_id)
  AND eca.id = customer;
--  ALTER TABLE INVOICE_CUSTOMER ADD PRIMARY KEY (token);

-- Get customer taxes:
-- Customer taxes should work as summary for sum(parts_taxes where account =)
  CREATE TEMP TABLE INVOICE_CUSTOMER_TAXES AS SELECT v_token AS token, ct.chart_id, rate, v_ammount AS ammount
   FROM customertax ct
   JOIN tax t ON (ct.chart_id = t.chart_id)
   WHERE customer_id = customer;

-- Each row has its own id to be related.
 CREATE TEMP TABLE INVOICE_PARTS (session_token character varying(32), id SERIAL, partid INT, partnumber TEXT, description TEXT, quantity INT, sellprice NUMERIC, stotal NUMERIC, total NUMERIC, inventory_accno_id INT, income_accno_id INT, expense_accno_id INT);
 CREATE TEMP TABLE INVOICE_PARTS_TAXES (session_token character varying(32), IP_id INT, chart_id INT, rate NUMERIC, ammount NUMERIC);
 CREATE TEMP TABLE INVOICE_PARTS_INCOME (session_token character varying(32), IP_id INT, chart_id INT, ammount NUMERIC);

-- Total should work as summary for sum(parts, parts_taxes, parts_income)
 CREATE TEMP TABLE INVOICE_TOTAL (session_token character varying(32), net NUMERIC, tax NUMERIC, total NUMERIC);
 CREATE TEMP TABLE INVOICE_TOTAL_PAID (session_token character varying(32), net NUMERIC, tax NUMERIC, total NUMERIC);
 INSERT INTO INVOICE_TOTAL (session_token, net, tax, total) VALUES (v_token, 0, 0, 0);

 RETURN v_token;
END;$$;


--
-- Name: FUNCTION wxpos_invoice_sell_new(customer integer); Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON FUNCTION wxpos_invoice_sell_new(customer integer) IS 'This function gets a customer entity_id and start a new invoice on memory for that session.';


--
-- Name: wxpos_invoice_sell_post(); Type: FUNCTION; Schema: public; Owner: ledgersmb
--

CREATE FUNCTION wxpos_invoice_sell_post() RETURNS integer
    LANGUAGE plpgsql
    AS $$-- select WxPos_invoice_sell_post(customer)
DECLARE
 v_token session.token%TYPE;
 v_id INT;
BEGIN
-- Get session token.
 SELECT s.token INTO v_token
  FROM session s
  JOIN users u ON (s.users_id = u.id)
  WHERE u.username = session_user;


 RETURN v_id;
END;$$;

--
-- Name: FUNCTION wxpos_invoice_sell_post(); Type: COMMENT; Schema: public; Owner: ledgersmb
--

COMMENT ON FUNCTION wxpos_invoice_sell_post() IS 'TODO - This function will execute a store procedure to post whole temporal tables on database.';