[sqlite] VACUUM can actually increase the DB file?

[Oliver Schneider]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA256

Hi there,

just a few minutes ago I ran a VACUUM on a DB file and the size before
was 2089610240 and afterwards 2135066624. Is this normal?


Thanks,

// Oliver
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (MingW32)

iQEcBAEBCAAGBQJMk/T6AAoJEJm+Ui/QYLFPNP4IAIGnbFh2kcPjlZgGpuSCnELO
1YaXtl3vmyMHSXjOykLdMd7hVgSNY3BDvzk2Lp4hWXGmDEgU3Zu7GBqKoJVadRzx
E8Q1IMLEebvxnoN3CUg3m2+aATUNYkxVZGNzRdFq2H84xFb8iZ42tRfkE11bIQsj
KdPaFbQI/xyUvv2AzvemvGIaeHtWjgn1Qjk5dM2Z+BWaQ4rIC8+OaaHPif1rl7Pv
oRPyjN2+mdrfQ1GdpPz+4OOmbAGWBbZtChUpjx29NLlz1Fs9bz/EBs46rhIirfnN
w/QeMaqBCkrxkQ1lE99z5yXgRkLX09weHO4db0O+lGh+Xt/MXGpHwPZ87HjrBfU=
=J/4p
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UNIQUE PRIMARY KEY on TEXT column?

[Nicolas Williams]

On Fri, Sep 17, 2010 at 02:02:44PM -0400, Igor Tandetnik wrote:
> Keith Roberts  wrote:
> > I think what I really mean is I want a text column with only
> > one unique value, that is also indexed.
> 
> UNIQUE constraint will do just that.
> 
> > Also ,I guess the INTEGER PRIMARY KEY column is worth
> > keeping in the table, as a matter of course?
> 
> Usually, yes.

SQLite3 always has an integer primary key, either explicit or implied.
So if you have a primary key column that is not an integer primary key,
then effectively what you get is the same as having one column as
integer primary key and another column with a unique constraint.

It doesn't have to be so though.  A SQL RDMBS could easily have tables
which are hash-based instead of b-tree-based, in which case a table with
a primary key column that is not an integer primary key could well not
have an implied integer primary key.  But that's not what SQLite3 does
at this time.

In any case, it's almost always useful to have an integer primary key.

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

Re: [sqlite] UNIQUE PRIMARY KEY on TEXT column?

[Keith Roberts]

On Fri, 17 Sep 2010, Roger Andersson wrote:

> To: 'General Discussion of SQLite Database' 
> From: Roger Andersson 
> Subject: Re: [sqlite] UNIQUE PRIMARY KEY on TEXT column?
> 
>> I'm getting there now ;)
>>
>> Keith
>
> I don't know if this will be of any help but you can do something like
> UPDATE file_downloads set
> dl_count = dl_count + 1
> where filename = "$dl_file";

Hi Roger.

Yes, that's viable.

I've been writing a PHP script and did the increment in 
that. Either way it's gonna work.

I know you are supposed to put as much of the logic into 
pure SQL code, but as it's only a simple SQL command, so it 
probably doesn't matter in this scenario.

Keith

-
Websites:
http://www.karsites.net
http://www.php-debuggers.net
http://www.raised-from-the-dead.org.uk

All email addresses are challenge-response protected with
TMDA [http://tmda.net]
-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UNIQUE PRIMARY KEY on TEXT column?

[Roger Andersson]

> I'm getting there now ;)
> 
> Keith

I don't know if this will be of any help but you can do something like
UPDATE file_downloads set
dl_count = dl_count + 1
where filename = "$dl_file";

/Roger

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

Re: [sqlite] UNIQUE PRIMARY KEY on TEXT column?

[Keith Roberts]

On Fri, 17 Sep 2010, Igor Tandetnik wrote:

> To: sqlite-users@sqlite.org
> From: Igor Tandetnik 
> Subject: Re: [sqlite] UNIQUE PRIMARY KEY on TEXT column?
> 
> Keith Roberts  wrote:
>> I think what I really mean is I want a text column with only
>> one unique value, that is also indexed.
>
> UNIQUE constraint will do just that.
>
>> Also ,I guess the INTEGER PRIMARY KEY column is worth
>> keeping in the table, as a matter of course?
>
> Usually, yes.

Thanks Igor.

I'm getting there now ;)

Keith

-
Websites:
http://www.karsites.net
http://www.php-debuggers.net
http://www.raised-from-the-dead.org.uk

All email addresses are challenge-response protected with
TMDA [http://tmda.net]
-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UNIQUE PRIMARY KEY on TEXT column?

[Igor Tandetnik]

Keith Roberts  wrote:
> I think what I really mean is I want a text column with only
> one unique value, that is also indexed.

UNIQUE constraint will do just that.

> Also ,I guess the INTEGER PRIMARY KEY column is worth
> keeping in the table, as a matter of course?

Usually, yes.
-- 
Igor Tandetnik


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

Re: [sqlite] UNIQUE PRIMARY KEY on TEXT column?

[Keith Roberts]

On Fri, 17 Sep 2010, O'Neill, Owen wrote:

> To: General Discussion of SQLite Database 
> From: "O'Neill, Owen" 
> Subject: Re: [sqlite] UNIQUE PRIMARY KEY on TEXT column?
> 
>
> Hi Keith,
>
> Depending on your definition of 'efficient' then this might do what you
> want. (untested - might need to add or remove bracket since sqlite does
> seem fussy about having what it considers to be extra ones)
>
> INSERT OR REPLACE INTO "file_downloads" SELECT
>   COALESCE( (SELECT record_id from "file_downloads" where where
> filename = "$dl_file"),(SELECT MAX( record_id) FROM
> "file_downloads")+1),
>   $dl_file,
>   COALESE( (select "dl_count" from "file_downloads" where where
> filename = "$dl_file"),0)+1;
>
> In summary - we get the record ID for the file in question - if it does
> not exist then we get the max of the current ones and add 1
> Then we get either the current download count for the file - or a zero
> and add 1 to it.
> So the whole thing is atomic and you run it once for each download that
> happens so can run it across multiple instances / threads etc.

Thanks Owen.

I don't think there will be a problem with the row for the 
particular filename not existing. I will run an insert 
statement from the sqlite3 command line to insert/create the 
neccessary row.

Using the UNIQUE constraint will ensure there is only one 
row for that particular filename in the database. I only 
want one counter per filename, otherwise things could get 
messy!

It appears that the UNIQUE constraint also creates an INDEX 
on that text column - which is just what I want.

So I'm now using:

CREATE TABLE "file_downloads"
  (
  "record_id" INTEGER PRIMARY KEY,

  "filename" TEXT UNIQUE,

  "dl_count" INTEGER

   /* PRIMARY KEY  ("record_id") */
);

And this is what I have on the SQLite3 command line:

sqlite> insert into file_downloads
...> (filename, dl_count)
...> VALUES ('KickstartUserGuide.pdf', '0');

sqlite> insert into file_downloads
...> (filename, dl_count)
...> VALUES ('KickstartUserGuide.pdf', '0');
Error: constraint failed

So the UNIQUE constraint is working nicely.

sqlite> insert into file_downloads
...> (filename, dl_count)
...> VALUES ('KickstartUserGuide.dvi', '0');
sqlite>

sqlite> select * from file_downloads;
record_id = 1
  filename = KickstartUserGuide.pdf
  dl_count = 0

record_id = 2
  filename = KickstartUserGuide.dvi
  dl_count = 0
sqlite>

That should do fine for my purposes. The code to increment 
the dl_count column is done in PHP, when the SQL command is 
prepared.

Thanks for all your help.

Kind Regards,

Keith Roberts

-
Websites:
http://www.karsites.net
http://www.php-debuggers.net
http://www.raised-from-the-dead.org.uk

All email addresses are challenge-response protected with
TMDA [http://tmda.net]
-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UNIQUE PRIMARY KEY on TEXT column?

[Keith Roberts]

On Fri, 17 Sep 2010, Igor Tandetnik wrote:

> To: sqlite-users@sqlite.org
> From: Igor Tandetnik 
> Subject: Re: [sqlite] UNIQUE PRIMARY KEY on TEXT column?
> 
> Keith Roberts  wrote:
>> I have the following table to keep a count of the number of
>> times files are downloaded from my website:
>>
>> CREATE TABLE "file_downloads"
>>  (
>>  "record_id" INTEGER PRIMARY KEY,
>>  "filename" char(60),
>>  "dl_count" INTEGER
>>   /* PRIMARY KEY  ("record_id") */
>> );
>>
>> The select statement to get the current download count for a
>> file is:
>>
>> SELECT *
>> from file_downloads
>> where filename = "$dl_file";
>>
>> And the update statement to update the dl_count column is:
>>
>> UPDATE file_downloads set
>> dl_count = "$new_count"
>> where filename = "$dl_file";
>>
>> Would this be an efficient way of doing things?
>>
>> I don't expect to be keeping track of more than 200 files
>> for downloading.
>
> Not really efficient, but with 200 records, you won't notice.
>
>> I was wondering if it is possible to setup the filename 
>> column as a UNIQUE PRIMARY KEY on a TEXT column.
>
> It's UNIQUE or PRIMARY KEY: they don't go together. Yes, 
> you could do either. There could be only one PRIMARY KEY 
> in a table, and you already have one, so you'll have to 
> choose which one you want. On the other hand, you could 
> have an unlimited number of UNIQUE constraints. -- Igor 
> Tandetnik

Thanks Igor.

I think what I really mean is I want a text column with only 
one unique value, that is also indexed.

Also ,I guess the INTEGER PRIMARY KEY column is worth 
keeping in the table, as a matter of course?

Keith

-
Websites:
http://www.karsites.net
http://www.php-debuggers.net
http://www.raised-from-the-dead.org.uk

All email addresses are challenge-response protected with
TMDA [http://tmda.net]
-

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

Re: [sqlite] UNIQUE PRIMARY KEY on TEXT column?

[O'Neill, Owen]

Hi Keith,

Depending on your definition of 'efficient' then this might do what you
want. (untested - might need to add or remove bracket since sqlite does
seem fussy about having what it considers to be extra ones)

INSERT OR REPLACE INTO "file_downloads" SELECT 
COALESCE( (SELECT record_id from "file_downloads" where where
filename = "$dl_file"),(SELECT MAX( record_id) FROM
"file_downloads")+1),
$dl_file,
COALESE( (select "dl_count" from "file_downloads" where where
filename = "$dl_file"),0)+1;

In summary - we get the record ID for the file in question - if it does
not exist then we get the max of the current ones and add 1
Then we get either the current download count for the file - or a zero
and add 1 to it.
So the whole thing is atomic and you run it once for each download that
happens so can run it across multiple instances / threads etc.

Cheers
Owen


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Keith Roberts
Sent: Friday, September 17, 2010 12:41 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] UNIQUE PRIMARY KEY on TEXT column?

Hi all.

I have the following table to keep a count of the number of 
times files are downloaded from my website:

CREATE TABLE "file_downloads"
  (
  "record_id" INTEGER PRIMARY KEY,

  "filename" char(60),

  "dl_count" INTEGER

   /* PRIMARY KEY  ("record_id") */
);


The select statement to get the current download count for a 
file is:

SELECT *
from file_downloads
where filename = "$dl_file";

And the update statement to update the dl_count column is:

UPDATE file_downloads set
dl_count = "$new_count"
where filename = "$dl_file";

Would this be an efficient way of doing things?

I don't expect to be keeping track of more than 200 files 
for downloading.

I was wondering if it is possible to setup the filename 
column as a UNIQUE PRIMARY KEY on a TEXT column. Is that 
possible? I don't need FTS capabilities, just want to make 
the filename column a UNIQUE column, so there is only one 
row with that particular filename.

Kind Regards,

Keith Roberts

-
Websites:
http://www.karsites.net
http://www.php-debuggers.net
http://www.raised-from-the-dead.org.uk

All email addresses are challenge-response protected with
TMDA [http://tmda.net]
-
___
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] Check-in [876845661a] incomplete

[Ralf Junker]

Check-in [876845661a] (http://www.sqlite.org/src/info/876845661a) is
incomplete: fts2_tokenizer.c still contains calls to isalnum() and
tolower() for FTS2.

FTS1 also #includes , just in case you care to remove it from
SQLite completely. The changes are just like for FTS2.

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

[sqlite] no driver for sqlite3-ruby

[Leo Valk]

trying to follow the ruby-on-rails-3.0 tutorial by Michael Hartl I am 
embarrassingly stuck already at 1.2.5
I have installed ruby 1.9.2
Gems 1.3.7
rails 3.0
set up first_app
changed the gemfile to request sqlite3-ruby 1.2.5
saw the welcome page at the localhost:3000
but when I hit the link:
about your application's environment I get the error message that says:
the procedure entry point rb_str2cstr could not be located in the dynamic link 
library msvcrt-ruby191.dll
and when I open the link it says
Runtime error
no driver for sqlite3 found

Any suggestions would be appreciated.

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

Re: [sqlite] UNIQUE PRIMARY KEY on TEXT column?

[Igor Tandetnik]

Keith Roberts  wrote:
> I have the following table to keep a count of the number of
> times files are downloaded from my website:
> 
> CREATE TABLE "file_downloads"
>  (
>  "record_id" INTEGER PRIMARY KEY,
>  "filename" char(60),
>  "dl_count" INTEGER
>   /* PRIMARY KEY  ("record_id") */
> );
> 
> The select statement to get the current download count for a
> file is:
> 
> SELECT *
> from file_downloads
> where filename = "$dl_file";
> 
> And the update statement to update the dl_count column is:
> 
> UPDATE file_downloads set
> dl_count = "$new_count"
> where filename = "$dl_file";
> 
> Would this be an efficient way of doing things?
> 
> I don't expect to be keeping track of more than 200 files
> for downloading.

Not really efficient, but with 200 records, you won't notice.

> I was wondering if it is possible to setup the filename
> column as a UNIQUE PRIMARY KEY on a TEXT column.

It's UNIQUE or PRIMARY KEY: they don't go together. Yes, you could do either. 
There could be only one PRIMARY KEY in a table, and you already have one, so 
you'll have to choose which one you want. On the other hand, you could have an 
unlimited number of UNIQUE constraints.
-- 
Igor Tandetnik

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

[sqlite] UNIQUE PRIMARY KEY on TEXT column?

[Keith Roberts]

Hi all.

I have the following table to keep a count of the number of 
times files are downloaded from my website:

CREATE TABLE "file_downloads"
  (
  "record_id" INTEGER PRIMARY KEY,

  "filename" char(60),

  "dl_count" INTEGER

   /* PRIMARY KEY  ("record_id") */
);


The select statement to get the current download count for a 
file is:

SELECT *
from file_downloads
where filename = "$dl_file";

And the update statement to update the dl_count column is:

UPDATE file_downloads set
dl_count = "$new_count"
where filename = "$dl_file";

Would this be an efficient way of doing things?

I don't expect to be keeping track of more than 200 files 
for downloading.

I was wondering if it is possible to setup the filename 
column as a UNIQUE PRIMARY KEY on a TEXT column. Is that 
possible? I don't need FTS capabilities, just want to make 
the filename column a UNIQUE column, so there is only one 
row with that particular filename.

Kind Regards,

Keith Roberts

-
Websites:
http://www.karsites.net
http://www.php-debuggers.net
http://www.raised-from-the-dead.org.uk

All email addresses are challenge-response protected with
TMDA [http://tmda.net]
-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] two table problem.

[Simon Davies]

On 16 September 2010 20:15, Chris Bruner  wrote:
>  I'm stuck with what should be a simple problem, but I can't move past it.
> Two tables, I'm trying to update one tables latitude with the other
> tables latitude where the zipcode match. (See below)
> Can anybody help me? Thanks in advance.
>
>
.
.
.
>
> sqlite> update agents set latitude=Latitude where agents.latitude=0 and
> agents.Z
> IP=zip.ZipCode;
> Error: no such column: zip.ZipCode

update agents set latitude=coalesce( (select latitude from zip where
ZipCode=agents.zip), latitude ) where latitude=0;

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

[sqlite] two table problem.

[Chris Bruner]

  I'm stuck with what should be a simple problem, but I can't move past it.
Two tables, I'm trying to update one tables latitude with the other 
tables latitude where the zipcode match. (See below)
Can anybody help me? Thanks in advance.


sqlite> .schema
CREATE TABLE agents("ZIP" text ,"CUSTOMER" text ,"PHONE" text ,"CITY" 
text ,"WEB
SITE" text ,"EMAIL"
, latitude double default 0, logitude double default 0);
CREATE TABLE zip(ZipCode text ,PrimaryRecord text ,Population text 
,HouseholdsPe
rZipCode text ,WhitePopulation text ,BlackPopulation text 
,HispanicPopulation te
xt ,AsianPopulation text ,HawaiianPopulation text ,IndianPopulation text 
,OtherP
opulation text ,MalePopulation text ,FemalePopulation text 
,PersonsPerHousehold
text ,AverageHouseValue text ,IncomePerHousehold text ,Latitude text 
,Longitude
text ,Elevation text ,State text ,StateFullName text ,CityType text 
,CityAliasAb
breviation text ,AreaCode text ,City text ,CityAliasName text ,County 
text ,Coun
tyFIPS text ,StateFIPS text ,TimeZone text ,DayLightSaving text ,MSA 
text ,PMSA
text ,CSA text ,CBSA text ,CBSA_Div text ,CBSAType text ,CBSAName text 
,MSAName
text ,PMSAName text ,Region text ,Division text ,MailingName text 
,PreferredLast
LineKey text ,ClassificationCode text ,MultiCounty text ,CSAName text 
,CBSA_Div_
Name text ,CityStateKey text ,CityAliasCode text ,CityMixedCase text 
,CityAliasM
ixedCase text ,StateANSI text ,CountyANSI text ,FacilityCode text 
,CityDeliveryI
ndicator text ,CarrierRouteRateSortation text ,FinanceNumber text 
,UniqueZIPName

);
CREATE INDEX "agentsZIP" on agents("ZIP");
CREATE INDEX zipZipCode on zip(ZipCode);

sqlite> update agents set latitude=Latitude where agents.latitude=0 and 
agents.Z
IP=zip.ZipCode;
Error: no such column: zip.ZipCode
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Building On AIX 6.1 Upgraded From 5.3

[Dr. David Kirkby]

On 09/13/10 09:44 PM, Joshua Gatcomb wrote:
> I am trying to install the perl module DBD::SQLite.  Since it didn't
> work, I figured I would start with SQLite (amalgamation tar ball
> 3.7.2).
>
> I am on AIX 6.1 (upgraded from 5.3) using gcc 4.2.0 which is compiled for 5.3
>
> I have two theories based on some limited research:
>
> 1. I need to use the xlc native AIX compiler (too expensive and is not
> going to happen)

You can get a trial though.

> 2. I need to update gcc to a version compiled for 6.1 (I didn't know
> the box had been upgraded until now)

I'm not sure if that is necessary. When I asked on an AIX list about this, it 
would appear the old gcc built for 5.3 would be ok on 6.1. I assume one would 
need to run the "fixincludes/fix-includes" program though, to fix the include 
files.

> If anyone has any pointers or can confirm my guesses above I would be
> very appreciative.

Did this work on AIX 5.3 for you? I'm not in the position now, but will soon 
have my AIX 5.3 box running. But my RS/6000 7025 F50 is ancient - only 32-bit, 
so 5.3 is the latest release of AIX I can run.

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

Re: [sqlite] Will a read-only transaction cause writes to the WAL?

[Martin C.]

Hi,

thanks for the clarification. I may need to add a custom
implementation of the VFS or a patch for the -shm files to be held on
memory-disk then.

Regarding the question, why I want to wrap reads in a transaction is,
because I don't know if there'll be a write as part of the transaction
in before. That partly depends on results returned from the query.

Thanks for your answers!

Best regards,
Martin

On Thu, Sep 16, 2010 at 9:14 PM, Richard Hipp  wrote:
> Reads to not write to the WAL.  But they might write to the shared-memory.
>
> On Thu, Sep 16, 2010 at 1:16 PM, Martin C.  wrote:
>
>> Hi,
>>
>> I am using SQLite on a NAND based embedded system, and to ensure the
>> lifetime of the NAND I need to optimize writes to the NAND.
>>
>> Can you tell me, if a read-only transaction, by which I mean
>>   BEGIN TRANSACTION
>>   SELECT * from SomeTable;
>>   COMMIT
>> will cause any writes to the WAL?
>>
>> What about the -shm file, is it likely that the transaction will cause
>> a write to the NAND in this case?
>>
>> Is there any statistics about actual writes I can query to analyze
>> write-behavior of my software?
>>
>> Thanks in advance!
>>
>> Best regards,
>> Martin
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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