[sqlite] Sqlite subqueries

[russ lyttle]

On 02/25/2015 10:31 AM, Rob Richardson wrote:
> A Google search for "USS Yorktown" turned up the following:
> 
> "On September 21, 1997, a division by zero error on board the USS Yorktown 
> (CG-48) Remote Data Base Manager brought down all the machines on the 
> network, causing the ship's propulsion system to fail."
> 
> RobR
> 
> -Original Message-
> 
> 
> To eliminate the need to reference a table would require combining  300 
> tables into one table. A user editing entries for one space could crash the 
> whole system. That's basically what happened aboard the Yorktown in 1997. A 
> cook trying to enter an item into the lunch menu killed the engines on the 
> ship.
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
That's the basic story. The DB people claimed the OS should have
protected against the division by zero, the OS people claimed the DB
should have not let the cook overflow the menu record and cause a
division by 0. The ship had to be towed in, the ship got under weigh in
a couple of hours, etc. My goal is to make sure that nothing like any of
that happens. Or if it does has the least effect possible.

It's beginning to look like I should replace the arduinos with R-Pi
model A running sqlite3. The 'a' db then becomes a data collection and
report generator, each remote has an individual 'b' db. Everything can
be managed by simple sql and Python scripts. The R-Pi has more computing
power than the old towers and are lots cheaper.





-- next part --
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 473 bytes
Desc: OpenPGP digital signature
URL: 

[sqlite] Sqlite subqueries

[russ lyttle]

On 02/25/2015 09:40 AM, Igor Tandetnik wrote:
> On 2/25/2015 9:16 AM, russ lyttle wrote:
>> To eliminate the need to reference a table would require combining  300
>> tables into one table.
> 
> Yes.
> 
>> A user editing entries for one space could crash
>> the whole system.
> 
> I don't see how this follows.
> 
>> That's basically what happened aboard the Yorktown in
>> 1997. A cook trying to enter an item into the lunch menu killed the
>> engines on the ship.
> 
> Did the software store engine configuration and menu in the same table?
> Did the software need to run statements joining engine configuration
> tables with lunch menu tables, thus necessitating putting them into the
> same database? How is scattering essentially the same data across 300
> different tables is expected to help prevent a similar mishap?
> 
>> It's beginning to look like the 'b' table should be broken into a
>> separate db and the 'a' table have indicators as to which table in b.db
>> to use.
> 
> What failure mode do you envision that would be avoided by this design?

There was a lot of finger pointing about the Yorktown incident. The DB
people blaming NT, the Microsoft people blaming the DB. Apparently the
cook tried to enter more items in the supper menu than was allowed and
caused a cascading failure ending in an engine shutdown. A complete
system reboot was required. Whether or not that was due to the DB or OS
doesn't matter now. The goal is to prevent similar events. Or at least
make it obvious where the fault lies.

I was personally involved in one incident where an occupant stuck a soda
can in a damper and caused a system shutdown.

-- next part --
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 473 bytes
Desc: OpenPGP digital signature
URL: 
<http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlite-users/attachments/20150225/699f61a0/attachment.pgp>

[sqlite] Sqlite subqueries

[russ lyttle]

On 02/24/2015 08:53 PM, Igor Tandetnik wrote:
> On 2/24/2015 8:42 PM, russ lyttle wrote:
>> The 'a' table defines spaces to be controlled, the 'b' tables the
>> control schedules and parameters.
>> It would not be unreasonable to assume the 'a' table has >100 rows.
>> Each row in the 'a' table is associated with 3 'b' tables, all the names
>> known in advance and created off line at the same time as the row in the
>> 'a' table.
>> Each 'b' table has up to 1,440 rows.
> 
> Replace these three tables with a single table, holding three times as
> many rows. It would have an extra column holding the "original source"
> indicator - a value that indicates which of the three tables this row
> originated from. Now, in table "a" store this indicator where you
> planned to store the table name.

Thanks for the input.

To eliminate the need to reference a table would require combining  300
tables into one table. A user editing entries for one space could crash
the whole system. That's basically what happened aboard the Yorktown in
1997. A cook trying to enter an item into the lunch menu killed the
engines on the ship.

It's beginning to look like the 'b' table should be broken into a
separate db and the 'a' table have indicators as to which table in b.db
to use.

If there were an sqlite3 sketch for arduino, that would work better than
my original plan. Each space could have its own copy of the tables it is
to use. Lots of database management and communications problems go away.

-- next part --
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 473 bytes
Desc: OpenPGP digital signature
URL: 
<http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlite-users/attachments/20150225/6e08d46f/attachment.pgp>

[sqlite] Sqlite subqueries

[russ lyttle]

Thanks. The application is for an energy conservation application.

The 'a' table defines spaces to be controlled, the 'b' tables the
control schedules and parameters.
It would not be unreasonable to assume the 'a' table has >100 rows.
Each row in the 'a' table is associated with 3 'b' tables, all the names
known in advance and created off line at the same time as the row in the
'a' table.
Each 'b' table has up to 1,440 rows.

The application will loop through the 'a' table, recover a row from the
target 'b' table and execute it on the space described in the 'a' table.
Users can edit (add or delete rows) in the 'b' tables, but nothing else.

There are also 'c' and 'd' tables.
I could do this by composing queries outside sqlite. But that doesn't
seem very elegant and more error prone.

Because the 'a' table looks a lot like the sqlite_master table, I
thought there might be a way do do it all in an sql script.


On 02/24/2015 06:12 PM, R.Smith wrote:
> There's been many discussions on this topic, you can search for it, but
> I will try to recap in short:
> 
> SQL does not work like this, not in SQLite or any other SQL engine may
> an entity construct be referenced by an uncontrolled data value. Of
> course it is easy to get around this in code whereby you can read a
> table name from one DB and use it in an SQL statement for any DB, but
> the onus here is on the maker of such software to implement whatever
> safety checks are needed to prevent corruption or indeed SQL injections
> and other mischief made possible by vulnerabilities exposed in this way.
> 
> What you are trying here is not possible in pure SQL by design.
> 
> There may however be other ways of achieving your goals, maybe
> explaining to us what you would like to do in a system/setup like this
> will help - surely someone here have done some similar thing before and
> they are always glad to assist.
> 
> 
> On 2015-02-24 11:37 PM, russ lyttle wrote:
>> I got the "Using SQLite" book and didn't find the answer there, or in a
>> Google, DuckDuckGo, or Gigiblast search.
>> I'm trying to create a field in a table to hold the name of a second
>> table, then retrieve that name for use.
>> The code below is the simplest of all the things I've tried. Can anyone
>> say what should be done so (10.) returns the same as (8.)?
>> Thanks
>>
>> 1.sqlite> CREATE TABLE a (id INTEGER PRIMARY KEY, name VARCHAR(16),
>> anotherTable TEXT);
>> 2.sqlite> SELECT * FROM sqlite_master;
>>  table|a|a|2|CREATE TABLE a (id INTEGER PRIMARY KEY, name
>> VARCHAR(16), anotherTable TEXT)
>> 3.sqlite> INSERT INTO a (name, anotherTable) VALUES ('table1', 'b');
>> 4.sqlite> SELECT * FROM a;
>>  1|table1|b
>> 5.sqlite> CREATE TABLE b (id INTEGER PRIMARY KEY, name VARCHAR(16),
>> data FLOAT);
>> 6.sqlite> INSERT INTO b (name, data) VALUES ('B1', 35.0);
>> 7.sqlite> INSERT INTO b (name, data) VALUES ('B2', 40.0);
>> 8.sqlite> SELECT * FROM b;
>>  1|B1|35.0
>>  2|B2|40.0
>> 9.sqlite> SELECT anotherTable FROM a WHERE name='table1';
>>  b
>> 10.sqlite> SELECT * FROM (SELECT anotherTable FROM a Where
>> name='table1');
>>  b
>>  sqlite>
>>
>>
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


-- next part --
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 473 bytes
Desc: OpenPGP digital signature
URL: 
<http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlite-users/attachments/20150224/c2381c45/attachment.pgp>

[sqlite] Sqlite subqueries

[russ lyttle]

I got the "Using SQLite" book and didn't find the answer there, or in a
Google, DuckDuckGo, or Gigiblast search.
I'm trying to create a field in a table to hold the name of a second
table, then retrieve that name for use.
The code below is the simplest of all the things I've tried. Can anyone
say what should be done so (10.) returns the same as (8.)?
Thanks

1.sqlite> CREATE TABLE a (id INTEGER PRIMARY KEY, name VARCHAR(16),
anotherTable TEXT);
2.sqlite> SELECT * FROM sqlite_master;
table|a|a|2|CREATE TABLE a (id INTEGER PRIMARY KEY, name
VARCHAR(16), anotherTable TEXT)
3.sqlite> INSERT INTO a (name, anotherTable) VALUES ('table1', 'b');
4.sqlite> SELECT * FROM a;
1|table1|b
5.sqlite> CREATE TABLE b (id INTEGER PRIMARY KEY, name VARCHAR(16),
data FLOAT);
6.sqlite> INSERT INTO b (name, data) VALUES ('B1', 35.0);
7.sqlite> INSERT INTO b (name, data) VALUES ('B2', 40.0);
8.sqlite> SELECT * FROM b;
1|B1|35.0
2|B2|40.0
9.sqlite> SELECT anotherTable FROM a WHERE name='table1';
b
10.sqlite> SELECT * FROM (SELECT anotherTable FROM a Where
name='table1');
b
sqlite>


-- next part --
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 473 bytes
Desc: OpenPGP digital signature
URL: 

[sqlite] Sqlite3 tutorial and reference manuals?

[russ lyttle]

On 02/22/2015 02:08 PM, Eduardo Morras wrote:
> On Sun, 22 Feb 2015 13:10:25 -0500
> russ lyttle  wrote:
> 
>> Contiki-os sounds interesting, especially if it can be adapted to run
>> sqlite3 on an Arduino UNO or MEGA (with attached SD card). Has anyone
>> attempted such a thing?
>>
>> The "natural" language for the Raspberry PI is Python. For Arduino it
>> is C++. I've confirmed that both PERL and Python drop too many
>> messages when using my naif DB with sqlite3, R-PI model A, and Digi
>> xbee s2 radios.
> 
> Sqlite3 under contiki-os works, but (a big BUT) it must be a sqlite3 
> bare-bones -Os compilation, omitting all advanced features (threading, fts, 
> extensions, foreign keys, etc...). You need to create a vfs too.
> 
> ---   ---
> Eduardo Morras 
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 

I've done  file systems on some of the larger TI and Freescale embedded
processors, but not on the AVR processors on the Arduinos. I like the
internet capabilities of the contiki-os.

-- next part --
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 473 bytes
Desc: OpenPGP digital signature
URL: 
<http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlite-users/attachments/20150222/a621e190/attachment.pgp>

[sqlite] Sqlite3 tutorial and reference manuals?

[russ lyttle]

On 02/22/2015 02:06 PM, Roger Binns wrote:
> On 02/22/2015 10:10 AM, russ lyttle wrote:
>> The "natural" language for the Raspberry PI is Python. For Arduino
>> it is C++. I've confirmed that both PERL and Python drop too many
>> messages when using my naif DB with sqlite3, R-PI model A, and Digi
>> xbee s2 radios.
> 
> You didn't say how you structured things, and some details really matter.
> 
> Any kind of database that has durability will have to sync to storage
> at various points, and that storage will be relatively very slow.  For
> SQLite the default is for each statement to be its own transaction,
> unless you explicitly manage transactions.  Consequently each insert
> will block waiting on storage fsync.  There is tweaking you can do to
> mitigate things, but we'd need to know more about what you are doing
> and have done to provide advice.  Not doing anything about this will
> provide dismal performance even on desktops.
> 
> The second part is about dropping messages.  Since you aren't using
> real time operating systems, there will be variability in latency in
> dealing with messages.  The usual solution is to put some sort of
> queue/buffer between message ingestion and message processing to
> smooth over the timing bumps.  It is generally a good idea to also
> split that across multiple threads or processes so that priorities can
> be managed.  Python's GIL is also very relevant.  For the RPi 1 with a
> single core, it isn't going to matter too much, but the RPi 2 gets you
> quad core.
> 
> Roger
> 
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
Here goes. This is pretty long for a mail list.

I have a handful of model A Raspberry pi plus some Arduino UNO, Arduino
FIO, and xbee S2 radios. All were left over from another project.

I also have "Raspberry Pi Home Automation with Arduino", Andrew K.
Dennis, PACKT Publishing. I've built the projects in the book, but they
don't scale well. Neither do the Python Xbee libraries I've found.

My one and only R-Pi 2 was on a UPS truck in a wreck on I-40 during the
ice storm. Big sigh.

My project is as follows (all caps for a potential TABLE):

Top level is a BUILDING which has
EQUIPMENT (hvac units, security, ...),
ROOMS,
ZONES,
LOG_FILES
ROOMS  have
   EQUIPMENT ( tstats, dampers, smoke detectors, etc)

ZONES have
   EQUIPMENT ( ZONE specific items not associated with a ROOM)
   ROOMS,
   NORMAL_SCHEDULE (time of day, setpoint),
   WEEK_END_SCHEDULE,
   HOLIDAY_SCHEDULE,
   LOG_FILES.

Only the schedules and log files may be modified at run time. All other
tables will be read-only.

The application would cycle through each zone performing actions
according to the schedules in effect. It would respond asap to any alarm
in any zone. It should acknowledge user input within 5 sec.
The system cannot drop messages (fire and safety requirement).

Main Controller (xbee Controller) and Zone Controllers  (xbee Router)
are currently R-Pi Model A;
tstats are Arduino UNO ( xbee Router) with pushbutton LCD display;
damper,fan, etc. are Arduino FIO (xbee EndDevice).

Starting point is 10 rooms, 3 zones, 33 end items. Ultimate goal is 21
rooms, 7 zones, ~100 end items.

My first crack at just implementing a bunch of tables and complex
queries didn't come close to meeting time constraints or being maintainable.




-- next part --
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 473 bytes
Desc: OpenPGP digital signature
URL: 
<http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlite-users/attachments/20150222/20fa2dc5/attachment.pgp>

[sqlite] Sqlite3 tutorial and reference manuals?

[russ lyttle]

On 02/22/2015 01:22 PM, Simon Slavin wrote:
> 
> On 22 Feb 2015, at 6:10pm, russ lyttle  wrote:
> 
>> The "natural" language for the Raspberry PI is Python. For Arduino it is
>> C++.
> 
> Arduinos are programmed in 'Wiring' which is descended from 'Processing', 
> which is a very small subset of C++ with a few library functions added.  It's 
> so small that a complete list of all language features, including build-in 
> library calls, fits on two sides of A4.
> 
> Which it may be technically possible to port SQLite to one, almost no 
> Arduinos have any storage with a file structure so it's not worth it unless 
> you yourself need SQLite for your own project.
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 

True. Unless someone has already started such a project, it's not for
me. However, Arduinos are AVR based and can be programmed using AVRDUDE
and gcc/g++ toolchains. It would be nice having a small sqlite subset
that would fit on some of the smaller embedded processors. I could use
such a thing to do queries on read-only tables stored in external flash.


-- next part --
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 473 bytes
Desc: OpenPGP digital signature
URL: 
<http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlite-users/attachments/20150222/0f38c46d/attachment.pgp>

[sqlite] Sqlite3 tutorial and reference manuals?

[russ lyttle]

Contiki-os sounds interesting, especially if it can be adapted to run
sqlite3 on an Arduino UNO or MEGA (with attached SD card). Has anyone
attempted such a thing?

The "natural" language for the Raspberry PI is Python. For Arduino it is
C++. I've confirmed that both PERL and Python drop too many messages
when using my naif DB with sqlite3, R-PI model A, and Digi xbee s2 radios.

On 02/22/2015 05:26 AM, Eduardo Morras wrote:
> On Sat, 21 Feb 2015 23:04:53 -0500
> russ lyttle  wrote:
> 
>> Thanks.
>> I doing building automation using Raspberry Pi, Arduino, and zigbee
>> devices. I started with Python, but that is too slow, so I would like
>> to move to C++. Dropped messages are not acceptable, and response to
>> user actions needs to be "timely".
>>
>> I'll hold futher questions until after looking at the recommendations
>> my by you kind people.
> 
> For zigbee devices I use contiki-os. I suggest you to use c only if your 
> devices are low power. 
> 
> ---   ---
> Eduardo Morras 
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 


-- next part --
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 473 bytes
Desc: OpenPGP digital signature
URL: 
<http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlite-users/attachments/20150222/0fc65907/attachment.pgp>

[sqlite] Sqlite3 tutorial and reference manuals?

[russ lyttle]

Thanks.
I doing building automation using Raspberry Pi, Arduino, and zigbee
devices. I started with Python, but that is too slow, so I would like to
move to C++. Dropped messages are not acceptable, and response to user
actions needs to be "timely".

I'll hold futher questions until after looking at the recommendations my
by you kind people.



On 02/21/2015 01:36 PM, Paolo Bolzoni wrote:
> About SQL I like the w3c schools[1]; about sqlite how do you want to
> use it? Via C interface? If so, check the website "sqlite in 5 minutes
> or less"[2].
> 
> [1]http://www.w3schools.com/sql/
> [2]https://www.sqlite.org/quickstart.html
> 
> On Sat, Feb 21, 2015 at 7:33 PM, russ lyttle  wrote:
>> I'm new to sqlite and not that experienced with SQL in general. Are
>> there any good sqlite tutorials or references better than the first few
>> pages returned by a google search?
>>
>> I have reference and tutorial books for MySql.
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 


-- next part --
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 473 bytes
Desc: OpenPGP digital signature
URL: 
<http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlite-users/attachments/20150221/752f584b/attachment.pgp>

[sqlite] Sqlite3 tutorial and reference manuals?

[russ lyttle]

Thanks for the recommendation. Sorry about the multiple postings, but
thunderbird was telling me it couldn't send the messages. I think I've
got it fixed now.

On 02/21/2015 01:48 PM, Conery John wrote:
> I really like Using SQLite, by Jay Kreibich (O?Reilly and Associates).  
> Comprehensive coverage of SQLite plus useful information on database design.
> 
> John Conery
> 
>> On Feb 21, 2015, at 10:36 AM, Paolo Bolzoni > gmail.com> wrote:
>>
>> About SQL I like the w3c schools[1]; about sqlite how do you want to
>> use it? Via C interface? If so, check the website "sqlite in 5 minutes
>> or less"[2].
>>
>> [1]http://www.w3schools.com/sql/
>> [2]https://www.sqlite.org/quickstart.html
>>
>> On Sat, Feb 21, 2015 at 7:33 PM, russ lyttle  wrote:
>>> I'm new to sqlite and not that experienced with SQL in general. Are
>>> there any good sqlite tutorials or references better than the first few
>>> pages returned by a google search?
>>>
>>> I have reference and tutorial books for MySql.
>>>
>>>
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users at mailinglists.sqlite.org
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 


-- next part --
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 473 bytes
Desc: OpenPGP digital signature
URL: 
<http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlite-users/attachments/20150221/5963113a/attachment.pgp>

[sqlite] Reference material for SQLITE3

[russ lyttle]

I'm new to sqlite and would like some intermediate to advanced tutorial
and reference material. Google didn't return much useful beyond
elementary level.

-- next part --
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 473 bytes
Desc: OpenPGP digital signature
URL: 

[sqlite] sqlite tutorials and reference manuals?

[russ lyttle]

I'm new to sqlite. Can anyone recommend online or hardcopy tutorials and
reference manuals for sqlite?
Google search hasn't returned much useful.

-- next part --
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 473 bytes
Desc: OpenPGP digital signature
URL: 

[sqlite] Sqlite3 tutorial and reference manuals?

[russ lyttle]

I'm new to sqlite and not that experienced with SQL in general. Are
there any good sqlite tutorials or references better than the first few
pages returned by a google search?

I have reference and tutorial books for MySql.

-- next part --
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 473 bytes
Desc: OpenPGP digital signature
URL: