[App Inventor Open Source Development] SQLite component [was: Hangout link for right now]

2,023 views
Skip to first unread message

Mark Friedman

unread,
Apr 29, 2013, 7:42:03 PM4/29/13
to App Inventor Open Source Development
We've been having a spirited discussion about the SQLite component in the #appinventor IRC channel on freenode.net.  It was triggered by this comment of mine:

I notice that there appeared to be general agreement on having a single SQL block in to which the user would type their SQL query.  That doesn't seem consistent with the general idea of App Inventor, which is to replace textual code with blocks.

I would (perhaps unfairly) summarize the replies as:

... but it would be hard to create SQL blocks and in any case can't this just be for advanced users?

Feel free to correct my summary ;-)

I've also attached a transcript of the IRC segment dealing with this, in case you want to see the full discussion.

-Mark

On Sun, Apr 28, 2013 at 7:29 PM, M. Hossein Amerkashi <kkas...@gmail.com> wrote:
See latest version on my AI instance here: http://hosseinamerkashi.appspot.com

You can use my sample app from HERE to experiment with.

-Hossein.



--
You received this message because you are subscribed to the Google Groups "App Inventor Open Source Development" group.
To unsubscribe from this group and stop receiving emails from it, send an email to app-inventor-open-so...@googlegroups.com.
To post to this group, send email to app-inventor-o...@googlegroups.com.
Visit this group at http://groups.google.com/group/app-inventor-open-source-dev?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

SQLite Discussion.pdf

Jos Flores

unread,
Apr 29, 2013, 8:39:32 PM4/29/13
to app-inventor-o...@googlegroups.com
Hey Mark, thanks for the forward!

I wouldn't say the summary is unfair, but it would be good to stress
that this particular module isn't 'just for advanced users' because it
could be difficult for beginners, but that in fact, those advanced
users would rather use text and write SQL directly instead of having
to use a large number of blocks.


Even though the conversation started off a particular component, the
discussion is bigger than that. Advanced users are not the primary
target, but could we come up with a solution that caters for
beginners, without affecting advanced users? Most systems out there
can work in different modes. I barely go near my mouse all day, and my
OS supports that; other people use the mouse all the time, using the
same OS. On that very note, I've been working on replicating type
blocking in Blockly for the last few weeks (that thing in the blocks
editor that, when you start typing the name of a block, it will
provide you with some options, and by hitting enter, the block will be
created). That's quite advanced functionality, and don't see many
beginners using it.

Fragmentation is certainly a problem (or can be), but I believe this
forum and the irc channel are the kind of places where more advanced
users can get some of that support (and also through github!). Most
advanced users will be able to run from sources, so making components
available to others would be of great help, even if those components
never make it into the MIT server.

The Component Development Kit came up in the conversation; for those
who haven't heard about it, it would be a way to be able to upload
your own components (or someone else's) through the Designer, so that
they are available in the same way that 'default' components are. That
is a fantastic project, and would be of great help for this kind of
situations. It's not an easy project though, and it might have more
implications that it might seem on the surface (such as using the
companion, for instance), but I hope we can work on this after new
blocks is out. There's some preliminary work, as this project has been
around for a long time, even before MIT, but nothing that we can
really use at the moment.
If anyone out there is looking for a challenging project, do get in touch! :)


cheers,
José

Gary Frederick

unread,
Apr 30, 2013, 10:35:30 AM4/30/13
to app-inventor-o...@googlegroups.com
Good stuff

both Hossein's SQL component and the conversation.

Some of this conversation is 'what is App Inventor'. I think it is more a place that folks can get into mobile development without a huge learning curve. Once you are there you can do quite a lot and I think it is a good way to prototype ideas.

I like TinyWebDB. It has some shortcomings but it is a great way to show someone how to get started with a data base on the web. Using it and TinyDB does a lot. And if someone is finding they need more, having a component like the SQL component gives them someplace to go.

I think Fusion tables are pretty nifty, but we should consider dropping them. With the new web block and the future ability to do OAuth2, we can use the RESTful api to do everything Fusion tables do and it does not add complexity to App Inventor.

José said
"The Component Development Kit...
If anyone out there is looking for a challenging project, do get in touch!"

consider yourself touched José!!!

and

We are starting to have servers that could 'fragment' App Inventor. The CDK could help keep that under control. 

sooo

we have some things to talk about.

Gary
ai4a

James Hancock

unread,
May 1, 2013, 10:35:59 AM5/1/13
to app-inventor-o...@googlegroups.com
Interesting , I agree with Mark in that it should remain simple and clear and be aligned with the simplicity of AI, yet I also see it would be much slower for experienced users to write it the way I've outlined below.

But we could we write a constructor block along the lines of the make text block. 

Much prettier than this.......



 
FIELD NAME CONSTRUCTOR
A variable definition is constructed for each field name 

NAME,(text)

it contains the following values

TYPE (INT, FLOAT(x,x), VARCHAR, TEXT, DATE)
LENGTH (number)
DEFAULT (As Defined, NULL,TIMESTAMP)
COLLATION ( *cough* utf-8)
ATTRIBUTES (BINARY UNSIGNED UNSIGNED_ZEROFILL)
UNIQUE (BOOLEAN)
INDEX (BOOLEAN)
PRIMARY (BOOLEAN)

SELECT UPDATE and DELETE BLOCKS
accept individual field names or wildcard*
in addition to 
a WHERE socket 
A LIMIT socket
an ORDER_BY socket

quotes around strings, not numerals

could also have a socket for (perform local) or (perform web).

for anything more complex statements or definitions we could still write with a standard text block, 
excuses for roughness : it's 2.30am 

Mark Friedman

unread,
May 1, 2013, 1:48:26 PM5/1/13
to App Inventor Open Source Development
I like the general direction of James' proposal.  

It would be nice to think about how to set reasonable defaults for as many of the sockets as possible so that users doing simple things wouldn't need to plug in a bazillion blocks ;-)  Many such defaults would come for free, as normal SQL defaults, some might have to be thought about more deeply.  For example, App Inventor play pretty fast and loose with types by doing automatic conversions.  It would be nice if there were some way to do something similar here.  One idea might be automatically generate the columns/fields from the first INSERTs.

-Mark


M. Hossein Amerkashi

unread,
May 1, 2013, 2:33:37 PM5/1/13
to app-inventor-o...@googlegroups.com
Please see yellow-highlighted text below




On Wednesday, May 1, 2013 1:48:26 PM UTC-4, Mark Friedman wrote:
I like the general direction of James' proposal.  

It would be nice to think about how to set reasonable defaults for as many of the sockets as possible so that users doing simple things wouldn't need to plug in a bazillion blocks ;-)  
...and that's what I meant hard / cumbersome (for user) in the IIRC .
Let's say you have a table with 20 columns. Would this mean 20 constructors (one for each)?

 
Many such defaults would come for free, as normal SQL defaults, some might have to be thought about more deeply.  For example, App Inventor play pretty fast and loose with types by doing automatic conversions.  It would be nice if there were some way to do something similar here.  One idea might be automatically generate the columns/fields from the first INSERTs.
I don't think this is doable. You could have a table with 10 columns, out of which 7 are optional. So when you insert, you can, in any order just populate those 3 columns:
insert into employee  [ (col5, col2, col10) ] values (1, 2, 3)

 
Also, sql statements can get pretty complicated; e.g. union, minus, nested sql, grouping, ...
Also, sql is not just for insert, update, delete, select. There are many functions available -- see HERE that may come in handy



-Mark


To unsubscribe from this group and stop receiving emails from it, send an email to app-inventor-open-source-dev+unsub...@googlegroups.com.

Taifun

unread,
May 2, 2013, 3:50:49 PM5/2/13
to app-inventor-o...@googlegroups.com
Mark said in the freenode chat "the primary target audience for App Inventor are people with little or no programming experience."

Now, what are probably(?) the main requirements of that audience?
1. to be able to CREATE a table
2. to INSERT, UPDATE and DELETE
3. to SELECT: to be able to filter the table (WHERE) and to order the rows (ORDER BY)

Everything else probably(?) could already be considered as more advanced stuff (references between tables, primary key/foreign key, GROUP BY/HAVING, joins, views, triggers, transactions, functions, ...)

To create a table could probably be done in the designer window: the table is like a canvas and the table fields are like ball components you put into the canvas. Each field has some properties like data type, default, NULL value: YES/NO

In the blocks editor some blocks for each table could be provided similar as James already suggested:
-<table>.INSERT
-<table>.UPDATE
-<table>.DELETE
-<table>.SELECT
Additionally for more advanced users it would be nice to have the <table>.RunQuery block Hossein already presented to be able to do some more advanced stuff.

Taifun

Jos Flores

unread,
May 2, 2013, 8:20:37 PM5/2/13
to app-inventor-o...@googlegroups.com
Hey guys,

I like the idea of having both alternatives. I also agree with the
idea that if you don't know any SQL, you will rarely want to do a JOIN
operation. Most people will use just the one table and put all the
data in there, in a de-normalised form. But in that case, how is that
different from storing a list in TinyDB? For writing data, I don't see
a difference; for retrieving data, a select with a where clause is a
lot more flexible, but that could be added to TinyDB as a new
method... are there any other obvious benefits I am missing?
I realise that the data models are different, and in TinyDB there is
no structure as such (like a fixed table definition), but that has
both pros and cons, right?


Gary, I can brief you on the CDK (in a different thread), but to make
any progress in this project, we'd expect at least a 10 hours/week
commitment from someone. It is a complex project, touching most parts
of the system to some extent, so whoever takes it on is going to need
a good understanding of every part of app inventor. First step would
be to write a component, and after that, to make some modifications in
the Designer, and feel comfortable with how the appengine project is
structured. Let me know if you can commit the time to it, and I'll
open another thread.

cheers,
José
> --
> You received this message because you are subscribed to the Google Groups
> "App Inventor Open Source Development" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to app-inventor-open-so...@googlegroups.com.

Gary Frederick

unread,
May 3, 2013, 3:47:47 AM5/3/13
to app-inventor-o...@googlegroups.com


On Thursday, May 2, 2013 7:20:37 PM UTC-5, Jos wrote:
Hey guys,
... 

Gary, I can brief you on the CDK (in a different thread), but to make
any progress in this project, we'd expect at least a 10 hours/week
commitment from someone. It is a complex project, touching most parts
of the system to some extent, so whoever takes it on is going to need
a good understanding of every part of app inventor. First step would
be to write a component, and after that, to make some modifications in
the Designer, and feel comfortable with how the appengine project is
structured. Let me know if you can commit the time to it, and I'll
open another thread.

Woo Hoo!

I do not have the time currently
but

I am (slowly) adding a component.
I am (slowly) looking at the Designer.
I am looking at the app engine structure.

Most of my time has been in the buildserver.

I'll let you know when I can commit several months with 10 hours a week.

Gary

 
> email to app-inventor-open-source-dev+unsub...@googlegroups.com.

Mark Friedman

unread,
May 3, 2013, 1:57:12 PM5/3/13
to App Inventor Open Source Development
On Thu, May 2, 2013 at 5:20 PM, Jos Flores <josmas...@gmail.com> wrote:
Hey guys,

I like the idea of having both alternatives. I also agree with the
idea that if you don't know any SQL, you will rarely want to do a JOIN
operation. Most people will use just the one table and put all the
data in there, in a de-normalised form. But in that case, how is that
different from storing a list in TinyDB? For writing data, I don't see
a difference; for retrieving data, a select with a where clause is a
lot more flexible, but that could be added to TinyDB as a new
method... are there any other obvious benefits I am missing?
I realise that the data models are different, and in TinyDB there is
no structure as such (like a fixed table definition), but that has
both pros and cons, right?

The ability to construct, modify and query collections of structured data (i.e. the relational model) is an important use case for many problems and the builtin SQLite in Android is, I think, a convenient way of supporting that use case.  As you say, there are pros and cons and it would be good to support the cases where the pros of the relational model outweigh the cons.

-Mark

Mark Friedman

unread,
May 3, 2013, 2:01:09 PM5/3/13
to App Inventor Open Source Development
Excellent suggestion about specifying the table structure in the designer!  I still think you could go a long way with automatic creation, defaulting and automatic type conversions but maybe both could be supported.

-Mark


--
You received this message because you are subscribed to the Google Groups "App Inventor Open Source Development" group.
To unsubscribe from this group and stop receiving emails from it, send an email to app-inventor-open-so...@googlegroups.com.

James Hancock

unread,
May 23, 2013, 1:17:09 AM5/23/13
to app-inventor-o...@googlegroups.com
Carrying on the discussion re: integration of Hossein's SQL component. and Taifuns workaround https://groups.google.com/forum/#!topic/programming-with-app-inventor/htEwYq1o5f8
It's reasonably easy to see that in the future there will be other cases where there are difficulties with the addition of components. 
Perhaps not so much about the "behind the scenes"  but more aligned with how they will work from a users perspective and if they fit into the "concept" of AI. 
I think we all appreciate that AI is a great tool due to its simplicity and there are many types of users from young students to experienced programmers.  

If it is too difficult for developers to make components we do perhaps run the risk of stagnating, yet the tradeoff is if we make components within AI that are less than intuitive, it will distort what AI is.
The problem is a an SQL statement is never going to be intuative, and it's not the first thing you would put in front of a beginner. 
It seems like an easy solution to compile an advanced version of AI or as Jos suggested use an "Advanced draw" and segregate the components according to complexity, warning users of the reqd learning curve and showing them where the steps are. 
Segregation I'm not a fan of and compromise is not always the best solution either.
We should consider that this problem will arise repeatedly and in some ways that will be a good problem for us to have, but a thorough discussion now will ultimately lead us to consider options and solutions for even more complex additions in the future.   

Jos Flores

unread,
May 23, 2013, 3:58:46 PM5/23/13
to app-inventor-o...@googlegroups.com
James, yes, totally agree, it will keep happening with new components.

One of the problems we have in this list is that we are all advanced
users, you guys can think of a way of involving other users at
different levels in the discussion?

cheers,
José

M. Hossein Amerkashi

unread,
May 24, 2013, 11:30:42 AM5/24/13
to app-inventor-o...@googlegroups.com
How about Advanced drawer like attached?

-Hossein.
foo.jpg

Jos Flores

unread,
May 24, 2013, 11:47:15 AM5/24/13
to app-inventor-o...@googlegroups.com
Personally, I would be happier with a 'dual' version, that could
accept an abstracted number of blocks (with defaults) for the most
used cases (for beginners), and a raw text block for advanced users.

I mentioned before that it would be good to have the opinion of
instructors, but thinking about it now, I wonder why would an
instructor want to teach SQL through a component that takes only text.
It would be easier to use any kind of GUI for sqlite, out of App
Inventor, for teaching purposes. The idea of constructing a query with
blocks might be more appealing to them, but don't know to what extent.

In any case, I'd rather have it in advanced than dropping the idea completely.

cheers,
José

Mark Friedman

unread,
May 24, 2013, 2:45:45 PM5/24/13
to App Inventor Open Source Development
I mostly agree with James here but I would say that a set of SQL-based blocks would be no less intuitive than the existing Blocks Language.  But, intuitiveness isn't really the issue.  The Blocks Language isn't very intuitive for inexperienced users but it's easier to learn than more fully featured textual languages because it simplifies and constrains the program construction process.

Conceptually, the relational DB model is probably easier to learn than the general purpose, iterative event-based programming model of base App Inventor.  Especially for people who haven't been completely poisoned by the latter ;-)

-Mark


Mark Friedman

unread,
May 24, 2013, 2:57:15 PM5/24/13
to App Inventor Open Source Development
This whole discussion has made me wonder about some fairly basic aspects of the appeal and uses of App Inventor.  I always assumed that one of the most important aspect of AI, even for advanced users, was the use of a graphical Blocks Language instead of a textual programming language.  However, I'm getting the impression here that that's not entirely true for some of you.  So, what am I missing?  Why are you willing to write fairly complex textual SQL code but not write, say, Java apps.  Is it because SQL is simpler than Java.  Is it because the Blocks Language isn't really that important to you but some other aspect of App Inventor is?

-Mark

Gareth Haylings

unread,
May 24, 2013, 5:21:30 PM5/24/13
to app-inventor-o...@googlegroups.com
I'm an experience programmer but have never used Java. I now SQL quite well.

App inventor is one of the most innovative and easy to use tools to create a fully working and complex app very quickly. I don't really need to move to Java at the moment because I have been create work around to achieve everything I want app inventor to do since I started using in 2011. For example even though app inventor only has a player with no sound effects it quite easy to add echo and chorus sound effects by adding 2 players playing the same mp3 file with a delay timer delay. Creating custom controls that don't exist in app inventor is pretty easy like rotating knobs, option button, target pads, etc.

My point being even though App Inventor was design initially for non programs to learn how to program, I think it has a much wider audience. I think as long as there is an advanced tab in app inventor for features like SQL Lite this would be a plus.  


able to  

> To post to this group, send email to
> app-inventor-o...@googlegroups.com.
> Visit this group at
> http://groups.google.com/group/app-inventor-open-source-dev?hl=en.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>

--
You received this message because you are subscribed to the Google Groups "App Inventor Open Source Development" group.
To unsubscribe from this group and stop receiving emails from it, send an email to app-inventor-open-source-dev+unsub...@googlegroups.com.

Mark Friedman

unread,
May 24, 2013, 6:42:51 PM5/24/13
to App Inventor Open Source Development
Gareth,

  Could you go into some more detail about what makes App Inventor so compelling for you?  I assume it's more than just that you don't want to learn Java - an experienced developer such as yourself could probably learn it in much less time than it took to build the tools that you've built ;-)


On Fri, May 24, 2013 at 2:21 PM, Gareth Haylings <gareth....@googlemail.com> wrote:
I'm an experience programmer but have never used Java. I now SQL quite well.

App inventor is one of the most innovative and easy to use tools to create a fully working and complex app very quickly. I don't really need to move to Java at the moment because I have been create work around to achieve everything I want app inventor to do since I started using in 2011. For example even though app inventor only has a player with no sound effects it quite easy to add echo and chorus sound effects by adding 2 players playing the same mp3 file with a delay timer delay. Creating custom controls that don't exist in app inventor is pretty easy like rotating knobs, option button, target pads, etc.

My point being even though App Inventor was design initially for non programs to learn how to program, I think it has a much wider audience. I think as long as there is an advanced tab in app inventor for features like SQL Lite this would be a plus.

Actually, that's not quite right.  We designed it for non-programmers to be able to quickly write interesting and fun mobile apps, in order to move them from being consumers of technology to creators.  The hope was that then they might become interested in learning in learning to program, but "getting them from zero to app" as soon as possible was the primary goal.  I suspect that if teaching programming had been the initial goal then App Inventor would have ended up quite different.

-Mark

To unsubscribe from this group and stop receiving emails from it, send an email to app-inventor-open-so...@googlegroups.com.

M. Hossein Amerkashi

unread,
May 24, 2013, 10:24:02 PM5/24/13
to app-inventor-o...@googlegroups.com
Mark,

In my opinion, advanced user doesn't necessarily mean proficient in Java. A user can be proficient in some other programming language, but have zero knowledge of Java. Also, one could have knowledge of Java, but zero knowledge about mobile app programing and Android SDK. For all programming languages, working with backend database and knowledge of SQL is almost a must. So, its natural for non-Java programmers to know SQL and as said, they may not be ready to dive directly into programming with Android SDK.

-Hossein.

James Hancock

unread,
May 25, 2013, 1:25:26 AM5/25/13
to app-inventor-o...@googlegroups.com
Low learning curve, low complexity = speed, understandability and confidence.
Being a visual learner a textual language takes a lot longer to get my head around.
I sort of relate Appinventor to UML, just started learning c# as part of a diploma I'm doing. I know what mark means about being "poisoned" I keep thinking about how I would achieve something in AI. 
I don't have many issues with understanding programming concepts but AI lets me "see" how to construct and what's going on. It's an interesting transition :)
 

Gareth Haylings

unread,
May 25, 2013, 5:35:00 AM5/25/13
to app-inventor-o...@googlegroups.com
Mark 

You are quite right, it would have been quicker for me to learn Java than to write the tools I've created but as Hossien said " one could have knowledge of Java, but zero knowledge about mobile app programing and Android SDK" I have neither but can decipher java and understanded even to I can write programs using the code.

The great thing with app inventor is that all the build blocks are already there without having to know or learn anything about Android developement or learn a language.

Heres an example of why I favour using App Inventor over feeling the need to learn Java and the Android SDK
I have a number of RS Media robots which are quite awkard to operate from their the remote control that comes with them, so 3 years a go I developed a piece of  windows software called the RS Media Control Station (RSMCS for short. You can find out more about my software on my old website www.themadrobot.talktalk.net) which allows you to control the robot from a windows computer and do a number of other tasks. The software took me roughly 6months to write and I had to learn lots of new skills along the way.

In 2011 I found out about App Inventor and with out any knowledge of android development wrote a cut down version of my RSMCS. This only took me 2 days to write compared to the 6 months I had spent writing the windows version. I could have spent a few months getting to grips with JAVA and the Android SDK meaning I would have had a good understanding of how to write a Android Apps from start to finish but this would have taken me away for my original goal of just wanting to be able control the RS Media from the my phone.

In my opinion a lot of innovative ideas are held back by the learning curve. App inventor I find gets over this as all the hard work is done behind the scenes. This is also why I have writing the AI Project Constructor software to try and speed up writing app even more when using App Inventor and to give users the ability to do thing currently not availble in the App inventor interface (e.g. copying snippet code from 1 project to another).

Jos Flores

unread,
May 25, 2013, 12:32:03 PM5/25/13
to app-inventor-o...@googlegroups.com
There's a fundamental part of App Inventor that is not available in
the Android SDK, the REPL.

We all love the instant feedback of incremental development, it's in
our nature (if you see it as a reward system), let alone how time
saving it is. Compare that to pushing to the phone (or emulator) every
time you make a change in the source of an SDK app.

Add that to the complex bits that Gareth mentions, like having to add
AsyncTasks or external libraries (fight with the IDE!), and the
boilerplate needed; even if you use the Design view for layouts, you
still need to hook in events, prepare strings, and so on.

The price we pay is flexibility, but a component like Hossein's brings
back a good part of that flexibility.

Blocks work great for beginners, and can make the experience fun, but
to me, the REPL is the killer feature; some members of the Clojure
community are working on that idea too:
http://www.youtube.com/watch?v=1NptqU3bqZE

cheers,
José

Wanddy Huang

unread,
May 26, 2013, 11:07:21 AM5/26/13
to app-inventor-o...@googlegroups.com
I agree with Mark and Jos on concentrating on the general idea of App Inventor and app inventor is designed for.App Inventor  is designed for People with little or no programming experience.So, we must consider skills they have and things they use App Inventor to do.
Gary said to me that most people use ai4a with Windows, so its easy to say that they have skills of Microosft Office.If they have the requirement of storing,retrieving,sorting,filtering table-like data,they naturaly consider using Excel,not a relational database like Access or SQL Server .So I think App Inventor just neet to provide a Table-like component only support storing,retrieving,sorting,filtering data but not  a component with full relational database capabilities.
I also agree with Gary that App Inventor provides a good way to prototype ideas.I noticed that more and more people even put their App build with App Inventor to Google Play.So  more and more functions are required.I hope the Component Development Kit will solve the requirement .
Sorry for my poor English and grammar!

Taifun

unread,
May 26, 2013, 4:19:56 PM5/26/13
to app-inventor-o...@googlegroups.com
Mark said "the primary target audience for App Inventor are people with little or no programming experience.", and I think everybody agrees to that statement.

While presenting my workaround for the SQLite component, I said the following slightly provoking statements ;-) (sorry if this probably resulted in a misunderstanding): "There is no secondary target audience" and "Hossein's SQLite enhancement in its current form will never make it into MIT App Inventor".

Well, I think, there is a secondary target audience, which are the advanced users. And these users could use the component as Hossein suggested very well! For educational purposes the current functionality of App Inventor already could be considered as sufficient. But App Inventor is more than only Hello Purr and learning the first steps, isn't it? What do you think: Is there a secondary target audience?

It is true, that beginners do not need a relational database. They might be happy if they additionally can sort their data (a nice simple enhancement to order lists already would do it, see here) and probably filter it.

We also have to consider the following: starting from mid 2011 - mid 2012 there was the transition phase from Google to MIT. Then from mid 2012 - mid/end 2013 there is another transition phase form App Inventor classic to App Inventor newblocks. Which means, the core development team almost does not have any time for new features since 2 years and there are only a very few people here in the Open Source Development Group who are able to add something. And there are lot's of features waiting to be added.

If there is a large development team and the corresponding budget available, even a more beginner friendly SQLite component could be doable, but not with 1-2 people which develop it in their spare time without budget.

I therefore like to vote for
1. the advanced drawer and the SQLite component in its current form for the secondary target audience and
2. the order list enhancement for the beginners

As Jos said: "In any case, I'd rather have it in advanced than dropping the idea completely."

Taifun
PS: looking forward to the MIT opinion...

James Hancock

unread,
May 27, 2013, 12:24:15 AM5/27/13
to app-inventor-o...@googlegroups.com
I wonder if its feasible to do this in stages ?

1st stage could be to integrate it as is into the advanced section, not too different from an off line version of fusion tables.

2nd stage could be a simpler version, perhaps being synchronous in some fashion with fusion tables (or something similar), a visual method of table construction that simplifies, removes or becomes more forgiving of the syntax for construction and queries but allows more complex queries than tinywebdb/tinydb. No question this would take longer to develop. 

I'm happy to provide some time with tutorials/documentation/use cases etc, if that helps.

Troy Borja

unread,
Jul 10, 2013, 9:10:31 AM7/10/13
to app-inventor-o...@googlegroups.com
Aren't we just making things too complicated? TinyDB is already very usable. TinyDB just needs these 2 methods.

 StartsWith (Text tag) this should a return a List so we can use the built in List capabilities
 Contains (Text tag) this should a return a List so we can use the built in List capabilities

I checked out the code for TinyDB and it uses sharedPreferences.getString. sharedPreferences.getStringSet could be used to retrieve all the stored records with the filters applied.

Right now TinyDB constructor used "TinyDB" as the preference name, this means that all the apps preferences are stored in 1 file. If we allow the constructor to have the filename as a parameter then we can split "tables" into multiple preference files.

I would love to work on this if someone can tell me if I am on the right track.

If the AppEngine Datastore works without SQL and JOINS, then TinyDB should work just fine. 

On Tuesday, April 30, 2013 7:42:03 AM UTC+8, Mark Friedman wrote:
We've been having a spirited discussion about the SQLite component in the #appinventor IRC channel on freenode.net.  It was triggered by this comment of mine:

I notice that there appeared to be general agreement on having a single SQL block in to which the user would type their SQL query.  That doesn't seem consistent with the general idea of App Inventor, which is to replace textual code with blocks.

I would (perhaps unfairly) summarize the replies as:

... but it would be hard to create SQL blocks and in any case can't this just be for advanced users?

Feel free to correct my summary ;-)

I've also attached a transcript of the IRC segment dealing with this, in case you want to see the full discussion.

-Mark

On Sun, Apr 28, 2013 at 7:29 PM, M. Hossein Amerkashi <kkas...@gmail.com> wrote:
See latest version on my AI instance here: http://hosseinamerkashi.appspot.com

You can use my sample app from HERE to experiment with.

-Hossein.

--
You received this message because you are subscribed to the Google Groups "App Inventor Open Source Development" group.
To unsubscribe from this group and stop receiving emails from it, send an email to app-inventor-open-source-dev+unsub...@googlegroups.com.

M. Hossein Amerkashi

unread,
Jul 10, 2013, 9:24:17 AM7/10/13
to app-inventor-o...@googlegroups.com
How would you join tables or just select certain columns from each table without doing multiple calls? How would you sort data? How would you put filter criteria and select specific rows that meet certain condition (e.g. filter based on user role)? There are lots more situation, but you are right and TinyDB would satisfy most AI user's requirement.

-Hossein.


To unsubscribe from this group and stop receiving emails from it, send an email to app-inventor-open-so...@googlegroups.com.

To post to this group, send email to app-inventor-o...@googlegroups.com.

Ryan Bis

unread,
Jul 10, 2013, 11:16:34 AM7/10/13
to app-inventor-o...@googlegroups.com


On Wednesday, July 10, 2013 9:10:31 AM UTC-4, Troy Borja wrote:
Aren't we just making things too complicated? TinyDB is already very usable. TinyDB just needs these 2 methods.

 StartsWith (Text tag) this should a return a List so we can use the built in List capabilities
 Contains (Text tag) this should a return a List so we can use the built in List capabilities

I checked out the code for TinyDB and it uses sharedPreferences.getString. sharedPreferences.getStringSet could be used to retrieve all the stored records with the filters applied.

The getStringSet method is only available in API 11 and above, which won't help the many users with devices that have an older version.

Right now TinyDB constructor used "TinyDB" as the preference name, this means that all the apps preferences are stored in 1 file. If we allow the constructor to have the filename as a parameter then we can split "tables" into multiple preference files.

This is not correct. The TinyDB file gets saved in the application's data directory, so each app stores it's TinyDB data privately per app.
 
I would love to work on this if someone can tell me if I am on the right track.

If the AppEngine Datastore works without SQL and JOINS, then TinyDB should work just fine. 

SQL is definitely not for everyone, but there are definitely cases where you'd want to use a SQL database.  Yes, AppEngine Datastore may run without SQL, but if you look, you'll also see that Google offers a Cloud SQL API. The Datastore, at least from what I can tell, is meant for simple data storage, nothing too complex (although there's nothing stopping you except for storage fees).

- Ryan

Jos Flores

unread,
Jul 10, 2013, 10:08:04 PM7/10/13
to app-inventor-o...@googlegroups.com
Wondering what would be be the gain in having more than 1 file... it
would complicate the blocks, cause you would have to specify which
file to use, but the only advantage I can see is that you could use
the same tag in two files... would that make it more confusing? Am I
missing something?

I agree with Ryan that API 11 is not ideal, but if you can replicate
the functionality for older devices, then it could be good. Also agree
on SQL and key-value stores having its place. In my opinion, the main
problem with tinyDB is not the storage part, but the manipulation of
the information, which is a limitation that you won't find working
with a kv store in a textual language.

If you want to work on anything at all AI related, feel free to use
this forum for questions; we'd be all very happy to help out (well,
shouldn't talk for everyone, but I definitely would!).

cheers,
José
>>>> an email to app-inventor-open-so...@googlegroups.com.
>>>> To post to this group, send email to app-inventor-o...@googlegroups.com.
>>>> Visit this group at
>>>> http://groups.google.com/group/app-inventor-open-source-dev?hl=en.
>>>> For more options, visit https://groups.google.com/groups/opt_out.
>>>>
>>>>
>>>
>>>
> --
> You received this message because you are subscribed to the Google Groups
> "App Inventor Open Source Development" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to app-inventor-open-so...@googlegroups.com.
> To post to this group, send email to
> app-inventor-o...@googlegroups.com.
> Visit this group at
> http://groups.google.com/group/app-inventor-open-source-dev.

Troy Borja

unread,
Jul 11, 2013, 10:19:22 AM7/11/13
to app-inventor-o...@googlegroups.com

On Thursday, July 11, 2013 10:08:04 AM UTC+8, Jos wrote:
Wondering what would be be the gain in having more than 1 file... it
would complicate the blocks, cause you would have to specify which
file to use, but the only advantage I can see is that you could use
the same tag in two files... would that make it more confusing? Am I
missing something?

The problem is with only 1 TinyDB file per app trying to do a getStringSet so that a comparison loop could be done thru all the tags will retrieve all the records. By allowing us to change the name of the TinyDB file we can for example create a 1 file 1 table scenario which would surely reduce the number of records to retrieve.. 
 
I agree with Ryan that API 11 is not ideal, but if you can replicate
the functionality for older devices, then it could be good. Also agree
on SQL and key-value stores having its place. In my opinion, the main
problem with tinyDB is not the storage part, but the manipulation of
the information, which is a limitation that you won't find working
with a kv store in a textual language.

If you want to work on anything at all AI related, feel free to use
this forum for questions; we'd be all very happy to help out (well,
shouldn't talk for everyone, but I definitely would!).


Thanks Jose.
 
>>>> an email to app-inventor-open-source-dev+unsub...@googlegroups.com.
>>>> To post to this group, send email to app-inventor-o...@googlegroups.com.
>>>> Visit this group at
>>>> http://groups.google.com/group/app-inventor-open-source-dev?hl=en.
>>>> For more options, visit https://groups.google.com/groups/opt_out.
>>>>
>>>>
>>>
>>>
> --
> You received this message because you are subscribed to the Google Groups
> "App Inventor Open Source Development" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to app-inventor-open-source-dev+unsub...@googlegroups.com.

rob...@gmail.com

unread,
Aug 2, 2013, 4:08:18 PM8/2/13
to app-inventor-o...@googlegroups.com
-Hossein.

I was wondering how I could add your SQL component to my local app inventor server?
I am using the a4ai personal sever from gary now.
Thanks in advance

M. Hossein Amerkashi

unread,
Aug 2, 2013, 4:11:48 PM8/2/13
to app-inventor-o...@googlegroups.com