Welcome!

Welcome to the official BlackBerry Support Community Forums.

This is your resource to discuss support topics with your peers, and learn from each other.

inside custom component

Java Development

Reply
Developer
marchywka
Posts: 1,415
Registered: ‎07-30-2008
My Device: Not Specified

Re: SQL database?

If your object isn't in memory, where is it? All you are talking about here is RAM versus FLASH perhaps.

 

What types of information do you actually store? I would have thought you would use fairly short

records and use the DB to organize them into things like arrays.. How do you use the DB to just

select part of an array? 

 

I've been reusing this one class now with different record types for just about everything for

this browser app and it seems to work reasonably well. Each time I need a new record type,

I just make a class with simple store delete and find methods with "find" returning an iterator

on one of the index "tables." 

 

Do you have any code samples with names that illustrate how you use the DB? Still curious

about specific queries or record formats. 

New Developer
Kiernan7
Posts: 35
Registered: ‎04-16-2009
My Device: Not Specified

Re: SQL database?

This is from another board (we have also been considering this -- be aware he seems to be a very small concern at the moment):

 

We have had a lot of questions about the Zoduna Datastore, so I am providing an update on the information. We do not have the website up, but the database is available and is being used by 30 companies at this time.

Here are some basic facts regarding the DB:

# Support for both Java J2ME / MIDP CLDC and CDC devices
# Database use: business applications, consumer applications, digital media management, games, etc. Is also being tested in military, aircraft, and automotive embedded application applications. We are also using the database for the development of consumer applications
# It is not free, but it is about 1/4 the cost of other commercial databases and is very inexpensive (can't build, maintain and support for free): Price is $15 on CLDC devices and $25 on CDC devices with volume discounts taking the price to as little as $.10 in very large quantities
# Size: 100K and small enough to run on nearly all phones, including the cheap, inexpensive phones (less than 1/3 the size of any other options and less than 1/20 to 1/200th the size of most free options)
# Because of the size of the database, it is the only database that will run on more than 1/2 of the devices in the world
# Performance: About 5X to 20X faster than any other options available (A query for 200 records can be less than 1/10th of a second or under 1 second depending on the type of query and indexing. We have joined over 10,000 records in 3 seconds on a mobile phone with average capabilities)
# Platform Support (over 3 billion devices): Fully tested on Windows mobile, Windows Blackberry, Symbian, Android, Brew, MobiLinux, LiMo and many other forms of Linux with more than 4,000 function points per platform
# JVM Support: Built-in JVMS for Blackberry, Symbian, and Android as well as Aplix (JBlend), Esmertec (Jbed), Mysaifu JVM (free), KAFFE (Free), NSIcomp (CreMe), IBM J9


Characteristics (Many of the functions can be removed upon request to shrink the DB size and further improve performance):

# Storage mode, Memory mode, Combination of storage and memory mode
# Built-in JDBC (In the very near future, we will also have an ODBC driver)
# Database Encryption: great for financial transactions and digital media (music, video, etc.)
# UTF-8 Unicode Support: with language, specific sort templates (starting with English, Spanish, French, German, Italian, Chinese and Arabic)
# Views with limited update and delete capabilities
# Triggers
# Referential integrity
# ACID Support (Autocommit, Commit, Rollback)
# Data Types: Data Types: INTEGER, STRING, TEXT,CHAR, NCHAR, VARCHAR, NVARCHAR, BINARY, DATE, TIME, TIMESTAMP, DECIMAL, BYTE, SHORT, BLOB, CLOB, NCLOB, BOOLEAN, FLOAT, DOUBLE, SMALLINT, INT
# Select Statements: Support WHERE, and all Aggregate Functions, all Predicate Functions, etc.
# Joins Functions: Inner Joins (Equi-join / Natural join, Cross join), Outer Joins (Left outer join, Right outer join, Full outer join), Self Join, Sub-Queries, Nested Queries, Union
# Predicate Functions: Comparison (">","<", "=", etc.), LIKE, AND, OR NOT, BETWEEN, LIMIT
# Aggregate Functions: DISTINCT, COUNT, GROUP BY, HAVING, IN, EXISTS, SUM, TOTAL, MAX, MIN, AVG
# String Functions: UPPER, LOWER, LENGTH, LTRIM, RTRIM, ISNULL, REPLACE, SOUNDEX, SUBSTRING, TRIM
# Arithmetic Functions: ABS, COALESCE, CEIL, FLOOR, EXP, POW, LN, ROUND, ROOT
# Date Functions: ADDMONTHS, ADDDAYS, CURRENTDATE, CURRENTTS, DIFFDATE, DATEFORMAT, TODATE, TOTIME, TOTIMESTAMP
# Cursor Functions: Scrollable and Updatable


We released the CLDC 1.0 version in February and are in the final testing of CLDC 1.1 with enhanced functionality across all of the platforms noted above. The product also runs in the desktop environment. We are currently working on database design tools / integration with popular tools as well as a mobile to server synchronization tool to improve the overall programming and deployment capabilities of the database.

That should give you a good view of the database. If you know someone who would like an evaluation copy, provide the following information to bruce.howard@zoduna.com and we will send you a copy:

# Company Name
# Company Address
# Company Phone Number
# Company Website
# Contact Person
# Contact Person Email
# Contact Person Phone
# The types of mobile devices you want to use the database on (i.e. CLDC 1.0, CLDC 1.1, Blackberry, Symbian, Android, Brew, MobiLinux, Limo, CDC, etc.)


Java J2ME MIDP CLDC Embedded SQL Persistence Database JDBC

This message was edited 8 times. Last update was at Sunday, April 26, 2009 12:22:39 PM by Bruce Howard
Bruce J Howard
bruce.howard@zoduna.com
815-530-6931
Developer
marchywka
Posts: 1,415
Registered: ‎07-30-2008
My Device: Not Specified

Re: SQL database?

"About 5X to 20X faster than any other options available "

 

What did you compare it to?  LOL.

it looks like most of those extra functions are already available in java and stuff you would need anyway

for the "rest of the app" ( I know, the GUI or DB IS the app). Certainly true ACID capability would be helpful but

there isn't much you can do if the hardware has certain issues that prevent it although I have to admit

to having not thought this through, I thought some one mentioned it isn't possible. 

Prewrite journals etc help but that isn't hard to implement I guess.

 

 

 I guess I'm still not sure the DB in this setting competes with a datastructure/persistent store.

OK, so you do a query and get back 100 "records" then what do you do with it? Iterate through it and put it in a datastructure

and into a GUI ? My tree structure I can count the operations to find the first record and it comes

with an iterator to find follow on. Sure, this relies on having set up the indexes properly but IIRC the DB is not omniscient

either.  I don't currently have much in the way of reorganization support but I did include it on desktop builds.

I haven't checked size lately ( I coded a bunch of kluges for things I didn't want to fix ) but I'm sure it is under 20k.

 

 

Certainly ANYTHING in native code pretty much will offer better performance than java. But, there can be a lot of overhead

in constructing a standarad query and then digging through your results. 

 

 

Developer
RexDoug
Posts: 4,764
Registered: ‎07-21-2008
My Device: Not Specified

Re: SQL database?

Having been through this issue of re-creating (I hesitate to use the word "porting") an app from iPhone to BB, data persistence was about 10% of the effort. The other 90% was stuff like creating UI components that (more-or-less) retained the "look and feel" of the app. I also spent considerable effort re-creating things like PLIST and CFDictionary, great stuff if you have it but a real pain when you don't. And (by the way), you have a real MVC in the iPhone, but not on the BB.

 

And, if your apps rely on the network, or utilize GPS, you will find that the BB environment is an entirely different animal. On the iPhone, you have an HTTP engine already coded up for you, and it only has to worry about one network. On the BB, you have 120 different carriers, with 120 different sets of network issues, and you have to code your own HTTP engine.

 

I guess that I'm trying to say "look at these other issues" before you decide that SQL is your primary impedement to moving your apps to the BB. Not trying to talk you out of it (it *is* doable), but don't make the mistake of thinking this one issue gets you on the platform.

 

 

New Developer
ptgoce
Posts: 14
Registered: ‎04-29-2009
My Device: Not Specified

Re: SQL database?


marchywka wrote:

If your object isn't in memory, where is it? All you are talking about here is RAM versus FLASH perhaps.


 

Not the whole object is in memory, that is in the memory used by the process.

 

I'll try to explain it via Golf Scores app (can see screen shots on my website):

 

  • when loading the first screen I create array of courses and each course only gets id and name from database.
  • when you select a course and with that go to 'rounds' screen the course object goes to the database and populates its rounds array with objects of type Round - each round only gets id and 'date played' from database
  • when you select Round, well you can guess what happens... 


Now, when the device notify my app of low memory I can de-allocate rounds array (and everything referenced down the tree) of each course but the one that is currently been used/view by the user.

I did something similar in Windows Mobile version of Golf Scores, which I haven't been maintaining for long time - waiting for Windows Mobile 7 :smileytongue:


Thanks RexDoug for the info!

Fortunately my apps don't really much on the things you mentioned. The only thing I need is database. I though RIM announced last December that they are going to bring sqlite in BB, what happened?

-----------------------------------------------------------------------------
http://petrovski.net.au
Developer
marchywka
Posts: 1,415
Registered: ‎07-30-2008
My Device: Not Specified

Re: SQL database?

I guess what I would have, and this can be quite naive given lack of disk or even significant multi-level memory caching

in this situation, is simply index trees with pointers to course records. I have been maintaining a simultaneous tree and double

linked list structure which may seem wasteful but compared to DB probably is not :smileyhappy: Anyway, given this and the begin/end indicies,

it is a simple matter to get the names out of one tree with total ignorance of the rest of the target data and code that is very simple requiring no

real lookup. For example, if I just want to list names that already ordered in an index,

 // this isn't my real interface, just for clarity.

Something like BonsaiIndex bidx= m_db.getFirst(INDEX_0);

while (bidx!=null)

{

 String name[i]= bidx.getKey(); // this is actually a fairly slow process as string keys I maintain as chunked long[] to save space with similar prefixes

bidx=bidx.getNext();

}

 

 If I then wanted the details, I can call bidx.getTarget() to return the complete record which could contain more

primitive types of pointers to other objects or other index entries etc. 

 

The index objects themselves are fairly small and could be made smaller with reasonable use of base and derived

classes ( I probably ended up with redundant entries and mutually exclusive members, have to check... ). Basic element

is just the key,  two pointers up to a mom and "God" as well as left, right,next, prior. Of course, there is also a pointer to

a target object which is typically something that looks like a record and an array of other index entries.

 

 And, sure, I can wipe out complete subtrees by settting a few pointers and provide special insert methods

if I know that I am adding sorted data to a given index- I may not even care if I get a linear "tree" (list) as long as I don't 

have to start at beginning each time I want to append to it  :smileyhappy: 

 

So, I'm not sure that SELECT and SORT etc are all that helpful if you already know what keys you are likely to care about.

I don't have to copy anything as the returned thing serves as an iterator on the target data without modification.

 

Sure, I have some kluges and performance suffers from a few dumb things I didn't want to think through but I'm

pretty sure I can fix that when I get time, and in any case it is acceptable and probably smaller and faster than

most alternatives in this settgin.

 

 

 

New Developer
ptgoce
Posts: 14
Registered: ‎04-29-2009
My Device: Not Specified

Re: SQL database?

marchywka, this sounds like writing some sort

of database engine :smileyvery-happy:

 

Why do all that when the guys that maintain sqlite have already done it for us. An the best part is that is all tested and maintained :smileywink:

 

Also, been written in C runs very fast!

 

Can you imagine how much time in coding and testing is involved in this compared with something like 10 lines of code?

 

  • run your SQL statement
  • loop trough each record returned and create, populate and store object in array


 I personally thing that RIM would have gotten better return on investment if they ported sqlite or derby to BB instead of writing the persistence store. But that's just me... and Apple, Nokia, Microsoft, who decided to use databases in their platform...

I'm just disappointed, that's all :smileysad:

-----------------------------------------------------------------------------
http://petrovski.net.au
Developer
marchywka
Posts: 1,415
Registered: ‎07-30-2008
My Device: Not Specified

Re: SQL database?

I'm not sure where you would draw a line - you could replace your hashtables with DB calls too-

and in fact I added a get/put interface so I could drop in my trees for hashtables where enumeration was

a common need. I can use the same trees for "DB's" that I don't want to save in between executions too.

So, if I need to que up things like network requests where I may have some flexibility and delaying

or reordering or even concatenating them, I can index them and make fairly involved decisions about

how to handle them depending on time or other constraints. It wouldn't make much sense to 

store these in a real DB and do SQL queries all the time and then make sure the DB is empty each

time I restart. 

 

OK, so you just created a bunch of garbage building an SQL query ( instead of using stringbuffer) and threw it over

a fence ( interface) to have it parsed and then have the DB pick a retrieval approach and maybe even sort records

so you can copy them into an array and then what? With many users and secondary storage ( and I'll be the

first to admit that multi-level memory caches need to be thought of as secondary storage LOL) sure some general

approach may be worth it. But here it just seems to make more sense to keep your DB in your own data structure.

The internals of not magic, it has to have some idea which indexes to build etc. 

 

 

When does the DB reorganize or clean itself up? 

 

 

 

 

 

New Developer
ptgoce
Posts: 14
Registered: ‎04-29-2009
My Device: Not Specified

Re: SQL database?


marchywka wrote:

I'm not sure where you would draw a line - you could replace your hashtables with DB calls too-

and in fact I added a get/put interface so I could drop in my trees for hashtables where enumeration was

a common need. I can use the same trees for "DB's" that I don't want to save in between executions too.

So, if I need to que up things like network requests where I may have some flexibility and delaying

or reordering or even concatenating them, I can index them and make fairly involved decisions about

how to handle them depending on time or other constraints. It wouldn't make much sense to 

store these in a real DB and do SQL queries all the time and then make sure the DB is empty each

time I restart. 


I don't think you get what I'm trying to use DB for, or maybe you don't want to, so you can be right?

Why are you so protective over this persistence store, like you wrote it or something...

 

Let me be clear.

  • I want to store data so next time the users start the app (maybe in a year time) the data is still there.
  • I want to be able to change my objects and I want the data to be still available. In .NET you can serialize (binary or XML) object and than even if you add/remove variables and/or properties you can still deserialize it. Now I can serialize dictionary of objects and get them back - this sound to me a lot like the persistent store, but more flexible.
  • I want to be able to export that data, like I do from the iPhone, and than import it (without the need to write translator) in another app - possibly not even written in Java, you see last time I checked java wasn't the only way you can develop.

 


marchywka wrote:

OK, so you just created a bunch of garbage building an SQL query ( instead of using stringbuffer) and threw it over

a fence ( interface) to have it parsed and then have the DB pick a retrieval approach and maybe even sort records

so you can copy them into an array and then what? With many users and secondary storage ( and I'll be the

first to admit that multi-level memory caches need to be thought of as secondary storage LOL) sure some general

approach may be worth it. But here it just seems to make more sense to keep your DB in your own data structure.

The internals of not magic, it has to have some idea which indexes to build etc.

 


 

 What do you mean "garbage? Do you even know what database are and how to use them?!?!? 

 "threw it over a fence ( interface)" - very surprised to see this coming from java developer. What happen with "not reinventing the wheel" or even better, why not use a wheel when is already built for you? Do you think you can make your wheel rounder?

 

From your arguments I don't think you really know much about databases, especially in-process ones. How well they perform, how they store and retrieve data- just have look at some of the open source ones.

 

Do you think that your approach would perform faster? Definitely would be much more code, and for what?

 

Are you saying that Apple made a mistake for using sqlite for their own apps? Do you think you know better than them or Microsoft using SQL Server in Windows Mobile?

 

You see I'm not the only one who is for databases. This is the only mobile platform that I have come across and doesn't have SQL database - very suppressed!

-----------------------------------------------------------------------------
http://petrovski.net.au
Developer
marchywka
Posts: 1,415
Registered: ‎07-30-2008
My Device: Not Specified

Re: SQL database?

"What do you mean "garbage? Do you even know what database are and how to use them?!?!?"

 

Garbage of course refers to the temp objects you often get from formulating string for things like queries etc. I'm just

suggesting you normally have limited retireval modes and it is unlikely that you benefit from creating this temporary

object, the "garbage" is just a minor part but it is literally accurate ( GC).

 

" I want to store data so next time the users start the app (maybe in a year time) the data is still there."

 

 Persistent store does this no?

 

" I want to be able to change my objects and I want the data to be still available."

 

I have to admit that sometimes this can be annoying but I personally haven't thought

through the various options here. I have only run into it during development and 

changes are often incompatible in any case- keeping old data can just lead to inconsistent

results anyway and export/import is more reasonable than encumbering a whole

app to give an appearance of support for this type of flexibility. 

 

 

"In .NET you can serialize (binary or XML) object and than even if you add/remove variables and/or properties you can still deserialize it. Now I can serialize dictionary of objects and get them back - this sound to me a lot like the persistent store, but more flexible"

 

In order to be flexible, it doesn't just need an API or interface with a bunch of widgets, it has to not exceed the

resource limitations of the target platform for any situation to which you try to bend it- it still have to work as part of being flexible. This is the same argument

you get regarding PDF reader or other bloated apps that add (often useless or distracting ) features and flexibility while slowing down everything else.

I'm not putting a DB generically into this "useless" catagory but pointing out that in this setting there are more "flexible"

options in terms of enabling applications consistent with device limitations. People always talk about platform

independece and flexibilty as if something could run with no platform- that is you have no concern for actual available resources. 

 

I've been through this with SOAP and I think you are exaggerating a bit here in terms or serialization capabilities. If you

are going to write code to define a DB record it is just as much work as writing your own XML IO and it can be

generated easily anyway. 

 

 

"I want to be able to export that data, like I do from the iPhone, and than import it (without the need to write translator) in another app - possibly not even written in Java, you see last time I checked java wasn't the only way you can develop."

 

As do I but even here the few times I've looked XML or other bloated standards don't make a lot

of sense, even if you want to limit yourself to wifi. It is often worthwhile to go to various flat-text or binary formats

depending on the specific. I'm not sure what iPhone supports but all your concern seems to be regarding XML

and further these other things will not likely be standards ( you need to write something on the server

or "flexible" app to get it to work). 

 

 

"From your arguments I don't think you really know much about databases, especially in-process ones. How well they perform, how they store and retrieve data- just have look at some of the open source ones."

 

I was asking for performance metrics and stated repeatedly that just about anything in native code could outperform

java.Someone else even suggested the SQL lite would only produce bloat is implemented as another piece of java code.

 

"Do you think that your approach would perform faster? Definitely would be much more code, and for what?"

 

 Since I am "replacing" the ultimate source and destination of my hypothetical DB lookup with the end result I'm not

sure how it could be slower (duh). I guess if you are only reading a flat list of records so you can put them on a GUI

this may be fine but I need to do a lot of other stuff and this data structure will support that. 

 

 

"Are you saying that Apple made a mistake for using sqlite for their own apps? Do you think you know better than them or Microsoft using SQL Server in Windows Mobile?"

 

I'm confessing ignorance, looking for data not ad hominens ( although I'm up for a flame war too waiting for a GUI app to load LOL).

Again, they have their uses as do PDF files. Often however people just throw them at problems and get something

that kind of works and is projected to work much better once you buy a more expensive phone. It's like watching

your CPU max out to animate some snow flakes on a flash ad while you are trying to read email. Nothing wrong

with flash, nothing wrong with PDF, nothing wrong with animation, but you can't just throw them at problems and

that is why I'm asking to understand specific reasons people think they need a DB.

 

 

 

"You see I'm not the only one who is for databases. This is the only mobile platform that I have come across and doesn't have SQL database - very suppressed!"

 

 

When we settle technical arguments by a popularity contest, we have already all lost :smileyhappy:

 

 

 

So, do they get true ACID performance and when does reorganization occur to maintain performance or have they found

a way  around this?