Re: [sqlite] Optimizing performance by moving large texts into a separate table
[Default] On Thu, 8 Nov 2007 00:50:47 +0300, "Igor Sereda" <[EMAIL PROTECTED]> wrote: >Thank you! How about separate DB just for large texts? >Would that be an overkill? I think it would be overkill indeed. I also think it's better to have 'too large' pages for the 'numeric table' than to have too small pages for the 'text table'. >We could use different page sizes for the two DBs. I'm not sure >how well transactions over several DBs are handled though. The problem with using separate databases is that you can't force referential integrity and cascading updates and deletes (using triggers) between the 'numeric table' and the 'text table'. >Best regards, >Igor HTH -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Optimizing performance by moving large texts into a separate table
"Igor Sereda" <[EMAIL PROTECTED]> wrote: > Thank you! How about separate DB just for large texts? Would that be an > overkill? We could use different page sizes for the two DBs. I'm not sure > how well transactions over several DBs are handled though. > You could do that, but the benefits are dubious. Transactions across multiple databases are atomic, but quite a bit more disk I/O is involved in making them so. I personally just put BLOBs in a separate table. That seems to be sufficient. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Optimizing performance by moving large texts into a separate table
Thank you! How about separate DB just for large texts? Would that be an overkill? We could use different page sizes for the two DBs. I'm not sure how well transactions over several DBs are handled though. Best regards, Igor -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, November 08, 2007 12:30 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Optimizing performance by moving large texts into a separate table "Igor Sereda" <[EMAIL PROTECTED]> wrote: > We have a database that can possibly grow into millions of rows. Some > = tables have TEXT fields, which may store texts of signigicant > length. = All other data is mostly numeric values. > > We have a thought of moving all large texts into a separate table, and > = replacing text_column with text_id in the rest of the schema. The = > assumption is that db pages are allocated fully to a single table, so > = the numerical part of the database will end up in a few db pages and > so = we'll be able to quickly run queries over them. (We won't have > queries = for texts, only look-ups by text_id.) > > Is our assumption correct? Is that a pattern someone here has = > implemented maybe? How does the size of the whole database affect = > queries to a single table? > This is a good assumption. Keeping large CLOBs and BLOBs in a separate table and referencing them by rowid is what I do. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Optimizing performance by moving large texts into a separate table
[EMAIL PROTECTED] wrote: "Igor Sereda" <[EMAIL PROTECTED]> wrote: We have a database that can possibly grow into millions of rows. Some = tables have TEXT fields, which may store texts of signigicant length. = All other data is mostly numeric values. We have a thought of moving all large texts into a separate table, and = replacing text_column with text_id in the rest of the schema. The = assumption is that db pages are allocated fully to a single table, so = the numerical part of the database will end up in a few db pages and so = we'll be able to quickly run queries over them. (We won't have queries = for texts, only look-ups by text_id.) Is our assumption correct? Is that a pattern someone here has = implemented maybe? How does the size of the whole database affect = queries to a single table? This is a good assumption. Keeping large CLOBs and BLOBs in a separate table and referencing them by rowid is what I do. I've had good luck with putting large texts into their own files, and storing the file names in the tables. But that precludes searches within the texts themselves. My applications didn't need to do that. b.g. -- Bill Gatliff [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Optimizing performance by moving large texts into a separate table
"Igor Sereda" <[EMAIL PROTECTED]> wrote: > We have a database that can possibly grow into millions of rows. Some = > tables have TEXT fields, which may store texts of signigicant length. = > All other data is mostly numeric values. > > We have a thought of moving all large texts into a separate table, and = > replacing text_column with text_id in the rest of the schema. The = > assumption is that db pages are allocated fully to a single table, so = > the numerical part of the database will end up in a few db pages and so = > we'll be able to quickly run queries over them. (We won't have queries = > for texts, only look-ups by text_id.) > > Is our assumption correct? Is that a pattern someone here has = > implemented maybe? How does the size of the whole database affect = > queries to a single table? > This is a good assumption. Keeping large CLOBs and BLOBs in a separate table and referencing them by rowid is what I do. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Optimizing performance by moving large texts into a separate table
We have a database that can possibly grow into millions of rows. Some tables have TEXT fields, which may store texts of signigicant length. All other data is mostly numeric values. We have a thought of moving all large texts into a separate table, and replacing text_column with text_id in the rest of the schema. The assumption is that db pages are allocated fully to a single table, so the numerical part of the database will end up in a few db pages and so we'll be able to quickly run queries over them. (We won't have queries for texts, only look-ups by text_id.) Is our assumption correct? Is that a pattern someone here has implemented maybe? How does the size of the whole database affect queries to a single table? Thanks! Igor - To unsubscribe, send email to [EMAIL PROTECTED] -
SV: [sqlite] Debug Build Works, Release Doesn't
Om du inte protestera så skulle jag vilja hoppa över denna veckas Torsdagsträff För att lägga allt id på att få bokslutet klart. Jag har pratat med Olle flera gånger, men glömt att be honom kolla på burken. Vilken adress var det den sitter på ? /Bertil -Ursprungligt meddelande- Från: Teg [mailto:[EMAIL PROTECTED] Skickat: den 7 november 2007 18:24 Till: PokerAce Ämne: Re: [sqlite] Debug Build Works, Release Doesn't Hello PokerAce, Wednesday, November 7, 2007, 12:07:49 PM, you wrote: P> I'm using version 3.5.2. I built the static library using MSVC++ 6. The P> debug build works great. However, when I use the release build, the connect P> works, but every SQL statement I try to execute returns "SQL logic error or P> missing database." I know it's not a configuration problem between the P> debug and release builds of my program, because if I link the debug build of P> SQLite into the release build of my application, everything works great. P> I log the SQL statements before they are executed and they are fine. I P> check the name of the db file before connecting to it, and that's fine. I P> checked the build options between the Debug and Release builds of SQLite and P> other than the standard differences, nothing stands out. P> Anyone experience anything like this before? P> I should also mention that I was previously using version 3.3.8 with the P> exact same build settings and it worked fine. I'd probably start by cutting back on the optimizations and see what happens. I'd never had "whole program" optimization work without breaking my code for example. I'd leave it in release build, select all the projects and disable optimization and see if it works. Release and Debug builds can often have different results. Debug builds initialize variables which release builds don't. Happens to me all the time. You can also build debug INTO a release build by setting the options in the compiler and linker tabs. I do that from time to time when I have weird release build issues. If the optimizer's being used, you'll be hopping around the code unexpectedly but, you can still get the jist of what's going on. -- Best regards, Tegmailto:[EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Debug Build Works, Release Doesn't
I'm using version 3.5.2. I built the static library using MSVC++ 6. The debug build works great. However, when I use the release build, the connect works, but every SQL statement I try to execute returns "SQL logic error or missing database." I know it's not a configuration problem between the debug and release builds of my program, because if I link the debug build of SQLite into the release build of my application, everything works great. I log the SQL statements before they are executed and they are fine. I check the name of the db file before connecting to it, and that's fine. I checked the build options between the Debug and Release builds of SQLite and other than the standard differences, nothing stands out. Anyone experience anything like this before? I should also mention that I was previously using version 3.3.8 with the exact same build settings and it worked fine.
Re: [sqlite] Collate
"Aviad Harell" <[EMAIL PROTECTED]> wrote:
> tnx for the quick replay.
>
> > when using collate NOCASE on some column definition, how does a
> > specific element is chose to be returned, the upper case, the lower
> > case or the first one appears?
>
> >Collation doesn't affect how the data is stored, just how it's compared.
> >You will get your strings out in exactly the same form you put them in.
>
> lets say i have the following table:
>
> create table t1 (col1 text collate nocase)
>
> insert t1 values {a}
> insert t1 values {A}
>
> and then i execute the following:
>
> select col1
> from t1
> group by col1
>
>
> the result will be: a or A?
>
That is undefined. It might be either.
--
D. Richard Hipp <[EMAIL PROTECTED]>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Re: Collate
tnx for the quick replay.
> when using collate NOCASE on some column definition, how does a
> specific element is chose to be returned, the upper case, the lower
> case or the first one appears?
>Collation doesn't affect how the data is stored, just how it's compared.
>You will get your strings out in exactly the same form you put them in.
lets say i have the following table:
create table t1 (col1 text collate nocase)
insert t1 values {a}
insert t1 values {A}
and then i execute the following:
select col1
from t1
group by col1
the result will be: a or A?
On 11/7/07, Igor Tandetnik <[EMAIL PROTECTED]> wrote:
>
> Aviad Harell <[EMAIL PROTECTED]>
> wrote:
> > is there any implications on performance of using collate i(NOCASE for
> > example) in the column definition when creating a table? doesn't it
> > effect the indexes use of those columns?
>
> If you also create an index on this column, and don't explicitly specify
> what collation the index should use, it would use the collation declared
> on the column. If no collation is specified anywhere, the default is
> COLLATE BINARY.
>
> > when using collate NOCASE on some column definition, how does a
> > specific element is chose to be returned, the upper case, the lower
> > case or the first one appears?
>
> Collation doesn't affect how the data is stored, just how it's compared.
> You will get your strings out in exactly the same form you put them in.
>
> Igor Tandetnik
>
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
>
> -
>
>
Re: [sqlite] How should virtual table module return its own error messages?
Hi, > Greetings, > > I followed one of the wiki notes on how a virtual table module method > such as xUpdate should set error message for consumption upstream. A > code snippet is: > ... > sqlite3_free(aVTab->zErrMsg); > aVTab->zErrMsg = sqlite3_mprintf( "No such FOO: %s", foo ); > ... > > where aVTab points to sqlite3_vtab. > > But my message gets ignored and replaced with stock error message > associated with the return code such as: > > "SQL logic error or missing database" > > Did I follow an obsolete note? Is it possible to do what I want? Is > there a special return code I should return in such cases rather than > SQLITE_ERROR? See: http://www.sqlite.org/cvstrac/tktview?tn=2215 It would be great if this could be fixed. Thanks, Andy. - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] How should virtual table module return its own error messages?
Thanks Andy > -Original Message- > From: Andrew McDermott [mailto:[EMAIL PROTECTED] > Sent: Wednesday, November 07, 2007 7:47 AM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] How should virtual table module return > its own error messages? > > > Hi, > > > Greetings, > > > > I followed one of the wiki notes on how a virtual table > module method > > such as xUpdate should set error message for consumption > upstream. A > > code snippet is: > > ... > > sqlite3_free(aVTab->zErrMsg); > > aVTab->zErrMsg = sqlite3_mprintf( "No such FOO: %s", foo ); > > ... > > > > where aVTab points to sqlite3_vtab. > > > > But my message gets ignored and replaced with stock error message > > associated with the return code such as: > > > > "SQL logic error or missing database" > > > > Did I follow an obsolete note? Is it possible to do what I > want? Is > > there a special return code I should return in such cases > rather than > > SQLITE_ERROR? > > See: http://www.sqlite.org/cvstrac/tktview?tn=2215 > > It would be great if this could be fixed. > > Thanks, > Andy. > > > -- > --- > To unsubscribe, send email to [EMAIL PROTECTED] > -- > --- > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: How create a databse user ?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 paulito santana wrote: > Create a account in database for login ! Like you make in ORACLE, MySQL : > > CREATE USER mike There are no such things as users or logins in SQLite. It's an embedded database. If you want to implement a permission system yourself, it's possible using the authorizer hook. - -- Gerhard > 2007/11/7, Igor Tandetnik <[EMAIL PROTECTED]>: >> paulito santana wrote: >>> in "SQLite" i can create a database user (in commnad line) ? What is >>> the sintax ? >> What exactly do you mean by "database user"? -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFHMb3KdIO4ozGCH14RAg1dAJ0WDuSg1e8INYolVXmNKPCePsYA6wCgiXby 6EcYuL0/YxLYkKhNcoIXJqQ= =BdwL -END PGP SIGNATURE- - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Re: How create a databse user ?
paulito santana <[EMAIL PROTECTED]> wrote: Create a account in database for login ! Like you make in ORACLE, MySQL : CREATE USER mike SQLite doesn't provide access control, and doesn't have a notion of a "user" or an "account". So there's nothing to create. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: How create a databse user ?
Create a account in database for login ! Like you make in ORACLE, MySQL : CREATE USER mike 2007/11/7, Igor Tandetnik <[EMAIL PROTECTED]>: > > paulito santana > <[EMAIL PROTECTED]> wrote: > > in "SQLite" i can create a database user (in commnad line) ? What is > > the sintax ? > > What exactly do you mean by "database user"? > > Igor Tandetnik > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > > - > >
Re: [sqlite] Sqlite Rename table
Hi All,
about renaming table in sqlite (not sqlite3)
we can use this sql
'SELECT sql,name,type FROM sqlite_master WHERE tbl_name = 'table' ORDER BY
type DESC;'
to create a new table like the first one, and then use insert into new table
from select * from the previous one.
By this way we can keep all the structure and the indexes of the previous
table.
2007/11/6, Valerio Bontempi <[EMAIL PROTECTED]>:
>
> @ Kishor:
> thank you for the idea:
> although it wasn't perfectly what I needed (I have to write a generic
> method to rename a table, so I don't know the structure of the table before)
> it took me the right idea and lastly I used the following sql:
>
> create table new as select * from old
>
> It still doesn't allow to recreate the exact structure of the prevoius
> table (eg indexes and keys) but it should be useful for my needs.
> :-)
>
> @John: thanks a lot for your suggest :-)
> in a future release of our opensource project probably we will translate
> our database support class (mysql, postgres and sqlite) using PDO in order
> to use sqlite3
>
>
> Regards
>
> Valerio
>
>
> 2007/11/6, John Stanton <[EMAIL PROTECTED]>:
> >
> > Sqlite3 is supported by PHP using PDO.
> >
> > Valerio Bontempi wrote:
> > > Hi Kees,
> > >
> > > thanks for your solution, it is a very interesting solution.
> > > But I need to rename a table using sql from php.
> > > (this is also the reason for my need of sqlite and not sqlite3, not
> > > supported yet by php)
> > >
> > > Thanks a lot
> > >
> > > Valerio
> > >
> > > 2007/11/5, Kees Nuyt < [EMAIL PROTECTED]>:
> > >>
> > >> Hi Valerio,
> > >>
> > >> On Mon, 5 Nov 2007 18:51:20 +0100, "Valerio Bontempi"
> > >> <[EMAIL PROTECTED] > wrote:
> > >>
> > >>> Hi All,
> > >>>
> > >>> I'm new in your mailing list.
> > >>> I need to use sqlite and not sqlite3 because of php still support
> > only
> > >> the
> > >>> first version.
> > >>> I read that sqlite3 support table renaming with common sql syntax
> > >>> alter table table1 rename to table2
> > >>>
> > >>> but, although it works fine on sqlite3, it doesn't work on sqlite
> > >>>
> > >>> Can anyone confirm that table renaming isn't supported in sqlite
> > first
> > >>> version?
> > >>> Moreover, if so, is there another way to do it?
> > >>>
> > >>> Thanks and regards
> > >>>
> > >>> Valerio Bontempi
> > >> If the name of your table plus a leading and trailing space
> > >> doesn't exist anywhere in your data, you could do :
> > >>
> > >> Windows:
> > >> sqlite old_db .dump |
> > >> awk "{sub(/ oldtable /,\" newtable \");print}" |
> > >> sqlite new_db
> > >>
> > >> Unix:
> > >> sqlite old_db .dump |
> > >> awk '{sub(/ oldtable /," newtable ");print}' |
> > >> sqlite new_db
> > >>
> > >> (all on one line, but I added linefeeds after every pipe
> > >> character for clarity)
> > >>
> > >> Untested, parenthesis in positions where i assume spaces might
> > >> cause some problems, but you get the idea.
> > >>
> > >> HTH
> > >> --
> > >> ( Kees Nuyt
> > >> )
> > >> c[_]
> > >>
> > >>
> > >>
> > -
> >
> > >> To unsubscribe, send email to [EMAIL PROTECTED]
> > >>
> > >>
> > -
> >
> > >>
> > >>
> > >
> > >
> >
> >
> >
> > -
> > To unsubscribe, send email to [EMAIL PROTECTED]
> >
> > -
> >
> >
>
>
> --
> *
> Valerio Bontempi
> Blog: http://mithland.wordpress.com/
> *
>
--
*
Valerio Bontempi
Blog: http://mithland.wordpress.com/
*
[sqlite] Re: Collate
Aviad Harell <[EMAIL PROTECTED]> wrote: is there any implications on performance of using collate i(NOCASE for example) in the column definition when creating a table? doesn't it effect the indexes use of those columns? If you also create an index on this column, and don't explicitly specify what collation the index should use, it would use the collation declared on the column. If no collation is specified anywhere, the default is COLLATE BINARY. when using collate NOCASE on some column definition, how does a specific element is chose to be returned, the upper case, the lower case or the first one appears? Collation doesn't affect how the data is stored, just how it's compared. You will get your strings out in exactly the same form you put them in. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: How create a databse user ?
paulito santana <[EMAIL PROTECTED]> wrote: in "SQLite" i can create a database user (in commnad line) ? What is the sintax ? What exactly do you mean by "database user"? Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] How create a databse user ?
Hello, in "SQLite" i can create a database user (in commnad line) ? What is the sintax ? Regards, Paulito
[sqlite] Collate
Hi, is there any implications on performance of using collate i(NOCASE for example) in the column definition when creating a table? doesn't it effect the indexes use of those columns? when using collate NOCASE on some column definition, how does a specific element is chose to be returned, the upper case, the lower case or the first one appears?
Re: [sqlite] UTF-8 BLOB
On Nov 6, 2007 8:55 AM, Joe Wilson <[EMAIL PROTECTED]> wrote: > It works in a Linux xterm. > There's probably some UTF or codepage issue with the Windows console. > Try using another command-line shell. > Thank you for your response Joe. It is a codepage issue. Using sqlite3.exe 3.5.2 from a Windows XP cmd.exe box, I tried SELECT blb FROM textblob WHERE blb IS NOT NULL; and got codepage 437 representations of the BLOB data in the blb field. Using the same query with tclsh and the tclsqlite3.dll 3.5.2 the output in the Windows XP cmd.exe box was a codepage 850 representation of the data. Also the sqlite3.exe 3.5.2 query SELECT dec, hex FROM textblob WHERE blb = 'À0'; worked when I substituted the codepage 437 glyphs for À0. I have yet to suceed with tclsh. Thanks again for the hint. I doubt that I would have remembered these codepage discrepancies if you hadn't reminded me. It has been several years since I last had this problem. Robert Wishlaw > --- Robert Wishlaw <[EMAIL PROTECTED]> wrote: > > > Using sqlite 3.5.2 on Windows XP, I have a textblob.csv file > > > > 192,C0,À0,À0 > > 193,C1,Á0,Á0 > > 254,FE,þ0,þ0 > > 255,FF,ÿ0,ÿ0 > > > > which I have imported into a new database > > > > sqlite3 textblob.db < textblob.sql > > > > via a file named textblob.sql > > > > .separator , > > create Table textblob(dec INTEGER, hex BLOB, txt TEXT, blb BLOB); > > .import 'textblob.csv' textblob > > > > When I run the query > > > > SELECT dec, hex FROM textblob WHERE blb = 'À0'; > > > > there is no result. Likewise > > > > SELECT dec, hex FROM textblob WHERE blb = "À0"; > > > > does not work. > > > > SELECT dec, hex FROM textblob WHERE hex = "C0"; > > > > returns > > > > 192|C0 > > > > Any idea how to get the BLOB data? Or is the problem that the BLOB > > data is not there because .import is filtering out invalid UTF-8? > > > > __ > Do You Yahoo!? > Tired of spam? Yahoo! Mail has the best spam protection around > http://mail.yahoo.com > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > - To unsubscribe, send email to [EMAIL PROTECTED] -
