Re: [sqlite] Possible in one SQL statement?

[Simon Slavin]

On 5 Mar 2011, at 8:40pm, Kai Peters wrote:

> Is it possible to obtain select result set containing the table names of all 
> user tables in a 
> database

Take a look at the results of

SELECT * FROM sqlite_master

and work out your own way of listing the tables.

> along with their record counts?

Not directly, the fastest way is to do for each table

SELECT count(*) FROM tableName

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Possible in one SQL statement?

[Kai Peters]

Is it possible to obtain select result set containing the table names of all 
user tables in a 
database along with their record counts?

TIA,
Kai


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using indexed fields in a table.

[Eric Smith]

On Sat, Mar 5, 2011 at 8:14 AM, BareFeetWare wrote:

> On 05/03/2011, at 1:59 AM, "J Trahair" 
> wrote:
>
> > I understand about Primary keys and Unique keys attributed to their own
> field. Is there a need to have other fields indexed, for faster searching?
> Eg. a table containing your favourite music. Say you have 9 Beethoven
> symphonies (one row in the table for each), 14 Bach partitas and 100 Haydn
> symphonies, and you want to select the Bach ones.
> > You have a table called AllMusic containing columns called Composer,
> NameOfPiece, YearComposed, etc.
> >
> > SELECT * FROM AllMusic_tbl WHERE Composer = 'Bach' ORDER BY YearComposed
>
>
Watch this talk by an author of SQLite for a great explanation in his own
words:

http://www.youtube.com/watch?v=Z_cX3bzkExE

Eric

--
Eric A. Smith

There is no likelihood man can ever tap the power of the atom.
-- Robert Millikan, Nobel Prize in Physics, 1923
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Adjusting strategy for ROWIDs

[Simon Slavin]

On 5 Mar 2011, at 11:21am, Enrico Thierbach wrote:

> using a different field would break associations between tables. For example:
> 
> Assume we have tables a and b, and a join table as_to_bs. The a and b table 
> both have a uniqueKey column, as you suggest, that will be set by a trigger.
> If two database applications create a and b objects that are joined (e.g.
> 
>   a_rowid = INSERT INTO a VALUES("a")  
>   b_rowid = INSERT INTO b VALUES("b") 
>   INSERT INTO as_to_bs (a_id, b_id)

Then none of these things should be relating to the id field, they should 
instead be relating to this new key field  you've made up:

INSERT INTO as_to_bs (a_mykey, b_mykey)

You're entering the realms of database synchronisation which is a whole kettle 
of worms you really don't want to get into.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using indexed fields in a table.

[BareFeetWare]

On 05/03/2011, at 1:59 AM, "J Trahair"  wrote:

> I understand about Primary keys and Unique keys attributed to their own 
> field. Is there a need to have other fields indexed, for faster searching? 
> Eg. a table containing your favourite music. Say you have 9 Beethoven 
> symphonies (one row in the table for each), 14 Bach partitas and 100 Haydn 
> symphonies, and you want to select the Bach ones.
> You have a table called AllMusic containing columns called Composer, 
> NameOfPiece, YearComposed, etc.
> 
> SELECT * FROM AllMusic_tbl WHERE Composer = 'Bach' ORDER BY YearComposed

In addition to Simon's fine answer, you should also consider properly 
normalizing your database. In essence, this means removing redundancy and 
replacing any repeated values with references to discrete rows in their own 
table. This also makes indexes automatic, since selects use the primary key 
(which is automatically indexed) of each table. In short, you use less space, 
get faster queries, consistent data and can more efficiently changing of data 
(ie one entry instead of many).

Something like this:

begin immediate
;
create table Music
(   ID integer
primary key
not null
,   Name text
unique
not null
collate nocase
,   Composer integer
references Composer (ID)
,   "Year Composed" integer
)
;
create table Composer
(   ID integer
primary key
not null
,   Name text
unique
not null
collate nocase
)
;
commit
;

You don't need any extra indexes. Your select now looks like:

select
Music.Name
,   "Year Composed"
from Music join Composer on Music.Composer = Composer.ID
where Composer.Name = 'Bach'
order by "Year Composed"
;

Tom
BareFeetWare

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Adjusting strategy for ROWIDs

[Black, Michael (IS)]

I wasn't aware SQLite's PRNG was not like most others.  Good to know.
I guess one could insert their own random() function if you need repeatability 
(which is actually a major point of most random number generators).  I don't 
see the seeding exposed in SQLite so you can restart from a given point.

This makes it impossible to generate test cases using this that can be 
repeated, at least using SQLite's functions.

As for Enrico's problem...could you explain a bit more about what you mean by 
synchronizing your databases?  Row ids are considered to be an internal number 
so why do they matter across databases?  It would seem that you shouldn't need 
it at all.  How/why are they synchronizing and why prevent rowid collisions 
rather than data collisions?  

Here's another way to do what you want.

On your first insert into your database just update the rowid using random.  
Then each subsequent insert will be 1-up from that.
So, in your trigger try this:

SQLite version 3.7.5
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table t(a int);
sqlite> create trigger tr after insert on t
   ...> when (select count(a) from t) = 1
   ...> begin
   ...>   update t set rowid=random() where rowid=1;
   ...> end;
sqlite> insert into t values(1);
sqlite> insert into t values(2);
sqlite> select rowid,a from t;
4193218855921046132|1
4193218855921046133|2

The odds of the starting value being within +/- 1000 of a given 64-bit random 
number is 2000*(1/2^64 ) or about 1e-16



Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Enrico Thierbach [e...@open-lab.org]
Sent: Friday, March 04, 2011 7:18 PM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Adjusting strategy for ROWIDs

On 04.03.2011, at 22:21, Drake Wilson wrote:

> Quoth Enrico Thierbach , on 2011-03-04 22:11:07 +0100:
>> If I insert a record into a table with a primary key column id,
>> Sqlite assigns a ROWID as outlined here
>> http://www.sqlite.org/autoinc.html. However, I would like to assign
>> a totally random rowid.
>
> Why do you want to do this?  In particular, why would it not work to
> randomize the values from the application side during inserts?
>
> Random numbers collide faster than you might expect if you're not
> familiar with the birthday paradox; normally, truly-random values that
> are expected to have no collisions are generated using entropy from
> the environment and are at least 128 bits long.  I suspect this is not
> what you want.  If you just want them to be "random-looking" then it
> may be more convenient to relate the underlying ID and the exterior ID
> through a suitable permutation of the 64-bit integer space.  If
> neither of those is true, you're probably looking at probing several
> times to avoid collisions, and that's not something the stock "pick a
> new row ID" mechanism handles AFAIK.
>

Hi drake,

thanks for your answer.

I am trying to build a solution where two or more databases can synchronize 
with each other. As in my problem area
the databases are pretty small (probably less than a 1000 rows), the collision 
probability given roughly 64 bit
of randomness would be about 1000 / 2^32. This is less than 10^-6, which is 
good enough for me. However,
I am aware that there are better ways to generate IDs that are guaranteed to be 
different in different databases.

I do have a working solution for synching my databases (with randomly generated 
ROWIDs). The problem I face
is that I cannot get the rowid of a newly created record to pass thru back into 
the application using last_insert_row_id.

To explicitely generate an ID in the application, however, is not a suitable 
solution for me. This must work out of the box
(i.e. on a database) with any application that uses this database. Therefore I 
am somewhat limited to what SQLite can
give me via SQL (or probably by extending SQLite via its C interface).

/eno

___
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] Adjusting strategy for ROWIDs

[Enrico Thierbach]

Hi list,

I should have omitted the word "random" from the original post :) What was 
answered (but not specifically asked) is

- sqlite has a pretty good PRNG.

- yes, there is the birthday paradoxon. BTW, the calculation I wrote down in 
the other post regarding the birthday paradox was wrong. The probability of a 
conflict between two unique sets of 1000 numbers from a 64-bit-space is not 
1000 * 2**-32, but about 1000 * 1000 * 2**-64, i.e. roughly 2e-14. (As a 
comparison: the probability to have all numbers right in a 6 of 49 lottery is 
~10 times that). To reach a probability for a conflict each set would need 
to have 2**32 numbers.  

But these were not the original questions:

> Does anyone have an idea, whether or not it is possible either
> 
> a) to adjust the id in a trigger and have the last_insert_row_id return the 
> adjusted value, or 
> b) to adjust the strategy how sqlite generates row ids for newly generated 
> records.
> 
> Any hint is greatly appreciated.

So can we please come back to that?

I will give this approach a try: I preset the sqlite_sequence entries for each 
autoincrement table on each database instance to some very large number. This 
should give each database a distinct "namespace". I see how far I can go with 
that.

thanks,
/eno

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Adjusting strategy for ROWIDs

[Enrico Thierbach]

On 05.03.2011, at 02:21, Simon Slavin wrote:

> 
> On 5 Mar 2011, at 1:18am, Enrico Thierbach wrote:
> 
>> I do have a working solution for synching my databases (with randomly 
>> generated ROWIDs). The problem I face
>> is that I cannot get the rowid of a newly created record to pass thru back 
>> into the application using last_insert_row_id.
> 
> I think you have no real reason to use rowid at all here.  Make a different 
> field called 'uniqueKey' or something, and use that for your random numbers.



Hi simon, hi list,

using a different field would break associations between tables. For example:

Assume we have tables a and b, and a join table as_to_bs. The a and b table 
both have a uniqueKey column, as you suggest, that will be set by a trigger.
If two database applications create a and b objects that are joined (e.g.

a_rowid = INSERT INTO a VALUES("a")  
b_rowid = INSERT INTO b VALUES("b") 
INSERT INTO as_to_bs (a_id, b_id)

both databases would end up with (1, 1) entries in the as_to_bs table. Now I 
could merge the a and b tables quite fine. In this process, however, the 
entries in the a and b tables would be assigned new rowids. If I would then 
merge the as_to_bs tables the association between the newly merged objects in A 
and B would be lost. 

Remember: as the application should not know about this replication thingy it 
would still use the id or rowid to refer its object and not a uniqueKey column 
or something. 

So, the question remains: how can I either adjust the way rowids are 
automatically generated or adjust each specific rowid when a row is inserted 
and have this one returned in the last_row_id.

/eno
 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users