[sqlite] ISO 8859
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
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
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
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
>>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
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
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
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
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
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
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
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
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
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?
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?
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
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
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...
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
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
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
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
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?
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
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?
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
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
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
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
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
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
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(*)
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
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
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?
Hello, Just wondering if there is a problem with this list. regards Greg
[sqlite] New function for SQlite
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
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?
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
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
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]
