Re: [sqlite] how to disable a trigger
On 3 Oct 2011, at 4:12am, Sam Carleton wrote: > Ok, how do I list what a trigger is so that I can add it back once I want to > "reactive" it? To list all triggers: SELECT * FROM sqlite_master WHERE type='trigger' To list all triggers for a particular table: SELECT * FROM sqlite_master WHERE tbl_name='myTable' If you know the name of a trigger, SELECT * FROM sqlite_master WHERE name='myTrigger' To get just the text of the trigger and nothing else SELECT sql FROM sqlite_master WHERE name='myTrigger' to delete a trigger DROP TRIGGER myTrigger Warning: messing with TRIGGERs by disabling them should probably only be done when importing startup data. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problems building/running SQLite test.exe with ICU enabled
All DLL's are in the same folder. Test.exe SQLite.Interop.DLL System.Data.SQLite.DLL We have tried everything and cannot get this to run with ICU enabled. Is there somewhere I can post my VS project files so someone can look at this? It seems like it should be pretty straightforward, but nothing seems to work. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to disable a trigger
Simon, Thank you! I have an odd ball case where the tables have two homes: One is a 'system' db one is a 'document' db. Depending on the case, the user can make changes to the document db, which in that case, needs the triggers, but in the case where the system db is being recloned to the document db, the triggers need to be off. Sam On Mon, Oct 3, 2011 at 4:51 AM, Simon Slavin wrote: > > On 3 Oct 2011, at 4:12am, Sam Carleton wrote: > > > Ok, how do I list what a trigger is so that I can add it back once I want > to > > "reactive" it? > > > To list all triggers: > > SELECT * FROM sqlite_master WHERE type='trigger' > > To list all triggers for a particular table: > > SELECT * FROM sqlite_master WHERE tbl_name='myTable' > > If you know the name of a trigger, > > SELECT * FROM sqlite_master WHERE name='myTrigger' > > To get just the text of the trigger and nothing else > > SELECT sql FROM sqlite_master WHERE name='myTrigger' > > to delete a trigger > > DROP TRIGGER myTrigger > > Warning: messing with TRIGGERs by disabling them should probably only be > done when importing startup data. > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Cleaning unicode text
Hi SQLiters,
I am trying to clean up some text in a database that has some weird
non-printable unicode characters.
For instance:
.mode insert
select distinct "Name", length ("Name"), substr("Name",-1,1) from "My Table"
gives:
INSERT INTO table VALUES('Roundup Ready®',15,'')
As you can see, the printable text is only 14 characters long, but there are
actually 15 characters there, as confirmed by the length result. This weird
extra character seems to have the unicode hex value of f87f and appears in
various positions (not just the end) of text. I want to remove it, but can't
figure out how.
I've tried:
replace("Name", x'f87f', '') but it doesn't seem to match the weird character.
Any ideas?
Thanks,
Tom
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Cleaning unicode text
> I've tried:
> replace("Name", x'f87f', '') but it doesn't seem to match the weird character.
> Any ideas?
I guess x'f87f' will match your character only if your database is in
UTF-16. For UTF-8 you probably should try x'efa1bf'. And maybe
explicit conversion to text is needed.
Pavel
On Mon, Oct 3, 2011 at 10:14 AM, BareFeetWare wrote:
> Hi SQLiters,
>
> I am trying to clean up some text in a database that has some weird
> non-printable unicode characters.
>
> For instance:
>
> .mode insert
> select distinct "Name", length ("Name"), substr("Name",-1,1) from "My Table"
>
> gives:
>
> INSERT INTO table VALUES('Roundup Ready®',15,'')
>
> As you can see, the printable text is only 14 characters long, but there are
> actually 15 characters there, as confirmed by the length result. This weird
> extra character seems to have the unicode hex value of f87f and appears in
> various positions (not just the end) of text. I want to remove it, but can't
> figure out how.
>
> I've tried:
>
> replace("Name", x'f87f', '') but it doesn't seem to match the weird character.
>
> Any ideas?
>
> Thanks,
> Tom
>
> ___
> 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] query performance help
> As can be guessed populating table is quite slow - ~150ms for around 10k rows > in IndexME. Why do you think it's slow? 6 rows per second is insanely fast. Pavel On Sun, Oct 2, 2011 at 5:49 AM, Mira Suk wrote: > > Hey everyone, > > currently I have some temporary table which I populate this way (recursive > triggers on) > > CREATE TEMPORARY TABLE IF NOT EXISTS NestedEntriesResult_ID (IDX INTEGER NOT > NULL, DBORDER INTEGER PRIMARY KEY NOT NULL); > > CREATE TEMPORARY TRIGGER IF NOT EXISTS NestedEntriesPopulate_ID > AFTER INSERT > ON NestedEntriesResult_ID > FOR EACH ROW > BEGIN > INSERT INTO NestedEntriesResult_ID (IDX) SELECT IndexME.IDI FROM IndexME > WHERE IndexME.Parent = New.IDX ORDER BY IndexME.[Order]; > END > > INSERT INTO NestedEntriesResult_ID (IDX) VALUES (?) > this insert runs chain of triggers which insert all child items in following > way > 0 - 1 - 2 - 3 > - 4 > - 5 - 6 > - 7 > - 8 > > > table IndexME contains tree-like structure where IDI - primary key, Parent - > item tree parent - link to "parent item" IDI, Order - item order within same > Parent (aka unique value for rows which have same Parent). All important > columns are indexed. > As can be guessed populating table is quite slow - ~150ms for around 10k rows > in IndexME. > > Suggestions for better (faster) method ? > I've yet to try simply doing it C, but I don't think running 10k inserts (to > get result into table - as it's part of another query) alone will give me > better performance. > > thanks in advance, > M. > > > ___ > 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] EXT :Re: Problems building/running SQLite test.exe with ICU enabled
You need to be aware of the DLL search paths...can be quite confusing... http://msdn.microsoft.com/en-us/library/windows/desktop/ms682586(v=vs.85).aspx#related_topics Process explorer should help ensure you're running what you think you are http://support.microsoft.com/kb/970920 Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Marc Henrickson [marc.henrick...@wtsparadigm.com] Sent: Monday, October 03, 2011 8:27 AM To: sqlite-users@sqlite.org Subject: EXT :Re: [sqlite] Problems building/running SQLite test.exe with ICU enabled All DLL's are in the same folder. Test.exe SQLite.Interop.DLL System.Data.SQLite.DLL We have tried everything and cannot get this to run with ICU enabled. Is there somewhere I can post my VS project files so someone can look at this? It seems like it should be pretty straightforward, but nothing seems to work. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to get the grand total of count(*) in a select statement
I have failed to answer for myself after googling extensively. Here is my sql statement, select region, count(*) from hosts group by region; And it produce, region count nyc 34 bos 21 was 20 My question is how to the grand total of count(*) also in one statement as in, region count nyc 34 bos 21 was 20 total 75 Thanks Any pointer is appreciated. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to get the grand total of count(*) in a select statement
This should do it. select region, count(*) from hosts group by region union all select 'Total, count(*) from hosts ; On 10/3/2011 11:49 AM, James Kang wrote: select region, count(*) from hosts group by region ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to get the grand total of count(*) in a select statement
That should be select 'Total', count(*) from hosts On 10/3/2011 11:52 AM, Jim Morris wrote: This should do it. select region, count(*) from hosts group by region union all select 'Total, count(*) from hosts ; On 10/3/2011 11:49 AM, James Kang wrote: select region, count(*) from hosts group by region ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] how to do automatic rollback on any error?
If I do BEGIN EXCLUSIVE insert / update / delete queries here COMMIT does it guarantee it will automatically rollback if the error occurs or do I have to specify it manually? What happens if I have 20 queries, 10 execute successfully (after COMMIT) and then it fails - will the database insert / update / delete first 10 or will it return to initial state before any inserts? Or do I have to use something like INSERT INTO [table] OR ROLLBACK... or something like that? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to get the grand total of count(*) in a select statement
That does it. Thank you! Now, I feel a little shame and will pick up a SQL book to read. On Oct 3, 2011, at 2:53 PM, Jim Morris wrote: > > That should be select 'Total', count(*) from hosts > On 10/3/2011 11:52 AM, Jim Morris wrote: >> This should do it. >> select region, count(*) from hosts group by region >> union all >> select 'Total, count(*) from hosts >> ; >> >> >> >> On 10/3/2011 11:49 AM, James Kang wrote: >>> select region, count(*) from hosts group by region >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to do automatic rollback on any error?
On 10/3/2011 3:21 PM, Pero Mirko wrote: If I do BEGIN EXCLUSIVE . insert / update / delete queries here COMMIT does it guarantee it will automatically rollback if the error occurs or do I have to specify it manually? http://sqlite.org/lang_conflict.html What happens if I have 20 queries, 10 execute successfully (after COMMIT) and then it fails - will the database insert / update / delete first 10 or will it return to initial state before any inserts? The default behavior is ABORT, which means the statement that caused the error is rolled back, but the transaction stays open with any prior changes still in place. If you COMMIT at this point, those changes will get committed. Or do I have to use something like INSERT INTO [table] OR ROLLBACK... or something like that? Either that, or you'll have to check for errors and, if any occur, issue a ROLLBACK statement. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problems building/running SQLite test.exe with ICU enabled
Your list does not seem to include the ICU DLLs that I mentioned, including: icuuc48.dll icuin48.dll The necessary ICU files can be obtained from the official web site: http://site.icu-project.org/ -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3.OperationalError: disk I/O error
Hello,
I have a piece of code that works fine on my desktop in cygwin, but fails on
our server. I am creating a simple database using the sqlite3 module in python.
I open a connection
con=sqlite3.connect("./file.db") #works
cur=con.cursor() #works
cur.execute("""create table test(name, age)""") #fails on server, works on
desktop
the result:
An empty file.db is created, and I get "sqlite3.OperationalError: disk I/O
error" on the execute command.
If I use ":memory:", the command works fine. For some reason, it is having
trouble writing to the disk. The disk is actually some networked file system;
not sure if that matters.
Thanks,
Kerry
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3.OperationalError: disk I/O error
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 10/03/2011 03:05 PM, Dungan, Kerry wrote: > the result: > An empty file.db is created, and I get "sqlite3.OperationalError: disk I/O > error" on the execute command. There could be any number of reasons. The message is coming from the SQLite library and it is doing that because of something that happened with the operating system. My best guess is that the journal can't be created for some reason. The server may have extra security rules, quotas etc causing this. The easiest way to find the problem is to run the program under strace which will show every system call made and the response. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.11 (GNU/Linux) iEYEARECAAYFAk6KO5EACgkQmOOfHg372QT59gCgsGUnfKkN0XvZoEB6ULgV1tQL 1YoAn3GNFXbOwyYQpJ6/VAO0rErdGWZA =6WFN -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] System.Data.SQLite version 1.0.75.0 released
System.Data.SQLite version 1.0.75.0 is now available on the System.Data.SQLite website: http://system.data.sqlite.org/ Further information about this release can be seen at http://system.data.sqlite.org/index.html/doc/trunk/www/news.wiki Please post on the SQLite mailing list (sqlite-users at sqlite.org) if you encounter any problems with this release. -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
