[sqlite] Sqlite subqueries

[Rob Richardson]

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] 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: 

[sqlite] Sqlite subqueries

[Igor Tandetnik]

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?
-- 
Igor Tandetnik

[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: 

[sqlite] Sqlite subqueries

[R.Smith]

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] Sqlite subqueries

[Paul Sanderson]

Search the mail list for "Column name as a variable" for a similar discussion
Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence


On 24 February 2015 at 21:58, Igor Tandetnik  wrote:
> On 2/24/2015 4:37 PM, russ lyttle wrote:
>>
>> I'm trying to create a field in a table to hold the name of a second
>> table, then retrieve that name for use.
>
>
> You can't. SQL doesn't work this way. Reconsider your design.
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Sqlite subqueries

[Igor Tandetnik]

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.
-- 
Igor Tandetnik

[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: 

[sqlite] Sqlite subqueries

[Igor Tandetnik]

On 2/24/2015 4:37 PM, russ lyttle wrote:
> I'm trying to create a field in a table to hold the name of a second
> table, then retrieve that name for use.

You can't. SQL doesn't work this way. Reconsider your design.
-- 
Igor Tandetnik

[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: