Re: [sqlite] normalization example(s)

On 6/25/2010 10:51 PM, Simon Slavin wrote: > > On 26 Jun 2010, at 3:47am, John wrote: > >> I don't know if SQLite now supports foreign keys yet or not. > > So you're posting to a mailing list you don't read ? I read this! ;-) Actually, I did do one project early this year with SQLite and had high

Re: [sqlite] count distinct on multiple fields

Peng Yu wrote: > I use || to count distinct on multiple fields. It works for this > particular example. But I'm not sure if it is robust. Would you please > let me know if there is any better way? > > select distinct value1, value2 from test; > select count(distinct value1||value2) from test; se

[sqlite] count distinct on multiple fields

I use || to count distinct on multiple fields. It works for this particular example. But I'm not sure if it is robust. Would you please let me know if there is any better way? #!/usr/bin/env bash rm -f main.db sqlite3 main.db

Re: [sqlite] normalization example(s)

On 26 Jun 2010, at 3:47am, John wrote: > I don't know if SQLite now supports foreign keys yet or not. So you're posting to a mailing list you don't read ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mail

Re: [sqlite] normalization example(s)

On 9/26/2009 10:17 PM, John wrote: > I have "The Definitive Guide to SQLite" and it has jump > started me with databases. (the index could stand a lot > of improvement though). > > I am looking for a "quick start" example of the normalization > case where one table has an index into another table o

[sqlite] set width from command line

I don't see an option (in man) to set width for column mode (equivalent to .width command in an sqlite3 session) from sqlite3 command line. I'm wondering if there is such an option or it is not available from the command line. -- Regards, Peng ___ sqlit

Re: [sqlite] alternative to UNIQUE CONSTRAINT

Oliver Peters wrote: > I can't use UNIQUE CONSTRAINTs because of strange behaviour in my frontend > (reason is assumably in the ODBC-driver). The PK is already used for an > autoincrement column. > > Is there another way to enforce UNIQUE CONSTRAINTs despite INSERT/UPDATE > triggers? I'd prefer CH

Re: [sqlite] Why is there no test suite for ''make check' ?

On 25 Jun 2010, at 7:06pm, Dr. David Kirkby wrote: > On 06/24/10 05:13 PM, Richard Hipp wrote: > >> I see. You are building from the amalgamation. And you should be too. But >> you are right - we do not ship test suites with the amalgamation. To use >> the free test suite for SQLite, you hav

Re: [sqlite] Why is there no test suite for ''make check' ?

On Fri, Jun 25, 2010 at 2:06 PM, Dr. David Kirkby wrote: > On 06/24/10 05:13 PM, Richard Hipp wrote: > > > I see. You are building from the amalgamation. And you should be too. > But > > you are right - we do not ship test suites with the amalgamation. To use > > the free test suite for SQLite

Re: [sqlite] SELECT query performance : One big table Vs many small tables

On Fri, Jun 25, 2010 at 5:01 AM, Nilesh SIMARIA wrote: > Hello, > > I have a query about 'SELECT' query's performance when we have one huge > table vs. multiple small tables. > > Basically in our application, we need to run select query multiple times and > today we have one huge table. Do you guy

Re: [sqlite] composite PK vs. single PK

On Fri, Jun 25, 2010 at 7:39 AM, P Kishor wrote: > Thanks Cory. > > On Fri, Jun 25, 2010 at 9:07 AM, Cory Nelson wrote: >> On Fri, Jun 25, 2010 at 6:49 AM, P Kishor wrote: >>> Is there any gotcha, any disadvantage (query complexity, db size, >>> query speed) to using a composite PK (two columns)

Re: [sqlite] Why is there no test suite for ''make check' ?

On 06/24/10 05:13 PM, Richard Hipp wrote: > I see. You are building from the amalgamation. And you should be too. But > you are right - we do not ship test suites with the amalgamation. To use > the free test suite for SQLite, you have to build from canonical source > code. We have a separate

Re: [sqlite] 64 bit libsqlite for AIX and Solaris

For AIX, using IBM's C compiler, set "OBJECT_MODE=64" in your environment before building. -Ross On Thu, Jun 24, 2010 at 1:13 PM, Sushil wrote: > Hi Nicholas, Dr. David, > > Thanks for help. > > I got it working by using the /opt/apps/SunStudio11/SUNWspro/bin/cc > compiler > > with "-xarch=v9

Re: [sqlite] Updating specific rows in a table

Erik, I didn't quite understand what you wanted to say. Neither about RSQLite and new field (it seems that you use the same field in both tables), nor about having data in the same table. Could you please elaborate? Pavel On Thu, Jun 24, 2010 at 11:37 AM, Erik Wright wrote: > Hi Pavel, > I need

Re: [sqlite] alternative to UNIQUE CONSTRAINT

Igor Tandetnik writes: [...] > Isn't that exactly what you were asking for - a different syntax to achieve the same end result? Not really because the assumed ODBC-Driver problem has nothing to do with the syntax but the underlying mechanism (sounds logically for me ;-) ) because I get an erro

Re: [sqlite] alternative to UNIQUE CONSTRAINT

Oliver Peters wrote: > Igor Tandetnik writes: > >> CREATE UNIQUE INDEX, perhaps? > > > I already thought about it but where's the difference There's none. Isn't that exactly what you were asking for - a different syntax to achieve the same end result? -- Igor Tandetnik

Re: [sqlite] alternative to UNIQUE CONSTRAINT

Igor Tandetnik writes: [...] > CREATE UNIQUE INDEX, perhaps? I already thought about it but where's the difference between solution 1 -- CREATE TABLE example( a INTEGER PRIMARY KEY AUTOINCREMENT b TEXTNOT NULL, c INTEGER NOT NULL UNIQUE -- or UNIQUE(c) ); AND soluti

Re: [sqlite] alternative to UNIQUE CONSTRAINT

Oliver Peters wrote: > I can't use UNIQUE CONSTRAINTs because of strange behaviour in my frontend > (reason is assumably in the ODBC-driver). The PK is already used for an > autoincrement column. > > Is there another way to enforce UNIQUE CONSTRAINTs CREATE UNIQUE INDEX, perhaps? -- Igor Tandet

Re: [sqlite] Use of strftime in a CREATE INDEX sintax.

On Fri, Jun 25, 2010 at 11:16:58AM -0400, Igor Tandetnik scratched on the wall: > Danilo Cicerone wrote: > > I'm trying to create an index as follow: > > > > CREATE UNIQUE INDEX Numerazione ON FattureCli (fat_numfat, strftime('%Y', > > fat_datfat)) ON CONFLICT FAIL; > > > > Could it be translated

Re: [sqlite] Use of strftime in a CREATE INDEX sintax.

Danilo Cicerone wrote: > I'm trying to create an index as follow: > > CREATE UNIQUE INDEX Numerazione ON FattureCli (fat_numfat, strftime('%Y', > fat_datfat)) ON CONFLICT FAIL; > > Could it be translated into a corret sintax without add another field > (fat_year)? No. -- Igor Tandetnik ___

Re: [sqlite] SELECT query performance : One big table Vs many small tables

On 25 Jun 2010, at 3:46pm, Nilesh SIMARIA wrote: > We have one more column in table of type blob and we have to > do look up based on that column as well. > > The query looks like this :- > > "SELECT * from table where parent == ? and c_blob == ?" > > We have to execute this query multiple tim

[sqlite] 2 Call For Papers, 17th Annual Tcl/Tk Conference 2010

[[ Important Changes: More information about registration at the hotel. Our location chair has organized special social activities, both geeky (FermiLab Tour) and cultured (Theatre). See below for more. ]] 17th Annual Tcl/Tk Conference (Tcl'2010) http://www.tcl.tk/community/tcl2010/

Re: [sqlite] SELECT query performance : One big table Vs many small tables

On Fri, Jun 25, 2010 at 7:26 PM, Simon Slavin wrote: > > On 25 Jun 2010, at 2:39pm, Nilesh SIMARIA wrote: > > > select * from table where parent = ? and aid = ?; > > > > Our index is "create index idx on table (parent, aid)" > > That should do fine. Make sure that columns which will hold integer

Re: [sqlite] composite PK vs. single PK

Thanks Cory. On Fri, Jun 25, 2010 at 9:07 AM, Cory Nelson wrote: > On Fri, Jun 25, 2010 at 6:49 AM, P Kishor wrote: >> Is there any gotcha, any disadvantage (query complexity, db size, >> query speed) to using a composite PK (two columns) vs. a single >> AUTOINCREMENT INT? >> >> Background: I ha

Re: [sqlite] composite PK vs. single PK

On Fri, Jun 25, 2010 at 6:49 AM, P Kishor wrote: > Is there any gotcha, any disadvantage (query complexity, db size, > query speed) to using a composite PK (two columns) vs. a single > AUTOINCREMENT INT? > > Background: I happen to have the two columns in question in my table > anyway. Adding an I

Re: [sqlite] SELECT query performance : One big table Vs many small tables

On 25 Jun 2010, at 2:39pm, Nilesh SIMARIA wrote: > select * from table where parent = ? and aid = ?; > > Our index is "create index idx on table (parent, aid)" That should do fine. Make sure that columns which will hold integers are defined as INTEGER, not INT or NUMBER or anything like that.

[sqlite] Use of strftime in a CREATE INDEX sintax.

Hi to all, I'm trying to create an index as follow: CREATE TABLE FattureCli ( fat_id INTEGER PRIMARY KEY, fat_rif_sogid INTEGER DEFAULT NULL, fat_rif_mpaid INTEGER DEFAULT NULL, fat_numfat INTEGER NOT NULL, -- It would be unique by year; fat_datfat CHAR(10) NOT NULL, -- -MM-DD;

[sqlite] composite PK vs. single PK

Is there any gotcha, any disadvantage (query complexity, db size, query speed) to using a composite PK (two columns) vs. a single AUTOINCREMENT INT? Background: I happen to have the two columns in question in my table anyway. Adding an INTEGER PRIMARY KEY would use up space I don't want to use. My

Re: [sqlite] SELECT query performance : One big table Vs many small tables

On Fri, Jun 25, 2010 at 5:47 PM, Simon Slavin wrote: > > I just want to check because this is a specialty of mine: do you perhaps mean that you have indexed all the fields individually ? Because that's a common mistake and it's a huge waste of time and space. There is one particular INDEX which

Re: [sqlite] UPDATE without a JOIN

update TABLE2 set z = @z where rowid in ( select t2.rowid from TABLE1_2 t12, TABLE2 t2 where t12.a = @a and t12.b = @b and t2.x = t12.x and t2.y = t12.y ) Pavel On Fri, Jun 25, 2010 at 9:04 AM, Matthew Jones wrote: > I've seen various posts about who to get around the lack of UPDATE with > a JO

Re: [sqlite] TRIGGER ?

Bingo. Thanx! On Fri, Jun 25, 2010 at 2:59 PM, Jay A. Kreibich wrote: > On Fri, Jun 25, 2010 at 02:16:41PM +0200, Laslo Forro scratched on the > wall: > > Hi there, I am trying to make a trigger calling a function BEFORE DELETE > on > > a table that contains file names. This function takes the f

[sqlite] UPDATE without a JOIN

I've seen various posts about who to get around the lack of UPDATE with a JOIN but they all seem to refer to tables joined on a single column. I need to do something very similar but with two-column primary key. E.g. sqlite> create table TABLE1 (a int, b int, primary key(a, b)); sqlite> create t

Re: [sqlite] TRIGGER ?

On Fri, Jun 25, 2010 at 02:16:41PM +0200, Laslo Forro scratched on the wall: > Hi there, I am trying to make a trigger calling a function BEFORE DELETE on > a table that contains file names. This function takes the filename of the > row to be deleted, removes it and returns undef on failure. > I am

Re: [sqlite] SELECT query performance : One big table Vs many small tables

On 25 Jun 2010, at 1:01pm, Nilesh SIMARIA wrote: > I have a query about 'SELECT' query's performance when we have one huge > table vs. multiple small tables. > > Basically in our application, we need to run select query multiple times and > today we have one huge table. Do you guys think breakin

[sqlite] TRIGGER ?

Hi there, I am trying to make a trigger calling a function BEFORE DELETE on a table that contains file names. This function takes the filename of the row to be deleted, removes it and returns undef on failure. I am not too expert of triggers and it can be solved from a script, but that is not a nic

Re: [sqlite] when foreign key is PRIMARY KEY AUTOINCREMENT

On Fri, Jun 25, 2010 at 02:42:45PM +0800, zeal scratched on the wall: > Hi, CREATE TABLE Parent( ParentId INTEGER PRIMARY KEY AUTOINCREMENT, > name TEXT, PRIMARY KEY (ParentId));CREATE TABLE Child( ChildId > INTEGER PRIMARY KEY AUTOINCREMENT, ParentId INT,name TEXT,

[sqlite] SELECT query performance : One big table Vs many small tables

Hello, I have a query about 'SELECT' query's performance when we have one huge table vs. multiple small tables. Basically in our application, we need to run select query multiple times and today we have one huge table. Do you guys think breaking them into multiple small tables will help ? For te

Re: [sqlite] 64 bit libsqlite for AIX and Solaris

Hi Nicholas, Dr. David, Thanks for help. I got it working by using the /opt/apps/SunStudio11/SUNWspro/bin/cc compiler with "-xarch=v9 -xcode=abs64" as CFLAGS. Regards, Sushil. On Thu, Jun 24, 2010 at 10:26 PM, Nicolas Williams < nicolas.willi...@oracle.com> wrote: > On Thu, Jun 24, 2010 at

Re: [sqlite] 64 bit libsqlite for AIX and Solaris

On Thu, Jun 24, 2010 at 01:37:50PM +0530, Sushil wrote: > I am looking for 64 bit libsqlite for AIX and Solaris. Is there a > place from where I can get them pre-built ? For OpenSolaris you can find SQLite3 packages, including a 64-bit build of the library, in the OpenSolaris IPS /release package

Re: [sqlite] Updating specific rows in a table

Hi Pavel, I need to transfer from another table because I am using an interface called RSQLite. With this interface there is no easy method for creating a new field in a currently existing table. It sounds like it would be a quicker operation if the data were all in the same table? Is there an

[sqlite] Index creation using 'strftime'.

Hi to all, I'm trying to create an index as follow: CREATE TABLE FattureCli ( fat_id INTEGER PRIMARY KEY, fat_rif_sogid INTEGER DEFAULT NULL, fat_rif_mpaid INTEGER DEFAULT NULL, fat_numfat INTEGER NOT NULL, -- It would be unique by year; fat_datfat CHAR(10) NOT NULL, -- -MM-DD;

[sqlite] Index creation using 'strftime'.

Hi to all, I'm trying to create an index as follow: CREATE TABLE FattureCli ( fat_id INTEGER PRIMARY KEY, fat_rif_sogid INTEGER DEFAULT NULL, fat_rif_mpaid INTEGER DEFAULT NULL, fat_numfat INTEGER NOT NULL, -- It would be unique by year; fat_datfat CHAR(10) NOT NULL, -- -MM-DD;

[sqlite] alternative to UNIQUE CONSTRAINT

Hello, I can't use UNIQUE CONSTRAINTs because of strange behaviour in my frontend (reason is assumably in the ODBC-driver). The PK is already used for an autoincrement column. Is there another way to enforce UNIQUE CONSTRAINTs despite INSERT/UPDATE triggers? I'd prefer CHECK-CONSTRAINTs but don't

Re: [sqlite] What is the sqlite3 script suffix?

I use for sql files also .sql. For sqlite2 db's I use .sqlite and for sqlite3 db's i use .sqlite3. with best wishes Artur -- > On Wed, 23 Jun 2010 22:41:55 -0500, Peng Yu > wrote: > >>I'm trying to following the convention. If I'm going to save sql >>commands in a script and run th

Re: [sqlite] sqlite documents in pdf format

Hello AR> You can also bookmark html pages. AR> > I'm using pdf because I can comment and bookmark it to facilitate my AR> > reading process. I'm open to any other options. But it seems that AR> > there is no way to comment on chm (Let me know if I'm wrong). Then AR> > probably the solution is to

Re: [sqlite] sqlite documents in pdf format

You can also bookmark html pages. Commenting in CHM files is not supported, but you have the option to save favorite pages in chm files, but only if the chm file was made with this option activated. Well if you still need it in pdf-format, then you need to search for a programm or make it file pe