[sqlite] ISO 8859

2004-06-17 Thread Greg Obleshchuk
Hello,
I had this guy email me about a problem about using ISO8859 character .  He
has this in the DB 
Handhilfsbetätigungssatz
 
but when queried using my wrapper it returns Handhilfsbet..tigungssatz
 
When using SQLITE.EXE the results are displayed fine.  When I debug the code
the char * returned in the call-back event for this column has this
character as value -124 .  Which I assume is an overflowed 7 bit value.  Can
anyone help me understand how to fix this in the source?
 
thanks 
Greg
 



RE: [sqlite] Sqlite Secure backup

2004-06-02 Thread Greg Obleshchuk
Hi David,
I have been thinking about developing a backup/restore as part of my
wrapper.  It would be great to have all of these features compatible.
I really wanted to backup the database and then have the ability to restore
just one table or the entire DB.  

What do you think?  Maybe we should create a wiki page on sqlite.org?

Greg


-Original Message-
From: David Costa [mailto:[EMAIL PROTECTED] 
Sent: Thursday, 3 June 2004 9:26 AM
To: [EMAIL PROTECTED]
Subject: [sqlite] Sqlite Secure backup

Hello Everyone,
I am a contributor on the php project  ( my profile is available at
http://pear.php.net/user/gurugeek ) where I maintain several extensions.
I am in the process of writing a PEAR package to handle Sqlite backups (both
local backups and remote backups)  and I am looking for some suggestions.

Is the right way to proceed the db dump ? I assume that copying the db will
result in a corrupted file if the db is used at the time of backup.

The aim would be to dump or duplicate a whole database for a local or remote
backup with the relevant integrity checks.

Thanks in advance for your time and attention I remain,

Very much obliged,

Regards,
David Costa
PEAR- PHP Extensions and Application Repository Developer
http://pear.php.net/user/gurugeek



-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]



RE: [sqlite] sqlite with Visual Basic

2004-05-16 Thread Greg Obleshchuk
Hi Murray,
Can I make a suggestion or two.  Firstly I wouldn't use Crystal Report.  It
sucks and I hate it , have a look at ActiveReports from DataDynamics 
Version 2.0 is fantastic and very light on it's feet (small).  The price is
great as well. 
 
Also ActiveReport supports ADO , DAO, RDO and XML.  You can pragmatically
send it a array and bind to that.
 
With the ODBC driver, I'm not a big fan of installing drivers (ODBC) during
setup or supplying them with the application.  That's why the wrapper I
wrote doesn't use them.
 
 
 
A & G Software   
Quality software with Style

Greg Obleshchuk
Partner  
A & G Software
Victoria
Australia

[EMAIL PROTECTED]
http://www.ag-software.com <http://www.ag-software.com/> 
MSN: [EMAIL PROTECTED]  
 


 <http://www.ag-software.com/ags_scribe_index.aspx> Document any Microsoft
database in minutes
 <http://www.ag-software.com/xp_ags_crosstab.aspx> Generate Cross tab
results from Microsoft SQL Server 2000

 


  _  

From: Murray Moffatt [mailto:[EMAIL PROTECTED] 
Sent: Monday, 17 May 2004 2:14 PM
To: [EMAIL PROTECTED]
Subject: RE: [sqlite] sqlite with Visual Basic


I'm interested in using SQLite from within a Visual Basic 6 app that I'm
writing. I need reporting features and so was looking at using Crystal
Reports.

I'm guessing that it would be easiest to use SQLite ODBC for accessing the
SQLite database, as the VB tools and Crystal Reports support ODBC.

When I downloaded SQLite ODBC from  <http://www.ch-werner.de/sqliteodbc/>
http://www.ch-werner.de/sqliteodbc/ and looked at the readme file I was a
bit concerned when I read that this was still "experimental". Obviously if I
want to distribute my app to others I want something that isn't going to
bomb out every so often! If anyone else is using SQLite ODBC in an app I'd
like to hear how stable it is?

Also the readme file says that it has to be installed and databases set up
by using the ODBC Control Panel applet. Obviously I don't want to have to
tell people that they have to set all this up themselves by hand after they
install my app, so is there any way I can programmatically set up the ODBC
connection? Either via my installation program or within the main app
itself?


At 20:32 16/05/2004 +0200, you wrote:



Personally, I use my own wrapper (attached) with Greg Obleshchuk's
AGS_SQLite.dll. It is very simple to use (it will generate the SQL code for
you):

Dim db as ADOSQLite
Set db = new ADOSQLite

db.Connect "database.db"
db.Table = "mytable"
db.Action = ActionSelect
db.Sort = "id"
db.Where = "id < 100"
db.Exec
If Not db.Err Then
do while not db.EOF
debug.print db.field("id")
db.MoveNext
Loop
End if

Of course this runs "in-memory" so if this is a concern for you, then try to
narrow down your selects.

Cheers!
Chris

> -Original Message-
> From: Raymond Irving [mailto:[EMAIL PROTECTED] 
> Sent: 14 May 2004 17:50
> To: [EMAIL PROTECTED]
> Subject: Re: [sqlite] sqlite with Visual Basic
> 
> 
> I use SQLite ODBC with ADO to access databases from VB. It 
> works like a charm.
>  
> http://www.ch-werner.de/sqliteodbc/
>  
> __
> Raymond Irving
> 
> 
> Jérôme_VERITE <[EMAIL PROTECTED]> wrote:
> I use this wrapper and to simplify again the code, I created 
> littles classes which are very simple and look like the DAO 
> classes. It permits to transform Simply a VB application 
> using DAO to SQLITE
> 
> Jérôme
> 
> -Message d'origine-
> De : Steve O'Hara [mailto:[EMAIL PROTECTED] 
> Envoyé : vendredi 14 mai 2004 10:35
> À : Jalil Vaidya; Carlos Garces; [EMAIL PROTECTED]
> Objet : RE: [sqlite] sqlite with Visual Basic
> 
> 
> Here's the SQLite wrapper for VB from the Yahoo groups
> 
> Steve
> 
> -Original Message-
> From: Jalil Vaidya [mailto:[EMAIL PROTECTED]
> Sent: 14 May 2004 02:13
> To: Carlos Garces; [EMAIL PROTECTED]
> Subject: Re: [sqlite] sqlite with Visual Basic
> 
> 
> There is a VBWrapper.zip in the old SQLite yahoo
> group's file section. The archive contains wrapper
> over the SQLite API so that it can be used from VB.
> The API declarations for VB are also in the archieve.
> Get it from here:
> 
> http://f4.grp.yahoofs.com/v1/EBqkQKgMWwt8clzVakFnZ6GAGVancQ9q-
> 4gKNXEFX9QQmKI
> vHaVaTJ9rp1fz-XHaFxRIq46etnp1v_WDUrQPABeSNps/VB%20Wrapper
> 
> If you cannot download from the link above then you
> will have to join the group to get it.
> 
> HTH,
> 
> Jalil Vaidya
> 
> Disclaimer: I have never used this wrapper myself.
> 
> --- Carlos Garces wrote:
> > Hi!
> > I can use SQLLite with Visual Basic without using
> > other external DLL
&g

RE: [sqlite] sqlite with Visual Basic

2004-05-13 Thread Greg Obleshchuk


Hi Carlos,
Sorry mate I didn't point out the wrappers on Sqlite.Org
http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers

Greg 

> -Original Message-
> From: Jalil Vaidya [mailto:[EMAIL PROTECTED] 
> Sent: Friday, 14 May 2004 11:13 AM
> To: Carlos Garces; [EMAIL PROTECTED]
> Subject: Re: [sqlite] sqlite with Visual Basic
> 
> There is a VBWrapper.zip in the old SQLite yahoo group's file 
> section. The archive contains wrapper over the SQLite API so 
> that it can be used from VB.
> The API declarations for VB are also in the archieve.
> Get it from here:
> 
> http://f4.grp.yahoofs.com/v1/EBqkQKgMWwt8clzVakFnZ6GAGVancQ9q-
> 4gKNXEFX9QQmKIvHaVaTJ9rp1fz-XHaFxRIq46etnp1v_WDUrQPABeSNps/VB%
> 20Wrapper
> 
> If you cannot download from the link above then you will have 
> to join the group to get it.
> 
> HTH,
> 
> Jalil Vaidya
> 
> Disclaimer: I have never used this wrapper myself.
> 
> --- Carlos Garces <[EMAIL PROTECTED]> wrote:
> > Hi!
> > I can use SQLLite with Visual Basic without using other 
> external DLL 
> > Any sample of using sqlite.dll API?
> > 
> > Thanks
> > Carlos Garcis
> 
> 
> =
> 01001010
> 0111
> 01101100
> 01101001
> 01101100
> 
> 
>   
>   
> __
> Do you Yahoo!?
> Yahoo! Movies - Buy advance tickets for 'Shrek 2'
> http://movies.yahoo.com/showtimes/movie?mid=1808405861 
> 
> -
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
> 


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]



Re: [sqlite] row size limit

2004-04-18 Thread Greg Obleshchuk
>>Indeed. But I wonder if most all databases do it the same way? Or do 
>>all file-based dbs do it the same way? etc.

I guess it would depend on the system. I assume (and may ask) that MS SQL and Oracle 
use multi-threaded processes to access the information and that is the way they get 
around it.  I know the MS is looking at replacing the file system with the SQL engine 
in Longhorn so they must have solved the issue.  The speed that they can generate from 
queries would indicate that they have solved the issue.  But perhaps the smaller 
systems PostgreSQL and/or MySQL don't.

Greg

  - Original Message - 
  From: Puneet Kishor 
  To: SQLite 
  Sent: Monday, April 19, 2004 10:41 AM
  Subject: Re: [sqlite] row size limit



  On Apr 18, 2004, at 7:31 PM, Greg Obleshchuk wrote:

  > Hi Richard,
  > You know that is the first clear and concise explanation of why not to 
  > store large blobs in a database that I have heard anywhere.

  Indeed. But I wonder if most all databases do it the same way? Or do 
  all file-based dbs do it the same way? etc.

  Nice explanation though. I am currently working on a web-based image 
  gallery application powered by Oracle and am storing file names in the 
  db while the images themselves are stored on the disk. Some said why 
  don't I just stick the images in the db and I said no... the OS would 
  be better and more flexible at managing the physical files. Now I have 
  a more scientific sounding answer to back my assertion.

  ;-)


  >
  > Greg
  >   - Original Message -
  >   From: D. Richard Hipp
  >   Cc: [EMAIL PROTECTED]
  >   Sent: Monday, April 19, 2004 9:50 AM
  >   Subject: Re: [sqlite] row size limit
  >
  >
  >   [EMAIL PROTECTED] wrote:
  >> According to the FAQ on sqlite.org, the row size is arbitrarily
  >> limited to 1MB, which can be increased to 16MB by changing a
  >> #define in the source code.
  >>
  >> My question is, why even limit the row size? Is there a way the
  >> code can modified so that there is no limit for the row size (other
  >> than the available disk/memory space)?
  >>
  >
  >   The current file format allocates 24 bits for storing the number of
  >   bytes in a particular row.  (See 
  > http://www.sqlite.org/fileformat.html
  >   for details.)  So the currect version of SQLite will never allow more
  >   than 16MB in one row.  The proposed version 3.0.0 of SQLite uses a
  >   variable-length integer to store the row size and can thus accomodate
  >   up to 2^64 bytes in a single row.  In theory.
  >
  >   But a limit of 1MB will probably still be enforced.  Why is this?
  >
  >   SQLite stores large rows by breaking the data up into 1K chunks and
  >   storing each chunk in a separate page of the database.  Filesystems
  >   do much the same thing in an operating system.  But filesystems have
  >   an advantage over databases in that they can access the many chunks
  >   that make up a file in parallel, whereas SQLite has to access them
  >   one at a time.
  >
  >   Suppose you have a 1MB row in SQLite and you want to read the whole
  >   thing.  SQLite must first ask for the 1st 1K page and wait for it to
  >   be retrieved.  Then it asks for the 2nd 1K page and waits for it.
  >   And so forth for all 1000+ pages.  If each page retrieval requires
  >   1 rotation of the disk, that's about 8.5 seconds on a 7200RPM disk
  >   drive.
  >
  >   The filesystem code inside the operating system can do the same task
  >   in parallel.  If you ask the operating system to read in all of a
  >   1MB file for you, it can request many separate blocks from the disk
  >   controller at once.  The blocks might arrive out of order, but the
  >   OS can reassemble them into the correct order before returning the
  >   result up to the user-space process.  Using this approach, only a few
  >   rotations of the disk platter would be required to retrieve a 1MB
  >   file, instead of thousands.  The retrival will be 100s of times 
  > faster.
  >
  >   The moral of the story:  If you have large amounts of data you want
  >   to store, it is best to store that data in a separate file and then
  >   write the name of that file into the database.
  >   --
  >   D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
  >
  >
  >   -
  >   To unsubscribe, e-mail: [EMAIL PROTECTED]
  >   For additional commands, e-mail: [EMAIL PROTECTED]


  -
  To unsubscribe, e-mail: [EMAIL PROTECTED]
  For additional commands, e-mail: [EMAIL PROTECTED]


Re: [sqlite] row size limit

2004-04-18 Thread Greg Obleshchuk
Hi Richard,
You know that is the first clear and concise explanation of why not to store large 
blobs in a database that I have heard anywhere.

Greg 
  - Original Message - 
  From: D. Richard Hipp 
  Cc: [EMAIL PROTECTED] 
  Sent: Monday, April 19, 2004 9:50 AM
  Subject: Re: [sqlite] row size limit


  [EMAIL PROTECTED] wrote:
  > According to the FAQ on sqlite.org, the row size is arbitrarily
  > limited to 1MB, which can be increased to 16MB by changing a
  > #define in the source code.
  > 
  > My question is, why even limit the row size? Is there a way the
  > code can modified so that there is no limit for the row size (other
  > than the available disk/memory space)?
  > 

  The current file format allocates 24 bits for storing the number of
  bytes in a particular row.  (See http://www.sqlite.org/fileformat.html
  for details.)  So the currect version of SQLite will never allow more
  than 16MB in one row.  The proposed version 3.0.0 of SQLite uses a
  variable-length integer to store the row size and can thus accomodate
  up to 2^64 bytes in a single row.  In theory.

  But a limit of 1MB will probably still be enforced.  Why is this?

  SQLite stores large rows by breaking the data up into 1K chunks and
  storing each chunk in a separate page of the database.  Filesystems
  do much the same thing in an operating system.  But filesystems have
  an advantage over databases in that they can access the many chunks
  that make up a file in parallel, whereas SQLite has to access them
  one at a time.

  Suppose you have a 1MB row in SQLite and you want to read the whole
  thing.  SQLite must first ask for the 1st 1K page and wait for it to
  be retrieved.  Then it asks for the 2nd 1K page and waits for it.
  And so forth for all 1000+ pages.  If each page retrieval requires
  1 rotation of the disk, that's about 8.5 seconds on a 7200RPM disk
  drive.

  The filesystem code inside the operating system can do the same task
  in parallel.  If you ask the operating system to read in all of a
  1MB file for you, it can request many separate blocks from the disk
  controller at once.  The blocks might arrive out of order, but the
  OS can reassemble them into the correct order before returning the
  result up to the user-space process.  Using this approach, only a few
  rotations of the disk platter would be required to retrieve a 1MB
  file, instead of thousands.  The retrival will be 100s of times faster.

  The moral of the story:  If you have large amounts of data you want
  to store, it is best to store that data in a separate file and then
  write the name of that file into the database.
  -- 
  D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565


  -
  To unsubscribe, e-mail: [EMAIL PROTECTED]
  For additional commands, e-mail: [EMAIL PROTECTED]


Re: [sqlite] Adding SQL commands

2004-04-13 Thread Greg Obleshchuk
Hi Basil,
The first use of IF is same syntax as the case statement so I don't think it is 
required and as IF isn't SQL92 I doubt it will be included

so your command is replaced by

case when (select count(*) from foo) = 100 then 'good' else 'bad' end 

I like the idea of exist but then again you can do this (in a query) count 
where (Select count(*) from foo where col1 ='a') = 1

but what I really think you are talking about is a command language like TSQL for 
Microsoft or P/SQL for Oracle (is it P/SQL what ever?)  At the moment you can't do 
that type of thing is SQLite.  I think the reason here is the it is a Database system 
that is included in your application and therefore you application can make these 
decisions far better that a SQL language

Greg O
Don't for get www.SQL-Scripts.Com

  - Original Message - 
  From: [EMAIL PROTECTED] 
  To: [EMAIL PROTECTED] 
  Sent: Wednesday, April 14, 2004 8:03 AM
  Subject: [sqlite] Adding SQL commands


  I would like to add some commands to SQLite to make my SQL(ite) programming
  life easier!!!
  The commands are:
  IF - e.g IF ((select count(*) from foo) = 100)
  select "good";
 ELSE
  select "bad";
 END

  EXISTS - e.g IF EXISTS(select * from sqlite_master where name =
  'foo')
  DROP TABLE foo;
  END

  local variables - e.g. DECLARE @var TEXT;
   select @var = name from foo;

  I am not familiar with how compilers work but would be willing to write the
  "c" code
  to make these commands work.

  Does anyone have a simple way to add a command using the lemon parser??? I
  find the documentation
  hard to understand and I would like to add these command s using the c++
  compiler from Visual Studio.Net

  B.Thomas



  This e-mail may be
  privileged and/or confidential, and the sender does not waive any related
  rights and obligations. Any distribution, use or copying of this e-mail or the
  information it contains by other than an intended recipient is unauthorized.
  If you received this e-mail in error, please advise me (by return e-mail or
  otherwise) immediately. 

  Ce courrier électronique est confidentiel et protégé. L'expéditeur ne renonce
  pas aux droits et obligations qui s'y rapportent. Toute diffusion, utilisation
  ou copie de ce message ou des renseignements qu'il contient par une personne
  autre que le (les) destinataire(s) désigné(s) est interdite. Si vous recevez
  ce courrier électronique par erreur, veuillez m'en aviser immédiatement, par
  retour de courrier électronique ou par un autre moyen.

  

Re: [sqlite] New Web site

2004-04-11 Thread Greg Obleshchuk
I forgot to mention that this web site is build using SQLite as the backend database.  
I am in the process of writing a case study on it just so others can see some of the 
ways to use SQLite in a product (and what I hope will be) high hit web site.

Greg
  - Original Message - 
  From: Greg Obleshchuk 
  To: [EMAIL PROTECTED] 
  Sent: Monday, April 12, 2004 8:53 AM
  Subject: [sqlite] New Web site


  Hi Everyone, (I know this is off subject)
  I have finished a new web site dedicated to SQL Scripts for database system.  I am 
looking for people to lodge there useful (and not) scripts onto the system .  

  If you are looking for scripts try here first.  There some useful stuff like the 
SQL-99 standard to download and news links.

  The site is http://www.sql-scripts.com 

  I am also looking for people to link to the site.  I hope everyone can help

  regards
  Greg O

[sqlite] New Web site

2004-04-11 Thread Greg Obleshchuk
Hi Everyone, (I know this is off subject)
I have finished a new web site dedicated to SQL Scripts for database system.  I am 
looking for people to lodge there useful (and not) scripts onto the system .  

If you are looking for scripts try here first.  There some useful stuff like the 
SQL-99 standard to download and news links.

The site is http://www.sql-scripts.com 

I am also looking for people to link to the site.  I hope everyone can help

regards
Greg O

Re: [sqlite] A proposal for SQLite version 3.0

2004-04-11 Thread Greg Obleshchuk
I think the collate feature will solve this.  have a pre-defined collate TEXT_CI is 
the solution.  
One thing Microsoft allows you to do is create a database with a defined collation.  
Maybe this is the idea around the parameters in the open API .  You could open a DB 
with COLLATION TEXT_CI and it would collate using the open parameter by default.  If 
there was a column override then it would use that

Greg
  - Original Message - 
  From: Darren Duncan 
  To: [EMAIL PROTECTED] 
  Sent: Monday, April 12, 2004 8:46 AM
  Subject: RE: [sqlite] A proposal for SQLite version 3.0


  At 11:22 PM +0100 4/11/04, Steve O'Hara wrote:
  >I agree with Greg, the most irksome feature of SQLite is the case
  >sensititvity - it's one of the few things MS got right with SQLserver.  I
  >know this is more mainstream/standard SQL behaviour but it's outdated in
  >modern SQL applications that nearly always do some kind of linguistic
  >searching.
  >In fact, I'd go one step further and advocate making SQLite case-insensitive
  >as a rule.
  >I'm waiting for the flames...
  >Steve

  As far as I'm concerned, the issue of case-sensitive vs insensitive 
  is related to locale or nationality specific matters.  It involves 
  treating a pair of different characters as being the same character. 
  Besides our latin characters, does any other written language have 
  such a concept as upper/lowercase?  Whichever is available will 
  probably have its fans.  Case-insensitive may be more like a "natural 
  human language" whereas the other may be less, or not.

  I think that both methods should be supported, perhaps with a compile 
  time directive determining the default, and a run-time directive 
  changing it on a case by case basis.

  One thing to make absolutely certain, though, is that the SQLite API 
  provides a means to programmatically determine at runtime what 
  behaviour is being used.  If nothing else, it allows an application 
  which either expects one behaviour or is able to adapt to either, to 
  know how to talk to the database such that its expectations match 
  reality.

  Personally, I would hate for an application which expects a 
  case-sensitive unique field, and inserts multiple rows that it thinks 
  are distinct, only to have one fail or overwrite the other because 
  some case-insensitive rule says they are actually the same.

  -- Darren Duncan

  -
  To unsubscribe, e-mail: [EMAIL PROTECTED]
  For additional commands, e-mail: [EMAIL PROTECTED]


Re: [sqlite] A proposal for SQLite version 3.0

2004-04-11 Thread Greg Obleshchuk
Hi Will,
Thanks for clearing that up for me, it make more sense now.

Greg
  - Original Message - 
  From: Will Leshner 
  To: Forum SQLite 
  Sent: Monday, April 12, 2004 8:06 AM
  Subject: Re: [sqlite] A proposal for SQLite version 3.0



  On Apr 11, 2004, at 3:01 PM, Greg Obleshchuk wrote:

  > You state that there may or may not be the call-back function wrapper. 
  >  I would be an advocate for keeping it.  This way of handling returned 
  > data is most useful.  Sometimes when returning thousands or more rows 
  > of data you want to cancel the statement without a call-back function 
  > you must wait until the statement is finished and then discard the 
  > result.  Having a call-back allows you (or the user) to terminate the 
  > statement.
  >

  With the non-callback-API, "executing" the query simply returns a 
  virtual machine ready to get query results. It doesn't actually return 
  any results. You then have to step through the results yourself. So you 
  can pretty much stop the query any time you'd like. And the callback 
  mechanism is now built on top of the non-callback mechanism anyway, so 
  you wouldn't be able to do anything with callbacks you couldn't do 
  without them.


  -
  To unsubscribe, e-mail: [EMAIL PROTECTED]
  For additional commands, e-mail: [EMAIL PROTECTED]


Re: [sqlite] A proposal for SQLite version 3.0

2004-04-11 Thread Greg Obleshchuk
Hello,
I just read the proposed changes and they seem fine.  A couple of thing.  

The data types I think are great but can we have a large and small integer?  I noticed 
some people (embedded systems people) complain about this.  I quite happy with the 
large type but as integers will now be stored as the native type that will double the 
storage requirement for numbers (64-bit integer taking 8bytes where 32 take 4bytes)  
So may be something like 
if the value is a number then check to see if it fits in a 32bit integer if it does 
use that otherwise use a 64 bit.  If this is too much trouble in code then maybe a 
compile directive to use a certain size integer.

When using a INTEGER PRIMARY KEY perhaps we could use

INTEGER SMALL PRIMARY KEY 
and 
INTEGER LARGE PRIMARY KEY

Support for user definable collating.  I'm assuming that this will give us the ability 
to turn SQLite into a non case sensitive system by defining our own collation which is 
not case sensitive?  Which is great and you state that there will be two predefined 
collations 
COLLATE TEXT and COLLATE NUMERIC.  Can I suggest that you create a third pre-defined 
collation (to make it easy on us that want it) can you create a COLLATE TEXT_CI .  
Which would be a case in-sensitive collation.  Then the people that really want this 
can use it off the bad, as it were.  I'm sure it will be a lot easier for you to 
create it that for someone else.

API and preferred way of executing queries 
I'm assuming there will still be the wrapper to execute a SQL in one line .  
You state that there may or may not be the call-back function wrapper.  I would be an 
advocate for keeping it.  This way of handling returned data is most useful.  
Sometimes when returning thousands or more rows of data you want to cancel the 
statement without a call-back function you must wait until the statement is finished 
and then discard the result.  Having a call-back allows you (or the user) to terminate 
the statement.

Apart from that great changes , more complete but things change and grow (which is 
good).  If I had to decide which is my most wanted feature from the stuff above I 
would say COLLATE TEXT_CI as this would enable me to use = instead of LIKE in my 
lookups (I really don't like case sensitive data in the real world)

kind regards
Greg O

  - Original Message - 
  From: D. Richard Hipp 
  To: [EMAIL PROTECTED] 
  Sent: Wednesday, April 07, 2004 11:22 PM
  Subject: [sqlite] A proposal for SQLite version 3.0


  A design proposal for SQLite version 3.0 can be found at:

   http://www.sqlite.org/prop2.html

  Feedback from the user community is strongly encouraged.
  An executive summary of the proposed changes follows:

  *  Support for UTF-16
  *  Better BLOB support
  *  User-defined collating sequences (for better
 internationalization support)
  *  Smaller and faster than 2.8.13.

  The plan is to continue to support the 2.8.X series
  indefinately and in parallel to the 3.X series.  But
  the only changes to 2.8.X going forward will be bug
  fixes.  New features will go into 3.X.  Beta releases
  of version 3.X are expected within a few months.

  I do not have much experience with UTF-16 and am
  expecially interested in feedback on that area of
  the design.
  -- 
  D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565


  -
  To unsubscribe, e-mail: [EMAIL PROTECTED]
  For additional commands, e-mail: [EMAIL PROTECTED]


Re: [sqlite] Group by only returning one row when source table is sub-query or view

2004-04-01 Thread Greg Obleshchuk
Hi Richard,
It is a bit more than that for me anyway.  I have several complex views which are 
using grouping within the view themselves.  At the moment I have no other way of 
summarising the results of these views apart from inserting the results into a temp 
table and then processing the temp table, which is slow.

Greg.
  - Original Message - 
  From: D. Richard Hipp 
  Cc: [EMAIL PROTECTED] 
  Sent: Friday, April 02, 2004 8:28 AM
  Subject: Re: [sqlite] Group by only returning one row when source table is sub-query 
or view


  Greg Obleshchuk wrote:
   >
   > I posted a bug ticket a few days ago (ticket 678) regarding group by only
   > returning one row if the source was a subquery or view.  I was wonder if
   > anyone could confirm the bug and how long would a fix take?  (I'm not in a
   > position to create a fix)
   >
   > http://www.sqlite.org/cvstrac/tktview?tn=678,2
   >

  It might be a bug, or it might not.  What seems clear is that
  it is not something that can't be worked around easily, so it
  is not a high priority.  I'll look at it when I get a chance.

  -- 
  D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565


  -
  To unsubscribe, e-mail: [EMAIL PROTECTED]
  For additional commands, e-mail: [EMAIL PROTECTED]


[sqlite] va_list

2004-04-01 Thread Greg Obleshchuk
Hi ,
Can anyone tell me if you can manually create a va_list object.  I have in my wrapper 
now the sqlite_vmprintf function but as .NET doesn't support the ... as a parameter I 
had to use a [ParamArray] .  Currently I am testing the number of parameters passed 
and calling sqlite_vmprintf different ways.  If I could create a va_list and just pass 
the object it would be a one line call.

thanks 
Greg O

Re: [sqlite] Re: SQLite Logos?

2004-03-27 Thread Greg Obleshchuk
No, I disagree. The aspire web site is OK but the colours don't do anything for me.  
The fading from blue to light blue really is quite old.

With the SQlite logo I would have liked the feature to be a bit darker but I think 
it's great.  If you compare it to the MySQL logo you can see what a darker image part 
does, it highlight the mean (in terms of mySQL the freedom ) In terms of the SQLite 
the feature is obviously the Lite indicating light footprint )

Anyway I think it's a great.

Greg

  - Original Message - 
  From: Allan Edwards 
  To: 'Greg Obleshchuk' ; 'D. Richard Hipp' ; [EMAIL PROTECTED] 
  Sent: Sunday, March 28, 2004 4:45 PM
  Subject: RE: [sqlite] Re: SQLite Logos?


  I think it is ok, but does not meet the look that such a good database
  technology deserves!  My websites, http://www.aspire.ws, and
  http://store.aspire.ws, show what you can do if you are touchy on
  aesthetics.  You can find lots of good graphical artists out their.  I think
  you guys ought to really push some good graphical people on some creative
  fresh, and more VIBRANT ideas.  A lot of times we get 20 or more ideas
  before pushing a decision.  Graphical appeal is very subjective but you
  should be able to find something that you know just fits.

  The image has a nice kick to it, butt he color needs some help.  Multiple
  colors would be optimal. 

  Thanks,
  Allan 

  -Original Message-
  From: Greg Obleshchuk [mailto:[EMAIL PROTECTED] 
  Sent: Sunday, March 28, 2004 12:29 AM
  To: D. Richard Hipp; [EMAIL PROTECTED]
  Subject: Re: [sqlite] Re: SQLite Logos?

  I love that logo!
- Original Message -
From: D. Richard Hipp
To: Karl Timmermann ; [EMAIL PROTECTED]
Sent: Sunday, March 28, 2004 9:36 AM
Subject: [sqlite] Re: SQLite Logos?


Karl Timmermann wrote:
>
> Do you know of any "Powered By SQLite" type logos that might exist, like
> MySQL has? I would like to help support your project by advertising the
> fact.
> 

Rasmus Schultz provided this art:

http://www.sqlite.org/cvstrac/getfile/sqlite/art/SQLite.gif

But, no, I don't know of any "Powered By SQLite" type logos.
Contributions from the artistically inclined will be welcomed.
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]


Re: [sqlite] Re: SQLite Logos?

2004-03-27 Thread Greg Obleshchuk
I love that logo!
  - Original Message - 
  From: D. Richard Hipp 
  To: Karl Timmermann ; [EMAIL PROTECTED] 
  Sent: Sunday, March 28, 2004 9:36 AM
  Subject: [sqlite] Re: SQLite Logos?


  Karl Timmermann wrote:
  > 
  > Do you know of any "Powered By SQLite" type logos that might exist, like 
  > MySQL has? I would like to help support your project by advertising the 
  > fact.
  > 

  Rasmus Schultz provided this art:

  http://www.sqlite.org/cvstrac/getfile/sqlite/art/SQLite.gif

  But, no, I don't know of any "Powered By SQLite" type logos.
  Contributions from the artistically inclined will be welcomed.
  -- 
  D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565


  -
  To unsubscribe, e-mail: [EMAIL PROTECTED]
  For additional commands, e-mail: [EMAIL PROTECTED]


[sqlite] Search results

2004-03-25 Thread Greg Obleshchuk
Hi Everyone,
This is a tiny bit off subject, so sorry.

I have a search page on my web site which when used will search the database for 
results.  I want to be able to rank the results in best match first order.

In the database I will have three columns where I will be searching for matches , my 
question is what type of method do people/systems use to rate a match in the database?

An example of a typical search would be .  They enter the work Backup.
I was thinking of selecting all rows which have backup in the three columns and then 
counting how many times the word appears in each column.  This then would be the 
ranking of the result.

I.e.
Result rows
Row 1 10 times
Row 2  33 times
Row 3 23 times

These results would appear like this

Row 2
Row 3
Row 1

Does anyone have any other idea's on ranking results?

If you do please email them to me

regards
Greg O


[sqlite] Information Schema Views

2004-03-22 Thread Greg Obleshchuk
Hello All,
I have created a new wiki page off the main wiki page called Information Schema. Here 
is the link

http://www.sqlite.org/cvstrac/wiki?p=InformationSchema

The SQL standard INFORMATION_SCHEMA views are a standard way of retrieving schema 
information from any SQL standardised database.  

I thought it would be a great idea to create these views for SQLite and place the 
creation script on this page.  People can then add them to there database as they 
need.  Perhaps Richard could add them one day to the system one day.

If anyone has any of these views perhaps you could add them to the page or send them 
to me.

regards
Greg O

Re: [sqlite] which files to compile...

2004-03-11 Thread Greg Obleshchuk
Hi Peter,
Which OS are you using?  I have the latest compiled LIB for Windows on my
system I could send you.

regards
Greg
- Original Message - 
From: "Peter Lau" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Friday, March 12, 2004 8:16 AM
Subject: [sqlite] which files to compile...


> Hi there,
>
> SQLite newbie here.  I am trying to compile SQLite as a static library,
> then statically compile and link with my application source.
>
>  From the reading the site and browsing the files in sqlite_source,
> these are the files I think I need, am I correct?
>
> Thanks in advance for your help.
>
> pete
>
> p.s. is this mailing list archived somewhere so that I can search
> instead of asking the already answer question?
>
> p.p.s.  the list of files... always forgot.
>
> attach.c
> build.c
> btree.c
> hash.c
> func.c
> where.c
> vdbeaux.c
> vdbe.c
> vacuum.c
> util.c
> update.c
> select.c
> random.c
> printf.c
> pragma.c
> opcodes.c
> pager.c
> date.c
> copy.c
> delete.c
> btree_rb.c
> parse.c
> auth.c
> insert.c
> expr.c
> tokenize.c
> trigger.c
> os.c
> main.c
> table.c
>
>
> -
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
>


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]



[sqlite] Bad Database file

2004-03-05 Thread Greg Obleshchuk
Hi,
I just checked a database which was giving me problems using PRAGMA
integrity_check; and discovered it was corrupt.

sqlite> PRAGMA integrity_check;
*** in database main ***
List of tree roots: invalid page number 24
List of tree roots: invalid page number 23

What would be the reasons for a database to go corrupt?  Anyone have any
ideas

regards
Greg O


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]



Re: [sqlite] Re: [inbox] Re: [sqlite] Primary key and index

2004-02-09 Thread Greg Obleshchuk
Thanks for clearing that up

Greg
  - Original Message - 
  From: D. Richard Hipp 
  To: [EMAIL PROTECTED] 
  Sent: Tuesday, February 10, 2004 11:52 AM
  Subject: Re: [sqlite] Re: [inbox] Re: [sqlite] Primary key and index


  Greg Obleshchuk wrote:
  > 
  > So in these cases there is no benefit from creating an index on a column 
  > that is INTEGER PRIMARY KEY?
  >  

  Putting an index on an INTEGER PRIMARY KEY will make INSERT,
  DELETE, and UPDATE slower since the index must be maintained.
  But no SELECT will ever use the index.  So adding an index
  to an INTEGER PRIMARY KEY is less than no benefit - it hurts.

  See ticket #292.  If you say "UNIQUE PRIMARY KEY" (as some
  users want to do) SQLite will create two identical indices
  Only one index will ever be used - the other justs wastes
  CPU time and disk space.  I'll get around to fixing that
  someday. Probably at the same time I should rig it so that
  attempts to create named indices on PRIMARY KEY are ignored
  too.  Once that happens, you can create indices on your
  INTEGER PRIMARY KEY all you want - SQLite will ignore your
  attempts - and everything will work at maximum efficiency
  regardless of what you try to do.

  -- 
  D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565


  -
  To unsubscribe, e-mail: [EMAIL PROTECTED]
  For additional commands, e-mail: [EMAIL PROTECTED]


Re: [sqlite] Re: [inbox] Re: [sqlite] Primary key and index

2004-02-09 Thread Greg Obleshchuk
Hi Richard,
So in these cases there is no benefit from creating an index on a column that is 
INTEGER PRIMARY KEY?

If so is there a way of exposing the fact that INTEGER PRIMARY KEY are used as the key 
tot he B-Tree table?  By looking in SQLite_Master it isn't obvious at all.

regards
Greg
  - Original Message - 
  From: D. Richard Hipp 
  To: Greg Obleshchuk 
  Sent: Tuesday, February 10, 2004 9:29 AM
  Subject: Re: [sqlite] Re: [inbox] Re: [sqlite] Primary key and index


  Greg Obleshchuk wrote:
   >
   > Select * , Product_Description.ProductName  from ORDERS , Product_Description
   > where ORDERS.Product_ID = Product_Description.Product_ID
   >
   > or would you have to do this
   >
   > Select * , Product_Description.ProductName  from ORDERS , Product_Description
   > where ORDERS.Product_ID = Product_Description.ROWID
   >

  If Product_Description.Product_ID is an INTEGER PRIMARY KEY, then
  Product_Description.Product_ID is just an alias for
  Product_Description.ROWID.  The two queries are identical.
  Verify this by using EXPLAIN and seeing that both generate
  identical byte code.

  -- 
  D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565


[sqlite] Re: [inbox] Re: [sqlite] Primary key and index

2004-02-09 Thread Greg Obleshchuk
Hi,
Doing some testing , creating a primary key with the INTEGER defined will not create 
an index but creating a PRIMARY KEY by it self does create an index

I.e
Create table z(a PRIMARY KEY, B);
creates an index

but 
Create table z(a INTEGER PRIMARY KEY, B);
does not

So Yes I would be creating an index on the a column if it is an autonumber(identity ) 
column if I was using this column in joins or where clauses.

regards
Greg 
  - Original Message - 
  From: Michael Hunley 
  To: Greg Obleshchuk 
  Sent: Tuesday, February 10, 2004 6:49 AM
  Subject: Re: [inbox] Re: [sqlite] Primary key and index


  At 09:20 AM 2/9/2004 +1100, you wrote:
  >This is from the doc on  the web
  >
  >Specifying a PRIMARY KEY normally just creates a UNIQUE index on the 
  >primary key. However, if primary key is on a single column that has 
  >datatype INTEGER, then that column is used internally as the actual key of 
  >the B-Tree for the table. This means that the column may only hold unique 
  >integer values. (Except for this one case, SQLite ignores the datatype 
  >specification of columns and allows any kind of data to be put in a column 
  >regardless of its declared datatype.) If a table does not have an INTEGER 
  >PRIMARY KEY column, then the B-Tree key will be a automatically generated 
  >integer. The B-Tree key for a row can always be accessed using one of the 
  >special names "ROWID", "OID", or "_ROWID_". This is true regardless of 
  >whether or not there is an INTEGER PRIMARY KEY.
  >
  >So adding the Primary Key does create an index.
  >
  >you can verfiy this by doing
  >
  >create table a (b primary key,c);
  >select * from sqlite_master;

  So, then, is he seeing a false performance increase if he creates an index 
  on the primary integer key?  If not, and he is seeing a real performance 
  boost, why isn't the index auto created?  I.e. should we all be creating an 
  extra index on our integer primary key tables for performance?

  thanks.
  michael 


Re: [sqlite] OK to drop support for legacy file formats?

2004-02-06 Thread Greg Obleshchuk
Hello,
Why not remove the feature but create a seperate utility project that
converts any version of SQLITE DB to the latest version.

In this way SQLite can be just what it is small and fast.  There would be a
tool from the orginal source which you would know would work and simple to
use.

The current version of SQLite would need to error when openning an older
formatted DB file.

regards
Greg



- Original Message - 
From: "D. Richard Hipp" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Saturday, February 07, 2004 1:05 AM
Subject: [sqlite] OK to drop support for legacy file formats?


> If you use a modern version of SQLite (version 2.6.0 through 2.8.11)
> to open an older database file (version 2.1.0 through 2.5.6) the
> library will automatically rebuild all the indices in the database
> in order to correct a design flaw in the older database files.
>
> I am proposing to drop support for this auto-update feature.
> Beginning with 2.8.12, if you attempt to open a database file
> built using version 2.5.6 or earlier, the open attempt will
> fail (with an appropriate error message).  You will have to
> update the database file manually.
>
> Would this proposed change cause anyone unreasonable hardship?
> -- 
> D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
>
>
> -
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
>


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]



Re: [sqlite] Query optimization help

2004-02-01 Thread Greg Obleshchuk
Hi Richard,
try this 

SELECT 
DISTINCT 
p.poster_id AS has_posted, 
t.id, 
t.subject, 
t.poster,
t.posted, 
t.last_post, 
t.last_post_id, 
t.last_poster, 
t.num_views,
t.num_replies, 
t.closed, 
t.sticky, 
t.moved_to
FROM 
topics AS t , posts AS p
WHERE 
t.id IN(3082, 2467, 3076, 3055, 3016, 3019, 2509, 2788, 2804, 2396,2761, 2745)
and 
t.id=p.topic_id 
AND 
p.poster_id=2

There a post from Dr R about how SQLite works out joins.  The above should be the 
final result it I read it right.  You might want to also try using group by instead of 
DISTINCT
In other DB it is faster.
http://www.sqlite.org/cvstrac/wiki?p=PerformanceTuning


regards
Greg 




  - Original Message - 
  From: Rickard Andersson 
  To: [EMAIL PROTECTED] 
  Sent: Monday, February 02, 2004 2:00 PM
  Subject: [sqlite] Query optimization help


  I'm having some performance problems with queries looking like the
  following:

  SELECT DISTINCT p.poster_id AS has_posted, t.id, t.subject, t.poster,
  t.posted, t.last_post, t.last_post_id, t.last_poster, t.num_views,
  t.num_replies, t.closed, t.sticky, t.moved_to
  FROM topics AS t
  LEFT JOIN posts AS p
  ON t.id=p.topic_id AND p.poster_id=2
  WHERE t.id IN(3082, 2467, 3076, 3055, 3016, 3019, 2509, 2788, 2804, 2396,
  2761, 2745)

  The above query takes a full second to complete on my P3-450. The database
  contains approx. 1200 topics and 8000 posts. All relevant columns have
  indices (t.id, p.topic_id, p.poster_id). Do you believe there is a way to
  speed it up?

  -- 
  Rickard Andersson
  arpen_at_home_dot_se


  -
  To unsubscribe, e-mail: [EMAIL PROTECTED]
  For additional commands, e-mail: [EMAIL PROTECTED]


Re: [sqlite] Best method to create a database with a tree structure?

2004-01-18 Thread Greg Obleshchuk
Hi Martin,
You are talking about standard Parent/Child relationships here but because you have 
unlimited number of entries you fold it into one table and have a Parent_ID column.  
So if you have your required details as being these columns

Name
SomethingElse

Then a standard table might be 

Name_ID
Name
Somethingelse

adding the parent id column would be

Name_ID
Name
Somethingelse
Parent_ID

or
Create Table TableName (
Name_ID INTERPER PRIMARYKEY , 
Name , 
Somethingelse , 
Parent_ID )

Now all you need to do is fill in the details , PARENT_ID = 0 means it has no parents 
(or leave it NULL if you want) 
When you retrieve the rows you always do select * from Tablename where Parent_ID = 2 
(or what ever)

regards
Greg O


  - Original Message - 
  From: John Scott 
  To: [EMAIL PROTECTED] 
  Sent: Monday, January 19, 2004 9:52 AM
  Subject: [sqlite] Best method to create a database with a tree structure?




  Hello folks,

  I am thinking of to create a database with a tree structure.

  BUt it seems to be a hard work. 

  I would like to have a tree structure embbeded into the database like:

  Root folder which contains two other folders "books" and "music".

  If a click on books, only the entries in the database which belong to the folder 
bools under root should be displayed.

   

  Does someone have a good idea??

  Thanks,

  Martin



  -
  每天都 Yahoo!奇摩 
  海的顏色、風的氣息、愛你的溫度,盡在信紙底圖
  信紙底圖

Re: [sqlite] How to link under Windows

2004-01-18 Thread Greg Obleshchuk
Erik,
You will need to use one of the .Net Wrappers or the .NET data providers.
http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers
regards
Greg O
Erik Jälevik wrote:

I'm just about to write some code using SQLite from Visual Studio .NET under Win
XP. I am however, not sure how I go about linking the library.
The precompiled zip for Windows contains a .dll and a .def file. Most other
libraries have a .lib file for including in the linker options. I will try and
link only the .def but it doesn't seem to contain enough info for the linker to
know how to treat the .dll.
Furthermore, I need an sqlite.h to include in my source file. After downloading
the sources, all I could find was a sqlite.h.in. As I'm not familiar with Unix
building procedures, I'm not sure if this file is valid or whether it undergoes
some changes when make is run.
So basically, I'd be very grateful if anyone using SQLite on Windows could point
out how to link it and where to get hold of the correct sqlite.h.
Many thanks,
Erik
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
 



-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]


[sqlite] New wiki page for Performance Tuning

2004-01-15 Thread Greg Obleshchuk
Hi All,
I just created a new wiki page off the wiki home called PerformanceTuning.  I have 
started to place clipets from emails in the group which relate to making queries or DB 
operations run quicker.

Please add your bits and pieces

http://www.sqlite.org/cvstrac/wiki?p=PerformanceTuning

regards
Greg O
  - Original Message - 
  From: Mauricio Piacentini 
  To: [EMAIL PROTECTED] 
  Sent: Thursday, January 15, 2004 1:30 PM
  Subject: Re: [sqlite] SQLite Browser (Mac OS 10.3)


  [EMAIL PROTECTED] wrote:
  >>> ...on Mac OS 10.3?
  >>
  >>
  >> What error you get?
  > None. That's what's strange about it. It won't even launch. It starts in 
  > the dock but dies one second (literally) later.

  I can run it on MacOSX 10.3.2. However the version of Qt used to compile 
  the binaries does not support Panther officially, so the widgets do not 
  look correct, specially buttons. There might be other subtle issues with 
  Panther. I will try to find time to compile a newer version against the 
  latest Qt, but please post a request directly to the sourceforge foruns 
  if you have not done so.
  If you are not using the binaries and have compiled from source you 
  probably do not have Qt setup correctly for static compilation, or your 
  environment is not setup correctly to use Qt shared libraries from the 
  Finder. Since this list is dedicated to SQLite I would recommend 
  checking the Qt forums, or posting to the sqlitebrowser message boards.

  Regards,
  Mauricio Piacentini


  -
  To unsubscribe, e-mail: [EMAIL PROTECTED]
  For additional commands, e-mail: [EMAIL PROTECTED]


RE: [sqlite] web-based admin utilities

2004-01-14 Thread Greg Obleshchuk
Hi, 
I've done one in VB.NET and it's here 
http://sourceforge.net/projects/dotnetsqliteadm/

Regards
Greg

-Original Message-
From: Wade Preston Shearer [mailto:[EMAIL PROTECTED] 
Sent: Thursday, 15 January 2004 6:01 AM
To: [EMAIL PROTECTED]
Subject: [sqlite] web-based admin utilities


Is anyone aware of a web-based admin utility for SQLite similar to 
phpMyAdmin? I found one...

.http://sqlitemanager.sourceforge.net/


...but it is not in English and doesn't look very impressive.

wade



-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]



RE: [sqlite] AGS_SQLite_Wrapper VB

2004-01-10 Thread Greg Obleshchuk
Hi,
The returned result is a variant array.  You move through a array as you
normally do, by increasing a index counter.  You can write a class
around any array to emulate an ADO recordset if you want.

Regards
Greg

-Original Message-
From: ISA Programmi [mailto:[EMAIL PROTECTED] 
Sent: Sunday, 11 January 2004 3:43 AM
To: [EMAIL PROTECTED]
Subject: [sqlite] AGS_SQLite_Wrapper VB


Hi all,

does someone could tell me how to
implement the record (rows) navigation
like "MoveNext", "MovePrevious",
"EOF" etc... using the AGS_SQLite
wrapper and VB6?

Thank you a lot in advance,

Giuliano Isacchi



-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]



[sqlite] .NET Assembly and VB Wrapper

2004-01-06 Thread Greg Obleshchuk
Hello,
I have updated my .NET Assembly and VB Wrapper for SQLite to support version 2.8.9.

Both can be downloaded from http://www.ag-software.com/SQLite.aspx

.NET Assembly Changes
None

VB Wrapper changes
Introduced a new function called ags_number_of_rows_from_last_call, which as the name 
suggests returns the number of rows from the last query run.  This is help in the 
situation where the SQL command executed correctly but didn't return any rows.  You 
should check this value before accessing the variant array.

Samples Changes
Both updated

regards
Greg O



[sqlite] TCL.h File missing from sqlite_source.zip

2004-01-06 Thread Greg Obleshchuk
Hi,
I have just downloaded the sqlite_source.zip file for MS-Windows and there seems to be 
a file missing TCL.H .

Anyone have any ideas where it went?

regards
Greg O


RE: [sqlite] need faster count(*)

2004-01-06 Thread Greg Obleshchuk
Hi,
No the process must count all the rows.  The ROWID value is generated
when a new row is inserted but if you delete rows the last rowid will
not be the same as the count of rows.  If you have an index on the
primary key and count that then it will run as quickly as possible.

Regards
Greg

-Original Message-
From: Buzz Weetman [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, 7 January 2004 7:43 AM
To: [EMAIL PROTECTED]
Subject: Re: [sqlite] need faster count(*)


In my particular situation, count(1) or count(my_column_name) does not
seem to make a dent. I understand that count(x) is useful for getting
the row count of a subset of results.  But since I just want the number
of rows in a table, shouldn't the query just be able to get this from a
value associated with this table?  Is this value maintained in the
sqlite code?
 
Buzz

Hennie Peters <[EMAIL PROTECTED]> wrote:
At 07:41 6-1-2004 -0800, Buzz Weetman wrote:
>I'm using sqlite in an embedded system. Disk I/O is relatively slow. I
>am doing a:
>SELECT count(*) FROM my_table;
>
>This is taking too much time. I've also tried count(1), as I saw
>suggested elsewhere in the mail list... though I'm not entirely sure
what 
>this means. 1st column? I've tried a column that I have an index for...

>not faster.
>
>Doesn't sqlite "know" the number of rows in each table without 
>explicitly
>counting them?
>
>Thanks for any help
>Buzz

The count function takes a column or a constant as an argument. I posted

the count(1) some days ago. I used this in
Oracle sql years ago. The idea was to make the query faster as it did
not 
need to scan the columns in the table.

groet,
Hennie
==
Linux is like a wigwam - no gates, no windows and an apache inside.
==


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]


-
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]



RE: [sqlite] Field name

2003-11-27 Thread Greg Obleshchuk
Hi,
Des that mater?  If it does then just un=quote them by removing the first
char and lasts


Kind regards
Greg O

-Original Message-
From: Bronislav Klucka [mailto:[EMAIL PROTECTED] 
Sent: Thursday, 27 November 2003 8:42 PM
To: SqLite Konference; Greg Obleshchuk
Subject: RE: [sqlite] Field name


but the results column name seems to be  [brona'-kluc"ka] instead of
brona'-kluc"ka

> -Original Message-
> From: Greg Obleshchuk [mailto:[EMAIL PROTECTED]
> Sent: Thursday, November 27, 2003 10:32 AM
> To: 'Bronislav Kluèka'
> Subject: RE: [sqlite] Field name
>
>
> Hi ,
> I prefer to using [ ] instead of ' or " it makes life so much easier
>
> So
> select [brona'-kluc"ka] from [brona'-klucka];
> Works perfectly and doesn't make your eyes water
>
> Kind regards
> Greg O
>
>
>


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]



re: [sqlite] Network Performance

2003-11-25 Thread Greg Obleshchuk

Hi Brian,
I few things.
The problem is your network speed compared to local disk speed (also distance is an 
issue as well)  Disk transfer is in Mega bytes where as network IO is in mega bits.  
SO while IDE standard 33.3MB looks slower than 100mb for your network (even switched) 
it is in fact slower.  Also most IDE are inboard to the motherboard which bypasses the 
IO bus.  Not the same for network cards while lots are inboard they tend to hang off 
the IO bus and therefore behave the same as plug-in cards(obviously depending on you 
motherboard).

So no matter what you do local will always be fast, by a magnitude at least.

Some things you can check with your network is to make sure you are using a 100mb card 
(if it's a plug-in)
Make sure that you have the card set to 100mb Full duplex as apposed to Auto.  There 
is a Cisco bug in lots of there switches and hubs.  This bug relates to auto detect of 
speed, basically if you have a network card set to Auto every packet is check to 
ensure it is at the right speed.  This slows down the transmission rate a great deal.  
Make sure you IT department has the switch set-up to 100mb Full duplex as well

You could try paging using the offset and limit clauses but depending on your queries 
these may not give you the results you require.  If you query is structured to return 
the rows in a certain order which the index will not handle this may require a full 
table scan .  Which will mean the same time delay for less rows.  The issue is very 
piece of data that needs to be check has to be transferred from the network location 
to your P.C.  Then that information is then stored and the next retrieved until the 
whole table is processed.  Indexes make it quicker by being sorted and having less 
information.

Another suggestion would be to look at the query and find out if you really need to 
return so many rows of data (58,840 is a lot)
Also have a look at your data if you DB is 37MB and you have 58,840 rows each row is 
on average 618K .  This is a lot of data in a single row.  Some times we get carried 
away with DB design and just because we can store data we do.  A good example of this 
is images and documents.  We can store these in a DB but it much better to store a URL 
for share path to the file and then store the file on the OS drive.  Drives ad 
directories are much better at servicing these items than databases.  Or if this isn't 
the case you could try splitting the table into a one to one relationship table.  Have 
the items you do lookups on in one table and the retrieval items in another.  Then you 
can do a lookup and just join the results to return all the data.  This should improve 
the processing a bit but not the transfer time.  It should also reduce the index sizes 
depending on what you index.

Lastly you could try a local cache of the data.  use a splash screen with some sort of 
information like "Init system please wait".  Copy the DB locally and then use the 
local system to query against.  When updating you will need to do two updates , but 
this then fails when other people update the data and you will not see the changes.

regards
Greg O
  - Original Message - 
  From: Brian Pugh 
  To: SQLite Email 
  Sent: Wednesday, November 26, 2003 10:12 AM
  Subject: [sqlite] Network Performance


  Hi,

  Now that I've completed my SQLite application (written in Pyxia's IBasic), using
  the SQLite DLL, I have some questions

  My database is about 37mb, and has 114,380 records (rows)

  If I open and query the database locally (ie, the .db file is in the same folder as
  the application), things move pretty quick. I can do a query for a certain range
  of records (59,840) in about 4 seconds

  Doing the same query with the same .db on a network server is taking 18 seconds.
  Is this acceptable? I find that pretty slow!

  Most of my queries are fashioned so that they use indexes - I try not to use
  the "like" operator

  We have a certified 100mb network, with Cisco gigabit switches, and I am running
  the application from a 2.4gHz P4 PC

  How do other users deal with querying large amounts of information over a network?
  Are you paging in records, say 500 at a time, and then paging in the next 500
  records?

  Could I set my database up differently?
  At the moment, when the .db loads up, I am setting:
  pragma cache_size=8192;
  pragma synchronous=off;
  pragma count_changes=off;

  Are there any other tweaks I could set in this area?

  If anyone has any ideas or suggestions as to how to improve the query
  speed over a network, I would love to hear them

  Thanks a lot,

  Brian Pugh
  Halifax, England


[sqlite] Is there a problem with this list?

2003-11-20 Thread Greg Obleshchuk
Hello,
Just wondering if there is a problem with this list.


regards
Greg 

[sqlite] New function for SQlite

2003-11-11 Thread Greg Obleshchuk
Hi,
I have just finished an SQL function for SQLite which will allow to call
exported function form a Win32 DLL.
 
This is an example of the calling syntax
update UserPassword set Password =
se_fn("mydll_01","Encrypt",'NewPassword');
 
or
 
select se_fn("mydll_01","Encrypt",[Message]) from UserMessages
 
The function is called  se_fn (SQLite Extension)
Parameter One: DLL name
Parameter Two: Function Name
All other parameter are passed to the extension DLL
 
My thoughts behind this function was to help the many people who are using
wrappers or who use languages that don't support points or c type strings
(like VB ASP PHP,..).  These languages can't use the sqlite_create_function.
By using this function they can still extend SQLite for more power.
Some of the uses I can see are compression , encryption, scientific
calculations , basically any thing.
 
At the moment I have written the WIN32 function and I would not know where
to start for any other platform.
 
This is the function as it appear in func.c, any comments would be welcome 
 
#if WIN_32
#include "Winbase.h"
#include "windows.h"
/***
This is an extesion Function which allows the loading and execution of
exported 
functions from DLL's.  The main purpose of this function is to provide
extensions 
to SQLite to enviroments that don't support C type pointer or strings. 
i.e VB ASP PHP and the like.
 
The external DLL must be a standard WIN32 DLL which exports two function
This is the first function, which is used to release the memory allocated by
the second function
void FreeMemory(char **argr) {
 if (argr[0] == NULL) {
  return;
 }
 free(argr[0]);
}
This is a prototype of the second function (the one you wnat to call)  The
first two parameter 
are your calling parameter as pssed in the SQL statement.  The third
parameter is the return parameter 
for results.  You must allocate the memory for that and NULL terminate the
value.
 
void TestFunction_01(int argc, const char **argv, char **argr) {
 // Test to see if we have a pointer for argr
 // Do some processing
 argr[0]=(char *)malloc(100);
 sprintf(argr[0],"Done");
 return;
}
 
***/
typedef void (*SE_ExternalFunction)(int , const char **, char **);
typedef void (*SE_FreeMemory)(char **);
 
static void SE_fnFunc (sqlite_func *context, int argc, const char **argv){
 HINSTANCE hinstLib;
 BOOL fFreeResult;
 SE_ExternalFunction LocalFunction;
 SE_FreeMemory FreeMemoryFunction;
 char *argr=NULL;
 
 if (argc < 2 ) {
  // Return is not enough parameters
  return;
 }
 hinstLib=LoadLibrary(argv[0]);
 if (hinstLib != NULL) {
  // We have loaded the DLL now lets map the function call
  // Load the extenal function
  LocalFunction=(SE_ExternalFunction)GetProcAddress(hinstLib, argv[1]); 
  // Load the Release memory fuction
  FreeMemoryFunction=(SE_FreeMemory)GetProcAddress(hinstLib, "FreeMemory"); 
  if (LocalFunction != NULL && FreeMemoryFunction != NULL) {
   // We have mapped the Function
   // Allocate argr;
   LocalFunction(argc, argv, );
   if (argr != NULL) {
sqlite_set_result_string(context, argr, -1);
   } 
   else 
   {
sqlite_set_result_string(context, NULL, -1);
   }
   // Free the memory allocated to argr on the other heap.  
   //Must do this by using the FreeMemeory function in the DLL
   FreeMemoryFunction();
  }
  else
  {
   // Error Loading Function
   sqlite_set_result_string(context, "Error Mapping Functions", -1);
  }
  // Free library
  fFreeResult = FreeLibrary(hinstLib); 
 }
 else
 {
  // Error loading Library
  sqlite_set_result_string(context, "Error Loading Library", -1);
  return;
 }
}
#endif
 
Kind regards
Greg 


Fw: [sqlite] Performance problem

2003-11-04 Thread Greg Obleshchuk
Sorry Richard I meant to send this to the group


Hello,
Last week I raised an issue about case sensitivity in where clauses. In doing a little 
research I happened to talk to an Oracle DBA here where I work and asked him the 
question of how Oracle handled case sensitivity .  He explained it is handle in the 
same way and suggested the same fix drop both to lower case and then compare.  The 
next thing is mentions is really what I want to raise, he said the latest release of 
Oracle allows you to build indexes which include functions.  He also said that these 
new type of indexes were used when the user issued a select statement with a function 
in the where clause or a like (i.e select Col1, Col2 from tableName where col1 like 
'Gr%' or select * from tablename where lower(col1) ='fred'  ).  Without knowing all 
the ins and outs of it, this seems like a great feature to have.

Any change of having someone look into the possibility of implementing some thing like 
that?

regards
Greg 




- Original Message - 
  From: D. Richard Hipp 
  To: [EMAIL PROTECTED] 
  Sent: Wednesday, November 05, 2003 1:45 PM
  Subject: Re: [sqlite] Performance problem


  [EMAIL PROTECTED] wrote:
  > 
  > DRH: Will the changes to indicies allow us to define arbitrary collation 
  > functions? If so, will those indicies be used when a query is done that 
  > could use the arbitrary collation function?
  > 

  Likely so.  But no promises yet.

  -- 
  D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565


  -
  To unsubscribe, e-mail: [EMAIL PROTECTED]
  For additional commands, e-mail: [EMAIL PROTECTED]



Re: [sqlite] Where statements are they case sensitive?

2003-10-29 Thread Greg Obleshchuk
  I checked a few systems and there doesn't seem to be a standard.  As was said Oracle 
is case sensitive but all the Microsoft products I check SQL, Access , MS Query where 
all case insensitive.

  I think a PRAGMA would be a great idea for this.  What do other people think?
  Using a like (which negates the use of indexes or a lower function which does the 
same si a solution but not the best )

  regards
  Greg 


RE: [sqlite] Web Database Manager for SQLite

2003-10-21 Thread Greg Obleshchuk
Hi,
Well thanks for all the input .

I have started a web based admin in .NET,  for two reasons 
One - I'm more comfortable with .NET than Perl or PHP
Two - As a further example for the .NET wrapper I have developed.

Good luck to everyone else

Kind regards
Greg O


-Original Message-
From: Darren Duncan [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, 22 October 2003 4:06 AM
To: Greg Obleshchuk
Cc: SQLite
Subject: Re: [sqlite] Web Database Manager for SQLite


At 3:54 PM +1000 10/21/03, Greg Obleshchuk wrote:
>Hi Everyone,
>Does anyone have a web based database manager for SQLite Databases?
> 
>I thought I would ask before starting one
> 
>regards
>Greg O

This message is an addendum to my previous response.  In light of the fact
that at least 2 other web managers for SQLite have been brought up here, I
will point out that unlike those, my solution is *not* written in PHP.
Rather, my project's first major release is entirely in Perl 5 (a more
capable language), and works under but doesn't require a pure mod_perl
environment.  In a subsequent major release, the shared library portion of
the project (which is not web-specific at all) will be available as a C
library for the obvious resource-usage and compatability reasons, though the
application part will still be in Perl 5, and later in Perl 6. -- Darren
Duncan


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]



Re: [sqlite] AUTO INCREMENT

2003-10-19 Thread Greg Obleshchuk
Hi Kevin,
You could also query the table or view but don't return any rows.  If you set the 
option to get the column types in the resultset this should do what you want .  AN 
example of what I am trying to say would help :)

PRAGMA show_datatypes = ON; 
select COl3 from tablename where 0 = 1

Then in the array that is returned you should have the column type as used when the 
table was created.  This may save you the trouble of working through the SQLtext.  The 
where statement should result in no CPU time used for the query

regards
Greg O

  - Original Message - 
  From: Kevin Waterson 
  To: [EMAIL PROTECTED] 
  Sent: Monday, October 20, 2003 10:59 AM
  Subject: Re: [sqlite] AUTO INCREMENT


  This one time, at band camp, "Ian VanDerPoel" <[EMAIL PROTECTED]> wrote:


  > You can find the info you want in the  sqlite_master 
  > table. There is some doco on it at the sqlite.org the website. I am not sure if 
the info is held anywhere else but 
  > select * from sqlite_master where name = quotes; will return the sql used to build 
the table. you can parse the data type from that.

  yep, guess I will just write up a getFieldType function

  Thanks for your time.

  Kind regards
  Kevin

  -- 
   __  
  (_ \ 
   _) )            
  |  /  / _  ) / _  | / ___) / _  )
  | |  ( (/ / ( ( | |( (___ ( (/ / 
  |_|   \) \_||_| \) \)
  Kevin Waterson
  Port Macquarie, Australia

  -
  To unsubscribe, e-mail: [EMAIL PROTECTED]
  For additional commands, e-mail: [EMAIL PROTECTED]