phpDivingLog 3.0 Development

3rd party extensions (phpDivingLog, dive computer downloader,...)
lloyd_borrett
Posts: 228
Joined: Sun Jun 11, 2006 09:24
Location: Frankston South, Victoria, Australia
Contact:

Re: phpDivingLog 3.0 Development

Post by lloyd_borrett »

G'day,

I'm not very good with these MySQL queries. Another problem. I'd like the basic details of all dives, with a count for the ones that also have pictures.

I have...

SELECT l.Number, l.Divedate, l.Divetime, l.Depth, l.Place, l.City, l.Profile, COUNT(*)AS Photos
FROM Logbook AS l, Pictures AS p
WHERE l.ID = p.LogID
GROUPBY l.ID
ORDERBY l.Number DESC

But this only returns the dives that have pictures. I'd like to see all dives returned with a count of 0 for those with no pictures.

Is this possible?

Best Regards, Lloyd Borrett.
rene
Posts: 65
Joined: Sun Sep 12, 2010 19:07
Location: France

Re: phpDivingLog 3.0 Development

Post by rene »

Hello,

yes it's possible:

SELECT l.Number, l.Divedate, l.Divetime, l.Depth, l.Place, l.City, l.Profile, COUNT(p.Id) AS Photos
FROM Logbook AS l LEFT JOIN Pictures AS p ON l.ID = p.LogID
GROUP BY l.ID
ORDER BY l.Number DESC

I assume that Pictures table has a primary key ID, otherwise use a secondary key, the image url for example.

Best regards,
rené
lloyd_borrett
Posts: 228
Joined: Sun Jun 11, 2006 09:24
Location: Frankston South, Victoria, Australia
Contact:

Re: phpDivingLog 3.0 Development

Post by lloyd_borrett »

Thanks Rene,

That works, but it's very slow. Took 16 seconds for a logbook with just 350 odd dives and 1500 odd pictures.

Might need another way.

Best Regards, Lloyd Borrett.
rene
Posts: 65
Joined: Sun Sep 12, 2010 19:07
Location: France

Re: phpDivingLog 3.0 Development

Post by rene »

Uh ... I'll take a look. Is the source code always at https://github.com/Infern1/phpDivinglog ?
lloyd_borrett
Posts: 228
Joined: Sun Jun 11, 2006 09:24
Location: Frankston South, Victoria, Australia
Contact:

Re: phpDivingLog 3.0 Development

Post by lloyd_borrett »

Yes, but that code is out of date as Rob's been on holidays for a few weeks. My latest changes are at https://github.com/lloyd-borrett/phpDivinglog

Best Regards, Lloyd Borrett.
rene
Posts: 65
Joined: Sun Sep 12, 2010 19:07
Location: France

Re: phpDivingLog 3.0 Development

Post by rene »

Ok.

I have tested with sqlite3 (I don't have mysql installed or an access to a server) on my 4 year old PC: CPU Time: user 0.296875 sys 1.000000

with the following query:

Code: Select all

select DL_LogBook.Number, DL_LogBook.Divedate, DL_LogBook.Divetime, DL_LogBook.Depth, DL_LogBook.Place, DL_LogBook.City, DL_LogBook.Profile, COUNT(DL_Pictures.Id) AS Photos
from DL_LogBook LEFT JOIN DL_Pictures ON DL_LogBook.ID = DL_Pictures.LogID
GROUP BY DL_LogBook.ID
ORDER BY DL_LogBook.Number DESC;
3392 rows in DL_LogBook
15234 rows in DL_Pictures (1452 dives have on average 10 pictures attached)
(No, I don't have 3000+ dives, I duplicated my logbook several times. The pictures also).

If I exclude the Profile (at least half dives have a profile) from the request, it takes about 20ms: CPU Time: user 0.125000 sys 0.093750.


So I think the problem is not in the SQL query, but what PHP makes with the query or its result (for example, memory allocation since Profile is a large column). Or MySql is very very slow...


PS: I have created the sqlite database from a MySql export and changing some things :
modification of the syntax of auto_increment / primary key
int(11) -> integer
enum('True','False') -> boolean
removal of « type=myisam »
that's all.
divinglog
Site Admin
Posts: 5768
Joined: Sat Feb 08, 2003 21:02
Location: Coburg
Contact:

Re: phpDivingLog 3.0 Development

Post by divinglog »

For the large profile column, there is a nice workaround if you just want to know if there is a profile available (e.g. to display a different icon for the dive list). I had the same problem in my Windows Phone app. Instead of retrieving the whole Profile column with all the data in it, call it that way:

Code: Select all

select DL_LogBook.Number, DL_LogBook.Divedate, DL_LogBook.Divetime, DL_LogBook.Depth, DL_LogBook.Place, DL_LogBook.City, DL_LogBook.Profile<>'' AS Profile, COUNT(DL_Pictures.Id) AS Photos
from DL_LogBook LEFT JOIN DL_Pictures ON DL_LogBook.ID = DL_Pictures.LogID
GROUP BY DL_LogBook.ID
ORDER BY DL_LogBook.Number DESC;
That will return just a "1" if there is a profile and you can easily convert that to a boolean to display a icon. It was a huge performance improvement on the phone. This works for SQLite and probably also for MySQL. Make sure to see if this works also when Profile is NULL instead of an empty string.
lloyd_borrett
Posts: 228
Joined: Sun Jun 11, 2006 09:24
Location: Frankston South, Victoria, Australia
Contact:

Re: phpDivingLog 3.0 Development

Post by lloyd_borrett »

Thanks,

These queries work, but are way too slow on my development setup. Query blows out from taking 0.0116 sec to always around 15 sec. So I guess I'll just have to forgo the nice to have but not essential extra details such a query could have provided.

Best Regards, Lloyd Borrett.
rene
Posts: 65
Joined: Sun Sep 12, 2010 19:07
Location: France

Re: phpDivingLog 3.0 Development

Post by rene »

lloyd_borrett wrote:Thanks,

These queries work, but are way too slow on my development setup. Query blows out from taking 0.0116 sec to always around 15 sec. So I guess I'll just have to forgo the nice to have but not essential extra details such a query could have provided.
Hello Lloyd,

Did you try to create a non unique index on the LogId column of the Pictures table ? I don't remember exactly how mysql works (Oracle database allows this kind of indexing).

I guess your development setup is your own computer, not a real server ? Sandbox servers of ISP are really slow and overloaded...

Have a G'day :)
lloyd_borrett
Posts: 228
Joined: Sun Jun 11, 2006 09:24
Location: Frankston South, Victoria, Australia
Contact:

Re: phpDivingLog 3.0 Development

Post by lloyd_borrett »

G'day Rene,

My development box for phpDivingLog is the humble Acer netbook computer I use for diving related stuff. (It's my cheapest dive computer.)

I'm well aware that shared ISP hosting has performance issues. Because most people will probably host their phpDivingLog implementation on just such a server, I'm conscious of keeping the queries reasonably fast so as to maintain the user experience.

Best Regards, Lloyd Borrett.
lloyd_borrett
Posts: 228
Joined: Sun Jun 11, 2006 09:24
Location: Frankston South, Victoria, Australia
Contact:

Re: phpDivingLog 3.0 Development

Post by lloyd_borrett »

G'day Sven,

Thought I'd have a go at adding support for the user defined fields to phpDivingLog. So I added a field called 'Extra Stuff 1' with a value of 'Test stuff' to a logbook entry. Then did a MySQL Export.

On importing the data using phpMyAdmin I got the following error:

Code: Select all

SQL query:

 CREATETABLE Userdefined(

ID int( 11)AUTO_INCREMENT ,
LogID int( 11)default NULL ,
Extra Stuff1varchar( 255) default'',
Field2 varchar( 255) default'',
Field3 varchar( 255) default'',
Field4 varchar( 255) default'',
Field5 varchar( 255) default'',
Field6 varchar( 255) default'',
Field7 varchar( 255) default'',
Field8 varchar( 255) default'',
Field9 varchar( 255) default'',
Field10 varchar( 255) default'',
UUIDchar( 36) default'',
Updated datedefault NULL ,
 PRIMARYKEY ( ID ) 
) ENGINE=MYISAMDEFAULT CHARSET= UTF8;
 
MySQL said: 
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Stuff 1 varchar(255) default '', Field2 varchar(255) default '', Field3 varchar(' at line 1
I suspect this is because I've entered 'Extra Stuff 1' thinking it's a column description rather than a database column name.

But if that's so, just how do we define a field description/title?

Just what are the rules we need to keep in mind when using user defined fields.

Best Regards, Lloyd Borrett.
AndyDragon
Posts: 110
Joined: Tue Nov 14, 2006 08:38
Location: Penetanguishene, Ontario
Contact:

Re: phpDivingLog 3.0 Development

Post by AndyDragon »

MySQL allows spaces in field names, but they need to be enclosed in back ticks. This following works on my server (5.1):

CREATE TABLE Userdefined(
ID int( 11) AUTO_INCREMENT ,
LogID int( 11) default NULL ,
`Extra Stuff 1` varchar( 255) default '' ,
Field2 varchar( 255) default '' ,
Field3 varchar( 255) default '' ,
Field4 varchar( 255) default '' ,
Field5 varchar( 255) default '' ,
Field6 varchar( 255) default '' ,
Field7 varchar( 255) default '' ,
Field8 varchar( 255) default '' ,
Field9 varchar( 255) default '' ,
Field10 varchar( 255) default '' ,
UUID char( 36) default '' ,
Updated date default NULL ,
PRIMARY KEY ( ID )
) ENGINE=MYISAM DEFAULT CHARSET= UTF8;

SELECT queries must also use back ticks:

SELECT u.`Extra Stuff 1` FROM Userdefined u;
Andrew Forget
PADI IDC Staff Instructor 212158
divinglog
Site Admin
Posts: 5768
Joined: Sat Feb 08, 2003 21:02
Location: Coburg
Contact:

Re: phpDivingLog 3.0 Development

Post by divinglog »

Yes, I have to modify the MySQL export in order to allow spaces in the userdefined field names. In the meantime, try to avoid blank spaces in the names.
divinglog
Site Admin
Posts: 5768
Joined: Sat Feb 08, 2003 21:02
Location: Coburg
Contact:

Re: phpDivingLog 3.0 Development

Post by divinglog »

I just saw that this is already implemented. Just go into the export settings and enable "NamesInBackquotes". Then you can have spaces in the column names.
lloyd_borrett
Posts: 228
Joined: Sun Jun 11, 2006 09:24
Location: Frankston South, Victoria, Australia
Contact:

Re: phpDivingLog 3.0 Development

Post by lloyd_borrett »

That's great Sven.

You can see support for user-defined fields implemented at http://www.borrett.id.au/divelog3/index.php?id=353

By the way, the other day is was looking at the Tree Browser part of Diving Log (used as an alternative way to see Locations, Buddies, Divetypes, Date, Equipment and Trips/Vacations) and thinking that feature would be a great addition to phpDivingLog. Is this something you wrote from scratch, or is it based on a class that maybe has a PHP/MySQL equivalent somewhere?

Best Regards, Lloyd Borrett.
Post Reply