Re: [sqlite] Defining a table that starts rowid as 0

[John Machin]

On 24/07/2009 3:10 PM, Simon Slavin wrote:
> On 24 Jul 2009, at 5:49am, John Machin wrote:
> 
>> On 24/07/2009 3:22 AM, Simon Slavin wrote:
>>
>>> And note that if you have a column which is an integer that
>>> has doesn't allow duplicates, SQLite will automatically use that
>>> column as the one it uses for _rowid_, etc..  So define your own
>>> integer column, feed it whatever integers you want, and you won't
>>> waste any space.
>> Documentation reference, please.
> 
> I'm sorry.  I think that instead of 'integer unique' what I meant was  
> 'autoincrement'.  It's in the section of the documentation about row  
> ids.  I can't get at the web right now.

Perhaps not:

sqlite> create table bar(x text, y integer autoincrement);
SQL error: near "autoincrement": syntax error
sqlite> create table bar(x text, y autoincrement);
SQL error: near "autoincrement": syntax error

In any case 'autoincrement' doesn't correlate with "feed it whatever 
integers you want".

Perhaps you meant 'integer primary key':

sqlite> create table bar(x text, y integer primary key);
sqlite> insert into bar values('aaa', 1);
sqlite> insert into bar values('bbb', 42);
sqlite> insert into bar values('ccc', 666);
sqlite> select rowid, * from bar;
1|aaa|1
42|bbb|42
666|ccc|666
sqlite>


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

Re: [sqlite] Defining a table that starts rowid as 0

[Simon Slavin]

On 24 Jul 2009, at 5:49am, John Machin wrote:

> On 24/07/2009 3:22 AM, Simon Slavin wrote:
>
>> And note that if you have a column which is an integer that
>> has doesn't allow duplicates, SQLite will automatically use that
>> column as the one it uses for _rowid_, etc..  So define your own
>> integer column, feed it whatever integers you want, and you won't
>> waste any space.
>
> Documentation reference, please.

I'm sorry.  I think that instead of 'integer unique' what I meant was  
'autoincrement'.  It's in the section of the documentation about row  
ids.  I can't get at the web right now.

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

Re: [sqlite] Defining a table that starts rowid as 0

[John Machin]

On 24/07/2009 3:22 AM, Simon Slavin wrote:

> And note that if you have a column which is an integer that  
> has doesn't allow duplicates, SQLite will automatically use that  
> column as the one it uses for _rowid_, etc..  So define your own  
> integer column, feed it whatever integers you want, and you won't  
> waste any space.

Documentation reference, please.

How do you account for this:

SQLite version 3.6.14
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table bar(x text, y integer unique);
sqlite> insert into bar values('aaa', 1);
sqlite> insert into bar values('bbb', 42);
sqlite> insert into bar values('ccc', 666);
sqlite> select rowid, * from bar;
1|aaa|1
2|bbb|42
3|ccc|666
sqlite> insert into bar values('ddd', 42);
SQL error: column y is not unique
sqlite>

?

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

[sqlite] SQLite OLEDB provider for Linked Server

[Curtis Whitworth]

We would like to setup a linked server from SQL2000, SQL2005, and
SQL2008 systems to connect and query a SQLite db.  Has anyone ever
accomplished this??  How would we query a SQLite db from SQL server??

 

We have tried to use the Cherry City Software OLEDB provider but it
won't let us activate the locked portion of the code.  And with their
limited resources, it doesn't appear we'll get this resolved with
them...

 

When we execute this syntax: 

select * from openquery (WOLFC, 'select * from category')

 

It returns:

Server: Msg 7321, Level 16, State 2, Line 1

An error occurred while preparing a query for execution against OLE DB
provider 'OleSQLite.SQLiteSource.1'. 

[OLE/DB provider returned message: This feature is currently locked. In
order to unlock this feature, SQLite OLE DB Provider must be activated.]

OLE DB error trace [OLE/DB Provider 'OleSQLite.SQLiteSource.1'
ICommandPrepare::Prepare returned 0x800404e8].

 

Thanks,

 

Curtis Whitworth

 

Pilgrim's Pride - Atlanta

770-393-5488 (Direct) 770-355-6721 (Cell)



**

The information contained in this e-mail (along with any attachments) is 
intended only for the use of the individual(s) to whom it is addressed.  It is 
confidential and may contain privileged information. If the reader of this 
message is not the intended recipient, you are hereby notified that you should 
not read its contents, and any dissemination, distribution, or copying of this 
communication is strictly prohibited. If you have received it in error, please 
immediately (1) delete this transmission and any attachments and (2) notify 
i...@pilgrimspride.com to advise us of the error.  THIS E-MAIL IS NOT AN OFFER 
OR ACCEPTANCE: Notwithstanding the Uniform Electronic Transactions Act or any 
other law of similar import, absent an express statement to the contrary 
contained in this e-mail, neither this e-mail nor any attachments are an offer 
or acceptance to enter into a contract, and are not intended to bind the 
sender, Pilgrim’s Pride Corporation, or any of its subsidiaries, or any other 
person or entity.

**

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

Re: [sqlite] Installing SQLite

[Hamish Allan]

On Thu, Jul 23, 2009 at 9:03 PM, Rich Shepard wrote:

>   Er, Dan, ... without the build step there's nothing to install.

A well-formed Makefile should specify the default build target as a
dependency of the install target.

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

Re: [sqlite] Installing SQLite

[Rich Shepard]

On Fri, 24 Jul 2009, Dan wrote:

> There is an amalgamation package that includes a supported configure
> script. "./configure && make install" and you're away.

   Er, Dan, ... without the build step there's nothing to install. The usual
sequence is ./configure && make && make install. Or, you build a package for
your distribution.

Rich

-- 
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Installing SQLite

[Fred Williams]

That might be an option to consider.  But, I think there might be a rather
small number of potential SQLite users interested.  I would think the group
with the greatest benefit of that kind of package would be students and
serious rookies.

Most users I feel either pre possess the required expertise to work with the
current source package or, like me, prefer to leave the implementation
details to those supplying the pre packaged .DLL's.  I could work from
source, but why bother?  I guess I'm getting lazy or am too willing to
accept what is prepackaged.

My main purpose is to utilize a small tightly implemented database that I do
not have to deal with below the prepackaged level.  If that was not possible
with SQLite, I would still be looking I suppose.  I would suspect that the
majority of users are pretty much in my camp.  I'll go so far as to say
20/80, the golden rule of most everything in life.  With 20 being the Gurus
or wana'bes and 80 the rest of us.

BTW, I'll go further and say that fewer than 20 of the above 80 even know
about this mailing list ;-)

Dr. Hipp may be able to back this up with some general numbers if he wishes
to totally waste his time...

Fred

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org]on Behalf Of Vance E. Neff
Sent: Thursday, July 23, 2009 1:06 PM
To: j...@kreibi.ch; General Discussion of SQLite Database
Subject: Re: [sqlite] Installing SQLite


If there was a zip file that included an open source compiler and linker
and a well commented makefile along with SQLite's source code so that
anyone (at least under Windows) can generate the version SQLite dll and
command shell that they want without having to search for tools, I think
more people would be more likely to experiment with some of the non
standard features.

Vance

Jay A. Kreibich wrote:
>   Grrr I didn't meant to send this just yet.  But since I did, I
>   guess I need to finish it.
>
> On Thu, Jul 23, 2009 at 10:50:37AM -0500, Jay A. Kreibich scratched on the
wall:
>> On Thu, Jul 23, 2009 at 09:46:24AM -0400, Wilson, Ron P scratched on the
wall:
>>
>>> I think the OP just has the wrong expectations.
>>   Yes, and no.  While SQLite doesn't have a one-click-to-install
>>   download, I have to agree that the current build and distribution
>>   state of SQLite is... let's just say "less than ideal."  I've been
>>   writing a lot of documentation on just this issue, and unless you
>>   want a perfect vanilla install, there are definitely a lot of hoops
>>   you have to jump through compared to most open-source projects of
>>   similar design.
>>
>>   A few versions ago we transitioned from a traditional UNIX style
>>   project, complete with "configure" script, to having the amalgamation
>>   be the "standard" distribution.  I've always felt like that
>>   transition is incomplete, and we've never gotten back to where we
>>   were before.
>>
>>   The amalgamation works well enough if what you want is mostly
>>   defaults.  The issue is that, while you can change a few of the
>>   #defines for numeric defaults, most of the more interesting build
>>   options won't work with the amalgamation.  Only that's it.  As the
>>   website clearly states, there is no other supported option.
>
>   The "by the file" distribution is bad enough, but you're totally out
>   of luck if you need to go to the tree for some of the really complex
>   build options.  Of course, the "by the file" distribution is there
>   and available for download because a lot of people still need it, but
>   apparently not enough to justify keeping it updated.  That's a bit of
>   a contradiction... for a piece of software that prides itself on its
>   testing systems, the end-users sees a whole lot of "there but not
>   supported; it might work it might not; you're on your own" stuff.
>   That's normally a big red flag in my book.  Stuff should be there, be
>   supported, and be documented, or it shouldn't.  The current situation
>   is only easily understandable if you've been following SQLite for a few
>   years.
>
>   And the OP is right... the build docs suck.  There are no build docs
>   for most downloads, just a archive file with source.  No Makefiles, no
>   nothing.  There is no "how to build" on the documentation page (just
>   docs on build options) and the Wiki pages on building and the
>   amalgamation are so out of date that they have negative value, doing
>   little more than confusing people.
>
>   I realize that you might take the argument that anyone smart enough
>   need to build the SQLite engine into an application should be able to
>   figure that out, and with enough time, maybe that's right a fair
>   percentage of the time.  On the other hand, the whole point of moving
>   to the amalgamation was to simplify putting SQLite into an
>   application.  I think overall it does this, but only if you're
>   working from the default everything.  The amalgamation might be
>   

Re: [sqlite] Installing SQLite

[Dan]

On Jul 23, 2009, at 10:50 PM, Jay A. Kreibich wrote:

> On Thu, Jul 23, 2009 at 09:46:24AM -0400, Wilson, Ron P scratched on  
> the wall:
>
>> I think the OP just has the wrong expectations.
>
>  Yes, and no.  While SQLite doesn't have a one-click-to-install
>  download, I have to agree that the current build and distribution
>  state of SQLite is... let's just say "less than ideal."  I've been
>  writing a lot of documentation on just this issue, and unless you
>  want a perfect vanilla install, there are definitly a lot of hoops
>  you have to jump through compared to most open-source projects of
>  similar design.
>
>  A few versions ago we transitioned from a traditional UNIX style
>  project, complete with "configure" script, to having the amalgimation
>  be the "standard" distribution.  I've always felt like that
>  transition is imcomplete, and we've never gotten back to where we
>  were before.
>
>  The amalgimation works well enough if what you want is mostly
>  defaults.  The issue is that, while you can change a few of the
>  #defines for numric defaults, most of the more interesting build
>  options won't work with the amalgimation.  Only that's it.  As the
>  website clearly states, there is no other supported option.
>  no suppored "by the file"

I think it's just the SQLITE_ENABLE_UPDATE_DELETE_LIMIT feature and
some of the SQLITE_OMIT_XXX symbols that won't work with the  
amalgamation
version. I haven't tried though, so could be wrong there.

There is an amalgamation package that includes a supported configure
script. "./configure && make install" and you're away. You can specify
compile time options via the CFLAGS environment variable.

The problem with the configure script in the full source tree
distribution is that it is complex and nobody really seems to understand
it. Hence "unsupported".
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Installing SQLite

[Vance E. Neff]

If there was a zip file that included an open source compiler and linker 
and a well commented makefile along with SQLite's source code so that 
anyone (at least under Windows) can generate the version SQLite dll and 
command shell that they want without having to search for tools, I think 
more people would be more likely to experiment with some of the non 
standard features.

Vance

Jay A. Kreibich wrote:
>   Grrr I didn't meant to send this just yet.  But since I did, I
>   guess I need to finish it.
> 
> On Thu, Jul 23, 2009 at 10:50:37AM -0500, Jay A. Kreibich scratched on the 
> wall:
>> On Thu, Jul 23, 2009 at 09:46:24AM -0400, Wilson, Ron P scratched on the 
>> wall:
>>
>>> I think the OP just has the wrong expectations.
>>   Yes, and no.  While SQLite doesn't have a one-click-to-install
>>   download, I have to agree that the current build and distribution
>>   state of SQLite is... let's just say "less than ideal."  I've been
>>   writing a lot of documentation on just this issue, and unless you
>>   want a perfect vanilla install, there are definitely a lot of hoops
>>   you have to jump through compared to most open-source projects of
>>   similar design.
>>   
>>   A few versions ago we transitioned from a traditional UNIX style
>>   project, complete with "configure" script, to having the amalgamation
>>   be the "standard" distribution.  I've always felt like that
>>   transition is incomplete, and we've never gotten back to where we
>>   were before.
>>
>>   The amalgamation works well enough if what you want is mostly
>>   defaults.  The issue is that, while you can change a few of the
>>   #defines for numeric defaults, most of the more interesting build
>>   options won't work with the amalgamation.  Only that's it.  As the
>>   website clearly states, there is no other supported option.  
> 
>   The "by the file" distribution is bad enough, but you're totally out
>   of luck if you need to go to the tree for some of the really complex
>   build options.  Of course, the "by the file" distribution is there
>   and available for download because a lot of people still need it, but
>   apparently not enough to justify keeping it updated.  That's a bit of 
>   a contradiction... for a piece of software that prides itself on its
>   testing systems, the end-users sees a whole lot of "there but not
>   supported; it might work it might not; you're on your own" stuff.
>   That's normally a big red flag in my book.  Stuff should be there, be
>   supported, and be documented, or it shouldn't.  The current situation
>   is only easily understandable if you've been following SQLite for a few
>   years.
> 
>   And the OP is right... the build docs suck.  There are no build docs
>   for most downloads, just a archive file with source.  No Makefiles, no
>   nothing.  There is no "how to build" on the documentation page (just
>   docs on build options) and the Wiki pages on building and the
>   amalgamation are so out of date that they have negative value, doing
>   little more than confusing people.
> 
>   I realize that you might take the argument that anyone smart enough
>   need to build the SQLite engine into an application should be able to
>   figure that out, and with enough time, maybe that's right a fair
>   percentage of the time.  On the other hand, the whole point of moving
>   to the amalgamation was to simplify putting SQLite into an
>   application.  I think overall it does this, but only if you're
>   working from the default everything.  The amalgamation might be
>   easier or better (for some definition of those terms) if you
>   understand it, but it is radically different from nearly every other
>   open source project out there.  A bit of hand holding, even for
>   experienced developers, is not out of place.
> 
>   SQLite is a great product, but there is still a lot of room for
>   improvement on the packaging, distribution, and "productization"
>   of the code.
> 
> 
>   And even if his post was a bit negative, I'm a little disappointed at
>   the community response.  We're here to help, not criticize.  If that
>   was my introduction to the product and its users, you can be sure I
>   wouldn't be coming back.  If you're not going to try to add something
>   to the conversation, there is no obligation to hit reply.
> 
>-j
> 
> 

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

Re: [sqlite] Defining a table that starts rowid as 0

[P Kishor]

On Thu, Jul 23, 2009 at 12:39 PM, Shaun Seckman
(Firaxis) wrote:
> I currently have all my tables with a column called "ID" that is defined
> as Integer Primary Key. For legacy code purposes, I need ID to start at
> 0 and not at 1 however I'd like to have this be defined as part of the
> table schema and not as part of the insertion statement.  I tried
> defining it as "integer primary key default 0" but that didn't work.
> Any other ideas?

INSERT the first row explicitly, and it will work thereafter

sqlite> CREATE TABLE foo (id INTEGER PRIMARY KEY, bar TEXT);
sqlite> INSERT INTO foo (id, bar) VALUES (0, 'test');
sqlite> INSERT INTO foo (bar) VALUES ('more');
sqlite> SELECT * FROM foo;
0|test
1|more
sqlite> SELECT rowid, * FROM foo;
0|0|test
1|1|more
sqlite>

>
> -Shaun
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Jay A. Kreibich
> Sent: Thursday, July 23, 2009 1:31 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Defining a table that starts rowid as 0
>
> On Thu, Jul 23, 2009 at 06:22:53PM +0100, Simon Slavin scratched on the
> wall:
>>
>> On 23 Jul 2009, at 4:56pm, Rich Shepard wrote:
>>
>> >   Using rowid for anything is not a good idea. There's no guarantee
>
>> > that the
>> > column values associated with each rowid are static. It's much
>> > better to
>> > ignore the rowid and use either a natural primary key or a defined
>> > one.
>>
>> Agreed.  And note that if you have a column which is an integer that
>> has doesn't allow duplicates,
>
>  The column has to very specifically be defined "INTEGER PRIMARY KEY".
>  "INT UNIQUE" won't cover it.
>
>  http://sqlite.org/lang_createtable.html#rowid
>
>  -j
>
> --
> Jay A. Kreibich < J A Y  @  K R E I B I.C H >
>
> "Our opponent is an alien starship packed with atomic bombs.  We have
>  a protractor."   "I'll go home and see if I can scrounge up a ruler
>  and a piece of string."  --from Anathem by Neal Stephenson
> ___
> 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
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
Sent from Madison, WI, United States
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Defining a table that starts rowid as 0

[Shaun Seckman (Firaxis)]

I currently have all my tables with a column called "ID" that is defined
as Integer Primary Key. For legacy code purposes, I need ID to start at
0 and not at 1 however I'd like to have this be defined as part of the
table schema and not as part of the insertion statement.  I tried
defining it as "integer primary key default 0" but that didn't work.
Any other ideas?

-Shaun

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Jay A. Kreibich
Sent: Thursday, July 23, 2009 1:31 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Defining a table that starts rowid as 0

On Thu, Jul 23, 2009 at 06:22:53PM +0100, Simon Slavin scratched on the
wall:
> 
> On 23 Jul 2009, at 4:56pm, Rich Shepard wrote:
> 
> >   Using rowid for anything is not a good idea. There's no guarantee

> > that the
> > column values associated with each rowid are static. It's much  
> > better to
> > ignore the rowid and use either a natural primary key or a defined  
> > one.
> 
> Agreed.  And note that if you have a column which is an integer that  
> has doesn't allow duplicates, 

  The column has to very specifically be defined "INTEGER PRIMARY KEY". 
  "INT UNIQUE" won't cover it.

  http://sqlite.org/lang_createtable.html#rowid

  -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
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] Defining a table that starts rowid as 0

[Jay A. Kreibich]

On Thu, Jul 23, 2009 at 06:22:53PM +0100, Simon Slavin scratched on the wall:
> 
> On 23 Jul 2009, at 4:56pm, Rich Shepard wrote:
> 
> >   Using rowid for anything is not a good idea. There's no guarantee  
> > that the
> > column values associated with each rowid are static. It's much  
> > better to
> > ignore the rowid and use either a natural primary key or a defined  
> > one.
> 
> Agreed.  And note that if you have a column which is an integer that  
> has doesn't allow duplicates, 

  The column has to very specifically be defined "INTEGER PRIMARY KEY". 
  "INT UNIQUE" won't cover it.

  http://sqlite.org/lang_createtable.html#rowid

  -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Defining a table that starts rowid as 0

[Simon Slavin]

On 23 Jul 2009, at 4:56pm, Rich Shepard wrote:

>   Using rowid for anything is not a good idea. There's no guarantee  
> that the
> column values associated with each rowid are static. It's much  
> better to
> ignore the rowid and use either a natural primary key or a defined  
> one.

Agreed.  And note that if you have a column which is an integer that  
has doesn't allow duplicates, SQLite will automatically use that  
column as the one it uses for _rowid_, etc..  So define your own  
integer column, feed it whatever integers you want, and you won't  
waste any space.

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

Re: [sqlite] Installing SQLite

[Jim Showalter]

Jay, your email below is superb. It's dispassionate, accurate, 
diplomatic, and informative.

I was also disappointed to see this email list go the way of so many 
others over the past day or so, but it's easy enough to fix it. Stop 
calling someone else a doofus for selecting a particular OS to work 
with, stop using the word "sucks" to describe a deficiency in the 
build system and instead offer constructive criticism and suggestions 
for improvement (as Jay does below), and stop calling people trolls 
even if their email is a bit of a flame.

In the short time I've subscribed to this list, I've been amazed at 
the technical acumen of the people who reply to questions. The replies 
I've gotten for my questions--even stupid ones--have been very 
helpful, and some of the in-depth discussions of particular features 
(like iterating over chunks of blobs) are fascinating.

- Original Message - 
From: "Jay A. Kreibich" 
To: "Jay A. Kreibich" 
Cc: "General Discussion of SQLite Database" 
Sent: Thursday, July 23, 2009 9:07 AM
Subject: Re: [sqlite] Installing SQLite


>
>  Grrr I didn't meant to send this just yet.  But since I did, I
>  guess I need to finish it.
>
> On Thu, Jul 23, 2009 at 10:50:37AM -0500, Jay A. Kreibich scratched 
> on the wall:
>> On Thu, Jul 23, 2009 at 09:46:24AM -0400, Wilson, Ron P scratched 
>> on the wall:
>>
>> > I think the OP just has the wrong expectations.
>>
>>   Yes, and no.  While SQLite doesn't have a one-click-to-install
>>   download, I have to agree that the current build and distribution
>>   state of SQLite is... let's just say "less than ideal."  I've 
>> been
>>   writing a lot of documentation on just this issue, and unless you
>>   want a perfect vanilla install, there are definitely a lot of 
>> hoops
>>   you have to jump through compared to most open-source projects of
>>   similar design.
>>
>>   A few versions ago we transitioned from a traditional UNIX style
>>   project, complete with "configure" script, to having the 
>> amalgamation
>>   be the "standard" distribution.  I've always felt like that
>>   transition is incomplete, and we've never gotten back to where we
>>   were before.
>>
>>   The amalgamation works well enough if what you want is mostly
>>   defaults.  The issue is that, while you can change a few of the
>>   #defines for numeric defaults, most of the more interesting build
>>   options won't work with the amalgamation.  Only that's it.  As 
>> the
>>   website clearly states, there is no other supported option.
>
>  The "by the file" distribution is bad enough, but you're totally 
> out
>  of luck if you need to go to the tree for some of the really 
> complex
>  build options.  Of course, the "by the file" distribution is there
>  and available for download because a lot of people still need it, 
> but
>  apparently not enough to justify keeping it updated.  That's a bit 
> of
>  a contradiction... for a piece of software that prides itself on 
> its
>  testing systems, the end-users sees a whole lot of "there but not
>  supported; it might work it might not; you're on your own" stuff.
>  That's normally a big red flag in my book.  Stuff should be there, 
> be
>  supported, and be documented, or it shouldn't.  The current 
> situation
>  is only easily understandable if you've been following SQLite for a 
> few
>  years.
>
>  And the OP is right... the build docs suck.  There are no build 
> docs
>  for most downloads, just a archive file with source.  No Makefiles, 
> no
>  nothing.  There is no "how to build" on the documentation page 
> (just
>  docs on build options) and the Wiki pages on building and the
>  amalgamation are so out of date that they have negative value, 
> doing
>  little more than confusing people.
>
>  I realize that you might take the argument that anyone smart enough
>  need to build the SQLite engine into an application should be able 
> to
>  figure that out, and with enough time, maybe that's right a fair
>  percentage of the time.  On the other hand, the whole point of 
> moving
>  to the amalgamation was to simplify putting SQLite into an
>  application.  I think overall it does this, but only if you're
>  working from the default everything.  The amalgamation might be
>  easier or better (for some definition of those terms) if you
>  understand it, but it is radically different from nearly every 
> other
>  open source project out there.  A bit of hand holding, even for
>  experienced developers, is not out of place.
>
>  SQLite is a great product, but there is still a lot of room for
>  improvement on the packaging, distribution, and "productization"
>  of the code.
>
>
>  And even if his post was a bit negative, I'm a little disappointed 
> at
>  the community response.  We're here to help, not criticize.  If 
> that
>  was my introduction to the product and its users, you can be sure I
>  wouldn't be coming back.  If you're not going to 

Re: [sqlite] sqlite_mem_methods

[Dan]

On Jul 23, 2009, at 11:16 PM, Zachary Turner wrote:

> I was looking into overriding the sqlite_mem_methods interface to
> specify my own allocator.  The two functions I have a question about
> are these:
>
> int (*xSize)(void*)
> int (*xRoundup)(int size);
>
> I've briefly looked over the source code it *seems* like I can safely
> return 0 from xSize and 'size' from xRoundup, with the only effects
> being that functions such as sqlite_mem_used() will be useless.  Are
> there any more subtle implications of implementing these functions in
> the aforementioned way that I'm missing?

Yes. Returning a copy of the 'size' parameter from xRoundup() will work,
but there are at least a few places in the code that depend on a working
sqlite3DbMallocSize(). Which sometimes needs to call xSize().

> the problem is that I have a 3rd party allocator that is extremely
> fast and implements malloc, free, and realloc, but does not implement
> the other functions.  I can probably implement xRoundup if necessary
> by figuring out their allocation / alignment algorithm and
> re-implementing the alignment portion, but xSize I don't think will be
> possible to implement at all.

Write a wrapper that allocates the requested size plus an 8-byte header
each time an allocation is requested. Use the header space to store the
size of the allocation.

Dan.


> ___
> 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] sqlite_mem_methods

[Zachary Turner]

I was looking into overriding the sqlite_mem_methods interface to
specify my own allocator.  The two functions I have a question about
are these:

int (*xSize)(void*)
int (*xRoundup)(int size);

I've briefly looked over the source code it *seems* like I can safely
return 0 from xSize and 'size' from xRoundup, with the only effects
being that functions such as sqlite_mem_used() will be useless.  Are
there any more subtle implications of implementing these functions in
the aforementioned way that I'm missing?

the problem is that I have a 3rd party allocator that is extremely
fast and implements malloc, free, and realloc, but does not implement
the other functions.  I can probably implement xRoundup if necessary
by figuring out their allocation / alignment algorithm and
re-implementing the alignment portion, but xSize I don't think will be
possible to implement at all.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Installing SQLite

[Jay A. Kreibich]

  Grrr I didn't meant to send this just yet.  But since I did, I
  guess I need to finish it.

On Thu, Jul 23, 2009 at 10:50:37AM -0500, Jay A. Kreibich scratched on the wall:
> On Thu, Jul 23, 2009 at 09:46:24AM -0400, Wilson, Ron P scratched on the wall:
> 
> > I think the OP just has the wrong expectations.
> 
>   Yes, and no.  While SQLite doesn't have a one-click-to-install
>   download, I have to agree that the current build and distribution
>   state of SQLite is... let's just say "less than ideal."  I've been
>   writing a lot of documentation on just this issue, and unless you
>   want a perfect vanilla install, there are definitely a lot of hoops
>   you have to jump through compared to most open-source projects of
>   similar design.
>   
>   A few versions ago we transitioned from a traditional UNIX style
>   project, complete with "configure" script, to having the amalgamation
>   be the "standard" distribution.  I've always felt like that
>   transition is incomplete, and we've never gotten back to where we
>   were before.
> 
>   The amalgamation works well enough if what you want is mostly
>   defaults.  The issue is that, while you can change a few of the
>   #defines for numeric defaults, most of the more interesting build
>   options won't work with the amalgamation.  Only that's it.  As the
>   website clearly states, there is no other supported option.  

  The "by the file" distribution is bad enough, but you're totally out
  of luck if you need to go to the tree for some of the really complex
  build options.  Of course, the "by the file" distribution is there
  and available for download because a lot of people still need it, but
  apparently not enough to justify keeping it updated.  That's a bit of 
  a contradiction... for a piece of software that prides itself on its
  testing systems, the end-users sees a whole lot of "there but not
  supported; it might work it might not; you're on your own" stuff.
  That's normally a big red flag in my book.  Stuff should be there, be
  supported, and be documented, or it shouldn't.  The current situation
  is only easily understandable if you've been following SQLite for a few
  years.

  And the OP is right... the build docs suck.  There are no build docs
  for most downloads, just a archive file with source.  No Makefiles, no
  nothing.  There is no "how to build" on the documentation page (just
  docs on build options) and the Wiki pages on building and the
  amalgamation are so out of date that they have negative value, doing
  little more than confusing people.

  I realize that you might take the argument that anyone smart enough
  need to build the SQLite engine into an application should be able to
  figure that out, and with enough time, maybe that's right a fair
  percentage of the time.  On the other hand, the whole point of moving
  to the amalgamation was to simplify putting SQLite into an
  application.  I think overall it does this, but only if you're
  working from the default everything.  The amalgamation might be
  easier or better (for some definition of those terms) if you
  understand it, but it is radically different from nearly every other
  open source project out there.  A bit of hand holding, even for
  experienced developers, is not out of place.

  SQLite is a great product, but there is still a lot of room for
  improvement on the packaging, distribution, and "productization"
  of the code.


  And even if his post was a bit negative, I'm a little disappointed at
  the community response.  We're here to help, not criticize.  If that
  was my introduction to the product and its users, you can be sure I
  wouldn't be coming back.  If you're not going to try to add something
  to the conversation, there is no obligation to hit reply.

   -j


-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Defining a table that starts rowid as 0

[Rich Shepard]

On Thu, 23 Jul 2009, Shaun Seckman (Firaxis) wrote:

>Is it possible in the create a table where the rowid
> will start at 0 instead of 1 prior to inserting a row and explicitly
> stating that the rowid is 0?

Shaun,

   Using rowid for anything is not a good idea. There's no guarantee that the
column values associated with each rowid are static. It's much better to
ignore the rowid and use either a natural primary key or a defined one.

Rich

-- 
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Installing SQLite

[Jay A. Kreibich]

On Thu, Jul 23, 2009 at 09:46:24AM -0400, Wilson, Ron P scratched on the wall:

> I think the OP just has the wrong expectations.

  Yes, and no.  While SQLite doesn't have a one-click-to-install
  download, I have to agree that the current build and distribution
  state of SQLite is... let's just say "less than ideal."  I've been
  writing a lot of documentation on just this issue, and unless you
  want a perfect vanilla install, there are definitly a lot of hoops
  you have to jump through compared to most open-source projects of
  similar design.
  
  A few versions ago we transitioned from a traditional UNIX style
  project, complete with "configure" script, to having the amalgimation
  be the "standard" distribution.  I've always felt like that
  transition is imcomplete, and we've never gotten back to where we
  were before.

  The amalgimation works well enough if what you want is mostly
  defaults.  The issue is that, while you can change a few of the
  #defines for numric defaults, most of the more interesting build
  options won't work with the amalgimation.  Only that's it.  As the
  website clearly states, there is no other supported option.  
  no suppored "by the file" 


> SQlite is not a
> windows application you install like MS Word or Firefox.  It is a
> code library that you embed into other code.  It is possible to
> use it in a 'stand alone' manner by using the command line
> utility, but I'm 99% sure this will not meet the OP's expectations
> either.



-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Installing SQLite

[Fred Williams]

I think you guys are most likely feeding a Troll, or the original poster
might ought to contact his IS support department, or enroll in some
introductory basic home computer continuing education courses in his local
area.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org]on Behalf Of John Machin
Sent: Thursday, July 23, 2009 10:02 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Installing SQLite


On 23/07/2009 6:48 AM, Rich Shepard wrote:
> On Wed, 22 Jul 2009, scabral wrote:
>
>> When i download the sqlite-amalgamation-3_6_16.zip i get 3 text files:
>>
>> sqlite3   C File
>> sqlite3   H File
>> sqlite3ext H File
>
>> what am i supposed to do with those?
>
>  Well, based on what others wrote about your initial comments, I suggest
> that you replace XP with a linux distribution. Then you can compile that
> source code all by yourself.

No need. One can compile sqlite on Windows all by ones's own self using
a variety of $-free C/C++ compilers (gcc/mingw32 (FOSS), Borland, and
there's a command-line compiler somewhere inside the 100Mb VS2009
Express download from the dark tower of Redmond).

> On the other hand, if you insist on sticking
> with Microsoft, download one of the pre-built Winduhs .zip files as I
> indicated in my previous message.

The pre-built Windows .zip files are paralleled one-to-one by pre-built
linux .gz files ... someone must consider that inability to compile
one's way out of a wet paper bag is platform-independent :-)
___
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] Defining a table that starts rowid as 0

[Igor Tandetnik]

Shaun Seckman (Firaxis)
 wrote:
>Is it possible in the create a table where the rowid
> will start at 0 instead of 1 prior to inserting a row and explicitly
> stating that the rowid is 0?

You can specify rowid explicitly in an insert statement: insert into 
mytable(rowid, ...) values (0, ...)

Igor Tandetnik 



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

[sqlite] Defining a table that starts rowid as 0

[Shaun Seckman (Firaxis)]

Hello,

Is it possible in the create a table where the rowid
will start at 0 instead of 1 prior to inserting a row and explicitly
stating that the rowid is 0?

 

-Shaun

 

 

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

Re: [sqlite] Installing SQLite

[John Machin]

On 23/07/2009 6:48 AM, Rich Shepard wrote:
> On Wed, 22 Jul 2009, scabral wrote:
> 
>> When i download the sqlite-amalgamation-3_6_16.zip i get 3 text files:
>>
>> sqlite3   C File
>> sqlite3   H File
>> sqlite3ext H File
> 
>> what am i supposed to do with those?
> 
>  Well, based on what others wrote about your initial comments, I suggest
> that you replace XP with a linux distribution. Then you can compile that
> source code all by yourself.

No need. One can compile sqlite on Windows all by ones's own self using 
a variety of $-free C/C++ compilers (gcc/mingw32 (FOSS), Borland, and 
there's a command-line compiler somewhere inside the 100Mb VS2009 
Express download from the dark tower of Redmond).

> On the other hand, if you insist on sticking
> with Microsoft, download one of the pre-built Winduhs .zip files as I
> indicated in my previous message.

The pre-built Windows .zip files are paralleled one-to-one by pre-built 
linux .gz files ... someone must consider that inability to compile 
one's way out of a wet paper bag is platform-independent :-)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Extending the db file [was: Memory leak on inserting fromsqlite c/c++ interfaces]

[Igor Tandetnik]

Zachary Turner 
wrote:
> Speaking of extending the file, if I know in advance that my database
> is ultimately going to end up being very large, is there any way for
> me to extend the file up front?  Or to specify the increment in which
> sqlite will extend the file when it needs to, rather than just having
> it extend the file by the exact amount needed to satisfy a commit?  It
> seems like this might result in some performance improvements for very
> large databases.

Well, you could try creating a table with a single blob field, inserting 
a record with a large zeroblob(), then dropping the table. Presumably, 
all the pages previously occupied by the blob will be added to free 
list, and reused for future allocations. I haven't tried that myself 
though.

Igor Tandetnik 



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

[sqlite] Extending the db file [was: Memory leak on inserting from sqlite c/c++ interfaces]

[Zachary Turner]

On Wed, Jul 22, 2009 at 3:22 PM, Igor Tandetnik wrote:
> Zachary Turner 
> wrote:
>> I still don't understand the transactions.  For example, I issue a
>> single BEGIN at the start of my application and then insert about
>> 500MB of data through many small inserts (about 4KB each).  During
>> this whole time I never issue a commit.  But the main db file grows
>> very large, and the journal file remains small.  Is sqlite manually
>> forcing commits for me at some fixed threshold?
>
> SQLite uses an undo journal: changes are written to the database file,
> and the journal keeps the original pages. Committing a transaction
> simply means discarding the journal; rolling back means copying saved
> pages from journal back to database file.
>
> Your massive insert operation doesn't modify many pages in the database
> file - it mostly creates new ones by extending the file. For this case,
> the only thing one needs to store in the journal is the original size of
> the database, so that the file could be truncated on rollback. That's
> why the journal file remains the same size even as the main file grows.
>
> Igor Tandetnik

I branched this off into a new thread since the topic is changing slightly.

Speaking of extending the file, if I know in advance that my database
is ultimately going to end up being very large, is there any way for
me to extend the file up front?  Or to specify the increment in which
sqlite will extend the file when it needs to, rather than just having
it extend the file by the exact amount needed to satisfy a commit?  It
seems like this might result in some performance improvements for very
large databases.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Installing SQLite

[Wilson, Ron P]

> Well, based on what others wrote about your initial comments, I suggest
> that you replace XP with a linux distribution. Then you can compile that
> source code all by yourself. On the other hand, if you insist on sticking
> with Microsoft, download one of the pre-built Winduhs .zip files as I
> indicated in my previous message.

SQlite compiles just fine on windows XP.  You just need tools to do it, which 
unlike linux, don't automatically come with the OS.  So there is most likely a 
teaching opportunity here, though admittedly the OP does not appear to be in a 
teachable frame of mind.

I think the OP just has the wrong expectations.  SQlite is not a windows 
application you install like MS Word or Firefox.  It is a code library that you 
embed into other code.  It is possible to use it in a 'stand alone' manner by 
using the command line utility, but I'm 99% sure this will not meet the OP's 
expectations either.

Also, I for one would appreciate it if in the future you avoid making the 
sweeping generalization that "Winduhs" users are dummies or lemmings.  I'm sure 
others would agree.

RW

Ron Wilson, Engineering Project Lead
(o) 434.455.6453, (m) 434.851.1612, www.harris.com

HARRIS CORPORATION   |   RF Communications Division assuredcommunicationsT
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Ability to convert Access to SQLite

[Chris]

Since this appears to be a reporting application with
multiple readers, why not use an HTML report output and
any web browser. Then if you decide to publish the reports
on your network, there is little more needed to do so.

On Wed, 2009-07-22 at 10:12 -0700, scabral wrote:
> Do you know of any third party applications that i could use to build the
> front end?  This will need to run on local machines, not on servers.  So,
> some sort of desktop application i would imagine
> 
> thanks
> Scott
> 
> P Kishor-3 wrote:
> > 
> > On Wed, Jul 22, 2009 at 11:34 AM, scabral wrote:
> >>
> >> HI,
> >>
> >> i am new to SQLite and i currently have an access database that i would
> >> like
> >> to convert over to a SQLite database.
> >>
> >> there are a couple of processes that would need to take place in order
> >> for
> >> this to work and i'm not sure if SQLite has these capabilities:
> >>
> >> 1.  Need to automatically import text file into SQLite database on local
> >> machine (machine always on).  I was thinking of using scheduled task to
> >> run
> >> a script to do this, but not sure if that is possible.
> > 
> > Sure it is possible. You will have to choose your favorite programming
> > language, write a program to locate and import the local text file,
> > and set up a scheduler to do so periodically.
> > 
> >> 2.  Neet to somehow create a front-end to query data from SQLite database
> >> (not sure if SQLite has front end application).
> > 
> > Lots of third-party applications. You might end up writing your own to
> > suit your own requirements.
> > 
> > 
> >>
> >> Basically, i want to get rid of the Access database because the user's
> >> have
> >> to manually update the tables everyday from the text files that get ftp'd
> >> over.  I was trying to figure out a solution that would automatically
> >> load
> >> the text files withouth any human intervention and then give the user's
> >> the
> >> ability to view the data through some sort of front-end (similar to
> >> Access
> >> form).
> >>
> >> Thanks
> >> scott
> > 
> > -- 
> > Puneet Kishor http://www.punkish.org
> > Carbon Model http://carbonmodel.org
> > Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
> > Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
> > Nelson Institute, UW-Madison http://www.nelson.wisc.edu
> > ---
> > Assertions are politics; backing up assertions with evidence is science
> > ===
> > Sent from Madison, WI, United States
> > ___
> > 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] Installing SQLite

[Jim Dodgen]

This is not a simple tool. I is primarily designed to be embedded in
applications. Your opinion about the quality of the *install* instructions
leads people to believe that you may need a better understanding of the
skill level required to use Sqlite.

On Wed, Jul 22, 2009 at 1:43 PM, scabral  wrote:

>
> i don't get it...
>
>
>
> Nuno Magalhães-2 wrote:
> >
> > On Wed, Jul 22, 2009 at 21:30, scabral wrote:
> >
> > [...]
> >
> >> the instructions on the SQLite website are pretty crappy to say the
> >> least.
> >> When i download the zip file, all i get is 3 txt files???
> >
> > [...]
> >
> > Oh... a troll.
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> >
>
> --
> View this message in context:
> http://www.nabble.com/Installing-SQLite-tp24614036p24614214.html
> Sent from the SQLite mailing list archive at Nabble.com.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Jim "Jed" Dodgen
j...@dodgen.us
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users