Android Question SQLite Create function / Another Question

[jahswant]

I'm porting one of my apps that uses MySQL to Android with SQLite database.Now i'm confronted to custom functions that are not supported by SQLite. Erel said here that you can create custom function by rebuilding the engine. How can i do it ? I have 3 main functions that i cannot simply implement in my sqlite queries listed below. How can i achieve this ?

1.

BEGIN
DECLARE n int;
    SELECT (DATEDIFF(E, S)*(3600*24))+(TIME_TO_SEC(E) - TIME_TO_SEC(S)) INTO n;
RETURN n;
END

2.

BEGIN
DECLARE n int;
if(S<=S0 AND S0<=E0 AND E0<=E) THEN
    SELECT (DATEDIFF(E0, S0)*(3600*24))+(TIME_TO_SEC(E0) - TIME_TO_SEC(S0)) INTO n;
elseif(S0<=S AND S<=E AND E<=E0) THEN
    SELECT (DATEDIFF(E, S)*(3600*24))+(TIME_TO_SEC(E) - TIME_TO_SEC(S)) INTO n;
elseif(S<=S0 AND S0<=E AND E<=E0) THEN
    SELECT (DATEDIFF(E, S0)*(3600*24))+(TIME_TO_SEC(E) - TIME_TO_SEC(S0)) INTO n;
elseif(S0<=S AND S<=E0 AND E0<=E) THEN
    SELECT (DATEDIFF(E0, S)*(3600*24))+(TIME_TO_SEC(E0) - TIME_TO_SEC(S)) INTO n;
else SELECT 0 INTO n;
END IF;
RETURN n;
END

3.

BEGIN
DECLARE n int;
DECLARE n2 int;
DECLARE n3 int;
DECLARE res int;
if(S<=S0 AND S0<=E0 AND E0<=E) THEN    
SELECT (DATEDIFF(E0, S0)*(3600*24))+(TIME_TO_SEC(E0) - TIME_TO_SEC(S0)) INTO n;
elseif(S0<=S AND S<=E AND E<=E0) THEN    
SELECT (DATEDIFF(E, S)*(3600*24))+(TIME_TO_SEC(E) - TIME_TO_SEC(S)) INTO n;
elseif(S<=S0 AND S0<=E AND E<=E0) THEN   
SELECT (DATEDIFF(E, S0)*(3600*24))+(TIME_TO_SEC(E) - TIME_TO_SEC(S0)) INTO n;
elseif(S0<=S AND S<=E0 AND E0<=E) THEN   
SELECT (DATEDIFF(E0, S)*(3600*24))+(TIME_TO_SEC(E0) - TIME_TO_SEC(S)) INTO n;
else SELECT 0 INTO n;
END IF;
SELECT n+(tol*60) INTO n2;
SELECT (DATEDIFF(E, S)*(3600*24))+(TIME_TO_SEC(E) - TIME_TO_SEC(S)) INTO n3;
IF(n2>=n3) THEN    SELECT n3 INTO res;
ELSE SELECT n2 INTO res;
END IF;
RETURN res;
END

These Ones...

 

[Erel]

I don't think that it is possible without building a custom ROM.

 

[jahswant]

I don't think that it is possible without building a custom ROM.

I'm revising seems there is even no way to pre-apply or post-apply the functions so i'm stucked here...

 

[Erel]

How large is the database? You can easily create such methods in the B4A code.

 

[jahswant]

How large is the database? You can easily create such methods in the B4A code.

Please advise me a scenario. This is a sample query.

SELECT ROUND(100*(duree_ss(s.debut, s.fin, p.debut, p.fin)/diff_ss(s.debut, s.fin))) PCT FROM pointage p, seance s WHERE p.seance_idseance=s.idseance AND p.id_pointage='"& ID & "'

. I have a transactional database with a 200 records every day. <> 100MB.
Don't seem to understand.

 

[Erel]

Such code should be avoided: p.id_pointage='"& ID & "'
It should be: p.id = ?

Get all the records and make the calculation in the B4A code.

 

[jahswant]

Get all the records and make the calculation in the B4A code

The problem is that i get many records and this time i need to choose only one and i don't know what's the best...