As I was discussing this last tip using a simile loop in SQLite to populate data entry http://blogmymix.blogspot.com/2017/06/sqlite-using-table-to-loop.html

A comment was made that the tRange table was not absolutely necessary
as the following command would suffice:

insert into tTarget(i) select ('My test without tRange: ' || tIndex.id) from tIndex where tIndex.id <=10000 ;

or with proper formatting

insert into tTarget(i)
select ('My test without tRange: ' || tIndex.id)
from tIndex where tIndex.id <=10000 ;

No comments: Labels: database, loop, populating data, sqlite, tips

Loop

You want to create a table with lots of records for testing purposes - in SQL you could make a loop using something like this:

while (@i <= 10000)
begin
insert into table1 values ( 'field1' + cast (@i as char).
'field2'+ cast (@i as char).
...
)
set @i+=1
end
go

Unfortunately in SQlite loops are not implemented - but you could use this trick instead
It will use a tIndex table, and a tRange table to generate some records in a tTarget table.

The tTarget table is for the result:
create table target (i text);

This table will receive the result as text

tRange table

create table tRange (start, finish);
insert into tRange (1, 100) ;

And then afterward you can change the start and finish parameters using:
update tRange set start = 3 ;
update tRange set finish = 90 ;

tIndex table

This one is tricky because it has to contain as many records as possible - you will have to create this table via a Java program (see previous post) - by default there will be 10,000 records.

Now the Loop trick

insert into tTarget(i)
select ('Record number '||tIndex.id)
from tIndex join tRange
on (tIndex.id >= tRange.start and tIndex.id <= tRange.finish) ;

This will generate Record Number + start ... up to Record Number+ finish

No comments: Labels: database, insert, join, loop, records, sqlite, tables, testing, tricks

No comments: Labels: database, development, java, sqlite, tricks

Random record

If many occasion developer want to get a random record(s) from a table, because they are programming a quizz, or a game - it turns out there is no native instructions for that in SQLite
but you can use this workaround:

SELECT
*, random() as R
FROM Table
ORDER BY R
LIMIT 1 ;

Why it works

It insert a random number (R) for each record and then using the order by there is going to be a selection of the smaller number (R) - you can even change the LIMIT to get more than one record - let say 3

SELECT *, random() as R FROM Table ORDER BY R LIMIT 3 ;

No comments: Labels: database, limit, random, records, sqlite, tips

The idea

As a good tip to get the most of the table entries - you want the user to focus on their important data - but you don't want to overwhelm them with extra typing that can be done automatically by database system.

Here is way to have an auto increment index and a default date and time of the entry done by the operator

Embedded during the table creation:

sqlite> create table testauto (autoid integer primary key autoincrement, autodate default (datetime('now')), notes text ) ;

Operator just have to enter the notes column:

sqlite> insert into testauto (notes) values ('this is a test of the auto increment and default date' ) ;
sqlite> insert into testauto (notes) values ('this is a second test of the auto increment and default date' ) ;
sqlite> insert into testauto (notes) values ('this is a third test of the auto increment and default date' ) ;

and as you see the index and the date are automatically filled

sqlite> select * from testauto ;
1|2017年03月15日 15:34:05|this is a test of the auto increment and default date
2|2017年03月15日 15:34:14|this is a second test of the auto increment and default date
3|2017年03月15日 15:34:24|this is a third test of the auto increment and default date
sqlite>

No comments: Labels: autoincrement, database, datetime, now, sqlite, tips

Random()

The Sqlite random() function will returns a pseudo-random integer between -9223372036854775808 and +9223372036854775807.

No comments: Labels: database, random, sqlite, tips

Assuming a table like this:

create table test (id integer primary key autoincrement, name text);

and values like:

insert into test values (null, "test") ;
insert into test values (null, "word1 word2 word3") ;
insert into test values (null, "word4 word5 word6") ;
insert into test values (null, "word7 word8 word9 word10 word11 word11") ;

here is the result:

select id, name from test

1;test
2;word1 word2 word3
3;word4 word5 word6
4;word7 word8 word9 word10 word11 word12

Counting

Now you want to count the word(s) in the field "name" - here is how to proceed:
select (length(name)-length(replace(name," ","")))+1 as wordcount, name from test

it works because the query is looking for the number of space - a space means a new word is in the field and that is why you need to add 1 to the total.

Sqlite is awesome

2 comments: Labels: database, search, sqlite, tips

Here is a little example of how brilliant Sqlite is :

sqlite> .schema country
CREATE TABLE country(id integer primary key autoincrement, name text);
sqlite> .schema search
CREATE TABLE search(term text);
sqlite> select * from country ;
id|name
1|France
2|Czech Republic
3|Italy
4|Great Britain
5|Ireland
6|Slovakia
7|Senegal
sqlite> select * from search ;
term
%fr%
%cz%
%ir%
ai
sqlite> select a.name from country a join search b on a.name like "%"||b.term||"%" ;
name
France
Czech Republic
Great Britain
Ireland

NB : the || is used for Sqlite for concatenation

No comments: Labels: blog, concatenation, database, search, sqlite, tips