[Bitpim-devel] database status

[Roger Binns]

The database code is now very well advanced and you can try it out.
Unfortunately I found many issues with pysqlite 2 (which wraps sqlite 3)
but it is an alpha release. The developer has promised a new build
with many things fixed around this weekend.

To get an understanding of how it works, do the following:

- install pysqlite 2
- run database.py

This will create a database using examples/phonebook-index.idx
as input, as well as do various manipulations to ensure the
database code works. You will also see how many SQL statements
are executed. SQLite is fast and it is no big deal. The database
is produced in the file testdb

- run bitpim with the developer console turned on
- opendb("testdb")

This will open the test database

- tables()

This will show all tables in the database

- rows("phonebook")

This shows the rows within the named table

- sql("your sql command here")

Executes raw sql and prints the results (if any)

Still todo on my list are timestamps, getting history for an entry,
generating undo information, and some sort of scavenge operation
(remove old values).

Roger

[d***@netzero.com]

I tried it out and it seemed to work pretty well. I saved and restored my own phonebook (using extractbpserials) and found the 2 dicts are equivalent. Without using extractbpserials, the original dict has integer keys and the restored dict has string keys so the comparison failed. Is the plan to use extractbpserials for saving & restoring phonebook dicts? What about the other dicts such as calendar, ringtone 'references', etc.? Do you have, or plan for, any wrapper that would return the result of a query as a list/tuple of lists/tuples of field values?

-Joe Pham



________________________________________________________________
NetZero Gift Certificates
Give the gift of Internet access this holiday season.
http://www.netzero.com/give

[Roger Binns]

Post by d***@netzero.com
Is the plan to use extractbpserials for saving & restoring phonebook dicts?

Initially. The database code needs a unique id for each record, and doesn't
know or care about it being several fields in. The main display code as
well as the merge etc works well using integer keys. (We can't use lists
since items get added and removed and it would require going back and
fixing all the references. Even the column sorting keeps references to
the keys).

Post by d***@netzero.com
What about the other dicts such as calendar, ringtone 'references', etc.?

They will all require that each record has a unique id in the same way.
It makes most sense to use the same 'serials' key in the same way.

Post by d***@netzero.com
Do you have, or plan for, any wrapper that would return the result of a
query as a list/tuple of lists/tuples of field values?

The current plan is that the database code will be indistinguishable
from the current save to disk code. The complexity of the database
will be hidden. There hasn't been any need for what you describe so
far, so no code has been written. If it is needed, it will be written.

At the moment I am writing my own wrapper for SQLite 3. Unfortunately
the pysqlite one tries too hard to be compatible with the Python DBAPI
and also tries to second guess what you want. pysqlite tries to do its
own transaction management, tries to make it look like SQLite supports
types that it doesn't, and screws up things like phonenumbers which it
often converts to integers and booleans which it converts to strings.
My wrapper doesn't do silly stuff like that, and should perform quite
a bit better. It isn't too far from being usable, which means the
database code can finally pass my test suite.

Roger

[d***@netzero.com]

Post by Roger Binns
The database code needs a unique id for each record, and doesn't
know or care about it being several fields in.

Can you save and restore the dict keys if you're using BP IDs as the record keys? I guess I'm considering the ideal goal of using your existing dict, calling populatefs and getfromfs, and having 2 equivalent dicts.

Post by Roger Binns
There hasn't been any need for what you describe so
far, so no code has been written. If it is needed, it will be >written.

I was thinking more from a developer point of view. With the data being stored in a relational DB, it's natural that a programmer would eventually have a need to query to view/manipulate the data for development & testing.

-Joe Pham


________________________________________________________________
NetZero Gift Certificates
Give the gift of Internet access this holiday season.
http://www.netzero.com/give

[Roger Binns]

Post by d***@netzero.com
Can you save and restore the dict keys if you're using BP IDs as the
record keys?

The dict keys are transient. For example every time a phonebook merge
happens, a new dict is created using new integer keys.

Post by d***@netzero.com
I guess I'm considering the ideal goal of using your existing dict,
calling populatefs and getfromfs, and having 2 equivalent dicts.

The important thing is that the *contents* are identical. For the keys,
most of the phonebook code (display, add, merge etc) knows nothing about
serials (and doesn't need to). That leaves two choices:

- (current) Leave the keys in the rest of the code as integers and
only have the code that interfaces with the database change them
to the serial

- modify every location that uses the dicts so that they always expect
and work with the keys being the serial

The first choice is way less effort and already done :-)

Post by d***@netzero.com
I was thinking more from a developer point of view. With the data being
stored in a relational DB, it's natural that a programmer would eventually
have a need to query to view/manipulate the data for development & testing.

Did you try the developer console and the commands I listed?

Roger

[d***@netzero.com]

Post by Roger Binns
The first choice is way less effort and already done :-)

Understand. It is what it is and there's no value of pursuing this thread; but I'd like to get the last word in :-) My thinking is that you'd treat the dict keys as 'transient data' similar to other fields so that they can be saved and restored with everything else. In any case, like you stated: you have something that works.

Post by Roger Binns
Did you try the developer console and the commands I listed?

Yes, and it seems to 'print' out the results to the console vs to return data. For example, if I enter:

sql('select count(*) from phonebook')

I'd see something printed out like:

(226,)

What I'd like to be able to do is:

for i in range(sql('select count(*) from phonebook')[0]):
.....


-Joe Pham


________________________________________________________________
NetZero Gift Certificates
Give the gift of Internet access this holiday season.
http://www.netzero.com/give

[Roger Binns]

Post by d***@netzero.com
My thinking is that you'd treat the dict keys as 'transient data' similar to
other fields so that they can be saved and restored with everything else.

You seem to think the keys have some meaning. They have absolutely
*no meaning* whatsoever.

In fact I could have used a list instead:

phonebook=[ {...record C...}, {...record A...}, {...record B...} ]

The column sorter just remembers what order to show things, so in this
case it would be [1,2,0]

The reason for using keys instead of a list is what happens when someone
deletes something? Say they deleted the middle record (#1). If I
was using a list I'd have to go back and modify the column sorter
data to become [1,0]. And if there were any other places that referenced
the list items I would have to modify them too.

By using integer keys instead, I can just remove the relevant item
from other lists and not have to worry about shuffling values up and down.

And then in other cases such as the phonebook importer, it deals with
3 concurrent phonebooks. One is the existing data, one is the imported
data and one is the result data. Many of the entries in the 3 phonebooks
don't even have serials until after the import is complete.

Just think of those keys as positions in a list, but there can be holes in
the sequence. That is it.

On the other hand, the database code cares deeply about records. In
particular it needs to know if a particular record is the same entity
as one it has seen before, so that we have a journal of changes for
each entity.

Rather than make the database code grovel into each structure and for
example know that the entity id/serial for a phonebook is stored how
it is, that code just uses the dictionary key. It is up to the caller
to ensure the key is relevant. And the good news is that the function
extractbpserials can easily make the conversion from the meaningless
integer keys dict to the meaningful entity id/serial keyed dict needed
by the database code.

Post by d***@netzero.com
sql('select count(*) from phonebook')
(226,)
.....

With pysqlite you have to do it in two steps:

cursor.execute("select count(*) from phonebook")
for i in range(cursor.next()[0]): print i

My new wrapper lets you do this:

for i in range(cursor.execute("select count(*) from phonebook").next()[0]):
print i

I have attached the html doc for my new wrapper (it has been cut/pasted from an
email message hence the formatting at the bottom).

I am considering also allowing SQL functions to be added written in Python.
That may make some of our work a lot easier. (In theory it could even handle
all the existing indirect stuff).

Roger

[Vic Heintz]

Why is it that when I purposely save an image with a color depth of
8-bits/pixel and add it as a wallpaper in bitpim it gets converted to
32 bits? This makes the file size too big to be used as a front display
wallpaper on the Samsung A670. This forces me to use filesystem view to
add these wallpapers.

It is overkill to have more than 16 bit color depth anyway for these
small displays. With 16-bits you can have a unique color for each pixel
of a 256X256 display. For my 128X128 display I am perfectly happy with
8-bit color.

Vic

[Roger Binns]

Post by Vic Heintz
Why is it that when I purposely save an image with a color depth of
8-bits/pixel and add it as a wallpaper in bitpim it gets converted to
32 bits?

Because that is how the wxWidgets code saves out images.

Post by Vic Heintz
This makes the file size too big to be used as a front display
wallpaper on the Samsung A670. This forces me to use filesystem view to
add these wallpapers.

Stephen has some code used by the Sanyo phones which saves the image
using the best amount of colour but have the file still fit within
a certain size. See conversions.convertto8bitpng.

The general goal in all this is that the Profile should specify
what happens to the image in terms of format and size (and it
needs to take into account the origin field).

What I am thinking about next is that BitPim should save the original
image it was given from the user, and remember conversion parameters from
that (ie a crop/resize/colour depth etc). When writing to a phone
a new image is done using he conversion parameters. When you select a
different phone, it will use new parameters but the original image is
still available.

Roger

[Stephen Wood]

Go back and read my postings on this topic (related to Sanyo phones).

It seems to be really hard (or impossible) to convert images to 8 bit
using the current version of wxpython. There is a routine
convertto8bitpng in conversions.py that uses external binaries. (I hope
this is a temporary solution) This is called in com_sanyo.py just
before the image is sent to the phone. You could do similar for the
Samsung phones.

Stephen

Post by Vic Heintz
Why is it that when I purposely save an image with a color depth of
8-bits/pixel and add it as a wallpaper in bitpim it gets converted to
32 bits? This makes the file size too big to be used as a front display
wallpaper on the Samsung A670. This forces me to use filesystem view to
add these wallpapers.
It is overkill to have more than 16 bit color depth anyway for these
small displays. With 16-bits you can have a unique color for each pixel
of a 256X256 display. For my 128X128 display I am perfectly happy with
8-bit color.
Vic
-------------------------------------------------------
SF email is sponsored by - The IT Product Guide
Read honest & candid reviews on hundreds of IT Products from real users.
Discover which products truly live up to the hype. Start reading now.
http://productguide.itmanagersjournal.com/
_______________________________________________
Bitpim-devel mailing list
https://lists.sourceforge.net/lists/listinfo/bitpim-devel

[Mike Wells]

Is there some kind of chart available that shows what the
file format and size limitations are for Sanyo phones using
the serial transfer mode? I'm using the SCP-5500.

Also, is it the case that if a wallpaper image is tranferred from
the phone to the PC, this same image can't be transferred back to
the phone without first reducing the color depth and image size?
In other words, the phone can't accept its own image export format?

Thanks.

[Stephen Wood]

Post by Mike Wells
Is there some kind of chart available that shows what the
file format and size limitations are for Sanyo phones using
the serial transfer mode? I'm using the SCP-5500.
Also, is it the case that if a wallpaper image is tranferred from
the phone to the PC, this same image can't be transferred back to
the phone without first reducing the color depth and image size?
In other words, the phone can't accept its own image export format?
Thanks.

Read all the notes for the Sanyo phones and if that does not answer your
questions, please let me know what is not clear or could be improved:

http://bitpim.org/testhelp/phones-sanyo.htm

Stephen

[Mike Wells]

Post by Stephen Wood

Post by Mike Wells
Is there some kind of chart available that shows what the
file format and size limitations are for Sanyo phones using
the serial transfer mode? I'm using the SCP-5500.
Also, is it the case that if a wallpaper image is tranferred from
the phone to the PC, this same image can't be transferred back to
the phone without first reducing the color depth and image size?
In other words, the phone can't accept its own image export format?
Thanks.

Read all the notes for the Sanyo phones and if that does not answer your
http://bitpim.org/testhelp/phones-sanyo.htm

The relevant note seems to be as follows:

"File too large: Sanyo phones appear to have a image file size limit of
16K bytes. Ringer files have a similar limitation, although it is reported
that some phones allow one larger MIDI file."

I found some additional information regarding Sanyo wallpaper limitations here:

http://www.howardforums.com/showthread.php?s=&threadid=93780

It's probably the case that the SCP-5500 and SCP-5300 have the exact same
limitations for serial uploads for all media.

The howardforums posts state that the screen size is 132 x 176, but that
the *useable* dimensions are actually:
132 x 129 for wallpaper aka screensaver aka background and
132 x 144 for caller ID.

As this information is not in the Sprint and Sanyo product specifications or
in their user manual maybe it can be included in the Bitpim phones-sanyo notes?

I assume the wallpaper images need to be cropped in addition to being
color reduced? Because 132x129 at 8-bit = 17,028 bytes. It seems that a
value of 16,383 is used by the ppm utility.

I do not know what the size limitation is for the MIDI files, but it
appears that it's not 16K for the SCP-5500 as I've uploaded larger files. I
guess I can just experiment and try to determine the limits and report the
results. BTW, I've found quite a few MIDI files aren't acceptable regardless
of their size for some reason, and the acceptability criteria is not simply
related to the MIDI type.

Thanks.

Mike