Re: [sqlite] Limit to database and/or blob size on Mac/Windows?

[John Machin]

On 17/04/2009 12:01 AM, Vinnie wrote:
> Dear Group:
> 
> I've done some calculations and its a fairly likely scenario that my users 
> will end up with sqlite databases that are over 1 gigabyte in size, in some 
> cases 4 gigabytes. An upper limit on the number of rows in a table could be 
> as high as 100,000 (yeah that not very high). There are rows containing blobs 
> that average around 50 kilobytes in size.
> 
> Is there a limit to the database size on Windows or Macintosh? I did a search 
> and the only thing I came up with was that large file support was enabled for 
> Unix in one of the releases.
> 
> I'm looking at sqlite.c from the amalgamation and it says that >2GB file 
> support is enabled on POSIX if the underlying OS supports it. And "Similar is 
> true for Mac OS X". But there is no mention of Windows.

IIRC: Earlier this week, Richard Hipp in response to a question on 
scalability gave the impression that up to 2 TiB would behave linearly. 
So the only question remaining is whether your filesystem can handle a 
file as large as you need (a) at all (b) reliably (c) fast enough.

My *guess* is that you shouldn't have any problem (except on a Windows 
"FAT" filesystem, but you wouldn't be using that, would you?).

Irrespective of what people tell you and how authoritative they seem, I 
would recommend that you do some simple tests: create an ordinary file 
of size 3.9Gib, then say 6 GiB (4 GiB is a magic hurdle because that 
number is 2^32). If that's OK, write a couple of quick scripts, one to 
populate the database with typical rows, one to query the data base, 
retrieving both low-rowid rows and high-rowid rows and comparing the 
timing. You may wish to experiment with varying the page size (upwards).

HTH,
John

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

Re: [sqlite] SQLite-amalgamation changes every few hours

[Volker Grabsch]

D. Richard Hipp  schrieb:
> > The mingw_cross_env verifies all downloads via their SHA-1 checksum.
> > However, the SQLite source package changes every few hours. [...]
> 
> The amalgamations packages changed once after the release announcement  
> because the first package we put up was a defective build.  This was  
> brought to our attention and we reran the build and put up new and  
> corrected amalgamation packages.

Thanks for your quick reply. That information helps me a lot.

> If you have seen the amalgamation SHA1 sums change more than one time,  
> that is an indication of unauthorized hacking of the SQLite website.   
> Please let us know.  But if you saw just this one change, then please  
> discard the first amalgamation and use only the second one.

I did notice at least three different checksums, i.e. at least
two changes. However, maybe I just downloaded the package while
the regeneration or upload was in progress?

Anyway, the current download has exactly the checksum you were
providing to me:

>950855d9eed9b8bd6fad406cece5a24b640c3e3f  sqlite- 
> amalgamation-3.6.13.tar.gz

So I think the problem is solved now. I'll update the checksum
in mingw_cross_env and hope there won't be any sudden changes
in the future.

In case of last corrections I strongly recommend to use a different
version number, something like "3.6.13a" or "3.6.14". That simple
trick prevents a lot of confusion.


Greets,

Volker

-- 
Volker Grabsch
---<<(())>>---
Administrator
NotJustHosting GbR
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite-amalgamation changes every few hours

[Alexey Pechnikov]

Hello!

On Friday 17 April 2009 00:09:21 D. Richard Hipp wrote:
> If you have seen the amalgamation SHA1 sums change more than one time,  
> that is an indication of unauthorized hacking of the SQLite website.  
> Please let us know.  But if you saw just this one change, then please  
> discard the first amalgamation and use only the second one.
>
> Here is the correct SHA1 checksum for the SQLite amalgamation version  
> 3.6.13:
>
>950855d9eed9b8bd6fad406cece5a24b640c3e3f  sqlite-
> amalgamation-3.6.13.tar.gz

Can you apply checksums in announces in mail lists and site release messages?

Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite-amalgamation changes every few hours

[D. Richard Hipp]

On Apr 16, 2009, at 3:58 PM, Volker Grabsch wrote:

> Dear SQLite authors,
>
> I'd like to include SQLite in my MinGW cross compiling environment
> (http://www.profv.de/mingw_cross_env/), but I ran into some trouble.
>
> The mingw_cross_env verifies all downloads via their SHA-1 checksum.
> However, the SQLite source package changes every few hours. Is there
> any good reason for that? Why don't the source packages remain stable
> once they are released?
>

The amalgamations packages changed once after the release announcement  
because the first package we put up was a defective build.  This was  
brought to our attention and we reran the build and put up new and  
corrected amalgamation packages.

If you have seen the amalgamation SHA1 sums change more than one time,  
that is an indication of unauthorized hacking of the SQLite website.   
Please let us know.  But if you saw just this one change, then please  
discard the first amalgamation and use only the second one.

Here is the correct SHA1 checksum for the SQLite amalgamation version  
3.6.13:

   950855d9eed9b8bd6fad406cece5a24b640c3e3f  sqlite- 
amalgamation-3.6.13.tar.gz



> What's even more strange: two downloads don't just differ in some
> timestamps - they also differ in variable names and the order of
> declarations in the sqlite3.c file.
>
> So what is the meaning of "SQLite release 3.6.13" when it changes
> every now and then? How can I verify the source package download?
>
> I thought about using a mirror or simply download the source package
> from Debian. However, I can't find any sqlite.org mirrors, and Debian
> currently contains only sqlite-3.6.12.
>
> I'd really like to support SQLite in mingw_cross_env. Any help to
> solve the trouble is appreciated.
>
>
> Greets,
>
>Volker
>
> -- 
> Volker Grabsch
> ---<<(())>>---
> Administrator
> NotJustHosting GbR
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

D. Richard Hipp
d...@hwaci.com



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

Re: [sqlite] Changes to closed tickets.

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Noah Hart wrote:
> If I append a report to a closed ticket, will developers look at it, or
> should I open a new ticket?

All changes show up in the timeline, so it will be clear that changes
have been made:

  http://www.sqlite.org/cvstrac/timeline

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEARECAAYFAknnkEcACgkQmOOfHg372QRvlwCdH0TSs/4XLk2qL0HKOxAMvjAz
7ccAnjX9fs2rsI0sbk+OcTFYmYnoxce5
=610d
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite-amalgamation changes every few hours

[Volker Grabsch]

Dear SQLite authors,

I'd like to include SQLite in my MinGW cross compiling environment
(http://www.profv.de/mingw_cross_env/), but I ran into some trouble.

The mingw_cross_env verifies all downloads via their SHA-1 checksum.
However, the SQLite source package changes every few hours. Is there
any good reason for that? Why don't the source packages remain stable
once they are released?

What's even more strange: two downloads don't just differ in some
timestamps - they also differ in variable names and the order of
declarations in the sqlite3.c file.

So what is the meaning of "SQLite release 3.6.13" when it changes
every now and then? How can I verify the source package download?

I thought about using a mirror or simply download the source package
from Debian. However, I can't find any sqlite.org mirrors, and Debian
currently contains only sqlite-3.6.12.

I'd really like to support SQLite in mingw_cross_env. Any help to
solve the trouble is appreciated.


Greets,

Volker

-- 
Volker Grabsch
---<<(())>>---
Administrator
NotJustHosting GbR
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to get PRIMARY KEY of newly INSERT INTO record

[Brad Stiles]

> Cool! That worked. The VB6 wrapper has a LastInsertAutoID that I never paid
> attention / saw before.

Be sure that it's doing what you think it is.  If it's a general
purpose wrapper, then it might not.  If it's a SQLite specific
wrapper, it might, but even if it's wired up correctly, you have to be
careful to use it in the correct way.  If more than one insert, for
instance, to child tables, happens in a query, only the last id
inserted will be returned.  You don't get a stack of ids that were
inserted since the last call.  :)

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

Re: [sqlite] How to get PRIMARY KEY of newly INSERT INTO record

[Sarah M. Weinberger]

Cool! That worked. The VB6 wrapper has a LastInsertAutoID that I never paid
attention / saw before. I will use the other SQL statement that you
mentioned for the C/C++ stuff. Awesome!

Thanks again :-),

Sarah

-- 
View this message in context: 
http://www.nabble.com/How-to-get-PRIMARY-KEY-of-newly-INSERT-INTO-record-tp23083322p23084501.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] Bus Error

[D. Richard Hipp]

On Apr 16, 2009, at 2:40 PM, Christopher Bare wrote:

> Hi,
>
> Here's a head's up on an obscure error I ran into. Maybe documenting
> it will help the developers or a future victim:

Thanks.  But this has already been documented at ticket #3530 
(http://www.sqlite.org/cvstrac/tktview?tn=3530 
) and fixed by check-in [5998] (http://www.sqlite.org/cvstrac/chngview?cn=5998 
).  The fix was first available in 3.6.7.

Shane first found the problem late one night while working on  
sqllogictest and we had it fixed before breakfast the following morning.

Segfaults are never acceptable, even for malformed SQL.  So please do  
report them whenever you see them.  Tnx.

>
>
> I have a couple tables like this:
>
> sqlite> .schema tracks
> CREATE TABLE tracks (
>   uuid text primary key not null,
>   name text not null,
>   type text not null,
>   table_name text not null);
>
> sqlite> .schema attributes
> CREATE TABLE attributes (
>   uuid NOT NULL,
>   key text NOT NULL,
>   value);
>
> Tracks represent graphical objects and one of their (optional)
> attributes is z-order. So, I wanted a query that would give me back
> the tracks ordered by z-order. What I wanted was this, which actually
> works fine:
>
> select t.name, ifnull(a.value,-1) as zorder from tracks as t left join
> (select * from attributes where key='z-order') as a on t.uuid=a.uuid
> order by zorder;
>
> A little muddled thinking on my part led to this totally bass-ackwards
> attempted query:
>
> sqlite> select * from tracks left join (select * from attributes where
> tracks.uuid=attributes.uuid and key='z-order');
> Bus error
>
> I know my query is bogus, but Bus error is probably not the desired
> feedback. Although, I can't say it's not deserved.
>
> $ /opt/local/bin/sqlite3 -version
> 3.6.3
>
> running on OS X 10.5.6
>
> Sqlite rocks. Thanks,
>
> -chris
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

D. Richard Hipp
d...@hwaci.com



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

[sqlite] Bus Error

[Christopher Bare]

Hi,

Here's a head's up on an obscure error I ran into. Maybe documenting
it will help the developers or a future victim:

I have a couple tables like this:

sqlite> .schema tracks
CREATE TABLE tracks (
uuid text primary key not null,
name text not null,
type text not null,
table_name text not null);

sqlite> .schema attributes
CREATE TABLE attributes (
uuid NOT NULL,
key text NOT NULL,
value);

Tracks represent graphical objects and one of their (optional)
attributes is z-order. So, I wanted a query that would give me back
the tracks ordered by z-order. What I wanted was this, which actually
works fine:

select t.name, ifnull(a.value,-1) as zorder from tracks as t left join
(select * from attributes where key='z-order') as a on t.uuid=a.uuid
order by zorder;

A little muddled thinking on my part led to this totally bass-ackwards
attempted query:

sqlite> select * from tracks left join (select * from attributes where
tracks.uuid=attributes.uuid and key='z-order');
Bus error

I know my query is bogus, but Bus error is probably not the desired
feedback. Although, I can't say it's not deserved.

$ /opt/local/bin/sqlite3 -version
3.6.3

running on OS X 10.5.6

Sqlite rocks. Thanks,

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

Re: [sqlite] How to get PRIMARY KEY of newly INSERT INTO record

[Igor Tandetnik]

Sarah M. Weinberger
 wrote:
> QUESTION: How do I get the primary key of the newly inserted record?

sqlite3_last_insert_rowid

select last_insert_rowid();

Igor Tandetnik 



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

Re: [sqlite] How to get PRIMARY KEY of newly INSERT INTO record

[Brad Stiles]

>      'Create the SQL command.
>      strSQLCommand = "INSERT INTO " + strDBTable + " VALUES (null" + 
> strUpdate + ");"

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

Use last_insert_rowid() as a second statement in your query:

'Create the SQL command.
strSQLCommand = "INSERT INTO " + strDBTable + " VALUES (null" + strUpdate + ");"
strSQLCommand = strSQLCommand + "select last_insert_rowid();"

'DATABASE: Execute the query.
bSuccess = Me.DBConnection.Execute(strSQLCommand)

Though, you'll need to examine the resulting recordset, rather than
check for a boolean value.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite complains about invalid column name but the column exists in the table

[D. Richard Hipp]

On Apr 16, 2009, at 12:46 PM, Arthur Skowronek wrote:

> Hello,
> I have a problem again with the same database as
> before with the segfault bug.
>
> I can't insert any rows into the userMailBox table with
> this query:
>
> INSERT INTO userMailBox ( userID, senderNickID, isSystem,
> messageSubject, messageBody ) VALUES ( 0, -1, 1, "This is your new
> mailbox", "Messages sent by other users or server will be stored  
> here." )
>
> The SQLite CLI prints out that there is no messageSubject
> column but thats not true. Here are the SQL instructions
> to construct the database:
>   http://pastebin.com/m61b3a437
>


Where you have

 FOR EACH ROW WHEN ( messageSubject = '' )

It should read:

 FOR EACH ROW WHEN ( new.messageSubject = '' )




D. Richard Hipp
d...@hwaci.com



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

[sqlite] SQLite complains about invalid column name but the column exists in the table

[Arthur Skowronek]

Hello,
I have a problem again with the same database as
before with the segfault bug.

I can't insert any rows into the userMailBox table with
this query:

INSERT INTO userMailBox ( userID, senderNickID, isSystem,
messageSubject, messageBody ) VALUES ( 0, -1, 1, "This is your new
mailbox", "Messages sent by other users or server will be stored here." )

The SQLite CLI prints out that there is no messageSubject
column but thats not true. Here are the SQL instructions
to construct the database:
http://pastebin.com/m61b3a437


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

[sqlite] Changes to closed tickets.

[Noah Hart]

General question:  

If I append a report to a closed ticket, will developers look at it, or
should I open a new ticket?

For example:

Ticket #3802 was closed with "Unable to reproduce this problem"

I've added new information to allow the developers to reproduce.  Will
it be looked at, or should I resubmit the ticket.


Noah



CONFIDENTIALITY NOTICE: 
This message may contain confidential and/or privileged information. If you are 
not the addressee or authorized to receive this for the addressee, you must not 
use, copy, disclose, or take any action based on this message or any 
information herein. If you have received this message in error, please advise 
the sender immediately by reply e-mail and delete this message. Thank you for 
your cooperation.


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

Re: [sqlite] using wstring

[Fin Springs]

On Apr 16, 2009, at 6:44 AM, galeazzi-at-korg.it |sqlite| wrote:

> I tried to use (const char*)sQuery.c_str() but it doesn't work. Any  
> ideas?

That won't work because std::wstring.c_str() returns wchar_t* not char*.

One way would be to convert your std::wstring to an std::string in  
UTF-8 format by some out of band means; then c_str() will return a  
char*. For example, on Windows you could use  
WideCharToMultiByte(CP_UTF8,...). You might also look at the C++  
locale::ctype.narrow() function.

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

Re: [sqlite] Corruption of incremental_vacuum databases

[Filip Navara]

Hello,

I have expected at least some reply. Oh well, new the corruption has happened
again (on another different machine) and I have saved the database files. One
of the corrupted files is available at http://www.emclient.com/temp/folders.zip.
I'd be glad for any help or at least confirmation that it could be
related to the
issues in the tickets listed below.

Thanks,
Filip Navara

On Tue, Mar 31, 2009 at 11:05 AM, Filip Navara  wrote:
> Hello,
>
> after seeing the recent change #6413 and ticket #3761 I finally
> decided to write about a corruption issue we have.
>
> This is the environment of our application:
> - We are using SQLite 3.6.3 (with SQLite.NET 1.0.60.0).
> - Several database files. Each file is opened in it's own connection
> and never shared across them.
> - Some of these connections have another database attached to it
> (containing mostly BLOB data).
> - In all cases the connections are opened on program start and closed
> on program shutdown.
> - There's a low-priority thread that executes "pragma
> incremental_vacuum" when the application is idle and there is enough
> free pages. Code of the thread is listed below.
> - "journal_mode=persist" is used on all databases in all connections
> (to workaround a bug in the journal deletion logic on Windows, search
> for "TortoiseSVN" in the mailing list archive for details)
> - "synchronous=off" is used on all databases in all connections. This
> setting is likely to change in future, but in no case of the
> corruption a system crash was involved.
>
> Since we started using the incremental_vacuum mode we were getting
> database corruption errors pretty often (sometimes as often as once a
> day in 3 people). Most, if not all, of these corruptions happened
> following a ROLLBACK (caused by constraint violation). "pragma
> integrity_check;" on the already corrupted databases usually reported
> few "Page XXX is never used" error.
>
> Unfortunately I don't have any of the corrupted databases at hand and
> I have no deterministic way to create them. My question is if these
> could be related to the just fixed problem (in ticket 3761) or if it
> could be another issue?
>
> Best regards,
> Filip Navara
>
> 
>
> WaitHandle[] handles = new WaitHandle[] { this.stopEvent, this.wakeupEvent };
> System.Diagnostics.Stopwatch stopWatch = new System.Diagnostics.Stopwatch();
> int timeout = -1;
> int pagesPerIteration = 32;
>
> // Wait for thread shutdown and wakeup event. The shutdown event
> // is used to stop the thread on application exit. The wakeup event is
> // fired on startup if there are free pages in the database or if a DELETE
> // statement was executed.
> while (WaitHandle.WaitAny(handles, timeout, false) != 0)
> {
>        long totalFreePages = 0, freePages;
>        lock (this.repositories)
>        {
>                stopWatch.Reset();
>                stopWatch.Start();
>                foreach (IRepositoryBase repository in this.repositories)
>                {
>                       // wrapper around "pragma freelist_count;"
>                        freePages = repository.GetFreePageCount();
>                        totalFreePages += freePages;
>                        if (freePages > 0)
>                               // wrapper around "pragma incremental_vacuum(x)"
>                                repository.Compact(pagesPerIteration);
>                }
>                stopWatch.Stop();
>        }
>
>        // We start by freeing 32 pages per one iteration of the loop for
>       // each database. After each iteration the number is recalculated
>       // based on the time spent on the operation and then it's
>       // truncated to the <24;4096> range.
>        pagesPerIteration = Math.Min(Math.Max(24, (int)(100.0 *
> pagesPerIteration / stopWatch.ElapsedMilliseconds)), 4096);
>
>        // If there are still free pages in the databases then schedule the
>       // thread to wake up in 200ms and continue vacuuming them.
>        if (totalFreePages > 0)
>                timeout = 200;
>        else
>                timeout = -1;
> }
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Need help with the SQL statement.

[Joanne Pham]

Thanks a ton Igor!
It worked. Your help is greatly appreciated.
Thanks,
JP





From: Igor Tandetnik 
To: sqlite-users@sqlite.org
Sent: Thursday, April 16, 2009 4:52:28 AM
Subject: Re: [sqlite] Need help with the SQL statement.

"Joanne Pham" 
wrote in message news:348376.69121...@web90302.mail.mud.yahoo.com
> 20657220 is number of minutes in GMT time zone.
> So we need to convert to second by 20657220 *60.
> select datetime(20657220*60, 'unixepoch','localtime' );
> will be 2009-04-11 00:00:00

In this case, this should work:

strftime('%s', date(startTime*60, 'unixepoch', 'localtime'), 'utc')/60

You convert your UTC timestamp to localtime, strip time portion (by way 
of date() function), then convert the result back to UTC (by way of 
strfrime(..., 'utc'). This way you'll get a UTC timestamp that 
corresponds to midnight local time of the same calendar date.

Igor Tandetnik 



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



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

Re: [sqlite] Limit to database and/or blob size on Mac/Windows?

[Gerry Snyder]

Vinnie wrote:
> .
>
> Is there a limit to the database size on Windows or Macintosh? 
>
>   
Does the following help?

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

[sqlite] Limit to database and/or blob size on Mac/Windows?

[Vinnie]

Dear Group:

I've done some calculations and its a fairly likely scenario that my users will 
end up with sqlite databases that are over 1 gigabyte in size, in some cases 4 
gigabytes. An upper limit on the number of rows in a table could be as high as 
100,000 (yeah that not very high). There are rows containing blobs that average 
around 50 kilobytes in size.

Is there a limit to the database size on Windows or Macintosh? I did a search 
and the only thing I came up with was that large file support was enabled for 
Unix in one of the releases.

I'm looking at sqlite.c from the amalgamation and it says that >2GB file 
support is enabled on POSIX if the underlying OS supports it. And "Similar is 
true for Mac OS X". But there is no mention of Windows.

Anyone?


Thanks!

Sincerely,


Vinnie

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

Re: [sqlite] using wstring

[Igor Tandetnik]

 wrote in message
news:20090416141406.euzgo2r52i00o...@webmail.korg.it
> I cannot use parameterized queries because the queries are more
> complex than the simple one that I wrote

I'm not sure how your conclusion follows from the premise. You can use 
parameters with any query, no matter how complex.

> I've  sqlite3_get_table

sqlite3_get_table requires that the query be in UTF-8. If you insist on 
using it, then you will have to convert your Unicode string to UTF-8, no 
way around it. On the other hand, you can use sqlite3_prepare16[_v2] 
directly on a UTF-16 string (which may or may not be helpful, depending 
on your platform: on some platforms sizeof(wchar_t)==2 and wstring holds 
a 16-bit string that can be passed directly to sqlite3_prepare16, on 
others sizeof(wchar_t) == 4 and wstring would require conversion 
anyway).

Note that you can happily use sqlite3_prepare16 on a query without any 
parameters, if for some reason you are averse to them. It's still better 
than sqlite3_get_table.

Igor Tandetnik



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

Re: [sqlite] using wstring

[galeazzi]

I cannot use parameterized queries because the queries are more  
complex than the simple one that I wrote, furthermore I've to use  
sqlite3_get_table
Citando Igor Tandetnik :

>  wrote in message
> news:20090416124428.h5zi6xji5h5wk...@webmail.korg.it
>> I'm currently working with a framework which uses wstring, how can I
>> dynamically create queries like this:
>> std::wstring sQuery = L"SELECT * FROM Table WHERE Tilte = '";
>> sQuery += title; //it's a wstring
>> sQuery += "'"
>>
>> I tried to use (const char*)sQuery.c_str() but it doesn't work.
>
> Tried to use where? Doesn't work how?
>
> By the way, your approach is going to break if title happens to have
> apostrophes in it. Rather than building the statement like this, use
> parameterized queries. See http://sqlite.org/c3ref/prepare.html
>
> Igor Tandetnik
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


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

Re: [sqlite] using wstring

[galeazzi]

I cannot use parameterized queries because the queries are more  
complex than the simple one that I wrote,  I've  sqlite3_get_table
Citando Igor Tandetnik :

>  wrote in message
> news:20090416124428.h5zi6xji5h5wk...@webmail.korg.it
>> I'm currently working with a framework which uses wstring, how can I
>> dynamically create queries like this:
>> std::wstring sQuery = L"SELECT * FROM Table WHERE Tilte = '";
>> sQuery += title; //it's a wstring
>> sQuery += "'"
>>
>> I tried to use (const char*)sQuery.c_str() but it doesn't work.
>
> Tried to use where? Doesn't work how?
>
> By the way, your approach is going to break if title happens to have
> apostrophes in it. Rather than building the statement like this, use
> parameterized queries. See http://sqlite.org/c3ref/prepare.html
>
> Igor Tandetnik
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


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

Re: [sqlite] using wstring

[Igor Tandetnik]

 wrote in message
news:20090416124428.h5zi6xji5h5wk...@webmail.korg.it
> I'm currently working with a framework which uses wstring, how can I
> dynamically create queries like this:
> std::wstring sQuery = L"SELECT * FROM Table WHERE Tilte = '";
> sQuery += title; //it's a wstring
> sQuery += "'"
>
> I tried to use (const char*)sQuery.c_str() but it doesn't work.

Tried to use where? Doesn't work how?

By the way, your approach is going to break if title happens to have 
apostrophes in it. Rather than building the statement like this, use 
parameterized queries. See http://sqlite.org/c3ref/prepare.html

Igor Tandetnik 



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

Re: [sqlite] Need help with the SQL statement.

[Igor Tandetnik]

"Joanne Pham" 
wrote in message news:348376.69121...@web90302.mail.mud.yahoo.com
> 20657220 is number of minutes in GMT time zone.
> So we need to convert to second by 20657220 *60.
> select datetime(20657220*60, 'unixepoch','localtime' );
> will be 2009-04-11 00:00:00

In this case, this should work:

strftime('%s', date(startTime*60, 'unixepoch', 'localtime'), 'utc')/60

You convert your UTC timestamp to localtime, strip time portion (by way 
of date() function), then convert the result back to UTC (by way of 
strfrime(..., 'utc'). This way you'll get a UTC timestamp that 
corresponds to midnight local time of the same calendar date.

Igor Tandetnik 



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

Re: [sqlite] Mismatched columns in union can repeatably crash 2.6.10

[Tom Sillence]

2009/4/16 D. Richard Hipp 

>
> On Apr 15, 2009, at 4:38 PM, Tom Sillence wrote:
>
> > I don't have all that much time to research whether this is a known
> > issue.
> > The following query crashes 2.6.10 every time, on linux and windows:
> >
> > create table crash(a,b); insert into crash select
> > 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15 union all select
> > 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26
> > union
> > select
> > 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26
> > order by 1
> >
> > If people are interested, I'm happy to build a debug version of
> > sqlite and
> > get a stacktrace. I'm also looking for advice on the proper way to
> > report a
> > bug like this - and how to check whether similar things have been
> > reported
> > already.
>
>
> Whenever somebody gives us a simple, reproducible test case, as you
> have done above, that is a "good" bug report.  No further work
> required on your part.  Thanks.
>
> D. Richard Hipp
> d...@hwaci.com
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

I see from cvstrack that you've fixed this already, just three hours after I
reported it. I'm very impressed.

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

[sqlite] using wstring

[galeazzi]

I'm currently working with a framework which uses wstring, how can I  
dynamically create queries like this:
std::wstring sQuery = L”SELECT * FROM Table WHERE Tilte = '”;
sQuery += title; //it's a wstring
sQuery += “'”

I tried to use (const char*)sQuery.c_str() but it doesn't work. Any ideas?

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

Re: [sqlite] fragmentation, overflow pages

[John Machin]

On 16/04/2009 5:47 PM, Martin Pfeifle wrote:
> Hi,
> we store proprietary organized data in blobs within an sqlite database.
> Assume you have a table mydata(id, attr1, attr2, blob). 
> The page size of the database is 1k.

This is the *default* page size. You can change it before you first put 
data in the db file.

> If you now store blobs in your table which are larger than 1k, 
> sqlite uses overflow pages and the content of the blobs is distributed 
> to several pages, right?

Not just blobs; if your row won't fit in a page for any reason, excess 
row contents are put into one or more overflow pages.

> Are the pages on which the blobs are stored
> consecutive or can they be distributed arbitrarily within the database file?

I imagine it would use pages off the list of free pages in preference to 
extending the size of the file ... try inspecting the source code.

 > Can two records share the same overflow page, or is an overflow page 
dedicated to one record?

My reading of the relevant section of 
http://www.sqlite.org/fileformat.html suggests the answer is dedicated 
... have a look and see if you agree. BTW there's lots of interesting 
bed-time reading in there :-)

> Is there a formal definition of the fragmentation value which is reported by 
> sqlite3_analyzer?

This was discussed briefly in this forum within the last few days ... 
AFAIK the only formal definition you will find is in the analyzer source 
code.

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

[sqlite] fragmentation, overflow pages

[Martin Pfeifle]

Hi,
we store proprietary organized data in blobs within an sqlite database.
Assume you have a table mydata(id, attr1, attr2, blob). 
The page size of the database is 1k. 
If you now store blobs in your table which are larger than 1k, 
sqlite uses overflow pages and the content of the blobs is distributed 
to several pages, right? Are the pages on which the blobs are stored
consecutive or can they be distributed arbitrarily within the database file?
Can two records share the same overflow page, or is an overflow page dedicated 
to one record?
Is there a formal definition of the fragmentation value which is reported by 
sqlite3_analyzer?
Best Martin


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

Re: [sqlite] Need help with the SQL statement.

[Joanne Pham]

Hi Igor,
20657220  is number of minutes in GMT time zone.
So we need to convert to second by 20657220 *60.
select datetime(20657220*60, 'unixepoch','localtime' ); 
will be 2009-04-11 00:00:00
Thanks for the hlep Igor
JP





From: Igor Tandetnik 
To: sqlite-users@sqlite.org
Sent: Wednesday, April 15, 2009 9:17:09 PM
Subject: Re: [sqlite] Need help with the SQL statement.

"Joanne Pham" 
wrote in message news:872428.4795...@web90308.mail.mud.yahoo.com
> But the first row (20657220 1 2 101 -- this is 2009-04-11 00:00:00)
> may not be there in the dailyDataTable so min(startTime) won't work
> in this case. Any idea Igor?

I don't quite see how 20657220 can represent midnight (of any day) when 
it's not a multiple of 24*60=1440. What epoch are you counting from? 
This:

select datetime(20657220*60, 'unixepoch');

produces 2009-04-11 07:00:00 for me.

Normally, I'd expect something like "startTime / 1440 * 1440" to work 
(this simply rounds down to nearest multiple of 1440). But I guess I 
don't understand your time representation conventions.

Igor Tandetnik 



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



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