Re: [sqlite] Indirect Referencing
On Tue, Nov 01, 2011 at 02:01:30PM +, Black, Michael (IS) scratched on the wall: > Hmmm...how hard would it be allow one to bind the table name with prepare? Generally, "very." Or, rather, it would be fairly straight forward if you're willing to skip the query optimization step and do all look-ups as linear table scans and all joins as nested loops. Much of the query construction, including the use of indexes, join optimizations, constraints, keys, and everything else is based off knowing which specific tables and columns are being accessed. You'd also need to give up most of the query sanity checking, since you wouldn't be able to do things like verify that a named column actually exists in the unspecified table. If you were to do all that work after "binding" the identifier names, you'd more or less be re-preparing the whole statement. So you might as well just re-prepare the statement. Besides, getting back to some comments from before, if you've got a whole series of tables with the same structure, so that you can take advantage of this kind of thing, you likely need to rethink your database design. Yes, it would be a minor convenience from time to time, but generally reuse comes from very simple statements that are easy to simply rebuild. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Indirect Referencing
Though you probably COULD merge them all together there are situations where you don't want to. Just like his original question. Imagine you have a bunch of tables of consumer products split by type. Yes, you could stick it all in one humogous database...but... The split allows you to reduce contention so that doing an update doesn't stop everybody and even reads will be faster since they are split. The bind allows you to use just one routine with a pre-prepared statement for speed instead of building your own sql string and having to prepare it each time. Seems like quite an upside to it to me unless I'm missing something (which is quite possible :-) Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Simon Slavin [slav...@bigfraud.org] Sent: Tuesday, November 01, 2011 9:10 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Indirect Referencing On 1 Nov 2011, at 2:01pm, Black, Michael (IS) wrote: > Hmmm...how hard would it be allow one to bind the table name with prepare? If you are in a position to bind the table name, does that not mean you could just merge all the tables together ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Indirect Referencing
Boy Howdy ! I've wished for that capability just about every time I've had to generate Dynamic SQL Statements to execute a common query against varying Table Names :) -- kjh Black, Michael (IS) wrote, On 11/01/2011 09:01 AM: Hmmm...how hard would it be allow one to bind the table name with prepare? Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Konrad J Hambrick [kon...@payplus.com] Sent: Tuesday, November 01, 2011 8:44 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Indirect Referencing Bertus -- The only way to reference a Variable Table Name would be to create a Dynamic SQL Statement at runtime, prep the Dynamic Statement then exec. This would be true for all implementations of SQL that I am aware of ... -- kjh Stander, Bertus (Pretoria) wrote, On 11/01/2011 05:25 AM: Good day to you all, I am very inexperienced with SQlite and any help will be appreciated. I want to know if I can use indirect referencing within the SQL language as per SQlite standards. I will give an example. If I create a table as illustrated below. CREATE TABLE Testing ( ID INTEGER PRIMARY KEY AUTOINCREMENT, Tbl_Name VARCHAR (45)); The value of 'Tbl_Name' is set to 'My_Table' Will it be possible to read the values in table My_Table using the field 'Tbl_Name'? Select * from Testing.Tbl_Name; The tests I have performed is not working. Any suggestions or work around please? This email has been scanned for viruses and malware, and automatically archived by Mimecast SA (Pty) Ltd, an innovator in Software as a Service (SaaS) for business. Mimecast Unified Email Management UEM) offers email continuity, security, archiving and compliance with all current legislation. To find out more,contact Mimecast. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Indirect Referencing
On 1 Nov 2011, at 2:01pm, Black, Michael (IS) wrote: > Hmmm...how hard would it be allow one to bind the table name with prepare? If you are in a position to bind the table name, does that not mean you could just merge all the tables together ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Indirect Referencing
Hmmm...how hard would it be allow one to bind the table name with prepare? Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Konrad J Hambrick [kon...@payplus.com] Sent: Tuesday, November 01, 2011 8:44 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Indirect Referencing Bertus -- The only way to reference a Variable Table Name would be to create a Dynamic SQL Statement at runtime, prep the Dynamic Statement then exec. This would be true for all implementations of SQL that I am aware of ... -- kjh Stander, Bertus (Pretoria) wrote, On 11/01/2011 05:25 AM: > Good day to you all, > > I am very inexperienced with SQlite and any help will be appreciated. > > I want to know if I can use indirect referencing within the SQL language > as per SQlite standards. I will give an example. > > If I create a table as illustrated below. > > CREATE TABLE Testing ( > >ID INTEGER PRIMARY KEY AUTOINCREMENT, > >Tbl_Name VARCHAR (45)); > > The value of 'Tbl_Name' is set to 'My_Table' > > Will it be possible to read the values in table My_Table using the field > 'Tbl_Name'? > > Select * from Testing.Tbl_Name; > > The tests I have performed is not working. > > Any suggestions or work around please? > > > > > This email has been scanned for viruses and malware, and automatically > archived by Mimecast SA (Pty) Ltd, an innovator in Software as a Service > (SaaS) for business. Mimecast Unified Email Management > UEM) offers email continuity, security, archiving and compliance with all > current legislation. To find out more,contact Mimecast. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Indirect Referencing
Bertus -- The only way to reference a Variable Table Name would be to create a Dynamic SQL Statement at runtime, prep the Dynamic Statement then exec. This would be true for all implementations of SQL that I am aware of ... -- kjh Stander, Bertus (Pretoria) wrote, On 11/01/2011 05:25 AM: Good day to you all, I am very inexperienced with SQlite and any help will be appreciated. I want to know if I can use indirect referencing within the SQL language as per SQlite standards. I will give an example. If I create a table as illustrated below. CREATE TABLE Testing ( ID INTEGER PRIMARY KEY AUTOINCREMENT, Tbl_Name VARCHAR (45)); The value of 'Tbl_Name' is set to 'My_Table' Will it be possible to read the values in table My_Table using the field 'Tbl_Name'? Select * from Testing.Tbl_Name; The tests I have performed is not working. Any suggestions or work around please? This email has been scanned for viruses and malware, and automatically archived by Mimecast SA (Pty) Ltd, an innovator in Software as a Service (SaaS) for business. Mimecast Unified Email Management UEM) offers email continuity, security, archiving and compliance with all current legislation. To find out more,contact Mimecast. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Indirect Referencing
Thanks Simon, The response I am giving here is to assist others in deciding whether they can use SQlite. I understand Triggers reasonably well as I am using it continuously very successfully. The 1200 tables in the SQlite database has no impact on performance as every transaction executed on a small device like the Trimble is sub-second. The storage space is also no problem as the database is stored on SSD. I do understand the terms "table" and "row" etc quite well, but must admit I need more good references to sites for valuable and RELIABLE information and EXAMPLES of MORE COMPLEX SQL. Please exclude the www.SQlite.org as I am aware of that site. Hopefully I will still receive more comments and suggestions on the possibilities of "Indirect Referencing" within SQlite. Bertus > That is one aspect. I also want to use minimal triggers to identify > certain abnormalities and perform certain processes. This I want to keep > Central and not writing triggers for all 1200 tables. So key to a > solution I was thinking of is to store table names and field names in a > central table and then reference those tables and those fields > identified as problem areas during the capture process. You will not be able to get SQLite to run triggers stored in tables automatically. And any database which includes 1200 tables will probably work slowly and take up a lot of storage space. I'm not sure you properly understand the SQL terms 'table' and 'row'. It may be that the best thing you can do now is read a beginner's book about SQL just so you understand how it works. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users This email has been scanned for viruses and malware, and automatically archived by Mimecast SA (Pty) Ltd, an innovator in Software as a Service (SaaS) for business. Mimecast Unified Email Management UEM) offers email continuity, security, archiving and compliance with all current legislation. To find out more,contact Mimecast. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Indirect Referencing
On 1 Nov 2011, at 11:44am, Stander, Bertus (Pretoria) wrote: > That is one aspect. I also want to use minimal triggers to identify > certain abnormalities and perform certain processes. This I want to keep > Central and not writing triggers for all 1200 tables. So key to a > solution I was thinking of is to store table names and field names in a > central table and then reference those tables and those fields > identified as problem areas during the capture process. You will not be able to get SQLite to run triggers stored in tables automatically. And any database which includes 1200 tables will probably work slowly and take up a lot of storage space. I'm not sure you properly understand the SQL terms 'table' and 'row'. It may be that the best thing you can do now is read a beginner's book about SQL just so you understand how it works. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Indirect Referencing
Thanks for the response Simon.
I am working with field loggers where the field workers are capturing
different assets using a Trimble mobile device.
I have more than 1200 individual tables, each table holding the values
of a unique asset.
Living in South Africa where bandwidth is a myth in some areas and lots
of verifications must happen before we pump the data unnecessarily to a
central point I would like to read only those asset data tables, that
were touched and not all 1200 individual tables just to find some empty
tables. This will assist me in verifying some basic data abnormalities
which could be rectified before data is transferred and then deleted.
That is one aspect. I also want to use minimal triggers to identify
certain abnormalities and perform certain processes. This I want to keep
Central and not writing triggers for all 1200 tables. So key to a
solution I was thinking of is to store table names and field names in a
central table and then reference those tables and those fields
identified as problem areas during the capture process.
I have inherit this system and trying my best to rewrite it as it is not
a normalised system. But as usual business continue and no one is
waiting for the IT guys and there is no end in sight for enhancement
requests! Does it sound familiar?
Bertus
On 1 Nov 2011, at 10:25am, Stander, Bertus (Pretoria) wrote:
> If I create a table as illustrated below.
>
> CREATE TABLE Testing (
>
> ID INTEGER PRIMARY KEY AUTOINCREMENT,
>
> Tbl_Name VARCHAR (45));
In this example you have a TABLE called 'Testing' and a COLUMN called
'Tbl_Name'.
> The value of 'Tbl_Name' is set to 'My_Table'
>
> Will it be possible to read the values in table My_Table using the
field
> 'Tbl_Name'?
>
> Select * from Testing.Tbl_Name;
>
> The tests I have performed is not working.
Good commands for your above example would be
INSERT INTO Testing (Tbl_Name) VALUES ('My_Table');
SELECT * FROM Testing;
SELECT ID,Tbl_Name FROM Testing;
SELECT ID FROM Testing WHERE Tbl_Name = 'My_Table';
However, the names you have picked are a little strange unless you are
storing information about tables. It would be more normal to see
CREATE TABLE My_Table (
ID INTEGER PRIMARY KEY AUTOINCREMENT,
My_Column TEXT);
INSERT INTO My_Table (My_Column) VALUES ('example row');
SELECT * FROM My_Table;
SELECT ID, My_Column FROM My_Table;
SELECT ID FROM My_Table WHERE My_Column = 'example row';
Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
This email has been scanned for viruses and malware, and automatically archived
by Mimecast SA (Pty) Ltd, an innovator in Software as a Service (SaaS) for
business. Mimecast Unified Email Management
UEM) offers email continuity, security, archiving and compliance with all
current legislation. To find out more,contact Mimecast.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Indirect Referencing
On 1 Nov 2011, at 10:25am, Stander, Bertus (Pretoria) wrote:
> If I create a table as illustrated below.
>
> CREATE TABLE Testing (
>
> ID INTEGER PRIMARY KEY AUTOINCREMENT,
>
> Tbl_Name VARCHAR (45));
In this example you have a TABLE called 'Testing' and a COLUMN called
'Tbl_Name'.
> The value of 'Tbl_Name' is set to 'My_Table'
>
> Will it be possible to read the values in table My_Table using the field
> 'Tbl_Name'?
>
> Select * from Testing.Tbl_Name;
>
> The tests I have performed is not working.
Good commands for your above example would be
INSERT INTO Testing (Tbl_Name) VALUES ('My_Table');
SELECT * FROM Testing;
SELECT ID,Tbl_Name FROM Testing;
SELECT ID FROM Testing WHERE Tbl_Name = 'My_Table';
However, the names you have picked are a little strange unless you are storing
information about tables. It would be more normal to see
CREATE TABLE My_Table (
ID INTEGER PRIMARY KEY AUTOINCREMENT,
My_Column TEXT);
INSERT INTO My_Table (My_Column) VALUES ('example row');
SELECT * FROM My_Table;
SELECT ID, My_Column FROM My_Table;
SELECT ID FROM My_Table WHERE My_Column = 'example row';
Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
