Re: [sqlite] Sparse matrix

[T]

Hi Darren,


It seems to me that you have a flawed design.


Displaying sparse like that should be a function of your  
application display code, not the database


I had to chuckle that when I asked "How do I use this to do that",  
your solution was "you shouldn't have that and you should do it with  
something other than this" ;-) Not really helpful, but good for a  
chuckle.


Fortunately Simon's replies helped me out.

Hopefully my followup posting gives a better example.

Tom


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Sparse matrix

[T]

Hi Simon,

Here's a less gruesome version - no cases. I've given no thought to  
performance comparisons.


Thanks for the two great solutions you posted. They certainly achieve  
the desired result with the simplified sample I gave. However, in the  
broader reality, it doesn't quite satisfy my situation since the  
rowids aren't necessarily in the order that they appear in the final  
view.


Here's a broader example.

Say I have a timetable like this:

rowid   Day   Period   TeacherRoomSubject
1   Tuesday   1Peters  A2 Maths
2   Monday2Peters  A2 Maths
3   Monday1Ng  A1 English
4   Monday3Peters  A2 Computing
5   Tuesday   3Ng  A1 English
6   Tuesday   2Ng  A1 History
7   Wednesday 4Smith   S2 Science
8   Wednesday 3Who S2 Science
9   Monday4KentH1 Sport
10  Wednesday 2KentH1 Sport
11  Tuesday   4Ng  A1 History
12  Wednesday 1Peters  A2 Maths

And I want to output something like this:

Day   Room SubjectTeacherPeriod
MondayA1   EnglishNg 1
  A2   Maths  Peters 2
   Computing 3
  H1   Sport  Kent   4
Tuesday   A2   Maths  Peters 1
  A1   HistoryNg 2
   English   3
   History   4
Wednesday A2   Maths  Peters 1
  H1   Sport  Kent   2
  S1   ScienceWho3
  Smith  4

But with counts of repetitions, like this:

Day   Room Subject  TeacherPeriod
MondayA1 1 English   1  Ng 1 1
  A2 2 Maths 1  Peters 2 2
   Computing 1   3
  H1 1 Sport 1  Kent   1 4
Tuesday   A2 1 Maths 1  Peters 1 1
  A1 3 History   1  Ng 3 2
   English   1   3
   History   1   4
Wednesday A2 1 Maths 1  Peters 1 1
  H1 1 Sport 1  Kent   1 2
  S1 2 Science   2  Who1 3
Smith  1 4

The "1" counts could even appear as null/blank, that would be fine.

The SQL for my sample Timetable above is:

create table Timetable
(
Day text,
Period integer,
Teacher text,
Room text,
Subject text
)
;
insert into table values('Tuesday',1,'Peters','A2','Maths');
insert into table values('Monday',2,'Peters','A2','Maths');
insert into table values('Monday',1,'Ng','A1','English');
insert into table values('Monday',3,'Peters','A2','Computing');
insert into table values('Tuesday',3,'Ng','A1','English');
insert into table values('Tuesday',2,'Ng','A1','History');
insert into table values('Wednesday',4,'Smith','S2','Science');
insert into table values('Wednesday',3,'Who','S2','Science');
insert into table values('Monday',4,'Kent','H1','Sport');
insert into table values('Wednesday',2,'Kent','H1','Sport');
insert into table values('Tuesday',4,'Ng','A1','History');
insert into table values('Wednesday',1,'Peters','A2','Maths');

and a view that sorts the data is:

create view "Timetable Sorted"
as
select rowid, Day, Subject, Room, Teacher, Period
from Timetable
order by Day, Period
;

Thanks for any further insight you may have,
Tom


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] _ROWID_ internal column, versus explicit INTEGER PRIMARY KEY with VACUUM

[Andrew Finkenstadt]

I realize that FTS1/2 has this slight flaw with the text indexes recording
the _rowid_ of a table, in the expectation that a rowid was permanent.  That
would have caught me unawares, as in Oracle a ROWID is permanent... even if
the row has migrated, there's a migrate record at the place where the row
used to be.

Does the same renumbering during VACUUM (auto or otherwise) apply to the
explicitly identified integer primary key column when it is named "id", or
named "pkey"?

RE: [sqlite] executing sql on a sqlite from the cmd line

[Gauthier, Dave]

I also discovered that I can just use the "-header" and "-column"
qualifiers before specifying the DB name.  And, apparently, you can even
epscify the init file with "-init".(foolish me, I was looking for this
type of info before using "-h".. not "-help").

-dave
 

-Original Message-
From: Joe Wilson [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, August 22, 2007 2:48 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] executing sql on a sqlite from the cmd line

--- "Gauthier, Dave" <[EMAIL PROTECTED]> wrote:
> But how can I ".mode column" and  ".header on" as well as run the
query

If you're using bash (ksh and sh might work as well):

$ echo -e ".header on\n.mode column\nselect * from sqlite_master;" |
./sqlite3 abc.db
typenametbl_namerootpagesql
--  --  --  --  ---
table   abc abc 2   CREATE TABLE abc(a,b,c)
table   xyz xyz 3   CREATE TABLE xyz(x,y,z)

or if you have printf(1):

$ printf ".header on\nselect * from sqlite_master;" | ./sqlite3 abc.db
type|name|tbl_name|rootpage|sql
table|abc|abc|2|CREATE TABLE abc(a,b,c)
table|xyz|xyz|3|CREATE TABLE xyz(x,y,z)

See also: http://www.sqlite.org/cvstrac/wiki?p=ShellScript



   


Got a little couch potato? 
Check out fun summer activities for kids.
http://search.yahoo.com/search?fr=oni_on_mail=summer+activities+for+ki
ds=bz 


-
To unsubscribe, send email to [EMAIL PROTECTED]

-

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] sqlite.init ???

[Gauthier, Dave]

Hi:

I found nothing on the official sqlite website (sqlite.org) regarding
the sqlite.init file.  Did I miss something?  Are there other (better)
documentation resources other than the documentation link on sqlite.org?

SPecifically, I'm trying to either point sqlite3 to a specific
sqlite.init (not in the default home dir), or even better, execute >1
command on a line where some of the commands are pragmas.  Like...


.mode column; .header on; select * from mytable;

Any help?

-dave

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] executing sql on a sqlite from the cmd line

[Joe Wilson]

--- "Gauthier, Dave" <[EMAIL PROTECTED]> wrote:
> But how can I ".mode column" and  ".header on" as well as run the query

If you're using bash (ksh and sh might work as well):

$ echo -e ".header on\n.mode column\nselect * from sqlite_master;" | ./sqlite3 
abc.db
typenametbl_namerootpagesql
--  --  --  --  ---
table   abc abc 2   CREATE TABLE abc(a,b,c)
table   xyz xyz 3   CREATE TABLE xyz(x,y,z)

or if you have printf(1):

$ printf ".header on\nselect * from sqlite_master;" | ./sqlite3 abc.db
type|name|tbl_name|rootpage|sql
table|abc|abc|2|CREATE TABLE abc(a,b,c)
table|xyz|xyz|3|CREATE TABLE xyz(x,y,z)

See also: http://www.sqlite.org/cvstrac/wiki?p=ShellScript



   

Got a little couch potato? 
Check out fun summer activities for kids.
http://search.yahoo.com/search?fr=oni_on_mail=summer+activities+for+kids=bz
 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] executing sql on a sqlite from the cmd line

[Gauthier, Dave]

Yes, the sql works.  (Thanks James)

But how can I ".mode column" and  ".header on" as well as run the query
(given that I'm developing this for others to use and don't want to make
them create/edit a ~/sqlite.init with those elements)

-dave

-Original Message-
From: James Dennett [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, August 22, 2007 12:04 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] executing sql on a sqlite from the cmd line


> -Original Message-
> From: Gauthier, Dave [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, August 22, 2007 8:10 AM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] executing sql on a sqlite from the cmd line
> 
> On Linux...
> 
> 
> 
> How can I execute a sql command against a sqlite DB straight from the
> command line.  Somethign like
> 
> 
> 
> 
> 
> sqlite3 foo.sqldb -e "select * from mytable"
> 
> or...
> 
> sqlite3 foo.sqldb < "select * from mytable"
> 
> 
> 
> (only these don't work)

sqlite3 foo.sqldb "select * from mytable"

works for me on various platforms.  (I tend to use single quotes in
practice.)

-- James



-
To unsubscribe, send email to [EMAIL PROTECTED]

-

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] executing sql on a sqlite from the cmd line

[Rich Shepard]

On Wed, 22 Aug 2007, Gauthier, Dave wrote:


On Linux...



How can I execute a sql command against a sqlite DB straight from the
command line.  Somethign like



sqlite3 foo.sqldb -e "select * from mytable"
or...
sqlite3 foo.sqldb < "select * from mytable"


Dave,

  Write a script. If there's a response from the application, include
expect.

Rich

--
Richard B. Shepard, Ph.D.   |The Environmental Permitting
Applied Ecosystem Services, Inc.|  Accelerator(TM)
 Voice: 503-667-4517  Fax: 503-667-8863

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] executing sql on a sqlite from the cmd line

[Gauthier, Dave]

On Linux...

 

How can I execute a sql command against a sqlite DB straight from the
command line.  Somethign like

 

 

sqlite3 foo.sqldb -e "select * from mytable" 

or...

sqlite3 foo.sqldb < "select * from mytable"

 

(only these don't work)

 

Also, can I prefix with a setup option (like ".model column")? ...

 

sqlite3 foo.sqldb -e ".model column; select * from mytable"

 

Thanks in Advance for any advise

 

-dave

 

 

 

Re: [sqlite] Sparse matrix

[Simon Davies]

Hi Tom,

Here's a less gruesome version - no cases. I've given no thought to
performance comparisons.

C:\Joinerysoft\JMS\dev\trunk> sqlite3 tst.db
SQLite version 3.4.0
Enter ".help" for instructions

sqlite> insert into List values( 'a' );
sqlite> insert into List values( 'a' );
sqlite> insert into List values( 'a' );
sqlite> insert into List values( 'a' );
sqlite> insert into List values( 'b' );
sqlite> insert into List values( 'b' );
sqlite> insert into List values( 'c' );
sqlite> insert into List values( 'c' );
sqlite> insert into List values( 'c' );
sqlite> select v.Code, v.count from
sqlite> List as L
sqlite> left join
sqlite> (select (select L1.rowid from List as L1 where
sqlite>
L1.Code=L2.Code limit 1) as id,
sqlite> Code,
sqlite> count(Code) as count from List as L2 group by Code) as v
sqlite> on L.rowid=v.id order by L.rowid;
a|4
|
|
|
b|2
|
c|3
|

sqlite>
sqlite>

Rgds,
Simon

On 22/08/07, T <[EMAIL PROTECTED]> wrote:
> Hi all,
>
> OK, this one has me stumped. I've been staring at it too long.
>
> I have a table with a column like this:
>
> Code
> a
> a
> a
> a
> b
> b
> c
> c
> c
>
> I want to output two columns, one showing the Code, another showing
> the count of each code, but with a null wherever it was a repeat,
> like this:
>
> Code   Count
> a  4
> .
> .
> .
> b  2
> .
> c  3
> .
> .
>
> (Note I've used . to show a null)
>
> Any ideas?
>
> I can simply do this to show the count of each, but it doesn't show
> the blank rows:
>
> select Code, count(*) as Count from List group by Code;
>
> Here's the test table:
>
> create table List( Code text );
> insert into List values( 'a' );
> insert into List values( 'a' );
> insert into List values( 'a' );
> insert into List values( 'a' );
> insert into List values( 'b' );
> insert into List values( 'b' );
> insert into List values( 'c' );
> insert into List values( 'c' );
> insert into List values( 'c' );
>
> Thanks,
> Tom
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Feedback: Enabling FTS2 in Python + .net or anything else

[Andre du Plessis]

Ok this is how I got the fts working in python and .net...

 

In python 2.5 sqlite3 is automatically included as a builtin. However I
could not find any method which loads the extention or to enable it,

I COULD do it in SQL by using:

 

conn.cursor().execute("SELECT load_extension('fts2')")

 

however this will give an not authorized sql error, so you MUST call
enable_load_extension however this is not available in python, so I
recompiled the SQLite3.dll with directive SQLITE_ENABLE_LOAD_EXTENSION=1
set.

Notice I had to set this to =1 because this directive is still an older
#ifdef instead of if defined, which would work with just
SQLITE_ENABLE_LOAD_EXTENSION.

 

Now this is enabled by default and the above can work in python or .net
or anything im guessing whether it has the loadextension or not, as long
as FTS2.dll is present.

 

I obviously understand that for SQLite to enable this by default is a
security issue, which could allow attackers to load malicious dll's,
maybe it would be nice, if the authors could do the following:

 

Maybe provide a precompiled dll with extension enabled, OR with FTS1,
and 2 compiled in,

 

What I tried that would be nice is to compile FTS2 and 1 in the dll so
it just works by default without having to loadext or anything,

By enabling: SQLITE_ENABLE_FTS1 and SQLITE_ENABLE_FTS2

But I could NOT get this to work in M$ Visual Studio , I was getting
weird memory corruption in my DLL when I used maybe because Im a c noob?
Could be, I did not do something I was supposed to.

 

Just thought others trying to do the same thing might find this helpful,
or provide feedback.

Re: [sqlite] Sparse matrix

[Simon Davies]

Hi Tom,

Its a pretty gruesome bit of sql...

C:\Joinerysoft\JMS\dev\trunk> sqlite3 tst.db
SQLite version 3.4.0
Enter ".help" for instructions

sqlite> create table List( Code text );
sqlite> insert into List values( 'a' );
sqlite> insert into List values( 'a' );
sqlite> insert into List values( 'a' );
sqlite> insert into List values( 'a' );
sqlite> insert into List values( 'b' );
sqlite> insert into List values( 'b' );
sqlite> insert into List values( 'c' );
sqlite> insert into List values( 'c' );
sqlite> insert into List values( 'c' );
sqlite>
sqlite> select
...>case rowid>(select rowid from List as L2 where L1.Code=L2.Code limit 1)
...>when 0 then L1.Code
...>else null end,
...>case rowid>(select rowid from List as L2 where L1.Code=L2.Code limit 1)
...>when 0 then
...>   (select count(Code) from List as L2 where l1.Code=L2.Code)
...>else null end
...>from List as L1;
a|4
|
|
|
b|2
|
c|3
|
|sqlite>
sqlite>

Rgds,
Simon

On 22/08/07, T <[EMAIL PROTECTED]> wrote:
> Hi all,
>
> OK, this one has me stumped. I've been staring at it too long.
>
> I have a table with a column like this:
>
> Code
> a
> a
> a
> a
> b
> b
> c
> c
> c
>
> I want to output two columns, one showing the Code, another showing
> the count of each code, but with a null wherever it was a repeat,
> like this:
>
> Code   Count
> a  4
> .
> .
> .
> b  2
> .
> c  3
> .
> .
>
> (Note I've used . to show a null)
>
> Any ideas?
>
> I can simply do this to show the count of each, but it doesn't show
> the blank rows:
>
> select Code, count(*) as Count from List group by Code;
>
> Here's the test table:
>
> create table List( Code text );
> insert into List values( 'a' );
> insert into List values( 'a' );
> insert into List values( 'a' );
> insert into List values( 'a' );
> insert into List values( 'b' );
> insert into List values( 'b' );
> insert into List values( 'c' );
> insert into List values( 'c' );
> insert into List values( 'c' );
>
> Thanks,
> Tom
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] bigfile test

[J Jayavasanthan]

More on to my earlier email,

I believe SQLite already uses those flags, I haven't done the testing
myself, but it is claimed SQLite is able to create rather large databases
(in terms of TBs) without any fuss,

Regards,
Jayavasanthan J

On 8/22/07, J Jayavasanthan <[EMAIL PROTECTED]> wrote:
>
> Hi Victor,
>
> Normally the large file creation should not fail on the 32 bit builds. And
> -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 will create other side effects
> such as off_t getting to long long rather than long and similar with
> size_t (I guess).
>
> The solution is to access/create the file along with the following flag
> O_LARGEFILE option. Please note that this option is not needed for FreeBSD
> as this OS uses 64 bit offsets by default.
>
> For windows, if you have to create a file larger than 4 GB, then you have
> to have NTFS file system and not FAT32 (FYKI)
>
> HTH,
>
> Regards,
> Jayavasanthan J.
>
>
> On 8/21/07, Victor Secarin <[EMAIL PROTECTED] > wrote:
> >
> > Having built sqlite 3.4.2 for Enterprise 3, 4, Fedora 5 and Solaris 8,
> > both 32 and 64 bits, I then ran the fulltest on each. As it takes a long
> > time and produces a large output, one looks first at the end, and if it
> > says "0 errors" one thinks everything is as expected.
> >
> > By chance, I later discovered the following:
> >
> > 
> > > ./testfixture ../sqlite-3.4.2/test/bigfile.test
> > bigfile-1.1... Ok
> >  Unable to create a file larger than 4096 MB. *
> > Memory leaked: 0 bytes in 0 allocations
> >
> > soft-heap-limit changed by this script from 0 to
> > Thread-specific data deallocated properly
> > 0 errors out of 2 tests
> > Failures on these tests:
> > ==
> >
> > This happens on all my 32 bit builds. Not on the 64 bit ones.
> > In order to get large file functionality one has to configure with
> > CFLAGS="-D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64"
> >
> > The problem is that "Unable to create a file larger than 4096 MB" is not
> >
> > considered a failure, so that people who only see the "0 errors out of
> > 409660 tests" at the end of the log file will not  know they have the
> > problem.
> > It is really difficult to read the whole log file.
> > I suggest to
> > (a) make this a counted error,  or else
> > (b) add an extra test specially for this problem, or else
> > (c) categorize the messages, prefixing them with a small number of
> > standard words, like "ERROR:" and "WARNING:" and "INFO:", which would
> > enable one to use grep or search in an editor. Then  the problem above
> > could be a WARNING rather than a counted ERROR.
> >
> >
> > yours truly,
> > Victor Secarin
> >
>
>


-- 
first me then home
first home then country
first country then world
fools always read inverse

Re: [sqlite] bigfile test

[J Jayavasanthan]

Hi Victor,

Normally the large file creation should not fail on the 32 bit builds. And
-D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 will create other side effects
such as off_t getting to long long rather than long and similar with size_t
(I guess).

The solution is to access/create the file along with the following flag
O_LARGEFILE option. Please note that this option is not needed for FreeBSD
as this OS uses 64 bit offsets by default.

For windows, if you have to create a file larger than 4 GB, then you have to
have NTFS file system and not FAT32 (FYKI)

HTH,

Regards,
Jayavasanthan J.


On 8/21/07, Victor Secarin <[EMAIL PROTECTED]> wrote:
>
> Having built sqlite 3.4.2 for Enterprise 3, 4, Fedora 5 and Solaris 8,
> both 32 and 64 bits, I then ran the fulltest on each. As it takes a long
> time and produces a large output, one looks first at the end, and if it
> says "0 errors" one thinks everything is as expected.
>
> By chance, I later discovered the following:
>
> 
> > ./testfixture ../sqlite-3.4.2/test/bigfile.test
> bigfile-1.1... Ok
>  Unable to create a file larger than 4096 MB. *
> Memory leaked: 0 bytes in 0 allocations
>
> soft-heap-limit changed by this script from 0 to
> Thread-specific data deallocated properly
> 0 errors out of 2 tests
> Failures on these tests:
> ==
>
> This happens on all my 32 bit builds. Not on the 64 bit ones.
> In order to get large file functionality one has to configure with
> CFLAGS="-D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64"
>
> The problem is that "Unable to create a file larger than 4096 MB" is not
> considered a failure, so that people who only see the "0 errors out of
> 409660 tests" at the end of the log file will not  know they have the
> problem.
> It is really difficult to read the whole log file.
> I suggest to
> (a) make this a counted error,  or else
> (b) add an extra test specially for this problem, or else
> (c) categorize the messages, prefixing them with a small number of
> standard words, like "ERROR:" and "WARNING:" and "INFO:", which would
> enable one to use grep or search in an editor. Then  the problem above
> could be a WARNING rather than a counted ERROR.
>
>
> yours truly,
> Victor Secarin
>

Re: [sqlite] Sparse matrix

[Darren Duncan]

It seems to me that you have a flawed design.

You should just have a 2 column database to begin with, with a table like this:

  Code  Count
  a 4
  b 2
  c 3

Rather than plain inserting or deleting rows, just sometimes insert 
or delete, you should instead increment or decrement counts, adding a 
row if its the first instance of the code, and removing the row if it 
would decrement to zero.


Displaying sparse like that should be a function of your application 
display code, not the database, and it can easily do that using a 
simple for-loop or something for each fetched row, iterating on the 
count.


-- Darren Duncan

-
To unsubscribe, send email to [EMAIL PROTECTED]
-